如何从Node.js向Vertica查询传递数组参数？

问题背景 #

在Vertica数据库中执行参数化查询时，单值参数可正常运行，但将数组用于IN (...)条件的参数时会失败。需要实现安全的参数化查询以避免SQL注入，Vertica使用?作为参数占位符，不同于PostgreSQL的$1, $2...格式。

示例数据：

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

PostgreSQL可行方案参考 #

使用pg包 #

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]);

使用postgres包 #

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

Vertica的问题表现 #

仅当userIds为单个值时可运行，传入1个以上值的数组则失败

使用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"}"

使用vertica包 #

该包不支持参数化查询，仅提供quote函数用于字符串插值前的内容转义，无法满足安全的参数化需求。

使用pg包连接Vertica #

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"}"

使用postgres包连接Vertica #

该包无法兼容Vertica，执行基础查询时会报错：

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

// -> Schema "pg_catalog" does not exist

尝试过的无效写法 #

• 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：动态生成占位符（推荐，安全无注入） #

根据数组长度生成对应数量的?占位符，将数组元素展开到参数列表中。这种方式完全基于参数化查询，不会引入SQL注入风险：

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

// 根据数组长度生成占位符
const idPlaceholders = userIds.map(() => '?').join(',');

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

原理：生成的SQL会是SELECT * FROM users WHERE first_name = ? AND user_id IN (?, ?, ?)，参数列表为['robert', 1, 2, 3]，完全符合参数化查询的要求。

方法2：使用Vertica的ARRAY函数配合ANY（需驱动支持） #

部分情况下，可通过将数组转为Vertica认可的数组格式，配合ANY操作符实现：

const res = await pool.query(`
  SELECT * FROM users WHERE first_name = ? AND user_id = ANY(ARRAY[${userIds.map(() => '?').join(',')}])
`, [name, ...userIds]);

本质和方法1类似，只是语法上用ANY(ARRAY[...])替代IN(...)。

方法3：临时表关联查询（适合超大数据集） #

如果数组元素数量极大，可先将数组插入临时表，再通过关联查询实现：

// 创建临时表
await pool.query(`CREATE LOCAL TEMPORARY TABLE temp_user_ids(id INT) ON COMMIT DELETE ROWS;`);

// 批量插入ID
const insertPlaceholders = userIds.map(() => '(?)').join(',');
await pool.query(`INSERT INTO temp_user_ids(id) VALUES ${insertPlaceholders};`, userIds);

// 关联查询
const res = await pool.query(`
  SELECT u.* FROM users u
  JOIN temp_user_ids t ON u.user_id = t.id
  WHERE u.first_name = ?;
`, [name]);

此方法适用于数组元素数量较多的场景，避免SQL语句过长。

内容的提问来源于stack exchange，提问作者roberkules