如何优化PostgreSQL含LIKE %的SQL语句以避免SQL注入风险

核心优化原则 #

所有SQL注入的根源都是用户可控的输入和SQL语句直接拼接，不管是用加号拼接字符串还是用字符串格式化符提前生成完整SQL，都存在风险。最优解决方案是统一使用数据库驱动自带的*参数化查询（预编译语句）*能力，让驱动自动完成参数转义，从根源阻断注入。

对应问题代码的优化方案 #

1. 带LIKE通配符的查询优化 #

原代码直接将用户可控的searchString拼接到SQL语句中，攻击者只要构造特殊字符就能篡改SQL逻辑。优化时要把通配符和搜索内容合并为单个参数传入，不要拼到SQL模板里：

# psycopg2 驱动中占位符统一使用 %s，由驱动自动处理转义
cur.execute("SELECT officialid from OFFICIAL WHERE username LIKE %s", ('%' + searchString + '%',))

注：此处的%s是驱动的参数占位符，不是Python的字符串格式化符，不会提前把参数拼到SQL里，完全避免注入风险。

2. UPDATE业务语句优化 #

原代码提前用Python的字符串格式化将参数拼到SQL中，即使是数值类型参数也存在注入风险。优化时所有变量都通过execute的第二个参数传入，不要提前做字符串拼接：

# 所有参数都用占位符，不需要区分数值/字符串类型，驱动自动适配
sql = "UPDATE EVENT SET eventname = %s, sportid = %s, referee = %s, judge = %s, medalgiver = %s WHERE eventid = %s"
cur.execute(sql, (event_name, s[0][0], r[0][0], j[0][0], m[0][0], event_id))

额外优化建议 #

• 如果频繁使用首尾带%的模糊查询，可开启PostgreSQL的pg_trgm扩展，给对应字段建GIN索引，大幅提升模糊查询性能，且完全不影响参数化的安全逻辑。
• 若存在需要动态拼接表名、字段名的场景，不要直接使用用户输入的内容，必须做白名单校验，只有在预定义白名单内的表名/字段名才能使用。
• 不要自己实现参数转义逻辑，客户端自行转义很容易遗漏边界场景，优先使用官方驱动的参数化能力。
• 数据库账号遵循最小权限原则，应用使用的账号只分配必要的业务表增删改查权限，禁止赋予DROP、ALTER、CREATE等高危操作权限，降低注入发生后的损失范围。

内容的提问来源于stack exchange，提问作者Chloe