mysql_real_escape_string() vs addslashes() vs Prepared Statements
Interesting posts by Chris and Ilia on their respective blogs.
In The addslashes() Versus mysql_real_escape_string() Debate Chris starts discussing the Google XSS exploit and then goes on to explaining about character set encoding and how one particular character set allows for this exploit:
Last month, I discussed Google's XSS Vulnerability and provided an example that demonstrates it. I was hoping to highlight why character encoding consistency is important, but apparently the addslashes() versus mysql_real_escape_string() debate continues. Demonstrating Google's XSS vulnerability was pretty easy. Demonstrating an SQL injection attack that is immune to addslashes() is a bit more involved, but still pretty straightforward.
In GBK, 0xbf27 is not a valid multi-byte character, but 0xbf5c is. Interpreted as single-byte characters, 0xbf27 is 0xbf (¿) followed by 0x27 ('), and 0xbf5c is 0xbf (¿) followed by 0x5c (\).
How does this help? If I want to attempt an SQL injection attack against a MySQL database, having single quotes escaped with a backslash is a bummer. If you're using addslashes(), however, I'm in luck. All I need to do is inject something like 0xbf27, and addslashes() modifies this to become 0xbf5c27, a valid multi-byte character followed by a single quote. In other words, I can successfully inject a single quote despite your escaping. That's because 0xbf5c is considered to be a single character, not two. Oops, there goes the backslash.
Ilia then goes onto writing in his piece mysql_real_escape_string() versus Prepared Statements he goes more into detail about the exploit.
Chris has written a compelling piece about how the use of addslashes() for string escaping in MySQL queries can lead to SQL injection through the abuse of multibyte character sets. In his example he relies on addslashes() to convert an invalid multibyte sequence into a valid one, which also has an embedded ' that is not escaped. And in an ironic twist, the function intended to protect against SQL injection is used to actually trigger it.
The problem demonstrated, actually goes a bit further, which even makes the prescribed escaping mechanism, mysql_real_escape_string() prone to the same kind of issues affecting addslashes(). The main advantage of the mysql_real_escape_string() over addslashes() lies in the fact that it takes character set into account and thus is able to determine how to properly escape the data. For example, if GBK character set is being used, it will not convert an invalid multibyte sequence 0xbf27 (¿’) into 0xbf5c27 (¿\’ or in GBK a single valid multibyte character followed by a single quote). To determine the proper escaping methodology mysql_real_escape_string() needs to know the character set used, which is normally retrieved from the database connection cursor. Herein lies the “trick”.