SQLite 字符串中的 NUL 字符问题

背景

SQLite 是一个广泛使用的轻量级嵌入式数据库引擎，它允许在 TEXT 类型的字符串中存储 NUL 字符（ASCII 0x00，Unicode \u0000）。这一特性在某些场景下可能有用，但由于 NUL 字符在 C 语言中通常用作字符串终止符，SQLite 的许多内置函数和命令行工具在处理包含 NUL 的字符串时表现出了不一致甚至令人困惑的行为。本文作者（Hacker News 用户）通过具体示例揭示了这些问题，并提供了检测和清理 NUL 字符的方法。

核心内容

SQLite 允许数据库中的字符串值在任意位置包含 NUL 字符（即 ASCII 0x00）。然而，使用 NUL 会导致以下意外行为：

• length() SQL 函数只计算第一个 NUL 之前的字符数，忽略 NUL 及其后的内容。
• quote() SQL 函数也只显示第一个 NUL 之前的字符。
• 命令行工具（CLI）的 .dump 命令在生成的 SQL 输出中会省略从第一个 NUL 开始及其后的所有文本。事实上，CLI 在所有上下文中都会忽略第一个 NUL 之后的内容。

因此，不建议在 SQL 文本字符串中使用 NUL 字符。

作者给出了一段示例 SQL：

CREATE TABLE t1( a INTEGER PRIMARY KEY, b TEXT );
INSERT INTO t1(a,b) VALUES(1, 'abc'||char(0)||'xyz');
SELECT a, b, length(b) FROM t1;

假设 CLI 设置了 .mode quote，上述 SELECT 的输出为：

1,'abc',3

但若执行：

SELECT * FROM t1 WHERE b='abc';

不会返回任何行。因为 SQLite 知道 t1.b 列实际上存储的是一个 7 字符的字符串 'abc'||char(0)||'xyz'，它与 3 字符的字符串 'abc' 不相等。然而用户可能被 CLI 的输出误导，以为该字符串只有 3 个字符。这看起来像一个 bug，但它就是 SQLite 当前的工作方式。

如果将字符串 CAST 为 BLOB，则可以看到完整的长度。例如：

SELECT a, CAST(b AS BLOB) FROM t1;

结果为：

1,X'6162630078797a'

在 BLOB 输出中，可以清晰地看到第 4 个字符是 NUL（0x00）。

另一种自动检测字符串值 X 是否包含内嵌 NUL 的方法是使用表达式：

instr(X, char(0))

如果该表达式返回非零值 N，则表示在第 N 个字符位置存在一个嵌入的 NUL。因此，统计包含嵌入 NUL 的行数可以这样写：

SELECT count(*) FROM t1 WHERE instr(b, char(0)) > 0;

下面的示例展示了如何从表的一列中移除 NUL 字符及其后的所有文本。如果数据库文件中包含嵌入的 NUL，并希望移除它们，可以运行类似下面的 UPDATE 语句：

UPDATE t1 SET b = substr(b, 1, instr(b, char(0))) WHERE instr(b, char(0));

该页面最后更新于 2022-05-23。

关键要点

• SQLite 在 TEXT 类型中允许 NUL 字符（0x00），但许多函数和 CLI 工具会将其视为隐式的字符串终止符，导致行为与预期不符。
• length() 和 quote() 函数只处理第一个 NUL 之前的部分，忽略 NUL 及其后的字符。
• CLI 的 .dump 以及在所有上下文中都会丢弃第一个 NUL 之后的内容，这会造成数据展示不完整，可能误导用户。
• 数据库实际上会完整存储整个字符串（包括 NUL），但查询时使用等值比较（如 WHERE b='abc'）会正确比较全字符串，不会匹配仅包含前三个字符的文本。
• 将字符串显式 CAST 为 BLOB 可以查看完整的二进制内容，从而确认 NUL 的存在。
• 使用 instr(X, char(0)) 可以检测字符串中是否包含内嵌 NUL，并确定其位置。
• 可以通过 UPDATE ... SET ... substr(b, 1, instr(b, char(0))) 来移除 NUL 及其后的所有文本，从而清理数据。
• 官方文档不建议在 SQL 文本字符串中使用 NUL 字符，以避免这些令人困惑的行为。

意义与影响

这一发现对 SQLite 用户具有重要的实践意义。首先，它提醒开发者在使用 SQLite 存储包含 NUL 的文本时（例如序列化的二进制数据或某些特殊编码的字符串）需要格外小心，因为内置函数和 CLI 工具可能会产生误导性的输出。其次，它揭示了 SQLite 内部实现与 C 语言字符串处理惯例之间的微妙差异：SQLite 的存储引擎并不以 NUL 作为字符串终止符，但部分 C 语言编写的接口或工具却会如此处理。这可能导致数据丢失或逻辑错误，尤其是在备份（.dump）或数据导出时。最后，文章提供的检测和清理方法为已有数据库中意外混入 NUL 字符的用户提供了实用的解决方案。总体而言，该内容强调了对数据库行为进行充分测试的重要性，并建议尽可能避免在 TEXT 字段中嵌入 NUL 字符，或者改用 BLOB 类型存储含 NUL 的二进制数据。