Mysql bulk insert with nodejs throws error: ER_PARSE_ERROR 1064 due to single quotes

Question

Basically when insert into table SET ? is used, the mysql npm package is scapping the quotes and that throws a parser error.

Code

var rows = [ { name: '100',
    value: '100',
    description: '100',
    type: 'P',
    scope: 'L' },
  { name: '101',
    value: '101',
    description: '101',
    type: 'P',
    scope: 'L' } ];

var sql = `INSERT into mytable SET ?;INSERT into mytable SET ?;`

connection.query(sql, rows, function(bulkInsertErr, bulkResult) {
  ....
});

Error Log

{ Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
 'INSERT into variable SET `name` = '101', `value` = '101', `description` = '101',' at line 1
    at Query.Sequence._packetToError (/foo/bar/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/foo/bar/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/foo/bar/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/foo/bar/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/foo/bar/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/foo/bar/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/foo/bar/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/foo/bar/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (events.js:198:13)
    at addChunk (_stream_readable.js:288:12)
    --------------------
    at Protocol._enqueue (/foo/bar/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/foo/bar/node_modules/mysql/lib/Connection.js:198:25)
    at /foo/bar/database/repository/VariableRepository.js:124:18
    at DatabaseConnection.getConnection (/foo/bar/database/DatabaseConnection.js:65:9)
    at VariableRepository.bulkInsert (/foo/bar/database/repository/VariableRepository.js:123:24)
    at /foo/bar/routes/ApplicationVariableRouter.js:261:28
    at Query.<anonymous> (/foo/bar/database/repository/ApplicationVariableRepository.js:224:11)
    at Query.<anonymous> (/foo/bar/node_modules/mysql/lib/Connection.js:526:10)
    at Query._callback (/foo/bar/node_modules/mysql/lib/Connection.js:488:16)
    at Query.Sequence.end (/foo/bar/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24)
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlMessage:
   'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'INSERT into variable SET `name` = \'101\', `value` = \'101\', `description` = \'101\',\' at line 1',
  sqlState: '42000',
  index: 0,
  sql:
   'INSERT into variable SET `name` = \'100\', `value` = \'100\', `description` = \'100\', `type` = \'P\', `scope` = \'L\';INSERT into variable SET `name` = \'101\', `value` = \'101\', `description` = \'101\', `type` = \'P\', `scope` = \'L\';' }

As you can see, the generated sql string is:

INSERT into variable SET `name` = \'100\', `value` = \'100\', `description` = \'100\', `type` = \'P\', `scope` = \'L\';
INSERT into variable SET `name` = \'101\', `value` = \'101\', `description` = \'101\', `type` = \'P\', `scope` = \'L\';

If I paste this to my mysql ide, throws the same error obtained in nodejs:

But if I fix replacing \' by ' in the ide, the rows are inserted.

I'm using ubuntu and this npm package "mysql": "2.18.1"

So, how can I fix that?

Answer 1

I'm not entirely sure that it is escaping your single quotes badly when executing the query, I think that it is just escaping them in the debug print.

I do not believe you can use the SET syntax in the mysql library for batch inserts by default. The reason being that you would need to include multiple statements within a single query which is not enabled by default, see here https://www.npmjs.com/package/mysql#multiple-statement-queries.

If you try something like this do you still get the same error?

var rows = [ { name: '100',
    value: '100',
    description: '100',
    type: 'P',
    scope: 'L' },
  { name: '101',
    value: '101',
    description: '101',
    type: 'P',
    scope: 'L' } ];

connection.query(
    'INSERT INTO variable (name, value, description, type, scope ) VALUES ?',
    [rows.map(row => [row.name, row.value, row.description, row.type, row.scope])],
    function(bulkInsertErr, bulkResult) {
  ....
});

Answer 2

It seems that the problem is not the escaping (due the fact it is only a view of your error between two '). Furthermore the error is near the second query (with values 101 and not 100, so the first query is correct)

You are using a multiple query statement that is disabled by default

You should:

• or exec one query at time (with a loop, but be careful with async loops)

• or use VALUES syntax of mysql that allows you to insert multiple values:

var rows = [
   ['100', '100', '100', 'P', 'L'], 
   ['101', '101', '101', 'P', 'L'] 
];

var sql = `INSERT into mytable (name, value, description, type, scope) VALUES 
${(new Array(rows.length)).fill("(?)").join(",")}
`
// is equivalent to => 'INSERT into mytable (name, value, description, type, scope) VALUES(?),(?)'

• or enable multiple statements:

mysql.createConnection({multipleStatements: true});

Answer 3

It's unclear from your example why you want to run the same query twice at the same time. If you would like to do so, you need to enable the multiple queries option for the module. You also need to provide two variables to the second argument of the query function.

Below are two examples. One assumes you want to include all the entries from your array of objects with one query (this is the correct way to do this). The second assumes you want to do that twice as your code snippet seems to suggest - this will create duplicate records and is only provided as a point of reference.

const mysql = require('mysql');

const connection = mysql.createPool({
  connectionLimit: 10,
  host: process.env.DB_HOST || '127.0.0.1',
  user: process.env.DB_USER || 'local_user',
  password: process.env.DB_PASSWORD || 'local_password',
  database: process.env.DB_NAME || 'local_database',
  multipleStatements: true, // necessary to run chained queries
  charset: 'utf8mb4' // necessary if you might need support for emoji characters
});


let rows = [
{
  name: '100',
  value: '100',
  description: '100',
  type: 'P',
  scope: 'L'
},
{
  name: '101',
  value: '101',
  description: '101',
  type: 'P',
  scope: 'L'
}];

// Create two records from the entries in array rows
// with one query
let keys = Object.keys(rows[0]);
let values = rows.map( obj => keys.map( key => obj[key]));
let sql = 'INSERT INTO mytable (' + keys.join(',') + ') ?;'
connection.query(sql, values, function(error, results, fields) {
  if (error) throw error;
  console.log(results);
});

// Create four records from the two entries in array rows
// by running the same query twice
let keys = Object.keys(rows[0]);
let values = rows.map( obj => keys.map( key => obj[key]));
let sql = 'INSERT INTO mytable (' + keys.join(',') + ') ?; INSERT INTO mytable (' + keys.join(',') + ') ?;'
// Note: since we are using two placeholders in the query
// we must provide two variables that will be mapped to each
// in the second argument of the query function.
// Since more that one is provided, they must be provided as
// an array.
connection.query(sql, [values,values], function(error, results, fields) {
  if (error) throw error;
  console.log(results[0]); // the results of the first query
  console.log(results[1]); // the results of the second query
});