placeholder injection fails

[joelabair]

// node v4.6.1
// [email protected]

 var Client = require('mariasql');

 var id = 8;
 var c = new Client({host: '127.0.0.1', port: 3306, db: 'test'});

 var SQL = 'SELECT DATE_FORMAT(CONVERT_TZ(a.updated, "SYSTEM", "UTC"), "%Y-%m-%dT%T-00:00") as updated FROM `accounts` `a` WHERE (a.id = :id) LIMIT 1';
 var statement = c.prepare(SQL);

 c.query(statement({id: id}), function(err, rows) {
  if (err)
    throw err;
  console.dir(rows);
});

c.end();

// produces this error in the console
//  Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') LIMIT 1'8'' at line 1
//  at Error (native)

// which is caused by this SQL logged by the server
// SELECT DATE_FORMAT(CONVERT_TZ(a.updated, "SYSTEM", "UTC"), "%Y-%m-%dT%T-00:00") as updated FROM `accounts` `a` WHERE (a.id = ) LIMIT 1'8'

// the id is appended to the end of the SQL string not at the placeholder position

/*
  Database mariadb 10.1.18
  
  accounts TABLE
 
  CREATE TABLE `accounts` (
  `id` int(12) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `accountName` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT '0',
  `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_accountNames_in_account` (`accountName`),
  KEY `accountNames_enabled` (`accountName`,`enabled`)
) ENGINE=MyISAM AUTO_INCREMENT=544 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

*/

SQL = 'SELECT DATE_FORMAT(CONVERT_TZ(a.updated, "SYSTEM", "UTC"), "%Y-%m-%dT%T-0000") as updated FROM `accounts` `a` WHERE (a.id = :id) LIMIT 1';
 var statement = c.prepare(SQL);

 c.query(statement({id: id}), function(err, rows) {
  if (err)
    throw err;
  console.dir(rows);
});

c.end();

// Works as expected executing the SQL
// SELECT DATE_FORMAT(CONVERT_TZ(a.updated, "SYSTEM", "UTC"), "%Y-%m-%dT%T-0000") as updated FROM `accounts` `a` WHERE (a.id = '8') LIMIT 1

// clearly the error comes from the format string '"%Y-%m-%dT%T-00:00"'
// is there any way to escape the :00 ?

[joelabair]

so was this fixed in 0.2.6 ?

[ajxville]

This appears to still be an issue. I'm noticing it in 0.2.6 when a time literal is included in the query as well.