Preventing SQL Injection: Principles, Practices, and Safe MyBatis Usage

SQL injection has become one of the top three web application vulnerabilities according to OWASP, and it remains a major threat to backend systems because it allows attackers to manipulate SQL statements and compromise databases.

SQL Injection Principle An attacker injects malicious SQL code into user‑controlled inputs (e.g., query strings or form fields) so that the database executes unintended commands. For example, a URL like http://www.example.com?test=123 may lead to a query SELECT * FROM user WHERE testId = 123 . If the attacker changes the parameter to 123 OR 1=1 , the executed SQL becomes SELECT * FROM user WHERE testId = 123 OR 1=1 , returning all rows.

Potential Harms 1. Database information leakage 2. Malicious database operations that affect the server 3. Deletion or modification of table data 4. Remote control of the server

General Prevention Principles 1) Use parameterized execution (PreparedStatement) whenever possible. 2) If parameters cannot be parameterized, receive them as strong types (e.g., enums). 3) When strong typing is not feasible, perform strict format validation. 4) As a last resort, apply proper escaping of injection characters.

Practical Measures

Practice 1: Use database‑side prepared statements to separate SQL logic from data.

Practice 2: Prefer non‑String (or enum) types for input parameters.

Practice 3: Perform whitelist validation for enumerable String parameters such as sorting fields.

Practice 4: Use stored procedures (though generally discouraged).

Practice 5: Apply escaping only when all other methods are impossible.

MyBatis Specific Guidance MyBatis XML mappers support two placeholder syntaxes: #{xxx} (parameterized) and ${xxx} (direct substitution). Use #{xxx} to avoid injection; avoid ${xxx} unless the value is validated and escaped. Example of unsafe usage: account = '${account}' should be replaced with account = #{account} .

Common unsafe patterns and their safe alternatives include:

IN clause: WHERE id IN (${item.ids}) → use <if test="ids != null and ids.size() > 0"> AND id IN <foreach collection="ids" item="id" open="(" separator="," close=")">#{id}</foreach> </if>

TOP clause: TOP ${queryCnt} → TOP (#{queryCnt})

BETWEEN clause: BETWEEN ${start} AND ${end} → BETWEEN #{start} AND #{end}

LIKE clause: email LIKE '%${emailSuffix}' → email LIKE concat('%', #{emailSuffix}) or bind a pattern in Java and use #{pattern}

LIMIT clause: LIMIT ${offset}, ${size} → LIMIT #{offset}, #{size}

Dynamic ORDER BY: avoid ${orderByClause} ; instead use conditional if blocks or whitelist the column names.

In addition to code‑level defenses, deploying a Web Application Firewall (WAF) can provide an extra layer of protection against SQL injection attacks.

Conclusion The article records the technical department’s exploration of SQL‑injection prevention, covering the attack principle, overall defensive guidelines, and concrete safe coding practices (especially for MyBatis), aiming to help other teams improve their security posture.