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

Question

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?

What are the possible values for `date_joined`? Why do you use placeholders here if it's not a constant value but a function call? — zerkms, Jul 03 '14 at 01:48
date_joined is of `datetime` type. I don't need to use a placeholder for the function I guess, but that would make it *much* more readable. — adamdport, Jul 03 '14 at 01:56
much more readable (arguable) and broken :-D So - can it be actually a string literal here or is it always `now()`? If so - why not put it in a query as-is? — zerkms, Jul 03 '14 at 01:59
I guess that's a fair point, though I do like the idea of ALL of my insert data being plain and clear inside one object, rather than being scattered about. I award you 'last resort' status. — adamdport, Jul 03 '14 at 02:10
Can I ask why this got down-voted? I'd be happy to clarify if I wasn't clear. — adamdport, Jul 03 '14 at 13:16
Just to be clear - it's not me. +1 because it doesn't deserve it indeed. — zerkms, Jul 03 '14 at 20:32

score 4 · Accepted Answer · answered Feb 27 '18 at 18:14

4

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

answered Feb 27 '18 at 18:14

Yasin Yaqoobi

1,888
3
27
38

1

[added 3.5 years after my question](https://github.com/mysqljs/mysql/blob/master/Changes.md#v2150-2017-10-05) but looks like exactly what I needed at the time. Thanks for necromancing this post! – adamdport Feb 27 '18 at 18:29

score 0 · Answer 2 · answered Oct 26 '15 at 16:09

Basically, a direct implementation of NOW() is not possible - see link here

Alternate solution/suggestion is also mentioned in the link above.

var config = require('./config');
var SqlString = require('mysql/lib/protocol/SqlString');
var mysql = require('mysql');

config.mysql.queryFormat = function(sql, values, timeZone) {
  sql = SqlString.format(sql, values, false, timeZone);
  sql = sql.replace(/'NOW\(\)'/g, 'NOW()'); 
  sql = sql.replace(/'UNIX_TIMESTAMP\(\)'/g, 'UNIX_TIMESTAMP()'); // if you want
  return sql;
};

var pool = mysql.createPool(config.mysql);

Of course, for something as straight forward as your example, one could always do

var data = ['foo'];

connection.query('INSERT INTO users (username) VALUES = (?, NOW())', data,function(err, result){ ... });

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

2 Answers2