Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

apostrophe ( ' ) issue in SQL of Hibernate.

 
Awais Bajwa
Ranch Hand
Posts: 192
  • Mark post as helpful
  • send pies
  • 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
 
Jeff Albertson
Ranch Hand
Posts: 1780
  • Mark post as helpful
  • send pies
  • 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic