Following atppp’s post about the difference between addslashes and mysql_real_escape_string from a topic on newsmth.net, I searched and found some topics and articles for on the issue, including Ilia Alshanetsky’s blog article: Ilia Alshanetsky: mysql_real_escape_string() versus Prepared Statements
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:
CODE:
[client]
default-character-set=GBK
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:
CODE:
SET CHARACTER SET ‘GBK’
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.
PHP:
$c = mysql_connect(”localhost”, “user”, “pass”);
mysql_select_db(”database”, $c);
// change our character set
mysql_query(”SET CHARACTER SET ‘gbk’”, $c);
// create demo table
mysql_query(”CREATE TABLE users (
username VARCHAR(32) PRIMARY KEY,
password VARCHAR(32)
) CHARACTER SET ‘GBK’”, $c);
mysql_query(”INSERT INTO users VALUES(’foo’,'bar’), (’baz’,'test’)”, $c);
// now the exploit code
$_POST[’username’] = chr(0xbf) . chr(0×27) . ‘ OR username = username /*’;
$_POST[’password’] = ‘anything’;
// Proper escaping, we should be safe, right?
$user = mysql_real_escape_string($_POST[’username’], $c);
$passwd = mysql_real_escape_string($_POST[’password’], $c);
$sql = “SELECT * FROM users WHERE username = ‘{$user}’ AND password = ‘{$passwd}’”;
$res = mysql_query($sql, $c);
echo mysql_num_rows($res); // will print 2, indicating that we were able to fetch all records
?>
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).
