How does this PDO Code protect from SQL Injections?

Question

So I was looking into this whole PDO thing and I was reading this blog tutorial when I came across this code and the explanation being that if I use PDO with data binding - users won't be able to add SQL Injections. How does this work?

# no placeholders - ripe for SQL Injection!  
$STH = $DBH->("INSERT INTO folks (name, addr, city) values ($name, $addr, $city)");  

# unnamed placeholders  
$STH = $DBH->("INSERT INTO folks (name, addr, city) values (?, ?, ?); 

# named placeholders 
$STH = $DBH->("INSERT INTO folks (name, addr, city) value (:name, :addr, :city)");  

Here's the link to the website I got it from incase you want to read it for reference. http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/

Answer 1

(There's a bug in the 2nd line; the string isn't terminated. Add a "); to the end, and you should be ok. It's on the page you linked to as well, so its their fault. You of course also need to supply the values that'll substitute the question marks, and then actually run the query, before you get any results.)

Anyway, to the point. PDO looks for the ? or :name markers, and replaces them (in order or by name, respectively) with the values you specify. When the values are inserted into the query string, they're first processed to escape anything that could be used for injection attacks.

It's similar to using mysql_real_escape_string() (or the weaker addslashes()) on a value before using it in a query, but PDO does it automatically and is better at it.

Answer 2

PDO does much more behind the scenes than simply replacing your placeholders with the parameterized data. Database engines may accept queries in a form similar to "here is your statement, here are the placeholders, and I will tell you what goes in each placeholder". The SQL engine knows the parameters are NOT raw code to be executed but to be treated as data only.

Answer 3

Because the PDO knows how to properly insert values into the query when you use a prepared statement.

Answer 4

When you're binding a value to the placeholder, e.g.

$sth->bindValue(':name', $name, PDO::PARAM_STR);

PDO will take care of escaping it properly. Thus, SQL Injections won't work.

Answer 5

Because a prepared statement with bind parameters is a statement where the query analyze is already done, and places for string or ints can only be striongs or ints. No new analysis of the statements is done, so no given arguments can be analysed as something related to SQL, and will never be analysed as SQL.