Escaping lost from pre-escaped query

[terje-rosenlund]

JSON.stringify escapes all special characters in string-values:

const query = UPDATE test SET jsonStr ='{"newline":"new\\nline"} WHERE id = 1;

The db result is an unescaped newline when the query is executed by pool.query while it is escaped when the same query is executed in mysql-cli

MySql log also shows that one backslash is missing when executed by a pool.query:

UPDATE test SET jsonStr = '{"newline":"new\nline"}' WHERE id = 1; ← pool.query UPDATE test SET jsonStr = '{"newline":"new\\nline"}' WHERE id = 1; ← mysql-cli

[dougwilson]

Hi @terje-rosenlund sorry you are having trouble. Can you provide both the DDL of the table you are trying to insert that in to as well as specific JavaScript to reproduce the issue? Above that you included is not actual JavaScript and to make it so, I may accidentally introduce differences that may affect the ability to reproduce exactly what is happening here.

[terje-rosenlund]

Attached testEscape.zip (DDL included, db password removed)

Modified query includes a \t which has the same problem so I presume all escaping gets lost

testEscape.zip

[terje-rosenlund]

Sorry for my impatience but this problem blocks my progress It would be nice if you could tell me if this is a bug or me not using it correctly

[dougwilson]

Hi @terje-rosenlund apologies, as I didn't see your original response. I just took a look and the issue is that you need to you add extra backslashes for JavaScript itself.

You see, in your mysql CLI example you wrote

UPDATE test SET jsonStr ='{"newline":"new\\nline\\ttab"}' WHERE id = 1

Which will send that to the server. But then you wrote the same thing in a JavaScript file. JavaScript itself will interpret backslashes in strings, so to actually match the number of backslashes you typed into the MySQL CLI in JavaScript, you would need to escape them (double them up). This is a property of the JavaScript language and not related to this library.

const query = `UPDATE testEscape SET jsonStr ='{"newline":"new\\\\nline\\\\ttab"}' WHERE id = 1;`; // ← Double slash: \\n

[terje-rosenlund]

Thank you for your answer which led me to a working solution I will however add a comment for others coming here:

It is not a good idè to use javascript to do the escaping for mysql Even though I got it to work for the most part I got problems with json strings containing single and double quotation marks

The solution was to use one of the recommended methods with placeholders and data or the connection.escape method which handles all special characters and is much safer in regards to injection attacks