Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

java sql query

 
Raj Ohadi
Ranch Hand
Posts: 316
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 265
Hibernate Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yep, old 'Bobby Tables'

Nice post, Stevi
 
Raj Ohadi
Ranch Hand
Posts: 316
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 265
Hibernate Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic