postgresql - 如何在 Postgres 函数中使用文本输入作为列名?
问题描述
我正在使用 Postgres 和 PostGIS。尝试编写一个根据给定参数选择特定列的函数。
WITH
在将结果表转换bytea
为返回之前,我正在使用语句创建结果表。
我需要帮助的部分是$4
部分。我试过它如下所示,$4::text
并且两者都给我输入的文本值而不是表中的列值,如果cols=name
这样我从查询名称而不是表中的实际名称返回。我也尝试data($4)
过输入错误。
代码是这样的:
CREATE OR REPLACE FUNCTION select_by_txt(z integer,x integer,y integer, cols text)
RETURNS bytea
LANGUAGE 'plpgsql'
AS $BODY$
declare
res bytea;
begin
WITH bounds AS (
SELECT ST_TileEnvelope(z, x, y) AS geom
),
mvtgeom AS (
SELECT ST_AsMVTGeom(ST_Transform(t.geom, 3857), bounds.geom) AS geom, $4
FROM table1 t, bounds
WHERE ST_Intersects(t.geom, ST_Transform(bounds.geom, 4326))
)
SELECT ST_AsMVT(mvtgeom, 'public.select_by_txt')
INTO res
FROM mvtgeom;
RETURN res;
end;
$BODY$;
调用函数的示例:
select_by_txt(10,32,33,"col1,col2")
参数 cols 可以是从 1 开始的多个列名,并且不受上述限制。cols
在调用函数之前,将检查内部列的名称是否为有效列。
解决方案
将多个列名作为串联字符串传递以进行动态执行迫切需要去污。我建议使用一个VARIADIC
函数参数,并使用正确引用的标识符(quote_ident()
在这种情况下使用):
CREATE OR REPLACE FUNCTION select_by_txt(z int, x int, y int, VARIADIC cols text[] = NULL, OUT res text)
LANGUAGE plpgsql AS
$func$
BEGIN
EXECUTE format(
$$
SELECT ST_AsMVT(mvtgeom, 'public.select_by_txt')
FROM (
SELECT ST_AsMVTGeom(ST_Transform(t.geom, 3857), bounds.geom) AS geom%s
FROM table1 t
JOIN (SELECT ST_TileEnvelope($1, $2, $3)) AS bounds(geom)
ON ST_Intersects(t.geom, ST_Transform(bounds.geom, 4326))
) mvtgeom
$$, (SELECT ', ' || string_agg(quote_ident (col), ', ') FROM unnest(cols) col)
)
INTO res
USING z, x, y;
END
$func$;
db<>在这里摆弄
%I
用于format()
处理单个标识符的格式说明符。您必须为多个标识符投入更多工作,尤其是对于可变数量的 0-n 标识符。此实现引用每个列名,并且仅在,
已传递任何列名时添加 a。所以它适用于所有可能的输入,甚至根本没有输入。注意VARIADIC cols text[] = NULL
作为最后一个输入参数,默认值为 NULL:
有关的:
在这种情况下,列名区分大小写!
打电话给你的例子(重要!):
SELECT select_by_txt(10,32,33,'col1', 'col2');
替代语法:
SELECT select_by_txt(10,32,33, VARIADIC '{col1,col2}');
更具启发性的调用,带有第三列名称和恶意(尽管徒劳)意图:
SELECT select_by_txt(10,32,33,'col1', 'col2', $$col3'); DROP TABLE table1;--$$);
About that odd third column name and SQL injection:
About VAIRADIC
parameters:
- Return rows matching elements of input array in plpgsql function
- Pass multiple values in single parameter
Using an OUT
parameter for simplicity. That's totally optional. See:
What I would not do
If you really, really trust the input to be a properly formatted list of 1 or more valid column names at all times - and you asserted that ...
the names of the columns inside cols will be checked before calling the function that they are valid columns
You could simplify:
CREATE OR REPLACE FUNCTION select_by_txt(z int, x int, y int, cols text, OUT res text)
LANGUAGE plpgsql AS
$func$
BEGIN
EXECUTE format(
$$
SELECT ST_AsMVT(mvtgeom, 'public.select_by_txt')
FROM (
SELECT ST_AsMVTGeom(ST_Transform(t.geom, 3857), bounds.geom) AS geom, %s
FROM table1 t
JOIN (SELECT ST_TileEnvelope($1, $2, $3)) AS bounds(geom)
ON ST_Intersects(t.geom, ST_Transform(bounds.geom, 4326))
) mvtgeom
$$, cols
)
INTO res
USING z, x, y;
END
$func$;
(How can you be so sure that the input will always be reliable?)
推荐阅读
- android - 出现键盘时按钮向上移动
- javascript - React Router 无法识别路由,将我重定向到空白屏幕
- c# - MLAgents 代理在培训方面并没有变得更好
- react-native - [网络错误]:TypeError:网络请求失败
- pandas - 如何在 Pandas 中对时间序列进行切片
- html - 需要保持在所有 lLayers 前面的 Box-Shadow
- git - 使用cherry-pick 或merge 将错误修复的更改分为两个分支?
- angular - PrimeNG 表返回空白行
- sql-server - 如何从 DBCC USEROPTIONS 返回特定设置?
- python - 如何使用 itertools 迭代范围?