I am currently facing an issue where I need to store a JSON object in the database to retrieve it later and manipulate it using JavaScript. However, I am encountering a problem I have never faced before. When I store the JSON object as a String and then try to retrieve it, the quotes are lost, making it impossible to escape the String and convert it back to a JSON object.
For example, the JSON string that is sent:
{"footer":"this is a double quote \"","header":""}
The String that is written to the database:
{"footer":"this is a double quote "","header":""}
This results in an error when trying to convert it back to JSON.
In my code:
// My SQL
String sql = "UPDATE table SET val='%s' WHERE 1";
// The JSON string to be stored (This value is stored in a variable, not as a literal)
String jsonString = // {"footer":"this is a double quote \"","header":""};
// Create JDBC statement
con.prepareStatement(String.format(sql, jsonString)).execute();
// The above SQL statement generates the toString();
UPDATE table SET val='{"footer":"this is a double quote \"","header":""}' WHERE 1
// The following String is recorded in the database
{"footer":"this is a double quote "","header":""}
My table is configured with the following settings: utf8 utf8_general_ci
Why is this happening and what can I do to solve it?
Thank you.