PostgreSQL中如何有效防范SQL注入？

SQL注入的核心概念与产生逻辑 #

SQL注入是非常经典的数据库安全风险，本质成因非常简单：开发过程中把用户可控的不可信输入，直接拼接进了待执行的SQL语句字符串中，数据库解析执行SQL时，会把输入里夹带的恶意SQL语法当成正常的业务指令执行，最终引发越权访问、数据泄露、数据被篡改/删除甚至服务器被接管的后果。
举个最常见的错误写法示例，比如用户登录场景直接拼接前端传入的参数：

// 错误示范：直接拼接用户输入构造SQL
query := fmt.Sprintf("SELECT id, username FROM users WHERE username = '%s' AND password = '%s'", 
    req.Username, req.Password)
db.Query(query)

如果攻击者在用户名输入框传入内容 ' OR 1=1 -- ，最终拼出来的SQL会变成：

SELECT id, username FROM users WHERE username = '' OR 1=1 -- ' AND password = 'xxx'

PostgreSQL里--是单行注释符，后面的密码校验逻辑会被直接注释掉，1=1是恒真条件，攻击者不需要知道任何账号密码就能拿到全量用户数据，这就是最典型的注入触发流程。

PostgreSQL场景下可落地的有效防范手段 #

• 第一优先级：全场景强制使用参数化查询（预编译语句）
  这是成本最低、防护可靠性最高的方案，没有之一。不管你用什么编程语言、什么数据库驱动，只要是涉及动态参数传入的SQL，绝对不要手动拼接字符串，把SQL语句模板和参数分离开传给数据库驱动。
  PostgreSQL支持两种参数占位符形式，一种是驱动通用的?/%s，另一种是数据库原生支持的编号占位符$1、$2...$n，参数传入时驱动会自动做合规转义，数据库在预编译阶段会先确定SQL的完整语法结构，后续传入的参数只会被当做纯字面量处理，完全不会被解析成SQL语法，从根源上阻断注入的可能。
  正确写法示例：

  # 正确示范：参数单独传入，不做字符串拼接
  cursor.execute(
      "SELECT id, username FROM users WHERE username = %s AND password = %s",
      (req.Username, req.Password)
  )
  

• 动态传入标识符（表名、列名、排序字段）时必须做白名单校验，必要时用内置函数转义
  表名、列名、排序规则这类SQL标识符没法用预编译占位符传参，遇到这类动态需求时，绝对不要直接把用户传入的内容拼进SQL：首先提前梳理业务允许传入的标识符范围做白名单，用户传入值不在白名单内直接拒绝请求；如果场景特殊必须做动态拼接，使用PostgreSQL内置的format()函数搭配%I占位符处理标识符，它会自动按照PostgreSQL的语法规则给标识符加双引号、转义特殊字符，不要自己手写转义函数。
  正确的动态标识符写法示例：

  -- 用%I处理动态传入的排序字段，自动完成转义，参数通过USING传入做预编译处理
  EXECUTE format('SELECT * FROM orders WHERE user_id = $1 ORDER BY %I DESC', sort_col) USING user_id;
  

• 严格遵循数据库账号最小权限原则
  给业务服务连接数据库的账号分配刚好满足业务需求的最小权限，禁止给业务账号授予superuser权限，按需分配对应表的SELECT/INSERT/UPDATE权限，默认不给DROP、ALTER、TRUNCATE、访问系统表、执行高危扩展的权限。就算极端场景下出现注入漏洞，受限的账号权限也能把攻击影响降到最低，攻击者没法执行删库、遍历全库数据这类高危操作。
• 谨慎使用高风险属性的函数与扩展
  定义存储过程/自定义函数时，非必要不要加SECURITY DEFINER属性（该属性会让函数以定义者的权限执行，容易引发提权风险），如果必须使用，要严格校验所有传入参数，禁止在函数内部做不可信输入的SQL拼接；非必要不要启用pg_execute_server_program这类可执行系统命令的扩展，dblink、postgres_fdw这类跨库连接扩展也要严格管控访问权限，避免被注入利用做横向移动。
• 生产环境禁止返回原始数据库报错信息
  PostgreSQL抛出的原始报错会携带库表结构、字段名、类型等敏感信息，攻击者可以通过报错回显逐步猜解数据库结构完成注入，生产环境要拦截所有数据库原始报错，只给前端返回通用的业务错误提示。

注意：不要迷信自定义特殊字符过滤、全局替换单引号这类“土方法”，这类方案很容易被编码绕过、特殊语法绕过，防护稳定性远不如参数化查询。

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