The Unexpected SQL Injection
When Escaping Is Not Enough

By Alexander Andonov ( sir(point)mordred(on)gmail )
Version 1.0
Last Modified: 9/1/2007

[TEXT] size: 30k (MD5 SUM: 5d1004bdd927b2030ce77f85981900f0)

Translations
[Belarusian]

We will look at several scenarios under which SQL injection may occur, even though mysql_real_escape_string() has been used. There are two major steps at writing SQL injection resistant code: correct validation and escaping of input and proper use of the SQL syntax. Failure to comply with any of them may lead to compromise. Many of the specific issues are already known, but no single document mentions them all.
Although the examples are built on PHP/MySQL, the same principles apply to ASP/MSSQL and other combinations of languages and databases.

Contents:

1. Introduction and rationale
2. Installation
3. Getting to mysql_real_escape_string()
4. Integer values
5. The Return of the Integer Values
6. Hi, what's your column name?
7. Do You LIKE My %WildCard%?
8. Other Databases and Other Issues
9. A Summary in Plaintext
10. About
11. References

1. Introduction and rationale

There are many papers ([1],[2],[3],[4]) and articles ([5],[6]) about SQL injection in dynamically built SQL queries (as opposed to prepared statements or stored procedures), and even more about how to protect against it ([7],[8]). And yet web programmers continue to make crucial mistakes when writing their SQL-related code; indeed there are cases not explicitly mentioned in the popular tutorials, which have to be handled with care. As the PHP documentation ([9]) on mysql_real_escape_string() says: "This function must always (with few exceptions) be used to make data safe before sending a query to MySQL.". This article attempts to enumerate the said exceptions and give a complete checkpoint list of protective measures. We will identify some common points of failure and provide the right (and in some occasions, the commonly applied wrong) solutions in hope that novice programmers and pen-testers will learn to recognise them in the