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.