SQLite 预处理语句：prepare、bind、step、finalize

预处理语句到底是什么

当你把一个 SQL 字符串交给 SQLite 时，在任何数据真正开始流动之前，它都要做不少工作：分词、解析、检查表和列是否存在、规划如何执行，再把执行计划编译成 SQLite 虚拟机的字节码。直到这时，查询才真正开始运行。

在“编译成字节码”这一步停下来，并把结果保留住，你得到的就是预处理语句（prepared statement）。编译好的程序里有一些槽位，也就是占位符，实际的值稍后再填进去。你可以用不同的值把同一个编译好的程序执行很多次，也可以安全地用来自不可信输入的值执行它。

可以这样类比：一种是每次做饭都把菜谱从头念一遍给对方听，另一种是只教对方一次菜谱，之后每天只需告诉他用什么食材。

生命周期：prepare、bind、step、finalize

每种语言的每个 SQLite 驱动，封装的都是同样四个 C API 调用。即使你永远不写 C，知道这些名字也有帮助，因为错误信息和文档都用这套术语：

1. sqlite3_prepare_v2：把 SQL 字符串编译成一个语句句柄。
2. sqlite3_bind_*：填入占位符的值（每种类型一个函数）。
3. sqlite3_step：运行程序。对于 SELECT，反复调用它来逐行读取。对于 INSERT/UPDATE/DELETE，调用一次就完成工作。
4. sqlite3_finalize：用完后释放编译好的程序。

在两次执行之间，sqlite3_reset 会把一个已执行完的语句倒回开头，这样你就可以重新绑定并再次执行，而不必重新预处理。

SQL 中的占位符

在 SQL 字符串里，每个要填值的位置都用占位符标出来，而不是把值直接拼进去。SQLite 支持几种形式：

-- Anonymous, positional:
INSERT INTO users (name, email) VALUES (?, ?);

-- Numbered:
INSERT INTO users (name, email) VALUES (?1, ?2);

-- Named:
INSERT INTO users (name, email) VALUES (:name, :email);
INSERT INTO users (name, email) VALUES (@name, @email);
INSERT INTO users (name, email) VALUES ($name, $email);

在驱动层面的代码里，? 最常见。当参数比较多，或者同一个值出现不止一次时，命名占位符（:name）的可读性更好。每个项目选定一种风格，然后坚持使用。

千万不要用字符串拼接来构造查询：

-- DO NOT DO THIS:
"INSERT INTO users (name) VALUES ('" + user_input + "')"

这是通往 SQL 注入的路，而且还会让你接下来要读到的字节码复用失效。

用 SQL 演示的示例

为了不借助宿主语言也能看清其中的机制，下面只用 SQLite 提供的 SQL 功能来演示和 prepare/bind/step 等价的过程。建一张表，插入几行数据，其中参数位置用字面量填充：

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

INSERT INTO users (name, email) VALUES ('Ada', 'ada@example.com');
INSERT INTO users (name, email) VALUES ('Boris', 'boris@example.com');

SELECT id, name, email FROM users ORDER BY id;

在真实的应用里，你不会把值直接写在语句中，而是用 ?, ? 占位符对 INSERT 只 prepare 一次，然后为每个用户 bind 名字和邮箱，再 step。每次调用的字节码都完全相同，变化的只有绑定的值。

复用语句（性能上的收益）

下面是驱动允许你写出的模式。这是伪代码，每种语言的写法略有不同，但结构是通用的：

-- prepared once:
INSERT INTO users (name, email) VALUES (?, ?);

-- then, in a loop:
--   bind(1, name)
--   bind(2, email)
--   step()
--   reset()

预处理只解析和编译一次 SQL。每次循环只需运行字节码，并把值复制到槽位里。对于批量插入（比如导入 100,000 行），这比执行 100,000 条分别解析的语句快得多，尤其是包在同一个事务里时，往往能快一个数量级。

一个常见的错误是在循环内部调用 prepare，这样就把全部好处都扔掉了。在循环外预处理，在循环内绑定和执行。

为什么这是安全的做法

绑定的参数并不是替换进 SQL 的字符串。它们是通过带类型的槽位（整数槽、文本槽、blob 槽）交给字节码程序的值。SQLite 永远不会把它们重新当作 SQL 解析，所以任何值都不可能改变查询的结构。

对比一下：

-- Vulnerable. If user_input is:  '); DROP TABLE users;--
-- the query becomes destructive.
"SELECT * FROM users WHERE name = '" + user_input + "'"

-- Safe. user_input is bound as a TEXT value and only ever
-- compared as a string, no matter what it contains.
SELECT * FROM users WHERE name = ?;

即使 user_input 是 '); DROP TABLE users;--，第二种写法也是安全的。SQLite 会老老实实地去找名字恰好是这个（奇怪）字符串的用户，找不到，返回零行。查询的结构不会因为值的内容而发生任何改变。

我们会在后面的文档里深入讨论注入，但这里的结论是：预处理语句不只是防御 SQL 注入的一种手段，它就是那个根本的防御手段。

返回行的语句

对于 SELECT，step 每次返回一行。驱动通常会一直循环，直到它返回“完成”：

CREATE TABLE products (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products (name, price) VALUES
    ('Notebook', 4.50),
    ('Pen',      1.20),
    ('Eraser',   0.80);

SELECT name, price FROM products WHERE price < 2.00 ORDER BY price;

在应用代码中，驱动会用 ? 代替 2.00 来 prepare 这条 SELECT，绑定阈值，然后循环调用 step，每次读取一行。最后一行读完后，step 报告执行完成，驱动要么 reset 这条语句（以便用新的阈值再次执行），要么 finalize 它。

别忘了 finalize

预处理语句是 SQLite 内部的一小块内存分配。泄漏它们会占用内存，更重要的是，它们会在数据库上持有内部锁，可能阻塞其他写操作。每个驱动都提供了自动清理的方式，比如 Python 的上下文管理器、C# 的 using 代码块、C++ 的 RAII，你应该用上它们：

• Python 的 sqlite3 会在游标被垃圾回收时 finalize，但显式调用 cursor.close() 更干净。
• better-sqlite3（Node）会在 Statement 被垃圾回收时 finalize；长期存在的预处理语句没有问题。
• 在原生 C 里，你得自己调用 sqlite3_finalize。忘了调用就是一个真正的 bug。

经验法则：只要你预处理了它，就得有东西来 finalize 它。

什么时候你可能不需要自己处理

你很少会直接调用 sqlite3_prepare_v2。高级驱动会把 connection.execute("SELECT ... WHERE id = ?", (42,)) 替你转换成 prepare/bind/step/finalize。理解这个生命周期的意义在于：

• 当你看到“statement is busy”或“cannot operate on a finalized statement”这类错误时，你能明白发生了什么。
• 当你在紧密循环里插入数据时，你会知道要缓存长期使用的预处理语句。
• 即使字符串拼接看起来很诱人，你也会本能地写出参数化查询。

ORM 和查询构建器在这方面走得更远。它们负责构建 SQL、管理预处理语句，再把带类型的结果交给你。但在底层，用的都是同样的四个调用。

下一篇：绑定参数

前面我们只是抽象地讨论了占位符。接下来会详细介绍绑定这一侧：位置参数与命名参数、类型处理、NULL，以及把真实的应用数据传进查询时会遇到的一些小坑。