如何在Python 3.12中使用Psycopg 3参数化防止SQL注入-SQL-PHP中文网

psycopg 3防sql注入须用%(name)s命名参数或%s位置参数配合cursor.execute(sql, params)，表名列名等标识符必须用sql.identifier+白名单校验，禁用字符串拼接；批量插入用executemany()或execute_batch()；连接和游标务必用with管理。

如何在python 3.12中使用psycopg 3参数化防止sql注入

Psycopg 3的参数化查询语法和旧版完全不同

Python 3.12 中用 psycopg 3.x 防 SQL 注入，核心是彻底放弃字符串拼接和 %s 占位符风格（那是 psycopg 2 的写法），改用 %(name)s 命名参数或位置参数 %s（注意：不是 Python 的字符串格式化，而是由 psycopg 自己解析）。如果混用 f-string 或 .format() 拼接 SQL，哪怕只拼一个变量，就等于开门放 SQL 注入进来。

常见错误现象：ProgrammingError: can't adapt type 'dict' 或执行时抛出 UndefinedColumn，往往是因为把参数当成了 SQL 字符串的一部分传进去，而不是通过 params 显式传递。

必须用 cursor.execute(sql, params)，其中 params 是 dict 或 tuple，不能是字符串
命名参数写法："SELECT * FROM users WHERE id = %(id)s AND status = %(status)s"，传参用 {"id": 123, "status": "active"}
位置参数写法："SELECT * FROM users WHERE id = %s AND status = %s"，传参用 (123, "active")
表名、列名、ORDER BY 子句等**不能参数化**——psycopg 不支持，需用白名单校验或 sql.Identifier

动态列名/表名必须用 `sql.Identifier` 安全校验

SQL 参数化只适用于值（WHERE 右侧、INSERT 的 VALUES 等），不适用于标识符（如表名、字段名、排序字段）。直接拼接用户输入的列名会立刻导致注入。psycopg 3 提供了 sql.Identifier 和 sql.SQL 来安全组合：

from psycopg import sql
<h1>✅ 安全：用户输入只用于 Identifier，且自动转义</h1><p>table_name = "users"
order_by = "created_at"
query = sql.SQL("SELECT * FROM {} ORDER BY {} DESC").format(
sql.Identifier(table_name),
sql.Identifier(order_by)
)
cursor.execute(query)</p>

常见错误现象：KeyError 或 SyntaxError 报错提示“unexpected token”，往往是把 sql.Identifier("user; DROP TABLE users") 这类恶意输入没做白名单过滤就直接用了。psycopg 的 Identifier 不过滤语义，只转义语法——它防的是引号闭合，不防逻辑恶意。

对用户可控的表名/列名，先查白名单：if order_by not in ["created_at", "name", "email"]: 再进 sql.Identifier
sql.SQL 用于拼接固定 SQL 片段，sql.Identifier 仅用于单个标识符，不能传路径或带点的字段（如 "public.users" 要拆成两个 Identifier）
不要试图用 sql.Literal 替代 Identifier——它只处理值，会加引号，导致列名被当成字符串字面量

批量插入必须用 `executemany()` 或 `execute_batch()`

一次插多行时，别写循环调 execute()，也别用 VALUES (%s), (%s), (%s) 手动拼参数占位符——后者容易漏数、难维护，且 psycopg 3 不推荐手动展开。正确做法是用 executemany()（简单场景）或 execute_batch()（高性能场景）。

python 查询技能

查询客流数据，输出JSON格式，可直接导入Bitable等可视化工具

下载

常见错误现象：插入 1000 行耗时几秒，或报 MemoryError，大概率是用了字符串拼接 + 大量 %s 占位符；更糟的是把整个元组列表用 str() 转成字符串再塞进 SQL。

executemany("INSERT INTO logs (msg, level) VALUES (%s, %s)", [("hi", "info"), ("fail", "error")]) —— 简单可靠，适合 ≤ 1k 行
超 1k 行建议用 execute_batch(cursor, "INSERT ...", seq_of_params, page_size=500)，减少网络往返
所有参数仍必须是值，不能在 seq_of_params 里塞 SQL 片段或表达式

连接和游标要显式关闭，否则参数化失效风险升高

Python 3.12 的垃圾回收更激进，但 psycopg 3 的连接和游标不是纯内存对象——它们持有底层 socket 和服务器会话。如果忘记 close() 或没用 with 语句，可能导致连接池混乱、参数绑定缓存错乱，极端情况下后继查询的参数被前一个未关闭游标的残留状态污染。

常见错误现象：同一段代码，第一次执行正常，第二次执行报 TypeError: argument of type 'NoneType' is not iterable，或参数值“错位”（比如 name 字段插进了 email 列）。

始终用 with connection.cursor() as cur:，确保游标自动关闭
连接建议用 with psycopg.connect(...) as conn:（psycopg 3.1+ 支持上下文协议）
若手动管理，务必在 finally 块中调 cursor.close() 和 connection.close()，不能只依赖 __del__

最易被忽略的一点：psycopg 3 默认启用 prepare_threshold（自动预编译），这会让参数绑定行为更严格——但也意味着首次执行失败时，后续同结构查询可能复用损坏的预编译计划。所以初始化连接后，最好确认 connection.prepare_threshold 设为 None（禁用）或一个合理值（如 5），并在测试中覆盖首次执行路径。