Understanding the Differences Between ${} and #{} in MyBatis and Their Impact on SQL Injection

The article begins with a brief introduction by the author, a senior architect, and then dives into the core topic: the distinction between ${} and #{} in MyBatis.

1.1 Demonstration

Sample DAO interface methods are shown:

List
findByUsername(String username);
List
findByUsername2(String username);

Corresponding Mapper.xml snippets illustrate the two placeholders:

select * from user where username like #{username}
select * from user where username like '%${value}%'

A JUnit test runs both queries and prints the results, confirming that both retrieve data correctly.

1.2 SQL Injection Issue

The article shows that using ${} can lead to SQL injection, while #{} prevents it. A malicious input " aaa' or 1=1 -- " is passed to the ${} version, producing a vulnerable SQL statement that returns all rows.

When the same input is used with #{} , the generated SQL safely escapes the characters, avoiding injection.

1.3 Detailed Difference

#{} acts as a placeholder that MyBatis replaces with a prepared‑statement parameter (similar to JDBC ? ), automatically quoting and escaping dangerous characters. ${} performs direct string substitution, inserting the raw value into the SQL.

1.4 How #{ } Prevents Injection

The article examines the MySQL driver source, focusing on the setString() method of PreparedStatement . It explains that the driver scans each character, escapes null, newline, carriage‑return, control characters, and quotes, then sends the safely‑encoded value to the server.

It also notes common misconceptions: the protection is not due to MySQL's own pre‑compilation but to the driver’s handling of parameters.

1.5 Application Scenarios

#{} should be used for ordinary query parameters (e.g., WHERE clause values) because it guards against injection. ${} is suitable when the value must become part of the SQL syntax itself, such as dynamic table names, column lists, or ORDER BY clauses, where the developer manually ensures safety.

Examples illustrate fuzzy searches using both placeholders, showing how #{} adds quotes automatically while ${} inserts the raw string.

1.6 Summary

#{} prevents SQL injection by using PreparedStatement.setString() which escapes special characters.

${} performs raw string concatenation and therefore can be vulnerable.

#{} can accept any value without manual quoting; ${} requires the developer to include quotes if needed.

Use #{} for value parameters, ${} for SQL fragments that must be assembled dynamically.

The article concludes with a practical tip: enabling MySQL general logs to observe the exact SQL generated by MyBatis, and provides configuration snippets for my.cnf and service restart commands.