node-postgres：如何执行“WHERE col IN（）”查询？
node-postgres：如何执行“WHERE col IN（）”查询？
我正在尝试执行类似这样的查询：
SELECT * FROM table WHERE id IN (1,2,3,4)
问题是，我想要过滤的id列表是不固定的，每次执行都可能不同。此外，我需要对这些id进行转义，因为它们可能来自不可信的来源，尽管无论来源的可信度如何，我都会对查询中的任何内容进行转义。
node-postgres似乎只与绑定参数一起使用：client.query('SELECT * FROM table WHERE id = $1', [ id ])；如果我有已知数量的值（client.query('SELECT * FROM table WHERE id IN ($1, $2, $3)', [ id1, id2, id3 ])），这将起作用，但直接使用数组将无效：client.query('SELECT * FROM table WHERE id IN ($1)', [ arrayOfIds ])，因为似乎没有对数组参数进行特殊处理。
根据数组中的项目数量动态构建查询模板，并将ids数组扩展到查询参数数组中（在我的实际情况中，查询参数数组还包含除ids列表之外的其他参数）似乎过于繁琐。在查询模板中硬编码ids列表似乎也不可行，因为node-postgres不提供任何值转义方法。
这似乎是一个非常常见的用例，所以我猜我可能忽视了某些东西，而不是无法在node-postgres中使用常见的IN (values) SQL运算符。
如果有人以比我上面列出的更优雅的方式解决了这个问题，或者如果我确实对node-postgres有所遗漏，请帮助一下。
问题的原因是需要执行一个查询，查询条件是一个动态值列表，即在列中匹配一个任意数组的值。解决方法是使用Postgres的数组转换，并使用ANY函数，这样可以将列与任意数组的值进行匹配，就像写col IN (v1, v2, v3)一样。下面是解决方法的具体内容：
查询语句应该如下所示：
SELECT * FROM table WHERE id = ANY($1::int[])
末尾的$1::int[]可以根据你的"id"列的类型进行更改。例如，如果你的ID的类型是uuid，那么你可以写$1::uuid[]来将参数转换为UUID数组。这样做比编写构造查询字符串的代码更简单，而且可以防止SQL注入。
下面是一个完整的JavaScript示例，使用node-postgres库：
var pg = require('pg');
var client = new pg.Client('postgres://username:password/database');
client.connect(function(err) {
if (err) {
throw err;
}
var ids = [23, 65, 73, 99, 102];
client.query(
'SELECT * FROM table WHERE id = ANY($1::int[])',
[ids], // array of query arguments
function(err, result) {
console.log(result.rows);
}
);
});
关于性能方面，可以通过查看数据库处理查询的方式来了解SQL查询的性能。示例表大约有400行，并且有一个名为"id"的主键，类型为text。
在这两种情况下，Postgres报告的查询计划是相同的：
Bitmap Heap Scan on tests (cost=8.56..14.03 rows=2 width=79)
Recheck Cond: (id = ANY ('{test-a,test-b}'::text[]))
-> Bitmap Index Scan on tests_pkey (cost=0.00..8.56 rows=2 width=0)
Index Cond: (id = ANY ('{test-a,test-b}'::text[]))
根据你的表的大小、是否有索引以及查询的方式，可能会看到不同的查询计划。但对于上述查询，ANY和IN的处理方式是相同的。
需要注意的是，虽然对于使用集合的ANY形式来说是正确的，但对于每个IN ()和= ANY()都有第二种形式，并不完全等价。可以参考这个链接了解更多信息：stackoverflow.com/questions/34627026/…
问题的原因是作者想要执行一个包含动态值列表的"WHERE col IN (
var ids = [1,3,4];
var q = client.query('SELECT Id FROM MyTable WHERE Id = ANY($1::int[])',[ids]);
q.on('row', function(row) {
console.log(row);
})
// 输出：{ id: 1 }
// { id: 3 }
// { id: 4 }
作者提到了一些问题，比如在arr中没有对值进行任何引用，node-postgres库也没有提供任何引用方法。作者希望找到一个“正确”的方法来解决这个问题，而不是实现自己的SQL引用代码。另外，如果继续这个方向，作者更愿意直接嵌入查询模板中的id列表，而不是准备一个数组，然后在服务器端再次解析它。
作者进一步询问了关于“this lacks any quoting of the values in arr”的含义。回答是，如果arr中不仅包含整数，而且包含包括逗号或大括号的字符串，那么代码将无法正常工作或无法正确执行。
作者解释说这只是一个示例，并没有对arr中的值进行任何引用。无论如何，在准备语句之前，您都应该对IN子句参数进行清理。作者的问题实际上是关于如何对IN参数进行清理。由于作者事先不知道有多少个参数，并且node-postgres库没有提供引用方法，因此无法使用绑定参数和引用方法。作者正在寻找一个干净和规范的解决方案来解决这个问题。
某些情况下即使使用参数化查询并且参数在postgres服务器上被解析和转义，也存在安全风险。但是，如果确实存在安全风险，那就有更大的问题了。还某些情况下这似乎是最干净的答案，但是在执行完全相同的查询时，却得到了非常普通的错误信息"Connection terminated"。在postgres日志中也没有任何显示。甚至对于一个字符串数组，它也无法工作。
在node-postgres中执行“WHERE col IN (
var arr = [1, 2, "hello"];
var params = [];
for(var i = 1; i <= arr.length; i++) {
params.push('$' + i);
}
var queryText = 'SELECT id FROM my_table WHERE something IN (' + params.join(',') + ')';
client.query(queryText, arr, function(err, cb) {
...
});
这样做可以实现对参数进行postgres参数化转义。另外，由于我们在Node.js环境中，可以安全地使用原生的map()函数来简化代码：
params = arr.map(function(item, idx) {return '$' + idx});
之前以为将纯文本放入查询中会出大问题，结果发现只是一些美元符号和数字而已-_-'
有一个建议有一个错误，应该是：
var params = arr.map(function(item, idx) {return '$' + (idx+1);});
如果数组中的任何文本元素包含单引号符号'，这个示例将会出错。
对于这个问题，最好使用const params！关于这个问题的更新已经在node-postgres的FAQ中了。下面的示例是可行的：
client.query("SELECT * FROM stooges WHERE name = ANY ($1)", [ ['larry', 'curly', 'moe'] ], ...);
这种方法容易受到SQL注入的攻击。
我不明白为什么这会容易受到SQL注入的攻击。构建的查询字符串必须是形如`SELECT id ... IN ($1, $2, ... $N)`的形式，不可能存在注入的可能性-它只是一个准备好填充的`$`参数化查询。然后通过库来填充实际的值，这种方式天然就能抵御SQL注入（除非库中存在严重的Bug）。
正如上面提到的，map函数很酷，但默认是从零开始的索引；所以一定要调整索引，因为pg需要基于1的索引。
这个解决方案可能更好：[stackoverflow.com/a/29575963/3070547](https://stackoverflow.com/a/29575963/3070547)。大家应该采用最新的约定。