提问人:cptkirkh 提问时间:5/27/2023 最后编辑:Erwin Brandstettercptkirkh 更新时间:5/27/2023 访问量:164
动态 SQL 和 SQL 注入
Dynamic SQL and SQL injection
问:
我被要求研究在 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$;
答:
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 输入时返回 null。STRICT
由于返回 ,因此不要聚合所有行。改为为每个结果行传递一个 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
是的,我想我“借用”了你对多边形函数的想法。感谢您的帮助,这让我走得更远。
评论
json_agg
SETOF json
to_json(e)