A cargo cult prepared statement


It is often can be seen that a PDO prepared statement is called this way:

$stmt $pdo->prepare("SELECT something FROM table WHERE username = '$name'");

this is essentially a cargo cult code: it just mimics a real prepared statement, while being called this way it never protects your data - just like a straw plane never flies.

There is no magical prepared statement that will let you run a query the usual way but somehow make it safe. The query and the data must be prepared first (pun not intended).

The idea behind the prepared statement is to substitute every data variable with a placeholder mark. While the actual variable being sent separately, through the execute() call. This way all data will be treated safely, and neither an SQL injection or a syntax error can ever be caused by the data used in the query.

So the correct way to run a prepared statement is

$stmt $pdo->prepare("SELECT something FROM table WHERE username = ?");

Other examples can be found on the PDO examples page

On the other hand, there are overzealous prepared statements, which are used for no purpose, when there are no placeholders in the query, like this

$stmt $pdo->prepare("SELECT something FROM table ORDER BY username");

in this case there is no point in using a prepared statement, so the whole affair could be written in a single line:

$stmt $pdo->query("SELECT something FROM table ORDER BY username");

or even along with getting the data right away:

$data $pdo->query("SELECT something FROM table ORDER BY username")->fetchAll();

Related articles: