File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes single quote problem with sql string Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "single quote problem with sql string" Watch "single quote problem with sql string" New topic
Author

single quote problem with sql string

sinasi susam
Ranch Hand

Joined: Jul 15, 2005
Posts: 67
i encountered an interesting problem.
if i build a string contains "'", it is making problems when i try to execute it with Statement.

if use prepared statement it doesnt make problems.It is executing properly by seting a value contains "'".And never fails.

Is there any other way to execute it with Statement because my query is not so long and Statement is enough for it.But it throws SQLException such as "quoted string not properly ended."

We have to think about it because if a string is read from db and used in another sql string and if it contains single quote it will make problems.

Then i reach that i always have to use prepared statement not to fail again..So why use Statement ya?

Do you know any other way to get rid of that?
[ February 16, 2006: Message edited by: sinasi susam ]
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
'' (using the single quote character twice) is what you have to use to specify the single quote as an actual character value in the string. Better to just use prepared statement bind items and avoid the problem.


Reid - SCJP2 (April 2002)
Jeff Albertson
Ranch Hand

Joined: Sep 16, 2005
Posts: 1780
Why do people resist PreparedStatements? Resistence is futile!
[ February 16, 2006: Message edited by: Jeff Albertson ]

There is no emoticon for what I am feeling!
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
The determination to write dynamic sql seems to be a force of nature. I wish all books about JDBC just moved it into an appendix at the back.
sinasi susam
Ranch Hand

Joined: Jul 15, 2005
Posts: 67
Umm why to resist?
I am not resisting.I have never think that also.
But i just wonder why you speaking as if it is super?Preparedstatement has its own purpose.

And for now i am out of it.If its complately useless so why sun is still putting it in JDBC?Also in new versions such "Tiger" ?And allright there will exist in mustand and dolphin too.
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Originally posted by sinasi susam:

Is there any other way to execute it with Statement because my query is not so long and Statement is enough for it.

[ February 16, 2006: Message edited by: sinasi susam ]


Use PreparedStatement, use PreparedStatement, use PreparedStatement.
- It's more secure (Google for "SQL injection attack")
- you never have to worry about escape characters for quotes
- you reduce the risk of coding an implicit datatype conversion, which is somtehing that is likely to break when the code is ported to another database or database settings are changed
- on many databases, it's faster, especially when many queries are running at once

Using Statement when you can use PreparedStatement is almost always a mistake, often a major one. When my company interviews a Java programmer who claims to know JDBC, we gave him a chance to make that mistake; if he does, we often don't hire him.
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
The "resistence" comments aren't about you, just about something that has become almost a constant theme on this forum. Consider it a "welcome to the family" ribbing.

Two parts to the issue:

- why were both created in the first place
- when would you use one versus the other

Historically PS was much slower to create, faster to execute, so you tended to create them more when you would re-use the statement. That really isn't the case any longer. Somebody recently posted some stats here on the forum, you pretty much always win with PS.

The fundamental issue isn't "Statement versus PreparedStatement", it is dynamic SQL versus SQL with bind items. Statement only allows the former. PreparedStatement allows both. Sun isn't going to deprecate Statement because there is too much code out there using it.

So, looking at dynamic SQL versus SQL with bind items, the reasons are many, but at a quick pass:

- you end up creating more hacks to get around parsing and data conversion problems with dynamic SQL
- dynamic SQL is much harder for most databases to process, so it is almost always slower
- dynamic SQL solutions don't scale as well in some database (like Oracle); they can support more query threads for SQL with bind items than they can for dynamic SQL.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42274
    
  64
If its complately useless so why Sun is still putting it in JDBC?

Because Sun has never removed a single method call from the Java API. The most that may happen is that they become deprecated - even those methods that are actually broken, not just those that have outlived their usefulness.


Ping & DNS - my free Android networking tools app
sinasi susam
Ranch Hand

Joined: Jul 15, 2005
Posts: 67
Thats ok.I know we never have to worry about quoted Strings.

But the first one, how can an attacker can do that if i am developing many-tier application?And A Serious Application usually made in form of many-tier application.
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
Sometimes people build webapps that just shovel form fields blindly into a dynamic SQL string. If a hacker reads the source of your pages they can figure out other combinations to put into a GET or POST in order to extract data that you hadn't intended to extract, or modify data in a way you hadn't intended to modify. If you have form/request validation that blocks these you may be safe, but you have to pay attention to what is possible to do in the request parameters in combination, not just if any one field validates to an expected format (number, date, whatever).
 
 
subject: single quote problem with sql string