动态 SQL 和 SQL 注入

Dynamic SQL and SQL injection

提问人:cptkirkh 提问时间:5/27/2023 最后编辑:Erwin Brandstettercptkirkh 更新时间:5/27/2023 访问量:164

问:

我被要求研究在 Postgres DB 上的函数中生成动态 SQL 语句。过去,我一直倾向于在应用程序级别生成 SQL,但在这种情况下,我们尝试在数据库本身中执行此操作。

更糟糕的是,我基本上会得到表名和 where 子句作为过滤器。我不会知道列数,因为表格可能会有所不同。

我已经查看了返回 JSON 和使用多态类型返回值。我试图避免对这些查询进行任何 SQL 注入,并想从 Postgres 专家那里知道这个函数在您眼中是否容易受到 SQL 注入的影响?这实际上只是一段愚蠢的代码,用于学习如何在 Postgres 中做事,因为我来自 Oracle 背景。

功能一:

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$;

功能二:

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$;
PostgreSQL PLPGSQL SQL注入 动态SQL

评论

0赞 Bergi 5/27/2023
你真的想要(返回一个数组)还是你想取回一个对象?对于后者,请使用json_aggSETOF jsonto_json(e)

答:

2赞 Erwin Brandstetter 5/27/2023 #1

您的尝试对 SQL 注入是安全的。基本:

一些功能/语法问题仍然存在。

功能 1

CREATE OR REPLACE FUNCTION ksh.get_data_json( _table text, _column text, _value text)
  RETURNS SETOF json 
  LANGUAGE plpgsql AS
$func$
BEGIN
   -- These asserts are optional, but recommended.
   -- Makes the rest simpler & safer, too
   IF _table <> '' THEN  -- all good
   ELSE RAISE EXCEPTION '_table missing!';
   END IF;
   
   IF _column <> '' THEN  -- all good
   ELSE RAISE EXCEPTION '_column missing!';
   END IF;

   IF _value <> '' THEN  -- all good
   ELSE RAISE EXCEPTION '_value missing!';
   END IF;

   RETURN QUERY EXECUTE format(
      'SELECT to_json(%1$I.*) FROM %1$I WHERE %2$I = %3$L'
    , _table, _column, _value
      );
END
$func$;

呼叫(普通):

SELECT * FROM ksh.get_data_json('tbl', 'col1', '1');

使用让您的生活更轻松。format()

我添加了一些断言。或者,使函数 - 使其在 null 输入时返回 nullSTRICT

由于返回 ,因此不要聚合所有行。改为为每个结果行传递一个 JSON 值。SETOF json

我使用 而不是 ,以便列名不能推翻相同的表名。to_json(%1$I.*)to_json(%1$I)

功能 2

看起来受到我的旧帖子的启发:

但还没有完全到位。

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

致电(重要!

SELECT * FROM pg_temp.get_data_poly(NULL::tbl, 'col1', '1');

由于传递列的数据类型未知,因此我们无法安全地将值作为传递。相反,连接一个字符串常量。

相关:

评论

0赞 cptkirkh 5/30/2023
是的,我想我“借用”了你对多边形函数的想法。感谢您的帮助,这让我走得更远。