Skip to content
Lv1SQL 与数据库进度 11 / 12

第 11 章:让代码安全地访问库 ​

前 10 章你都是在命令行里跟数据库对话。真实项目里,SQL 是代码发出去的——这一章讲代码这一侧最容易出的事:注入、连接、N+1。


学习目标 ​

  • 亲手复现一次 SQL 注入,理解它为什么能绕过登录
  • 会用参数化查询写所有带用户输入的 SQL
  • 知道连接池解决什么问题,SQLite 和 PostgreSQL 的差别在哪
  • 能识别 ORM 的 N+1 问题,并知道怎么修

11.1 先看一次注入是怎么发生的 ​

下面这段代码看起来很正常:

python
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 变成:

sql
SELECT * FROM customers WHERE name = '' OR 1=1 --'

OR 1=1 让条件永远为真,-- 把后面的引号注释掉。结果:整张表的数据全部返回。

如果这是登录查询:

python
sql = f"SELECT * FROM users WHERE username = '{u}' AND password = '{p}'"

在用户名框里输入 admin' --,密码随便填,SQL 就变成:

sql
SELECT * FROM users WHERE username = 'admin' --' AND password = 'xxx'

密码校验被注释掉了,直接以 admin 身份登录。

为什么这不是"过滤特殊字符"能解决的 ​

很多人第一反应是"把单引号转义掉"。这确实能挡住上面这个例子,但绕过方式还有很多:不同数据库的转义规则不一样、编码问题、\ 转义、多字节字符……这是一场没有终点的军备竞赛。

正确的解法不是"过滤输入",而是不让输入参与 SQL 的拼接。


11.2 参数化查询:把数据和语句分开 ​

python
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)%sWHERE id = %s
MySQL(PyMySQL)%sWHERE id = %s
SQLAlchemy:nameWHERE id = :id

写法不同,原理一样:语句是语句,值是值。

多参数与批量 ​

python
# 多个参数:按位置对应
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 字符串,而是拼占位符的数量:

python
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 连接:不要每个请求都开一个 ​

python
con = sqlite3.connect("bookstore.db")

SQLite 的连接很轻(打开一个文件),开销小。但服务端数据库不一样:PostgreSQL 的一个连接对应一个后端进程,建立一个连接要几十毫秒,还要占用内存。

如果每个 HTTP 请求都 connect() 再 close():

  • 高并发时连接数暴涨,数据库直接拒绝(too many connections);
  • 每次请求都付建立连接的成本,QPS 上不去。

连接池的做法是:启动时建好一批连接,请求来了借一个,用完还回去,不真的关闭。

python
# 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 操作数据:

python
# 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 的"懒加载"写法:

python
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 一次取完

python
rows = session.execute(
    text("""
        SELECT o.id, c.name
        FROM orders o JOIN customers c ON c.id = o.customer_id
    """)
).all()

修法二:预加载(ORM 提供的能力)

python
orders = session.query(Order).options(joinedload(Order.customer)).all()

joinedload 告诉 ORM:"这次把关联对象一起 JOIN 出来",避免循环里逐条查。

修法三:批量查再拼

python
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。

python
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 两类必须处理的错误 ​

唯一约束冲突 ​

python
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 上。最后一章讲迁移:类型怎么对、自增怎么写、大小写为什么会让查询突然查不到、备份怎么做。

👉 第 12 章:从 SQLite 到 PostgreSQL

本文阅读--次|全站访问--次|访客--人