mysql_real_escape_string() versus Prepared Statements

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”. In MySQL there are two ways to change the character set, you can do it by changing in MySQL configuration file (my.cnf) by doing:



Or you can change on a per-connection basis, which is a common practice done by people without admin level access to the server via the following query:



The problem with the latter, is that while it most certainly modified the charset it didn’t let the escaping facilities know about it. Which means that mysql_real_escape_string() still works on the basis of the default charset, which if set to latin1 (common default) will make the function work in a manner identical to addslashes() for our purposes. Another word, 0xbf27 will be converted to 0xbf5c27 facilitating the SQL injection. Here is a brief proof of concept.



So what can you do? The solution is to use prepared statements, which are supported by nearly all PHP database extensions with the notable exceptions of MySQL (ext/mysql) and SQLite2 (ext/sqlite). So, to be on the safe side, I'd recommend using the PDO interface to talks with those databases or in the case of MySQL using the newer MySQLi (ext/mysqli) extension. Those interfaces provide prepared statement support, which allows for separation between query structure and the query parameters. It should be noted that while PDO does emulated prepared statements for older versions of MySQL that do not support them natively, emulation is still prone to the same kind of issues demonstrated here and in Chris’ article. Therefore for security reasons you should definitely consider upgrading to a more modern version of MySQL and SQLite (SQLite 3).