Are PDO prepared statements sufficient to prevent SQL injection?

Question 1

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.

Question 2

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.

The Attack

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

$pdo->query('SET NAMES gbk');
$var = "xbfx27 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 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. There is another way of doing it, but we'll get there soon enough.
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...
$stmt->execute()

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 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 PDO Prepared Statements...

The Simple Fix

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 Correct Fix

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

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

Safe Examples

The following examples are safe:

mysql_query('SET NAMES utf8');
$var = mysql_real_escape_string("xbfx27 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("xbfx27 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("xbfx27 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("xbfx27 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 = "xbfx27 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 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)

OR

Enable NO_BACKSLASH_ESCAPES SQL mode

You're 100% safe.

Otherwise, you're vulnerable even though you're using PDO Prepared Statements...

Addendum

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

Question 3

This is a more up-to-date answer to this question.

The old way of preventing multi query execution was to disable emulated prepares, however this was only applicable to the PDO::prepare() method. In newer versions of PHP (>= 5.5.21 and >= 5.6.5), a new constant has been introduced to disable this multi query execution in both PDO::prepare() and PDO::query(). (Constants aren't usually added in patch versions, but this was done due to the severity of a Drupal SQL injection attack brought about by this capability).

The new constant is PDO::MYSQL_ATTR_MULTI_STATEMENTS and must be set on object creation (as the fourth argument to the PDO constructor) - setting it on a pre-existing object with PDO::setAttribute() will not work.

$pdo = new PDO('mysql:host=_;dbname=_', '', '', [PDO::MYSQL_ATTR_MULTI_STATEMENTS => false]);

Question 4

According the the page you referenced, the Active Record class uses mysql_ functions for string-escaping. That means it's still building SQL strings up in PHP-land instead of using parametrized APIs into the database. While it may be free of known defects right now, it is still a better idea to use an API that follows a more secure design.

Question 5

The best option: do not use SQL statements that get concatenated together - use parametrized queries.

E.g. do not create something like

string stmt = "INSERT INTO dbo.MyTable(field1,field2) VALUES(" + value1 + ", " + value2 + ")"

or something like that and then try to "sanitize" it by replacing single quotes or something - you'll never catch everything, someone will always find a way around your "safe guarding".

Instead, use:

string stmt = "INSERT INTO dbo.MyTable(field1,field2) VALUES(@value1, @value2)";

and then set the parameter values before executing this INSERT statement. This is really the only reliable way to avoid SQL injection - use it!

UPDATE: how to use parametrized queries from PHP - I found something here - does that help at all?

$tsql = "INSERT INTO DateTimeTable (myDate, myTime,
                                    myDateTimeOffset, myDatetime2)
         VALUES (?, ?, ?, ?)";

$params = array(
            date("Y-m-d"), // Current date in Y-m-d format.
            "15:30:41.987", // Time as a string.
            date("c"), // Current date in ISO 8601 format.
            date("Y-m-d H:i:s.u") // Current date and time.
          );

$stmt = sqlsrv_query($conn, $tsql, $params);

So it seems you can't use "named" parameters like @value1, @value2, but instead you just use question marks ? for each parameter, and you basically just create a parameter array which you then pass into the query.

This article Accessing SQL Server Databases with PHP might also help - it has a similar sample of how to insert data using the parametrized queries.

UPDATE: after you've revealed that you're on Linux, this approach doesn't work anymore. Instead, you need to use an alternate library in PHP to call a database - something like PDO.

PDO should work both on any *nix type operating system, and against all sorts of databases, including SQL Server, and it supports parametrized queries, too:

$db = new PDO('your-connection-string-here');
$stmt = $db->prepare("SELECT priv FROM testUsers WHERE username=:username AND password=:password");
$stmt->bindParam(':username', $user);
$stmt->bindParam(':password', $pass);
$stmt->execute();

Question 6

You always need to use white-lists for stuff like table- or column names, whether you use prepared statements or the mysql escape functions.

The problem is that table names and column names are not quoted in single or double quotes, so if you use a function that specifically quotes these characters (and some more of course...), it will do nothing for your table name.

Consider the table name my_table; DELETE * FROM mysql; SELECT * FROM my_table. Nothing in this string will get escaped by mysql's escape functions but it is definitely a string you would want to check against a white-list.

Apart from that the mysql escape functions have a problem with character sets that can render them useless, so you are always better off with prepared statements.