SELECT WHERE IN in node-mysql

Question

Does anyone know how to use SELECT WHERE IN in node-mysql?

I've tried the code below, but I get the following error message:

'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''(`PHP`,`apache`)'' at line 1'

This is my code:

whereIn = '(';
for ( var i in tagArray ) {
    if ( i != tagArray.length - 1 ) {
        whereIn += "`" + tagArray[i] + "`,";    
    }else{
        whereIn += "`" + tagArray[i] + "`"; 
    }
}
whereIn += ')';

console.log(whereIn);

client.query(
    'SELECT tag_id FROM tag WHERE tag_name IN ?',
    [whereIn],
    function(err, result, fields) {
        client.destroy();

        if (err) {
            throw err;
        }

        console.log(result);

        res.redirect('/');
    }
);

Don't you love SQL error messages? Soooo0o0o0oo helpful – lbstr Jun 14 '12 at 22:14 — lbstr, Jun 14 '12 at 22:14

score 19 · Answer 1 · answered Mar 06 '15 at 09:21

19

You have to use IN (?) and NOT IN ?.

Any string manipulation may result in a SQL INJECTION backdoor.

answered Mar 06 '15 at 09:21

Daniele Vrut

2,835
2
22
32

score 3 · Answer 2 · answered Jul 30 '18 at 13:03

You simply need to pass the tagArray of values to node-mysql and it will handle the rest for you:

client.query(
    'SELECT tag_id FROM tag WHERE tag_name IN (?)',
    [tagArray],
    function(err, result, fields) {
        client.destroy();

        if (err) {
            throw err;
        }

        console.log(result);

        res.redirect('/');
    }
);

For more information, see the section in the manual for how different values are automatically escaped: https://github.com/mysqljs/mysql#escaping-query-values

score 2 · Answer 3 · answered Jun 14 '12 at 21:02

2

You need to quote your strings, not use backticks.

whereIn = '(';
for ( var i in tagArray ) {
    if ( i != tagArray.length - 1 ) {
        whereIn += "'" + tagArray[i] + "',";    
    }else{
        whereIn += "'" + tagArray[i] + "'"; 
    }
 }
whereIn += ')';

answered Jun 14 '12 at 21:02

gcochard

11,408
1
26
41

Yes, backticks are not for values. – Nick Rolando Jun 14 '12 at 21:06
Ive tried that already, and I get this, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''(\'PHP\',\'apache\',\'Java\')'' at line 1' :( – Takehiro Adachi Jun 14 '12 at 21:10
Looks like your query is automatically escaping quotes. Is there an option that will NOT escape them? – gcochard Jun 14 '12 at 21:11
wont there be a security problem if it's not escaped? – Takehiro Adachi Jun 14 '12 at 21:25
You want to escape any quotes inside of tagArray, but not the quotes outside of each of the values. – gcochard Jun 14 '12 at 21:40
i got it by doing this var sql = new String(); sql = 'SELECT tag_id FROM tag WHERE tag_name IN ('; for ( var i in tagArray ) { tagArray[i] = client.escape(tagArray[i]); if ( i != tagArray.length - 1 ) { sql += tagArray[i] + ","; }else{ sql += tagArray[i]; } } sql += ')'; console.log(sql); client.query( sql, function(err, result, fields) { client.destroy(); if (err) { throw err; } console.log(result); res.redirect('/'); } ); – Takehiro Adachi Jun 14 '12 at 21:51
Most SQL systems only allow parameter substitution where an identifier would normally be allowed; a WHERE list isn't an identifier. I vaguely remember that Mysql has some sort of extension function that allows you to achieve the same effect using a different syntax; I think it came up here sometime in the last few months. – ebohlman Jun 15 '12 at 00:58

score 1 · Answer 4 · answered Jun 15 '17 at 13:47

For a more secure solution that avoids having to escape values, use ? params like you would normally do, but create the param placeholders dynamically like this:

var inlist = '';
for(var i=0; i<ids.length; i++) {
  inlist += '?,';
}
inlist = inlist.substring(0,inlist.length-1);

var sql = 'SELECT a, b, c FROM mytable WHERE id in (' + inlist + ')';

conn.query( sql, ids, function(err, rows) {
  . . .
})

score 0 · Answer 5 · answered Feb 25 '21 at 21:39

In case anyone is looking for answer to this in 2021.

 client.query(
      'SELECT tag_id FROM tag WHERE tag_name IN (?)', 
       [['val1', 'val2']],
       function(err, result, fields) {
    client.destroy();

    if (err) {
        throw err;
    }

    console.log(result);

    res.redirect('/');
}

);

score -1 · Answer 6 · answered Oct 25 '16 at 13:50

-1

A working solution:

client.query(
    'SELECT tag_id FROM tag WHERE tag_name IN ?',
    [tagArray],
    function(err, result, fields) {
        client.destroy();

        if (err) {
            throw err;
        }

        console.log(result);

        res.redirect('/');
    }
);

No need to manually wrap tagArray in quotes. It is escaped by the mysql module.

answered Oct 25 '16 at 13:50

Orr Siloni

1,268
10
21

1

You should add brackets to ?: 'SELECT tag_id FROM tag WHERE tag_name IN (?)', – zhang xuefeng Mar 21 '17 at 09:30

SELECT WHERE IN in node-mysql

6 Answers6