Python sqlite3 set_authorizer 怎样限制连接可执行的 SQL 操作

如果应用允许用户提交一段查询语句，单靠字符串检查很容易漏掉大小写、视图、触发器和嵌套表达式。更稳妥的做法是把策略装在具体的 sqlite3.Connection 上，让 SQLite 在准备 SQL 时回调 set_authorizer。回调返回 SQLITE_OK 放行，返回 SQLITE_DENY 拒绝整条语句，返回 SQLITE_IGNORE 则把特定读取替换成 NULL。

官方地址：https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.set_authorizer

先弄清回调到底在判断什么

Python 回调通常接收五个参数：操作码、对象名、列名、数据库名，以及触发器或视图来源。不同操作码下，后几个参数可能是字符串，也可能是 None，所以判断时要先按操作码理解参数含义。

用只读策略限制一个连接的 SQL 操作

下面的例子只允许查询，拒绝常见写操作。它没有把 SQL 当字符串拆解，而是根据 SQLite 传入的操作码处理；如果还需要允许临时表或特定函数，应在白名单里逐项加回，并为每项写清理由。

import sqlite3

def read_only_authorizer(action, arg1, arg2, dbname, source):
    # 这些操作会改变数据或数据库结构，直接拒绝整条语句。
    blocked = {
        sqlite3.SQLITE_INSERT,
        sqlite3.SQLITE_UPDATE,
        sqlite3.SQLITE_DELETE,
        sqlite3.SQLITE_DROP_TABLE,
        sqlite3.SQLITE_ALTER_TABLE,
    }
    if action in blocked:
        return sqlite3.SQLITE_DENY

    # 其他操作先放行；生产环境应按实际查询集合继续收窄。
    return sqlite3.SQLITE_OK

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE reports(id INTEGER, title TEXT)")
con.set_authorizer(read_only_authorizer)

# SELECT 可以准备并执行，INSERT 会在准备阶段被拒绝。
rows = con.execute("SELECT id, title FROM reports").fetchall()
try:
    con.execute("INSERT INTO reports VALUES (?, ?)", (1, "draft"))
except sqlite3.DatabaseError as exc:
    print(type(exc).__name__, exc)

策略只属于 con。如果程序另外打开一个连接，那个连接默认没有同样的 authorizer。连接池复用时要在连接创建或借出流程中统一安装策略，不要假设“同一个数据库文件”会共享授权状态。

按表和列收窄敏感数据读取

SQLITE_READ 可以让策略进一步落到表列。返回 SQLITE_DENY 会让语句失败；返回 SQLITE_IGNORE 则允许语句继续，但对应列值会变成 NULL。如果报表允许读取订单，却不允许读客户手机号，这个模式比执行查询后再遍历结果删字段更早、更明确。

def report_authorizer(action, arg1, arg2, dbname, source):
    # 对敏感列返回 IGNORE，查询结构保留但手机号不会返回原值。
    if action == sqlite3.SQLITE_READ and arg1 == "customers" and arg2 == "phone":
        return sqlite3.SQLITE_IGNORE

    # 禁止任何写入，避免只读报表连接产生副作用。
    if action in {sqlite3.SQLITE_INSERT, sqlite3.SQLITE_UPDATE, sqlite3.SQLITE_DELETE}:
        return sqlite3.SQLITE_DENY

    # 未命中的操作保持默认允许，便于先观察再收窄策略。
    return sqlite3.SQLITE_OK

con.set_authorizer(report_authorizer)

生效时机和工程边界不能忽略

authorizer 在 SQLite 准备 SQL 的过程中被调用，重点是“这条语句准备做什么”，不是替代执行阶段的业务校验。官方 SQLite 文档还提醒，回调不应修改正在被回调的连接；同一连接同时只能有一个 authorizer，重复设置会覆盖前一个，传入 None 可以解除授权。

因此，生产接入可以按下面的检查清单推进：先为每个连接安装策略；再记录操作码、表名和列名用于调试；最后用固定 SQL 集合测试视图、触发器和缓存语句。若 SQL 来自不可信输入，还要结合 SQLite 运行时限制、数据库大小限制、业务身份校验和超时控制。authorizer 是一层操作约束，不是完整的沙箱。

常见问题

set_authorizer 会拦截已经执行到一半的语句吗？

它主要在 SQL 准备阶段检查操作，不能把它理解成逐行监控器。数据库结构变化导致重新准备时可能再次触发，因此长生命周期操作应保持正确的回调仍然安装。

什么时候该用 SQLITE_IGNORE 而不是 SQLITE_DENY？

想让报表继续返回行、只隐藏某个列时用 SQLITE_IGNORE；想让整条语句不可用时用 SQLITE_DENY。前者会让列值变成 NULL，调用方必须能处理这个结果。

解除 authorizer 后还需要重新创建连接吗？

不需要，调用 con.set_authorizer(None) 就能禁用当前连接的回调。但如果连接来自池，建议把启用和解除放在明确的借出、归还边界，避免策略状态泄漏到下一次请求。