How to prevent injection for my postgres query in node?-CodePudding

Below is my code base with the query

export const getQuery = (idList) => {
 return `SELECT * from glacier_restore_progress where id in ${idList}`;
}

const query = getQuery('(1,2)');
dbResponse = await pool.query(query)
...

it works fine. But the Sql Injection issue is popping from my sonar server. So i tried below code change and it didn't work,

...
dbResponse = await pool.query('SELECT * from glacier_restore_progress where id in $1', ['(1,2)']);

What am i missing here?

CodePudding user response：

The best solution uses the ANY function with Postgres array coercion. This lets you match a column with an arbitrary array of values as if you had written out col IN (v1, v2, v3). This is the approach in pero's answer.

SELECT * FROM glacier_restore_progress WHERE id = ANY($1::int[])

dbResponse = await pool.query('SELECT * FROM glacier_restore_progress WHERE id = ANY($1::int[])', [[1,2, ...]]);