How to Fix SQL Injection using Hibernate
Hibernate facilitates the storage and retrieval of Java domain objects via Object/Relational Mapping (ORM). 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 (Hibernate Query Language); the former is prone to SQL Injection and the later is prone to HQL (or ORM) injection.
This article is intended to illustrate how certain syntax offered by hibernate to define SQL & HQL, is better over the other, in terms of defense against SQL and/or HQL injection attacks.
/* Positional parameter in HQL */ Query hqlQuery = session.createQuery("from Orders as orders where orders.id = ?"); List results = hqlQuery.setString(0, "123-ADB-567-QTWYTFDL").list();
/* named parameter in HQL */ Query hqlQuery = session.createQuery("from Employees as emp where emp.incentive > :incentive"); List results = hqlQuery.setLong("incentive", new Long(10000)).list();
/* named parameter list in HQL */ List items = new ArrayList(); items.add("book"); items.add("clock"); items.add("ink"); List results = session.createQuery("from Cart as cart where cart.item in (:itemList)").setParameterList("itemList", items).list();
/* JavaBean in HQL */ Query hqlQuery = session.createQuery("from Books as books where book.name = :name and book.author = :author"); List results = hqlQuery.setProperties(javaBean).list(); //assumes javaBean has getName() & getAuthor() methods.
/* Native-SQL */ Query sqlQuery = session.createSQLQuery("Select * from Books where author = ?"); List results = sqlQuery.setString(0, "Charles Dickens").list();
The above code snippets use parameter binding to set data. The JDBC driver will escape this data appropriately before the query is executed, making sure that data is used just as data.
Assuming data used in the above code snippets is user input, that has not been validated or escaped and it contains malicious database code (payload), the payload will be escaped appropriately by the JDBC driver (since parameterized queries are used), such that it would be used as data and not as code.
List results = session.createQuery("from Orders as orders where orders.id = " + currentOrder.getId()).list(); List results = session.createSQLQuery("Select * from Books where author = " + book.getAuthor()).list();
Assuming orderId and author are user input that have not been validated or escaped, it leaves the above queries vulnerable to SQL and HQL(ORM) injection attacks.