Correct UPDATE query syntax for node-mysql

Question

I have an array 'flag' and I have changed the values of that array in my function. Now I need to update the same into the database, but I am unable to do so. I already have flag column in the table. I don't know how to update the value within the variable flag in the table

If I try

connection.query('UPDATE visentry SET flag = "flag" ',  function(err,rows,fields) { }

It updates the column flag with value flag. If I try the following

    var sql = 'UPDATE visentry SET flag= ?';
   connection.query(sql,[{flag:flag}], function(err,rows,fields) { 

It gives an error

Error: ER_PARSE_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 '' at line 1

Any suggestions please

Answer 1

Replace Array with Object,

connection.query('UPDATE visentry SET flag = ?', {flag: flag})

Or add support for custom formatter and write like this:

connection.query("UPDATE visentry SET flag = :flag", { flag: flag });

Answer 2

It is smart enough to deal with an array:

var sql = 'UPDATE visentry SET flag= ? WHERE row_name = ?';
var row_name = 'blah_blah_blah';

connection.query(sql,[flag, row_name], function(err,rows,fields) { });

If you want to use your first approach, than you should use a variable, not the string inside your query. The right syntax will be:

connection.query('UPDATE visentry SET flag = "' + flag + '"', function(err,rows,fields) { });

But this approach is not safe enough, you probably want to escape value of flag (quotes and other special chars) and make it sql-friendly.

You can use for this purpose some code like this (read more - Making a javascript string sql friendly):

function mysql_real_escape_string (str) {
    return str.replace(/[\0\x08\x09\x1a\n\r"'\\\%]/g, function (char) {
        switch (char) {
            case "\0":
                return "\\0";
            case "\x08":
                return "\\b";
            case "\x09":
                return "\\t";
            case "\x1a":
                return "\\z";
            case "\n":
                return "\\n";
            case "\r":
                return "\\r";
            case "\"":
            case "'":
            case "\\":
            case "%":
                return "\\"+char; // prepends a backslash to backslash, percent,
                                  // and double/single quotes
        }
    });
}

So, probably the best practice for this approach will be:

connection.query('UPDATE visentry SET flag = "' + mysql_real_escape_string(flag) + '"', function(err,result) { });

Answer 3

Consider this following snippet:

connection.query('UPDATE visentry SET ?', {flag: flag}, function(err, result)

or

connection.query('UPDATE visentry SET flag = ?', [flag], function(err, result)

or

connection.query("UPDATE visentry SET flag = :flag", { flag: flag });