如何在含ORDER BY参数的EXECUTE ... INTO中防范SQL注入？

1. SQL注入风险判断：正确 #

你当前的函数确实存在SQL注入风险。因为format函数在这里仅做简单字符串拼接，未对参数做安全处理：

• 当orderdir传入类似'ASC; DROP ALL TABLES;'的内容时，拼接后的SQL会变为：

  SELECT * FROM users ORDER BY [你的orderby值] ASC; DROP ALL TABLES;
  

  执行该语句时，数据库会先执行SELECT查询，再执行DROP操作，直接造成数据丢失。

2. 数据库层面的防范方案 #

方案一：用format标识符占位符处理排序字段 #

对orderby参数使用%I占位符（专为SQL标识符设计，如字段名、表名），它会自动对特殊字符转义，避免恶意注入。

方案二：对排序方向做白名单校验 #

orderdir的合法值仅为ASC或DESC，在函数内先校验参数合法性，不符合则抛出错误或使用默认值，彻底阻断恶意输入的可能。

改进后的函数示例 #

CREATE OR REPLACE FUNCTION dummy_ordered_select(orderby text, orderdir text)
   RETURNS users AS
$$
DECLARE result_data users;
        valid_orderdir text := UPPER(orderdir);
BEGIN
    -- 校验排序方向合法性
    IF valid_orderdir NOT IN ('ASC', 'DESC') THEN
        RAISE EXCEPTION 'Invalid sort direction: %', orderdir;
    END IF;
    
    -- 使用%I处理排序字段，确保安全
    EXECUTE format('SELECT * FROM users ORDER BY %I %s', orderby, valid_orderdir) INTO result_data;            
    RETURN result_data; -- 修正原代码中RETURN语句的语法错误
END;
$$
LANGUAGE plpgsql;

额外说明 #

• %I会自动将字段名用双引号包裹，即使字段名包含特殊字符或与SQL关键字重名，也能安全处理。
• 白名单校验是处理排序方向最可靠的方式，因为合法值范围明确，无模糊操作空间。

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