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 :

org.mybatis.spring.boot
mybatis-spring-boot-starter
2.1.1
mysql
mysql-connector-java

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
getUserInfo(@RequestParam("username") String username){
        List
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
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
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
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:

SELECT * FROM t_user WHERE username = ${username}

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.