File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes apostrophe ( ' ) issue in SQL of Hibernate. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "apostrophe ( Watch "apostrophe ( New topic
Author

apostrophe ( ' ) issue in SQL of Hibernate.

Awais Bajwa
Ranch Hand

Joined: Jan 16, 2001
Posts: 191
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

Joined: Sep 16, 2005
Posts: 1780
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.


There is no emoticon for what I am feeling!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: apostrophe ( ' ) issue in SQL of Hibernate.