Search code examples
mysqlnode.jsexpressnode-mysql

Using node-mysql, how can I NOT escape mysql functions?

I'm trying to use the MySQL function "now()" in an insert statement using the node-mysql module:

var insert = {
  username: 'foo',
  date_joined: 'now()',
};

connection.query('INSERT INTO users SET ?', [insert],function(err, result){ ... });

As I expected, this gives me an error, Error: ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: 'now()' for column 'date_joined', as it escaped the now() function to a string instead of letting MySQL parse it as the function I intended.

In practicality, my insert statement and query is much more complicated, and so I'd like to utilize the escaping query values shortcuts node-mysql offers rather than building out my query manually.

How can I tell node-mysql NOT to escape the now() function?

Solution

  • You can use mysql.raw(). This is directly from the docs. 

    var CURRENT_TIMESTAMP = mysql.raw('CURRENT_TIMESTAMP()');
var sql = mysql.format('UPDATE posts SET modified = ? WHERE id = ?', [CURRENT_TIMESTAMP, 42]);
console.log(sql); // UPDATE posts SET modified = CURRENT_TIMESTAMP() WHERE id = 42