A cargo cult prepared statement

  1. Comments

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'");
$stmt->execute(); 

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

There is no magical prepared statement that will let you run the query the usual way but somehow make it safe. The query and the date 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 is sent so a database server separately, through the execute() call. This way all data will be treated safely, and neither an SQL injection or a syntax error can be ever caused by the data.

So, the correct way to run a prepared statement is

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

Other examples can be found in the main article or PDO examples

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");
$stmt->execute(); 

in this case there is no point tin using prepared statements, so the whole statement 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: