SQL injection attack prevention in JEE | Complete guide

For those who didn’t see the first part. This blog post is a part of a long series where I explain in detail how to prevent the SQL injection attack in your source code.

This guide is an open series of posts and this is the first post. If you want me to add any technology, please just comment below or contact me, and I will be very happy to explain SQL injection prevention for that technology. This guide will analyze the following technologies:

• PHP
  • Laravel
  • Symfony
  • CodeIgniter
  • CakePHP
  • FuelPHP
  • Zend
• JEE
  • JDBC
  • JPA
• ASP.NET
  • ADO.NET
  • ADO.NET Dopper
  • NHibernate

The first post has focused on the PHP language and all its frameworks, Now we will see how to prevent such vulnerability in a JAVA EE application.

For those who do not know what is SQL injection attack, let me do a small introduction to explain this to them. SQL injection attacks happen when a bad user tries to inject a malicious SQL request into a legitimate request. The impact of SQL injection attack differs from a situation to another depending on multiple elements related to the app environment, and it can go from as “simple” as information leak to a full server control. I will not go deeper into the way an attacker could exploit this vulnerability or how we can discover it in a blackbox test as this is not the objective of this post. What we will see is how to discover it in source code and how to fix it.

In JAVA things are a little bit different. Actually, SQL requests in java are not managed by the frameworks. In fact, they are managed by some APIs like JDBC, modern JPA, or Hibernate. So, to prevent the SQL injection attack in the JAVA web app, you will need to fix things at this level. In this article, we will analyze examples of vulnerable code in each API and I will explain in details how to prevent this vulnerability.

Enough talking now :p …

Using Java DataBase Connectivity (JDBC)

Let’s start with one of the most popular and old API, which is the JDBC. Here is an example of a vulnerable code:

con = DBConnection.getConnection();
stmt = con.createStatement();
String query = "select name, country, password from Users where email = '"+email+"' and password='"+pwd+"'";
rs = stmt.executeQuery(query);

Now let’s analyze this source code…

In this source code we are using the JDBC API. However, If you take a look at the query you will see that the developer of the app is injecting the user data directly in the legitimate SQL request. This is a dangerous practice, and it is vulnerable to SQL injection.

To fix this vulnerability you will need to use the prepared statements, already implemented in the JDBC. Here is an example of how to correctly use this API:

String query = "select name, country, password from Users where email = ? and password = ?";
con = DBConnection.getConnection();
ps = con.prepareStatement(query);
ps.setString(1, email);
ps.setString(2, pwd);
rs = ps.executeQuery();

SQL injection attack prevention using Java Persistence API (JPA)

For those who don’t know about it, JPA is a Jakarta EE application programming interface that describes the management of relational data Java apps. Working with this model is supposed to be safe, as the sql logic in the ORM technologies is a little bit different. However, in some cases, the JPA gives the developer the possibility to create custom requests with complex. Here is a simple example of a vulnerable code using JPA:

public List<AccountDTO> findArticleByArticleId(String ArticleId) {
String jql = "from Articles where articleId = '" + articleId + "'";       
    TypedQuery<Account> q = em.createQuery(jql, Account.class);       
    return q.getResultList()
      .stream()
      .map(this::toAccountDTO)
      .collect(Collectors.toList());       
}

The fact that the articleId variable is coming directly from the user input, make the code vulnerable to SQL injection even if we are using the JPA, as the user inputs are not filtered before being injected in the request.

Now to fix this code we will need to filter the data before using them or using the prepared statement even if we are using an ORM system. Here is an example of such use:

 public List<AccountDTO> findArticleByArticleId(String ArticleId) {
String jql = "from Articles where articleId = :articleId ";
TypedQuery<Account> q = em.createQuery(jql, Account.class).setParameter("articleId", articleId); 
return q.getResultList()
.stream()
.map(this::toAccountDTO)
.collect(Collectors.toList()); 
}

The parameters are set separately using the setParameter() method.

Note:

The prepared statements do not work in the case of a table name or column name, and it would generate some errors. Therefore, you will need to avoid giving the user the possibility to manipulate these items. However, if this manipulation is necessary, try to filter the user data before injecting them, or use whitelists to check the input.

I hope these examples help you create a much more secure web application and I hope that you contact me if there is anything you want me to add to make this article as complete as possible.