英文:
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 existuser_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 existuser_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 $
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论