Postgres批量插入/更新是注入安全的。也许是一个接受数组的函数？[重复]

Question

本周我正在努力偿还一些技术债务，我突然意识到，我不知道如何使多值插入免受意外或恶意SQL注入。我们在Postgres 11.4上。我有一个测试平台可以使用，其中包括一个大约有26K行的小表，这是我用于测试的一个小表的声明：

BEGIN;

DROP TABLE IF EXISTS "data"."item" CASCADE;

CREATE TABLE IF NOT EXISTS "data"."item" (
    "id" uuid NOT NULL DEFAULT NULL,
    "marked_for_deletion" boolean NOT NULL DEFAULT false,
    "name_" citext NOT NULL DEFAULT NULL,

CONSTRAINT item_id_pkey
    PRIMARY KEY ("id")
);

CREATE INDEX item_marked_for_deletion_ix_bgin ON "data"."item" USING GIN("marked_for_deletion") WHERE marked_for_deletion = true;

ALTER TABLE "data"."item" OWNER TO "user_change_structure";
COMMIT;

我一直在使用多值插入到此表和许多其他表中，如下所示：

BEGIN;
INSERT 
   bundle up hundres or thousands of rows
  ON CONFLICT do what I need
COMMIT or ROLLBACK on the client side

很好用。但是如何使多值语句安全呢？这是我无法弄清楚的。这是我无法很好地推理问题的领域之一。我没有破解东西的胃口、能力或耐心。我想不出漏洞利用毫无意义，作为黑客，我会很糟糕。就此而言，我通常更关心代码中的错误而不是邪恶，因为我更经常遇到错误。

我看到的安全插入的标准建议是使用准备好的声明。INSERT 的预准备语句几乎是一个临时的运行时函数，用于在代码模板上进行插值。对我来说，编写一个实际的函数更简单，就像这样：

DROP FUNCTION IF EXISTS data.item_insert_s (uuid, boolean, citext);

CREATE OR REPLACE FUNCTION data.item_insert_s (uuid, boolean, citext) 
  RETURNS int
AS $$
INSERT INTO item (
    id,
    marked_for_deletion,
    name_)

VALUES
    ($1,$2,$3)

ON CONFLICT(id) DO UPDATE SET 
    marked_for_deletion = EXCLUDED.marked_for_deletion,
    name_ = EXCLUDED.name_;

SELECT 1; -- No clue what to return, but you have to return something.

$$ LANGUAGE sql;

ALTER FUNCTION data.item_insert_s(uuid, boolean, citext) OWNER TO user_bender;

所有这些都有效，我也试过一些计时测试。我截断表，执行多值插入，截断，执行一系列函数调用插入，看看有什么不同。我尝试了多次运行，以不同的顺序执行操作等。这两种情况都以相同的方式使用BEGIN/COMMIT块，因此在任何一个测试中，我都会得到相同数量的事务。测试之间的结果差异比测试内部的结果更大，但多值插入总是更快。祝贺我证实了这一点。

有没有办法安全地进行批量插入和更新？我突然想到，我可以编写一个函数，该函数采用一个或多个数组，解析它，然后在函数内的循环中运行代码。我想对此进行测试，但被Postgres数组语法所困扰。我环顾四周，听起来像是一个对象数组，一个前循环可能正是我所追求的。我环顾四周，这是一个已经解决的主题，但我还没有找到一个关于如何准备插入数据以及解压缩数据的简单示例。我怀疑我将无法使用SQL和普通的unest（），因为1）我想保护输入，2）我可能有函数不在其输入中获取表中的所有字段。

为了让事情简单一点，我可以处理具有固定参数列表的函数和具有固定格式的数组输入。我将为我的各种表编写代码生成器，所以我不需要使Postgres端代码比必要的更复杂。

感谢任何帮助！

注意：我收到一条消息，解释为什么这个问题与我更新的相关问题不同：

改进基于输入数组向上插入的函数

回答：是的，这是同一个起点。在这个问题中，我问的是SQL注入，而在第二个问题中我试图将重点放在数组输入解决方案上。我不太确定什么时候应该提出新的问题，什么时候应该让问题变成多部分的线索。

Answer 1

现在是新南威尔士州远南海岸的早晨，我想我应该再试试这个。我之前应该提到，我们的部署环境是RDS，这使得COPY不那么吸引人。但是传入一个数组，其中每个元素都包含行数据的想法非常吸引人。它很像多值INSERT，但语法糖不同。我对Postgres中的数组进行了一些研究，但总是被语法弄糊涂了。我从一些顶级海报中找到了一些非常优秀的帖子，其中有很多细节需要研究：

https://dba.stackexchange.com/questions/224785/pass-array-of-mixed-type-into-stored-function

https://dba.stackexchange.com/questions/131505/use-array-of-composite-type-as-function-parameter-and-access-it

https://dba.stackexchange.com/questions/225176/how-to-pass-an-array-to-a-plpgsql-function-with-variadic-parameter/

从那里，我得到了一个有效的测试函数：

DROP FUNCTION IF EXISTS data.item_insert_array (item[]);

CREATE OR REPLACE FUNCTION data.item_insert_array (data_in item[]) 
  RETURNS int
AS $$
INSERT INTO item (
    id, 
    marked_for_deletion, 
    name_)

SELECT
    d.id, 
    d.marked_for_deletion,
    d.name_

FROM unnest(data_in) d

ON CONFLICT(id) DO UPDATE SET 
    marked_for_deletion = EXCLUDED.marked_for_deletion,
    name_ = EXCLUDED.name_;

SELECT cardinality(data_in); -- array_length() doesn't work. ¯\_(ツ)_/¯

$$ LANGUAGE sql;

ALTER FUNCTION data.item_insert_array(item[]) OWNER TO user_bender;

要关闭圆圈，下面是一些输入的示例：

select * from item_insert_array(

    array[
        ('2f888809-2777-524b-abb7-13df413440f5',true,'Salad fork'),
        ('f2924dda-8e63-264b-be55-2f366d9c3caa',false,'Melon baller'),
        ('d9ecd18d-34fd-5548-90ea-0183a72de849',true,'Fondue fork')
        ]::item[]
    );

回到我的测试结果，它的性能和我最初的多值插入差不多。我最初发布的另外两种方法，比方说，慢了4倍。（结果非常不稳定，但总是慢得多。）但我仍然有我最初的问题：

这种注射安全吗？

如果没有，我想我需要在PL / pgSQL中用一个FOREACH循环重写它并执行...使用 或 FORMAT 获取注入清理文本处理/插值功能。有人知道吗？

关于此功能，我还有很多其他问题(它是否应该是一个过程，以便我可以管理事务？我如何使输入成为任意数组？返回什么样的结果是合理的？)但我想我得把那些当作他们自己的问题来追究。

感谢任何帮助！