The Unexpected SQL Injection

Saturday, September 29th, 2007

Something every PHP developer should be reading:

The Unexpected SQL Injection – When Escaping Is Not Enough

The conclusions:

• Write properly quoted SQL:
1. Single quotes around values (string literals and numbers)
2. Backtick quotes around identifiers (databases, tables, columns, aliases)
• Properly escape the strings and numbers:
1. mysql_real_escape_string() for all values (string literals and numbers)
2. intval() for all number values and the numeric parameters of LIMIT
3. Escape wildcard/regexp metacharacters (addcslashes(‘%_’) for LIKE, and you better avoid REGEXP/RLIKE)
4. If identifiers (columns, tables or databases) or keywords (such as ASC and DESC) are referenced in the script parameters, make sure (and force) their values are chosen only as one of an explicit set of options
5. No matter what validation steps you take when processing the user input in your scripts, always do the escaping steps before issuing the query. Validation is not a substitute for escaping!

Like my rule #1 of what I like to call Defensive Coding: Don’t be implicit, be explicit. In other words, don’t try to escape things you don’t want in your strings, simply only leave everything you do want in your strings. A column name in a ORDER BY clause should only consist of A-Z, a-z and 0-9. Anything else in the string invalidates that string.