Not able to escape JSON.stringify string containing single quotes (')

[varun2410]

While Inserting into the table from DBeaver the query is running fine, but when i am trying to the run the same query from the mysqljs, its throwing the syntax error.

Query
INSERT INTO CHECK1 (testField) VALUES ('{"keywords":["1/4 pounder\'s"]}')

JS Code
mysql.query('INSERT INTO CHECK1 (testField) VALUES (?)', [JSON.stringify(data.params)]);

From what i am able to figure out is the its not able to escape the single quotes ('), and that is what is throwing the error.

Error Thrown
UnhandledPromiseRejectionWarning: 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 's\"]}' at line 1

[sidorares]

In your example generated sql is slightly different: INSERT INTO CHECK1 (testField) VALUES ('{\"keywords\":[\"1/4 pounder\'s\"]}') - not sure what's wrong exactly, but mysql server complains about this syntax

> m = require('mysql')
> console.log(m.format('INSERT INTO CHECK1 (testField) VALUES (?)', [JSON.stringify({"keywords":["1/4 pounder\'s"]})]))
INSERT INTO CHECK1 (testField) VALUES ('{\"keywords\":[\"1/4 pounder\'s\"]}')
undefined
>

[varun2410]

@sidorares Thanks for the reply.
If you try to run this via any mysql client (i am using dbeaver), its working fine.

Here is the schema i am using

CREATE TABLE CHECK1 ( testField MEDIUMTEXT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Here is the sqlfiddle link - http://sqlfiddle.com/#!9/7b5dad/1

I am not able to understand if it is my code issue or something problem with mysqljs

[varun2410]

Hi @sidorares
Thanks for the help. Turns out there was a problem in SQL Query only. Instead of escaping apostrophe with a backslash(), we need to use 2 single quotes ('') to escape it.

[dougwilson]

Instead of escaping apostrophe with a backslash(), we need to use 2 single quotes ('') to escape it.

This would be the case if you have NO_BACKSLASH_ESCAPES mode enabled. In order to use our escaping, you cannot disable that mode. See https://github.com/mysqljs/mysql#escaping-query-values

Caution These methods of escaping values only works when the NO_BACKSLASH_ESCAPES SQL mode is disabled (which is the default state for MySQL servers).