4

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.

Nikunj Banka
  • 11,117
  • 16
  • 74
  • 112
  • possible duplicate of [MySQL quotes, double quotes mess](http://stackoverflow.com/questions/20827036/mysql-quotes-double-quotes-mess) – mc110 Jul 11 '14 at 12:56

5 Answers5

7

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.

Guffa
  • 687,336
  • 108
  • 737
  • 1,005
3

You're experiencing benign SQL injection. Be lucky string was not Little Bobby Tables.

Instead you should use parameterized queries like INSERT INTO tableName VALUES (?) and send the value of string via a parameter.

For more details, see the JDBC documentation at http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html


Now, to answer the question: Assuming the string does not come from a user source and instead you are trying to write the query yourself, you can replace " with \" in the string before concatenating.

lc.
  • 113,939
  • 20
  • 158
  • 187
0

Per MySQL Spec it recignizes \" as escaping double quote. you can as well use two double quote "" to escape double quote " character

\" A double quote (“"”) character

Rahul
  • 76,197
  • 13
  • 71
  • 125
0

change this

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

to

String ssql = "INSERT INTO tableName VALUES ('"+ string + "')";

Or simplify it with a PreparedStatement like this

String ssql = "INSERT INTO tableName VALUES (?)";
preparedStatement.setString(1,"string");

When you want to insert double-quotes you need to escape them something like this

String ssql = "INSERT INTO tableName VALUES (?)";
preparedStatement.setString(1,"\"string\"");

This shall insert "string" in the table

SparkOn
  • 8,806
  • 4
  • 29
  • 34
-1

I tried adding the string by escaping the double quote but that didn't work. The only way I could add the string is by using PreparedStatement instead of a Statement class.

String string = "abc\"abc";
String sql = "INSERT INTO tableName VALUES(?)";

connection = DriverManager.getConnection(DB_URL, USER, PASS);
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, string);
preparedStatement.executeUpdate();

This code segment successfully adds the string containing the double quote into the database.

Nikunj Banka
  • 11,117
  • 16
  • 74
  • 112