Code Review Detect SQLi in Java

Code Review Detect SQLi in Java
Share this article with your friends!

Why do we need a Code Review Detect SQLi in Java when we can interact with the target and finding if it is vulnerable? After all the HTTP response’s behavior should help us finding if something is not configured properly. Based on the attack, there are cases where we will not can deduce so easily if we are vulnerable or not. Making code review can help us finding what the interactive analysis misses.

Introduction to Code Review Detect SQLi in Java

There are a wide variety of SQLi techniques, attacks, vulnerabilities that can occur in different situations. Some of them include:

  • Obtaining hidden data.
  • Unsettle the application’s logic.
  • Blind SQLi.
  • Examining the database.
  • Union attacks
Code Review Detect SQLi in Java

SQLi vulnerabilities can in principle occur at any location within an interrogation and in different interrogation types. Most of us probably found SQLi within a WHERE clause of a SELECT interrogation.
The most common other locations where a SQLi can occur are:

  • In SELECT statements, within the table or column name.
  • In SELECT statements, within the ORDER BY clause.
  • In INSERT statements, within the inserted values.
  • In UPDATE statements, within the updated values or the WHERE clause.

There are cases where the application takes the user’s input and store it for a future use, instead of processing the user’s input from a HTTP request and adding it into a SQL interrogation. This is usually done by storing the input into a database, no vulnerability arises at the point where the data is stored. The stored SQLi vulnerabilities are hard to be detected through interactive analysis.

Many programmers are thinking that today frameworks can resolve all the SQLi injection vulnerabilities, but this is wrong. Many are using SQL interrogation and connections with the databases in a wrong manner. In what will follow, I will present some examples of how to detect SQLi in Java code and how to correctly write it.

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. JPA allows the use of native SQL and defines its own interrogation language, named, JPQL (Java Persistence Query Language). It is a common misconception that JPA (Java Persistence API) is SQL Injection proof.

1. Vulnerable usage of JPA

  • List sql_result = EntityManager.createNativeQuery(“Select * from PcComponents where component = ” + component).GetResultList()
  • List sql_result = entityManager.createQuery(“Select age from Persons age order where age.id = ” + age.id).getResultList();
  • int sql_result = entityManager.createNativeQuery(“Delete from Persons where CNP = ” + CNP).executeUpdate();

I consider that component, age.id & CNP are user input. As you can see in the above examples, they have not been validated or escaped as required. Therefore, it leaves the above queries vulnerable to SQLi attacks.

2. Secure usage of JPA

Native SQL

  • Query sql_query = entityManager.createNativeQuery(“Select * from PcComponents where component = ?”, PcComponents.class);
    List sql_result = sql_query.setParameter(1, “AMD Ryzen 5000”).getResultList();

Positional parameter in JPQL

  • Query jpql_query = entityManager.createQuery(“Select person from Personss person where person.CNP = ?1”);
    List sql_result = jpql_query.setParameter(1, “1940000332211”).getResultList();

Named parameter in JPQL

  • Query jpql_query = entityManager.createQuery(“Select employee from Employees employee where employee.salary > :salary”);
    List sql_result = jpql_query.setParameter(“salary”, new Long(1000)).getResultList();

Named parameter in JPQL

  • Query jpql_query = entityManager.createQuery(“Select employee from Employees employee where employee.salary > :salary”);
    List sql_result = jpql_query.setParameter(“salary”, new Long(1000)).getResultList();

Named query in JPQL – Query named “PcComponents” being “Select component from PcComponents component where component.itemId = :itemId”

  • Query jpql_query = entityManager.createNamedQuery(“PcComponents”);
    List sql_result = jpql_query.setParameter(“itemId”, “AMD-Ryzen-5000”).getResultList();

If your JPA provider processes all input arguments to handle injection attacks then you should be covered.
Tip for devs: Never use string concatenation in your SQL queries.

Hibernate

Hibernate facilitates the storage and retrieval of Java domain objects via Object/Relational Mapping (ORM).Hibernate allows the use of “native SQL” and defines a proprietary interrogation language, named, HQL (Hibernate Query Language).

1. Vulnerable usage of Hibernate

The story is repeating as you can see in the below examples, the inputs have not been validated or escaped as required. Therefore, it leaves the above queries vulnerable to SQLi attacks.

  • List sql_result = session.createQuery(“Select age from Persons age order where age.id = ” + currentPersons.getId()).list();
  • List sql_result = session.createSQLQuery(“Select * from PcComponents where component = ” + PcComponent.getComponent()).list();

2. Secure usage of Hibernate

The positional parameter in HQL

  • Query hql_query = session.createQuery(“from PcComponents as component where component.id = ?”);
    List sql_result = hql_query.setString(1, “AMD Ryzen 5000”).list();

Named parameter in HQL

  • Query hql_query = session.createQuery(“Select employee from Employees employee where employee.salary > :salary”);
    List sql_result = hql_query.setLong(“salary”, new Long(1000)).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 hql_query = session.createQuery(“from Persons as person where person.name = :person and person.surname = :surname”);
    List sql_result = hql_query.setProperties(javaBean).list(); //assumes javaBean has getName() & getSurname() methods.

Native-SQL

  • Query sql_query = session.createSQLQuery(“Select * from PcComponents where component = ?”);
    List sql_result = sql_query.setString(0, “AMD Ryzen 5000”).list();

PreparedStatement

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

1. Vulnerable usage of PreparedStatement

Example 1:

String query = "SELECT * FROM Persons WHERE CNP ='"+ CNP + "'" + " AND FirstName='" + FirstName + "'";
Statement statement = connection.createStatement();
ResultSet response_query = statement.executeQuery(preparedstatement_query);

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

Example2:

String query = "SELECT * FROM Persons WHERE CNP ='"+ CNP + "'" + " AND FirstName='" + FirstName + "'";
PreparedStatement statement = connection.prepareStatement(query);
ResultSet response_query = statement.executeQuery();

Even though it uses the PreparedStatement class it is still creating the query dynamically via string concatenation.

2.Secure usage of PrepareStatement

For preventing SQLi we have to correctly use parameterized queries. By utilizing Java’s PreparedStatement class, bind variables (the question marks) and the corresponding setString methods, SQL Injection can be easily prevented.

PreparedStatement statement = connection.prepareStatement("SELECT * FROM Persons WHERE CNP=? AND FirstName=?");
statement.setString(1, CNP);
statement.setString(2, FirstName);
ResultSet result = statement.executeQuery();

Conclusion

ORMs spare us from creating hand-coded SQL statements, but they won’t prevent from writing vulnerable code. A good tip for finding if your code is vulnerable to SQLi is to look at how the queries are written or built. Try to understand how the input is used in the background. If it is sanitized, try to understand how the programmers thought to sanitize it and check if you can take advantage over its sanitize functions in case they are custom made.

Thanks for this material to our partner SecurityNest

References: https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html

If you find it interesting, you can read about how Hackers Are Targeting Microsoft Exchange Servers With Ransomware

JOIN OUR NEWSLETTER
Join over 1.000 visitors who are receiving our newsletter and get free eBooks, breaking news, learn how secure your data, your company accounts, database, clients passwords, get the best security advice and more.
We hate spam. Your email address will not be sold or shared with anyone else.

Share this article with your friends!