permaculture playing cards
The moose likes JDBC and Relational Databases and the fly likes Problem with single quote Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Problem with single quote " Watch "Problem with single quote " New topic

Problem with single quote

rita mistry

Joined: Dec 04, 2001
Posts: 27
Hi All,
I've set up a form on a jsp page where I have both a text field and two textarea boxes. The only problem is when I have a string of text which includes a single quote e.g.
Why won't this work?
I get an error similar to the following:
java.sql.SQLException: [Microsoft][odbc SQL Server Driver] [SQL Server]Line 1: Incorrect syntax near 'why'.
When I take out the single quote it works. Why is it causing an error?
Any help will be very much appreciated.
Many Thanks
Dave Vick
Ranch Hand

Joined: May 10, 2001
Posts: 3244
This is more of a JDBC question than a general one. Check out the JDBC forum for your answer. This is a fairly poplar question and has been asked quite often in the past. you can also do a search of the JDBC forum for your answer too - that would probably be the fastest way to get an answer.
Here's a hint for you to get you started - 'preparedStatement'.

Steve Deadsea
Ranch Hand

Joined: Dec 03, 2001
Posts: 125
When you put user submitted data into a database using sql you have to be very careful. The main problem is that sql inserts are a string of code that gets interpretted. The data is usually between quotes or single quotes. If a malicious user were able to put a quote or a single quote into their data they would be able to insert sql code that could do things like delete all the records in your database.
The way to deal with this is to escape any quotes in the data. Actually there are a whole range of characters that need to be escaped to be safe. I have written a method to do so. You can find it at:
Similarly, if you write the data back out to html. You must make sure that there is no html characters in it or that all html characters are properly escapes. Otherwise you run the risk of cross site scripting vulnerabilities. A malicious cracker could direct a user to your site with a script in the data that would steal the users cookies and send them to the cracker. There is also a function in StringHelper to escape the data for html.
rita mistry

Joined: Dec 04, 2001
Posts: 27
Thanks for all your help guys.
And Steve your information is really helpful. So cheers mate.
Kind Regards,
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15302

Or you could use a PreparedStatement instead of a Statement. That will solve your escape character problem you are having.

GenRocket - Experts at Building Test Data
Don't get me started about those stupid light bulbs.
subject: Problem with single quote
It's not a secret anymore!