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

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

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:

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

postgres db (working!)

using pg package:

  1. const pool = new pg.Pool({ /* config */ });
  2. const client = await pool.connect();
  4. const { rows } = client.query(`
  5.   SELECT * FROM users WHERE first_name = $1 AND user_id = ANY($2);
  6. `, [name, userIds]);

using postgres:

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

vertica db (NOT working)

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

using vertica-nodejs:

  1. import Vertica from 'vertica-nodejs';
  2. const { Pool } = Vertica;
  3. const pool = new Pool({ /* vertica db config */ });
  5. const res = await pool.query(`
  6.   SELECT * FROM users WHERE first_name = ? AND user_id IN (?);
  7. `, [name, userIds]);
  9. // -> 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:

  1. const pool = new pg.Pool({ /* vertica db config */ });
  2. const client = await pool.connect();
  4. const { rows } = client.query(`
  5.   SELECT * FROM users WHERE first_name = ? AND user_id IN (?);
  6. `, [name, userIds]);
  8. // -> Invalid input syntax for integer: "{"1","2","3"}"

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

  1. const sql = postgres({ /* vertica db config */ });
  2. const rows = await sql`
  3.   SELECT * FROM users;
  4. `;
  6. // -> 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:

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

postgres db (working!)

using pg package:

  1. const pool = new pg.Pool({ /* config */ });
  2. const client = await pool.connect();
  4. const { rows } = client.query(`
  5.   SELECT * FROM users WHERE first_name = $1 AND user_id = ANY($2);
  6. `, [name, userIds]);

using postgres:

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

vertica db (NOT working)

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

using vertica-nodejs:

  1. import Vertica from 'vertica-nodejs';
  2. const { Pool } = Vertica;
  3. const pool = new Pool({ /* vertica db config */ });
  5. const res = await pool.query(`
  6.   SELECT * FROM users WHERE first_name = ? AND user_id IN (?);
  7. `, [name, userIds]);
  9. // -> 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:

  1. const pool = new pg.Pool({ /* vertica db config */ });
  2. const client = await pool.connect();
  4. const { rows } = client.query(`
  5.   SELECT * FROM users WHERE first_name = ? AND user_id IN (?);
  6. `, [name, userIds]);
  8. // -> Invalid input syntax for integer: "{"1","2","3"}"

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

  1. const sql = postgres({ /* vertica db config */ });
  2. const rows = await sql`
  3.   SELECT * FROM users;
  4. `;
  6. // -> 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. . `]1$ [$ YRNA )YNA `YRTA(
  3.  .s hsatcat $]1[ )YNA(YRNA ;0$ yrts "lseCTEcnI )"lseCTEcnI( rD TB = _tdsneiruq siht ot ma I
  4. )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:

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

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

发表评论

匿名网友

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

确定