node-postgres:如何执行"WHERE col IN(<动态值列表>)".询问? [英] node-postgres: how to execute "WHERE col IN (<dynamic value list>)" query?

问题描述

我正在尝试执行如下查询:

I'm trying to execute a query like this:

SELECT * FROM table WHERE id IN (1,2,3,4)

问题是我要过滤的ID列表不是恒定的，并且每次执行时都必须不同.我还需要转义这些id，因为它们可能来自不受信任的来源，尽管我实际上将转义查询中的所有内容，而不管该来源的可信度如何.

The problem is that the list of ids I want to filter against is not constant and needs to be different at every execution. I would also need to escape the ids, because they might come from untrusted sources, though I would actually escape anything that goes in a query regardless of the trustworthiness of the source.

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 ])，因为似乎没有对数组参数的任何特殊处理.

node-postgres appears to work exclusively with bound parameters: client.query('SELECT * FROM table WHERE id = $1', [ id ]); this will work if I had a known number of values (client.query('SELECT * FROM table WHERE id IN ($1, $2, $3)', [ id1, id2, id3 ])), but will not work with an array directly: client.query('SELECT * FROM table WHERE id IN ($1)', [ arrayOfIds ]), as there does not seem to be any special handling of array parameters.

根据数组中的项数动态构建查询模板，并将ids数组扩展为查询参数数组(在我的实际情况下，除了ids列表之外，它还包含其他参数)似乎是不合理的负担.硬编码查询模板中的ID列表似乎也不可行，因为node-postgres不提供任何值转义方法.

Building the query template dynamically according to the number of items in the array and expanding the ids array into the query parameters array (which in my actual case also contains other parameters besides the list of ids) seems unreasonably burdensome. Hard-coding the list of ids in the query template seems not viable either, as node-postgres does not provide any value escaping methods.

这似乎是一个非常常见的用例，所以我的猜测是我实际上正在忽略某些东西，而不是不可能将常见的IN (values) SQL运算符与node-postgres一起使用.

This seems like a very common use-case, so my guess is that I'm actually overlooking something, and not that it is not possible to use the common IN (values) SQL operator with node-postgres.

如果有人以比我上面列出的方式更优雅的方式解决了这个问题，或者如果我真的缺少关于node-postgres的东西，请帮忙.

If anybody has solved this problem in a more elegant manner than those I listed above, or if I'm really missing something about node-postgres, please help.

推荐答案

我们之前在github问题列表上已经看到了这个问题.正确的方法是根据数组动态生成参数列表.像这样:

We've seen this question before on the github issues list. The correct way is to dynamically generate your list of parameters based on the array. Something like this:

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参数化的转义.

That way you get the postgres parameterized escaping.

这篇关于node-postgres:如何执行"WHERE col IN(<动态值列表>)".询问?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！