how safe are PDO prepared statements
Strictly speaking, there's actually no escaping needed, because the parameter value is never interpolated into the query string.
The way query parameters work is that the query is sent to the database server when you called prepare()
, and parameter values are sent later, when you called execute()
. So they are kept separate from the textual form of the query. There's never an opportunity for SQL injection (provided PDO::ATTR_EMULATE_PREPARES
is false).
So yes, query parameters help you to avoid that form of security vulnerability.
Are they 100% proof against any security vulnerability? No, of course not. As you may know, a query parameter only takes the place of a single literal value in an SQL expression. You can't make a single parameter substitute for a list of values, for example:
SELECT * FROM blog WHERE userid IN ( ? );
You can't use a parameter to make table names or column names dynamic:
SELECT * FROM blog ORDER BY ?;
You can't use a parameter for any other type of SQL syntax:
SELECT EXTRACT( ? FROM datetime_column) AS variable_datetime_element FROM blog;
So there are quite a few cases where you have to manipulate the query as a string, prior to the prepare()
call. In these cases, you still need to write code carefully to avoid SQL injection.
Are PDO prepared statements sufficient to prevent SQL injection?
The short answer is YES, PDO prepares are secure enough.
Some time ago there were some bugs that have been fixed a decade ago. You can read further for the historical purposes, but if came here for a practical answer, here it is:
- as long as you're using prepared statements correctly (which means that every PHP variable must be substituted by a parameter in the query)
- as long as you're using prepared statements for the every query in the application that involves PHP variables
- as long as prepared statements are applicable (you can parameterize only SQL data literals but not keywords or identifiers)
- and as long as you are using utf8mb4 charset set in the DSN,
there is no known attack that can breach PDO prepared statements
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
andsjis
. We'll selectgbk
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
. Ingbk
, that's an invalid multibyte character; inlatin1
, it's the string¿'
. Note that inlatin1
andgbk
,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 with0xbf5c27
, which ingbk
is a two character sequence:0xbf5c
followed by0x27
. Or in other words, a valid character followed by an unescaped'
. But we're not usingaddslashes()
. 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 fromaddslashes()
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 usinglatin1
for the connection, because we never told it otherwise. We did tell the server we're usinggbk
, but the client still thinks it'slatin1
.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 thegbk
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).
Is My PDO Prepared Statement Secure If I Bind The Parameters In The Execute Function?
It's fine either way. The security benefit is from using placeholders at all. There are some cases (dynamically constructed queries) where it's beneficial to use bindParam
separately from the execute
call, and there are others where you would prefer to avoid the verbosity. Use what suits you.
In PHP, how does PDO protect from SQL injections? How do prepared statements work?
Ok, I found the answer to my question in this related question: Are PDO prepared statements sufficient to prevent SQL injection?
Thanks to Haim for pointing this Q to me.
In non technical terms, here is how prepared statements protect from injection:
When a query is sent to a data base, it's typically sent as a string. The db engine will try to parse the string and separate the data from the instructions, relying on quote marks and syntax. So if you send "SELECT * WHERE 'user submitted data' EQUALS 'table row name', the engine will be able to parse the instruction.
If you allow a user to enter what will be sent inside 'user submitted data', then they can include in this something like '..."OR IF 1=1 ERASE DATABASE'. The db engine will have trouble parsing this and will take the above as an instruction rather than a meaningless string.
The way PDO works is that it sends separately the instruction (prepare("INSERT INTO ...)) and the data. The data is sent separately, clearly understood as being data and data only. The db engine doesn't even try to analyze the content of the data string to see if it contains instructions, and any potentially damaging code snipet is not considered.
Can I fully prevent SQL injection by PDO Prepared statement without bind_param?
You're doing it right. The bound parameters are the one declared in a "prepared statement" using ?. Then they are bound using execute() with their value as a parameter to be bound to the statement.
Related Topics
Access Controller Method from Another Controller in Laravel 5
Can You Store a Function in a PHP Array
Laravel Certificate Verification Errors When Sending Tls Email
PHP Curl, Extract an Xml Response
If Statement in the Middle of Concatenation
Retrieving Multiple Result Sets With Stored Procedure in PHP/MySQLi
Is There an Equivalent For Var_Dump (PHP) in JavaScript
Get the Files Inside a Directory
How to Import a .SQL File in MySQL Database Using PHP
Convert Utf8-Characters to Iso-88591 and Back in PHP
How to Get the Classname from a Static Call in an Extended PHP Class
Laravel 5 Failed Opening Required Bootstrap/../Vendor/Autoload.PHP
How to Best Store User Information and User Login and Password
Pdo Error: " Sqlstate[Hy000]: General Error " When Updating Database