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?
mysql_real_escape_string ALONE can prevent nothing. Moreover, this function has nothing to do with injections at all. Whenever you need escaping, you need it despite of "security", but just because it is required by SQL syntax.
This extension was deprecated in PHP 5.5. 0, and it was removed in PHP 7.0.
It does not prevent other injections like HTML injection or Cross-Site Scripting (XSS).
mysql_real_escape_string is safe to use if used properly (ie, everywhere you're inserting PHP variables into your queries), but as has been pointed out in the comments it's not the only thing you need to worry about. For example, HTML markup could be inserted into your DB and used for Cross Site Scripting attacks.
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.
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()
...
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).
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.
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.
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.
If you:
mysql_set_charset()
/ $mysqli->set_charset()
/ PDO's DSN charset parameter (in PHP ≥ 5.3.6)OR
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";
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With