Skip to content

# `;`.`*`.`.` is a valid column name

MySQL Security Syntax February 12, 2009February 12, 2009

And the following query:

>     SELECT `;`.`*`.`.` FROM `;`.`*`;

is valid as well. So are the following:

>     DROP DATABASE IF EXISTS `;`;
>     CREATE DATABASE `;`;
>     CREATE TABLE `;`.`*` (`.` INT);
>     CREATE TABLE `;`.```` (`.` INT);
>     CREATE TABLE `;`.`$(ls)` (`.` INT);

So, on my Linux machine:

>     root@mymachine:/usr/local/mysql/data# ls -l
>     total 30172
>     drwx------ 2 mysql mysql     4096 2009-01-11 08:00 ;
>     -rw-rw---- 1 mysql mysql 18874368 2009-01-09 19:08 ibdata1
>     -rw-rw---- 1 mysql mysql  5242880 2009-01-09 19:08 ib_logfile0
>     -rw-rw---- 1 mysql mysql  5242880 2009-01-09 19:08 ib_logfile1
>     drwxr-x--- 2 mysql mysql     4096 2008-12-09 11:38 mysql
>     -rw-rw---- 1 mysql mysql  1423612 2009-01-11 08:00 mysql-bin.000001
>     -rw-rw---- 1 mysql mysql       19 2009-01-04 09:05 mysql-bin.index
>     drwx------ 2 mysql mysql     4096 2008-12-21 13:58 sakila
>     -rw-rw---- 1 mysql root      9783 2009-01-04 09:05 mymachine.err
>     -rw-rw---- 1 mysql mysql        6 2009-01-04 09:05 mymachine
>     .pid
>     drwx------ 2 mysql mysql     4096 2009-01-04 08:30 world

Well then…

>     root@mymachine:/usr/local/mysql/data# cd ;
>     root@mymachine:~#

Trying again:

>     root@mymachine:~# cd -
>     /usr/local/mysql/data
>     root@mymachine:/usr/local/mysql/data# cd ";"
>     root@mymachine:/usr/local/mysql/data/;#

And now:

>     root@mymachine:/usr/local/mysql/data/;# ls -l *.frm
>     -rw-rw---- 1 mysql mysql 8554 2009-01-11 08:00 `.frm
>     -rw-rw---- 1 mysql mysql 8554 2009-01-11 08:00 *.frm
>     -rw-rw---- 1 mysql mysql 8554 2009-01-11 08:00 $(ls).frm

Oh, sorry, I meant:

>     root@mymachine:/usr/local/mysql/data/;# ls -l "*".frm
>     -rw-rw---- 1 mysql mysql 8554 2009-01-11 08:00 *.frm

Weird.

As a nice surprise, though, the dot (.) is not allowed in database or table names (but is allowed in column names). Nor are the slash (/) and backslash (\). Look here for more on this.

#### Support for non English naming

I kinda new about this all along, but never thought of the consequences. It’s nice to have a relaxed naming rule (I can even name my tables in Hebrew if I like), but “nice” doesn’t always play along with “practical”.

As a Hebrew speaker, I repeatedly encounter issues with using my language. In many applications Hebrew encoding is not supported (many times even UTF8 isn’t). Not to mention the fact that Hebrew is written from right to left. On many occasion I was irritated by the lack of support for non-English or non-ASCII characters.

But not always and not everywhere. I’ve had my share of programming languages, and, to be honest, I never expected my programming language to support UTF8 encoding for function names, variables, modules, packages or whatever. Using “a-zA-Z0-9_” is just fine. Many people who are not well familiar with English just name their variables in their native language, but written with English characters. This works well till you get someone from outside the country, who doesn’t speak the language and does not understand (nor can pronounce, nor has the matching keyboard layout or knows how to use it) the names.

In the same way, I have no wish for my table names to be named in Hebrew, German or Japanese names. English is just fine.

Using non-letter characters just adds to the mess. Popular “command” characters such as ‘~’, ‘,’, ‘:’, ‘;’, ‘*’, ‘?’, ‘(‘, ‘$’ are better left alone. They don’t belong in database or table names (mapped to file names) or column names (internally handled by MySQL).

English has become the de-facto computer world language. Programming languages, file systems, TCP/IP protocols, SQL: everything “speaks” English.

#### Security

There’s another aspect, though: security. It may sound silly, but you can actually write complete scripts in a table’s name! Not wanting to give the wrong idea, I’m not presenting some table names which can wreak havoc on your machine if used improperly.

But think about it: don’t we all use a couple of scripts which backup/clean/automate some stuff for us? Don’t these scripts just go ahead and read some table names, then do stuff on those tables? How well do they trust table names?

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
5
Leave a Reply

5 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
4 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Zilvinas

Nice insights there. I think you will find this link interesting: http://dev.mysql.com/doc/refman/5.1/en/identifier-mapping.html

Guest
Sergei Golubchik

Two comments: 1. In 5.1 table names are converted to a safe representation, you won’t see these dangerous characters in a filename there. 2. All these characters are actually allowed in identifiers by the standard, MySQL must support all them. In a delimited identifier one can have *any* character: ::= ::= … ::= | ::= “” !! two consecutive double quote characters … 6) A is any character of the source language character set other… Read more »

Guest
Sergei Golubchik

fine, thanks HTML markup 🙂

http://pastebin.com/m29081b3d

Author
shlomi

@Zilivinas – thanks for the link!

@sergei – thanks, also fixed comment’s HTML issues.

Guest
themattreid

Those are valid names, but are a total hassle to deal with when writing automation scripts and general day to day DBA duties. Having to always escape table/schema names is annoying at best, and time consuming as well. I prefer to tell my clients to follow some basic naming convention rules: stay away from special characters like $,%,^,#,&,*,; and so forth. In addition, using a dash “-” in names is possible, but an underscore “_” is preferred. Just because we can do things doesn’t always mean we should 🙂 Nice write up though, I think a lot of people forget… Read more »

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.