Insert data to sql using java issue

Question

I'm facing an issue with insertion to SQL database from java code. I'm using INSERT sql query using the java code to enter the data from XML file to SQL database. You may suppose column named "Description". Imagine there is a record in XML which contains apostrophe ('). The program crashes due to the error caused by the apostrophe which is included in the data. I know that manually we can add another apostrophe and make it work, but imagine data of 10.000 records, how can we handle this issue?

Also it's always good to mention the actual DBMS you are using (Oracle, Postgres, ...) — user330315
– user330315, Commented Dec 6, 2013 at 7:49

T.J. Crowder · Accepted Answer · 2013-12-06 08:26:09Z

3

Don't do this (string concatenation):

String sql = "insert into MyTable (description) values ('" + value + "')";
Statement st = connection.createStatement();
st.executeUpdate(sql);

Do do this (prepared statement):

PreparedStatement ps = connection.prepareStatement(
    "insert into MyTable (description) values (?)"
);
ps.setString(1, value);
pt.executeUpdate();

The value will get correctly escaped for you. Not only does this protect against mishaps like the one you mentioned, it also helps defend you from SQL injection attacks.

Humorous illustration:

Exploits of a mom

Source

edited Dec 6, 2013 at 8:26

answered Dec 6, 2013 at 7:52

T.J. Crowder

1.1m201 gold badges2k silver badges2k bronze badges

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

Comments

Elliott Frisch · Accepted Answer · 2013-12-06 07:51:28Z

0

You have two options, you should use PreparedStatement and bind your parameter(s). Or, if you really, really, want - you could use StringEscapeUtils.escapeSql(str).

answered Dec 6, 2013 at 7:51

Elliott Frisch

202k20 gold badges166 silver badges265 bronze badges

2 Comments

Jayamohan Over a year ago

Please note that since commons 3.0 StringEscapeUtils.escapeSql(str) is no longer supported.

Elliott Frisch Over a year ago

I certainly wouldn't recommend it over a PreparedStatement.

Collectives™ on Stack Overflow

Insert data to sql using java issue

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related