Rectangle 27 0

php Are PDO prepared statements sufficient to prevent SQL injection?


$dbh = new PDO("blahblah");

$tableToUse = $_GET['userTable'];
$allowedTables = array('users','admins','moderators');
if (!in_array($tableToUse,$allowedTables))    
 $tableToUse = 'users';

$stmt = $dbh->prepare('SELECT * FROM ' . $tableToUse . ' where username = :username');
$stmt->execute( array(':username' => $_REQUEST['username']) );
$stmt = $dbh->prepare('SELECT * FROM foo ORDER BY :userSuppliedData');

@RobForrest yes you are missing :). The data you bind only works for DDL (Data Definition Language). You need those quotes and proper escaping. Placing quotes for other parts of the query breaks it with a high probability. For example, SELECT * FROM 'table' can be wrong as it should be SELECT * FROM `table` or without any backsticks. Then some things like ORDER BY DESC where DESC comes from the user can't be simply escaped. So, practical scenarios are rather unlimited.

Am I missing something here but isn't the whole point of prepared statements to avoid treating sql like a string? Wouldn't something like $dbh->prepare('SELECT * FROM :tableToUse where username = :username'); get around your problem?

I wonder how 6 people could upvote a comment proposing a plainly wrong use of a prepared statement. Had they even tried it once, they'd have discovered right away that using named parameter in place of a table name will not work.

It depends on whether you allow user input to be placed within the query itself. For example:

No, they are not always.

Note: you can't use PDO to bind data that goes outside of DDL (Data Definition Language), i.e. this does not work:

The reason why the above does not work is because DESC and ASC are not data. PDO can only escape for data. Secondly, you can't even put ' quotes around it. The only way to allow user chosen sorting is to manually filter and check that it's either DESC or ASC.

You should never use a query string/POST body to pick the table to use. If you don't have models, at least use a switch to derive the table name.

would be vulnerable to SQL injections and using prepared statements in this example won't work, because the user input is used as an identifier, not as data. The right answer here would be to use some sort of filtering/validation like:

Note
Rectangle 27 0

php Are PDO prepared statements sufficient to prevent SQL injection?


"Never" is way overstating it, to the point of being misleading. If you are using prepared statements incorrectly, it's not much better than not using them at all. (Of course, a "prepared statement" that has had user input injected into it defeats the purpose...but i've actually seen it done. And prepared statements can't handle identifiers (table names etc) as parameters.) Add to that, some of the PDO drivers emulate prepared statements, and there's room for them to do so incorrectly (for instance, by half-assedly parsing the SQL). Short version: never assume it is that easy.

Parameterised queries work by sending the code and the data separately, so it would never be possible to find a hole in that.

Yes, it is sufficient. The way injection type attacks work, is by somehow getting an interpreter (The database) to evaluate something, that should have been data, as if it was code. This is only possible if you mix code and data in the same medium (Eg. when you construct a query as a string).

You can still be vulnerable to other injection-type attacks though. For example, if you use the data in a HTML-page, you could be subject to XSS type attacks.

Note
Rectangle 27 0

php Are PDO prepared statements sufficient to prevent SQL injection?


' + (SELECT UserName + '_' + Password FROM Users LIMIT 1) + '

2nd order injection means data has been cycled through the database once before being included in a query, and is much harder to pull off. AFAIK, you almost never see real engineered 2nd order attacks, as it is usually easier for attackers to social-engineer their way in, but you sometimes have 2nd order bugs crop up because of extra benign ' characters or similar.

@troelskn that must be where the developer is the source of untrustworthy data

Ah, yes. But what about third order injection. Have to be aware of those.

If ALL your queries are parametrized, you're also protected against 2nd order injection. 1st order injection is forgetting that user data is untrustworthy. 2nd order injection is forgetting that database data is untrustworthy (because it came from the user originally).

If there are no other restrictions on the username, a prepared statement would still make sure that the above embedded query doesn't execute at the time of insert, and store the value correctly in the database. However, imagine that later the application retrieves your username from the database, and uses string concatenation to include that value a new query. You might get to see someone else's password. Since the first few names in users table tend to be admins, you may have also just given away the farm. (Also note: this is one more reason not to store passwords in plain text!)

Prepared statements / parameterized queries are generally sufficient to prevent 1st order injection on that statement*. If you use un-checked dynamic sql anywhere else in your application you are still vulnerable to 2nd order injection.

That's interesting. I wasn't aware of 1st order vs. 2nd order. Can you elaborate a little more on how 2nd order works?

We see, then, that prepared statements are enough for a single query, but by themselves they are not sufficient to protect against sql injection attacks throughout an entire application, because they lack a mechanism to enforce that all access to a database within the application uses safe code. However, used as part of good application design which may include practices such as code review or static analysis, or use of an ORM, data layer, or service layer that limits dynamic sql prepared statements are the primary tool for solving the Sql Injection problem. If you follow good application design principles, such that your data access is separated from the rest of your program, it becomes easy to enforce or audit that every query correctly uses parameterization. In this case, sql injection (both first and second order) is completely prevented.

You can accomplish a 2nd order injection attack when you can cause a value to be stored in a database that is later used as a literal in a query. As an example, let's say you enter the following information as your new username when creating an account on a web site (assuming MySQL DB for this question):

Note
Rectangle 27 0

php Are PDO prepared statements sufficient to prevent SQL injection?


$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();
$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 /*"));
$pdo->query('SET NAMES gbk');
$pdo->query('SET NAMES gbk');
$var = "\xbf\x27 OR 1=1 /*";
$query = 'SELECT * FROM test WHERE name = ? LIMIT 1';
$stmt = $pdo->prepare($query);
$stmt->execute(array($var));
$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 /*"));
NO_BACKSLASH_ESCAPES
SELECT * FROM test WHERE name = '' OR 1=1 /*' LIMIT 1
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");
mysql_set_charset('gbk');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

Selecting a Character Set

  • Don't use a vulnerable character set for connection encoding (you only use utf8 / latin1 / ascii / etc)

@nicogawenda: that was a different bug. Prior to 5.0.22, mysql_real_escape_string wouldn't properly handle cases where the connection was properly set to BIG5/GBK. So actually even calling mysql_set_charset() on mysql < 5.0.22 would be vulnerable to this bug! So no, this post is still applicable to 5.0.22 (because mysql_real_escape_string is only charset away to calls from mysql_set_charset(), which is what this post is talking about bypassing)...

@progfa Whether it does or not you should always validate your input on the server before doing anything with user data.

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 (albeit not with PDO).

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 insteadbut 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.

Because MySQLi does true prepared statements all the time.

Because the server's expecting utf8...

Because we've properly set the character set so the client and the server match.

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, which should be used instead of SET NAMES...

Congratulations, you just successfully attacked a program using PDO Prepared Statements...

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.

I am finding that the simple fix PDO::ATTR_EMULATE_PREPARES = false can cause dropped DB connections when preparing some SQL statements. I've not identified the exact circumstances yet (it happens to me when using lots of joins and selecting from a custom DB function) but it is something to be aware of when using this option.

I was scrolling down and wondering about who will appear under that great post, and as you guess.. :)

I wasn't aware of emulated prepared statements. Great work +1

I've been slowly working on a patch to change the default to not emulate prepares for a future version of PHP. The problem that I'm running into is that a LOT of tests break when I do that. One problem is that emulated prepares will only throw syntax errors on execute, but true prepares will throw errors on prepare. So that can cause issues (and is part of the reason tests are borking).

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.

In certain circumstances, that will return more than 1 row. Let's dissect what's going on here:

Now, it's very important to note the use of SET NAMES here. This sets the character set ON THE SERVER. There is another way of doing it, but we'll get there soon enough.

Now, it's worth noting that you can prevent this by disabling emulated prepared statements:

So, let's start off by showing the attack...

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.

The following examples are safe:

The important thing to realize here is that PDO by default does NOT do true prepared statements. It emulates them (for MySQL). Therefore, PDO internally builds the query string, calling mysql_real_escape_string() (the MySQL C API function) on each bound string value.

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.

The problem here is that we didn't call the C API's mysql_set_charset() instead of SET NAMES. If we did, we'd be fine provided we are using a MySQL release since 2006.

The short answer is NO, PDO prepares will not defend you from all possible SQL-Injection attacks. For certain obscure edge-cases.

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:

This part is just a formality, but here's the rendered query:

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).

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...

Which is exactly what the attack requires.

Note
Rectangle 27 0

php Are PDO prepared statements sufficient to prevent SQL injection?


$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();
$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 /*"));
$pdo->query('SET NAMES gbk');
$pdo->query('SET NAMES gbk');
$var = "\xbf\x27 OR 1=1 /*";
$query = 'SELECT * FROM test WHERE name = ? LIMIT 1';
$stmt = $pdo->prepare($query);
$stmt->execute(array($var));
$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 /*"));
NO_BACKSLASH_ESCAPES
SELECT * FROM test WHERE name = '' OR 1=1 /*' LIMIT 1
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");
mysql_set_charset('gbk');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

Selecting a Character Set

  • Don't use a vulnerable character set for connection encoding (you only use utf8 / latin1 / ascii / etc)

@nicogawenda: that was a different bug. Prior to 5.0.22, mysql_real_escape_string wouldn't properly handle cases where the connection was properly set to BIG5/GBK. So actually even calling mysql_set_charset() on mysql < 5.0.22 would be vulnerable to this bug! So no, this post is still applicable to 5.0.22 (because mysql_real_escape_string is only charset away to calls from mysql_set_charset(), which is what this post is talking about bypassing)...

@progfa Whether it does or not you should always validate your input on the server before doing anything with user data.

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 (albeit not with PDO).

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 insteadbut 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.

Because MySQLi does true prepared statements all the time.

Because the server's expecting utf8...

Because we've properly set the character set so the client and the server match.

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, which should be used instead of SET NAMES...

Congratulations, you just successfully attacked a program using PDO Prepared Statements...

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.

I am finding that the simple fix PDO::ATTR_EMULATE_PREPARES = false can cause dropped DB connections when preparing some SQL statements. I've not identified the exact circumstances yet (it happens to me when using lots of joins and selecting from a custom DB function) but it is something to be aware of when using this option.

I've been slowly working on a patch to change the default to not emulate prepares for a future version of PHP. The problem that I'm running into is that a LOT of tests break when I do that. One problem is that emulated prepares will only throw syntax errors on execute, but true prepares will throw errors on prepare. So that can cause issues (and is part of the reason tests are borking).

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.

In certain circumstances, that will return more than 1 row. Let's dissect what's going on here:

Now, it's very important to note the use of SET NAMES here. This sets the character set ON THE SERVER. There is another way of doing it, but we'll get there soon enough.

Now, it's worth noting that you can prevent this by disabling emulated prepared statements:

So, let's start off by showing the attack...

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.

The following examples are safe:

The important thing to realize here is that PDO by default does NOT do true prepared statements. It emulates them (for MySQL). Therefore, PDO internally builds the query string, calling mysql_real_escape_string() (the MySQL C API function) on each bound string value.

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.

The problem here is that we didn't call the C API's mysql_set_charset() instead of SET NAMES. If we did, we'd be fine provided we are using a MySQL release since 2006.

The short answer is NO, PDO prepares will not defend you from all possible SQL-Injection attacks. For certain obscure edge-cases.

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:

This part is just a formality, but here's the rendered query:

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).

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...

Which is exactly what the attack requires.

Note
Rectangle 27 0

php Are PDO prepared statements sufficient to prevent SQL injection?


' + (SELECT UserName + '_' + Password FROM Users LIMIT 1) + '

2nd order injection means data has been cycled through the database once before being included in a query, and is much harder to pull off. AFAIK, you almost never see real engineered 2nd order attacks, as it is usually easier for attackers to social-engineer their way in, but you sometimes have 2nd order bugs crop up because of extra benign ' characters or similar.

@troelskn that must be where the developer is the source of untrustworthy data

Ah, yes. But what about third order injection. Have to be aware of those.

If ALL your queries are parametrized, you're also protected against 2nd order injection. 1st order injection is forgetting that user data is untrustworthy. 2nd order injection is forgetting that database data is untrustworthy (because it came from the user originally).

If there are no other restrictions on the username, a prepared statement would still make sure that the above embedded query doesn't execute at the time of insert, and store the value correctly in the database. However, imagine that later the application retrieves your username from the database, and uses string concatenation to include that value a new query. You might get to see someone else's password. Since the first few names in users table tend to be admins, you may have also just given away the farm. (Also note: this is one more reason not to store passwords in plain text!)

Prepared statements / parameterized queries are generally sufficient to prevent 1st order injection on that statement*. If you use un-checked dynamic sql anywhere else in your application you are still vulnerable to 2nd order injection.

That's interesting. I wasn't aware of 1st order vs. 2nd order. Can you elaborate a little more on how 2nd order works?

We see, then, that prepared statements are enough for a single query, but by themselves they are not sufficient to protect against sql injection attacks throughout an entire application, because they lack a mechanism to enforce that all access to a database within the application uses safe code. However, used as part of good application design which may include practices such as code review or static analysis, or use of an ORM, data layer, or service layer that limits dynamic sql prepared statements are the primary tool for solving the Sql Injection problem. If you follow good application design principles, such that your data access is separated from the rest of your program, it becomes easy to enforce or audit that every query correctly uses parameterization. In this case, sql injection (both first and second order) is completely prevented.

You can accomplish a 2nd order injection attack when you can cause a value to be stored in a database that is later used as a literal in a query. As an example, let's say you enter the following information as your new username when creating an account on a web site (assuming MySQL DB for this question):

Note
Rectangle 27 0

php Are PDO prepared statements sufficient to prevent SQL injection?


"Never" is way overstating it, to the point of being misleading. If you are using prepared statements incorrectly, it's not much better than not using them at all. (Of course, a "prepared statement" that has had user input injected into it defeats the purpose...but i've actually seen it done. And prepared statements can't handle identifiers (table names etc) as parameters.) Add to that, some of the PDO drivers emulate prepared statements, and there's room for them to do so incorrectly (for instance, by half-assedly parsing the SQL). Short version: never assume it is that easy.

Parameterised queries work by sending the code and the data separately, so it would never be possible to find a hole in that.

Yes, it is sufficient. The way injection type attacks work, is by somehow getting an interpreter (The database) to evaluate something, that should have been data, as if it was code. This is only possible if you mix code and data in the same medium (Eg. when you construct a query as a string).

You can still be vulnerable to other injection-type attacks though. For example, if you use the data in a HTML-page, you could be subject to XSS type attacks.

Note
Rectangle 27 0

php Are PDO prepared statements sufficient to prevent SQL injection?


$dbh = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Also note that that only is about the database side of the things you would still have to watch yourself when displaying the data. E.g. by using htmlspecialchars() again with the correct encoding and quoting style.

Another thing that always should be done it set the correct encoding of the database:

No this is not enough (in some specific cases)! By default PDO uses emulated prepared statements when using MySQL as a database driver. You should always disable emulated prepared statements when using MySQL and PDO:

Note
Rectangle 27 0

php Are PDO prepared statements sufficient to prevent SQL injection?


' + (SELECT UserName + '_' + Password FROM Users LIMIT 1) + '

2nd order injection means data has been cycled through the database once before being included in a query, and is much harder to pull off. AFAIK, you almost never see real engineered 2nd order attacks, as it is usually easier for attackers to social-engineer their way in, but you sometimes have 2nd order bugs crop up because of extra benign ' characters or similar.

@troelskn that must be where the developer is the source of untrustworthy data

Ah, yes. But what about third order injection. Have to be aware of those.

If ALL your queries are parametrized, you're also protected against 2nd order injection. 1st order injection is forgetting that user data is untrustworthy. 2nd order injection is forgetting that database data is untrustworthy (because it came from the user originally).

If there are no other restrictions on the username, a prepared statement would still make sure that the above embedded query doesn't execute at the time of insert, and store the value correctly in the database. However, imagine that later the application retrieves your username from the database, and uses string concatenation to include that value a new query. You might get to see someone else's password. Since the first few names in users table tend to be admins, you may have also just given away the farm. (Also note: this is one more reason not to store passwords in plain text!)

Prepared statements / parameterized queries are generally sufficient to prevent 1st order injection on that statement*. If you use un-checked dynamic sql anywhere else in your application you are still vulnerable to 2nd order injection.

That's interesting. I wasn't aware of 1st order vs. 2nd order. Can you elaborate a little more on how 2nd order works?

We see, then, that prepared statements are enough for a single query, but by themselves they are not sufficient to protect against sql injection attacks throughout an entire application, because they lack a mechanism to enforce that all access to a database within the application uses safe code. However, used as part of good application design which may include practices such as code review or static analysis, or use of an ORM, data layer, or service layer that limits dynamic sql prepared statements are the primary tool for solving the Sql Injection problem. If you follow good application design principles, such that your data access is separated from the rest of your program, it becomes easy to enforce or audit that every query correctly uses parameterization. In this case, sql injection (both first and second order) is completely prevented.

You can accomplish a 2nd order injection attack when you can cause a value to be stored in a database that is later used as a literal in a query. As an example, let's say you enter the following information as your new username when creating an account on a web site (assuming MySQL DB for this question):

Note
Rectangle 27 0

php Are PDO prepared statements sufficient to prevent SQL injection?


$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();
$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 /*"));
$pdo->query('SET NAMES gbk');
$pdo->query('SET NAMES gbk');
$var = "\xbf\x27 OR 1=1 /*";
$query = 'SELECT * FROM test WHERE name = ? LIMIT 1';
$stmt = $pdo->prepare($query);
$stmt->execute(array($var));
$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 /*"));
NO_BACKSLASH_ESCAPES
SELECT * FROM test WHERE name = '' OR 1=1 /*' LIMIT 1
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");
mysql_set_charset('gbk');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

Selecting a Character Set

  • Don't use a vulnerable character set for connection encoding (you only use utf8 / latin1 / ascii / etc)

@nicogawenda: that was a different bug. Prior to 5.0.22, mysql_real_escape_string wouldn't properly handle cases where the connection was properly set to BIG5/GBK. So actually even calling mysql_set_charset() on mysql < 5.0.22 would be vulnerable to this bug! So no, this post is still applicable to 5.0.22 (because mysql_real_escape_string is only charset away to calls from mysql_set_charset(), which is what this post is talking about bypassing)...

@progfa Whether it does or not you should always validate your input on the server before doing anything with user data.

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 (albeit not with PDO).

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 insteadbut 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.

Because MySQLi does true prepared statements all the time.

Because the server's expecting utf8...

Because we've properly set the character set so the client and the server match.

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, which should be used instead of SET NAMES...

Congratulations, you just successfully attacked a program using PDO Prepared Statements...

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.

I am finding that the simple fix PDO::ATTR_EMULATE_PREPARES = false can cause dropped DB connections when preparing some SQL statements. I've not identified the exact circumstances yet (it happens to me when using lots of joins and selecting from a custom DB function) but it is something to be aware of when using this option.

I've been slowly working on a patch to change the default to not emulate prepares for a future version of PHP. The problem that I'm running into is that a LOT of tests break when I do that. One problem is that emulated prepares will only throw syntax errors on execute, but true prepares will throw errors on prepare. So that can cause issues (and is part of the reason tests are borking).

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.

In certain circumstances, that will return more than 1 row. Let's dissect what's going on here:

Now, it's very important to note the use of SET NAMES here. This sets the character set ON THE SERVER. There is another way of doing it, but we'll get there soon enough.

Now, it's worth noting that you can prevent this by disabling emulated prepared statements:

So, let's start off by showing the attack...

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.

The following examples are safe:

The important thing to realize here is that PDO by default does NOT do true prepared statements. It emulates them (for MySQL). Therefore, PDO internally builds the query string, calling mysql_real_escape_string() (the MySQL C API function) on each bound string value.

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.

The problem here is that we didn't call the C API's mysql_set_charset() instead of SET NAMES. If we did, we'd be fine provided we are using a MySQL release since 2006.

The short answer is NO, PDO prepares will not defend you from all possible SQL-Injection attacks. For certain obscure edge-cases.

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:

This part is just a formality, but here's the rendered query:

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).

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...

Which is exactly what the attack requires.

Note