Why Prepared Statements?
SQL injection is OWASP's top vulnerability. Prepared statements separate code (SQL structure) from data (parameters) at the protocol level, making injection impossible.
PHP
<?php
// VULNERABLE - SQL injection!
$id = $_GET["id"]; // user can send: 1 OR 1=1
$pdo->query("SELECT * FROM users WHERE id = $id");
// SAFE - prepared statement
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$_GET["id"]]);
How They Work
- Prepare: PHP sends the SQL template (with placeholders) to the database.
- The database parses and plans the query - placeholders are NOT yet filled in.
- Execute: PHP sends just the parameter values.
- The database plugs values into the plan. Values can never become SQL code.
Positional Placeholders
PHP
<?php
$stmt = $pdo->prepare(
"SELECT * FROM users WHERE email = ? AND active = ?"
);
$stmt->execute(["alice@example.com", 1]);
while ($row = $stmt->fetch()) { /* ... */ }
Named Placeholders
More readable when you have many parameters or repeat the same value:
PHP
<?php
$stmt = $pdo->prepare(
"INSERT INTO orders (user_id, total, currency, ip)
VALUES (:user_id, :total, :currency, :ip)"
);
$stmt->execute([
":user_id" => $userId,
":total" => $total,
":currency" => "USD",
":ip" => $_SERVER["REMOTE_ADDR"],
]);
bindValue vs bindParam
PHP
<?php
$stmt = $pdo->prepare("SELECT * FROM users WHERE id > :min LIMIT :limit");
// bindValue - bind a literal (or current variable value)
$stmt->bindValue(":min", 100, PDO::PARAM_INT);
// bindParam - bind a variable by REFERENCE (re-read on execute)
$limit = 10;
$stmt->bindParam(":limit", $limit, PDO::PARAM_INT);
$stmt->execute();
Use bindValue in 95% of cases. Use bindParam for output params from stored procedures.
IN() with Arrays
You can't bind an array directly. Build placeholders dynamically:
PHP
<?php
$ids = [1, 4, 7, 12];
if (!$ids) { /* handle empty list */ }
// Build "?, ?, ?, ?" with matching count
$placeholders = implode(",", array_fill(0, count($ids), "?"));
$stmt = $pdo->prepare(
"SELECT * FROM users WHERE id IN ($placeholders)"
);
$stmt->execute($ids);
$rows = $stmt->fetchAll();
Safe Dynamic SQL
When the user picks the sort column, validate against an allow-list. Never concatenate user input as an identifier:
PHP
<?php
$allowedSort = ["name", "email", "created_at"];
$allowedDir = ["ASC", "DESC"];
$sort = in_array($_GET["sort"] ?? "", $allowedSort, true)
? $_GET["sort"] : "id";
$dir = strtoupper($_GET["dir"] ?? "") === "DESC" ? "DESC" : "ASC";
// $sort and $dir are now safe to interpolate
$stmt = $pdo->prepare(
"SELECT * FROM users ORDER BY $sort $dir LIMIT ?"
);
$stmt->bindValue(1, 20, PDO::PARAM_INT);
$stmt->execute();
Reusing a Prepared Statement
PHP
<?php
// Prepare ONCE, execute MANY times - much faster
$stmt = $pdo->prepare(
"INSERT INTO log_entries (level, message) VALUES (?, ?)"
);
foreach ($events as $event) {
$stmt->execute([$event->level, $event->message]);
}
Wrap bulk inserts in a transaction
Call $pdo->beginTransaction() before the loop and $pdo->commit() after. This batches the writes and gives 10-100x speedup on InnoDB.
SQL Injection Anti-Patterns
PHP
<?php
// NEVER do these:
// 1. Direct concatenation
$pdo->query("SELECT * FROM users WHERE id = " . $_GET["id"]);
// 2. Manual quote() - error-prone, still risky
$pdo->query("SELECT * FROM users WHERE name = " . $pdo->quote($name));
// OK but use prepared statements anyway
// 3. addslashes() / mysql_real_escape_string() - WRONG TOOL
$id = addslashes($_GET["id"]); // doesn't protect against everything
$pdo->query("SELECT * FROM users WHERE id = '$id'");
// 4. Trusting numeric casts blindly
$pdo->query("SELECT * FROM users WHERE id = " . (int)$_GET["id"]);
// safer but still mixes code and data - just use a placeholder