-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 10 posts ] 
  Previous topic | Next topic 
Author Message
sushi.at
 Post subject: HSQLDB: Single quotes in strings don't get escaped
PostPosted: Wed Nov 05, 2008 8:13 pm 
Newbie

Joined: Wed Nov 05, 2008 7:59 pm
Posts: 8
Hi everybody,
When persisting a mapped object that has a String containing a single quote ' into the HSQLDB, the quote does not get escaped. The insert statement therefore fails...

I'm using the org.hibernate.dialect.HSQLDialect in my hibernate.cfg.xml, which according to it's javadoc should work with HSQL 1.8; also using the standard JDBC driver which comes with HSQLDB org.hsqldb.jdbcDriver.

For better logging, I use the JDBC "redirector" P6Spy, so I thought the problem comes from there, but after switching back to the direct "un-redirected" JDBC driver, the problem remained.

According the all the forum posts I found in the forum and the documentation, the JDBC driver should take care of escaping. Is there an updated version of the JDBC driver, or any other workaround?

And just if somebody is wondering: YES I'm using named parameters, since I just use session.save(myObject) ...

Any help with this problem, would be highly appreciated since I don't want to inspect all my objects if their strings contain single quotes...

Markus

Hibernate version: Hibernate 3.3.1.GA

Mapping documents: none, using annotations

Code between sessionFactory.openSession() and session.close():
Transaction tx=session.openSession();
session.save(myObject);
tx.commit();

Full stack trace of any exception that occurs:
[2008-11-06 00:53:31,447] WARN (JDBCExceptionReporter.java:100) - SQL Error: 0, SQLState: null
[2008-11-06 00:53:31,448] ERROR (JDBCExceptionReporter.java:101) - failed batch
[2008-11-06 00:53:31,449] ERROR (AbstractFlushingEventListener.java:324) - Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:266)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1027)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:365)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137)
at tk.korbel.thetvdb.api.LocalTvDbManager.performUpdate(LocalTvDbManager.java:192)
at tk.korbel.tvshoworganizer.Api_Test.main(Api_Test.java:21)
Caused by: java.sql.BatchUpdateException: failed batch
at org.hsqldb.jdbc.jdbcStatement.executeBatch(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.executeBatch(Unknown Source)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
... 9 more
[2008-11-06 00:53:31,452] ERROR (Api_Test.java:23) - Error during API testing...
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:266)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1027)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:365)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137)
at tk.korbel.thetvdb.api.LocalTvDbManager.performUpdate(LocalTvDbManager.java:192)
at tk.korbel.tvshoworganizer.Api_Test.main(Api_Test.java:21)
Caused by: java.sql.BatchUpdateException: failed batch
at org.hsqldb.jdbc.jdbcStatement.executeBatch(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.executeBatch(Unknown Source)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
... 9 more

Name and version of the database you are using: HSQLDB 1.8.0.10

The generated SQL (show_sql=true):
Hibernate: insert into series (series_actors, series_added, series_added_by, series_airs_dayofweek, series_airs_time, series_banner, series_content_rating, series_fanart, series_first_aired, series_genre, series_imdb_id, language_abbreviation, series_last_updated, series_name, series_network, series_overview, series_poster, series_rating, series_runtime, series_status, series_zap2it_id, series_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Debug level Hibernate log excerpt: INFO
Top
 Profile  
 
sushi.at
 Post subject: "Real" SQL statement generated
PostPosted: Wed Nov 05, 2008 8:17 pm 
Newbie

Joined: Wed Nov 05, 2008 7:59 pm
Posts: 8
After switching P6Spy back on I was able to copy the actual SQL statement:

The problem resides in the first value, which contains the actor Terry O'Quinn.....

insert into series (series_actors, series_added, series_added_by, series_airs_dayofweek, series_airs_time, series_banner, series_content_rating, series_fanart, series_first_aired, series_genre, series_imdb_id, language_abbreviation, series_last_updated, series_name, series_network, series_overview, series_poster, series_rating, series_runtime, series_status, series_zap2it_id, series_id) values ('|Matthew Fox|Evangeline Lilly|Terry O'Quinn|Elizabeth Mitchell|Kiele Sanchez|Rodrigo Santoro|Cynthia Watros|Adewale Akinnuoye-Agbaje|Henry Ian Cusick|Michael Emerson|Michelle Rodriguez|Naveen Andrews|Dominic Monaghan|Emilie de Ravin|Harold Perrineau Jr.|Ian Somerhalder|Daniel Dae Kim|Yunjin Kim|Josh Holloway|Maggie Grace|Jorge Garcia|Malcolm David Kelley|Jeremy Davies|Ken Leung|Rebecca Mader|', '', '', 'Thursday', '10:00 PM', 'graphical/73739-g4.jpg', 'TV-14', 'fanart/original/73739-15.jpg', '2004-09-22', '|Action and Adventure|Drama|Science-Fiction|', 'tt0411008', '', 1225907261, 'Lost', 'ABC', 'After their plane, Oceanic Air flight 815, tore apart whilst thousands of miles off course, the survivors find themselves on a mysterious deserted island where they soon find out they are not alone.', 'posters/73739-2.jpg', 8.7, 45, 'Continuing', 'SH672362', 24313)
Top
 Profile  
 
sushi.at
 Post subject:
PostPosted: Thu Nov 06, 2008 4:31 pm 
Newbie

Joined: Wed Nov 05, 2008 7:59 pm
Posts: 8
No ideas how to fix this anyone? Could some please at least tell me if that is a problem with hibernate/the dialect of the database. Or if I should take my problem to the JDBC driver provider....
Top
 Profile  
 
evilsephiroth
 Post subject:
PostPosted: Fri Nov 07, 2008 4:54 am 
Newbie

Joined: Fri Nov 07, 2008 4:51 am
Posts: 2
i have the same problem.

I use SQLSERVER 2005 and i 've got problems withe escaping single quotes into string.

Use Case:

Username or password with a single quotes into the string (I.E. D'astro).

I've tried replacing single quotes with exact ascii code(') or also escaping with '\'.

No solutions yet...
Top
 Profile  
 
sushi.at
 Post subject:
PostPosted: Fri Nov 07, 2008 6:57 am 
Newbie

Joined: Wed Nov 05, 2008 7:59 pm
Posts: 8
i know how to escape them just replace ' with '' .... At least thats the way it is done in HSQL. But I don't want to do it manually for all the strings in my objects, since I import them from another source and the amount of data can get quite large... But thx for your reply
Top
 Profile  
 
evilsephiroth
 Post subject:
PostPosted: Fri Nov 07, 2008 7:25 am 
Newbie

Joined: Fri Nov 07, 2008 4:51 am
Posts: 2
sushi.at wrote:
i know how to escape them just replace ' with '' .... At least thats the way it is done in HSQL. But I don't want to do it manually for all the strings in my objects, since I import them from another source and the amount of data can get quite large... But thx for your reply



Yes in SQLServer is the same but i don't want to use createSqlQuery because i want to mantain my code DB indipendent.

With createquery("") i'm searching a way to make hibernate escape this character.
Top
 Profile  
 
sushi.at
 Post subject:
PostPosted: Sat Nov 08, 2008 6:10 pm 
Newbie

Joined: Wed Nov 05, 2008 7:59 pm
Posts: 8
I think (but thats just my first impression without investigating it further) that the Hibernate dialect class is the way to solve the problem. I will try to find the source the code of the HSQLDB dialect class and check if I can insert the functionality there....
Top
 Profile  
 
sushi.at
 Post subject:
PostPosted: Sat Nov 08, 2008 6:52 pm 
Newbie

Joined: Wed Nov 05, 2008 7:59 pm
Posts: 8
Ok I checked the source code of both org.hibernate.dialect.Dialect and org.hibernate.dialect.HSQLDialect, and it's a dead end. Seems like the Hibernate dialect does not contain any information about how to escape special characters in strings. The only "quote-related" code I could find in there was related to quoting SQL identifiers.

I'm going to take a different approach now and create a class that uses java.lang.reflection tools for finding strings in classes and do my escaping there. It's gonna create a little extra overhead but at least for the moment its the only possibility I can think about right now. Maybe a better solution will present itself in the future.....

I going to post the code of that class here, as soon as I got it finished...
Top
 Profile  
 
sushi.at
 Post subject:
PostPosted: Sat Nov 08, 2008 7:58 pm 
Newbie

Joined: Wed Nov 05, 2008 7:59 pm
Posts: 8
Ok here you got the source code for the reflecting quote escaper, just replace the logging calls with System.out or any other logging framework that you use in your project....

Code:
public class ReflectingQuoteEscaper {

   public static void escapeQuotes(Object object) {
      Logging.log.info("Escaping quotes for object: " + object.toString());
      for (Field field : object.getClass().getDeclaredFields()) {
         if (field.getType() == String.class) {
            if ((field.getModifiers() & Field.PUBLIC) != 0) {
               Logging.log.info("Escaping quotes for public string field: " + field.toString());
               try {
                  String value = (String) field.get(object);
                  value = value.replaceAll("'", "''");
                  field.set(object, value);
               } catch (IllegalArgumentException e) {
                  Logging.log.error("Error escaping quotes for string field: " + field.toString(), e);
               } catch (IllegalAccessException e) {
                  Logging.log.error("Error escaping quotes for string field: " + field.toString(), e);
               }
            } else {
               // Check for getter/setter methods
               String fieldName = field.getName();
               String capitalFieldName = fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
               String getterName = "get" + capitalFieldName;
               String setterName = "set" + capitalFieldName;
               Method getter = null, setter = null;
               for (Method method : object.getClass().getDeclaredMethods()) {
                  if (method.getName().equals(getterName))
                     getter = method;
                  if (method.getName().equals(setterName))
                     setter = method;
               }
               if (getter != null && setter != null) {
                  Logging.log.info("Escaping quotes for string field using getter="
                        + getter.getName()
                        + " and setter="
                        + setter.getName());
                  try {
                     String value = (String) getter.invoke(object, new Object[0]);
                     value = value.replaceAll("'", "''");
                     setter.invoke(object, new Object[] { value });
                  } catch (IllegalArgumentException e) {
                     Logging.log.error("Error escaping quotes for string field: " + field.toString(), e);
                  } catch (IllegalAccessException e) {
                     Logging.log.error("Error escaping quotes for string field: " + field.toString(), e);
                  } catch (InvocationTargetException e) {
                     Logging.log.error("Error escaping quotes for string field: " + field.toString(), e);
                  }
               }
            }
         }
      }
      Logging.log.info("Finished escaping quotes for object: " + object.toString());
   }
}
Top
 Profile  
 
Binod Suman
 Post subject:
PostPosted: Fri Dec 12, 2008 12:33 pm 
Newbie

Joined: Fri Dec 12, 2008 12:27 pm
Posts: 1
You can more information on HSQLDB from below link. You would be also come that how to setup the HSQLDB for your project.

http://binodsuman.blogspot.com/
Thanks,

Binod Suman
http://binodsuman.blogspot.com/

_________________
Binod Suman
http://binodsuman.blogspot.com/

Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 10 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.