Menu
I am currently building a query where both the field/column and value parts possibly consist of user inputted data.
The problem is escaping the fieldnames. I'm using prepared statements in order to properly escape and quote the values but when escaping the fieldnames i run into trouble.
Anyone has an idea on what the best way is to properly insert the fieldnames into the query before passing it to PDO::prepare?
443
PDO::quote Processes a string for use in a query by placing quotes around the input string as required by the underlying SQL Server database. PDO::quote will escape special characters within the input string using a quoting style appropriate to SQL Server.
Return Values ¶ If the database server successfully prepares the statement, PDO::prepare() returns a PDOStatement object. If the database server cannot successfully prepare the statement, PDO::prepare() returns false or emits PDOException (depending on error handling).
In layman's terms, PDO prepared statements work like this: Prepare an SQL query with empty values as placeholders with either a question mark or a variable name with a colon preceding it for each value. Bind values or variables to the placeholders. Execute query simultaneously.
To catch a PDOException object and handle the associated error: Wrap the call to the PDO constructor in a try block. Following the try block, include a catch block that catches the PDOException object.
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.
139
The correct answer, is
str_replace("`", "``", $fieldname) Wrong:
mysql> SELECT `col\"umn` FROM user; ERROR 1054 (42S22): Unknown column 'col\"umn' in 'field list'
Right:
mysql> SELECT `kid``s` FROM user; ERROR 1054 (42S22): Unknown column 'kid`s' in 'field list' mysql> SELECT ```column``name``` FROM user; ERROR 1054 (42S22): Unknown column '`column`name`' in 'field list'
(Note that in last example, the column name has 3 (three) extra back-ticks in it, just to show an extreme case)
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
