Code Review Detect SQLi in Java
Why do we need code review 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.
There are a wide variety of SQLi techniques, attacks, vulnerabilities which can occur in different situations. Some of them include:
- Obtaining hidden data.
- Unsettle the application's logic.
- Blind SQLi.
- Examining the database.
- Union attacks
SQLi vulnerabilities can in principle occur at any location within a query and in different query types. Most of us probably found SQLi within a WHERE clause of a SELECT query.
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 query. 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 query 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)
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 query 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
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
Positional parameter in JPQL
Named parameter in JPQL
Named query in JPQL - Query named “PcComponents” being “Select component from PcComponents component where component.itemId = :itemId”
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 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 query 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.
2. Secure usage of Hibernate
Positional parameter in HQL
Named parameter in HQL
Named parameter list in HQL
JavaBean in HQL
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
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.
Even though it uses the PreparedStatement class it is still creating the query dynamically via string concatenation.
2.Secure usage of PrepareStatement
For preveting 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.
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.