The tragic fate of mysql_real_escape_string()

Delusion
Source
Disclosure
The Right Way
Comments

Delusion

"mysql_real_escape_string() have to be used in order to make user-supplied data safe, by means of escaping dangerous characters"

Source

The very manual page, stating "If this function is not used to escape data, the query is vulnerable to SQL Injection Attacks." (formerly "This function must always (with few exceptions) be used to make data safe before sending a query to MySQL.") while both statements being complete, utter bullshit.

Disclosure

First, in brief:

- escaping has nothing to do with whatever "safety", "security" or injections.
- it doesn't make data "safe"
- it doesn't replace or delete whatever "dangerous" characters
- there are no such characters at all
- there is no such thing like "user-supplied data" (from this function's usage point of view)

A through explanation:

This honest function belongs not to security but to mere SQL formatting. Moreover - SQL strings formatting only. And it is useless for any other literal - be it number, identifier or a keyword.

Frankly, the only purpose of this function is to escape the very quotes were used to delimit a string.

And yes - properly formatted string literal would be fairly safe just as a side effect.

The Right Way

The best approach was made by PDO, either with its quote() function and faked placeholders.
The former does both quoting and escaping which makes data perfectly safe:

- in case of adding a string (or a literal that can be formatted as string - such as float number), it does full proper formatting and thus makes a literal safe (as a side effect)
- but if you try to use it for improper literal - an identifier, for example - SQL will result in an erroneous, but still safe query.

So, using complete approach like PDO::quote() would be perfectly safe.

Related articles:

• The (im)proper use of try..catch.
• Short tags