【MyBatis】预编译SQL与即时SQL

目录

1. 以基本类型参数为例测试#{ }与${ }传递参数的区别

1.1 参数为Integer类型

1.2 参数为String类型

2. 使用#{ }传参存在的问题

2.1 参数为排序方式

2.2 模糊查询

 3. 使用${ }传参存在的问题

3.1 SQL注入

3.2 对比#{ } 与 ${ }在SQL注入方面存在的问题

3.3 预编译SQL与即时SQL

3.4 解决模糊查询只能使用${}的问题

使用MyBatis进行数据库操作在进行参数传递时，有#{ } 与 ${ }两种方式。

本文介绍两种方式的区别；

1. 以基本类型参数为例测试#{ }与${ }传递参数的区别

1.1 参数为Integer类型

在UserInfoMapper文件中创建selectOne方法，分别使用#{ } 与 ${ }传递参数：

package com.zhouyou.mybatisdemo1.mapper;
import com.zhouyou.mybatisdemo1.model.UserInfo;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserInfoMapper {
    @Select("select* from userinfo where id= #{id}")
    UserInfo selectOne(Integer id);
}

编写测试类UserInfoMapperTest及测试方法：

package com.zhouyou.mybatisdemo1.mapper;

import com.zhouyou.mybatisdemo1.model.UserInfo;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

import static org.junit.jupiter.api.Assertions.*;
@Slf4j
@SpringBootTest
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void selectOne() {
        log.info(userInfoMapper.selectOne(4).toString());
    }
}

 使用#{ }的运行测试类情况：

使用${ }的运行测试类情况：

可见当参数为Intgeer类型时，使用#{ } 与 ${ }均可正确传递参数；

1.2 参数为String类型

在UserInfoMapper文件中创建selectOneByName方法，分别使用#{ } 与 ${ }传递参数：

package com.zhouyou.mybatisdemo1.mapper;
import com.zhouyou.mybatisdemo1.model.UserInfo;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserInfoMapper {
    @Select("select* from userinfo where username =#{username}")
//    @Select("select* from userinfo where username =${username}")
    UserInfo selectOneByName(String name);
}

编写测试类UserInfoMapperTest及测试方法：

package com.zhouyou.mybatisdemo1.mapper;

import com.zhouyou.mybatisdemo1.model.UserInfo;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

import static org.junit.jupiter.api.Assertions.*;
@Slf4j
@SpringBootTest
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void selectOneByName() {
        log.info(userInfoMapper.selectOneByName("zhangsan").toString());
    }
}

启动测试类，

对于使用#{ } 进行参数传递的方法，可正确运行；

对于使用${ } 进行参数传递的方法报错如下：

使用#{ }时，如果参数为String类型，会自动加上'  ' ，

使用${ }时，会直接进行拼接，故如果参数为字符串类型，需手动增加 '  '，修改SQL语句如下：

@Select("select* from userinfo where username ='${username}'")

重启测试类，启动成功，日志如下：

2. 使用#{ }传参存在的问题

2.1 参数为排序方式

现以实现升序（参数为asc）或降序（参数为desc）的全列查询为例，分别使用#{ } 与 ${ }进行参数传递：

1、使用#{ }进行参数传递：

在UserInfoMapper中编写排序方法：

    // 排序
    @Select("select* from userinfo order by id #{sort}")
    List<UserInfo> selectUserBySort(String sort);

生成测试方法并对排序结果进行打印：

    @Test
    void selectUserBySort() {
        log.info(userInfoMapper.selectUserBySort("asc").toString());
    }

启动测试类，报错如下： 

2、使用${ }参数传递：

在UserInfoMapper中编写修改排序方法，使用${ }传参：

    @Select("select* from userinfo order by id ${sort}")
    List<UserInfo> selectUserBySort(String sort);

启动测试类，运行成功：

可见当参数为sql查询语句的排序方式：asc与desc时，若使用#{ }进行参数传递，则在该字符串上加上' '会造成错误，因此对于参数为排序方式的方法，只能使用${ }进行参数传递；

不止排序方式，包括参数为表名、字段名等作为参数时，也不能使用#{ }进行参数传递；

2.2 模糊查询

1、使用#{ }参数传递：

 在UserInfoMapper中编写排序方法：

    // 模糊查询
    @Select("select* from userinfo where username like '%#{partPara}%' ")
    List<UserInfo> selectUserByLike(String partPara);

生成测试方法并对排序结果进行打印：

    @Test
    void selectUserByLike() {
        log.info(userInfoMapper.selectUserByLike("tian").toString());
    }

启动测试类，报错如下： 

可见由于#{ }由于增加引号，也使得模糊查询出现问题。

2、使用${ }参数传递：

  在UserInfoMapper中修改排序方法，使用${ }传递参数：

    // 模糊查询
    @Select("select* from userinfo where username like '%${partPara}%' ")
    List<UserInfo> selectUserByLike(String partPara);

启动测试类，运行成功，查询结果如下：

 3. 使用${ }传参存在的问题

3.1 SQL注入

SQL注入：是指将另外的SQL语句作为参数，执行后修改了原本的SQL语句，从而通过执行代码对服务器进行攻击。

比如：正常SQL如下：以参数为'admin'为例：

select* from userinfo where username= 'admin'

试传参为 'or 1 = ' 1，（使用'or 1 = ' 1直接替换admin），则SQL语句变为：

select* from userinfo where username= ''or 1 = ' 1'

1='1'恒成立，故会导致进行全列查询，获取userinfo整张表的信息。

3.2 对比#{ } 与 ${ }在SQL注入方面存在的问题

1、使用# { }传参：

 在UserInfoMapper文件中创建selectOneByName方法，使用#{ } 传递参数：

package com.zhouyou.mybatisdemo1.mapper;
import com.zhouyou.mybatisdemo1.model.UserInfo;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserInfoMapper {
    @Select("select* from userinfo where username =${username}")
    UserInfo selectOneByName(String name);
}

编写测试类UserInfoMapperTest及测试方法：

package com.zhouyou.mybatisdemo1.mapper;

import com.zhouyou.mybatisdemo1.model.UserInfo;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

import static org.junit.jupiter.api.Assertions.*;
@Slf4j
@SpringBootTest
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void selectOneByName() {
        log.info(userInfoMapper.selectOneByName("'or 1='1 ").toString());
    }
}

运行启动类，可见发生了SQL注入，使得查询到了整张表的信息：

2、使用# { }传参：

修改UserInfoMapper文件中的selectOneByName方法，改为使用#{ }传参：

    @Select("select* from userinfo where username =#{username}")
    List<UserInfo> selectOneByName(String name);

重新启动测试类，查询结果如下：

可见使用#{ }进行参数传递并未发生SQL注入问题；

3.3 预编译SQL与即时SQL

1、对于使用#{ }进行参数传递的SQL语句采取预编译的方式，称为预编译SQL，SQL执行会进行语法解析、SQL优化、SQL编译等步骤，可避免SQL注入的发生；

2、对于使用${ }进行参数传递的SQL语句采取即时的方式，称为即时SQL，直接对参数进行拼接，有出现SQL注入的风险；

可见综合比对，使用#{}进行参数传递是更安全的选择。

3.4 解决模糊查询只能使用${}的问题

为了防止模糊查询使用${ }进行参数传递时导致的SQL注入问题，可以使用mysql的内置函数CONCAT搭配#{ }进行参数传递实现模糊查询：

    @Select("select* from userinfo where username like CONCAT('%',#{partPara},'%')")
    List<UserInfo> selectUserByLike(String partPara);

测试函数传递参数为tian： 

在实际开发中，尽量都使用#{ }进行传递。