CODEWITHSUNDEEP
javascriptnode.jsnode-mysql
wdetac
wdetac

Reputation: 2852

node-mysql escaping query values - array with unknown length

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?

Upvotes: 0

Views: 1473

Answers (1)

mscdex
mscdex

Reputation: 106746

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)

Upvotes: 7

Related Questions