How to prevent injection for my postgres query in node?

Question

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?

Pooya · Accepted Answer · 2021-11-15 05:39:52Z

2

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

answered Nov 15, 2021 at 5:39

Pooya

3,2032 gold badges16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to prevent injection for my postgres query in node?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related