1

I have to use an SQL query like the one below in a Node.js app.

SELECT * FROM my_table
WHERE my_column IN ['name1','name2']

The array ['name1', 'name2'] is inputted by the user. And it may contain more than 2 names sometimes. How do I format this query using the pg-format package to avoid SQL injection?

Improve this question
2
  • Please include your actual Node.js code, including the code where you execute the query. The SQL you posted above is not valid Postgres, we need to see the actual code. Commented Jul 1, 2020 at 2:49
  • General concept: this is done (safely) with generating placeholders dynamically, then binding a value to each placeholder. Commented Jul 1, 2020 at 5:54

1 Answer 1

Reset to default
3

Aren't the IN clause arguments supposed to be wrapped using parentheses? Anyway, here's an example on formatting using pg-format,

var format = require('pg-format');
var sql = format("SELECT * FROM my_table WHERE my_column IN (%L, %L)", 'Alice', 'Bob'); // name1, name2
console.log(sql);

Edit 1: With dynamic names using an array,

var format = require('pg-format');
var names = ['Alice', 'Bob', 'Charlie'];
var inCaluseStr = '(' + Array(names.length).fill('%L').join(',') + ')';
var sql = format.withArray("SELECT * FROM my_table WHERE my_column IN " + inCaluseStr, names);
console.log(sql);

I hope this helps.

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

4 Comments

Thanks. but I have to pass 'name1' and 'name2' as an array. Cause the names are inputted by the user and there can be more than 2 names.
Check Edit 1. Thanks.
Possibly cleaner to do without using “format”, and leaving text + bindings separate.
Thanks. This method is good.