Pass multiple id(s) in query string and get data for them in mysql in node js

Question

I have a get API in nodejs , which gets string of variables as query string ( delimited by ",") as follows

const mac = req.query.mac; 
console.log(mac); // 00:11:22:33:FF:EE,11:11:22:33:FF:EE
var sql =  mysql.format("SELECT * FROM user_email WHERE macId IN ?",[mac]);
        connection.query(sql, function(err, row) ...{ ... .. }

But i am getting errors

code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''00:11:22:33:FF:EE,11:11:22:33:FF:EE'' at line 1",
  sqlState: '42000',
  index: 0,
  sql: "SELECT * FROM user_email WHERE macId IN '00:11:22:33:FF:EE,11:11:22:33:FF:EE'"
}

Someone please help !!

''00:11:22:33:FF:EE,11:11:22:33:FF:EE'' additional quote is getting added — xMayank
– xMayank, Commented Jul 8, 2020 at 11:00
"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''\\'00:11:22:33:FF:EE,11:11:22:33:FF:EE\\''' at line 1", — pushE
– pushE, Commented Jul 8, 2020 at 11:33

AmRzA · Accepted Answer · 2020-07-12 06:03:03Z

1

your mistake is in converting mac from string to array

[mac]

won't convert it to a string. try this instead:

const mac = req.query.mac; 
const macArr = mac.split(',');
var sql =  mysql.format("SELECT * FROM user_email WHERE macId IN (?)", macArr);
connection.query(sql, function(err, row) ...{ ... .. }

edited Jul 12, 2020 at 6:03

answered Jul 8, 2020 at 10:57

AmRzA

19011 bronze badges

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

4 Comments

pushE Over a year ago

STILL AS THIS ------------------------------------------------------------- code: 'ER_PARSE_ERROR', errno: 1064, sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''00:11:22:33:FF:EE'' at line 1", sqlState: '42000', index: 0, sql: "SELECT * FROM user_email WHERE macId IN '00:11:22:33:FF:EE'" }

pushE Over a year ago

Only one mac is passed to query now

pushE Over a year ago

Its solved now, used brackets on ? as this (?) solved the issue

AmRzA Over a year ago

oh sure my mistake, I will edit my answer. thanks for sharing

Collectives™ on Stack Overflow

Pass multiple id(s) in query string and get data for them in mysql in node js

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related