（面试）Mybatis中的#和$的区别

r囧r小猫 2021-12-03 12:13 301阅读 0赞

## 话不多说，直接进入主题 ##

## 一：下面我们写个关于“\#”的个sql，看能不能注入。 ##

<select id="selectUser" resultMap="BaseResultMap">
        SELECT 
            acc.user_name FROM dfws_sys_user_account AS acc
        WHERE
            acc.user_name like #{userName}
     </select>

### 1.正常传参 ###

DfwsSysUserAccount user = new DfwsSysUserAccount();
    user.setUserName("wanglingzhi");
    List<DfwsSysUserAccount> list = userAccountService.selectUser(user);
    if(list!=null && list.size()>0){
        for (DfwsSysUserAccount u:list) {
            System.out.println("用户名："+u.getUserName());
        }
    }else{
            System.out.println("暂无数据");
    }

![70][]

**sql打印：**

Preparing: SELECT acc.user_name FROM dfws_sys_user_account AS acc WHERE acc.user_name = ? 
    
    Parameters: wanglingzhi(String)

### 2.拼接传参 ###

DfwsSysUserAccount user = new DfwsSysUserAccount();
    user.setUserName("'wanglingzhi' or acc.user_name = 'shuizhong'");
    List<DfwsSysUserAccount> list = userAccountService.selectUser(user);
    if(list!=null && list.size()>0){
        for (DfwsSysUserAccount u:list) {
            System.out.println("用户名："+u.getUserName());
        }
    }else{
            System.out.println("暂无数据");
    }

![70 1][]

**sql打印：**

Preparing: SELECT acc.user_name FROM dfws_sys_user_account AS acc WHERE acc.user_name = ? 
    
    Parameters: wanglingzhi or acc.user_name = shuizhong(String)

## 二：下面我们写个关于“$”的个sql，看能不能注入。 ##

<select id="selectUser" resultMap="BaseResultMap">
        SELECT 
            acc.user_name FROM dfws_sys_user_account AS acc
        WHERE
            acc.user_name like ${userName}
    </select>

###    1.正常传参 ###

DfwsSysUserAccount user = new DfwsSysUserAccount();
    user.setUserName("'wanglingzhi'");
    List<DfwsSysUserAccount> list = userAccountService.selectUser(user);
    if(list!=null && list.size()>0){
        for (DfwsSysUserAccount u:list) {
            System.out.println("用户名："+u.getUserName());
        }
    }else{
            System.out.println("暂无数据");
    }

![70 2][]  
**打印sql：**

SELECT acc.user_name FROM dfws_sys_user_account AS acc WHERE acc.user_name = 'wanglingzhi'

### 2.拼接传参 ###

![70 3][]

**打印sql：**

SELECT acc.user_name FROM dfws_sys_user_account AS acc WHERE acc.user_name = 'wanglingzhi' or acc.user_name = 'shuizhong'

## ##

### 很显然，这里已经sql注入了。 ###

--------------------

# 总结： #

### **\#\{\}** ###

**预编译SQL，类似于JAVA的预编译，例如：**

**String sql = "insert into info(name,age) values(?,?)";**

**PreparedStatement sta = con.prepareStatement(sql);**

**sta.setString(1, “张三”);**

**sta.setInt(2, 10);**

**只不过默认情况下会把参数当做字符串而自动添加一个双引号，例如：order by \#id\#，当传入的值是10时则为order by “10”。**

**凡是用到table、column名字的地方都不能用\#，例如：insert into/select/delete/order by等这些后面是紧跟table名字和column名字的，这些名字是不允许添加引号的。**

**优点在于：防止SQL注入。**

### **$\{\}** ###

**不会预编译，原样输出，不会额外添加任何符号。例如：order by $\{id\}，当传入的值是10时则为order by 10。**

**缺点：可能发生SQL注入问题。**

--------------------

原文：https://blog.csdn.net/qq\_27811247/article/details/80775036

原文：https://blog.csdn.net/attack\_breast/article/details/88039493

[70]: /images/20211203/79bcbebc519841749d4ed80fa12759e1.png
[70 1]: /images/20211203/fdeb594a6bc3458895480ac13e76ac06.png
[70 2]: /images/20211203/cbb893114f534ad3ac94f21a22456006.png
[70 3]: /images/20211203/348de2247f0d4efe8aeb426a25b45230.png