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 混在一起,改一个字段要翻三个地方。下一章把它们拆成三层,并保证接口行为一点不变。