Introduction
As hackers find increasingly creative ways to attack applications, organizations must try to stay one step ahead in protecting themselves, even from the most common types of attacks and across a variety of frameworks.
Let’s start this post with a few definitions.
SQL Injection
SQL injection is a common way that hackers and users with malicious intentions attempt to hack applications. In an SQL injection, they “inject” values into a database query in order to gain visibility into the database’s structure and eventually gain access to personal data stored in the database.
Spring Boot
Spring Boot is part of the Spring Framework. The Spring Framework is a well-known framework for Java development, and Spring Boot is the framework’s solution for convention over configuration-based software development. This post is about preventing SQL injections in Spring Boot. Let’s now elaborate on the above.
What Is an SQL Injection?
As explained in Stackhawk’s SQL injection post, an SQL injection is a popular attack vector on the client’s input into a software system. For instance, let’s look at a website that contains a form for the user to fill. An attacker can use the form’s content maliciously to fetch data from the database. They do this by entering specific data with control characters as input to manipulate the database to execute arbitrary code. As stated on OWASP‘s website, “A successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file present on the DBMS file system, and in some cases issue commands to the operating system.”
Why Is It Important to Mitigate SQL Injection?
There are many factors that make an SQL injection something you should take the time to mitigate. Here are a few:
- Client data is the most prevalently used type of data for generating database queries.
- Hackers can use this attack against any SQL database.
- A low level of expertise is needed to execute this type of attack.
What Is Spring Boot?
The Spring Framework is a very popular Java framework for developing enterprise and web applications. Originally created in 2005 by Pivotal, it is still going strong today and is the most popular Java framework on the market. Over the years, different modules were added to the core Spring container: authentication and authorization, data access, transaction management, etc. One of the most popular modules is Spring Boot. It provides preconfigured defaults and modules for the core Spring Framework. Thus abstracting the complexity of manual configuration away and allowing rapid development of applications. This “batteries included”/”ready to develop”/”no setup” approach is what made this module so popular. As of today, almost all new projects developed on Spring will be based on Spring Boot (except possibly those for large enterprises or those that need to have manual customization of the base containers).
Java SQL Injection
As Spring Boot is written in Java, we need to discuss SQL injections in Java first. In general, in most cases, preventing a Java SQL injection is the same as preventing a Spring Boot SQL injection. As stated above, an SQL injection is basically an attack that incorporates special control characters into a valid input for malicious intents. We’ll give a few examples of such inputs here and three ways to process them:
- The wrong way to process (which allows an attacker to execute an SQL injection)
- The right way to process them in Java (preventing any SQL injection attempts)
- And the right way with Spring
Example 1
The Wrong Way
Let’s assume we want to fetch a user’s data from the users table.
public List<User>
getUserByUserId(String userId)
throws SQLException {
String sql = "select "
+ "first_name,last_name,username "
+ "from users where userid = '"
+ userId
+ "'";
Connection c = dataSource.getConnection();
ResultSet rs = c.createStatement().executeQuery(sql);
}If we want to fetch data for a user with id=20, the resulting SQL query will look like this:
select first_name,last_name,username from users where userId = 20 ;This is a valid SQL query that will produce the expected output. However, if an attacker is able to provide the input of 20 or 1=1 and we run the code as is, the resulting SQL query will become as follows:
select first_name,last_name,username from users where userId = 20 or '1'='1';
Which will result in returning all the users in the database to the client. This is a Boolean-based SQL injection. If we use plain JDBC as in the example above, the proper way to construct the query is with prepared statements. With prepared statements, the SQL engine caches the final query before executing it and treats any data we insert into it as plain input omitting any control characters. Note that using JPA or other ORMs without prepared statements with bound parameters won’t protect you from an SQL injection.
The Right Way
public List<User> getUserByUserId(String userId)
throws SQLException {
String sql = "select "
+ "first_name,last_name,username "
+ "from users where
+ userId = ?";
Connection c = dataSource.getConnection();
PreparedStatement p = c.prepareStatement(sql);
p.setString(20, userId);
ResultSet rs = p.executeQuery(sql));
}The Spring Boot Way
Another right way to execute this SQL query in Spring Boot is to use the NamedParameterJdbcTemplate class. This method has an additional benefit of providing more clarity by replacing the question marks in the query with meaningful names:
Map<String, Object> params = new HashMap<>();
integer userId = 20;
String sql = "select "
+ "first_name,last_name,username "
+ "from users where
+ userId = :userId";
params.put("userId", userId);
template.update(sql,params);Example 2
Another type of SQL injection is the Union SQL injection:
https://someurl.com/user?name=="Bilbo' union all select 1, concat(username,permission) from permissions where '1'='1"
If we naively use GET parameters input in an SQL query, the attacker will be able to fetch data from the permissions table. The way to mitigate such a scenario is by using prepared statements with bound parameters as described above.
Stored Procedures
A different approach to sanitizing the input and preventing Spring Boot SQL injections is to use stored procedures instead of plain SQL query. The database engine sanitizes any parameter passed to a stored procedure. Here’s an example of executing the query in the first example via a stored procedure:
String userId = 20
try {
CallableStatement cs = connection.prepareCall("{call sp_getUserByUserId(?)}");
cs.setInt(userId, userid);
ResultSet results = cs.executeQuery();
// … result set handling
} catch (SQLException se) {
// … logging and error handling
}
Worst-Case Scenario
SQL injections usually occur due to programming errors. Since we can’t always review each DB query, an SQL injection can still come through. In order to minimize the devastating consequences that such a scenario can bring, we can invest time in properly designing our database access. Below are some best practices you can employ to keep such a happening under control.
Best Practices
- Follow the least privileges principle. Execute SQL queries with as minimal privileges as possible. For instance, for a READ operation, the database user doesn’t need DROP or TRUNCATE privileges.
- Rotate DB credentials. As with your other passwords, routinely change your database password. Thus, even if it becomes public, it might be irrelevant and out of date once an attacker tries to use it.
- Use a Web Application Firewall. Those tools can autodetect patterns that attackers frequently use as part of their SQL injection attacks. It can notify you once an attack takes place and potentially prevent it from happening at all.
- Employ IP allowlisting. Allow only a specific set of IP addresses to access your database. Block access for all other addresses.
- Avoid using common and easy-to-guess table names and column names in your schema as much as possible. For instance, don’t use users, permissions, customers, etc.
- Proactively use a tool like sqlmap to find and fix SQL injection vulnerabilities in your code.
Conclusion
SQL injections are a common and high-risk attack vector. However, as we’ve seen above, it’s fairly easy to mitigate this risk with prepared statements, bound parameters, and stored procedures. As long as you’re following the best practices, hackers won’t be able to execute SQL injections in your application.