single quotes and double quotes in mysql insert and update query (java)

Question

The ComName is 'a'b'c'"def"j Limited.

i try to add \ before every ' and " but the resultant query that is executed is

"UPDATE empTable SET empId= '25', ComName = 'a'b'c'"def"j Limited  where ID=1"

i don't see my Comname within '' and \ is not present before every ' and "

Here is the code to construct the column value

columnValue.replaceAll("\'", "\\" + "\'");
columnValue.replaceAll("\"", "\\" + "\"");
columnValue=("'" + columnValue + "'");

How to insert string of these types?

Answer 1

Take a look at PreparedStatements in the JDBC library. e.g.

PreparedStatement pstmt = con.prepareStatement("update Orders set pname = ? where Prod_Id = ?");
pstmt.setInt(2, 100);
pstmt.setString(1, "Bob");
pstmt.executeUpdate();

By using setString() etc. it will save you from having to quote strings. Here's a tutorial on how to use them.

EDIT: As Nick has highlighted below, this will save you not just from quoting issues, but from SQL injection (security) issues as well.

Answer 2

as quick fix you can try using Apache commons lang StringEscapeUtils#escapeSql or equivalent library such as this one