java sql query

[Raj Ohadi]

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]

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

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

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):

PreparedStatement ps = con.prepareStatement ("select address from station where name like ? ");
ps.setString(1,"%La'Plata%");

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

[David O'Meara]

yep, old 'Bobby Tables'

Nice post, Stevi

[Raj Ohadi]

Originally posted by Stevi Deter:
The quick and dirty answer is to escape the single quote with a second single quote:

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

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):

PreparedStatement ps = con.prepareStatement ("select address from station where name like ? ");
ps.setString(1,"%La'Plata%");

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]

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:

    
select address from station where name like '%\\%%' ESCAPE '\\'

This example should match any string that has a literal % character.