Let's say I have code like this:
$dbh = new PDO("blahblah"); $stmt = $dbh->prepare('SELECT * FROM users where username = :username'); $stmt->execute( array(':username' => $_REQUEST['username']) );
The PDO documentation says:
The parameters to prepared statements don't need to be quoted; the driver handles it for you.
Is that truly all I need to do to avoid SQL injections? Is it really that easy?
You can assume MySQL if it makes a difference. Also, I'm really only curious about the use of prepared statements against SQL injection. In this context, I don't care about XSS or other possible vulnerabilities.
The short answer is NO, PDO prepares will not defend you from all possible SQL-Injection attacks. For certain obscure edge-cases.
I'm adapting this answer to talk about PDO...
The long answer isn't so easy. It's based off an attack demonstrated here.
So, let's start off by showing the attack...
$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));
In certain circumstances, that will return more than 1 row. Let's dissect what's going on here:
Selecting a Character Set
$pdo->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
0x5c. As it turns out, there are 5 such encodings supported in MySQL 5.6 by default:
sjis. We'll select
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.
The payload we're going to use for this injection starts with the byte sequence
gbk, that's an invalid multibyte character; in
latin1, it's the string
¿'. Note that in
0x27 on its own is a literal
We have chosen this payload because, if we called
addslashes() on it, we'd insert an ASCII
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...
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 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
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.
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 PDO Prepared Statements...
Now, it's worth noting that you can prevent this by disabling emulated prepared statements:
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 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.
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, which should be used instead of
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 (albeit not with PDO).
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
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.
You're 100% safe.
Otherwise, you're vulnerable even though you're using PDO Prepared Statements...
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).
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.
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.
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):
' + (SELECT UserName + '_' + Password FROM Users LIMIT 1) + '
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!)
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.
*It turns out that MySql/PHP are (okay, were) just dumb about handling parameters when wide characters are involved, and there is still a rare case outlined in the other highly-voted answer here that can allow injection to slip through a parameterized query.