how to pass list of ids to mysql delete query from NodeJs

Question

i need to do a multiple row delete query with the where clause in list (id1,id2..) But since we don't have lists in JS i'm having a problem with passing the parameters to the query.This is my code:

 let list =[1,2,..];
 let Query = 'DELETE FROM Table WHERE id IN (?)';
 connection.query(Query,list, function (err, result) {
     `enter code here`
 };

when i pass it this way and after logging the mysql server to a Logfile i see that it actually passes only the first id.

PS : i tried without the parentheses around ? and also doing a Array.join on the list but both didn't work.

This is similar to stackoverflow.com/questions/41935358/in-clause-in-mysql-nodejs As @hoangdv wrote, you need to pass all the parameters as array. — splintor
– splintor, Commented Feb 19, 2020 at 12:40
? is simply broken for use with arrays, sad but true. Just interpolate in the array - and it's up to you to avoid injection. — Fattie
– Fattie, Commented May 25 at 18:41

hoangdv · Accepted Answer · 2017-07-06 07:27:56Z

7

Read in document of https://github.com/mysqljs/mysql#performing-queries (if you use this lib to connect mysql)

 let list =[1,2,..];
 let Query = 'DELETE FROM Table WHERE id IN (?)';
 // values param need is a array of value to binding to "?"
 // you can try [list.toString()] if the block above not working
 connection.query(Query,[list], function (err, result) {
     `enter code here`
 };

answered Jul 6, 2017 at 7:27

hoangdv

16.3k4 gold badges31 silver badges54 bronze badges

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

1 Comment

Devin Over a year ago

This is exactly what I want. Works well in node mysql2 package

lxe · Accepted Answer · 2017-07-06 00:15:42Z

0

Simply append the list of items to the query string:

 let list = [1, 2, 3];
 let Query = `DELETE FROM Table WHERE id IN (${list})`;
 connection.query(Query, function (err, result) {
     `enter code here`
 };

answered Jul 6, 2017 at 0:15

lxe

7,7772 gold badges24 silver badges33 bronze badges

2 Comments

tiomno Over a year ago

good as long as the list variable is not filled in with data from outside the system, e.g. user inputted data. Otherwise, SQL injection would be a problem.

Alwin Kesler Over a year ago

There's a reason why MySQL says to "always escape your data". You don't want to find it out

tiomno · Accepted Answer · 2018-12-04 06:57:13Z

0

Unfortunately, ? placeholders don't work with the IN operator. So you should escape the values. Say the list variable is coming from an external source; so to prevent SQL Injection you can:

// `list` is filled in outside this process
const Query = `DELETE FROM Table WHERE id IN (${list.map((item) => connection.escape(item))})`;
connection.query(Query, function (err, result) {
  // handle error or result here
};

I noticed that list.map(connection.escape) won't work and throw: TypeError: Cannot read property 'config' of undefined

answered Dec 4, 2018 at 6:57

tiomno

2,25730 silver badges33 bronze badges

Collectives™ on Stack Overflow

how to pass list of ids to mysql delete query from NodeJs

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related