How to add a string containg a double quote to a sql database?

Question

I want to add a string to my sql database using JDBC. But the problem is that whenever the string contains a double quote, then the sql command is interpreted completely differently and a "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax" gets thrown."

For example, String ssql = "INSERT INTO tableName VALUES (\""+ string + "\")";

if string = "abc", then sql = INSERT INTO tableName VALUES ("abc")
But if string = "ab\"cd", then sql = INSERT INTO tableName VALUES ("ab"c")


And hence for a string that contains a double quote, the sql command is interpreted completely differently.

How can I add such a string to the database.

PS. I cannot afford to change the double quote to a single quote. And there can be other hacks to add such a string but I want to know if there really is no such way of adding such a string directly.

Answer 1

You need to escape the string value to make a string literal in the query.

When you are using quotation marks to delimiter the string:

• A backslash (\) in the string should be replaced by two backslashes.

• A quotation mark (") in the string should be replaced by a backslash and a quotation mark.

If you would use apostrophes (') to delimiter the string (which is more common in SQL), you would escape the apostrophes instead of the quotation marks.

If possible, you should use parameterised queries, instead of concatenating the values into the queries.