Recommended way to use ANY?

[mmkal]

This works - is it safe?

await db.query("SELECT * FROM mytable WHERE id = ANY('{$1:csv}')", [1, 2, 3])

Or is there another recommended way of getting SELECT * FROM mytable WHERE id = ANY('{1,2,3}')?

The docs give an example of how to use IN with the csv filter, but I am migrating some existing code that uses ANY and I want to be sure I can get to parity before considering changing queries. Also ANY is more versatile, so I think there's a real use case.

[vitaly-t]

Is your question about how to format parameters differently? Please clarify what's the issue with the current code. This library is agnostic of the SQL syntax, as in it doesn't care if you use IN or ANY there. There is no such thing as recommended for your case specifically.

[mmkal]

Thank you for the response (and the library!)!

Sorry, I should have clarified, I meant how to make sure it is safe as far as SQL injection is concerned. Say, instead of hardcoded numbers [1, 2, 3] it was a list of strings that came straight from an untrusted client:

app.use((req, res) => {
  const result = await db.query("SELECT * FROM mytable WHERE id = ANY('{$1:csv}')", req.body.ids)
  res.send(result)
})

Having tried it, I see that it's not safe:

const req = { body: { ids: ["normal string", "'string with quotes'"] } }
await db.query("SELECT * FROM mytable WHERE id = ANY('{$1:csv}')", req.body.ids)

This results in a query with a syntax error, and doesn't even seem to include the second string which is surprising to me:

SELECT * FROM mytable WHERE id = ANY('{'normal string'}')

So this method I suggested, while it works for number inputs, doesn't work at all for strings. So even if there's no recommended way, could you point me to a way to use ANY with a list of string that works?

---

Edit: ah, my mistake, from experimenting it looks like I'm overthinking it, the ids just need to be passed as a single parameter:

const req = { body: { ids: ["normal string", "'string with quotes'"] } }
await db.query("SELECT * FROM mytable WHERE id = ANY($1)", [req.body.ids])

[vitaly-t]

In your code, you are making the same mistake as here and here.

Proper usage for CSV filter:

await db.query("SELECT * FROM mytable WHERE id = ANY($1:csv)", [req.body.ids])

or

await db.query("SELECT * FROM mytable WHERE id = ANY(${ids:csv})", req.body)

or

await db.query("SELECT * FROM mytable WHERE id = ANY(${body.ids:csv})", req)

But if you want an ARRAY syntax there, then simply omit the :csv part.

[mmkal]

Thank you!