Are PDO prepared statements sufficient to prevent SQL injection?
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
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).
How does PHP PDO's prepared statements prevent sql injection? What are other benefits of using PDO? Does using PDO reduce efficiency?
Well, at second glance your question looks more complex to be answered with just one link
How does php pdo's prepared statements prevent sql injection?
How can prepared statements protect from SQL injection attacks?
What are other pros/cons of using PDO?
Most interesting question.
A greatest PDO disadvantage is: it is peddled and propagated a silver bullet, another idol to worship.
While without understanding it will do no good at all, like any other tool.
PDO has some key features like
- Database abstraction. It's a myth, as it doesn't alter the SQL syntax itself. And you simply can't use mysql autoincremented ids with Postgre. Not to mention the fact that switching database drivers is not among frequent developer's decisions.
- Placeholders support, implementing native prepared statements or emulating them. Good approach but very limited one. There are lack of necessary placeholder types, like identifier or SET placeholder.
- a helper method to get all the records into array without writing a loop. Only one. When you need at least 4 to make your work sensible and less boring.
Does using PDO reduce efficiency?
Again, it is not PDO, but prepared statements that reduces efficiency.
It depends on the network latency between the db server and your application but you may count it negligible for the most real world cases.
Is this PDO code safe enough from SQL injection?
If you use only prepare statments as in your code above you are secure. There are AFIK no other posibilities to hack your site with SQL injections.
The prepare statments encupulates the data from the commands so can no content be executed as part of a SQL statment.
PDO and SQL Injection concerns
This is safe because your code sets $id
to a literal, constant value. There is no way an untrusted value can be used.
$id = 123;
$db->query("SELECT * FROM products WHERE id LIKE $id");
This is safe because doing a type-casting removes any possibility of special characters that could cause a problem with respect to SQL injection. A plain integer is safe.
$id = (int) $_GET['id'];
$db->query("SELECT * FROM products WHERE id LIKE $id");
But the fact remains that once you use different methods of writing queries, sometimes using variable expansion in strings, and sometimes using bound query parameters, you make your code harder to maintain.
Consider the lifetime of this code. How confident are you that the next junior programmer who takes over support for this code will understand the risks of SQL injection well enough to judge when it's safe to use variable expansion, and when they should use bound query parameters?
It's safer to establish a coding standard that you always use bound query parameters when you want to combine a variable with an SQL query, because this coding standard is easier to document and easier to enforce. Therefore it's less likely to allow unsafe cases by accident.
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.
Is using PDO prepared statement and htmlspecialchars enough to prevent XSS and SQL injection?
You're calling prepare()
, but just calling prepare()
is not a magical way to protect from SQL injection.
You are still copying unsafe request data into your SQL query, without using parameters. This is how SQL injection happens.
The protection is to use parameters. This also requires that you use prepare()
and execute()
, but the point you should learn is that it's the parameterization that protects you, not the prepare()
.
I want you to understand this, and I would advise you not to put your code on any public web site until you do understand it.
Read the good description in the accepted answer to How can prepared statements protect from SQL injection attacks?
P.S.: You're also using htmlspecialchars()
. This is no protection against SQL injection. Using htmlspecialchars()
is helpful to protect against a different security risk, Cross-Site Scripting, but you do this when you want to echo output, not when you're writing an SQL query.
Does pg_prepare() prepared statement (not PDO) prevent SQL-Injection?
A prepared statement is safe from SQL injection because nobody can change the queryplan after it's prepared. But, if your statement is already compromised, you still suffer from SQL injection:
<?php
// how NOT to construct your SQL....
$query = 'SELECT * FROM user WHERE login=$1 and password=md5($2) LIMIT '. $_POST['limit']; -- injection!
$result = pg_prepare($dbconn, "", $query);
$result = pg_execute($dbconn, "", array($_POST["user"], $_POST["password"]));
if (pg_num_rows($result) < 1) {
die ("failure");
}
?>
Related Topics
[Php][Mysql] How to Insert Data in 2 Tables At Same Time
Update Database After Select Option Change
Apache Virtual Host Always Redirecting to /Dashboard
How to Get Next Id of Autogenerated Field in Laravel for Specific Table
Sql Get an Id from a Column Where Ids Separated by Commas
Validating Base64 Encoded Images
How to Get Only Date from Datetime in Codeigniter
How to Check, If a PHP String Contains Only English Letters and Digits
Regex Optionally Match a Pattern Multiple Times
Api to Get All the Reviews and Rating from Google for Business
Unable to Upload File Greater Than 2Mb in Laravel
Generate an N-Digit Random Number
Php How to Determine the First and Last Iteration in a Foreach Loop
Laravel - Display a Pdf File in Storage Without Forcing Download