How can i pass an array as a parameter to a Vertica query from node.js?

Question

I'm trying to execute sql queries against a vertica db. that works so far. but to prevent sql injection, I want to use parameterized queries. looks like vertica supports parameters as ? (compared to postgres' $1, $2, ...)

so the parameters work, BUT NOT if the parameter is an array of values (to use in IN (...) conditions)

any idea how to fix this?

---

let's say I have a list of user ids and a name:

const userIds = [1, 2, 3];
const name = 'robert'

postgres db (working!)

using pg package:

const pool = new pg.Pool({ /* config */ });
const client = await pool.connect();

const { rows } = client.query(`
  SELECT * FROM users WHERE first_name = $1 AND user_id = ANY($2);
`, [name, userIds]);

using postgres:

const sql = postgres({ /* postgres db config */ });
const rows = await sql`
  SELECT * FROM users WHERE first_name = ${name} AND user_id = ANY(${userIds});
`;

vertica db (NOT working)

only works if userIds is passed as a single value, not an array of 1+ values

using vertica-nodejs:

import Vertica from 'vertica-nodejs';
const { Pool } = Vertica;
const pool = new Pool({ /* vertica db config */ });

const res = await pool.query(`
  SELECT * FROM users WHERE first_name = ? AND user_id IN (?);
`, [name, userIds]);

// -> Invalid input syntax for integer: "{"1","2","3"}"

using vertica:
doesn't seem to support parameters at all, just provides a function (quote) to sanitize them before string interpolation.

using pg:

const pool = new pg.Pool({ /* vertica db config */ });
const client = await pool.connect();

const { rows } = client.query(`
  SELECT * FROM users WHERE first_name = ? AND user_id IN (?);
`, [name, userIds]);

// -> Invalid input syntax for integer: "{"1","2","3"}"

using postgres:
(doesn't seem to support connecting to a vertica db at all)

const sql = postgres({ /* vertica db config */ });
const rows = await sql`
  SELECT * FROM users;
`;

// -> Schema "pg_catalog" does not exist

---

I also tried those variations instead of user_id IN (?):

• user_id IN (?::int[]) -> Operator does not exist: int = array[int]
• user_id = ANY (?) -> Type "Int8Array1D" does not exist
• user_id = ANY (?::int[]) -> Type "Int8Array1D" does not exist

Answer 1

Try ANY (ARRAY [$1]) . I don't know about node.js or SQL injection, but in a bash shell it seems to work:

marco ~/1/Vertica/supp $ cat test.sh      
#!/usr/bin/env zsh
vsql -c "
SELECT cust_id,cust_from_dt,cust_fname,cust_lname 
FROM scd.d_customer_scd 
WHERE cust_id = ANY(ARRAY[$1]) 
ORDER BY 1,2"
marco ~/1/Vertica/supp $ ./test.sh 1,2,3
 cust_id | cust_from_dt | cust_fname | cust_lname 
---------+--------------+------------+------------
       1 | 2021-12-05   | Arthur     | Dent
       1 | 2021-12-15   | Arthur     | Dent
       1 | 2021-12-22   | Arthur     | Dent
       1 | 2021-12-29   | Arthur     | Dent
       2 | 2021-12-05   | Ford       | Prefect
       3 | 2021-11-05   | Zaphod     | Beeblebrox
       3 | 2021-12-15   | Zaphod     | Beeblebrox
       3 | 2021-12-22   | Zaphod     | Beeblebrox
       3 | 2021-12-29   | Zaphod     | Beeblebrox
(9 rows)

marco ~/1/Vertica/supp $