getting the date in the outfile of mysql query in node js code

Question

I am trying to achieve writing a outfile in sql including the timestamp.:

sql query:SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s');
SET @FOLDER = 'now';
SET @EXT    = '.csv';
SET @CMD = CONCAT("SELECT * FROM test.extract INTO     OUTFILE'",@FOLDER,@TS,@EXT,"' FIELDS TERMINATED BY ',' LINES TERMINATED BY     '\n';");
PREPARE statement FROM @CMD;
EXECUTE statement;

If I give the same query in the node js script given below it is failing. can someone help.

var mysql      = require('mysql');
var connection = mysql.createConnection({
host     : 'localhost',
user     : 'root',
password : 'root',
database : 'test'
});

connection.connect();

var timeInMs = Date.now();
console.log(timeInMs);


connection.query("SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s');
SET @FOLDER = 'now';
SET @EXT    = '.csv';
SET @CMD = CONCAT(
"SELECT * FROM test.extract INTO OUTFILE'",@FOLDER,@TS,@EXT,"' FIELDS     TERMINATED BY ',' LINES TERMINATED BY '\n';");

PREPARE statement FROM @CMD;
EXECUTE statement;",function(err, rows, fields) {
if (!err)
  console.log('The solution is: ', rows);
else
  console.log('Error while performing Query.');
});
connection.end();

Answer 1

According to the documentation of the mysql nodejs module:

Support for multiple statements is disabled for security reasons (it allows for SQL injection attacks if values are not properly escaped). To use this feature you have to enable it for your connection:

var connection = mysql.createConnection({multipleStatements: true});

Also, for multi-line to work properly, put it in backticks. The following code works for me:

var mysql      = require('mysql');
var connection = mysql.createConnection({
host     : 'localhost',
user     : 'root',
password : 'root',
database : 'test',
multipleStatements: true
});

connection.connect();

var timeInMs = Date.now();
console.log(timeInMs);

connection.query(`SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s'); 
SET @FOLDER = 'now';
SET @EXT  = '.csv';
SET @CMD = CONCAT(
"SELECT * FROM mysql.user INTO OUTFILE'",@FOLDER,@TS,@EXT,"' FIELDS     TERMINATED BY ',' LINES TERMINATED BY '\n';");
PREPARE statement FROM @CMD;
EXECUTE statement;`    
,function(err, rows, fields) {
if (!err)
  console.log('The solution is: ', rows);
else
  console.log('Error while performing Query.', err);
});
connection.end();