MyBatis中的#{} 和 ${}

目录

#{} 和 ${}

预编译 SQL 和 即时 SQL

SQL注入

${}的使用

---

#{} 和 ${}的使用

MyBatis参数赋值有两种方式，在上一篇文章中，一直使用 #{} 进行赋值，接下来，我们来使用 ${} 进行赋值，并观察 #{} 和 ${} 的区别

使用#{}进行赋值：

    @Select("select * from userinfo where id = #{id}")
    public UserInfo selectById(Integer id);

测试并观察结果：

    @Test
    void selectById() {
        System.out.println(userInfoMapper.selectById(2));
    }

 使用 ${} 进行赋值

    @Select("select * from userinfo where id = ${id}")
    public UserInfo selectById2(Integer id);

测试并观察结果：

    @Test
    void selectById2() {
        System.out.println(userInfoMapper.selectById2(2));
    }

 对比两次运行的结果：

我们可以发现：当我们使用 #{} 时，输入的参数并未在后面之间拼接，而是使用 ? 进行占位，这种SQL称之为"预编译SQL"，而当使用 ${} 时，输入的参数直接拼接在后面

当我们传递 String 类型的参数时

    @Select("select * from userinfo where username = #{username}")
    public List<UserInfo> selectByUsername(String username);
    
    @Select("select * from userinfo where username = ${username}")
    public List<UserInfo> selectByUsername2(String username);

分别进行测试：

    @Test
    void selectByUsername() {
        System.out.println(userInfoMapper.selectByUsername("zhangsan"));
    }

    @Test
    void selectByUsername2() {
        System.out.println(userInfoMapper.selectByUsername2("zhangsan"));
    }

 观察结果：

我们可以发现：

 当使用 ${} 时，由于参数直接拼接在SQL语句中，而字符串作为参数时需要添加 ''，而 ${} 不会拼接''，因此程序报错

我们可以修改代码：

    @Select("select * from userinfo where username = '${username}'")
    public List<UserInfo> selectByUsername2(String username);

 此次成功返回结果

通过上述的对比分析，我们可以发现：

#{} 使用的是预编译SQL，通过 ? 占位的方式，提取对SQL进行编译，然后将参数填充到 SQL 语句中，且 #{} 会通过参数类型，自动拼接引号 ''

${} 会直接进行字符串替换，然后再一起对 SQL 进行编译，当参数为字符串时，需要添加上 ''

（参数为数字类型时也可以加，查询结果不变，但可能导致索引失效，性能下降）

#{} 和 ${} 的区别就是 预编译SQL 和 即时SQL 的区别

预编译 SQL 和 即时 SQL

预编译 SQL（Prepared SQL）：

在预编译阶段，数据库管理系统（DBMS）会对 SQL 语句进行编译，生成执行计划，并将其存储在数据库中，而不是在每次执行时重新编译。

在执行阶段，应用程序发送带有占位符的预编译 SQL 语句给 DBMS，DBMS 根据预编译的执行计划执行 SQL，并将实际参数传递给占位符，以生成最终的查询结果。

即时 SQL（Immediate SQL） ：

即时 SQL 是指每次执行 SQL 语句时，数据库管理系统都会即时地进行语法分析、语义分析、优化和执行，而不是提前进行编译和优化。

每次执行即时 SQL 语句都会有一定的性能开销，因为需要进行完整的编译和优化过程。

当服务器接收到一条 SQL 语句后：

先解析语法和语义，校验 SQL 语句是否正确

再优化 SQL 制定执行计划

执行并返回结果

一条 SQL 若执行上述流程，我们就称为 Immediate Statements(即时SQL)

而预编译 SQL，则会在编译一次之后将编译后的 SQL 语句缓存起来，后面再执行这条语句时，便不会再进行编译，省去了解析优化等过程

预编译 SQL 可以提高执行效率，因为 SQL 语句只需编译一次，然后可以多次执行，避免了重复编译的开销。预编译 SQL 通常用于需要频繁执行的 SQL 语句，如重复执行的查询或更新操作

即时 SQL 通常用于不经常执行或每次执行的 SQL 语句

即，预编译 SQL 可以提高执行效率和性能，适用于重复执行的 SQL 语句，而即时 SQL 则更灵活，适用于临时性或不经常执行的 SQL 操作。

因此，相比于使用 ${}，#{} 的性能更高，且 ${} 会有SQL注入的风险

SQL注入

SQL注入：通过操作输入的数据来修改事先定义好的SQL语句，以达到执行代码对服务器进行攻击的方法。

当使用 ${} 时，由于没有对用户输入进行充分检查，而SQL又是拼接而成的，在用户输入参数时，在参数中添加一些 SQL关键字，达到改变SQL运行结果的目的，也可以完成恶意攻击。

我们以 selectByUsername() 来进一步理解 SQL 注入：

当我们传递参数：

    @Test
    void selectByUsername2() {
        System.out.println(userInfoMapper.selectByUsername2("' or 1='1"));
    }

测试结果：

此时参数 or 被当作 SQL语句的一部分， 查询条件变为 username = ' ' or 1 = '1'，1 = ‘1’ 恒成立，因此 查询出所有数据

我们再以用户登录的例子来看：

UserController：

@RestController
@RequestMapping("/user")
public class UserController {
    @Autowired private UserService userService;
    @RequestMapping("/login")
    public boolean login(String username, String password){
        //参数校验
        if(!StringUtils.hasLength(username)
                || !StringUtils.hasLength(password)){
            return false;
        }
        UserInfo userInfo = userService.selectUserByPassword(username, password);
        if(userInfo == null){
            return false;
        }
        return true;
    }
}

UserService：

@Service
public class UserService {
    @Autowired
    private UserInfoMapper userInfoMapper;
    public UserInfo selectUserByPassword(String username, String password) {
        List<UserInfo> userInfos = userInfoMapper.selectUserByPassword(username, password);
        System.out.println(userInfos);
        if(userInfos != null && userInfos.size() > 0){
            return userInfos.get(0);
        }
        return null;
    }
}

UserInfoMapper：

@Mapper
public interface UserInfoMapper {
    @Select("select * from userinfo where username = '${username}' and password = '${password}'")
    List<UserInfo> selectUserByPassword(String username, String password);
}

若在用户登录时，输入密码 ' or 1 = '1，也就有可能完成登录

运行并访问：

http://127.0.0.1:8080/user/login?username=zhangsan&password= ' or 1 = '1

结果：

此时虽然密码错误，但是仍能登录

SQL注入是一种常见的数据库攻击手段，SQL注入漏洞也是网络世界中最为普遍的漏洞之一

${} 会有 SQL 注入的风险，所以我们尽量使用 #{} 进行查询，

既然已经有 #{} ，那 ${} 是否就没必要使用了呢？

当然不是，在一些特定的场景下，${} 能够完成

${}的使用

当我们需要按照升序或是降序对查询的数据进行排序时：

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

测试：

    @Test
    void selectAllUserBySort() {
        userInfoMapper.selectAllUserBySort("desc");
    }

而当我们使用 #{}

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

由于 参数类型为 String，desc 被自动加上了 ''，因此导致 sql 错误

 同理，当我们使用 like 查询时：

    @Select("select * from userinfo where username like '%${key}%'")
    List<UserInfo> selectAllUserByLike(String key);

测试：

    @Test
    void selectAllUserByLike() {
        userInfoMapper.selectAllUserByLike("zhang");
    }

而当我们使用 #{} 时：

使用 #{} 时会报错，我们可以通过 mysql的内置函数 concat() 进行拼接，从而解决问题：

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

由于 ${} 存在 SQL注入的问题，因此，在能够使用 #{} 的情况下，我们尽可能选择使用 #{}，而在需要使用 ${} 时，我们需要对用户输入的数据进行验证，确保其符合预期的格式和范围