后台订单列表加了一个搜索框,按客户名模糊查。测试同学输入「张」,正常;输入「O'Brien」,页面 500,日志里是 SQLSTATE[42000]: Syntax error。开发看了一眼,说「数据库对单引号敏感,让用户别输」。第二天安全扫描报告出来,同一个接口输入 ' OR 1=1 -- ,返回了全表数据,包括别人的手机号。
这就是 SQL 注入。它不是什么高深攻击,本质就是「用户输入被当成了 SQL 代码的一部分」。今天这篇把两件事讲透:注入是怎么发生的,预处理为什么能挡住;顺带把 PDO 的连接、事务、索引和分页讲清楚,因为一个能用的数据库层,安全和性能是连在一起的。
注入是怎么发生的
先看一段非常典型的代码:
<?php
$q = $_GET['q'] ?? '';
$sql = "SELECT id, customer_name FROM orders WHERE customer_name LIKE '%{$q}%'";
$rows = $pdo->query($sql)->fetchAll();
用户输入「张」,拼出来的 SQL 是 ... LIKE '%张%',没问题。用户输入 ' OR 1=1 -- ,拼出来变成:
SELECT id, customer_name FROM orders WHERE customer_name LIKE '%' OR 1=1 -- %'
拆开看发生了什么:第一个单引号闭合了原来的字符串;OR 1=1 是一个永远为真的条件;-- 是 SQL 注释,把后面那个没闭合的引号注释掉了。整条语句语法合法,结果返回全部订单。
更进一步的攻击会读别的表:' UNION SELECT username, password FROM users -- 。只要数据库账号有权限,数据就会被带出来。如果账号是 root,连系统表都能读。
注意,注入不只在「字符串」参数里。数字参数拼接同样危险:"WHERE id = " . $_GET['id'],输入 1 OR 1=1 一样生效,因为根本没引号可闭合。还有 ORDER BY {$_GET['sort']},输入一个表达式就能改排序逻辑甚至做盲注。
PDO 连接:几个必须设的选项
PDO 是 PHP 自带的数据库访问层,支持 MySQL、PostgreSQL、SQLite 等多种数据库,接口一致。连接时通过 DSN 指定驱动和参数:
<?php
declare(strict_types=1);
$dsn = 'mysql:host=127.0.0.1;port=3306;dbname=shop;charset=utf8mb4';
$pdo = new PDO($dsn, 'app', 'secret', [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]);
三个选项各有用处。ERRMODE_EXCEPTION 让错误抛异常,而不是返回 false 等你手动判断——漏判一次就是线上悄悄出错。DEFAULT_FETCH_MODE 决定查询结果默认是关联数组还是数字索引,统一成关联数组,代码更可读。EMULATE_PREPARES = false 关闭模拟预处理,让 MySQL 真正执行预处理。
字符集要写 utf8mb4,不要写 utf8。MySQL 里的 utf8 其实是三字节版本,存不下 Emoji 和部分生僻字,表现为写入报错或者字符被截断。表、库、连接三处都要是 utf8mb4。
密码不要写在代码里。用环境变量或配置文件,确认配置文件不在公网可访问的目录。第 9 篇会讲配置与文件的保护。
连接本身也有成本。每次请求都新建一条 PDO 连接,意味着一次 TCP 握手加一次认证,几十毫秒就没了,流量大时还容易把数据库的连接数打满。常见做法是把连接封装成一个只创建一次的对象,在同一请求内复用。再往上一层是持久连接(PDO::ATTR_PERSISTENT),它让 FPM worker 之间的连接不关闭,省掉重复握手。但它有明确的代价:连接状态不会自动复位,没提交的事务、临时表、会话变量、锁,都可能留给下一个请求;用户权限也是按连接固定的。要用就得保证每个请求结束时状态是干净的,否则会出现「A 用户的事务影响了 B 用户」这种极难复现的问题。中小项目用「单请求单连接复用」就够了,别一上来就开持久连接。
预处理为什么能挡住注入
预处理的流程分两步。第一步,PHP 把带占位符的 SQL 发给数据库,数据库先解析、校验、生成执行计划,此时结构已经固定。第二步,把用户输入作为「参数」绑定进去,数据库只把它当数据,不当代码。
<?php
$stmt = $pdo->prepare('SELECT id, customer_name FROM orders WHERE customer_name LIKE :q');
$stmt->execute([':q' => '%' . $q . '%']);
$rows = $stmt->fetchAll();
输入 ' OR 1=1 -- 时,整个字符串被当成一个值传给 LIKE,数据库不会重新解析里面的引号和关键字。它要找的是名字里包含这段字符的订单,找不到就返回空。攻击者写的 SQL 片段失去了语法意义。
这就是关键区别:字符串拼接是「先拼、再解析」,输入有机会改变结构;预处理是「先定结构、再填数据」,输入没有机会改变结构。
字符串拼接
SQL 结构和数据在同一段文本里,解析器无法区分哪部分来自用户。任何引号、注释、关键字都可能改变语句含义。
预处理
结构先固定,占位符是明确的「这里要填一个值」。绑定阶段只传值,不参与语法解析,所以注入片段只是普通字符。
模拟预处理(EMULATE_PREPARES = true)也能正确转义,但它在 PHP 侧完成替换,依赖字符集和转义实现,边界情况更多。原生预处理的边界更清楚,能关就关。
预处理有它管不到的地方:表名、列名、ORDER BY 的字段、LIMIT 的数值,都不能用占位符代替(至少不能直接代)。这些必须用白名单:把允许的值写死在一个数组里,用户传什么只能命中数组里的项。
<?php
$allowedSort = ['created_at', 'amount_cents'];
$sort = in_array($_GET['sort'] ?? '', $allowedSort, true)
? $_GET['sort']
: 'created_at';
$limit = max(1, min(100, (int) ($_GET['per_page'] ?? 20)));
$offset = max(0, (int) ($_GET['offset'] ?? 0));
$sql = "SELECT * FROM orders ORDER BY {$sort} LIMIT {$limit} OFFSET {$offset}";
这里的 $sort 来自白名单,$limit 和 $offset 强转成整数并夹在范围内,都是安全的。关键是别把「用户传的字符串」直接放进 SQL,哪怕它看起来像数字。
但预处理也有「用了却照样被注入」的两种情况。第一种是把占位符拼进了标识符。有人把表名也参数化,写成 prepare('SELECT * FROM :table WHERE id = :id'),结果不是更安全,而是直接报 SQLSTATE[42000]: Syntax error or access violation: 1064——占位符只能替代值,数据库不会把参数当表名解析。更常见的是只把值参数化、结构还在拼:"SELECT * FROM orders WHERE {$column} = :id"、"ORDER BY {$sort}",用户传的列名或排序字段里带表达式,注入照样成立。还有一种不报错的写法:ORDER BY :sort 能跑,但它按一个常量字符串排序,排序功能悄悄失效,比报错更难发现。
第二种更隐蔽:模拟预处理遇上错误的字符集。PDO::ATTR_EMULATE_PREPARES 的默认值就是 true,很多人写了 prepare() 就以为安全了。模拟模式下,转义是 PHP 按 DSN 里声明的 charset 做的;如果 DSN 写 utf8、库表实际是 gbk,或者连接后有人执行了 SET NAMES gbk,同一段字节会被数据库用另一套编码解释。经典的宽字节注入就是这么来的:输入 %bf%27,转义后变成 %bf%5c%27,而 %bf%5c 在 GBK 里是一个合法汉字,剩下的单引号重新获得闭合能力。具体到某个驱动版本的转义实现会有差异,这点按待确认处理;最省事的做法是关掉模拟、同时保证 DSN 的 charset=utf8mb4 和库表一致。
顺带记一个容易误判的报错:SQLSTATE[HY093]: Invalid parameter number。常见触发是同一个命名占位符在 SQL 里出现两次、只绑定了一次,比如 WHERE a = :q OR b = :q 配 execute([':q' => $q]);原生预处理会认为这是两个参数,模拟模式下有时却照样跑。看到 HY093 先数占位符和绑定值的个数,别急着怀疑数据库,改成两个不同的名字或统一用 ? 按顺序绑定都能绕开。
事务:要么全做,要么全不做
下单要同时写订单、扣库存、记流水。三步中间任何一步失败,都不能留下半成品。PDO 的事务写法:
<?php
try {
$pdo->beginTransaction();
$stmt = $pdo->prepare('UPDATE products SET stock = stock - :qty WHERE id = :id AND stock >= :qty2');
$stmt->execute([':qty' => $qty, ':id' => $productId, ':qty2' => $qty]);
if ($stmt->rowCount() === 0) {
throw new RuntimeException('库存不足');
}
$pdo->prepare('INSERT INTO orders (product_id, qty) VALUES (:pid, :qty)')
->execute([':pid' => $productId, ':qty' => $qty]);
$pdo->commit();
} catch (Throwable $e) {
$pdo->rollBack();
throw $e;
}
几个细节:beginTransaction() 之后必须 commit() 或 rollBack(),异常路径漏掉回滚,连接会一直持有锁,直到请求结束。判断库存够不够,不要先 SELECT 再 UPDATE,那是竞态条件;用带条件的 UPDATE ... WHERE stock >= :qty,再看 rowCount()。
表引擎要支持事务。MySQL 的 InnoDB 支持,MyISAM 不支持,后者执行 beginTransaction 不会报错,但回滚无效。老项目里混用引擎,事务失效就是这么来的。
事务不是越长越好。事务里不要调外部接口、不要做耗时计算,锁持有时间一长,其他请求就排队,表现为整站变慢。
rowCount() 在 UPDATE 上还有个必须知道的坑:MySQL 默认返回「真正被改动的行数」,不是「匹配到的行数」。UPDATE orders SET status = 'paid' WHERE id = :id AND status = 'paid' 再执行一次,rowCount 是 0,代码会以为订单不存在;UPDATE ... SET updated_at = NOW() 在同一秒内重复执行也一样。要判断「这行在不在」,可以加连接选项 PDO::MYSQL_ATTR_FOUND_ROWS => true 让 rowCount 返回匹配行数,或者先 SELECT ... FOR UPDATE。扣库存这类语句不受影响,因为 stock - 1 一定改变值,rowCount 为 0 就真是条件没命中。统计行数可以老实写 COUNT(*)。
第二个坑是异常之后的事务残留。beginTransaction() 之后抛异常,如果 catch 里只记日志没回滚,或者 rollBack() 自己又抛了(连接已经断),连接上的事务状态不会自动复位。表现有两种:同一请求里后面的 SQL 莫名其妙进了上一个事务,最后被一起提交;或者行锁一直不放,别的请求报 SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded。排查时在 catch 里打印 $pdo->inTransaction();正确写法是不管走哪条路径都检查一遍再回滚。用持久连接时更要在请求结束前确认没有未提交事务,否则它会原样留给下一个请求。
还有一个反直觉的点:MySQL 的 DDL 会隐式提交。事务里执行了 ALTER TABLE、TRUNCATE、CREATE INDEX,之前的改动已经落盘,后面再抛异常也回滚不回来。所以迁移脚本、加字段这类操作,永远不要和业务写入放在同一个事务里。
性能:索引、N+1 和分页
本地 0.1 秒、线上 3 秒,最常见的三个原因:没索引、查太多、查太深。
索引方面,先看查询条件里用到了哪些列。WHERE status = ? ORDER BY created_at DESC 这种,可以建联合索引 (status, created_at),顺序按「先筛选再排序」来。用 EXPLAIN 看执行计划,重点看 type 是不是 ALL(全表扫描)、rows 估算了多少行。注意 LIKE '%关键词%' 前导模糊匹配用不上普通索引,要么改成前缀匹配 LIKE '关键词%',要么接受它慢、换搜索引擎。
N+1 查询是另一种典型:先查 20 个订单,再循环查每个订单的商品明细,一共 21 条 SQL。改成一次查回来:
<?php
$orderIds = array_column($orders, 'id');
$in = implode(',', array_fill(0, count($orderIds), '?'));
$stmt = $pdo->prepare("SELECT order_id, name FROM order_items WHERE order_id IN ({$in})");
$stmt->execute($orderIds);
$items = $stmt->fetchAll();
$in 里的问号数量是按 id 数量生成的,参数仍然通过 execute() 传入,没有拼接用户数据,安全。注意 $orderIds 要是空数组,这条 SQL 会变成 IN () 报错,先判断空。
分页别用大 OFFSET。LIMIT 20 OFFSET 100000 会让数据库先跳过十万行,越翻越慢。可行的改法是记录上一页最后一条的 id,用 WHERE id < :lastId ORDER BY id DESC LIMIT 20,这叫游标分页。代价是不能直接跳页,适合「下拉加载更多」,不适合需要页码的后台列表。
还有 SELECT *。多取不用的列,既浪费带宽又可能让覆盖索引失效。只取需要的字段,是一条几乎没有代价的优化。
把 LIKE 单独说清楚,因为它是搜索功能里最容易「加了索引还是慢」的地方。WHERE customer_name LIKE '%张%' 用不上 B-Tree 索引:索引按前缀排序,前面的 % 让数据库不知道从哪里开始找。LIKE '张%' 可以走索引,但要求列上没有函数(LOWER(name) 会失效,MySQL 8.0.13 起可以建函数索引)、排序规则一致。中文要保留中间匹配,三条路:MySQL 5.7 起的 InnoDB 全文索引配 ngram 分词器(建表时写 WITH PARSER ngram)、把关键词前缀单独建索引列、或者上 Elasticsearch 这类搜索引擎。
EXPLAIN 不用背所有字段,先看四列。type 从好到坏大致是 const、eq_ref、ref、range、index、ALL,出现 ALL 就是全表扫;key 是实际用的索引,为空说明没走索引;rows 是预估扫描行数,只是估算值;Extra 里 Using filesort 表示排序没走索引,Using temporary 表示用了临时表,Using index 是覆盖索引(好事)。key_len 能看出联合索引用了几列,(status, created_at) 只用了 status 时长度会明显偏小。MySQL 8.0.18 起可用 EXPLAIN ANALYZE 看实际耗时,线上跑前先确认语句只读。
拿到报错别急着改代码,MySQL 的错误文本通常已经给了线索。SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'ORD20261106001' for key 'orders.uniq_order_no' 是唯一索引冲突;SQLSTATE[42S22]: Column not found: 1054 Unknown column 'amount' 是字段名写错或迁移没跑;SQLSTATE[HY000]: General error: 1364 Field 'user_id' doesn't have a default value 是插入漏了非空字段。定位顺序是:先把完整 SQL 和绑定参数打到日志(注意脱敏),再拿到数据库客户端原样执行,最后才怀疑框架。
场景走查:订单搜索从能用到扛住
需求:后台订单列表支持按客户名模糊搜索、按状态筛选、按时间或金额排序、分页,还要显示总数。
第一版用字符串拼接,功能当天就做完了。测试输入带单引号的名字时报错,开发让用户「别输入特殊字符」。第二周安全扫描报注入,第一版直接作废。
第二版改用预处理加白名单:
<?php
declare(strict_types=1);
$q = trim((string) ($_GET['q'] ?? ''));
$status = (string) ($_GET['status'] ?? '');
$allowedStatus = ['paid', 'shipped', 'cancelled'];
$sql = 'SELECT id, customer_name, amount_cents, created_at FROM orders WHERE 1 = 1';
$params = [];
if ($q !== '') {
$sql .= ' AND customer_name LIKE :q';
$params[':q'] = '%' . $q . '%';
}
if (in_array($status, $allowedStatus, true)) {
$sql .= ' AND status = :status';
$params[':status'] = $status;
}
$sql .= ' ORDER BY created_at DESC LIMIT 20 OFFSET 0';
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$rows = $stmt->fetchAll();
第三版上线后功能正常,但搜索慢了:订单表两百万行,customer_name 没有索引,前导模糊匹配又用不上索引,每次都全表扫。改成把搜索范围收窄到「最近 90 天」并加联合索引,响应从 3 秒降到 200 毫秒。产品接受了这个限制,因为查三个月前的订单本来就该走归档表。
第四版处理分页和总数。总数用单独的 COUNT 查询,条件与列表一致,避免把全量数据捞回来再数。深分页改成游标分页,前端从「页码」改成「加载更多」。上线后慢查询日志里再没出现这条 SQL。
现象、原因与第一步
| 现象 | 真实原因 | 第一步查什么 |
|---|---|---|
| 输入单引号报语法错误 | SQL 是拼接的,用户输入改变了语句结构 | 搜 query( 和字符串拼接,改 prepare 加参数 |
| 搜索返回了全表数据 | 已经发生 SQL 注入,条件被 OR 1=1 覆盖 | 看请求参数和实际 SQL;审计所有拼接点,必要时查访问日志 |
| 中文或 Emoji 变问号 | 连接、表、库的字符集不是 utf8mb4 | 检查 DSN 的 charset 和建表语句 |
| 本地快,线上慢 | 数据量差异、缺索引、网络延迟 | 在线上跑 EXPLAIN,看 type 和 rows |
| 分页越往后越慢 | 大 OFFSET 导致扫描大量行 | 看 SQL 里的 OFFSET;改用游标分页 |
| 偶发 MySQL server has gone away | 连接超时、包过大或长任务占着连接 | 查 wait_timeout、max_allowed_packet 和慢查询 |
| 事务回滚没生效 | 表引擎不支持事务,或异常路径没调 rollBack | 看建表引擎;检查 catch 里有没有回滚 |
| 扣库存后出现负库存 | 先查后改,存在并发竞态 | 改成带条件的 UPDATE 并检查 rowCount |
边界与代价
预处理能防注入,但不能防「越权」。查询带上用户 id 只是安全的一部分,接口还要校验当前用户有没有权限看这条数据。注入解决的是「别让输入改代码」,权限解决的是「该不该给你看」,两件事不能混。
PDO 是抽象层,性能比数据库专用扩展略低,但差异在业务系统里通常可以忽略。真正影响性能的是查询次数和索引,而不是驱动本身。
事务的代价是锁。粒度越粗、时间越长,并发越差。能用一条带条件的 UPDATE 解决的事,不要写成「查一遍、判断、再改」的长事务。
索引不是越多越好。每个索引都占空间,写入时都要维护,表上十几个索引会让插入变慢。按实际查询建,定期清理没人用的索引。
当天能自己验证的练习
- 建一个 SQLite 或 MySQL 表,写一段拼接查询,输入
' OR 1=1 --,观察返回结果;再改成预处理,确认返回空。 - 用 PDO 连接时分别设置
ATTR_EMULATE_PREPARES为 true 和 false,打印实际执行的 SQL 或开启数据库日志对比。 - 写一个事务:扣库存、写订单,故意让第二步抛异常,确认库存回滚。
- 造 10 万行数据,对同一个查询分别跑有索引和无索引版本,用
EXPLAIN对比type和耗时。 - 写一个 N+1 的订单列表,再用
WHERE IN改成一次查询,数一数 SQL 条数从多少降到多少。
上面的练习本地半小时能跑完。用 Docker 起一个 MySQL 8:docker run --rm --name mysqltest -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=shop -p 3306:3306 mysql:8.4,等日志出现 ready for connections 再连。造十万行数据先放宽递归深度:mysql -h127.0.0.1 -uroot -proot shop -e "SET SESSION cte_max_recursion_depth=100000; INSERT INTO orders (customer_name, status, created_at) WITH RECURSIVE seq(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n < 100000) SELECT CONCAT('客户', n), 'paid', NOW() FROM seq;",再查 SELECT COUNT(*) 应该返回 100000。
起内置服务器 php -S 127.0.0.1:8080 -t public,另开窗口 curl -s "http://127.0.0.1:8080/search.php?q=%27%20OR%201%3D1%20--%20"。拼接版本会返回一大串订单,预处理版本返回 {"count":0} 或空数组 [];看到空,才说明输入被当成值而不是结构。再跑 mysql ... -e "EXPLAIN SELECT * FROM orders WHERE customer_name LIKE '%张%'",type 是 ALL、rows 接近总行数就是没走索引;换成 LIKE '张%' 并建索引后,type 应变成 range,key 显示索引名。
最后验证 HY093:把 :q 在 SQL 里写两次、只绑一次,原生预处理下会抛 SQLSTATE[HY093]: Invalid parameter number;把 ATTR_EMULATE_PREPARES 改回 true,它可能不报错照样跑,这正是「看着用了预处理、其实没保护住」的那种情况。
小结
三句话:注入的本质是输入被当成代码,预处理让结构和数据分开;表名列名排序字段不能占位,必须白名单;能用的查询还要看索引、查询次数和分页方式。
下一篇讲表单、会话与 Cookie。今天的搜索参数从 $_GET 来,登录状态则要靠 Session 维持。下一篇从「用户提交完表单刷新,数据库里多了一条」开始,讲清楚 POST 与 GET、PRG 模式、Cookie 属性和 Session 固定攻击。