REPLACE INTO ?? (??) VALUES (?) not working due array literals not escaped

[rogierschouten]

REPLACE INTO ?? (??) VALUES (?) not working due array literals not escaped

Running the statement above does not work (error is below). I found out that it is the very last question mark of the query that doesn't work: if I flatten only the last value array and use multiple question marks then it works. Presumably the values are not escaped.

Table schema:

CREATE TABLE `version` (
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Key.',
  `value` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Value.',
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Database schema version.';

Error message:

SqlError: ER_BAD_FIELD_ERROR: Unknown column 'schema_version' in 'field list' in statement 'REPLACE INTO ?? (??) VALUES (??), ['version', [ 'name', 'value' ], [ 'schema_version', '1' ]]'

README.md says: "Arrays are turned into list, e.g. ['a', 'b'] turns into 'a', 'b'."
So I figure it should be possible. Am I doing something wrong?

[adrianblynch]

Could you post your JS code?

[rogierschouten]

Hi,

Sorry I'm working through the any-db module so not calling mysql directly. As any-db does not do value-escaping and googling the error led me to believe the problem was an unescaped value, I supposed it had to be the underlying mysql module that was at fault. However the following code works, i.e. mysql works fine:

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

connection.query('REPLACE INTO ?? (??) VALUES (?)', 
    ['version', [ 'name', 'value' ], [ 'schema_version', '1' ]], 
    function(err, rows) {

  if (err) {
    console.error(err);
  } else {
    console.log("OK!");
  }

});