CODEWITHSUNDEEP
javasql
digiarnie
digiarnie

Reputation: 23345

Making Java String usable for a SQL Script

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.

Upvotes: 0

Views: 318

Answers (4)

Dead Programmer
Dead Programmer

Reputation: 12575

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

Upvotes: 0

Pascal Thivent
Pascal Thivent

Reputation: 570355

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.

Upvotes: 2

user158017
user158017

Reputation: 2991

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.

Upvotes: 0

duffymo
duffymo

Reputation: 308763

Use PreparedStatement:

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

Upvotes: 2

Related Questions