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.

Pascal Thivent · Accepted Answer · 2010-09-23 02:08:38Z

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.

edited Sep 23, 2010 at 2:08

answered Sep 23, 2010 at 1:42

Pascal Thivent

572k140 gold badges1.1k silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

duffymo · Accepted Answer · 2010-09-23 01:41:42Z

2

Use PreparedStatement:

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

answered Sep 23, 2010 at 1:41

duffymo

310k46 gold badges376 silver badges570 bronze badges

1 Comment

digiarnie Over a year ago

I'm not executing the SQL however. I'm intending on generating a SQL Script that has INSERT statements in it for someone else to execute.

user158017 · Accepted Answer · 2010-09-23 01:57:24Z

0

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.

answered Sep 23, 2010 at 1:57

user158017

3,0011 gold badge31 silver badges37 bronze badges

Comments

Dead Programmer · Accepted Answer · 2010-09-23 07:08:23Z

0

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

answered Sep 23, 2010 at 7:08

Dead Programmer

12.6k23 gold badges83 silver badges113 bronze badges

Collectives™ on Stack Overflow

Making Java String usable for a SQL Script

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related