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

February 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?

tags: ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

5 Comments to "`;`.`*`.`.` is a valid column name"

  1. Zilvinas wrote:

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

  2. Sergei Golubchik wrote:

    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:

    <delimited identifier> ::= <double quote> <delimited identifier body> <double quote>
    <delimited identifier body> ::= <delimited identifier part>...
    <delimited identifier part> ::=
    <nondoublequote character>
    | <doublequote symbol>
    <doublequote symbol> ::= "" !! two consecutive double quote characters
    ...
    6) A <nondoublequote character> is any character of the source language character set other than a <double
    quote>.

  3. Sergei Golubchik wrote:

    fine, thanks HTML markup :)

    http://pastebin.com/m29081b3d

  4. shlomi wrote:

    @Zilivinas - thanks for the link!

    @sergei - thanks, also fixed comment's HTML issues.

  5. themattreid wrote:

    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 or just never learned about the back tick escapes in MySQL.

Leave Your Comment

 
Powered by Wordpress and MySQL. Theme by openark.org