Preventing SQL Injection: Use Prepared Statements and MyBatis Safely

In web development we constantly interact with databases, and a seemingly harmless login box can hide a risk capable of crippling an entire system: SQL injection.

What is SQL injection?

SQL injection is a code‑injection technique where an attacker inserts malicious SQL fragments into application input fields, tricking the server into executing unintended database operations such as data theft, tampering, or even gaining administrative privileges.

Typical attack scenario: from query to "drop table"

The following diagram (originally shown in the source) illustrates how the same query endpoint behaves differently for a normal user versus an attacker.

Assume a simple query that retrieves a user by username is built by concatenating strings: SELECT * FROM users WHERE username = '输入的用户名'; Normal case: User inputs admin. The concatenated SQL becomes SELECT * FROM users WHERE username = 'admin'; which executes correctly and returns the expected result.

Attack case: Attacker inputs admin'; DROP TABLE users; --. The resulting SQL is

SELECT * FROM users WHERE username = 'admin'; DROP TABLE users; --';

, turning a harmless query into a destructive command.

Breaking down the malicious SQL: ' closes the original string literal, allowing the rest to be parsed as a complete statement. ; separates statements, signalling the end of the first query and the start of a new one. DROP TABLE users; is the injected destructive command that permanently deletes the user table. -- comments out any trailing characters that would cause a syntax error.

Root cause: data‑code boundary collapse

The problem arises because developers embed raw user input (data) directly into SQL code via string concatenation. The database cannot distinguish between intended SQL logic and injected data, so special characters like ', ;, and -- allow the data to become executable code.

Core solution: PreparedStatement (parameterized query)

Parameterized queries separate the SQL structure from user data, eliminating the injection vector. In Java JDBC this is implemented with PreparedStatement:

Compilation phase: The developer sends a SQL template containing placeholders (e.g., SELECT * FROM users WHERE username = ?). The database parses, compiles, and creates a fixed execution plan; the structure cannot be altered later.

Execution phase: User input (e.g., admin'; DROP TABLE users; --) is passed as a parameter. The database safely inserts the value into the pre‑compiled placeholder, treating it purely as data, regardless of any special characters.

This process is analogous to a mold: the SQL template defines the shape, and user input is poured in as harmless material that cannot change the mold.

MyBatis practice: #{ } vs. ${ }

MyBatis offers two ways to inject parameters into SQL: #{} (safe): Internally translated to a PreparedStatement placeholder ( ?) and executed as a parameterized query. This should be the default choice for all dynamic data. ${} (dangerous): Performs direct string substitution, re‑introducing the risk of SQL injection. It should only be used when the SQL structure itself must change (e.g., dynamic ORDER BY ${columnName}), and even then the substituted value must be strictly whitelisted (e.g., only 'id', 'name', 'gmt_create').

Depth defense (Defense‑in‑Depth)

While parameterized queries are a strong defense, a robust system should employ multiple layers:

Input validation: Rigorously check data formats and types before processing.

Principle of least privilege: Grant the database account only the permissions required for the application (e.g., deny DROP, TRUNCATE).

Error message sanitization: Log detailed errors internally but return generic messages to users.

Web Application Firewall (WAF): Deploy a WAF to detect and block known SQL injection patterns at the entry point.

Never trust user input. This principle should be ingrained in every developer’s mindset.