JavaScript (Postgres DB) - 如何在 WHERE IN ( ) 子句中使用带有数组作为参数的准备语句

Question

我目前正在使用来自 http://vitaly-t.github.io/pg-promise/Database.html 的数据库类并尝试在我的 Postgres 数据库上使用 PreparedStatment 实现更新语句，同时将数组传递给 WHERE IN 子句？

const updatePreparedStatment = new PS('prepared-statement', 'UPDATE mytable SET "MESSAGE"=$1 WHERE "ID" IN ($2)', ["dummy update", ["1","2","3"]]);

Answer 1

在node-postgres的FAQ中有描述https://github.com/brianc/node-postgres/wiki/FAQ#11-how-do-i-build-a-where-foo-in--query-to-find-rows-matching-an-array-of-values

如何构建 WHERE foo IN (...) 查询以查找与值数组匹配的行？node-postgres 支持将简单的 JavaScript 数组映射到 PostgreSQL 数组，因此在大多数情况下，您可以像传递任何其他参数一样传递它。

client.query("SELECT * FROM stooges WHERE name = ANY ($1)", [ ['larry', 'curly', 'moe'] ], ...);

请注意 = ANY 是另一种写 IN (...) 的方式，但与 IN (...) 不同的是，当您将数组作为查询参数传递时，它将按照您预期的方式工作。

如果您事先知道数组的长度，您可以将其展平为 IN 列表:

// passing a flat array of values will work:
client.query("SELECT * FROM stooges WHERE name IN ($1, $2, $3)", ['larry', 'curly', 'moe'], ...);

...但是当 = ANY 与 JavaScript 数组一起使用时几乎没有什么好处。

如果您使用的是旧版本的 node-postgres，或者您需要创建更复杂的 PostgreSQL 数组(复合类型的数组等)而 node-postgres 无法处理，您可以使用动态生成数组文字SQL，但在执行此操作时要格外小心 SQL 注入(inject)。以下方法是安全的，因为它会生成带有查询参数和扁平化参数列表的查询字符串，因此您仍在使用驱动程序对参数化查询(“准备好的语句”)的支持来防止 SQL 注入(inject):

var stooge_names = ['larry', 'curly', 'moe'];
var offset = 1;
var placeholders = stooge_names.map(function(name,i) { 
    return '$'+(i+offset); 
}).join(',');
client.query("SELECT * FROM stooges WHERE name IN ("+placeholders+")", stooge_names, ...);

希望对您有所帮助，因为谷歌无法找到它