Javascript SQL request Bad Field Error in where clause

Question

the Code is supposed to update the score only if there is no score with the same email greater than the current score, instead it displays the error:

Error: Error: ER_BAD_FIELD_ERROR: Unknown column 'li' in 'where clause'

function postScores(useremail, username, scoreValue, leaderboardName) {
    if (leaderboardName === "GHOSTS" || leaderboardName === "PACMAN" || leaderboardName === "OVERALL") {
        connection.query('UPDATE SCORES_' + leaderboardName + ' SET SCORES=' + scoreValue + ' WHERE SCORES < ' + scoreValue + ' AND USER_EMAIL=' + useremail,
            function(err, rows, fields) {
                if (err) {
                    console.log("Failed to Update. Attempting to Insert.");
                    console.log("Error: " + err);
                    connection.query(
                        'INSERT INTO SCORES_' + leaderboardName + '(USER_EMAIL, USER_NAME, SCORES) VALUES (?,?,?)', [
                            useremail, username, scoreValue
                        ],
                        function(err, rows, fields) {
                            if (err) {
                                console.log("Total Failure. Systems down");
                            } else {
                                console.log("Success. Inserted new Scores");
                            }
                        });
                }
            });
    } else {
        // Reference to Non-Existent Leaderboard
        return console.log('Specified Leaderboard of the name ' + leaderboardName + ' does not Exist');
    }
}

Should USER_EMAIL have quotes around it (as you're inserting a string)? — John Reid
– John Reid, Commented Oct 10, 2014 at 10:32

MrCode · Accepted Answer · 2014-10-10 10:36:19Z

3

In the UPDATE query, don't concatenate raw data into the values, use placeholders and pass an array for the values, in the same way as your INSERT query.

An unquoted string value is likely causing the SQL syntax error. By using placeholders, you don't need to handle quotes in the values.

connection.query('UPDATE SCORES_' + leaderboardName + ' SET SCORES = ? WHERE SCORES < ? AND USER_EMAIL = ?',
    [scoreValue, scoreValue, useremail],
    function (err, rows, fields) {

answered Oct 10, 2014 at 10:36

MrCode

64.6k10 gold badges95 silver badges114 bronze badges

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

Collectives™ on Stack Overflow

Javascript SQL request Bad Field Error in where clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related