使用nodejs在mysql中插入多个值会导致解析错误

Question

我需要使用nodejs在mysql中插入一条记录。我可以通过在查询中输入值来直接插入。我可以使用查询+值语法来连接这些值，但是这样做会带来SQL注入的风险。

​

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

​

下面是我从mysql得到的附加错误。它似乎在gametype变量两边加了额外的引号，而不是试图将其余的插入到查询中。有什么想法吗？error from mysql

Answer 1

我建议您不要使用数组，并将查询替换为以下查询

​

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

​

然后您将一个接一个地传递这些值

Answer 2

如果查询有4个值参数，那么也应该有4个问号。尝试将第一行更改为：

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

Answer 3

您可以尝试将值包装到数组中，因为此方法用于插入多行：

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