PostgreSQL动态SQL函数的SQL注入风险排查咨询

问题背景 #

需要在PostgreSQL数据库端实现动态SQL生成，输入包含表名、查询条件列及对应值，涉及的表结构不固定。考虑返回JSON和多态类型两种方案，现分析以下两个函数的SQL注入风险：

函数1：返回JSON的动态查询函数 #

函数代码 #

CREATE OR REPLACE FUNCTION ksh.get_data_json( p_table text,
    p_column text,
    p_value text)
    RETURNS SETOF json 
    LANGUAGE 'plpgsql'

AS $BODY$
DECLARE
    query TEXT := 'SELECT json_agg(e) FROM (SELECT * FROM ' ||quote_ident(p_table);
BEGIN
    IF p_column IS NOT NULL THEN
        query := query || ' WHERE ' || quote_ident(p_column) || ' = ' ||quote_literal(p_value)||')e';
    END IF;
    
    RETURN QUERY EXECUTE query;

END;
$BODY$;

注入风险分析 #

该函数不存在SQL注入漏洞，防护逻辑有效：

• 表名p_table和列名p_column均通过quote_ident()处理，会将输入转义为合法的PostgreSQL标识符，避免恶意标识符（如users; DROP TABLE xxx;）触发注入。
• 参数值p_value通过quote_literal()转义为合法的SQL字符串字面量，防止类似' OR 1=1 --的条件注入。

非注入类问题 #

当p_column为NULL时，拼接后的SQL语句缺少闭合的)e，会导致语法错误。修复方式调整初始SQL拼接逻辑，确保结构完整：

-- 修正后的初始query
query TEXT := 'SELECT json_agg(e) FROM (SELECT * FROM ' || quote_ident(p_table) || ')e';
-- IF块仅拼接WHERE条件
IF p_column IS NOT NULL THEN
    query := query || ' WHERE ' || quote_ident(p_column) || ' = ' || quote_literal(p_value);
END IF;

函数2：多态类型返回的动态查询函数 #

函数代码 #

CREATE OR REPLACE FUNCTION ksh.get_data_poly(_tbl_type anyelement, _col text, _value text)
  RETURNS SETOF anyelement
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('
      SELECT *
      FROM   %s   WHERE ' || quote_ident(_col) ||' = '|| quote_literal(_value)||
      'ORDER  BY 1'
    , pg_typeof(_tbl_type))
   USING  _col,_value;
END
$func$;

注入风险分析 #

该函数不存在直接的SQL注入漏洞，但代码写法不规范，存在冗余和潜在维护风险：

• 列名_col用quote_ident()处理、参数值_value用quote_literal()处理，这两处的注入防护有效。
• 冗余问题：USING _col,_value子句无实际作用，SQL语句中未使用$1、$2占位符接收参数，属于无效代码。
• 不规范点：使用format函数的%s占位符处理表类型名，虽pg_typeof()返回合法类型名，但规范上应使用%I（标识符占位符）确保特殊场景下的转义安全。

优化建议 #

遵循PostgreSQL动态SQL最佳实践，改用USING子句传递参数值，避免直接拼接字面量，同时修正format占位符：

CREATE OR REPLACE FUNCTION ksh.get_data_poly(_tbl_type anyelement, _col text, _value text)
  RETURNS SETOF anyelement
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('
      SELECT *
      FROM   %I
      WHERE  %I = $1
      ORDER  BY 1',
      pg_typeof(_tbl_type), _col)
   USING _value;
END
$func$;

这种写法既保留注入防护能力，又提升了代码可维护性和执行效率（参数可被PostgreSQL复用，无需重复转义）。

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