mybatis LIKE 查询时 $、# sql注入问题分析

先说结论：

1. 存在 sql 注入安全问题：

SELECT 
	fd_userid,
	fd_nickname 
FROM 
	tb_user 
WHERE  
	fd_nickname LIKE '%${value}%'

2. 不存在 sql 注入安全问题：

SELECT 
	fd_userid,fd_nickname 
FROM 
	tb_user 
WHERE  
	fd_nickname LIKE  CONCAT( '%',#{value},'%' )

因为 $ 就是最简单的拼接嘛，原本是啥就给直接拼接在一起，比如 value 如果如下所示：

xxx' UNION  SELECT fd_userid,fd_nickname FROM tb_user WHERE  fd_nickname LIKE '泰戈尔

则拼接以后得完整 sql 如下：

SELECT 
	fd_userid,
	fd_nickname 
FROM 
	tb_user 
WHERE  
	fd_nickname LIKE '%xxx' 
UNION  
SELECT 
	fd_userid,
	fd_nickname 
FROM 
	tb_user 
WHERE  
	fd_nickname LIKE '泰戈尔%'

很显然查询结果已经不如预期了，查询结果如下：

在 springboot + mybatis 工程中完整测试：

TestController.java：

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
import java.util.Map;

@Api(value = "TestController",description="TestController")
@RestController
@Slf4j
@RequestMapping("/test")
public class TestController {

    @Autowired
    private ITbUserService userService;

    @ApiOperation(value = "queryUserListByNicknameLikeQuery_notSafe")
    @GetMapping( "/queryUserListByNicknameLikeQuery_notSafe" )
    public ResponseEntity queryUserListByNicknameLikeQuery_notSafe(@RequestParam("value") String value)  {
        try {
            List<TbUser> users = this.userService.queryUserListByNicknameLikeQuery_notSafe( value );
            return ResponseEntity.success( users );
        }catch ( BusinessLogicException e ){
            return ResponseEntity.serverError( e.getMessage() );
        }catch ( Exception e ){
            log.error( CommonConstant.EXCEPTION_PREFIX,e );
            return ResponseEntity.serverError( "查询失败，服务器错误" );
        }
    }

    @ApiOperation(value = "queryUserListByNicknameLikeQuery_safe")
    @GetMapping( "/queryUserListByNicknameLikeQuery_safe" )
    public ResponseEntity queryUserListByNicknameLikeQuery_safe(@RequestParam("value") String value)  {
        try {
            List<TbUser> users = this.userService.queryUserListByNicknameLikeQuery_safe( value );
            return ResponseEntity.success( users );
        }catch ( BusinessLogicException e ){
            return ResponseEntity.serverError( e.getMessage() );
        }catch ( Exception e ){
            log.error( CommonConstant.EXCEPTION_PREFIX,e );
            return ResponseEntity.serverError( "查询失败，服务器错误" );
        }
    }
}

ITbUserService.java：

import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.text.ParseException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public interface ITbUserService extends IService<TbUser> {

    List<TbUser> queryUserListByNicknameLikeQuery_notSafe(String value);
    List<TbUser> queryUserListByNicknameLikeQuery_safe(String value);
}

TbUserServiceImpl.java：

import cn.hutool.core.util.ZipUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import sun.misc.BASE64Encoder;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.*;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.List;
import java.util.stream.Collectors;


@Service
@Slf4j
public class TbUserServiceImpl extends ServiceImpl<TbUserMapper, TbUser> implements ITbUserService {
    
    @Autowired
    TbUserMapper userMapper;


    @Override
    public List<TbUser> queryUserListByNicknameLikeQuery_notSafe(String value) {
        return this.userMapper.queryUserListByNicknameLikeQuery_notSafe( value );
    }
    @Override
    public List<TbUser> queryUserListByNicknameLikeQuery_safe(String value) {
        return this.userMapper.queryUserListByNicknameLikeQuery_safe( value );
    }
}

TbUserMapper.java：

import org.apache.ibatis.annotations.Param;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@DataSource("dataSource")
public interface TbUserMapper extends BaseMapper<TbUser> {

    List<TbUser> queryUserListByNicknameLikeQuery_notSafe(@Param( "value" ) String value);
    List<TbUser> queryUserListByNicknameLikeQuery_safe(@Param( "value" ) String value);
}

TbUserMapper.xml：

<?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.xxx.mapper.TbUserMapper">
    <select id="queryUserListByNicknameLikeQuery_notSafe" resultType="com.xxx.xxx.xxx.entity.TbUser">
        SELECT fd_userid,fd_nickname FROM tb_user WHERE  fd_nickname LIKE '%${value}%'
    </select>

    <select id="queryUserListByNicknameLikeQuery_safe" resultType="com.xxx.xxx.xxx.entity.TbUser">
        SELECT fd_userid,fd_nickname FROM tb_user WHERE  fd_nickname LIKE  CONCAT( '%',#{value},'%' )
    </select>
</mapper>

然后 value 传递 xxx' UNION  SELECT fd_userid,fd_nickname FROM tb_user WHERE  fd_nickname LIKE '泰戈尔，分别调用 接口 queryUserListByNicknameLikeQuery_notSafe、queryUserListByNicknameLikeQuery_safe 进行测试