Understanding SQL Injection and Prevention with Spring Boot and MyBatis

SQL injection is a common web attack that occurs when input validation is insufficient, allowing attackers to retrieve or modify data beyond their authorized scope.

An example vulnerable query is SELECT * FROM t_user WHERE id = 1 OR 1=1;, which returns all rows because the injected condition always evaluates to true.

First, a test table is created in MySQL and populated with sample data:

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(30) DEFAULT NULL,
  `password` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_user` VALUES ('1','John','111111');
INSERT INTO `t_user` VALUES ('2','Tom','222222');

The Java side uses Spring Boot with MyBatis. Required dependencies are added to pom.xml:

<!-- 连接Spring Boot和MyBatis -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.1</version>
</dependency>

<!-- mysql 驱动 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

The controller exposes an endpoint that receives a username and returns a list of matching users:

package com.fullstack.commerce.user.controller;

import com.fullstack.commerce.user.entity.User;
import com.fullstack.commerce.user.service.UserService;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import java.util.List;

@RestController
@RequestMapping("user")
public class UserController {
    @Resource
    private UserService userService;

    @RequestMapping("getUserInfo")
    @ResponseBody
    // 根据用户姓名查询出用户列表信息
    public List<User> getUserInfo(@RequestParam("username") String username){
        List<User> result = userService.getUserInfo(username);
        return result;
    }
}

The service layer consists of an interface and its implementation:

package com.fullstack.commerce.user.service;

import com.fullstack.commerce.user.entity.User;
import java.util.List;

public interface UserService {
    List<User> getUserInfo(String username);
}

package com.fullstack.commerce.user.service.impl;

import com.fullstack.commerce.user.dao.UserDao;
import com.fullstack.commerce.user.entity.User;
import com.fullstack.commerce.user.service.UserService;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;

@Service
public class UserServiceImpl implements UserService {
    @Resource
    private UserDao userDao;

    @Override
    public List<User> getUserInfo(String username) {
        return userDao.getUserInfo(username);
    }
}

The DAO layer defines a single method to query the database:

package com.fullstack.commerce.user.dao;

import com.fullstack.commerce.user.entity.User;
import java.util.List;

public interface UserDao {
    List<User> getUserInfo(String username);
}

The Spring Boot application entry point scans the mapper package:

package com.fullstack.commerce.user;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan(basePackages = "com.fullstack.commerce.user.dao")
public class UserApplication {
    public static void main(String[] args) {
        SpringApplication.run(UserApplication.class, args);
    }
}

The MyBatis mapper XML uses a $ placeholder, which directly substitutes the parameter and is vulnerable to injection:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.fullstack.commerce.user.dao.UserDao">
    <resultMap id="UserMap" type="com.fullstack.commerce.user.entity.User">
        <result property="id" column="id" jdbcType="INTEGER"/>
        <result property="username" column="username" jdbcType="VARCHAR"/>
        <result property="password" column="password" jdbcType="VARCHAR"/>
    </resultMap>

    <select id="getUserInfo" resultMap="UserMap">
        SELECT * FROM t_user WHERE username = ${username}
    </select>
</mapper>

Configuration in application.yml supplies the datasource details:

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test
    username: myuser 
    password: myuser
    driver-class-name: com.mysql.cj.jdbc.Driver
mybatis:
  mapper-locations: mapper/*.xml

Testing the endpoint with http://localhost:8080/user/getUserInfo?username='John' returns the expected single record. Changing the URL to

http://localhost:8080/user/getUserInfo?username='John' OR 1=1

demonstrates the injection, returning all rows because the $ placeholder concatenates the raw input.

Replacing the placeholder with the safe # syntax forces MyBatis to use a prepared statement, preventing the injection; the same malicious URL then yields no results.

In summary, SQL injection is a prevalent threat, and when using MyBatis it is essential to employ parameter placeholders (#{}) rather than direct string substitution ($) to ensure queries are pre‑compiled and user input cannot alter the intended SQL logic.