"where in" MySQL query in nodejs

Question

I need to make MySQL query using "WHERE IN". This is my query:

var myQuery = 'SELECT uid FROM ' +tableName+ ' where Gender IN (' + Info.Gender.join() + ')';

If i print Info.Gender it will be ['Male', 'Female'], as a string. but when the query is done it says

SELECT uid FROM appUsers where Gender IN (Male, Female)

But should be:

SELECT uid FROM appUsers where Gender IN ('Male', 'Female')

That means it takes the Female not as a string.

Any ideas?

' where Gender IN (\'' + Info.Gender.join("', '") + \'')'; — fuyushimoya
– fuyushimoya, Commented Dec 16, 2015 at 10:24
When you're generating SQL in javascript, it's smart to always use double quotes, so you can be sure that any single quotes will be part of the SQL and not of the javascript. — SWeko
– SWeko, Commented Dec 16, 2015 at 10:29

robertklep · Accepted Answer · 2015-12-16 10:52:10Z

27

You should use query escaping (provided that you're using node-mysql):

var myQuery = 'SELECT uid FROM ?? where Gender IN (?)';
connection.query(myQuery, [ tableName, Info.Gender ], ...);

answered Dec 16, 2015 at 10:52

robertklep

205k38 gold badges421 silver badges412 bronze badges

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

Comments

Clay · Accepted Answer · 2015-12-16 10:25:53Z

2

You need single quotes in your query:

var myQuery = "SELECT uid FROM " +tableName+ " where Gender IN ('" + Info.Gender.join("','") + "')";

answered Dec 16, 2015 at 10:25

Clay

4,7703 gold badges37 silver badges49 bronze badges

For future visitors, use escaping instead of this proposed answer.

will this escape user input? it seems dangerous

Lintang Wisesa · Accepted Answer · 2024-12-06 02:57:43Z

0

You can pass the Info.Gender as array too:

var myQuery = `SELECT uid FROM users where Gender IN (?)`
var arrayData = ['Male', 'Female']
connection.query(myQuery, [arrayData])

answered Dec 6, 2024 at 2:57

Lintang Wisesa

66711 silver badges14 bronze badges