# MyBatis SQL Injection Security Audit Guide

> MyBatis Framework SQL Injection Dedicated Audit Module
> Applicable to: MyBatis 3.x, MyBatis-Plus, Spring Boot + MyBatis Integration
> Source: Critical gaps found in DataEase audit — `${}` injection detection requires systematic coverage

## Core Principle: `${}` vs `#{}`

```xml
<!-- #{} = PreparedStatement parameter binding (safe) -->
<select id="getUser" resultType="User">
    SELECT * FROM users WHERE id = #{id}
</select>
<!-- Generates: SELECT * FROM users WHERE id = ?  ✓ -->

<!-- ${} = Direct string replacement (dangerous) -->
<select id="getUser" resultType="User">
    SELECT * FROM users WHERE id = ${id}
</select>
<!-- Generates: SELECT * FROM users WHERE id = 1 OR 1=1  ❌ -->

<!-- Core difference:
     #{} → JDBC PreparedStatement, parameterized binding, injection prevention
     ${} → String concatenation, directly replaced into SQL, injectable
-->
```

---

## `${}` Basic Detection

```xml
<!-- Dangerous: Using ${} in WHERE conditions -->
<select id="findUser">
    SELECT * FROM users WHERE name = '${name}'      <!-- ❌ Critical -->
</select>

<select id="findById">
    SELECT * FROM users WHERE id = ${id}             <!-- ❌ Critical -->
</select>

<!-- Audit command: Find all ${} in XML -->
grep -rn '\$\{' --include="*.xml" src/main/resources/mapper/

<!-- Audit regex: Precisely match ${} in SQL -->
WHERE.*\$\{|AND.*\$\{|OR.*\$\{|HAVING.*\$\{|SET.*\$\{

<!-- Security fix -->
<select id="findUser">
    SELECT * FROM users WHERE name = #{name}         <!-- ✓ -->
</select>
```

---

## ORDER BY / LIMIT Injection (Cannot use `#{}`)

```xml
<!-- Problem: Cannot use #{} after ORDER BY, because it gets quoted as a string -->
<!-- #{sort} → ORDER BY 'name' (syntax error) -->
<!-- Therefore developers commonly use ${} -->

<!-- Dangerous: ORDER BY injection -->
<select id="listUsers">
    SELECT * FROM users ORDER BY ${sortColumn} ${sortOrder}  <!-- ❌ High -->
</select>
<!-- Attack: sortColumn = (CASE WHEN (SELECT 1 FROM users WHERE name='admin'
     AND password LIKE 'a%') THEN id ELSE name END) -->

<!-- Dangerous: LIMIT injection -->
<select id="pageUsers">
    SELECT * FROM users LIMIT ${offset}, ${limit}            <!-- ❌ -->
</select>

<!-- Dangerous: GROUP BY injection -->
<select id="stats">
    SELECT count(*) FROM orders GROUP BY ${groupField}       <!-- ❌ -->
</select>

<!-- Audit regex -->
ORDER\s+BY\s+\$\{|LIMIT\s+\$\{|GROUP\s+BY\s+\$\{|OFFSET\s+\$\{

<!-- Security fix: Java layer whitelist validation -->
```
```java
// ORDER BY safe handling
private static final Set<String> ALLOWED_COLUMNS = Set.of("id", "name", "created_at");
private static final Set<String> ALLOWED_ORDERS = Set.of("ASC", "DESC");

public List<User> listUsers(String sortColumn, String sortOrder) {
    if (!ALLOWED_COLUMNS.contains(sortColumn)) {
        sortColumn = "id";  // ✓ Default value
    }
    if (!ALLOWED_ORDERS.contains(sortOrder.toUpperCase())) {
        sortOrder = "ASC";  // ✓
    }
    return mapper.listUsers(sortColumn, sortOrder);
}

// LIMIT safe handling: Force type conversion
int offset = Integer.parseInt(offsetStr);  // ✓ Non-numeric throws exception
int limit = Math.min(Integer.parseInt(limitStr), 100);  // ✓ Upper bound
```

---

## Dynamic Table/Column Name Injection

```xml
<!-- Dangerous: Dynamic table name (cannot use #{}) -->
<select id="queryTable">
    SELECT * FROM ${tableName} WHERE id = #{id}              <!-- ❌ Critical -->
</select>

<!-- Dangerous: Dynamic column name -->
<select id="getField">
    SELECT ${columns} FROM users WHERE id = #{id}            <!-- ❌ -->
</select>

<!-- Audit regex -->
FROM\s+\$\{|INTO\s+\$\{|UPDATE\s+\$\{|JOIN\s+\$\{
SELECT\s+\$\{|INSERT\s+INTO.*\(\s*\$\{

<!-- Security fix: Whitelist + regex validation -->
```
```java
private static final Set<String> ALLOWED_TABLES = Set.of("users", "orders", "products");

public List<Map> queryTable(String tableName) {
    if (!ALLOWED_TABLES.contains(tableName)) {
        throw new IllegalArgumentException("Invalid table name");
    }
    // Additional validation: Only allow alphanumeric and underscores
    if (!tableName.matches("^[a-zA-Z_][a-zA-Z0-9_]*$")) {  // ✓
        throw new IllegalArgumentException("Invalid table name format");
    }
    return mapper.queryTable(tableName);
}
```

---

## `${}` in Conditional Branches (Hidden Injection)

```xml
<!-- Dangerous: ${} in <if> conditions — Only injects when condition is met, easily missed in audit -->
<select id="search">
    SELECT * FROM users WHERE 1=1
    <if test="name != null">
        AND name LIKE '%${name}%'                            <!-- ❌ Hidden in if -->
    </if>
    <if test="sortField != null">
        ORDER BY ${sortField}                                <!-- ❌ -->
    </if>
</select>

<!-- Dangerous: ${} in <choose>/<when> -->
<select id="dynamicQuery">
    SELECT * FROM users
    <choose>
        <when test="searchType == 'name'">
            WHERE name = '${searchValue}'                    <!-- ❌ -->
        </when>
        <when test="searchType == 'email'">
            WHERE email = '${searchValue}'                   <!-- ❌ -->
        </when>
    </choose>
</select>

<!-- Dangerous: ${} in <foreach> -->
<select id="batchQuery">
    SELECT * FROM ${tableName} WHERE id IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        ${id}                                                <!-- ❌ -->
    </foreach>
</select>

<!-- Audit regex: ${} in conditional branches -->
<if\s+test=.*>\s*.*\$\{|<when\s+test=.*>\s*.*\$\{
<foreach.*\$\{

<!-- Security fix: LIKE query using #{} + CONCAT -->
<if test="name != null">
    AND name LIKE CONCAT('%', #{name}, '%')                  <!-- ✓ -->
</if>
```

---

## MyBatis-Plus Wrapper Injection

```java
// Dangerous: apply() concatenating SQL fragments
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.apply("name = '" + userInput + "'");              // ❌ Critical
wrapper.apply("date_format(create_time,'%Y') = " + year); // ❌

// Dangerous: last() appending arbitrary SQL
wrapper.last("LIMIT " + userInput);                       // ❌ Critical
wrapper.last("ORDER BY " + sortField);                    // ❌

// Dangerous: orderByAsc/orderByDesc string
wrapper.orderByAsc(userInput);                            // ❌ High

// Dangerous: having() concatenation
wrapper.having("count > " + count);                       // ❌

// Dangerous: exists/notExists concatenation
wrapper.exists("SELECT 1 FROM admin WHERE id = " + id);  // ❌

// Audit regex
\.apply\s*\(.*\+|\.last\s*\(.*\+|\.having\s*\(.*\+
\.exists\s*\(.*\+|\.orderByAsc\s*\(.*userInput|\.orderByDesc\s*\(.*param

// Safe: apply with parameter placeholder
wrapper.apply("name = {0}", userInput);                   // ✓ MyBatis-Plus parameter binding
wrapper.apply("date_format(create_time,'%Y') = {0}", year); // ✓
wrapper.last("LIMIT 10");                                 // ✓ Hardcoded

// Safe: orderBy whitelist
wrapper.orderByAsc(ALLOWED_COLUMNS.contains(col) ? col : "id");  // ✓
```

---

## Provider Dynamic SQL Injection

```java
// Dangerous: Concatenation in @SelectProvider
public class UserSqlProvider {
    public String findUser(String name) {
        return "SELECT * FROM users WHERE name = '" + name + "'";  // ❌ Critical
    }
}

@SelectProvider(type = UserSqlProvider.class, method = "findUser")
User findUser(@Param("name") String name);

// Dangerous: @UpdateProvider concatenation
public class UpdateProvider {
    public String updateField(Map<String, Object> params) {
        return "UPDATE users SET " + params.get("field") +   // ❌
               " = '" + params.get("value") + "' WHERE id = " + params.get("id");
    }
}

// Audit regex
@SelectProvider|@InsertProvider|@UpdateProvider|@DeleteProvider
return\s*".*SELECT.*\+|return\s*".*UPDATE.*\+|return\s*".*DELETE.*\+

// Safe: Use MyBatis SQL Builder
public String findUser(@Param("name") String name) {
    return new SQL() {{
        SELECT("*");
        FROM("users");
        WHERE("name = #{name}");  // ✓ Use #{} placeholder
    }}.toString();
}
```

---

## Indirect Injection (Concatenated in Java layer then passed to MyBatis)

```java
// Dangerous: StringBuilder concatenation then passed in
StringBuilder condition = new StringBuilder();
condition.append("name = '").append(userInput).append("'");  // ❌
mapper.queryByCondition(condition.toString());
// XML: SELECT * FROM users WHERE ${condition}

// Dangerous: String.format building conditions
String filter = String.format("status = '%s' AND type = '%s'", status, type);  // ❌
mapper.queryWithFilter(filter);

// Dangerous: Multi-layer calls masking injection (typical DataEase pattern)
// Service layer
public String buildWhereClause(ChartView view) {
    return "column_name " + view.getFilterOperator() + " '" + view.getFilterValue() + "'";  // ❌
}
// Call chain: Controller → Service.buildWhereClause() → Mapper.query(${whereClause})

// Audit method: Trace data source of all ${} parameters
// 1. Find ${paramName} in XML
// 2. Trace to Mapper interface method parameters
// 3. Trace how the parameter is constructed in Service layer
// 4. Trace to Controller layer parameter source (is it from user input?)

// Audit regex (Java layer)
StringBuilder.*append\s*\(.*\+.*append|String\.format\s*\(.*SELECT|String\.format\s*\(.*WHERE
".*SELECT.*"\s*\+|".*WHERE.*"\s*\+|".*ORDER BY.*"\s*\+
```

---

## Second-Order Injection (Store → Retrieve → SQL)

```java
// Stage 1: Store malicious data (no injection, normal INSERT)
// User input name = "admin' OR '1'='1"
mapper.insertUser(user);  // INSERT INTO users(name) VALUES(#{name}) — Stored safely

// Stage 2: Retrieve and concatenate into SQL (injection triggered)
String name = mapper.getUserName(userId);  // Retrieved: admin' OR '1'='1
// Dangerous: Concatenating database values into SQL
String sql = "SELECT * FROM orders WHERE customer = '" + name + "'";  // ❌
// Actual SQL: SELECT * FROM orders WHERE customer = 'admin' OR '1'='1'

// Audit strategy:
// 1. Search all ${} parameters
// 2. If parameter value comes from database query results (not direct user input), it is still second-order injection
// 3. Pay special attention to: Configuration tables, user-defined fields, data source names, etc.

// Audit regex
// Find pattern of concatenation after database value retrieval
get.*\(\).*\+.*".*SELECT|get.*\(\).*\+.*".*WHERE
```

---

## XML Mapper Audit Workflow

```bash
# Step 1: Full scan for ${} usage
grep -rn '\$\{' --include="*.xml" src/main/resources/

# Step 2: Categorize
# ${} in WHERE/AND/OR conditions — Critical
grep -rn 'WHERE.*\$\{' --include="*.xml" src/
grep -rn 'AND.*\$\{' --include="*.xml" src/
grep -rn 'OR.*\$\{' --include="*.xml" src/

# ${} in ORDER BY/LIMIT/GROUP BY — High
grep -rn 'ORDER\s*BY.*\$\{' --include="*.xml" src/
grep -rn 'LIMIT.*\$\{' --include="*.xml" src/
grep -rn 'GROUP\s*BY.*\$\{' --include="*.xml" src/

# ${} in table/column names — High
grep -rn 'FROM\s\+\$\{' --include="*.xml" src/
grep -rn 'SELECT\s\+\$\{' --include="*.xml" src/

# ${} in LIKE — High
grep -rn "LIKE.*\$\{" --include="*.xml" src/

# ${} hidden in <if>/<when>/<foreach> — Medium-High
grep -rn '<if.*>.*\$\{' --include="*.xml" src/
grep -rn '<when.*>.*\$\{' --include="*.xml" src/
grep -rn '<foreach.*\$\{' --include="*.xml" src/

# Step 3: Java layer concatenation detection
grep -rn 'String\.format.*SELECT\|String\.format.*WHERE' --include="*.java" src/
grep -rn '"SELECT.*"\s*+\|"WHERE.*"\s*+\|"ORDER.*"\s*+' --include="*.java" src/

# Step 4: MyBatis-Plus Wrapper
grep -rn '\.apply\s*(.*+\|\.last\s*(.*+' --include="*.java" src/

# Step 5: Provider concatenation
grep -rn '@SelectProvider\|@UpdateProvider' --include="*.java" src/
```

---

## Search Pattern Summary

```regex
# XML ${} full scan
\$\{[^}]+\}

# WHERE condition injection
(WHERE|AND|OR|HAVING|SET)\s.*\$\{

# ORDER/LIMIT injection
(ORDER\s+BY|GROUP\s+BY|LIMIT|OFFSET)\s+\$\{

# Table/column name injection
(FROM|INTO|UPDATE|JOIN|SELECT)\s+\$\{

# LIKE injection
LIKE\s+['"]?%?\$\{

# Conditional branch hidden
<if\s+test=.*\$\{|<when\s+test=.*\$\{|<foreach.*\$\{

# MyBatis-Plus
\.apply\s*\(.*\+|\.last\s*\(.*\+|\.having\s*\(.*\+|\.exists\s*\(.*\+

# Provider
@(Select|Insert|Update|Delete)Provider

# Java layer concatenation
String\.format\s*\(.*"(SELECT|WHERE|ORDER|INSERT|UPDATE|DELETE)
"(SELECT|WHERE|ORDER|INSERT|UPDATE|DELETE).*"\s*\+
StringBuilder.*append.*"(SELECT|WHERE|ORDER)"
```

---

## Quick Audit Checklist

```markdown
[ ] Full scan of all ${} usage locations in XML
[ ] Categorize: WHERE conditions / ORDER BY / table names / column names / LIKE / LIMIT
[ ] Check for ${} hidden inside <if>/<when>/<foreach>
[ ] Trace data source of each ${} parameter (user input → Service → Mapper)
[ ] Search for MyBatis-Plus apply()/last()/having() concatenation
[ ] Search for string concatenation in @SelectProvider/@UpdateProvider
[ ] Search for Java layer StringBuilder/String.format constructing SQL fragments
[ ] Check second-order injection: Database values then concatenated into SQL
[ ] Verify ORDER BY / table names / column names have whitelist
[ ] Verify LIKE uses CONCAT('%', #{}, '%') pattern
[ ] Check parameter binding in batch operations with foreach
```

---

## DataEase Audit Experience Notes

```markdown
Key findings pattern:
1. Data source management: tableName/schemaName concatenated into SQL via ${} → Table name injection
2. Chart queries: User-defined fields/filter conditions concatenated through multi-layer Service → Indirect injection
3. Sort fields: Frontend sort column names used directly with ${} → ORDER BY injection
4. Permission filtering: Dynamically concatenated WHERE clauses passed as strings → Condition injection

Audit challenges:
- Long call chains (Controller → Service → Provider → Mapper), requires complete tracing
- Hidden in conditional branches, only triggered under specific conditions
- Whitelist validation in Service layer is incomplete (misses some parameters)
- Second-order injection: Data source configurations stored then used for dynamic SQL construction
```

---

## Reference Resources

- [MyBatis Official: SQL Injection Prevention](https://mybatis.org/mybatis-3/sqlmap-xml.html)
- [MyBatis-Plus Security](https://baomidou.com/pages/10c804/)
- [OWASP SQL Injection Prevention](https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html)
