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?

  • 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. – Tim Biegeleisen Jul 01 '20 at 02:49
  • General concept: this is done (safely) with generating _placeholders_ dynamically, then binding a value to each placeholder. – user2864740 Jul 01 '20 at 05:54

1 Answers1

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.

Sohel Aman
  • 482
  • 4
  • 6