• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

apostrophe ( ' ) issue in SQL of Hibernate.

 
Ranch Hand
Posts: 196
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi GUys I am facing a problem in my SQL query..when I use apostrophe (')

Explaination:
I want to search Java's in the search crieteria

so my SQL looks like

I used java's in my search textbox and following broblem is created in the

QUERY:
sql=
select distinct project from Project as project where project.projectStatus.projectStage = 1 and ( project.deleted is null or project.deleted <> 1 ) and ( project.inactive is null or project.inactive <> 1 )and ( project.additionalSkills like '%java's%' or project.summary like '%java's%' or project.version like '%java's%' or project.projectRequirements like '%java's%' ) Order By project.id

Code:

Session s = null;
s = getSession();
s.find(sql);

ERROR:
Could not execute query
java.sql.SQLException: Syntax error or access violation message from server: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's% ' or project.summary like ' %java 's%' )or(project0_.VERSION like '%java' s% ' at line 1"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)

Solution I tried:

I tried replacing ( ' ) with \' using standard String.replaceAll() method

using the following code .

Session s = null;
s = getSession();
s.find(sql.replaceAll("'", "\'"));


but still get the same error ...I think the find method is again using the (') as it is ... any idea or solution will be highly appreciated.


Awais Bajwa
awaisbajwa@gmail.com
 
Ranch Hand
Posts: 1780
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Depending on your database you should replace those embedded single quotes with either two single quotes in a row or a double quote: '' or " (is that readable?).

What I think is even better, and for lots of other reasons too, is to use a PreparedStatement + setString -- the setString method will take care of single quotes automatically.
reply
    Bookmark Topic Watch Topic
  • New Topic