dineshonjava

SQL Injection and Parameter Binding in Hibernate

In this tutorial of SQL Injection and Parameter Binding in Hibernate we will discuss about SQL inject and its demerits and also describe Parameter binding, it means is way to bind parameter with SQL to use in the hibernate for particular criteria.

SQL Injection:

Injecting the value to the SQL statement. SQL injection refers to the act of someone inserting a MySQL statement to be run on your database without your knowledge. Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a MySQL statement that you will unknowingly run on your database.

Normal:    " SELECT * FROM student WHERE studentName= 'sweety'"
Injection:  "SELECT * FROM student WHERE studentName= '' +studentName

It is a very common misconception that ORM solutions, like hibernate, are SQL Injection proof. Hibernate allows the use of "native SQL" and defines a proprietary query language, named, HQL the former is prone to SQL Injection and the later is prone to HQL injection.


SQL Injection
@ImageSource-Slideshare.net

Parameter Binding:

A bind variable is a named placeholder (preceded by a colon) that is embedded in the query string in place of a literal. The actual value is substituted at runtime using the setParameter() method. 
Without parameter binding, you have to concatenate the parameter String like this (bad code) :

String hql = "from Student student where student.studentName = '" + studentName+ "'";
Query query = session.createQuery(hql);
List result = query.list();

Pass an unchecked value from user input to the database will raise security concern, because it can easy get hack by SQL injection. You have to avoid the above bad code and using parameter binding instead.

Hibernate parameter binding

There are two ways to parameter binding :
  1. Named parameters binding
  2. Positional parameters binding.
 1. Named Parameters Binding: 
This is the most common and user friendly way. It use colon followed by a parameter name (:example) to define a named parameter. See examples…
Example 1 – setParameter
The setParameter is smart enough to discover the parameter data type for you.
String hql = "from Student student where student.rollNumber= :rollNumber";
Query query = session.createQuery(hql);
query.setParameter("rollNumber", "3");
List result = query.list();

Example 2 – setString
You can use setString to tell Hibernate this parameter date type is String.
String hql = "from Student student where student.studentName= :studentName";
Query query = session.createQuery(hql);
query.setString("studentName", "Sweety Rajput");
List result = query.list();
Example 3 – setProperties
This feature is great ! You can pass an object into the parameter binding. Hibernate will automatic check the object’s properties and match with the colon parameter.
Student student= new Student();
student.setCourse("MCA");
String hql = "from Student student where student.course= :course";
Query query = session.createQuery(hql);
query .setProperties(student);
List result = query.list();

2. Positional parameters

It’s use question mark (?) to define a named parameter, and you have to set your parameter according to the position sequence. See example…
String hql = "from Student student where student.course= ? and student.studentName = ?";
Query query = session.createQuery(hql);
query.setString(0, "MCA");
query.setParameter(1, "Dinesh Rajput")
List result = query.list();


In Hibernate parameter binding, i would recommend always go for "Named parameters", as it’s more easy to maintain, and the compiled SQL statement can be reuse (if only bind parameters change) to increase the performance.


In the Next Chapter we will discuss about the Named Query.

            

   <<Previous Chapter 27<<    >>Next Chapter 29>>