Recipe 2.6. Handling Special Characters in Identifiers

Problem

You need to construct SQL statements that refer to identifiers containing special characters.

Solution

Quote the identifiers so that they can be inserted safely into statement strings.

Discussion

Section 2.5 discusses how to handle special characters in data values by using placeholders or quoting methods. Special characters also can be present in identifiers such as database, table, and column names. For example, the table name some table contains a space, which is not allowed by default:

mysql> CREATE TABLE some table (i INT); ERROR 1064 (42000): You have an error in your SQL syntax near 'table (i INT)' 

Special characters are handled differently in identifiers than in data values. To make an identifier safe for insertion into an SQL statement, quote it by enclosing it within backticks:

mysql> CREATE TABLE `some table` (i INT); Query OK, 0 rows affected (0.04 sec) 

If a quoting character appears within the identifier itself, double it when quoting the identifier. For example, quote abc`def as `abc``def`.

In MySQL, backticks are always allowed for identifier quoting. If the ANSI_QUOTES SQL mode is enabled, the double-quote character also is legal for quoting identifiers. Thus, both of the following statements are equivalent with the ANSI_QUOTES SQL mode enabled:

CREATE TABLE `some table` (i INT); CREATE TABLE "some table" (i INT); 

If it's necessary to know which identifier quoting characters are allowable, issue a SELECT @@sql_mode statement to retrieve the SQL mode and check whether its value includes ANSI_QUOTES.

Be aware that although strings in MySQL normally can be quoted using either single-quote or double-quote characters ('abc', "abc"), that is not true when ANSI_QUOTES is enabled. In that case, MySQL interprets 'abc' as a string and "abc" as an identifier, so you must use only single quotes for strings.

Within a program, you can use an identifier-quoting routine if your API provides one, or write one yourself if it does not. Perl DBI has a quote_identifier⁠(⁠ ⁠ ⁠) method that returns a properly quoted identifier. For an API that has no such method, you can quote an identifier by enclosing it within backticks and doubling any backticks that occur within the identifier. Here's a Ruby routine that does so:

def quote_identifier(ident)   return "`" + ident.gsub(/`/, "``") + "`" end 

If you're willing to assume that an identifier has no internal backticks, you can simply enclose it within backticks.

Portability note: If you write your own identifier-quoting routines, remember that other DBMSs may require different quoting conventions.

In some contexts, identifiers might be used as data values, and should be handled as such. If you select information from INFORMATION_SCHEMA, the metadata database, it's common to indicate which rows to return by specifying database object names in the WHERE clause. For example, this statement retrieves the column names for the profile table in the cookbook database:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'cookbook' AND TABLE_NAME = 'item'; 

The database and table names are used here as data values, not as identifiers. Were you to construct this statement within a program, you would parameterize them using placeholders, not identifier quoting. For example, you might do this in Ruby:

names = dbh.select_all(                "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS                 WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?",                db_name, tbl_name)