how to query mysql database with where clause using dynamic array

Question

I'm trying to query mysql database with where clause as below.

Here popularTopicsNames is a dynamic array and its length and elements varies.

var scoreQuery=connection.query('SELECT * FROM LEADERBOARD WHERE SUBTOPIC IN ('+popularTopicsNames+')', function(err,result,fields){
if(err) throw err;
else{
    console.log(result);
    }

Ben Fortune · Accepted Answer · 2014-04-17 15:46:41Z

2

If it's just an array then you're trying to pass it as a string, which won't work.

Try

var scoreQuery = connection.query("SELECT * FROM LEADERBOARD WHERE SUBTOPIC IN ('" + popularTopicsNames.map(mysql.escape).join("','") + "')",
    function(err, result, fields) {
        if (err) {
            throw err;
        } else {
            console.log(result);
        }
    });

edited Apr 17, 2014 at 15:46

answered Apr 17, 2014 at 11:57

Ben Fortune

32.2k10 gold badges82 silver badges82 bronze badges

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

4 Comments

mscdex Over a year ago

You're missing the beginning and end single quotes right inside the parenthesis. Without them you'll end up with invalid SQL for more than 1 item in the array. Example: SELECT * FROM LEADERBOARD WHERE SUBTOPIC IN (foo','bar)

mscdex Over a year ago

Also, if the topic names are user submitted, then they should be properly escaped first to prevent SQL injection attacks.

mscdex Over a year ago

I think you need something like popularTopicsNames.map(mysql.escape).join("','") instead.

Ben Fortune Over a year ago

@mscdex You're right, as the escape return value wraps it in quotes.

Ishan Anuradha · Accepted Answer · 2021-05-27 08:56:08Z

1

This will solve the issue in a more readable way.

var scoreQuery = connection.query(`SELECT * FROM LEADERBOARD WHERE SUBTOPIC IN (?)`, [popularTopicsNames],
(err, result, _fields) => {
    if (err) {
        throw err;
    } else {
        console.log(result);
    }
});

answered May 27, 2021 at 8:56

Ishan Anuradha

514 bronze badges

Collectives™ on Stack Overflow

how to query mysql database with where clause using dynamic array

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related