跳到正文
零到全栈 · 卷 6 · 数据与状态

SQLite 实战与注入

本合集目录
本章目录

单册说明:本文件是卷 6 的第 3 章,可独立阅读。前置:第 2 章(表与 SQL)。本章把 sqlite3 模块的用法收成几张表,然后用一次真实的删库演示说明"拼接字符串"有多危险,最后讲什么时候该建索引、怎么看查询计划。

项 内容
所属卷 卷 6 · 数据与状态(第 3 / 8 章)
前置章节 6.2 表与 SQL 基础
本章产出 一套能用的连接/查询写法 · 一次注入现场与修复 · 两份查询计划对照
对应代码 app/db.py、app/storage/notes.py、app/migrations/001_init.sql 里的索引
配图 无(后续统一制图)
读完的判定 看到任何一条手写 SQL,能立刻指出它是不是参数化的,以及它能不能走索引

6.3.1 一句话结论:所有值都走参数,所有名字都走白名单

sqlite3 只有一个 API 需要背下来:execute(sql, params)。SQL 是代码,值不是代码,两者永远分开传。

先看现场。你加了一个"按标题清理测试数据"的接口,图省事这么写:

title = request.json()["title"]
con.execute(f"DELETE FROM notes WHERE title = '{title}'")
con.commit()

有人传了个 title:

' OR 1=1 --

实际执行的语句变成:

DELETE FROM notes WHERE title = '' OR 1=1 --'

-- 之后全被当注释,条件是"标题等于空 或者 1=1"——恒真。执行前后数一下:

删除前 3 行,删除后 0 行

三行摘录全没了。日志里留下的是这样一句:

127.0.0.1 - "DELETE /api/notes/cleanup HTTP/1.1" 204

一个 204,看起来像"成功清理了 0 条"。

6.3.2 原理与方法

sqlite3 的五个必记点

项 怎么写 为什么
连接 sqlite3.connect(path, timeout=5.0, isolation_level=None) timeout 是等锁时间;isolation_level=None 关掉隐式事务(第 5 章细讲)
执行 con.execute(sql, params) 连接对象可以直接执行,不必先建 cursor
取结果 fetchone() / fetchall() fetchone 拿一行或 None,fetchall 拿列表
行转字典 con.row_factory = sqlite3.Row 之后 row["title"] 和 dict(row) 都能用
提交 con.commit() 或 with transaction(con) 不提交就等于没写(第 5 章)

cursor 仍然存在,它适合"同一条语句执行多次":

cur = con.cursor()
for title in titles:
    cur.execute("INSERT INTO notes(user_id,title,content) VALUES (?,?,?)", (1, title, "x"))

绝大多数时候用不着,con.execute 会隐式建一个用完即弃的 cursor。

参数化的两种写法

写法 样子 传参
qmark WHERE user_id = ? AND id = ? 元组 (uid, nid),顺序必须对上
named WHERE user_id = :uid AND id = :nid 字典 {"uid": 1, "nid": 2},顺序无关

选择标准很简单:参数超过四个、或者同一条语句里同一个值要用两次,就用 named。本书统一用 qmark,因为它短,而且顺序错了会立刻报错:

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 2, and there are 1 supplied.

这句报错看着烦,其实是好事——它让你在开发时就发现参数数量不对,而不是上线后数据错位。

为什么参数化能挡住注入

关键在于解析顺序。拼接字符串时,数据库看到的是"一整句话",它无法区分哪部分是用户写的、哪部分是代码写的;参数化时,SQL 模板先被解析成语法树(这一步叫预编译),值在之后才作为"数据"填进去。

拼接 参数化
数据库先看到 一整句含用户输入的 SQL 固定模板 ... WHERE title = ?
用户输入的地位 语法的一部分,引号能被闭合 一个字符串值,引号是内容
传 ' OR 1=1 -- 的结果 条件恒真,全表被删 查"标题恰好等于这串字符"的行,0 行
需要转义吗 要,而且很容易漏 不需要,驱动负责

实测修复后的行为:

SQL 模板: DELETE FROM notes WHERE title = ?  参数: ("' OR 1=1 --",)
rowcount = 0
剩余行数 = 2

rowcount = 0 就是答案:数据库把它当成一个普通的、没人用过的标题。

参数化管不了的部分

占位符只能替"值",不能替"名字"。下面这些位置必须拼字符串:

  • 表名、列名:SELECT * FROM {table}
  • 排序方向:ORDER BY {column} {ASC|DESC}
  • LIMIT 后面的数字在某些驱动里也要拼

所以规则是:值走参数,名字走白名单。 白名单就是一个写死的允许集合:

SORTABLE = {"created_at": "n.created_at", "title": "n.title"}
sort_key = request.args.get("sort", "created_at")
column = SORTABLE.get(sort_key, "n.created_at")   # 不在表里就用默认值
sql = f"SELECT ... ORDER BY {column} DESC"

用户传 title 得到按标题排,传 ; DROP TABLE notes 得到默认排序——拼进去的永远是你自己写过的字符串。

有个隐形的坑要先说:ORDER BY ? 不报错。它会被当成"按一个常量排序",结果就是"没排序":

ORDER BY title: [(2, 'Python 的 with'), (1, 'SQLite 的事务'), (3, '别人的摘录')]
ORDER BY ?    : [(1, 'SQLite 的事务'), (2, 'Python 的 with'), (3, '别人的摘录')]

第二行是插入顺序,看起来"也能显示",用户点了"按标题排序"却毫无反应。这类"不报错的错"最难查。

还有两个地方会拼 SQL,而且经常被忽略:

场景 长什么样 安全的前提
动态 SET(局部更新) ", ".join(f"{k} = ?" for k in fields) fields 的键来自代码里的固定字段名,不来自请求体
动态 WHERE(多条件搜索) 逐个 if 往列表里 append("t.name = ?") 拼的是写死的条件片段,值仍然进参数

本书第 4 章之后的 storage/notes.py 里的 update_note() 就是第一种:字段名来自 NotePatch.model_dump(exclude_unset=True),也就是 Pydantic 类里声明过的那几个,用户没法往里面塞一个 id = 999。判据只有一条:拼进去的字符串必须是你在源码里写死的。

什么时候建索引

索引是一份额外的、排好序的目录。它让"按某列找行"从"翻完整本书"变成"查目录"。代价是每次写入都要更新目录。

该建 不该建
WHERE 里高频出现的列 几乎不查的列
JOIN 的连接列(note_tags.note_id、note_tags.tag_id) 只有几十行的表(全表扫描本来就快)
ORDER BY 的列,且和过滤条件能组成复合索引 区分度极低的列(如布尔值,一半行都一样)
唯一约束自动带的索引(users.email) 已经有等价索引的前缀列

复合索引要记"最左前缀":INDEX (user_id, id DESC) 能服务 WHERE user_id = ? ORDER BY id DESC,但单独查 WHERE id = ? 用不上它。所以索引的列顺序要和查询的写法对齐。

用 EXPLAIN QUERY PLAN 看它到底走没走

在两万行的表上跑(实测):

EXPLAIN QUERY PLAN SELECT * FROM notes WHERE user_id = 7 ORDER BY id DESC LIMIT 20;
有索引 输出 怎么读
没有 SCAN notes 全表扫描,逐行判断
有 (user_id, id DESC) SEARCH notes USING INDEX idx_notes_user_id (user_id=?) 走索引定位,不再扫全表
关键词 含义 该做什么
SCAN 全表扫 数据量大时考虑加索引
SEARCH ... USING INDEX 用上了索引 正常
USING COVERING INDEX 只读索引就够,没回表 更好
USE TEMP B-TREE FOR ORDER BY 排序要额外建临时结构 索引的列顺序和 ORDER BY 没对齐
USING INTEGER PRIMARY KEY 按 rowid 直取 最快

同一条查询,200 次耗时从 0.050s 降到 0.019s。这个差距不算惊人,因为两万行还太小;索引的收益随数据量放大,十万行以上会是几十倍。别用几行数据测索引——SQLite 在小表上会主动选择全表扫描,EXPLAIN 的结论会误导你。

本项目最后只建了三条索引,每条都对应一个真实查询:

索引 服务的查询 说明
users.email 的唯一索引 登录时 WHERE email = ? 由 UNIQUE 约束自动创建,不用手写
idx_notes_user_id (user_id, id DESC) 列表页 WHERE user_id = ? ORDER BY id DESC LIMIT 20 过滤和排序一次搞定,不再建临时 B 树
idx_note_tags_tag (tag_id) 按标签反查摘录、标签统计 note_tags 的联合主键已经覆盖了"按 note_id 查"的方向

三条就是全部。索引不是越多越好:每一条都要在每次写入时维护,写多读少的表上尤其不划算。

LIKE 与全文检索的取舍

需求 用什么 原因
标题前缀匹配(LIKE 'Python%') 普通索引 + LIKE 前缀能走索引,实测 USING COVERING INDEX
任意位置包含(LIKE '%Python%') 全表扫描,或 FTS5 前导 % 让索引失效,只能扫
几千条以内的模糊搜索 LIKE '%x%' 就够 简单,几毫秒
上万条、要按相关度排序、要分词 FTS5 虚拟表 支持中文需要额外分词器,配置成本明显上升

本项目的量级(个人几千条)用 LIKE 完全够。第 8 章做性能优化时再考虑 FTS5,而且要先有"真的慢了"的证据。

6.3.3 动手:命令行一次,Python 一次,注入一次

第一步:命令行看库

Ubuntu 服务器上有 sqlite3 命令(卷 7 会装);Windows 上没有,用标准库自带的极简版:

# Ubuntu / macOS(装了 sqlite3)
sqlite3 data/dudao.db "SELECT COUNT(*) FROM notes;"

# Windows 或没装 CLI 的机器
python -m sqlite3 data/dudao.db "SELECT COUNT(*) FROM notes;"

两者都输出:

(3,)

注意 python -m sqlite3 不支持 .tables、.schema 这类点命令(实测会报 Error: unknown command or invalid arguments: "tables")。要看表结构,用 SQL 自己查:

python -m sqlite3 data/dudao.db "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"

第二步:注入现场(请在一个临时库上做)

# demo_inject.py —— 故意写错,只为看清后果
import sqlite3, shutil
shutil.copy("data/dudao.db", "data/demo.db")
con = sqlite3.connect("data/demo.db")

user_input = "' OR 1=1 --"
sql = f"DELETE FROM notes WHERE title = '{user_input}'"
print("实际执行的 SQL:")
print("  " + sql)

before = con.execute("SELECT COUNT(*) FROM notes").fetchone()[0]
con.execute(sql)
con.commit()
after = con.execute("SELECT COUNT(*) FROM notes").fetchone()[0]
print(f"删除前 {before} 行,删除后 {after} 行")

输出:

实际执行的 SQL:
  DELETE FROM notes WHERE title = '' OR 1=1 --'
删除前 3 行,删除后 0 行

再跑一次修复版:

sql = "DELETE FROM notes WHERE title = ?"
cur = con.execute(sql, (user_input,))
con.commit()
print("rowcount =", cur.rowcount)
print("剩余行数 =", con.execute("SELECT COUNT(*) FROM notes").fetchone()[0])
rowcount = 0
剩余行数 = 2

为什么是 2 而不是 3:上一步已经删空了,中间补插了两条做恢复。这就是注入的破坏力——它不需要任何权限,只要一句拼错的 SQL。

第三步:索引前后各看一次

-- 建索引前
EXPLAIN QUERY PLAN SELECT * FROM notes WHERE user_id = 1 ORDER BY id DESC;
-- (4, 0, 158, 'SCAN notes')

CREATE INDEX idx_notes_user_id ON notes(user_id, id DESC);

-- 建索引后
EXPLAIN QUERY PLAN SELECT * FROM notes WHERE user_id = 1 ORDER BY id DESC;
-- (5, 0, 90, 'SEARCH notes USING INDEX idx_notes_user_id (user_id=?)')

索引写在迁移文件里(001_init.sql),不要手工在线上库敲。原因在第 5 章:手工敲的东西,换台机器就没了。

想确认索引真的存在,别靠记忆:

python -m sqlite3 data/dudao.db "SELECT name, tbl_name FROM sqlite_master WHERE type='index'"

输出里应该能看到 idx_notes_user_id、idx_note_tags_tag,以及 UNIQUE 约束自动生成的 sqlite_autoindex_users_1。名字里带 sqlite_ 前缀的是系统自己维护的,不要手动删。

6.3.4 三个坑

坑一:把 ORDER BY 也参数化了,排序静默失效

症状:前端点"按标题排序",接口 200,数据也返回了,但顺序没变。日志里 SQL 长得完全正确。

成因:写成了 ... ORDER BY ?,参数传 "title"。占位符只能替值,这里被当成"按字符串常量 'title' 排序"——每一行的排序键都相同,等于没排。SQLite 不报错。

修法:排序字段用白名单映射,见上文 SORTABLE。同时给前端一个固定枚举(created_at / title),别让它传任意字段名。

坑二:索引建了,EXPLAIN 还是 SCAN

症状:明明给 title 建了索引,LIKE 查询还是全表扫。

成因:三个常见原因——① 用了前导 %(LIKE '%事务%'),索引按前缀排序,从中间匹配用不上;② 列被函数包住(WHERE lower(title) = 'x'),索引存的是原值;③ 复合索引的列顺序和查询对不上(建的是 (created_at, user_id),查的是 WHERE user_id = ?)。

修法:先 EXPLAIN QUERY PLAN 确认,再动索引。前导 % 就接受全表扫,或上 FTS5;函数包列就建"表达式索引"(CREATE INDEX ... ON notes(lower(title)));列顺序不对就重建索引。不要凭感觉加索引——加了不用的索引只增加写入成本。

坑三:CREATE INDEX 执行了,重启后不见了

症状:在脚本里跑完 CREATE INDEX,同一进程里查询变快了;换个进程再连,索引没了。

成因:Python 默认的隐式事务。如果这条 CREATE INDEX 之前发生过一次失败的 INSERT(抛了 IntegrityError),事务还开着,CREATE INDEX 被卷进这个事务,而 close() 对未提交事务的处理是回滚。实测复现过:建完索引关连接,再打开,sqlite_master 里没有它。

修法:isolation_level=None + 显式提交(第 1 章的 db.py 已经这么写),或者每条 DDL 后跟一句 con.commit()。更彻底的做法是把所有 DDL 交给迁移脚本,由它统一在事务里执行并记录版本。判断"刚才那条到底提交了没有",最直接的办法是关掉连接重开一次再查——同一连接里的"看到了"不算数。

本章验收

  • 能用命令行和 Python 各查出一次 notes 的行数
  • 在临时库上完整复现"' OR 1=1 -- 删空全表",并看到 删除前 3 行,删除后 0 行
  • 用参数化改写后,rowcount 为 0,数据不丢
  • 能说出哪三个位置不能参数化,以及白名单怎么写
  • 能在 EXPLAIN QUERY PLAN 的输出里指出 SCAN 与 SEARCH ... USING INDEX 的差别

自测

Q1:为什么 DELETE FROM notes WHERE title = ? 传入 ' OR 1=1 -- 之后只会删 0 行?

看答案 因为参数化时 SQL 模板先被编译,`?` 是一个"值的位置",驱动把整串 `' OR 1=1 --` 当作一个普通字符串传进去,里面的单引号是内容的一部分,不会被当成语法符号去闭合前面的引号。最终条件是"标题恰好等于这 9 个字符",库里没有这样的标题,所以 0 行。注入的本质是"数据越界成了代码",参数化把这堵墙砌在编译之前。

Q2:表名必须拼字符串,怎么保证安全?

看答案 用白名单:代码里维护一个"允许的表名/列名 → 实际 SQL 片段"的映射,用户传来的值只用来查这个字典,查不到就用默认值,永远不把用户输入直接拼进 SQL。白名单的好处是"允许集合"写在代码里,可以 review、可以测试;黑名单(过滤 `;`、`--`)永远漏,因为 SQL 的语法花样太多。

Q3:为什么说"用几行数据测索引效果"会得出错误结论?

看答案 SQLite 的查询优化器会估算成本:当表很小,全表扫描可能比"查索引再回表"更快,于是它主动放弃索引,`EXPLAIN` 里就是 `SCAN`。这不代表索引没用,只代表"这张表还不值得用"。要验证索引的价值,得灌到接近真实的量级(比如两万行以上),再对比执行计划和耗时。

下一步

到这里,SQL 会写了、注入挡住了。但所有这些代码还挤在一个文件里:路由、业务、SQL 混在一起,改一个字段要翻三个地方。下一章把它们拆成三层,并保证接口行为一点不变。

→ 卷6-04-分层重构.md