ZZeo.
ZH
← Back to all posts
Code Auditing6 min read

Self-Studying Java Code Auditing: SQL Injection

This article documents a self-directed approach to learning Java code auditing, focusing on SQL injection principles, vulnerable SQL construction, incorrect use of prepared statements in MyBatis and Hibernate, remediation methods, and practical auditing techniques.

Published Jul 24, 2020
#Java#Code Auditing
Read in Chinese→

0x00 Background

This article primarily documents my approach to learning Java code auditing. Since I am studying alone, my approach can sometimes feel like working in isolation and relying on Baidu to learn. Nevertheless, I want to record and share the process so that I can summarize and reflect on it later. If I ultimately manage to learn something useful, I will reorganize my thoughts and provide other self-learners with a better approach. This is why I created the following series of articles on self-studying Java code auditing.

0x01 Vulnerability Principles

Although this is basic knowledge, it is still worth introducing.

SQL injection is an old topic. It occurs when SQL commands are inserted into a request and then used after being received by the server without effective filtering. This causes the server to execute unintended, malicious SQL commands, ultimately enabling malicious data extraction.

The principles behind SQL injection in Java and PHP are essentially the same. In theory, SQL injection may occur wherever data is exchanged with a database if the supplied data is fully controlled by the user and is not effectively filtered.

One distinctive aspect of Java is that some frameworks manage part of the database operations, so we need to understand them.

0x02 Categories: Concatenation and Prepared Statements

1. Direct concatenation without filtering

The id obtained through request.getParameter("id") is inserted directly into the SQL statement without filtering and is concatenated into the query.

The traditional JDBC approach shown below uses direct string concatenation and is vulnerable to SQL injection:

request.getParameter("userId")

private String getNameByUserId(String userId) {
    Connection conn = getConn();//获得连接
    String sql = "select name from user where id=" + userId;
    PreparedStatement pstmt =  conn.prepareStatement(sql);
    ResultSet rs=pstmt.executeUpdate();
}

Search globally for issues involving =" or sql.

Preventive measures and remediation

//安全的，预编译的，防止了sql注入
Connection conn = getConn();//获得连接
String sql = "select id, username, password, role from user where id=?"; //执行sql前会预编译号该条语句
PreparedStatement pstmt = conn.prepareStatement(sql); 
pstmt.setString(1, id); 
ResultSet rs=pstmt.executeUpdate();

2. MyBatis and Hibernate Frameworks

The main issue is the incorrect use of prepared statements.

These frameworks generally use annotations or XML to map Java objects to database SQL operations.

The following uses MyBatis as an example.

  • MyBatis Maven configuration
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.2</version>
</dependency>
  • config.xml, the database connection configuration file
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url"
                          value="jdbc:mysql://localhost:3306/mybatistest"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="UserMapper.xml"/>
    </mappers>
</configuration>

Key points to note!

  • UserMapper.xml

Correct use of prepared statements:

<select id="selectByNameAndPassword" parameterType="java.util.Map" resultMap="BaseResultMap">
select id, username, password, role
from user
where username = #{username,jdbcType=VARCHAR}
and password = #{password,jdbcType=VARCHAR}
</select>

Vulnerable use of prepared statements:

<select id="selectByNameAndPassword" parameterType="java.util.Map" resultMap="BaseResultMap">
select id, username, password, role
from user
where username = ${username,jdbcType=VARCHAR}
and password = ${password,jdbcType=VARCHAR}
</select>

Difference between # and $:

  1. # treats all supplied data as a string and automatically encloses it in quotes. This is the correct form of parameterization, and all supplied parameters become values within the query.

  2. $ directly concatenates the supplied data into the SQL statement, resulting in SQL injection. For example: where username=${username}. If the supplied value is 111, the parsed SQL becomes where username=111;
    if the supplied value is 1 and 1=1 ;, the parsed SQL becomes: select id, username, password, role from user where username=1 and 1=1

0x03 Three Situations in Which MyBatis Is Prone to SQL Injection Vulnerabilities

1. Fuzzy queries

Select * from news where title like ‘%#{title}%’

In this situation, using # causes the program to report an error. A novice programmer may therefore replace # with $. If the Java code does not process the user-supplied content, this will inevitably create an SQL injection vulnerability.

Correct usage:

select * from news where tile like concat(‘%’,#{title}, ‘%’)

2. Multiple parameters after IN

Using # when querying multiple IDs after IN will likewise cause an error.

Select * from news where id in (#{ids})

The correct approach is to use foreach rather than replacing # with $.

id in<foreach collection="ids" item="item" open="("separatosr="," close=")">#{ids} </foreach>

3. After ORDER BY

By default, using the #\{\} syntax causes MyBatis to create a PreparedStatement parameter and set it safely, just as when using ?. This is safer and faster, and is usually the preferred approach.

When sorting news articles by information such as publication time or click count, the corresponding SQL statement should look like the following if secure coding standards are taken into consideration:

Select * from news where title =‘123’ order by #{time} asc

However, because the publication time field time is not a user-supplied parameter, it cannot be used in a prepared statement. The developers therefore modified the SQL query as follows:

Select * from news where title =‘123’ order by ${time} asc

After this change, the program passes precompilation, but SQL statement concatenation is introduced, making an SQL injection vulnerability highly likely.

However, sometimes you may want to insert an unescaped string directly into an SQL statement. For example, with ORDER BY, you can use: ORDER BY \$ \{COLUMNNAME\}. MyBatis will not modify or escape the string in this case.

Note that in SQL statements automatically generated by mybatis-generator, ORDER BY also uses $, while LIKE and IN do not have this issue.

0x04 Preventing SQL Injection in Hibernate

Binding by parameter name: Named parameters defined in an HQL statement must begin with :, as shown below:

Query query=session.createQuery(“from User user where user.name=:customername and user:customerage=:age ”); 
query.setString(“customername”,name); 
query.setInteger(“customerage”,age);

Binding by parameter position:
Use \? in an HQL query statement to define parameter positions, as shown below:

Query query=session.createQuery(“from User user where user.name=? and user.age =? ”); 
query.setString(0,name); 
query.setInteger(1,age);

The setParameter\(\) method:
In Hibernate HQL queries, the setParameter\(\) method can bind parameters of any type, as shown below:

String hql=”from User user where user.name=:customername ”; 
Query query=session.createQuery(hql); 
query.setParameter(“customername”,name,Hibernate.STRING);

The setProperties\(\) method:

Entity entity=new Entity();
entity.setXx(“xx”);
entity.setYy(100);
Query query=session.createQuery(“from Entity c where c.xx=:xx and c.yy=:yy ”); 
query.setProperties(entity);

0x05 Discovery Methods

Use IntelliJ IDEA to search for the $ keyword.

You can first filter for XML files and search for $, then analyze the results one by one. Pay particular attention to ORDER BY injection in code generated by mybatis-generator.

Press Ctrl+Shift+F to open Find in Path, filter for files with the .xml extension, and search for the $ keyword.

Identify the MyBatis database mapping file.

After locating the function that calls it, press Alt+F7 to inspect the call chain and check whether the input is filtered at any point along the way.