Postgres使用pg_trgm搜索字符串前的输入sanitize及安全防护方案

必备安全防护措施 #

• 强制使用参数化查询（预处理语句），绝对禁止直接拼接用户输入到SQL语句中。这是防范SQL注入最有效的手段，参数化查询会将所有用户输入作为纯值处理，不会被解析为SQL语法的一部分，pg_trgm的相似度匹配、模糊匹配场景都完全支持参数化传递输入值。
  错误写法示例（存在注入风险）：

  # 禁止直接拼接用户输入
  query = f"SELECT * FROM goods WHERE name LIKE '%{user_input}%'"
  

  正确写法示例：

  # 统一使用参数化传递用户输入
  cur.execute("SELECT * FROM goods WHERE name LIKE %s", (f"%{user_input}%",))
  

• 严格限制用户输入长度。pg_trgm的检索性能随输入字符串长度增长快速下降，过长的用户输入可被用于发动拒绝服务攻击，建议根据业务场景将用户检索输入限制在10~100字符区间。
• 最小化业务数据库账号权限。给Web业务使用的数据库账号仅开通必要的SELECT权限，禁止授予CREATE、ALTER、DROP、DELETE等高阶权限，即使出现注入漏洞也能大幅降低攻击影响范围。
• 收回匹配规则控制权。通配符、正则匹配规则统一由后端代码封装处理，不要允许用户直接输入通配符、正则字符控制检索逻辑，如果业务需要模糊匹配，统一由代码层在参数前后拼接%，不要把拼接逻辑暴露给前端。
• 配置检索接口限流和慢查询熔断。对检索接口设置合理的QPS阈值，对pg_trgm相关查询设置执行时间上限，超过阈值自动中断查询，避免数据库资源被恶意请求耗尽。

需要特殊转义的字符 #

• 只要正确使用参数化查询，不需要手动转义任何字符，所有特殊字符的转义都会由数据库驱动自动完成，手动转义反而容易出现疏漏引发安全风险。
• 如果你确实因为特殊场景无法使用参数化查询（非常不推荐），需要针对使用的检索语法转义对应特殊字符：
  • 使用LIKE模糊匹配时，需要转义%、_两个通配符，转义后分别为\%、\_
  • 使用正则匹配相关函数/操作符时，需要转义所有正则特殊字符：., *, +, ?, ^, $, (, ), [, ], {, }, |, \

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