When is quoting necessary in prepared statements of pdo in PHP?

Question

It's from the comment under this answer,but I really don't figure out what he means:

http://stackoverflow.com/questions/2211919/how-to-change-from-mysql-to-pdo-using-prepared-statements-in-php/2211992#2211992

Answer 1

When using prepared statements you never have to escape/quote a string parameter for the dbms (parser) manually.
The comment relates to http://docs.php.net/pdo.prepared-statements:

Prepared statements are so useful that they are the only feature that PDO will emulate for drivers that don't support them.

I.e. if the driver doesn't support prepared statements PDO will still expose the prepare statements part of api and "translate" them to sql statements containing the parameters (e.g. INSERT INTO foo (x,y,z) values(1,2,3)). But it will do so transparently, i.e. it will automagically handle the quoting.

Answer 2

Prepared statements are prepared because you create markers for PDO to insert values, and these values can be named (for example, :accountId, :url) where PDO will find the named marker, or positional (specifically, a question mark (?)) where PDO will insert the values in the order the markers were placed.

eg:

$query = "SELECT user_id FROM users WHERE username = ?";
$statement = $pdo->prepare($query);
$statement->execute(array("John Smith"));

Notice the distinct lack of named parameters (specifically, using a ? instead of :username), and the positional style is used instead. It is purely a personal choice to use one or the other, although I find using named parameters is clearer when debugging.

Anyways. What this means is you do not have to quote if you are using prepared statements, and you do not have to worry about SQL injection when using prepared statements.

Now, what is really happening is PDO is asking the database driver (MySQL, PostgreSQL, MS SQL, Oracle, etc) to prepare the statement, but if the database driver cannot prepare it, PDO will simulate that feature. This where things start to get confusing, but you can safely forget it and just remember to use prepared statements with parameters.