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 java sql query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "java sql query" Watch "java sql query" New topic
Author

java sql query

Raj Ohadi
Ranch Hand

Joined: Jun 30, 2006
Posts: 316
In jdbc code if I need to do some query like

select address from station where name like '%La'Plata%';

If does not work. I want to query rows whose name include La'Plata. But I guess since it includes a special character ('), it doesn't work. How should this be fixed ?
Stevi Deter
Ranch Hand

Joined: Mar 22, 2008
Posts: 265

The quick and dirty answer is to escape the single quote with a second single quote:


However, it's far better to use parameterized queries if you're going to be accepting input from users to avoid SQL Injection attacks (and just good practice in any case):



This allows you to avoid worrying about escaping special characters while minimizing security risks like this one


There will always be people who are ahead of the curve, and people who are behind the curve. But knowledge moves the curve. --Bill James
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

yep, old 'Bobby Tables'

Nice post, Stevi
Raj Ohadi
Ranch Hand

Joined: Jun 30, 2006
Posts: 316
Originally posted by Stevi Deter:
The quick and dirty answer is to escape the single quote with a second single quote:


However, it's far better to use parameterized queries if you're going to be accepting input from users to avoid SQL Injection attacks (and just good practice in any case):



This allows you to avoid worrying about escaping special characters while minimizing security risks like this one


Thanks Stevi. I like the 2nd approach. but for the 1st approach, what if my search term includes a special char like %, should I still use ' as the escape ?
Stevi Deter
Ranch Hand

Joined: Mar 22, 2008
Posts: 265

Raj,

For the wildcard characters '_' and '%' in a Like statement, I think you need to use the escape syntax, which lets you define the escape character sequence:



This example should match any string that has a literal % character.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: java sql query
 
Similar Threads
Hibernate query using named parameter and %
Dynamically setting schema name for native queries in hbm file
can't run query from java war
HQL query problem
Need help with a query (HQL)