Inserting multiple values in mysql using nodejs is causing parsing errors

Question

I need to insert a record into mysql using nodejs. I am able to insert directly by typing values into the query just fine. I can insert using query + value syntax to concatenate the values but read this leaves open the risk of SQL injection.

        let sql ="INSERT INTO gametypes (strGameType, intTeamSize, intMaxPlayers, intMinPlayers) Values ? ";
        var gametype ="Solo Zonewars";
        var teamSize =1;
        var maxPlayers = 16;
        var minPlayers = 10;
        var values = [gametype, teamSize, maxPlayers, minPlayers];

        console.log("connected as id '" + connection.threadId);
        
        connection.query(sql, values, function(err, result, fields) {
            connection.release();
            if(!err) {
                console.log(result);
            }else console.log(err);
        });

Below is the attached error I am getting from mysql. It seems like it is putting extra quotes around the gametype variable and not attempting to insert the rest into the query. Any ideas? error from mysql

Alain Burindi · Accepted Answer · 2019-12-21 19:12:58Z

1

I would suggest you to not use an array and replace your query by this one

let sql ="INSERT INTO gametypes (strGameType, intTeamSize, intMaxPlayers, intMinPlayers) Values (?, ?, ?, ?) ";

then you will pass the values one by one

edited Dec 21, 2019 at 19:12

answered Dec 21, 2019 at 19:05

Alain Burindi

1214 bronze badges

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

Comments

edgars · Accepted Answer · 2019-12-21 19:04:57Z

1

If you are having 4 value parameters to the query, you should have 4 question marks as well. Try with the first line changed to:

let sql ="INSERT INTO gametypes (strGameType, intTeamSize, intMaxPlayers, intMinPlayers) Values (?, ?, ?, ?) ";

answered Dec 21, 2019 at 19:04

edgars

1,0881 gold badge7 silver badges18 bronze badges

Comments

Marcin · Accepted Answer · 2019-12-21 19:07:21Z

0

You can try wrapping your values into an array, as this method is intended for inserting multiple rows:

   let sql ="INSERT INTO gametypes (strGameType, intTeamSize, intMaxPlayers, intMinPlayers) Values ? ";
        var gametype ="Solo Zonewars";
        var teamSize =1;
        var maxPlayers = 16;
        var minPlayers = 10;
        var values = [
               [gametype, teamSize, maxPlayers, minPlayers]
        ];

    console.log("connected as id '" + connection.threadId);

    connection.query(sql, [values], function(err, result, fields) {
        connection.release();
        if(!err) {
            console.log(result);
        }else console.log(err);
    });

answered Dec 21, 2019 at 19:07

Marcin

1,4921 gold badge14 silver badges27 bronze badges

Collectives™ on Stack Overflow

Inserting multiple values in mysql using nodejs is causing parsing errors

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related