How PDO prepared statements help to prevent SQL vulnerable statements?

Question

I'm so confused or rather I'm like, soooooooooo confused with pdo prepared statements. I know that prepared statements are the best way to keep data safe from hackers.

From : How can prepared statements protect from SQL injection attacks?

We are sending program to the server first

$db->prepare("SELECT * FROM users where id=?"); where the data is substituted by some variable called "placeholder".

Note that the very same query being sent to the server, without any data in it! And then we're sending the data with the second request, totally separated from the query itself:

$db->execute($data);

query-

$query=$db->prepare("SELECT * FROM USERS WHERE username=?");
$query->execute(array($tex));
$tex=blah; DROP TABLE users;--

then it will be like - SELECT * FROM USERS WHERE username=blah; DROP TABLE users;--

how prepare statements will help me with this example above?

I'm really sorry if this question is vague to understand. Any help would be appreciated. Thanks in advance.

user2864740 user2864740 · Accepted Answer · 2015-06-20T05:44:57

The prepared statement handler will make sure the bound value is always used as valid SQL value/literal (ie. an SQL string or a number) and never as 'raw SQL text'¹.

This is why placeholders values cannot be used as identifiers such as column or table names or act as other SQL keywords; and cannot generate the vulnerable query hypothesized. Instead it is treated as the following:

WHERE username='blah; DROP TABLE users;--'
            --^ placeholder ensures valid SQL string value is used
            --  (note automatic/implicit addition of SQL quotes)

And even when binding with 'more tricky' data:

$tex = "blah'; DROP TABLE users;--";  // embedded SQL quote character

It would still be safe:

WHERE username='blah''; DROP TABLE users;--'
            --^ placeholder STILL ensures valid SQL string value is used

Thus, when using placeholders, it is impossible to generate the SQL that is vulnerable (in this way).

For SQL Injection the 'shape' of the query (which includes keywords and identifiers, but excludes values) must itself be altered by the input.

¹ Technically placeholders values can also be sent through a separate data channel (depending on adapter/driver) and thus might not even appear in the raw SQL query itself.

However a simple way to think about why placeholders are safe, or how they 'work' is:

When using placeholders the adapter ensures that the equivalent of 'sql really safe escape' and applicable quoting is always used for every bound text value - and is thus impossible for accidentally forget.

How PDO prepared statements help to prevent SQL vulnerable statements?

1 Answers