IN clause in mysql nodejs

Question

I have a simple nodejs application which executes the following query.

select * from User where userid in (?)

The userids i get is a JSON array send from client side. How can i use that in this select query ? I tried 1. As itself but not working. 2. Convert this to Javascript array, not working

Answer 1

If you are using node module like mysql, the 2nd approach should work.

var query=select * from User where userid in (?);
var data=['a','b','c'];
var queryData=[data];

conn.query(query, queryData, function (err, results) {})

According to the documentation, "Arrays are turned into list, e.g. ['a', 'b'] turns into 'a', 'b'". So this approach should work (I have used it practically).

Answer 2

If you pass an array to the parameter it works with node mysql2. Parameters are already passed as arrays, so your first parameter needs to be an array [[1,2,3]].

select * from User where userid in (?)

const mysql = require('mysql2/promise');

async function main(){
  let db = await mysql.createPool(process.env.MYSQL_URL);
  let SQL = 'select * from User where userid in (?)';

  let [res, fields] = await db.query(SQL, [[1,2,3]]);
  console.log(res)
  return res;
}

main().then(() => {process.exit()})

Answer 3

Revisiting this, since the original approach on the question is valid, but with some caveats. If your only escaped argument is the one on the IN clause, then you have to specify it as nested array; something like: [['usrId1', 'usrId2', 'usrIdN']]. This is because the un-escaping functionality expects an array, replacing each '?' with the corresponding array element. So, if you want to replace your only '?' with an array, that array should be the first element of all arguments passed. If you had more than one '?', the syntax is more intuitive, but at the end consistent and the same; in this case, you could have your arguments similar to: ['myOtherArgument1', 'myOtherArgument2', ['usrId1', 'usrId2', 'usrIdN'], 'myOtherArgument3']

Answer 4

Something like this could work!

// get your possible IDs in an array
var ids = [1,2,3,4,5];

// then, create a dynamic list of comma-separated question marks
var tokens = new Array(ids.length).fill('?').join(',');

// create the query, passing in the `tokens` variable to the IN() clause
var query = `SELECT * FROM User WHERE userid IN (${tokens})`;

// perform the query
connection.query(query, ids, (err, data) => {
   // do something with `err` or `data`
});