如何从Node.js将数组作为参数传递给Vertica查询?

huangapple go评论57阅读模式
英文:

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

问题

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
英文:

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

答案1

得分: 1

. `]1$ [$ YRNA )YNA `YRTA(

 .s hsatcat $]1[ )YNA(YRNA ;0$ yrts "lseCTEcnI )"lseCTEcnI( rD TB = _tdsneiruq siht ot ma I
)slehs hsab a ni ,no ti llebhs hsab a ni ,xjs.edon` osla tuoba wonk ot t'nseem ti ,erhs htiw lls hsab a ni ,si j donk t'ndluow I
英文:

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 $ 

huangapple
  • 本文由 发表于 2023年5月28日 23:06:20
  • 转载请务必保留本文链接:https://go.coder-hub.com/76352130.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定