Why MyBatis # vs $ Matters: Preventing SQL Injection and Fixing IN-Clause Errors

Problem Overview

During a code‑optimization process, some data could not be retrieved after functional changes because of the handling of # and $ in an SQL statement.

Difference between #{ } and ${ }

#{ } is a pre‑compiled placeholder. MyBatis replaces it with ? and uses PreparedStatement to set the value, automatically adding single quotes around string parameters (e.g., "4,44,514" becomes '4,44,514').

${ } performs direct string substitution without adding quotes, which can lead to SQL injection vulnerabilities.

Security Implications

Using ${ } can expose the application to SQL injection, where malicious SQL commands are injected via user input or request parameters.

Use Cases

#{ } : Primarily used for DAO parameters in mapped SQL statements, creating a pre‑compiled statement.

${ } : Mainly used for configuration values, field names, or table names where dynamic SQL is required; however, it should be limited to non‑user‑controlled data.

When using ${ } for DAO parameters, the parameter must be annotated with @Param or referenced by index.

Problem Analysis

By printing the executed SQL to the console (see image below), it was discovered that the IN clause was rendered as wwlr.LabelId in ('4,44,514'), causing the query to miss records.

Solution

The quick fix of replacing # with $ works but reintroduces security risks. A safer approach is to use MyBatis foreach to build the IN clause from a collection.

By converting the original string parameter (e.g., "4,44,514") into a List<Integer> and iterating with foreach, the query becomes correctly formatted without extra quotes, eliminating the data‑missing issue and preventing SQL injection.