SQLite 搜索百分号却返回所有商品：LIKE 通配符的定位与修复

场景与现象

后台有一个商品标题搜索框，产品约定是“包含输入文字”。运营想找标题里带 50% 的商品，却同时搜到了 500g；输入 _ 后，大部分非空标题都出现在结果里。接口已经使用 SQL 参数绑定，代码审查也没有发现字符串拼接。

下面复现的是搜索语义错误：输入中的特殊字符进入了 LIKE 模式。本文以 Python 标准库 sqlite3 和 SQLite 为实验环境，不需要连接业务数据库。

先把问题缩到一条查询

常见实现是：

def search_broken(connection, tenant_id, keyword):
    """复现未经模式转义的包含搜索，仅用于故障对照。"""
    return connection.execute(
        "SELECT id FROM product WHERE tenant_id = ? AND title LIKE ? ORDER BY id",
        (tenant_id, f"%{keyword}%"),
    ).fetchall()

对同一租户准备两条标题：辣椒50%优惠、辣椒500g。搜索 50% 时，绑定值是 %50%%，两条都会命中。搜索 % 时，绑定值变成 %%%，所有非 NULL 标题都符合条件。

排查时按这个顺序核实：

1. 在合成数据上重放请求，确认异常随 %、_ 出现；普通汉字搜索是否正常。
2. 看最终 SQL 模板和绑定参数的构造位置，区分“包含搜索”和“允许用户写模式”两种产品契约。
3. 对比结果的业务主键，不能只看结果条数或第一页截图。
4. 确认租户、权限和状态条件仍在服务端执行。搜索修复不能替代权限过滤。

生产排查记录请求标识、耗时、返回行数及是否含特殊字符即可；未经脱敏的用户搜索原文不应直接写进日志。

原因：绑定参数仍然是模式

SQLite 的 LIKE 用 % 匹配任意长度字符序列，用 _ 匹配一个字符。ESCAPE 可以指定一个字符，使这些符号按字面值参与匹配。SQLite 表达式文档

参数绑定让输入作为 SQL 的值传入；这个值随后仍由 LIKE 按模式解释。两层处理都需要正确，不能因为使用了占位符就认定搜索语义已经符合产品约定。Python sqlite3 参数绑定说明

本例固定使用 ! 作为转义字符。转换规则为：! → !!，% → !%，_ → !_，再在整个结果两端添加 %。必须先处理输入原本的 !，否则后加的转义前缀可能再次被转义。

用户输入	绑定的模式	预期语义
50%	%50!%%	包含文字 50%
a_b	%a!_b%	包含文字 a_b
!%_	%!!!%!_%	包含连续文字 !%_

不要删掉这些字符，也不要手工处理单引号后拼接 SQL。前者改变搜索内容，后者放弃了参数化边界。

完整实现与可重复实验

将下面整段保存为 like_search_demo.py，用 Python 3.10 或更新版本运行。它只创建内存数据库，不访问网络，不修改磁盘数据库。测试使用合成商品标题，每项测试单独建库并关闭连接。

import sqlite3
import unittest


def build_contains_pattern(keyword: str) -> str:
    """构造字面包含模式，保留输入空格，拒绝空词及不可支持的输入。"""
    if not isinstance(keyword, str):
        raise TypeError("搜索词必须是字符串")
    if not 1 <= len(keyword) <= 128:
        raise ValueError("搜索词长度必须在 1 到 128 个字符之间")
    if "\x00" in keyword:
        raise ValueError("搜索词不能包含空字符")
    escaped = keyword.replace("!", "!!").replace("%", "!%").replace("_", "!_")
    return f"%{escaped}%"


def search_products(
    connection: sqlite3.Connection, tenant_id: int, keyword: str
) -> list[int]:
    """在已授权租户内搜索标题，最多返回一百个稳定排序的主键。"""
    if type(tenant_id) is not int or tenant_id <= 0:
        raise ValueError("租户标识必须是正整数")
    pattern = build_contains_pattern(keyword)
    rows = connection.execute(
        "SELECT id FROM product "
        "WHERE tenant_id = ? AND title LIKE ? ESCAPE '!' "
        "ORDER BY id LIMIT 100",
        (tenant_id, pattern),
    ).fetchall()
    return [row[0] for row in rows]


class LiteralSearchTests(unittest.TestCase):
    """同时验证命中、排除和输入失败路径，防止修复扩大查询范围。"""

    def setUp(self) -> None:
        self.connection = sqlite3.connect(":memory:")
        self.addCleanup(self.connection.close)
        self.connection.execute(
            "CREATE TABLE product ("
            "id INTEGER PRIMARY KEY, tenant_id INTEGER NOT NULL, title TEXT)"
        )
        self.connection.executemany(
            "INSERT INTO product VALUES (?, ?, ?)",
            [
                (1, 1, "辣椒50%优惠"),
                (2, 1, "辣椒500g"),
                (3, 1, "a_b"),
                (4, 1, "axb"),
                (5, 1, "惊喜!%_组合"),
                (6, 1, "O'Reilly"),
                (7, 2, "辣椒50%优惠"),
                (8, 1, None),
                (9, 1, ""),
                (10, 1, " 促销 "),
            ],
        )

    def test_original_defect(self) -> None:
        rows = self.connection.execute(
            "SELECT id FROM product WHERE tenant_id = ? AND title LIKE ? ORDER BY id",
            (1, "%50%%"),
        ).fetchall()
        self.assertEqual(rows, [(1,), (2,)])

    def test_percent_is_literal(self) -> None:
        self.assertEqual(search_products(self.connection, 1, "50%"), [1])
        self.assertEqual(search_products(self.connection, 1, "%"), [1, 5])

    def test_underscore_is_literal(self) -> None:
        self.assertEqual(search_products(self.connection, 1, "a_b"), [3])
        self.assertEqual(search_products(self.connection, 1, "_"), [3, 5])

    def test_escape_character_and_combination(self) -> None:
        self.assertEqual(search_products(self.connection, 1, "!"), [5])
        self.assertEqual(search_products(self.connection, 1, "!%_"), [5])
        self.assertEqual(build_contains_pattern("!%_"), "%!!!%!_%")

    def test_normal_text_and_no_match(self) -> None:
        self.assertEqual(search_products(self.connection, 1, "辣椒"), [1, 2])
        self.assertEqual(search_products(self.connection, 1, "不存在"), [])

    def test_quote_and_sql_fragment_are_values(self) -> None:
        self.assertEqual(search_products(self.connection, 1, "O'Reilly"), [6])
        self.assertEqual(search_products(self.connection, 1, "' OR 1=1 --"), [])

    def test_tenant_scope(self) -> None:
        self.assertEqual(search_products(self.connection, 2, "50%"), [7])

    def test_invalid_keyword(self) -> None:
        for keyword in ("", "x" * 129, "a\x00b"):
            with self.subTest(keyword_length=len(keyword)):
                with self.assertRaises(ValueError):
                    search_products(self.connection, 1, keyword)
        with self.assertRaises(TypeError):
            build_contains_pattern(None)
        self.assertEqual(len(build_contains_pattern("x" * 128)), 130)

    def test_spaces_are_preserved(self) -> None:
        self.assertEqual(search_products(self.connection, 1, " 促销 "), [10])
        self.assertEqual(search_products(self.connection, 1, " "), [10])

    def test_invalid_tenant_and_result_limit(self) -> None:
        for tenant_id in (0, -1, True, "1"):
            with self.subTest(tenant_id=tenant_id):
                with self.assertRaises(ValueError):
                    search_products(self.connection, tenant_id, "辣椒")
        self.connection.executemany(
            "INSERT INTO product VALUES (?, ?, ?)",
            [(100 + index, 1, "批量商品") for index in range(105)],
        )
        self.assertEqual(
            search_products(self.connection, 1, "批量"), list(range(100, 200))
        )


if __name__ == "__main__":
    unittest.main(verbosity=2)

执行命令：

python like_search_demo.py

运行结果应为 Ran 10 tests、OK。第一项故意保留故障查询，证明测试数据确实能触发问题；后续测试才验证修复后的语义。NULL 和空标题也在样本中，搜索百分号不能让它们混入结果。

tenant_id 必须来自服务端完成授权后的上下文，示例中的正整数检查只验证格式。LIMIT 100 是演示接口的返回上限，并不代表数据库最多扫描一百行；正式接口还需要明确分页或截断提示。

证明测试能拦住旧缺陷

在实验副本中，把 escaped = ... 那一行临时改成 escaped = keyword，其余代码保持原样，再运行测试。百分号、下划线以及组合字符测试必须失败；如果仍然全绿，先检查是否运行了正确文件、是否收集到测试，再谈上线。

恢复原实现后重新运行，确认十项通过。这个验证比“输入普通商品名能查出来”更有价值，因为普通文字无法区分两种实现。

本次发布前，在 Windows、Python 3.14.6、SQLite 3.50.4 上执行了完整十项测试，全部通过；恢复未转义实现后，相关回归断言失败。未安装 Ruff，因此未执行 Ruff 格式与静态检查；同时进行了 Python 语法、Markdown 围栏和 UTF-8 检查。

上线和回滚要检查的边界

• 先确认原来的搜索框是否允许高级通配符。若已有用户依赖这种功能，应另设明确的模式搜索入口，不能静默改变现有契约。
• 列表查询、数量统计、导出筛选应共用同一模式构造函数和 ESCAPE 约定，否则会出现列表十条而统计一百条的分歧。
• 本例不自动清除空格；若产品要求忽略首尾空格，应在接口层明确该规则并增加测试。空词、NUL 和超长输入在查询前失败，接口层应转成统一的参数错误响应。
• 这次转义修复保留了 SQLite 默认的大小写行为；默认 LIKE 对 ASCII 大小写通常不敏感，不能因此推断完整 Unicode 大小写匹配也相同。需要这类能力时，应单独定义、验证检索规则。SQLite LIKE 大小写说明
• %关键词% 仍需单独做大表性能评估。修复准确性之后，用脱敏样本检查执行计划、扫描量、查询耗时和并发，不能用本例的内存测试替代容量验证。
• 若发布后发生语义争议，可以撤回新入口或回滚本次应用变更；回滚会恢复原来的通配符行为，应同步通知使用方。此修复没有表结构与数据迁移。

这里没有验证生产权限系统、ORM 转义行为、分页协议、大表执行计划、ICU 扩展或其他数据库。迁移到 MySQL、PostgreSQL 时，应在目标驱动和数据库上重跑同类用例，核对占位符和转义规则。

总结

搜索故障需要同时验证输入如何绑定、绑定值又如何被运算符解释。对于约定为字面包含的搜索，固定转义字符、先转义用户输入、再添加外层通配符，并保留参数绑定和权限条件。把 %、_、转义字符及混合输入加入回归测试，才能避免一次看似无害的模式构造让查询范围悄悄扩大。