How to escape quotes "" characters in MySQL and Java

Question

How can we escape quotes "" characters in Java and MySQL?

An incoming XML file has quotes, and I am parsing through that file using Java. So I want to escape the quotes here, but in the database it should contain quotes. When I am doing a query the result would have quotes. While displaying on a webpage it should also show quotes.

Answer 1

Let me try and understand...

The incoming file has quotes in it. You want to send it to a database. When you get it back from the database then you still want those quotes to be there.

So is it just to/from the database that you are having your issue?

If so then I highly suspect you are doing something on the order of: (I'm wrapping it in a disclaimer to keep the unsuspecting from misunderstanding and cutting/pasting into their own applications. ;))

Bad - do not do this

String sql = "insert into foo (bar,baz) values(" +myValue1 + ", " + myValue2 + ")";
Statement stmt = connection.createStatement();
stmt.executeUpdate(sql);

Bad - do not do that

If so then you should really be using prepared statement's parameters at a minimum. a) you will be less vulnerable to malicious garbage deleting all of your tables, and b) you will not have any escaping problems.

String sql = "insert into foo (bar, baz) values( ?, ? )";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString(1, myValue1);
stmt.setString(2, myValue2);
stmt.executeUpdate();

Note that it's also safer in the case of things like CLOBs and the specifics of different database implementations (I'm thinking of you, Oracle >))

If it is some other kind of escaping, that is, to/from XML or to/from HTML then that's different, but it is well documented all over the web.

Or provide some example code if I'm totally off base.