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?

mscdex · Accepted Answer · 2017-05-18 19:22:58Z

7

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)

edited May 18, 2017 at 19:22

answered Aug 6, 2015 at 4:38

mscdex

107k15 gold badges201 silver badges159 bronze badges

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

2 Comments

wdetac Over a year ago

Thank you very much. I know I can generate numbers of ? base on length on the array, which is pure javascript approach. I am looking for some higher level solutions. I will use your method if there is no better way.

Berty Over a year ago

i had the same id using lodash functions: _.times(ids.length, _.constant('?')).join(). and then passing the array like: query(query, ids, function...). But your approach is much cleaner.

Collectives™ on Stack Overflow

node-mysql escaping query values - array with unknown length

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related