Supabase RPC暴露的PL/pgSQL函数：防SQL注入及安全优化咨询

你的核心思路——通过白名单校验限制允许的列名——已经是防范SQL注入的黄金准则，当前代码的基础逻辑是可靠的，但可以补几个细节来消除潜在风险，同时提升鲁棒性：

现有代码的安全优势 #

• 从information_schema.columns获取合法列名：这个系统视图是PostgreSQL维护的可信数据源，普通用户无法篡改，所以生成的白名单是安全的。
• 使用format()的%I占位符：自动对标识符进行转义，即使列名包含特殊字符（比如空格、关键字）也不会引发注入，同时保证SQL语法正确。

需要补全的优化点 #

1. 新增NULL参数校验 #

当传入的criterion为NULL时，criterion = any (valid_columns)会返回NULL，不会触发异常判断，可能导致后续动态SQL出错。直接在白名单校验前加检查：

if criterion is null then
  raise exception 'Criterion cannot be null';
end if;

2. 限定表的Schema #

如果数据库中有多个Schema，只通过table_name='leaderboard'可能会混入其他Schema中同名表的列。加上Schema限定（比如默认的public）：

select array_agg(column_name::text)
into valid_columns
from information_schema.columns
where table_name='leaderboard'
  and table_schema = 'public'; -- 替换成你的实际Schema

3. 限制允许的列数据类型 #

你的函数返回value bigint，如果传入的列不是bigint类型，会导致查询报错。可以在白名单中直接过滤出bigint类型的列：

select array_agg(column_name::text)
into valid_columns
from information_schema.columns
where table_name='leaderboard'
  and table_schema = 'public'
  and data_type = 'bigint'; -- 只允许bigint类型的列

4. 用EXISTS替代ARRAY_AGG（可选优化） #

如果leaderboard表的列很多，array_agg会占用额外内存。改用EXISTS直接校验参数是否在合法列中，更高效：

-- 替换原来的array_agg和if判断
if not exists (
  select 1 from information_schema.columns
  where table_name='leaderboard'
    and table_schema = 'public'
    and data_type = 'bigint'
    and column_name = criterion
) then
  raise exception 'Invalid criterion: %', criterion;
end if;

优化后的完整代码 #

create function top_100_by_criterion(criterion varchar)
returns table(id uuid, rank bigint, username text, value bigint) as
$$
begin
  -- 校验参数非空
  if criterion is null then
        raise exception 'Criterion cannot be null';
  end if;

  -- 校验参数是否为合法的bigint类型列
  if not exists (
    select 1 from information_schema.columns
    where table_name='leaderboard'
      and table_schema = 'public'
      and data_type = 'bigint'
      and column_name = criterion
  ) then
        raise exception 'Invalid criterion: %', criterion;
  end if;

  -- 安全生成动态SQL
  return query execute format(
    'select id, rank, username, %I
    from (
      select l.id, 
             dense_rank() over (order by %I desc) as rank, 
             p.username, 
             %I
      from leaderboard as l 
      inner join profiles as p on l.id = p.id
      order by rank, p.username
    ) as ranked_leader_board
    limit 100',
    criterion, criterion, criterion
  );
end;
$$
language plpgsql;

安全结论 #

你的白名单校验逻辑不会被绕过——因为information_schema.columns的内容由PostgreSQL系统维护，攻击者无法篡改；再加上%I的自动转义，双重保障下完全可以防范SQL注入风险。

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