A prepared statement sends the SQL and the values separately, so the values can never change the meaning of the query. This is the main defence against SQL injection (SQL Injection, PHP Prepared Statements). This lesson covers what is specific to MySQL.
The basic form
<?php
$pdo = require __DIR__ . '/db.php';
$stmt = $pdo->prepare('SELECT id, name, price FROM products WHERE category = :category AND price <= :max ORDER BY price');
$stmt->execute(['category' => 'writing', 'max' => 50]);
foreach ($stmt as $row) {
echo $row['name'], ' costs ', $row['price'], "\n";
}Pencil costs 5.00
Pen costs 12.50
Marker costs 45.00:category and :max are named placeholders. A positional placeholder is ?, and the values go in the array in the same order:
<?php
$pdo = require __DIR__ . '/db.php';
$stmt = $pdo->prepare('SELECT name FROM products WHERE category = ? AND stock > ? ORDER BY name');
$stmt->execute(['paper', 0]);
echo implode(', ', $stmt->fetchAll(PDO::FETCH_COLUMN)), "\n";Diary, NotebookUse one style in a query, never a mix.
Native and emulated prepares
PDO can prepare in two ways:
| Mode | How | Setting |
|---|---|---|
| Native | MySQL itself prepares the statement and executes it with parameters (PREPARE/EXECUTE in the protocol) | ATTR_EMULATE_PREPARES => false |
| Emulated | PDO quotes the values and builds the final SQL text in PHP | true (PDO’s default for MySQL) |
Native prepares are the safer and more precise choice (the SQL parser sees the structure first), and you get real MySQL errors at prepare() time. Emulation saves one round trip per statement, and it needs the connection charset to be right to quote safely. The connection file turns emulation off. Mind the one difference that bites most often: LIMIT.
LIMIT and OFFSET
<?php
$pdo = require __DIR__ . '/db.php';
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
try {
$stmt = $pdo->prepare('SELECT name FROM products ORDER BY id LIMIT ?');
$stmt->execute([3]); // PDO sends the number as a quoted string: LIMIT '3'
echo "emulated, execute([3]) worked\n";
} catch (PDOException $e) {
echo 'emulated, execute([3]) failed: ', $e->errorInfo[1], "\n";
}
$stmt = $pdo->prepare('SELECT name FROM products ORDER BY id LIMIT ?');
$stmt->bindValue(1, 3, PDO::PARAM_INT); // an integer parameter: LIMIT 3
$stmt->execute();
echo 'emulated, bindValue INT: ', implode(', ', $stmt->fetchAll(PDO::FETCH_COLUMN)), "\n";emulated, execute([3]) failed: 1064
emulated, bindValue INT: Notebook, Pen, BackpackWith emulated prepares, a number passed through execute([...]) becomes a quoted string, and LIMIT '3' is a syntax error (code 1064). (With native prepares the server accepts the same call.) Bind LIMIT and OFFSET as integers with bindValue(..., PDO::PARAM_INT), and it works in both modes:
<?php
$pdo = require __DIR__ . '/db.php'; // native prepares
$page = 2;
$perPage = 3;
$stmt = $pdo->prepare('SELECT id, name FROM products ORDER BY id LIMIT :limit OFFSET :offset');
$stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
$stmt->bindValue(':offset', ($page - 1) * $perPage, PDO::PARAM_INT);
$stmt->execute();
foreach ($stmt as $row) {
echo $row['id'], ' ', $row['name'], "\n";
}4 Marker
5 Diary
6 StaplerIN lists
You cannot bind an array to one placeholder. Build one placeholder per value:
<?php
$pdo = require __DIR__ . '/db.php';
$ids = [1, 3, 5];
$marks = implode(',', array_fill(0, count($ids), '?')); // ?,?,?
$stmt = $pdo->prepare("SELECT id, name FROM products WHERE id IN ($marks) ORDER BY id");
$stmt->execute($ids);
foreach ($stmt as $row) {
echo $row['id'], ' ', $row['name'], "\n";
}1 Notebook
3 Backpack
5 DiaryOnly the ? characters are put into the SQL text, so this is safe. If $ids can be empty, handle that first: IN () is a syntax error. Return an empty result in PHP instead.
LIKE searches
Bind the whole pattern as the value:
<?php
$pdo = require __DIR__ . '/db.php';
$term = 'pen';
$stmt = $pdo->prepare('SELECT name FROM products WHERE name LIKE :pattern ORDER BY name');
$stmt->execute(['pattern' => '%' . $term . '%']);
echo implode(', ', $stmt->fetchAll(PDO::FETCH_COLUMN)), "\n";Pen, PencilA % or _ typed by the user still acts as a wildcard. Escape them if that matters (SQL Injection).
Binding types
| Constant | Use for |
|---|---|
PDO::PARAM_STR (default) | Strings, dates, decimals |
PDO::PARAM_INT | Integers (needed for LIMIT) |
PDO::PARAM_BOOL | Booleans (sent as 0 or 1) |
PDO::PARAM_NULL | NULL |
PDO::PARAM_LOB | Large binary data |
With native prepares, MySQL converts a string such as '42' to a number where the column is numeric, so WHERE id = ? with execute(['42']) works.
NULL
<?php
$pdo = require __DIR__ . '/db.php';
$stmt = $pdo->prepare('SELECT name FROM customers WHERE city = ?');
$stmt->execute([null]);
var_dump($stmt->fetchAll()); // = NULL is never true
$stmt = $pdo->prepare('SELECT name FROM customers WHERE city <=> ?');
$stmt->execute([null]);
echo implode(', ', $stmt->fetchAll(PDO::FETCH_COLUMN)), "\n"; // the NULL-safe operator finds themarray(0) {
}
IshaBinding null does not change the rule that = NULL never matches (NULL Values). Use IS NULL or the NULL-safe <=> operator.
What placeholders cannot do
Placeholders stand for values only. They cannot replace a table name, a column name, ASC/DESC or a keyword. Use a whitelist for those:
<?php
$pdo = require __DIR__ . '/db.php';
$sortable = ['name' => 'name', 'price' => 'price', 'stock' => 'stock'];
$sort = $sortable[$_GET['sort'] ?? ''] ?? 'name'; // unknown input falls back to a safe column
$dir = (($_GET['dir'] ?? '') === 'desc') ? 'DESC' : 'ASC';
$sql = "SELECT name, price FROM products ORDER BY $sort $dir LIMIT 3";
foreach ($pdo->query($sql) as $row) {
echo $row['name'], ' ', $row['price'], "\n";
}Backpack 799.00
Diary 150.00
Folder 30.00Reuse and the limit of repeated placeholders
A prepared statement can be executed many times with different values, which is faster for loops:
<?php
$pdo = require __DIR__ . '/db.php';
$stmt = $pdo->prepare('UPDATE products SET stock = stock - :qty WHERE id = :id AND stock >= :qty2');
foreach ([[1, 5], [4, 10], [5, 9]] as [$id, $qty]) {
$stmt->execute(['qty' => $qty, 'id' => $id, 'qty2' => $qty]);
echo "product $id: ", $stmt->rowCount(), " row updated\n";
}product 1: 1 row updated
product 4: 1 row updated
product 5: 0 row updatedNote :qty and :qty2. With native prepares you must not repeat the same named placeholder in one statement (PDO reports Invalid parameter number), so give it two names. Product 5 has only 4 in stock, so the stock >= :qty2 guard makes its update change 0 rows. Always check rowCount() when a guard decides success.
Never build SQL from input
"WHERE name = '$name'" is injectable in every mode. Prepare once with placeholders, then bind. Values go in execute(), never into the SQL string.