SQL Injection and how to prevent it? Hibernet/JPA/SQL

SQL Injection

1.      Prepared Statement and Callable Statement:

A PreparedStatement represents a precompiled SQL statement that can be executed multiple times without having to recompile for every execution.

Secure Code:

PreparedStatement stmt = connection.prepareStatement("SELECT * FROM users WHERE userid=? AND password=?");

stmt.setString(1, userid);

stmt.setString(2, password);

ResultSet rs = stmt.executeQuery();

Why this code is secure?

Ans: This code is not vulnerable to SQL Injection because it correctly uses parameterized queries. By utilizing Java's PreparedStatement class, bind variables (i.e. the question marks) and the corresponding setString methods, SQL Injection can be easily prevented.

Vulnerable Code 1:

// Example #1

String query = "SELECT * FROM users WHERE userid ='"+ userid + "'" + " AND password='" + password + "'";

Statement stmt = connection.createStatement();

ResultSet rs = stmt.executeQuery(query);

Why this code is vulnerable?

Ans: This code is vulnerable to SQL Injection because it uses dynamic queries to concatenate malicious data to the query itself. Notice that it uses the Statement class instead of the PreparedStatement class.

Vulnerable Code 2:

// Example #2

String query = "SELECT * FROM users WHERE userid ='"+ userid + "'" + " AND password='" + password + "'";

PreparedStatement stmt = connection.prepareStatement(query);

ResultSet rs = stmt.executeQuery();

Why this code is vulnerable?

Ans: This code is also vulnerable to SQL Injection. Even though it uses the PreparedStatement class it is still creating the query dynamically via string concatenation.

http://software-security.sans.org/developer-how-to/fix-sql-injection-in-java-using-prepared-callable-statement

2.      Hibernate:

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.

Secure Usage:

Code-1:

/* 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();

Code-2:

/* 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();

Code-3:

/* 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();

Code-4:

/* 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.

Code-5:

/* Native-SQL */

Query sqlQuery = session.createSQLQuery("Select * from Books where author = ?");

List results = sqlQuery.setString(0, "Charles Dickens").list();

Why above 5 codes are secure ?

Ans:

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.

Vulnerable 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();

Why this code is vulnerable ?

Ans:

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.

http://software-security.sans.org/developer-how-to/fix-sql-injection-in-java-hibernate

3.      Java Persistence API(JPA):

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.

Secure usage:

Code-1:

/* 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();

Code-2:

/* 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();

Code-3:

/* 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();

Code-4:

/* Native SQL */

Query sqlQuery = entityManager.createNativeQuery("Select * from Books where author = ?", Book.class);

List results = sqlQuery.setParameter(1, "Charles Dickens").getResultList();

Why above 4 codes are secure ?

Ans:

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.

Vulnerable 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();

Why this code is vulnerable ?

Ans:

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.

http://software-security.sans.org/developer-how-to/fix-sql-injection-in-java-persistence-api-jpa

You can use Prepared Statements wrong like this:

Code:

String strUserName = request.getParameter("Txt_UserName");
 PreparedStatement prepStmt = con.prepareStatement("SELECT * FROM user WHERE userId = '+strUserName+'");

So be sure to use Prepared Statements WITH ALL Bind Variables.

Code:

String selectStatement = "SELECT * FROM User WHERE userId = ? ";
PreparedStatement prepStmt = con.prepareStatement(selectStatement);
prepStmt.setString(1, userId);
ResultSet rs = prepStmt.executeQuery();