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 = "\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 \
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("\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 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).
I use javascript:void(0)
.
Three reasons. Encouraging the use of #
amongst a team of developers inevitably leads to some using the return value of the function called like this:
function doSomething() {
//Some code
return false;
}
But then they forget to use return doSomething()
in the onclick and just use doSomething()
.
A second reason for avoiding #
is that the final return false;
will not execute if the called function throws an error. Hence the developers have to also remember to handle any error appropriately in the called function.
A third reason is that there are cases where the onclick
event property is assigned dynamically. I prefer to be able to call a function or assign it dynamically without having to code the function specifically for one method of attachment or another. Hence my onclick
(or on anything) in HTML markup look like this:
onclick="someFunc.call(this)"
OR
onclick="someFunc.apply(this, arguments)"
Using javascript:void(0)
avoids all of the above headaches, and I haven't found any examples of a downside.
So if you're a lone developer then you can clearly make your own choice, but if you work as a team you have to either state:
Use href="#"
, make sure onclick
always contains return false;
at the end, that any called function does not throw an error and if you attach a function dynamically to the onclick
property make sure that as well as not throwing an error it returns false
.
OR
Use href="javascript:void(0)"
The second is clearly much easier to communicate.
Best Answer
The History
This was my first Stackoverflow answer. A lot has changed since, specially the deprecation and removal of the mysql API. Even if you are still on php 5.6, the mysql_* api should not be used. Now PDO or mysqli are the only options to choose. PDO is better to lots of reasons.
Are prepared statements cached across page loads?
The same prepared statement will not be used in between page loads. It has to be prepared every time. If squeezing every large millisecond matters, a stored procedure might be a good idea (assuming you have a complicated query).
For large inserts (thousands of rows) A bigger boost can probably be gained by dumping your data into a text file and loading it with LOAD DATA IN FILE . It's a lot faster than a series of inserts.
The original answer
The truth of the matter is that sometimes mysqli is faster and at other times mysql api is faster. But the difference is really really small. If you look at any of the performance tests on the web the difference is really just 10 - 20 milliseconds. The best way to boost performance is to optimize table design.
Many of the tests that 'prove' the older api to be faster conveniently forget that for maximum security mysql_real_escape_string() should be called for each variable used in the query.
Queries are cached by the server, if and only if the data on all the tables that are used in the query have remained unchanged.
Await another update with actual numbers