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
– zerkms, Commented Jul 3, 2014 at 1: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
– adamdport, Commented Jul 3, 2014 at 1: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
– zerkms, Commented Jul 3, 2014 at 1: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
– adamdport, Commented Jul 3, 2014 at 2:10
Can I ask why this got down-voted? I'd be happy to clarify if I wasn't clear. — adamdport
– adamdport, Commented Jul 3, 2014 at 13:16

Yasin Yaqoobi · Accepted Answer · 2018-02-27 18:14:07Z

5

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, 2018 at 18:14

Yasin Yaqoobi

2,0683 gold badges31 silver badges39 bronze badges

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

1 Comment

adamdport Over a year ago

added 3.5 years after my question but looks like exactly what I needed at the time. Thanks for necromancing this post!

Kaya Toast · Accepted Answer · 2015-10-26 16:09:44Z

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){ ... });

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related