关于使用#和$的使用

#{}和${}有啥区别：

① 含义不一样：${}是直接替换，#{}是预处理；

② 使用场景不一样：普通的参数用#{}，这也是我们日常用的最多的，如果是传递SQL或者SQL关键字，就用${}，但需要做好安全验证

③ 安全性问题：#{}不存在安全问题，使用${}也存在安全问题，如SQL注入

SQL注入指应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者在应用程序实现定义好的查询语句的尾上添加额外的SQL语句，执行SQL成功，获取到非法数据

这里写个demo，演示下SQL注入

xml

    <select id="getUserById"  resultType="org.ongoal.tun.demos.entity.User">
        select * from user_info where user_name = '${username}' and password = '${password}'
    </select>

apifox入参

 控制台打印如下

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@18576e47] was not registered for synchronization because synchronization is not active
JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@440a8ac2] will not be managed by Spring
==>  Preparing: select * from user_info where user_name = 'lisi' and password = '' or 1 = '1'
==> Parameters: 
<==    Columns: uid, user_name, password, manager, create_time, update_time
<==        Row: 3, lisi, 123456, 1, 2024-03-14 17:10:19, 2024-03-14 17:10:24
<==        Row: 1753297296487964673, zhangsan, bf6540446c4348b982f65cbdd1cc4522$a22ef612376787d1d3b697c95aa73be6, 0, null, null
<==        Row: 1753326948434624514, admin, 9bfaf99d54284e78a7de4b5023360ca4$70b4cbc831c70eb4557d5679fcbab939, 1, null, null
<==      Total: 3
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@18576e47]

由此就获取到了数据库所有的数据，因为最后的1=1成立，类似的还有很多

所以鉴于此，一般情况下，使用#{}去代替${}传参。

下面演示下使用使用${}放SQL关键字

serviceImpl

@Override
public List<User> getUserById(String username,String password,Integer sort) {
    String time = "";
    if (1 == sort){
        time = "create_time";
    }
    if (2 == sort){
        time = "update_time";
    }
    return deviceMapper.getUserById(username,password,time);
}

xml

<select id="getUserById"  resultType="org.ongoal.tun.demos.entity.User">
    select * from user_info where user_name = #{username} and password = #{password} order by ${time} desc
</select>

apifox

 控制台

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2468e97c] was not registered for synchronization because synchronization is not active
JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4729a351] will not be managed by Spring
==> Preparing: select * from user_info where user_name = ? and password = ? order by update_time desc  此处不是预处理，是已经直接替换
==> Parameters: lisi(String), 123456(String)
<== Columns: uid, user_name, password, age, manager, create_time, update_time
<== Row: 3, lisi, 123456, 17, 1, 2024-03-14 17:10:19, 2024-03-14 17:10:24
<== Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2468e97c]

不用在乎查询的数据是否为一条，主要看的是order by 后面的替换