CODEWITHSUNDEEP
javascriptsqlarrayspostgresqlstring-interpolation
Rahul
Rahul

Reputation: 452

Javascript string interpolation to create SQL query with Array

I want to create a SQL query to select all records where values are in an array of strings. say I have the following array

myarray = ['a', 'b', 'c']

I want to use this to construct a query like this

SELECT * FROM my_table WHERE mycol IN ('a', 'b', 'c')

Regular string interpolation obviously does not work for this. I even tried sql-bricks but apparently it does not support IN with WHERE.

Is there a good way to use arrays in SQL queries?

Upvotes: 2

Views: 1855

Answers (1)

Stefan Steiger
Stefan Steiger

Reputation: 82186

let sql = `SELECT * FROM my_table WHERE mycol IN ('${ myarray.join("','") }');`

Also, if you want to remove null-values: 

let myarray = ['a', 'b', null, 'c']
let sql = `SELECT * FROM my_table WHERE mycol IN ('${ 
myarray.filter(function (el) {
  return el != null;
}).join("','")
 }');`

==>

"SELECT * FROM my_table WHERE mycol IN ('a','b','c');"

Also, to avoid sql-injection, you should first map myarray to an array where ' has been replaced with ''.

.ie. 

let myarray = ['a', 'b', null, 'c', "d'Alambert"]
.filter(function (el) { return el != null; })
.map(function(el){ return el.replace(/'/g, "''");}); 

let sql = `SELECT * FROM my_table WHERE mycol IN ('${ myarray.join("','") }');`

Upvotes: 4

Related Questions