Javascript string interpolation to create SQL query with Array

Question

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?

Stefan Steiger · Accepted Answer · 2019-10-10 14:08:51Z

4

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("','") }');`

edited Oct 10, 2019 at 14:08

answered Oct 10, 2019 at 13:55

Stefan Steiger

83k70 gold badges405 silver badges461 bronze badges

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

1 Comment

Rahul Over a year ago

Thanks a lot. This sure works. I am surprised to not find any more elegant way to do this.. given that this is such a standard use case.

Collectives™ on Stack Overflow

Javascript string interpolation to create SQL query with Array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related