CHOOSE WHERE in node-mysql

Question

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

I 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('/'); } ); 

Answer 1

Should you use IN (?) And NOT IN ? .

Any string manipulation may result in a SQL INJECTION backdoor.

Answer 2

You need to quote the lines, not use backlinks.

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

Answer 3

For a safer solution that avoids values, use? parameters like you usually do, but dynamically create parameter placeholders as follows:

 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) { . . . }) 

Answer 4

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 quotation marks. It is shielded by the mysql module.