Can no longer pass an array of data to use inside `column IN ($1)` parameterized query

[whatl3y]

I apologize if there was an update that caused this and I missed it, but suddenly (using node-postgres version 6.1.5) the following query doesn't work when it used to pretty recently:

import { Pool } from 'pg'
const pool = new Pool(config)
const { rows } = await pool.query('SELECT * FROM table WHERE id IN ($1)', [[1,2,3]]

I get the following error, which I haven't gotten in the past:

error: invalid input syntax for integer: "{"1","2","3"}"

I also tried the following which isn't working either:

const { rows } = await pool.query('SELECT * FROM table WHERE id IN ($1)', [[1,2,3].join(',')]
error: invalid input syntax for integer: "1,2,3"

How do I run a parameterized query using the IN keyword and provide a list of data as we used to be able to before? Is there documentation on this breaking change somewhere I missed?

[sehrope]

AFAIK there isn't any breaking change is as this is how both the server and node-postgres client have always been:

=> SELECT 1 WHERE 1 IN ('{1,2,3}'::int[]);
ERROR:  42883: operator does not exist: integer = integer[]
LINE 1: SELECT 1 WHERE 1 IN ('{1,2,3}'::int[]);
                         ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
LOCATION:  op_error, parse_oper.c:722

To do this type of thing replace the value IN (...) with value = ANY($1):

=> SELECT 1
    WHERE 1 = ANY('{1,2,3}'::int[]);
 ?column? 
----------
        1

Then the $1 can be an array. The IN (...) approach only works for a inline list in the SQL itself and requires dynamically composing (and escaping!) the values so they're part of the SQL text. The = ANY($1) approach is always better as it can be parameterized.

[charmander]

I’m pretty sure this has never worked, like sehrope said.

[retorquere]

Does the library offer a way to safely quote values so I can inject them directly into the query?

[whatl3y]

@retorquere you should use parameterized queries wherever you can, but I believe for strings simply replacing all single ticks with multiple ticks should safely prevent injection:

SELECT * FROM table WHERE my_string_column = '${str_value.replace("'", "''")}'

[retorquere]

I'd very much prefer parametrized queries, but according to what's above, there's no parametrized way to do IN ($1), right? So string injection is my only option.

[whatl3y]

Oh, use ANY as @sehrope mentions above. So instead of using col IN (val1, val2, ...) use col = ANY($1) with an array of values as your parameter for $1.

const arrayOfStrings = ['val1', 'val2', 'val3']
const { rows } = await pool.query('SELECT * FROM table WHERE my_string_column = ANY($1)', [arrayOfStrings])

[retorquere]

Great! I thought that was the JSON syntax, sorry.

[f-w]

This doesn't work in redshift.

Oh, use ANY as @sehrope mentions above. So instead of using col IN (val1, val2, ...) use col = ANY($1) with an array of values as your parameter for $1.

const arrayOfStrings = ['val1', 'val2', 'val3']
const { rows } = await pool.query('SELECT * FROM table WHERE my_string_column = ANY($1)', [arrayOfStrings])