如何向PostgreSQL函数传入ORDER BY参数实现自定义查询排序

核心设计原则：绝对不能将用户传入的原始排序字符串直接拼接到SQL执行语句中，必须通过白名单机制做全链路校验，从根源规避SQL注入风险。

具体实现步骤 #

1. 定义固定的排序规则白名单 #

• 提前枚举所有允许用于排序的字段集合，固定为当前表结构返回的四个字段：id、age、height、weight，不接受任何未在集合内的字段参与排序逻辑
• 提前枚举所有允许的排序方向：仅支持ASC（正序）、DESC（倒序）两种固定方向，不接受其他自定义方向传参
• 白名单校验可以同时在API服务层、数据库函数层做双层拦截，API层过滤明显非法的请求，数据库层兜底校验，避免绕过API层直接访问数据库的风险

2. 改造原PostgreSQL函数的动态排序逻辑 #

保留原函数order_by varchar(64)[]的入参定义，内部不再直接拼接原始入参作为排序规则，而是通过解析+校验+安全拼接的方式生成合法的排序子句，参考内部实现逻辑：

create or replace function get_people (order_by varchar(64)[])
returns table (id bigint, age smallint, height smallint, weight smallint)
as $$
declare
  -- 白名单定义
  allowed_cols text[] := array['id', 'age', 'height', 'weight'];
  allowed_dirs text[] := array['ASC', 'DESC'];
  order_clause text := '';
  col text;
  dir text;
  elem text;
begin
  -- 逐一遍历传入的排序规则做校验
  foreach elem in array order_by loop
    -- 拆分每个排序规则为字段、方向两部分
    col := trim(split_part(elem, ' ', 1));
    dir := upper(trim(split_part(elem, ' ', 2)));
    -- 校验字段合法性，非法参数直接抛出错误终止执行
    if col <> all(allowed_cols) then
      raise exception '非法排序字段%，仅支持: %', col, array_to_string(allowed_cols, ',');
    end if;
    -- 校验方向合法性
    if dir <> all(allowed_dirs) then
      raise exception '非法排序方向%，仅支持ASC/DESC', dir;
    end if;
    -- 用format函数的%I参数做标识符转义，拼接合法排序片段
    order_clause := order_clause || format('%I %s, ', col, dir);
  end loop;
  -- 清理末尾多余的分隔符，生成完整order by子句
  order_clause := rtrim(order_clause, ', ');
  -- 拼接完整查询并执行
  return query execute format('select id, age, height, weight from person order by %s', order_clause);
end
$$ language plpgsql stable;

3. API层的参数透传适配 #

• 前端提交的order_by数组参数，直接通过数据库驱动的参数绑定能力传入PostgreSQL函数，不要在API层自己拼接SQL调用语句
• 在API层加基础格式校验：每个排序规则必须是“字段+空格+方向”的结构，长度不超过64字符，将明显格式错误的请求直接拦截，不打到数据库层

方案优势 #

• 安全性拉满：白名单严格匹配+动态SQL转义的双重防护，彻底杜绝SQL注入风险，完全规避直接拼接原始入参带来的安全漏洞
• 灵活性足够：不需要为每一种排序组合写硬编码分支，支持用户传入任意合法的排序组合，比如示例中的['weight ASC', 'height DESC', 'age ASC', 'id ASC']可以直接解析执行
• 维护成本极低：后续如果需要新增支持的排序字段，只需要在白名单数组中补充对应字段名即可，不需要修改核心查询逻辑

常见踩坑提醒：禁止直接将用户传入的排序字符串原样拼接到SQL语句中，哪怕做了简单的关键字过滤，只要没有做白名单完全匹配，就存在SQL注入被利用的风险。

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