Error executing UPDATE

Question

I'm having a little trouble performing an update query with the node mysql2 module. I'm preparing the query using the '?' placeholder and then passing in the values like so;

socket.on('connection', function(client){
    [...]
    client.on('userjoin', function(username, userid){
        run_db_insert("UPDATE users_table SET clientid = ? WHERE user = ?", [client.id, userid], function(){
            console.log(client.id + ' <=> ' + userid);
        });
    [...]
});

Unfortunately, this is raising an error; You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''12345678' WHERE userid = ?' at line 1

The data isn't reflected in the database. For some reason, the code doesn't appear to be picking up the second question mark placeholder and so it's not passing the correct value (i.e. it's trying to find the userid of ?).

If I change the code to this;

run_db_insert("UPDATE users_table SET clientid = ? WHERE user = '" + userid + "'", [client.id], function(){

...then the update runs without error and is reflected in the DB. If I console.log both client.id and userid, the console correctly reflects these values.

My run_db_insert function is as follows;

function run_db_insert(sql, args, callback){
    var mysql = svc_mysql2.createConnection({
        // connection details
    });
    mysql.connect(function(err){
        if(err){
            console.log('Error connecting to DB: ' + err);
        }
    });

    mysql.query(sql, [args], function(err){
        if (err){
            console.log(err);
            return;
        }
        callback();
    });
    mysql.end();
};

I've had no problems performing SELECT or INSERT queries using multiple '?' placeholders (with a slightly modified function that has result in the line 11 of that function and then returns that in the callback), but I'm finding that UPDATE isn't correctly assigning all the parameters I'm passing in to it.

Answer 1

I think your problem is that you're wrapping your query replacement values in another array, so [[client.id, userid]] is being passed to mysql.query().

Try changing:

mysql.query(sql, [args], function(err){

to:

mysql.query(sql, args, function(err){