addslashes() Versus mysql_real_escape_string()

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 is 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 interpreted as a single character, not two. Oops, there goes the backslash.

I'm going to use MySQL 5.0 and PHP's mysqli extension for this demonstration. If you want to try this yourself, make sure you're using GBK. I just changed /etc/my.cnf, but that's because I'm testing locally:

[client]
default-character-set=GBK

Create a table called users:

CREATE TABLE users (
    username VARCHAR(32) CHARACTER SET GBK,
    password VARCHAR(32) CHARACTER SET GBK,
    PRIMARY KEY (username)
);

The following script mimics a situation where only addslashes() (or magic_quotes_gpc) is used to escape the data being used in a query:

<?php
 
$mysql = array();
 
$db = mysqli_init();
$db->real_connect('localhost', 'myuser', 'mypass', 'mydb');
 
/* SQL Injection Example */
$_POST['username'] = chr(0xbf) .
                     chr(0x27) .
                     ' OR username = username /*';
$_POST['password'] = 'guess';
 
$mysql['username'] = addslashes($_POST['username']);
$mysql['password'] = addslashes($_POST['password']);
 
$sql = "SELECT *
        FROM   users
        WHERE  username = '{$mysql['username']}'
        AND    password = '{$mysql['password']}'";
 
$result = $db->query($sql);
 
if ($result->num_rows) {
    /* Success */
} else {
    /* Failure */
}
 
?>

Despite the use of addslashes(), I'm able to log in successfully without knowing a valid username or password. I can simply exploit the SQL injection vulnerability.

To avoid this type of vulnerability, use mysql_real_escape_string(), prepared statements, or any of the major database abstraction libraries.

This type of attack is possible with any character encoding where there is a valid multi-byte character that ends in 0x5c, because addslashes() can be tricked into creating a valid multi-byte character instead of escaping the single quote that follows. UTF-8 does not fit this description.