SQL injection that gets around mysql_real_escape_string()
The short answer is yes, yes there is a way to get around mysql_real_escape_string()
.
#For Very OBSCURE EDGE CASES!!!
The long answer isn't so easy. It's based off an attack demonstrated here.
The Attack
So, let's start off by showing the attack...
mysql_query('SET NAMES gbk');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");
In certain circumstances, that will return more than 1 row. Let's dissect what's going on here:
Selecting a Character Set
mysql_query('SET NAMES gbk');
For this attack to work, we need the encoding that the server's expecting on the connection both to encode
'
as in ASCII i.e.0x27
and to have some character whose final byte is an ASCII\
i.e.0x5c
. As it turns out, there are 5 such encodings supported in MySQL 5.6 by default:big5
,cp932
,gb2312
,gbk
andsjis
. We'll selectgbk
here.Now, it's very important to note the use of
SET NAMES
here. This sets the character set ON THE SERVER. If we used the call to the C API functionmysql_set_charset()
, we'd be fine (on MySQL releases since 2006). But more on why in a minute...The Payload
The payload we're going to use for this injection starts with the byte sequence
0xbf27
. Ingbk
, that's an invalid multibyte character; inlatin1
, it's the string驴'
. Note that inlatin1
andgbk
,0x27
on its own is a literal'
character.We have chosen this payload because, if we called
addslashes()
on it, we'd insert an ASCII\
i.e.0x5c
, before the'
character. So we'd wind up with0xbf5c27
, which ingbk
is a two character sequence:0xbf5c
followed by0x27
. Or in other words, a valid character followed by an unescaped'
. But we're not usingaddslashes()
. So on to the next step...mysql_real_escape_string()
The C API call to
mysql_real_escape_string()
differs fromaddslashes()
in that it knows the connection character set. So it can perform the escaping properly for the character set that the server is expecting. However, up to this point, the client thinks that we're still usinglatin1
for the connection, because we never told it otherwise. We did tell the server we're usinggbk
, but the client still thinks it'slatin1
.Therefore the call to
mysql_real_escape_string()
inserts the backslash, and we have a free hanging'
character in our "escaped" content! In fact, if we were to look at$var
in thegbk
character set, we'd see:绺? OR 1=1 /*
Which is exactly what the attack requires.
The Query
This part is just a formality, but here's the rendered query:
SELECT * FROM test WHERE name = '绺? OR 1=1 /*' LIMIT 1
Congratulations, you just successfully attacked a program using mysql_real_escape_string()
...
The Bad
It gets worse. PDO
defaults to emulating prepared statements with MySQL. That means that on the client side, it basically does a sprintf through mysql_real_escape_string()
(in the C library), which means the following will result in a successful injection:
$pdo->query('SET NAMES gbk');
$stmt = $pdo->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$stmt->execute(array("\xbf\x27 OR 1=1 /*"));
Now, it's worth noting that you can prevent this by disabling emulated prepared statements:
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
This will usually result in a true prepared statement (i.e. the data being sent over in a separate packet from the query). However, be aware that PDO will silently fallback to emulating statements that MySQL can't prepare natively: those that it can are listed in the manual, but beware to select the appropriate server version).
The Ugly
I said at the very beginning that we could have prevented all of this if we had used mysql_set_charset('gbk')
instead of SET NAMES gbk
. And that's true provided you are using a MySQL release since 2006.
If you're using an earlier MySQL release, then a bug in mysql_real_escape_string()
meant that invalid multibyte characters such as those in our payload were treated as single bytes for escaping purposes even if the client had been correctly informed of the connection encoding and so this attack would still succeed. The bug was fixed in MySQL 4.1.20, 5.0.22 and 5.1.11.
But the worst part is that PDO
didn't expose the C API for mysql_set_charset()
until 5.3.6, so in prior versions it cannot prevent this attack for every possible command!
It's now exposed as a DSN parameter.
The Saving Grace
As we said at the outset, for this attack to work the database connection must be encoded using a vulnerable character set. utf8mb4
is not vulnerable and yet can support every Unicode character: so you could elect to use that instead鈥攂ut it has only been available since MySQL 5.5.3. An alternative is utf8
, which is also not vulnerable and can support the whole of the Unicode Basic Multilingual Plane.
Alternatively, you can enable the NO_BACKSLASH_ESCAPES
SQL mode, which (amongst other things) alters the operation of mysql_real_escape_string()
. With this mode enabled, 0x27
will be replaced with 0x2727
rather than 0x5c27
and thus the escaping process cannot create valid characters in any of the vulnerable encodings where they did not exist previously (i.e. 0xbf27
is still 0xbf27
etc.)鈥攕o the server will still reject the string as invalid. However, see @eggyal's answer for a different vulnerability that can arise from using this SQL mode.
Safe Examples
The following examples are safe:
mysql_query('SET NAMES utf8');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");
Because the server's expecting utf8
...
mysql_set_charset('gbk');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");
Because we've properly set the character set so the client and the server match.
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->query('SET NAMES gbk');
$stmt = $pdo->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$stmt->execute(array("\xbf\x27 OR 1=1 /*"));
Because we've turned off emulated prepared statements.
$pdo = new PDO('mysql:host=localhost;dbname=testdb;charset=gbk', $user, $password);
$stmt = $pdo->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$stmt->execute(array("\xbf\x27 OR 1=1 /*"));
Because we've set the character set properly.
$mysqli->query('SET NAMES gbk');
$stmt = $mysqli->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$param = "\xbf\x27 OR 1=1 /*";
$stmt->bind_param('s', $param);
$stmt->execute();
Because MySQLi does true prepared statements all the time.
Wrapping Up
If you:
- Use Modern Versions of MySQL (late 5.1, all 5.5, 5.6, etc) AND
mysql_set_charset()
/$mysqli->set_charset()
/ PDO's DSN charset parameter (in PHP 鈮 5.3.6)
OR
- Don't use a vulnerable character set for connection encoding (you only use
utf8
/latin1
/ascii
/ etc)
You're 100% safe.
Otherwise, you're vulnerable even though you're using mysql_real_escape_string()
...
Consider the following query:
$iId = mysql_real_escape_string("1 OR 1=1");
$sSql = "SELECT * FROM table WHERE id = $iId";
mysql_real_escape_string()
will not protect you against this.
The fact that you use single quotes (' '
) around your variables inside your query is what protects you against this. The following is also an option:
$iId = (int)"1 OR 1=1";
$sSql = "SELECT * FROM table WHERE id = $iId";