执行SQL语句有四大主流技术:JDBC、Mybatis、Mybatis Plus以及Hibernate。
零、String 拼接常见方法
常见的拼接String的方法有+
、StringBuilder
以及StringBuffer
,代码示例如下:
String s1 = "Hello" + ", " + "World!";
StringBuilder sb = new StringBuilder();
sb.append("Hello").append(", ").append("World!");
String s3 = sb.toString();
StringBuffer sbf = new StringBuffer();
sbf.append("Hello").append(", ").append("World!");
String s4 = sbf.toString();
一、JDBC
JDBC是早期Java Web实现SQL执行的标准API,它为Java程序访问各种关系型数据库提供了统一的接口。
JDBC有两种执行SQL的方法,分别是PreparedStatement和Statement,其中PreparedStatement会对语句进行预编译处理,直接拼接仍会存在sql注入漏洞
statement.executeQuery(sql); //执行拼接字符串 导致SQL注入
@RequestMapping("/jdbc/vuln")
public String jdbc_sqli_vul(@RequestParam("username") String username) {
StringBuilder result = new StringBuilder();
try {
Class.forName(driver);
Connection con = DriverManager.getConnection(url, user, password);
// sqli vuln code
Statement statement = con.createStatement();
String sql = "select * from users where username = '" + username + "'";
logger.info(sql);
ResultSet rs = statement.executeQuery(sql);
while (rs.next()) {
String res_name = rs.getString("username");
String res_pwd = rs.getString("password");
String info = String.format("%s: %s\n", res_name, res_pwd);
result.append(info);
logger.info(info);
}
rs.close();
con.close();
} catch (ClassNotFoundException e) {
logger.error("Sorry,can`t find the Driver!");
} catch (SQLException e) {
logger.error(e.toString());
}
return result.toString();
}
利用 ? 作为占位符 , 使用PreparedStatement预编译执行SQL 修复SQL注入
@RequestMapping("/jdbc/sec")
public String jdbc_sqli_sec(@RequestParam("username") String username) {
StringBuilder result = new StringBuilder();
try {
Class.forName(driver);
Connection con = DriverManager.getConnection(url, user, password);
if (!con.isClosed())
System.out.println("Connecting to Database successfully.");
// fix code
String sql = "select * from users where username = ?";
PreparedStatement st = con.prepareStatement(sql);
st.setString(1, username);
logger.info(st.toString()); // sql after prepare statement
ResultSet rs = st.executeQuery();
while (rs.next()) {
String res_name = rs.getString("username");
String res_pwd = rs.getString("password");
String info = String.format("%s: %s\n", res_name, res_pwd);
result.append(info);
logger.info(info);
}
rs.close();
con.close();
} catch (ClassNotFoundException e) {
logger.error("Sorry, can`t find the Driver!");
e.printStackTrace();
} catch (SQLException e) {
logger.error(e.toString());
}
return result.toString();
}
- PreparedStatement 未使用
?
占位符 in
语句未使用?
占位符like
语句未使用?
占位符,或没有手动过滤%
和_
导致慢查询 DOSfrom
语句动态表名 未加约束范围select
语句动态列名 未使用预编译ORDER BY
语句未做动态映射LIMIT
二、Mybatis
Mybatis是当下最流行的SQL执行ORM框架之一,SQL写在XML文件或注解中,与 Java 代码分离,便于维护和优化。
若Java项目使用Mybatis框架,需要导入以下依赖
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>x.x.x</version>
</dependency>
Mybatis框架使用XML文件将Mapper类(实体类)与SQL语句映射到一起;
XML文件中namespace绑定Mapper接口,SQL标签的id对应接口方法名,resultType/resultMap定义返回值类型,#{}
或${}
直接映射方法参数。
简单示例如下:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.mybatis.example.BlogMapper">
<select id="selectBlog" resultType="Blog">
select * from Blog where id = #{id}
</select>
</mapper>
Java 接口
package org.mybatis.example;
public interface BlogMapper {
public Blog selectBlog(int id);
}
foreach
、choose
标签。
场景 | 标签 | 功能亮点 |
---|---|---|
条件分支 | <choose>/<when>/<otherwise> |
替代多重 if-else 结构 |
循环迭代 | <foreach> |
安全处理 IN 查询/批量操作 |
非空校验 | <if> |
动态过滤无效条件 |
#{}
为预编译方式执行SQL,${}
为拼接字符串执行SQL,因此若将以上XML文件中的#{}
改为${}
会产生SQL注入
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.mybatis.example.BlogMapper">
<select id="selectBlog" resultType="Blog">
select * from Blog where id = ${id}
</select>
</mapper>
Like语句开发者将通配符%
与用户输入值直接拼接形成完整查询条件,SQL注入代码示例:
<select id="searchUsers" resultType="User">
SELECT * FROM users
WHERE username LIKE '%${keyword}%'
</select>
应该使用CONCAT
函数加预编译方式修复:
<select id="searchUsers" resultType="User">
SELECT * FROM users
WHERE username LIKE CONCAT('%', #{keyword}, '%')
</select>
IN语句直接使用${}
拼接字符串,SQL注入代码示例:
<select id="findUsersByIds" resultType="User">
SELECT * FROM users
WHERE id IN (${ids})
</select>
标签通过 collection
属性指定待遍历的集合,item
定义元素别名,配合 open="("
、close=")"
实现括号闭合,separator=","
设置逗号分隔符,最终安全生成 IN (?#{id}, ?#{id}, ...)
<select id="findUsersByIds" resultType="User">
SELECT * FROM users
WHERE id IN
<foreach collection="idList" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</select>
ORDER BY语句${}
直接拼接列名和排序方式 , SQL注入代码示例:
<select id="getUsersOrdered" resultType="User">
SELECT * FROM users
ORDER BY ${sortField} ${sortOrder}
</select>
使用 <choose>
<select id="getUsersOrdered" resultType="User">
SELECT * FROM users
ORDER BY
<choose>
<when test="sortField == 'name' or sortField == 'email'">
${sortField}
</when>
<otherwise>
id
</otherwise>
</choose>
<choose>
<when test="sortOrder == 'DESC'">
DESC
</when>
<otherwise>
ASC
</otherwise>
</choose>
</select>
LIMIT 语句${}
<select id="getUsersWithLimit" resultType="User">
SELECT * FROM users
LIMIT ${offset}, ${pageSize}
</select>
使用 CAST(value AS UNSIGNED)
强制将参数转为数字,进行修复:
<select id="getUsersWithLimit" resultType="User">
SELECT * FROM users
LIMIT
CAST(#{offset} AS UNSIGNED),
CAST(#{pageSize} AS UNSIGNED)
</select>
${}
<select id="getTableData" resultType="map">
SELECT * FROM ${tableName}
</select>
使用 <choose>
标签对表名进行白名单校验,进行修复:
<select id="getTableData" resultType="map">
SELECT * FROM
<choose>
<when test="tableName == 'users' or tableName == 'products'">
${tableName}
</when>
<otherwise>
audit_log
</otherwise>
</choose>
</select>
三、Mybatis Plus
Mybatis Plus中提供两种API,分别是QueryWrapper
以及LambdaQueryWrapper
;QueryWrapper
为主要的漏洞产生API,主要是因为QueryWrapper
提供了SQL拼接函数.apply()
。
// 攻击者可控输入(来自 HTTP 请求)
String year = request.getParameter("year");
String column = request.getParameter("column");
// 危险!直接拼接用户输入
new QueryWrapper<User>()
.apply("YEAR(create_time) = " + year) // 数值型注入点
.apply("is_valid = 1 AND " + column + " IS NOT NULL") // 字段名注入点
.list();
使用白名单机制以及{}
// 1. 对字段名进行白名单校验
Set<String> safeColumns = Set.of("name", "email", "phone");
String checkedColumn = safeColumns.contains(column) ? column : "user_id";
// 2. 使用 {0} 预编译占位符
new QueryWrapper<User>()
.apply("YEAR(create_time) = {0}", year) // 数值预编译
.apply("is_valid = 1 AND {0} IS NOT NULL", checkedColumn) // 字段名白名单
.list();
四、Hibernate
-
重点检查:
-
所有
.createQuery()
调用 -
@Query
注解内容 -
SQL字符串拼接操作
-
没有回复内容