两者都是sql 语句中指定变量名称的符号,基本使用相同,但是有一点细微的差别。
- 章节1 为源码解释,章节2为测试,不想看源码的直接看结论。
1. 解析 sql 语句的时机
- 两者都是通过指定 openToken("#{" / “${”)、closeToken("}"),来遍历得到的 textSql,执行 parse() 解析
- 获取指定的 expression(如 id),执行 handler.handleToken(expression.toString() 解析 expression,不同的实现类执行不同的操作。
/* 遍历先查找 openToken,再寻找 closeToken,若其中一项找不到结束遍历,都找到执行handleToken,然后继续遍历 */
public String parse(String text) {
if (text == null || text.isEmpty()) {
return "";
}
// search open token
int start = text.indexOf(openToken);
if (start == -1) {
return text;
}
char[] src = text.toCharArray();
int offset = 0;
final StringBuilder builder = new StringBuilder();
StringBuilder expression = null;
while (start > -1) {
if (start > 0 && src[start - 1] == '\\') {
// this open token is escaped. remove the backslash and continue.
builder.append(src, offset, start - offset - 1).append(openToken);
offset = start + openToken.length();
} else {
// found open token. let's search close token.
if (expression == null) {
expression = new StringBuilder();
} else {
expression.setLength(0);
}
builder.append(src, offset, start - offset);
offset = start + openToken.length();
int end = text.indexOf(closeToken, offset);
while (end > -1) {
if (end > offset && src[end - 1] == '\\') {
// this close token is escaped. remove the backslash and continue.
expression.append(src, offset, end - offset - 1).append(closeToken);
offset = end + closeToken.length();
end = text.indexOf(closeToken, offset);
} else {
expression.append(src, offset, end - offset);
break;
}
}
if (end == -1) {
// close token was not found.
builder.append(src, start, src.length - start);
offset = src.length;
} else {
builder.append(handler.handleToken(expression.toString()));
offset = end + closeToken.length();
}
}
start = text.indexOf(openToken, offset);
}
if (offset < src.length) {
builder.append(src, offset, src.length - offset);
}
return builder.toString();
}
- 明白了如何解析 sql 语句后,再来看 handleToken 二者实现的区别,先来看TokenHandler 接口,该接口有四个实现类
public interface TokenHandler {
String handleToken(String content);
}
1.1 BindingTokenParser
- 该类为 TextSqlNode 的静态内部类,这里只看相关的部分方法
- 结合 parse() 可知,${} 是直接将获得的值添加进 sql 表达式,存在 sql注入 的风险
public class TextSqlNode implements SqlNode {
private final String text;
private final Pattern injectionFilter;
/* 创建解析器,可看出是与 ${} 相关联的 */
private GenericTokenParser createParser(TokenHandler handler) {
return new GenericTokenParser("${", "}", handler);
}
private static class BindingTokenParser implements TokenHandler {
private DynamicContext context;
private Pattern injectionFilter;
public BindingTokenParser(DynamicContext context, Pattern injectionFilter) {
this.context = context;
this.injectionFilter = injectionFilter;
}
/* 处理 ${id} 中 id 的方法 */
@Override
public String handleToken(String content) {
/* 获取sql对应的方法的所有参数 */
Object parameter = context.getBindings().get("_parameter");
if (parameter == null) {
context.getBindings().put("value", null);
} else if (SimpleTypeRegistry.isSimpleType(parameter.getClass())) {
/* 判断 parameter 是否为简单类型如 String.class */
context.getBindings().put("value", parameter);
}
/* OGNL语法(我也不懂),暂且看作是获取 content 对应的值 */
Object value = OgnlCache.getValue(content, context.getBindings());
/* 找到返回 value 值,找不到返回 "" 而不是 null */
String srtValue = value == null ? "" : String.valueOf(value); // issue #274 return "" instead of "null"
checkInjection(srtValue);
return srtValue;
}
private void checkInjection(String value) {
if (injectionFilter != null && !injectionFilter.matcher(value).matches()) {
throw new ScriptingException("Invalid input. Please conform to regex" + injectionFilter.pattern());
}
}
}
}
1.2 DynamicCheckerTokenParser
- 该类同样为 TextSqlNode 的静态内部类,直接设置为 动态 sql ,返回 null
private static class DynamicCheckerTokenParser implements TokenHandler {
private boolean isDynamic;
public DynamicCheckerTokenParser() {
// Prevent Synthetic Access
}
public boolean isDynamic() {
return isDynamic;
}
/* 直接设置当前 Parser 的 isDynamic 为 true,并返回 null */
@Override
public String handleToken(String content) {
this.isDynamic = true;
return null;
}
}
1.3 VariableTokenHandler
- 该类是 PropertyParser 的静态内部类,主要用于 xml 配置的属性解析 如 ${username}
public class PropertyParser {
@Override
public String handleToken(String content) {
if (variables != null) {
String key = content;
if (enableDefaultValue) {
final int separatorIndex = content.indexOf(defaultValueSeparator);
String defaultValue = null;
if (separatorIndex >= 0) {
key = content.substring(0, separatorIndex);
defaultValue = content.substring(separatorIndex + defaultValueSeparator.length());
}
if (defaultValue != null) {
return variables.getProperty(key, defaultValue);
}
}
if (variables.containsKey(key)) {
return variables.getProperty(key);
}
}
return "${" + content + "}";
}
}
}
1.4 小总结
- BindingTokenParser 用于解析 ${id} 中的 id,替换为真正的值
- DynamicCheckerTokenParser 如其名,动态检查,主要用于判断该 sql 语句是动态的还是静态的
- VariableTokenHandler 用于解析 xml 配置中的变量,如数据库的 url、username 等。
1.5 ParameterMappingTokenHandler
- 该类是 SqlSourceBuilder 的静态内部类
public class SqlSourceBuilder extends BaseBuilder {
private static final String PARAMETER_PROPERTIES = "javaType,jdbcType,mode,numericScale,resultMap,typeHandler,jdbcTypeName";
public SqlSourceBuilder(Configuration configuration) {
super(configuration);
}
public SqlSource parse(String originalSql, Class<?> parameterType, Map<String, Object> additionalParameters) {
ParameterMappingTokenHandler handler = new ParameterMappingTokenHandler(configuration, parameterType, additionalParameters);
GenericTokenParser parser = new GenericTokenParser("#{", "}", handler);
String sql = parser.parse(originalSql);
return new StaticSqlSource(configuration, sql, handler.getParameterMappings());
}
private static class ParameterMappingTokenHandler extends BaseBuilder implements TokenHandler {
private List<ParameterMapping> parameterMappings = new ArrayList<>();
private Class<?> parameterType;
private MetaObject metaParameters;
/* 处理参数对应,并返回 "?" (回想sql语句执行,?就是一个占位符) */
@Override
public String handleToken(String content) {
parameterMappings.add(buildParameterMapping(content));
return "?";
}
}
}
2. 空口无凭,实践为王
- Mybatis 数据库 user 表数据如下
2.1 ${} 的测试结果
- UserMapper.xml 配置如下
<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- namespace=绑定一个对应的Dao/Mapper接口 -->
<mapper namespace="com.jack.dao.UserMapper">
<!-- 查询操作 -->
<select id="getUserByName" parameterType="String" resultType="com.jack.pojo.User">
select * from mybatis.user where name like ${name}
</select>
</mapper>
- 测试类
public class UserMapperTest {
@Test
public void test() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userByName = userMapper.getUserByName("'cerish01'");
List<User> userByName01 = userMapper.getUserByName("'cerish01' or 1=1");
if(userByName.size() == 0) {
System.out.println("userByName 为 null");
} else {
for (User user : userByName) {
System.out.println(user);
}
}
System.out.println("========== 使用 ${} 被 sql 注入攻击 ==========");
if(userByName01.size() == 0) {
System.out.println("userByName 为 null");
} else {
for (User user : userByName01) {
System.out.println(user);
}
}
sqlSession.close();
}
}
2.2 #{} 的测试结果
- UserMapper.xml 配置如下
<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- namespace=绑定一个对应的Dao/Mapper接口 -->
<mapper namespace="com.jack.dao.UserMapper">
<!-- 查询操作 -->
<select id="getUserByName" parameterType="String" resultType="com.jack.pojo.User">
select * from mybatis.user where name like ${name}
</select>
</mapper>
- 测试类
public class UserMapperTest {
@Test
public void test01() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userByName = userMapper.getUserByName("cerish01");
List<User> userByName01 = userMapper.getUserByName("cerish01 or 1=1");
if(userByName.size() == 0) {
System.out.println("userByName 为 null");
} else {
for (User user : userByName) {
System.out.println(user);
}
}
System.out.println("========== 使用 #{} 防止被 sql 注入攻击 ==========");
if(userByName01.size() == 0) {
System.out.println("userByName 为 null");
} else {
for (User user : userByName01) {
System.out.println(user);
}
}
sqlSession.close();
}
}
- 大功告成,测试完毕
2.3 为什么还要使用 ${} ?
- 来看一个简单例子,
select * from #{tableName}
,解析后的结果为select * from 'user'
,明显不符合我们想要的结果。 - 若换成
select * from ${tableName}
,解析后的结果为select * from user
,这就符合我们想要的结果了。 - 类似 group by、order by 这种不想在变量处加引号的值,则还需要使用 ${} 。即两者缺一不可。