SQL Standard to Escape Column Names

escaping column name as query parameter

In a parameterized query, parameters can only be used to supply column values, not column names. Therefore, you need to use string manipulation to insert the column name and then use a parameter to specify the value. For example, with Python3:

column_name = 'param1'
column_value = 'param2'
cursor.execute(f"UPDATE ttt SET status = 'Good' WHERE [{column_name}] = %s", (column_value,))

In Python2

column_name = 'param1'
column_value = 'param2'
sql = "UPDATE ttt SET status = 'Good' WHERE [{}] = %s".format(column_name)
cursor.execute(sql, (column_value,))

How to deal with SQL column names that look like SQL keywords?

Wrap the column name in brackets like so, from becomes [from].

select [from] from table;

It is also possible to use the following (useful when querying multiple tables):

select table.[from] from table;

Escaping column names in PDO statements

The ANSI standard way of doing a delimited identifier is:

SELECT "field1" ...

and if there's a " in the name, double it:

SELECT "some""thing" ...

Unfortunately this doesn't work in MySQL with the default settings, because MySQL prefers to think double quotes are an alternative to single quotes for string literals. In this case you have to use backticks (as outlined by Björn) and backslash-escaping.

To do backslash escaping correctly, you would need mysql_real_escape_string, because it's character-set-dependent. But the point is moot, because neither mysql_real_escape_string nor addslashes escape the backquote character. If you can be sure there will never be non-ASCII characters in the column names you can get away with just manually backslash-escaping the ` and \ characters.

Either way, this isn't compatible with other databases. You can tell MySQL to allow the ANSI syntax by setting the config option ANSI_QUOTES. Similarly, SQL Server also chokes on double quotes by default; it uses yet another syntax, namely square brackets. Again, you can configure it to support the ANSI syntax with the ‘quoted_identifier’ option.

Summary: if you only need MySQL compatibility:

a. use backquotes and disallow the backquote, backslash and nul character in names because escaping them is unreliable

If you need cross-DBMS compatibility, either:

b. use double quotes and require MySQL/SQL-Server users to change the configuration appropriately. Disallow double-quote characters in the name (as Oracle can't handle them even escaped). Or,

c. have a setting for MySQL vs SQL Server vs Others, and produce either the backquote, square bracket, or double-quote syntax depending on that. Disallow both double-quotes and backslash/backquote/nul.

This is something you'd hope the data access layer would have a function for, but PDO doesn't.

Summary of the summary: arbitrary column names are a problem, best avoided if you can help it.

Summary of the summary of the summary: gnnnnnnnnnnnh.

How to escape column names with hyphen in Spark SQL

Backticks (`) appear to work, so

val newTable = sqlContext.sql("select `column-1` from myDF")

should do the trick, at least in Spark v1.3.x.

Escaping keyword-like column names in Postgres

Simply enclose year in double quotes to stop it being interpreted as a keyword:

INSERT INTO table (id, name, "year") VALUES ( ... );

From the documentation:

There is a second kind of identifier: the delimited identifier or
quoted identifier. It is formed by enclosing an arbitrary sequence of
characters in double-quotes ("). A delimited identifier is always an
identifier, never a key word. So "select" could be used to refer to a
column or table named "select", whereas an unquoted select would be
taken as a key word and would therefore provoke a parse error when
used where a table or column name is expected.

SQL column name is same as Its function name

Escape in square brackets the column names which are coincident with SQL Server functions:

SELECT PersonNumber, [Left], [Right], PhotoNumbr
FROM Person;

For future reference, do not name your columns using keyword or function names.

---

Related Topics