第 11 章:让代码安全地访问库
前 10 章你都是在命令行里跟数据库对话。真实项目里,SQL 是代码发出去的——这一章讲代码这一侧最容易出的事:注入、连接、N+1。
学习目标
- 亲手复现一次 SQL 注入,理解它为什么能绕过登录
- 会用参数化查询写所有带用户输入的 SQL
- 知道连接池解决什么问题,SQLite 和 PostgreSQL 的差别在哪
- 能识别 ORM 的 N+1 问题,并知道怎么修
11.1 先看一次注入是怎么发生的
下面这段代码看起来很正常:
import sqlite3
con = sqlite3.connect("bookstore.db")
name = input("请输入客户姓名:")
sql = f"SELECT * FROM customers WHERE name = '{name}'"
rows = con.execute(sql).fetchall()
print(rows)输入 张三,查到一行;输入 不存在,查到零行。功能正确。
现在输入这个:
' OR 1=1 --拼出来的 SQL 变成:
SELECT * FROM customers WHERE name = '' OR 1=1 --'OR 1=1 让条件永远为真,-- 把后面的引号注释掉。结果:整张表的数据全部返回。
如果这是登录查询:
sql = f"SELECT * FROM users WHERE username = '{u}' AND password = '{p}'"在用户名框里输入 admin' --,密码随便填,SQL 就变成:
SELECT * FROM users WHERE username = 'admin' --' AND password = 'xxx'密码校验被注释掉了,直接以 admin 身份登录。
为什么这不是"过滤特殊字符"能解决的
很多人第一反应是"把单引号转义掉"。这确实能挡住上面这个例子,但绕过方式还有很多:不同数据库的转义规则不一样、编码问题、\ 转义、多字节字符……这是一场没有终点的军备竞赛。
正确的解法不是"过滤输入",而是不让输入参与 SQL 的拼接。
11.2 参数化查询:把数据和语句分开
name = "' OR 1=1 --"
rows = con.execute(
"SELECT * FROM customers WHERE name = ?", (name,)
).fetchall()
print(rows) # []? 是占位符。数据库收到的是两条独立的信息:"语句模板"和"参数值"。参数永远被当作数据,不会被解释成 SQL 语法,所以 ' OR 1=1 -- 只是一个"名字很奇怪的字符串",查不到任何行。
这是防注入的唯一正确做法,没有之一。
各种数据库的占位符
| 数据库 / 驱动 | 占位符 | 示例 |
|---|---|---|
| SQLite(Python) | ? | WHERE id = ? |
| PostgreSQL(psycopg) | %s | WHERE id = %s |
| MySQL(PyMySQL) | %s | WHERE id = %s |
| SQLAlchemy | :name | WHERE id = :id |
写法不同,原理一样:语句是语句,值是值。
多参数与批量
# 多个参数:按位置对应
con.execute(
"SELECT title FROM books WHERE category = ? AND price < ?",
("文学", 50),
)
# 批量插入:一次提交,性能差一个数量级
con.executemany(
"INSERT INTO books (title, category, price) VALUES (?, ?, ?)",
[("书 A", "文学", 30), ("书 B", "历史", 45)],
)executemany 不只是"少写几行",它把多条插入合并成一次网络/磁盘往返。导入 1 万行数据时,逐条 execute 可能要几十秒,executemany 通常一秒内完成。
参数化也能用于"动态条件"
需要按不同条件查询时,不要拼 SQL 字符串,而是拼占位符的数量:
def search(category=None, max_price=None):
sql = "SELECT title, price FROM books WHERE 1=1"
params = []
if category:
sql += " AND category = ?"
params.append(category)
if max_price:
sql += " AND price <= ?"
params.append(max_price)
return con.execute(sql, params).fetchall()拼接的只是固定的 SQL 片段,用户输入始终走参数。这是安全的。
11.3 连接:不要每个请求都开一个
con = sqlite3.connect("bookstore.db")SQLite 的连接很轻(打开一个文件),开销小。但服务端数据库不一样:PostgreSQL 的一个连接对应一个后端进程,建立一个连接要几十毫秒,还要占用内存。
如果每个 HTTP 请求都 connect() 再 close():
- 高并发时连接数暴涨,数据库直接拒绝(
too many connections); - 每次请求都付建立连接的成本,QPS 上不去。
连接池的做法是:启动时建好一批连接,请求来了借一个,用完还回去,不真的关闭。
# PostgreSQL 常见写法(SQLAlchemy)
from sqlalchemy import create_engine
engine = create_engine(
"postgresql+psycopg://user:pass@localhost/bookstore",
pool_size=10, # 常驻连接数
max_overflow=20, # 高峰期临时多开的上限
pool_pre_ping=True, # 借出前 ping 一下,避免拿到失效连接
)几个参数的经验值:
pool_size不要设太大,数据库的连接数是稀缺资源,10–20 通常够一个中型服务;max_overflow是缓冲,不是"越大越好",超过数据库上限一样会失败;pool_pre_ping能避免"连接被数据库单方面断开后,拿到一个死连接"的报错。
SQLite 要不要池
SQLite 是文件数据库,没有"服务端连接数"的限制,简单的池化收益不大。但不要在每次查询时新开连接——尤其是开了 WAL 之后,频繁开关文件句柄反而更慢。
一种实用做法是"每个线程一个连接",或者用一个全局连接加锁(SQLite 本身是串行写的)。
11.4 ORM:它解决什么问题,不解决什么
ORM(Object-Relational Mapping)把"表"映射成"对象",让你用代码而不是 SQL 操作数据:
# SQLAlchemy 风格
book = session.query(Book).filter(Book.category == "文学").first()
print(book.title)好处:
- 类型友好,IDE 能补全;
- 不用手写 SQL,减少拼错列名;
- 迁移、关系加载有配套工具。
坏处:
- 它不保证查询高效。ORM 生成的 SQL 可能比你手写的差很多;
- 出问题时你要懂 SQL 才能看懂它在干什么;
- 复杂查询(多表聚合、窗口函数)用 ORM 写反而更难读。
所以正确的定位是:ORM 是便利层,SQL 是底层能力。简单的增删改查用 ORM,复杂查询直接写 SQL(SQLAlchemy 也支持 session.execute(text(...)))。
如果你只用过 ORM,遇到慢查询会完全无从下手——这也是这门课先把 SQL 讲透的原因。
11.5 N+1:ORM 最经典的性能坑
需求:列出所有订单和对应的客户名。
用 ORM 的"懒加载"写法:
orders = session.query(Order).all() # 1 次查询
for o in orders:
print(o.customer.name) # 每次触发 1 次查询12 张订单 → 1 + 12 = 13 次查询。如果订单有 1000 张,就是 1001 次。这就是 N+1。
问题不在"查询次数多"本身,而在每次查询都有网络往返。本地 SQLite 感觉不出来,连远程 PostgreSQL 时,1000 次往返就是几秒钟。
三种修法
修法一:JOIN 一次取完
rows = session.execute(
text("""
SELECT o.id, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
""")
).all()修法二:预加载(ORM 提供的能力)
orders = session.query(Order).options(joinedload(Order.customer)).all()joinedload 告诉 ORM:"这次把关联对象一起 JOIN 出来",避免循环里逐条查。
修法三:批量查再拼
orders = session.query(Order).all() # 1 次
ids = {o.customer_id for o in orders}
customers = session.query(Customer).filter(Customer.id.in_(ids)).all() # 1 次
by_id = {c.id: c for c in customers}
for o in orders:
print(by_id[o.customer_id].name)两次查询解决,适合关联数据量大、不能一次 JOIN 完的场景。
怎么发现 N+1
- 打开数据库的查询日志,看一次请求打了几条 SQL;
- 如果条数和"数据条数"成正比,基本就是 N+1;
- SQLAlchemy 可以开
echo=True直接打印所有语句。
11.6 事务边界放在应用层
第 10 章讲了事务本身,这里讲它在代码里怎么放。
推荐的结构是:一个业务方法一个事务,在服务层控制,不在每个 DAO 方法里各自 BEGIN。
def create_order(customer_id, items):
with con: # 进入事务
for book_id, qty in items:
con.execute(
"UPDATE books SET stock = stock - ? WHERE id = ? AND stock >= ?",
(qty, book_id, qty),
)
order_id = con.execute(
"INSERT INTO orders (customer_id, ordered_at, status) VALUES (?, ?, ?)",
(customer_id, today(), "pending"),
).lastrowid
con.executemany(
"INSERT INTO order_items (order_id, book_id, quantity, unit_price) "
"VALUES (?, ?, ?, ?)",
[(order_id, b, q, price_of(b)) for b, q in items],
)with con: 在 SQLite 的 Python 驱动里是事务上下文:正常退出时提交,抛异常时回滚。注意它不关闭连接,连接可以继续复用。
如果某个 DAO 自己 BEGIN,嵌套事务的行为会因数据库而异(SQLite 不支持真正的嵌套,PostgreSQL 用 SAVEPOINT)。所以统一在服务层开事务,下层只负责执行语句,是最不容易乱的做法。
11.7 两类必须处理的错误
唯一约束冲突
import sqlite3
try:
con.execute("INSERT INTO customers (name, email) VALUES (?, ?)", ("张三", "zhangsan@example.com"))
except sqlite3.IntegrityError:
print("邮箱已存在")注册接口如果不捕获这个异常,用户会看到一个 500。正确做法是捕获并转成业务提示("该邮箱已注册"),而不是把数据库错误直接暴露给前端——那既是体验问题,也是信息泄露。
锁等待超时
SQLite 在并发写时会报:
sqlite3.OperationalError: database is locked处理方式:
- 缩短事务(第 10 章);
- 设置
timeout(Python 驱动默认 5 秒),让写操作多等一会儿; - 必要时重试,但要区分"可重试"和"不可重试"的错误。
PostgreSQL 对应的是死锁检测后的 deadlock detected,同样需要重试逻辑。
本章小结
| 要点 | 说明 |
|---|---|
| 拼 SQL = 注入 | ' OR 1=1 -- 能绕过登录 |
| 参数化是唯一正解 | 语句与值分开,输入永不参与语法 |
| 占位符因库而异 | SQLite 用 ?,PostgreSQL 用 %s |
executemany 批量提交 | 性能比逐条高一个数量级 |
| 连接池省的是建立连接的成本 | PostgreSQL 连接昂贵,池大小别贪大 |
| ORM 不保证高效 | 简单查询用它,复杂查询写 SQL |
| N+1 = 1 + N 次查询 | 用 JOIN、预加载或批量查修 |
| 事务边界放服务层 | 一个业务动作一个事务 |
| 捕获约束与锁错误 | 转成业务提示,别暴露 500 |
下一章
SQLite 适合学习和单机,但真实服务端项目大多跑在 PostgreSQL 上。最后一章讲迁移:类型怎么对、自增怎么写、大小写为什么会让查询突然查不到、备份怎么做。