How to How to Fix SQL Injection using the Java Persistence API (JPA)
Java Persistence API (JPA), is an ORM solution that is a part of the Java EE framework. It helps manage relational data in applications that use Java SE and Java EE. It is a common misconception that ORM solutions like JPA (Java Persistence API) are SQL Injection proof. JPA allows the use of native SQL and defines its own query language, named, JPQL (Java Persistence Query Language). The former is prone to traditional SQL injection attacks and the later is prone to JPQL (or ORM) injection attacks.
This article is intended to illustrate how certain syntax offered by JPA to define SQL & HQL, is better over the other, in terms of defense against SQL and/or HQL injection attacks.
/* positional parameter in JPQL */ Query jpqlQuery = entityManager.createQuery("Select order from Orders order where order.id = ?1"); List results = jpqlQuery.setParameter(1, "123-ADB-567-QTWYTFDL").getResultList();
/* named parameter in JPQL */ Query jpqlQuery = entityManager.createQuery("Select emp from Employees emp where emp.incentive > :incentive"); List results = jpqlQuery.setParameter("incentive", new Long(10000)).getResultList();
/* named query in JPQL - Query named "myCart" being "Select c from Cart c where c.itemId = :itemId" */ Query jpqlQuery = entityManager.createNamedQuery("myCart"); List results = jpqlQuery.setParameter("itemId", "item-id-0001").getResultList();
/* Native SQL */ Query sqlQuery = entityManager.createNativeQuery("Select * from Books where author = ?", Book.class); List results = sqlQuery.setParameter(1, "Charles Dickens").getResultList();
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 = entityManager.createQuery("Select order from Orders order where order.id = " + orderId).getResultList(); List results = entityManager.createNativeQuery("Select * from Books where author = " + author).getResultList(); int resultCode = entityManager.createNativeQuery("Delete from Cart where itemId = " + itemId).executeUpdate();
Assuming orderId, author & itemId are user input that have not been validated or escaped as required, it leaves the above queries vulnerable to SQL and JPQL (ORM) injection attacks.