How to escape double and single quotes in a JSON array

I’m trying to create a chat system where every time someone sends a message it gets added to the JSON array in my database for that specific person, but the issue I’m coming across is when anyone tries to use double quotes " " or a single quote ' in their message. Anytime a message is added with these special characters I get the 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. I’ve looked on the internet for similar issues and all of them say that I need to escape the double quotes with ", but I’m already doing that when I use the JSON.stringify method.

Unfortunately, this doesn’t work in a JSON array instead I’ve had to replace the double quotes with \" but when I use the JSON.parse method I’m seeing the backslash in my output. And if I just use a single slash I get an SQL parsing error.

The message being sent has an ID, Timestamp, Message, and the name of the person who sent it. [{"ID": 1, "Timestamp": "10/20/2020 11:00 AM", "Msg": "Hello There", "Name": "John"}]

An example of what I’m trying to attempt is below:

let previousMessages = JSON.parse(this.state.Messages);
let newMsg = `testing double "quotes" and single quote's`
previousMessages.push({ID: previousMessages.length+1, Timestamp: new Date(), Msg: newMsg.replace(/"/g,'\"'), Name: "John"});

The query to my database looks like this:

UPDATE table1 SET Messages = "${JSON.stringigy(previousMessages)}" WHERE ID = '1';
// output: UPDATE table1 SET Messages = "[{"ID":1,"Name":"John","Msg":"testing double \"quotes\" and single quote's","TimeStamp":"10/20/2020 11:00 AM"}]" WHERE ID = '1';

The error I receive is this:

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 'ID":1,"Name":"John","Msg":"testing double \"quotes\" and single quote's","T' at line 2

Things I’ve Tried

I’ve tried replacing the quotes with other characters like replacing ' with _ which works but it seems really unnecessary to have to do that every time when I want to create a message.

I’ve also tried using single quotes instead of double quotes in my SQL query like so: UPDATE table1 SET Messages = '${JSON.stringify(previousMessages)}' WHERE ID = '1'; but this didn’t make a difference

If anyone knows a good way on how to escape these characters in a JSON array that would be super helpful. I feel like there has to be a simpler way to escape these characters than having to have two different replace methods for every single one.

Answer

If you are writing the SQL directly then, given a valid JSON text you need to:

• Surround it with single quotes
• Escape any single quotes in it
• Escape any escape sequences

You don’t need to do anything with double quotes.

So the JSON: [1, "John O'Brian", { "hello": "world" }]

Would become Messages='[1, "John O'Brian", { "hello": "world" }]'

---

If you want to generate that with JS then it would look something like:

const array = [1, "John O'Brian", { "hello": "world" }];
const json = JSON.stringify(array);
const sql = `UPDATE table1 SET Messages='${json.replace(/\/g, "\\").replace(/'/g, "\'")}' ...`

… but you shouldn’t do that.

Use an API that lets you use placeholders and leave it up to the database engine to figure out how to use the quotes. It’s easier, less prone to errors, and more secure.

e.g. with the mysql module from NPM:

const array = [1, "John O'Brian", { "hello": "world" }];
const json = JSON.stringify(array);
connection.query(
    "UPDATE table1 SET Messages=? ....",
    [ json ],
    function (error, results, fields) {

     });