For beginners: Practice with Spring Boot and MyBatis! SQL Injection Risks and Prevention

Security measures are unavoidable when developing web applications. Among them, "SQL injection" is known as a serious threat to databases.

In this article, explain in an easy-to-understand way how SQL injection works and demonstrate how attacks are actually carried out. Furthermore, a safe implementation using Spring Boot and MyBatis, with code! This is a must-read for web developers, from beginners to intermediate players.

What is SQL injection? Experience the dangers of attacks through videos!

SQL injection is an attack that uses vulnerabilities in an application to launch fraudulent operations on a database.
Even if you find it "it looks a little difficult...", you'll be able to get a rough idea of what kind of attack it is by watching the short video below!

📺 Watch the demo on YouTube : You can watch it from this link

The video explains how SQL injection works with simple examples.
Please take a look and proceed to the next section to learn more!

What is SQL injection?

an attack technique in which an attacker injects malicious SQL code when an application interacts with a database causing an attacker to perform illicit operations.

How SQL injection works in concrete examples

The login function explains how it works. For example, for the following parameters, SQL is converted like this:

• Username: admin
• Password: password

SELECT COUNT(*) > 0 FROM users WHERE username = 'admin' AND password = 'password';

This is normal behavior, but what happens if an attacker inputs something like this:

• Username: ' OR '1'='1' --
• Password: Any value

This can be rewritten as follows:

SELECT COUNT(*) > 0 FROM users WHERE username = '' OR '1'='1' -- AND password = 'any value';

Result : -- AND password = 'any value' is commented out by -- OR '1'='1 is always true, so all users are considered authenticated and unauthorized logins are possible.

How to deal with SQL injection

SQL injection is a serious threat to application security. However, taking appropriate measures can effectively mitigate this risk.

This time, we will explain how to actually take measures using Spring Boot and MyBatis Learn how to build secure applications by leveraging these tools.

1. Use a parameterized query with placeholders

Summary : Securely bind parameters using placeholders (#{}) rather than embedding user input directly into SQL statements.
Reason : Placeholders automatically escape input values and prevent SQL injection.

Below are the codes that use placeholders and codes that are not used.
The differences are the parts "'${username}'→#{username}'" and "'${password}'→#{password}'".

In the case of Mybatis, you can safely bind parameters by setting it to
#{} If you want to use parameters as is in SQL, make sure to use #{}

Vulnerable

@Select("SELECT COUNT(*) > 0 FROM users WHERE username = '${username}' AND password = '${password}'") boolean vulnerableAuthenticate(@Param("username") String username, @Param("password") String password);

safety

@Select("SELECT COUNT(*) > 0 FROM users WHERE username = #{username} AND password = #{password}") boolean secureAuthenticate(@Param("username") String username, @Param("password") String password);

2. Verification and sanitization of user input

Summary : Restricts the types and formats that users can enter, preventing illegal input.
Reason : Removing dangerous strings and SQL keywords can significantly reduce the success rate of your attack.

restrict the character type and format of a parameter, or remove or escape specific characters (e.g. ' , -- , ; etc.) to avoid SQL injection.

if (!username.matches("^[a-zA-Z0-9]+$")) { throw new IllegalArgumentException("Invalid input"); }

Basically, it is better to consider "1. Use a parameterized query with placeholders," but if it is impossible, this solution can significantly reduce the success rate of the attack.

3. Avoid building dynamic queries

Summary : Uses parameterized queries rather than dynamically generating SQL statements with string operations.
Reason : Building SQL with string manipulations makes SQL injection more likely.

This is a little different from the countermeasures, but I believe that it is best to understand
that using string manipulation (+ operators or String.format) when assembling dynamic SQL will result in a risk of SQL injection.

Mybatis is designed by default to avoid using string operations to construct queries directly, so this is no problem, but be careful if you are using string operations when assembling dynamic SQL.

In past cases, I have seen sources that construct a WHERE statement using string manipulation and set it with ${}.
In this case, of course, the possibility of SQL injection increases.

public String buildWhereClause(String username, String email) { String whereClause = "WHERE 1=1"; if (username != null) { whereClause += " AND username = '" + username + "'"; } if (email != null) { whereClause += " AND email = '" + email + "'"; } return whereClause; } @Select("SELECT * FROM users ${whereClause}") List<User> findUsers(@Param("whereClause") String whereClause);

In the case of Mybatis,<if> You can generate dynamic queries using this option, so make sure to use this.

<select id="findUsers" resultType="User">SELECT * FROM users WHERE 1=1<if test="username != null"> AND username = #{username}</if><if test="email != null"> AND email = #{email}</if></select>

4. Other

There are several other ways to reduce SQL injection, but it is important to understand that it is not perfect and is merely a mitigation.

• Set minimum database permissions
• Using stored procedures
• Deploying Web Application Firewall (WAF)
• etc

Example implementation using Spring Boot + MyBatis

As mentioned on YouTube, we have included an example implementation that allows you to actually demonstrate SQL injection.
The entire source code is posted on GitHub, so please check it if you need it.

💾 GitHub Repository : Check the source code at this link

GitHub

security-demo/spring-app/security-demo/src/main/java/com/youtube/security/app/security_demo at 12144… Contribute to aazutaku/security-demo development by creating an account on GitHub.

Controller

package com.youtube.security.app.security_demo.controller; import com.youtube.security.app.security_demo.repository.SqlInjectionMapper; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.RestController; /** * This controller shows how SQL injection vulnerabilities arise and provides a secure implementation using * MyBatis. * * SQL injection is a type of attack that makes * arbitrary SQL statements possible on a database by injecting malicious SQL code into a query. * * Safe implementation example: * - Use a parameterized query with placeholders * -> Prevents SQL injection by using placeholders (#{}) instead of embedding user input directly into the query. * - Verify and sanitize user input * -> Increase safety by limiting the type of character that the user can enter, and removing and escaping dangerous characters and symbols. * - Avoid dynamic SQL generation with string operations * -> Using string operations (+ operators and String.format) when assembling dynamic SQL increases the risk of SQL injection. * -> It is important to assemble queries in a safe way, not dynamically generating them with string manipulations. * -> Mybatis is designed by default to avoid the way to construct queries directly using string manipulation. * * * This class should be used for educational purposes only. */ @RestController public class SqlInjectionController { private final SqlInjectionMapper sqlInjectionMapper; public SqlInjectionController(SqlInjectionMapper sqlInjectionMapper) { this.sqlInjectionMapper = sqlInjectionMapper; } /** * Example of a vulnerable query using MyBatis. * * @param username Username * @param password Password * @return Login Result (Success or Fail) */ @PostMapping("/login/vulnerable") public String vulnerableLogin(@RequestParam String username, @RequestParam String password) { // Call MyBatis Vulnerable Query boolean isAuthenticated = sqlInjectionMapper.vulnerableAuthenticate(username, password); return isAuthenticated ? "Success Login (Vulnerable Implementation)" : "Failed Login (Vulnerable Implementation)"; } /** * Run parameterized queries using MyBatis. * * @param username Username * @param password Password * @return Login result (success or failure) */ @PostMapping("/login/secure") public String secureLogin(@RequestParam String username, @RequestParam String password) { // Call MyBatis secure query boolean isAuthenticated = sqlInjectionMapper.secureAuthenticate(username, password); return isAuthenticated ? "Successful login (secure implementation)" : "Failed login (secure implementation)"; } }

Mapper

package com.youtube.security.app.security_demo.repository; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; import org.apache.ibatis.annotations.Select; @Mapper public interface SqlInjectionMapper { /** * Method that executes vulnerable SQL queries. * @param username * @param password Password * @return True if authentication is successful, false if failure */ @Select("SELECT COUNT(*) > 0 FROM users WHERE username = '${username}' AND password = '${password}'") boolean vulnerableAuthenticate(@Param("username") String username, @Param("password") String password); /** * Method for executing a secure SQL query. * @param username Username * @param password Password * @return True if authentication is successful, false if failure */ @Select("SELECT COUNT(*) > 0 FROM users WHERE username = #{username} AND password = #{password}") boolean secureAuthenticate(@Param("username") String username, @Param("password") String password); }

Practice of SQL injection

We have tools to help you practice SQL injection.
It is available on GitHub, so please make use of it. (Even if you don't use it, you can just send the same request and there's no problem.)

💾 GitHub Repository : Check the source code at this link

GitHub

security-demo/tools/sql_injection.py at 12144e4a08d5bda2ea570210363e134ef50be019 · aazutaku/security… Contribute to aazutaku/security-demo development by creating an account on GitHub.

The following YouTube video demonstrates a SQL injection attack. This content allows you to experience specific examples of attacks in person, so please take a look.

📺 Watch the demo on YouTube : You can watch it from this link

The video demonstrates how SQL injection attacks exploit application vulnerabilities. It's a simple demonstration, but it should help you understand the outline of the attack.