本周我正在努力偿还一些技术债务，我突然意识到我不知道如何使多值插入免受意外或恶意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 数组语法弄糊涂了。我环顾四周，听起来像是一组对象和一个 foreach 循环可能正是我想要的。我环顾四周，这是一个已经解决的主题，但我还没有找到一个关于如何准备要插入的数据以及解压缩数据的简单示例。我怀疑我无法使用 SQL 和普通的 unnest()，因为 1) 我想保护输入，2) 我可能有函数不在其输入中获取表中的所有字段。
为了使事情变得更容易一些，我对具有固定参数列表的函数和具有固定格式的数组输入感到满意。我将为我的各种表编写代码生成器，因此我不需要使 Postgres 端代码变得过于复杂。
感谢您的任何帮助！
注意：我收到一条消息，解释为什么这个问题与我较新的相关问题不同：
改进基于输入数组的 UPSERT 的功能
答：是的，这是相同的起点。在这个问题中，我问的是SQL注入，在第二个问题中，我试图关注数组输入解决方案。我不太确定何时拆分新问题，以及何时让问题变成多部分线程。
这是新南威尔士州远南海岸的早晨，我想我会再试一试。我之前应该提到我们的部署环境是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 循环重写它并执行......USING 或 FORMAT 以获取那里的注入清理文本处理/插值功能。有人知道吗？
我对这个函数还有很多其他问题(它应该是一个程序，以便我可以管理交易吗？如何使输入任意数组？返回的明智结果是什么？但我想我必须把这些作为他们自己的问题来追求。
感谢您的任何帮助！