node-mysql escaping query values - array with unknown length

Question

I use node.js and the module node-mysql for connecting to the mySQL server. However, I got some problems when I tried to escape an array in a query. Here is my code:

connection.query("select * from table where id in (?)", [1, 3, 5], function(err, res) {
    ...
});

The above query is select * from table where id in (1), which is not my expectation.

As the documents said:

Arrays are turned into list, e.g. ['a', 'b'] turns into 'a', 'b'

I know select * from table where id in (?,?,?) works. The question is, what should I do if I have an array with unknown length?

Answer 1

One solution is to nest the array so that it gets properly converted to a list:

connection.query("select * from table where id in (?)", [[1, 3, 5]], ...);

Another solution would be to dynamically generate the ?s. For example:

var values = [1, 3, 5];
var query = "select * from table where id in ("
            + new Array(values.length + 1).join('?,').slice(0, -1)
            + ")";
connection.query(query, values, function(err, res) {
    ...
});

With ES6, you can simplify the list creation in the second solution to:

'?,'.repeat(values.length).slice(0, -1)