PHP MySQL 预处理语句
Placeholders, parameter binding, and why it stops SQL injection.
Key PointsPrepared statements send SQL structure and data separately, so data is never parsed as syntax.
Two placeholder styles
PHP
<?php
// 位置占位符
$stmt = $pdo->prepare("SELECT * FROM users WHERE name = ? AND age > ?");
$stmt->execute(["Tom", 18]);
// 命名占位符（推荐）
$stmt = $pdo->prepare("SELECT * FROM users WHERE name = :name AND age > :age");
$stmt->execute(["name" => "Tom", "age" => 18]);
echo "占位符个数: " . substr_count("SELECT * FROM users WHERE name = :name AND age > :age", ":") . "\n";Output
占位符个数: 2Why injection fails
With concatenation the database receives one string, so the OR in 1 OR 1=1 becomes syntax. With a prepared statement the skeleton is compiled first and the data can only be a value.
PHP
<?php
$input = "1 OR 1=1";
// 拼接：注入
$sql = "SELECT COUNT(*) FROM users WHERE id = $input";
echo $sql, "\n";
// 预处理：安全
$stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE id = ?");
$stmt->execute([$input]);
echo "匹配行数: " . $stmt->fetchColumn() . "\n";Output
SELECT COUNT(*) FROM users WHERE id = 1 OR 1=1
匹配行数: 0"1 OR 1=1" is compared as a string against an integer column, coerced to 0, matching nothing. The injection is dead.
Binding parameters
PHP
<?php
$stmt = $pdo->prepare("SELECT name FROM users WHERE id = ?");
// 方式一：execute 传数组（最常用）
$stmt->execute([1]);
// 方式二：bindValue 显式指定类型
$stmt->bindValue(1, 2, PDO::PARAM_INT);
$stmt->execute();
// 方式三：bindParam 按引用（循环里要小心）
$id = 3;
$stmt->bindParam(1, $id, PDO::PARAM_INT);
$stmt->execute();
echo $stmt->fetchColumn(), "\n";Output
BobbindParam binds by reference — reusing the variable in a loop makes every parameter take the last value. **Use execute([...]) or bindValue in loops.**LIMIT cannot be bound
LIMIT ? fails with EMULATE_PREPARES = false because PDO sends it as a string. Cast to int and interpolate.
PHP
<?php
$page = max(1, (int)($_GET["page"] ?? 1));
$size = 10;
$offset = ($page - 1) * $size;
// 强制整数，杜绝注入
$sql = sprintf("SELECT name FROM users ORDER BY id LIMIT %d OFFSET %d", $size, $offset);
$stmt = $pdo->query($sql);
echo $sql, "\n";
echo "本页 " . count($stmt->fetchAll()) . " 行\n";Output
SELECT name FROM users ORDER BY id LIMIT 10 OFFSET 0
本页 3 行%d is the only safe interpolation slot. Casting plus %d means even "1; DROP TABLE" becomes 1.The IN clause
PHP
<?php
$ids = [1, 2, 3];
// 按数量生成占位符
$in = implode(",", array_fill(0, count($ids), "?"));
$stmt = $pdo->prepare("SELECT name FROM users WHERE id IN ($in)");
$stmt->execute($ids);
print_r($stmt->fetchAll(PDO::FETCH_COLUMN));Output
Array
(
[0] => Tom
[1] => Amy
[2] => Bob
)IN (?) treats the whole array as one value. Generate one placeholder per element — the most commonly missed detail.
Common mistakes
| Mistake | Problem |
|---|---|
WHERE id = ? 传 ["1,2"] | 当成字符串，匹配不到 |
| 表名用占位符 | 语法错误，标识符不能绑定 |
LIKE %?% | 占位符必须在引号内，写 LIKE ? 传 "%kw%" |
| 占位符个数与参数不符 | HY093 错误 |
ORDER BY ? | 排序列名不能绑定，用白名单 |
PHP
<?php
// LIKE 的正确写法
$kw = "o";
$stmt = $pdo->prepare("SELECT name FROM users WHERE name LIKE ?");
$stmt->execute(["%$kw%"]); // 通配符放在参数里
print_r($stmt->fetchAll(PDO::FETCH_COLUMN));Output
Array
(
[0] => Tom
[1] => Bob
)两种占位符
PHP
<?php
// 位置占位符
$stmt = $pdo->prepare("SELECT * FROM users WHERE name = ? AND age > ?");
$stmt->execute(["Tom", 18]);
// 命名占位符（推荐）
$stmt = $pdo->prepare("SELECT * FROM users WHERE name = :name AND age > :age");
$stmt->execute(["name" => "Tom", "age" => 18]);
echo "占位符个数: " . substr_count("SELECT * FROM users WHERE name = :name AND age > :age", ":") . "\n";Output
占位符个数: 2为什么能防注入
拼接 SQL 时，数据库收到的是一整条语句，1 OR 1=1 里的 OR 被当成语法。预处理时数据库先编译好语句骨架，数据后到，只能当值，不能当语法。
PHP
<?php
$input = "1 OR 1=1";
// 拼接：注入
$sql = "SELECT COUNT(*) FROM users WHERE id = $input";
echo $sql, "\n";
// 预处理：安全
$stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE id = ?");
$stmt->execute([$input]);
echo "匹配行数: " . $stmt->fetchColumn() . "\n";Output
SELECT COUNT(*) FROM users WHERE id = 1 OR 1=1
匹配行数: 0"1 OR 1=1" 作为一个字符串去和整数列比较，MySQL 转成 0，没有 id 为 0 的行，所以是 0。注入语句完全失效。
绑定参数
PHP
<?php
$stmt = $pdo->prepare("SELECT name FROM users WHERE id = ?");
// 方式一：execute 传数组（最常用）
$stmt->execute([1]);
// 方式二：bindValue 显式指定类型
$stmt->bindValue(1, 2, PDO::PARAM_INT);
$stmt->execute();
// 方式三：bindParam 按引用（循环里要小心）
$id = 3;
$stmt->bindParam(1, $id, PDO::PARAM_INT);
$stmt->execute();
echo $stmt->fetchColumn(), "\n";Output
BobbindParam 绑定的是变量引用，循环里复用同一个变量会导致所有参数都变成最后一次的值。**循环里用 execute([...]) 或 bindValue。**LIMIT 不能绑定
LIMIT ? 在 EMULATE_PREPARES = false 时会报错，因为 MySQL 协议里 LIMIT 的参数是整数类型，PDO 默认当字符串发。解决办法：强制转整数后拼接。
PHP
<?php
$page = max(1, (int)($_GET["page"] ?? 1));
$size = 10;
$offset = ($page - 1) * $size;
// 强制整数，杜绝注入
$sql = sprintf("SELECT name FROM users ORDER BY id LIMIT %d OFFSET %d", $size, $offset);
$stmt = $pdo->query($sql);
echo $sql, "\n";
echo "本页 " . count($stmt->fetchAll()) . " 行\n";Output
SELECT name FROM users ORDER BY id LIMIT 10 OFFSET 0
本页 3 行%d 是唯一安全的拼接口子。(int) 强转 + %d 双重保证，即使 $_GET["page"] 是 "1; DROP TABLE" 也只会变成 1。IN 子句
PHP
<?php
$ids = [1, 2, 3];
// 按数量生成占位符
$in = implode(",", array_fill(0, count($ids), "?"));
$stmt = $pdo->prepare("SELECT name FROM users WHERE id IN ($in)");
$stmt->execute($ids);
print_r($stmt->fetchAll(PDO::FETCH_COLUMN));Output
Array
(
[0] => Tom
[1] => Amy
[2] => Bob
)IN (?) 只会当成一个值，传数组进去会被转成字符串。必须按元素个数动态生成占位符，这是最容易被忽略的一点。
常见错误
| 错误写法 | 问题 |
|---|---|
WHERE id = ? 传 ["1,2"] | 当成字符串，匹配不到 |
| 表名用占位符 | 语法错误，标识符不能绑定 |
LIKE %?% | 占位符必须在引号内，写 LIKE ? 传 "%kw%" |
| 占位符个数与参数不符 | HY093 错误 |
ORDER BY ? | 排序列名不能绑定，用白名单 |
PHP
<?php
// LIKE 的正确写法
$kw = "o";
$stmt = $pdo->prepare("SELECT name FROM users WHERE name LIKE ?");
$stmt->execute(["%$kw%"]); // 通配符放在参数里
print_r($stmt->fetchAll(PDO::FETCH_COLUMN));Output
Array
(
[0] => Tom
[1] => Bob
)