CODEWITHSUNDEEP
javascriptmysqlnode.js
Justin Wade
Justin Wade

Reputation: 127

Inserting multiple values in mysql using nodejs is causing parsing errors

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

Upvotes: 2

Views: 596

Answers (3)

Alain Burindi
Alain Burindi

Reputation: 121

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

Upvotes: 1

Marcin
Marcin

Reputation: 1494

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);
    });

Upvotes: 0

edgars
edgars

Reputation: 1068

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 (?, ?, ?, ?) ";

Upvotes: 1

Related Questions