Making Java String usable for a SQL Script

Question

If I have a string that looks like this:

This is a string
with single quotes ' all over
the ' place as well as
return characters

How would I convert this to a string that can be used in an INSERT statement?

INSERT INTO MyTable VALUES ('<the above string>');

The string above has the problems that it has return characters as well as single quotes which would mess up the validity of the INSERT statement above.

Edit: Sorry I probably should have been more clear. I'm generating a SQL Script with INSERT statements, not executing SQL within a Java app.

Answer 1

Use PreparedStatement:

String sql = "INSERT INTO MyTable VALUES (?)";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, theAboveString);
ps.executeUpdate();

Answer 2

I'm generating a SQL Script with INSERT statements, not executing SQL within a Java app.

In that case, you'll have to generate an "escaped" version of the String. To do so, I'd suggest using the ESAPI library from the OWASP project (if possible). See Defense Option 3: Escaping All User Supplied Input for more details.

Answer 3

To put a single quote inside of an SQL string, use it twice.

so

insert into mytable
values ('isn''t it lovely?')

So when generating the sql script, just replace all single quotes with double quotes before tacking the beginning and ending single quotes onto it.

Answer 4

why dont use escape the single quotes like below

This is a string with single quotes \' all over the \' place as well as return characters