Fighting SQL injection in hibernate may hit down the performance

The problem

A couple of days ago I found a query which hangs when running from the application but goes fine when running from a SQL client.

The problem was in the way I was calling the query. It was:

hibernateSession.createQuery(“from Product p inner join p.attributeValues av where av.value=:value”).setParameter(“value”, someValue)

Nothing wrong with it, isnt it?

DB server background

When you implement a parametrized query the DB (MSSQL server 2008R2 in my case) uses the same query execution plan for all parameter values. Unfortunately the plan may be not efficient. The execution plan may (and I think will) fit the first parameter value. But may not fit the second.

JDBC and Hibernate reality

Theoretically you can avoid execution plan reusing by using regular java.sql.Statement instead of java.sql.PreparedStatement. But this is not possible when you work with Hibernate. Hibernate uses java.sql.PreparedStatement only internally.

The solution:

hibernateSession.createQuery(“from Product p inner join p.attributeValues av where av.value=” + someValue)

Welcome hackers 😉

 

Advertisements

About andrewosipenko

Java dev. Groovy dev. Grails dev. Project man.
This entry was posted in Java, MSSQL and tagged , . Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s