1

I'm using pg-promise and am not understanding how to run this query. The first query works, but I would like to use pg-promise's safe character escaping, and then I try the second query it doesn't work.

Works:

db.any(`SELECT title FROM books WHERE id = ANY ('{${ids}}') ORDER BY id`)

Doesn't work

db.any(`SELECT title FROM books WHERE id = ANY ($1) ORDER BY id`, ids)
vitaly-t
  • 24,279
  • 15
  • 116
  • 138
Ron I
  • 4,090
  • 8
  • 33
  • 64

1 Answers1

5

The example has 2 problems. First, it goes against what the documentation tells you:

IMPORTANT: Never use the reserved ${} syntax inside ES6 template strings, as those have no knowledge of how to format values for PostgreSQL. Inside ES6 template strings you should only use one of the 4 alternatives - $(), $<>, $[] or $//.

Manual query formatting, like in your first example, is a very bad practice, resulting in bad things, ranging from broken queries to SQL injection.

And the second issue is that after switching to the correct SQL formatting, you should use the CSV Filter to properly format the list of values:

db.any(`SELECT title FROM books WHERE id IN ($/ids:csv/) ORDER BY id`, {ids})

or via an index variable:

db.any(`SELECT title FROM books WHERE id IN ($1:csv) ORDER BY id`, [ids])

Note that I also changed from ANY to IN operand, as we are providing a list of open values here.

And you can use filter :list interchangeably, whichever you like.

vitaly-t
  • 24,279
  • 15
  • 116
  • 138
  • I get the following error: `error: { error: invalid input syntax for integer: "1,2,3,4,5,6,7,8,9,10,11,12,...."` Here is the code: db.any(`SELECT * FROM objectives WHERE id IN ($1:csv) ORDER BY $2`, [ids , order]) .then(function (data) { Here is the console of the query: db.any SELECT * FROM objectives WHERE id IN ($1:csv) ORDER BY $2 [ '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34', 'id' ] – Ron I Oct 15 '19 at 15:51
  • @RonI Variable `$1` expects the value to be an array, not a string. And `$2` should be changed into `$2:name`. – vitaly-t Oct 15 '19 at 16:04
  • Got it! Thanks, I had converted ids to a string because I was improperly using the es6 string templates. Much appreciated! :) – Ron I Oct 15 '19 at 16:13
  • Update - it works with array that has values, but for empty array, I get this error: SELECT * FROM objectives WHERE id IN ($1:list) ORDER BY $2:name [ [], 'id' ] error: { error: syntax error at or near ")" – Ron I Oct 15 '19 at 16:59
  • @RonI See https://stackoverflow.com/questions/49188085/where-col-in-query-with-empty-array-as-parameter – vitaly-t Oct 15 '19 at 17:01