Why MyBatis # vs $ Matters: Fixing SQL IN Errors and Avoiding Injection

Introduction

This issue was discovered during a code optimization when some data could not be retrieved after a functional update; the root cause was the handling of # and $ in a MyBatis SQL statement.

Difference

#{ } is processed as a prepared‑statement parameter: MyBatis replaces it with ? and sets the value via PreparedStatement, automatically adding single quotes around string values (e.g., "'4,44,514'").

${ } performs direct string substitution; the value is inserted as‑is without surrounding quotes, which can lead to SQL injection vulnerabilities.

SQL injection occurs when malicious SQL commands are inserted into web forms, query strings, or other inputs, allowing attackers to execute arbitrary queries.

Application Scenarios

Use #{ } for DAO parameters in mapped SQL to benefit from pre‑compilation and safe handling.

Use ${ } only for dynamic identifiers such as column or table names, and never for user‑provided values.

Problem Analysis

Initially the SQL appeared correct when run directly in the database, but after outputting the generated SQL from the application, it was clear that the IN clause values were being wrapped in single quotes, turning wwlr.LabelId in(4,44,514) into wwlr.LabelId in('4,44,514'), causing missing data.

Solution

Quick Fix

Replacing # with $ seemed to work locally but failed in the Docker deployment because the environment applies an additional SQL‑injection protection layer that disables the unsafe substitution.

Using foreach Tag

The proper solution is to convert the string parameter into a List<Integer> and use MyBatis's foreach tag to build the IN clause safely.

Original source: https://m.toutiaocdn.com/i6685496024770806280