Prepared Statements
Send the SQL and the values apart so a value can never be read as SQL: a value is never concatenated into a query.
$stmt = $pdo->prepare('SELECT id FROM users WHERE email = :email'); $stmt->execute(['email' => $email]);A prepared statement is a query you hand to the database with holes in it. The holes are called placeholders. You send the query first, then the values for the holes. The database has already decided what the query means before it ever sees a value, so a value cannot change the meaning.
Why this matters more than speed
Prepared statements are often described as faster because the database can reuse the plan. Ignore that. The reason to use them every time is SQL injection: if you glue user input into SQL with ., then input containing a quote ends the string early and the rest is read as more SQL. An email box becomes a way to run DELETE. With a placeholder the value is never part of the query text.
Two kinds of placeholder
Named placeholders are written :email, and you pass an array keyed by name. Positional placeholders are written ?, and you pass a list in order. Pick one per query; they cannot be mixed in one statement.
Shorthand and longhand
Passing an array to execute() is shorthand for calling bindValue() once per placeholder and then execute() with no arguments. They do the same thing, except that execute() sends every value as a string, while bindValue() lets you say PDO::PARAM_INT, which some drivers need for LIMIT.
The rule has no exceptions. Any value from a form, a URL, a cookie or another system goes in through a placeholder.
Example
When to use it
- Look up a user by the email address typed into a login form.
- Rewrite an older query that concatenated $_GET values into its SQL.
- Update many rows in a loop without re-parsing the query every time.
- Filter a product list by a category id that arrived in the URL.
More examples
Named, positional and bindValue
The first two are the same query written the two ways PDO allows. The third shows why the longhand still exists: execute() sends every value as a string, and with PDO::ATTR_EMULATE_PREPARES set to false, MySQL rejects a string where LIMIT expects a number. Use the array form for ordinary values and bindValue() with PDO::PARAM_INT for LIMIT and OFFSET.
<?php
declare(strict_types=1);
// Named placeholders. The array keys match the names, with or without the colon.
$stmt = $pdo->prepare('SELECT id, email FROM users WHERE email = :email AND active = :active');
$stmt->execute(['email' => $email, 'active' => 1]);
// Positional placeholders. The array is a list, in the order the ? appear.
$stmt = $pdo->prepare('SELECT id, email FROM users WHERE email = ? AND active = ?');
$stmt->execute([$email, 1]);
// Longhand. Exactly what passing an array to execute() does, with types added.
$stmt = $pdo->prepare('SELECT id, email FROM users WHERE email = :email LIMIT :max');
$stmt->bindValue(':email', $email, PDO::PARAM_STR);
$stmt->bindValue(':max', 10, PDO::PARAM_INT);
$stmt->execute();
An IN list needs one placeholder per value
This is the one place a query string is assembled at run time, and it is safe because only the number of question marks changes: no value is ever interpolated. Reach for it whenever you have a list of ids from a form of checkboxes. The early return matters, because IN () with nothing inside it is a syntax error.
<?php
declare(strict_types=1);
/**
* WHERE id IN (:ids) does not work: a placeholder is one value, not a list.
* Build as many ? as there are ids, and keep the ids out of the SQL text.
*/
function findByIds(PDO $pdo, array $ids): array
{
if ($ids === []) {
return [];
}
// Integers only, so the count of placeholders is all that varies.
$ids = array_values(array_map('intval', $ids));
$placeholders = implode(', ', array_fill(0, count($ids), '?'));
$stmt = $pdo->prepare("SELECT id, title FROM products WHERE id IN ({$placeholders})");
$stmt->execute($ids);
return $stmt->fetchAll();
}
Prepare once, execute many times
A prepared statement can be executed as many times as you like with different values, which is the one case where the speed argument is real: the query is parsed once. Use this shape for any import or bulk update. Wrap the loop in a transaction when the rows only make sense together.
<?php
declare(strict_types=1);
$rows = [
['sku' => 'A-1', 'price' => 1999],
['sku' => 'B-2', 'price' => 2499],
['sku' => 'C-3', 'price' => 999],
];
// One prepare, outside the loop. The statement is reusable.
$stmt = $pdo->prepare('UPDATE products SET price = :price WHERE sku = :sku');
foreach ($rows as $row) {
$stmt->execute(['price' => $row['price'], 'sku' => $row['sku']]);
}
Discussion