SQL injection that gets around mysql_real_escape_string()

Question 1

Is there an SQL injection possibility even when using mysql_real_escape_string() function?

Consider this sample situation. SQL is constructed in PHP like this:

$login = mysql_real_escape_string(GetFromPost('login'));
$password = mysql_real_escape_string(GetFromPost('password'));
$sql = "SELECT * FROM table WHERE login='$login' AND password='$password'";

I have heard numerous people say to me that code like that is still dangerous and possible to hack even with mysql_real_escape_string() function used. But I cannot think of any possible exploit?

Classic injections like this:

aaa' OR 1=1 --

do not work.

Do you know of any possible injection that would get through the PHP code above?

Question 2

@ThiefMaster - I prefer not to give verbose errors like invalid user / invalid password... it tells brute force merchants that they have a valid user ID, and it's just the password they need to guess

Question 3

It's horrible from an usability point of view though. Sometimes you couldn't use your main nickname/username/email-address and forget this after some time or the site deleted your account for inactivity. Then it's extremely annoying if you continue trying passwords and maybe even get your IP blocked even though it's just your username that is invalid.

Question 4

Please, don't use mysql_* functions in new code. They are no longer maintained and the deprecation process has begun on it. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

Question 5

@machineaddict, since 5.5 (which was released recently) the mysql_* functions already produce E_DEPRECATED warning. The ext/mysql extension has not been maintained for more then 10 years. Are you really so delusional?

Question 6

@machineaddict They just removed that extension on PHP 7.0 and it is not 2050 yet.

Question 7

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 and sjis. We'll select gbk 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 function mysql_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. In gbk, that's an invalid multibyte character; in latin1, it's the string ¿'. Note that in latin1 and gbk, 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 with 0xbf5c27, which in gbk is a two character sequence: 0xbf5c followed by 0x27. Or in other words, a valid character followed by an unescaped '. But we're not using addslashes(). So on to the next step...
mysql_real_escape_string()

The C API call to mysql_real_escape_string() differs from addslashes() 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 using latin1 for the connection, because we never told it otherwise. We did tell the server we're using gbk, but the client still thinks it's latin1.

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 the gbk 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—but 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.)—so 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()...

Question 8

PDO emulating prepare statements for MySQL, really? I don't see any reason why it would do that since the driver supports it natively. No?

Question 9

It does. They say in the documentation it doesn't. But in the source code, it's plainly visible and easy to fix. I chalk it up to incompetence of the devs.

Question 10

@TheodoreR.Smith: It's not that easy to fix. I've been working on changing the default, but it fails a boat load of tests when switched. So it's a bigger change than it seems. I'm still hoping to have it finished by 5.5...

Question 11

@shadyyx: No, the vulnerability the article described was about addslashes. I based this vulnerability on that one. Try it yourself. Go get MySQL 5.0, and run this exploit and see for yourself. As far as how to put that into PUT/GET/POST, it's TRIVIAL. Input data are just byte streams. char(0xBF) is just a readable way of generating a byte. I've demoed this vulnerability live in front of multiple conferences. Trust me on this... But if you don't, try it yourself. It works...

Question 12

@shadyyx: As for passing such funkiness in $_GET... ?var=%BF%27+OR+1=1+%2F%2A in the URL, $var = $_GET['var']; in the code, and Bob's your uncle.

Question 13

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";

Question 14

But this wouldn't be a real problem, because mysql_query() doesn't execute multiple statements, no?

Question 15

@Pekka, Although the usual example is DROP TABLE, in practice the attacker is more likely to SELECT passwd FROM users. In the latter case, the second query is usually executed by use of a UNION clause.

Question 16

(int)mysql_real_escape_string - this makes no sense. It doesn't differ from (int) at all. And they will produce the same result for every input

Question 17

This is more of a misuse of the function than anything else. After all, it is named mysql_real_escape_string, not mysql_real_escape_integer . It's not mean to be used with integer fields.

Question 18

@ircmaxell, Yet the answer is totally misleading. Obviously the question is asking about the contents within the quotes. "Quotes are not there" is not the answer to this question.

Question 19

TL;DR

mysql_real_escape_string() will provide no protection whatsoever (and could furthermore munge your data) if:

MySQL's NO_BACKSLASH_ESCAPES SQL mode is enabled (which it might be, unless you explicitly select another SQL mode every time you connect); and

your SQL string literals are quoted using double-quote " characters.

This was filed as bug #72458 and has been fixed in MySQL v5.7.6 (see the section headed "The Saving Grace", below).

This is another, (perhaps less?) obscure EDGE CASE!!!

In homage to @ircmaxell's excellent answer (really, this is supposed to be flattery and not plagiarism!), I will adopt his format:

The Attack

Starting off with a demonstration...

mysql_query('SET SQL_MODE="NO_BACKSLASH_ESCAPES"'); // could already be set
$var = mysql_real_escape_string('" OR 1=1 -- ');
mysql_query('SELECT * FROM test WHERE name = "'.$var.'" LIMIT 1');

This will return all records from the test table. A dissection:

Selecting an SQL Mode
```
mysql_query('SET SQL_MODE="NO_BACKSLASH_ESCAPES"');
```
As documented under String Literals:
There are several ways to include quote characters within a string:
- A "'" inside a string quoted with "'" may be written as "''".
- A """ inside a string quoted with """ may be written as """".
- Precede the quote character by an escape character ("\").
- A "'" inside a string quoted with """ needs no special treatment and need not be doubled or escaped. In the same way, """ inside a string quoted with "'" needs no special treatment.
If the server's SQL mode includes NO_BACKSLASH_ESCAPES, then the third of these options—which is the usual approach adopted by mysql_real_escape_string()—is not available: one of the first two options must be used instead. Note that the effect of the fourth bullet is that one must necessarily know the character that will be used to quote the literal in order to avoid munging one's data.
The Payload
```
" OR 1=1 -- 
```
The payload initiates this injection quite literally with the " character. No particular encoding. No special characters. No weird bytes.
mysql_real_escape_string()
```
$var = mysql_real_escape_string('" OR 1=1 -- ');
```
Fortunately, mysql_real_escape_string() does check the SQL mode and adjust its behaviour accordingly. See libmysql.c:
```
ulong STDCALL
mysql_real_escape_string(MYSQL *mysql, char *to,const char *from,
 ulong length)
{
 if (mysql->server_status & SERVER_STATUS_NO_BACKSLASH_ESCAPES)
 return escape_quotes_for_mysql(mysql->charset, to, 0, from, length);
 return escape_string_for_mysql(mysql->charset, to, 0, from, length);
}
```
Thus a different underlying function, escape_quotes_for_mysql(), is invoked if the NO_BACKSLASH_ESCAPES SQL mode is in use. As mentioned above, such a function needs to know which character will be used to quote the literal in order to repeat it without causing the other quotation character from being repeated literally.

However, this function arbitrarily assumes that the string will be quoted using the single-quote ' character. See charset.c:
```
/*
 Escape apostrophes by doubling them up
// [ deletia 839-845 ]
 DESCRIPTION
 This escapes the contents of a string by doubling up any apostrophes that
 it contains. This is used when the NO_BACKSLASH_ESCAPES SQL_MODE is in
 effect on the server.
// [ deletia 852-858 ]
*/
size_t escape_quotes_for_mysql(CHARSET_INFO *charset_info,
 char *to, size_t to_length,
 const char *from, size_t length)
{
// [ deletia 865-892 ]
 if (*from == '\'')
 {
 if (to + 2 > to_end)
 {
 overflow= TRUE;
 break;
 }
 *to++= '\'';
 *to++= '\'';
 }
```
So, it leaves double-quote " characters untouched (and doubles all single-quote ' characters) irrespective of the actual character that is used to quote the literal! In our case $var remains exactly the same as the argument that was provided to mysql_real_escape_string()—it's as though no escaping has taken place at all.

The Query

mysql_query('SELECT * FROM test WHERE name = "'.$var.'" LIMIT 1');

Something of a formality, the rendered query is:

SELECT * FROM test WHERE name = "" OR 1=1 -- " LIMIT 1

As my learned friend put it: congratulations, you just successfully attacked a program using mysql_real_escape_string()...

The Bad

mysql_set_charset() cannot help, as this has nothing to do with character sets; nor can mysqli::real_escape_string(), since that's just a different wrapper around this same function.

The problem, if not already obvious, is that the call to mysql_real_escape_string() cannot know with which character the literal will be quoted, as that's left to the developer to decide at a later time. So, in NO_BACKSLASH_ESCAPES mode, there is literally no way that this function can safely escape every input for use with arbitrary quoting (at least, not without doubling characters that do not require doubling and thus munging your data).

The Ugly

It gets worse. NO_BACKSLASH_ESCAPES may not be all that uncommon in the wild owing to the necessity of its use for compatibility with standard SQL (e.g. see section 5.3 of the SQL-92 specification, namely the <quote symbol> ::= <quote><quote> grammar production and lack of any special meaning given to backslash). Furthermore, its use was explicitly recommended as a workaround to the (long since fixed) bug that ircmaxell's post describes. Who knows, some DBAs might even configure it to be on by default as means of discouraging use of incorrect escaping methods like addslashes().

Also, the SQL mode of a new connection is set by the server according to its configuration (which a SUPER user can change at any time); thus, to be certain of the server's behaviour, you must always explicitly specify your desired mode after connecting.

The Saving Grace

So long as you always explicitly set the SQL mode not to include NO_BACKSLASH_ESCAPES, or quote MySQL string literals using the single-quote character, this bug cannot rear its ugly head: respectively escape_quotes_for_mysql() will not be used, or its assumption about which quote characters require repeating will be correct.

For this reason, I recommend that anyone using NO_BACKSLASH_ESCAPES also enables ANSI_QUOTES mode, as it will force habitual use of single-quoted string literals. Note that this does not prevent SQL injection in the event that double-quoted literals happen to be used—it merely reduces the likelihood of that happening (because normal, non-malicious queries would fail).

In PDO, both its equivalent function PDO::quote() and its prepared statement emulator call upon mysql_handle_quoter()—which does exactly this: it ensures that the escaped literal is quoted in single-quotes, so you can be certain that PDO is always immune from this bug.

As of MySQL v5.7.6, this bug has been fixed. See change log:

Functionality Added or Changed

Incompatible Change: A new C API function, mysql_real_escape_string_quote(), has been implemented as a replacement for mysql_real_escape_string() because the latter function can fail to properly encode characters when the NO_BACKSLASH_ESCAPES SQL mode is enabled. In this case, mysql_real_escape_string() cannot escape quote characters except by doubling them, and to do this properly, it must know more information about the quoting context than is available. mysql_real_escape_string_quote() takes an extra argument for specifying the quoting context. For usage details, see mysql_real_escape_string_quote().

Note

Applications should be modified to use mysql_real_escape_string_quote(), instead of mysql_real_escape_string(), which now fails and produces an CR_INSECURE_API_ERR error if NO_BACKSLASH_ESCAPES is enabled.

References: See also Bug #19211994.

Safe Examples

Taken together with the bug explained by ircmaxell, the following examples are entirely safe (assuming that one is either using MySQL later than 4.1.20, 5.0.22, 5.1.11; or that one is not using a GBK/Big5 connection encoding):

mysql_set_charset($charset);
mysql_query("SET SQL_MODE=''");
$var = mysql_real_escape_string('" OR 1=1 /*');
mysql_query('SELECT * FROM test WHERE name = "'.$var.'" LIMIT 1');

...because we've explicitly selected an SQL mode that doesn't include NO_BACKSLASH_ESCAPES.

mysql_set_charset($charset);
$var = mysql_real_escape_string("' OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

...because we're quoting our string literal with single-quotes.

$stmt = $pdo->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$stmt->execute(["' OR 1=1 /*"]);

...because PDO prepared statements are immune from this vulnerability (and ircmaxell's too, provided either that you're using PHP≥5.3.6 and the character set has been correctly set in the DSN; or that prepared statement emulation has been disabled).

$var = $pdo->quote("' OR 1=1 /*");
$stmt = $pdo->query("SELECT * FROM test WHERE name = $var LIMIT 1");

...because PDO's quote() function not only escapes the literal, but also quotes it (in single-quote ' characters); note that to avoid ircmaxell's bug in this case, you must be using PHP≥5.3.6 and have correctly set the character set in the DSN.

$stmt = $mysqli->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$param = "' OR 1=1 /*";
$stmt->bind_param('s', $param);
$stmt->execute();

...because MySQLi prepared statements are safe.

Wrapping Up

Thus, if you:

use native prepared statements

OR

use MySQL v5.7.6 or later

OR

in addition to employing one of the solutions in ircmaxell's summary, use at least one of:
- PDO;
- single-quoted string literals; or
- an explicitly set SQL mode that does not include NO_BACKSLASH_ESCAPES

...then you should be completely safe (vulnerabilities outside the scope of string escaping aside).

Question 20

So, TL;DR would be like "there is a NO_BACKSLASH_ESCAPES mysql server mode which can cause an injection if you aren't using single quotes.

Question 21

People shouldn't be using " for strings in the first place. SQL says that's for identifiers. But eh...just another example of MySQL saying "screw standards, i'll do whatever i want". (Fortunately, you can include ANSI_QUOTES in the mode to fix the quoting brokenness. The open disregard of standards, though, is a bigger issue that might require more severe measures.)

Question 22

@DanAllen: my answer was a little broader, in that you can avoid this particular bug through PDO's quote() function—but prepared statements are a much safer and more appropriate way to avoid injection generally. Of course, if you have directly concatenated unescaped variables into your SQL then you are most certainly vulnerable to injection no matter what methods you use thereafter.

Question 23

@eggyall: Our system relies on the 2nd safe example above. There are errors, where mysql_real_escape_string has been omitted. Fixing those in an emergency mode seems to be the prudent path, hoping we don't get nuked before the corrections. My rationale is converting to prepared statements will be a much longer process that will have to come after. Is the reason prepared statements is safer the fact that errors don't create vulnerabilities? In other words, is correctly implemented 2nd example above is just as safe as prepared statements?

Question 24

@kittygirl - you've got it backwards. This answer says "NO_BACKSLASH_ESCAPES" is dangerous (unless you do one of the other suggestions, to avoid the danger). You show an sql_mode that includes "NO_BACKSLASH_ESCAPES". You just took an unnecessary risk - remove that.

Question 25

Well, there's nothing really that can pass through that, other than % wildcard. It could be dangerous if you were using LIKE statement as attacker could put just % as login if you don't filter that out, and would have to just bruteforce a password of any of your users. People often suggest using prepared statements to make it 100% safe, as data can't interfere with the query itself that way. But for such simple queries it probably would be more efficient to do something like $login = preg_replace('/[^a-zA-Z0-9_]/', '', $login);

Question 26

+1, but the wildcards are for LIKE clause, not simple equality.

Question 27

By what measure do you consider a simple replacement more efficient than using prepared statements? (Prepared statements always work, the library can be quickly corrected in case of attacks, doesn't expose human error [such as mis-typing the complete replace string], and have significant performance benefits if the statement is re-used.)

Question 28

@Slava: You're effectively limiting usernames and passwords to word chars only. Most people who know anything about security would consider that a bad idea, as it shrinks the search space considerably. Course they'd also consider it a bad idea to store cleartext passwords in the database, but we don't need to be compounding the problem. :)

Question 29

@Slava: I've seen that idea before; see xkcd.com/936 . Problem is, the math doesn't quite bear it out. Your example 17-char password would have like 96^17 possibilities, and that's if you forgot the umlauts and limited yourself to printable ASCII. That's about 4.5x10^33. We're talking literally a billion trillion times more work to brute force. Even an 8-char ASCII password would have 7.2x10^15 possibilities -- 3 thousand times more.

Question 30

Users just want to get shit done. Security directly opposes that in most cases, and you can safely assume it will be avoided or subverted by any means possible. People aren't going to pick a half dozen random imaginary words; they're going to pick a short line/sentence/catchphrase from their favorite book or movie or whatever, making the phrase much, much more predictable. In order to prevent them from doing such things, you'd basically have to make the server understand English and/or search a database of most known artistic works for the phrase used.

ircmaxell ircmaxell 165k36 gold badges269 silver badges316 bronze badges · Accepted Answer · 2012-08-25 02:08:52Z

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 and sjis. We'll select gbk 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 function mysql_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. In gbk, that's an invalid multibyte character; in latin1, it's the string ¿'. Note that in latin1 and gbk, 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 with 0xbf5c27, which in gbk is a two character sequence: 0xbf5c followed by 0x27. Or in other words, a valid character followed by an unescaped '. But we're not using addslashes(). So on to the next step...
mysql_real_escape_string()

The C API call to mysql_real_escape_string() differs from addslashes() 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 using latin1 for the connection, because we never told it otherwise. We did tell the server we're using gbk, but the client still thinks it's latin1.

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 the gbk 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—but 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.)—so 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()...

PDO emulating prepare statements for MySQL, really? I don't see any reason why it would do that since the driver supports it natively. No?
It does. They say in the documentation it doesn't. But in the source code, it's plainly visible and easy to fix. I chalk it up to incompetence of the devs.
@TheodoreR.Smith: It's not that easy to fix. I've been working on changing the default, but it fails a boat load of tests when switched. So it's a bigger change than it seems. I'm still hoping to have it finished by 5.5...
@shadyyx: No, the vulnerability the article described was about addslashes. I based this vulnerability on that one. Try it yourself. Go get MySQL 5.0, and run this exploit and see for yourself. As far as how to put that into PUT/GET/POST, it's TRIVIAL. Input data are just byte streams. char(0xBF) is just a readable way of generating a byte. I've demoed this vulnerability live in front of multiple conferences. Trust me on this... But if you don't, try it yourself. It works...
@shadyyx: As for passing such funkiness in $_GET... ?var=%BF%27+OR+1=1+%2F%2A in the URL, $var = $_GET['var']; in the code, and Bob's your uncle.

CollectivesTM on Stack Overflow

SQL injection that gets around mysql_real_escape_string()

4 Answers 4

The Attack

The Bad

The Ugly

The Saving Grace

Safe Examples

Wrapping Up

TL;DR

This is another, (perhaps less?) obscure EDGE CASE!!!

The Attack

The Bad

The Ugly

The Saving Grace

Functionality Added or Changed

Note

Safe Examples

Wrapping Up

Linked

Hot Network Questions

CollectivesTM on Stack Overflow

4 Answers 4

The Attack

The Bad

The Ugly

The Saving Grace

Safe Examples

Wrapping Up

TL;DR

This is another, (perhaps less?) obscure EDGE CASE!!!

The Attack

The Bad

The Ugly

The Saving Grace

Functionality Added or Changed

Note

Safe Examples

Wrapping Up

Linked

Related