EP07. “SQL 安全与存储过程”

• 一个登录表单的密码栏位，只要背后的 SQL 是拼字符串组出来的，攻击者不用破解密码，一句 ' OR '1'='1 就能直接登进任何账号
• SQL Injection（SQL 注入）的本质是「让使用者的输入变成可执行的 SQL 语法」，防御的核心就是把「数据」和「SQL 逻辑」彻底分开
• 参数化查询（Parameterized Queries）跟预处理语句（Prepared Statements）是两种最主流的防御手段，EP02 转账那段代码其实已经在用了
• 存储过程（Stored Procedure）是把一段会重复执行的 SQL 逻辑存进数据库、之后直接调用，跟临时写的查询或函数不是一回事
• 前置知识：先看过 EP01、EP02，熟悉基本查询和事务（Transaction）的写法

重点内容

SQL Injection：一句注入就能绕过登录

后端如果直接把使用者输入拼进 SQL 字符串，就等于让使用者能自己「编辑」你的 SQL 语法：

// 危险写法：直接把使用者输入拼进 SQL 字符串
const query = `SELECT * FROM users WHERE email = '${email}' AND password_hash = '${passwordHash}'`;
await client.query(query);

正常情况下，email 栏位应该是一个 email 地址。但如果攻击者在登录表单的 email 栏位输入：

' OR '1'='1' --

拼出来的查询就变成：

SELECT * FROM users WHERE email = '' OR '1'='1' --' AND password_hash = '...';

-- 后面全部变成注释，原本要检查密码的 AND password_hash = '...' 直接被砍掉；'1'='1' 恒为真，整个 WHERE 条件永远成立，查询会把 users 表第一笔（或全部）数据返回，攻击者不需要知道任何密码就能登进去。

更危险的情况是拼接进破坏性语句，比如攻击者输入 x'; DROP TABLE orders; --，如果驱动程式允许一次执行多条语句，orders 整张表会被直接删掉。

参数化查询：把输入当「数据」，不是「语法」

参数化查询用占位符代替直接拼字符串，数据库引擎知道占位符的位置是「数据」，会把传进来的值当成普通文字比对，不会被当成 SQL 语法执行——就算传进来的字符串长得像 SQL，也不会生效。不同数据库的占位符写法不一样：

用 EP02 出现过的 Node.js pg 驱动改写上面的登录查询，把危险写法换成安全写法：

// 安全写法：参数化查询
const result = await client.query(
  'SELECT * FROM users WHERE email = $1 AND password_hash = $2',
  [email, passwordHash]
);

这跟 EP02 事务例子里 client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [100, fromId]) 是同一个写法——只要用了 $1/$2 这种占位符 + 参数数组传值，而不是自己拼字符串，就已经是参数化查询了。

预处理语句（Prepared Statements）：先编译，再代值

预处理语句分三步：

1. Prepare（准备）：先把带占位符的 SQL 模板送给数据库，数据库解析、编译、优化这个模板，但先不执行
2. Bind（绑定）：把实际的值绑定到占位符上
3. Execute（执行）：用绑定好的值真正跑一次查询——同一个模板可以反复绑定不同的值、执行多次，不用每次都重新解析 SQL

PostgreSQL 支持直接用 PREPARE/EXECUTE 语法手动做这三步（实际开发时大多由数据库驱动程式自动处理，很少需要手写）：

-- 准备一次模板
PREPARE find_user_by_email AS
SELECT id, name, email FROM users WHERE email = $1;

-- 之后可以反复执行，代入不同的值
EXECUTE find_user_by_email('alice@example.com');
EXECUTE find_user_by_email('bob@example.com');

大部分现代数据库驱动程式（包括 EP02 用的 Node.js pg）执行参数化查询时，背地里就是照这个「先送模板、再代值执行」的方式跟数据库沟通——不用另外手写 PREPARE/EXECUTE，参数化查询语法本身已经享有这层防护和效能优化。

存储过程（Stored Procedure）

存储过程是存在数据库里、可以重复调用的一段 SQL 逻辑，跟平常写的查询或函数不一样：

• 一般查询：写一次、执行一次，逻辑留在应用程式代码里
• 函数（Function）：一定要有返回值，能直接塞进 SELECT 里当栏位用
• 存储过程（Procedure）：不一定要有返回值，不能塞进 SELECT，只能用 CALL 单独执行，适合拿来做「一次做完好几个步骤」的批次操作

比如 EP02 用事务包起来的「订单完成时，同时更新订单状态和扣库存」，也可以直接包成一个存储过程：

CREATE OR REPLACE PROCEDURE complete_order(p_order_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE orders
  SET status = 'completed', completed_at = NOW()
  WHERE id = p_order_id;

  UPDATE products p
  SET stock_quantity = p.stock_quantity - oi.quantity
  FROM order_items oi
  WHERE oi.order_id = p_order_id AND oi.product_id = p.id;
END;
$$;

调用方式：

CALL complete_order(1001);

以后应用程式只要 CALL complete_order(订单ID)，不用在代码里重复写两段 UPDATE 的逻辑，也不用担心漏写事务保护——逻辑集中在数据库这一处维护。

语法速查

适用版本

存储过程语法以 PostgreSQL（CREATE PROCEDURE ... LANGUAGE plpgsql + CALL）为主；SQL Server 用 CREATE PROCEDURE ... AS BEGIN ... END 定义、EXEC 调用，语法不同，实际使用前建议对照所用数据库的官方文档确认。

常见错误

• ❌ 后端直接把使用者输入用字符串拼接组成 SQL——这是 SQL Injection 最常见的成因，永远优先用参数化查询或预处理语句，不要手动拼字符串
• ❌ 以为只要前端做了输入检查（比如 JS 检查 email 格式）就够安全——前端检查很容易被绕过（直接用 Postman/curl 发请求跳过网页），后端一样要做参数化查询，这道防线不能省
• ❌ 密码用明文存进数据库、登录时用字符串直接比对——密码要先用 bcrypt 等算法 hash 过再存，比对时也是比对 hash 值，不是比对明文
• ❌ 数据库连线帐号权限开太大（能 DROP TABLE、能读其他不相关的表）——应用程式用的帐号只给它实际需要的权限，就算真的被注入了，也能限制伤害范围
• 💡 存储过程也能拿来做权限隔离：让应用程式帐号只有权限 EXECUTE 特定存储过程，不给它直接读写表的权限，是常见的额外防护层

Sources

Blog / Website：

1. SQL Injection
2. SQL Parameterized Queries
3. SQL Prepared Statements
4. SQL Stored Procedures