[Java知识库]mybatis中 #{}和${}的区别

1：?#{}和${}的区别

首先配置在UserMapper.xm中配置l两个sql:

<mapper namespace="com.demo.mybatis.UserMapper">
?? ??? ?<select id="selectUser" resultType="com.demo.mybatis.domain.User">
?? ??? ???? ??? ?? ?select * from user where id = #{id}
?? ??? ?</select>
?? ??? ?<select id="selectUserById" timeout="0" resultType="User">
?? ??? ??? ?select * from user where id = ${id}
?? ??? ?</select>
</mapper>

测试代码：

    @Test
    public void dollarAndPound() {
        //         select * from user where id = #{id}
        String selectUser = "com.demo.mybatis.UserMapper.selectUser";
        //  select * from user where id = ${id}
        String selectUserById = "com.demo.mybatis.UserMapper.selectUserById";
        List users1 =  sqlSessionTest.selectList(selectUser,"1 or 1=1 ");
        List users2 =  sqlSessionTest.selectList(selectUserById,"1 or 1=1");
        System.out.println(users1.size());
        System.out.println(users2.size());
    }

测试结果

22:45:13.624 [main] DEBUG com.demo.mybatis.UserMapper.selectUser - ==>  Preparing: select * from user where id = ?
22:45:13.654 [main] DEBUG com.demo.mybatis.UserMapper.selectUser - ==> Parameters: 1 or 1=1 (String)
22:45:13.674 [main] DEBUG com.demo.mybatis.UserMapper.selectUser - <==      Total: 1
after....
before....
22:45:15.194 [main] DEBUG com.demo.mybatis.UserMapper.selectUserById - ==>  Preparing: select * from user where id = 1 or 1=1
22:45:15.194 [main] DEBUG com.demo.mybatis.UserMapper.selectUserById - ==> Parameters: 
22:45:15.194 [main] DEBUG com.demo.mybatis.UserMapper.selectUserById - <==      Total: 6

执行结果截然不同，使用$的sql不能防止sql注入，这是为什么呢？

我们要知道mybatis在处理#和$的sql的语句是怎样的流程。
首先mybatis在解析UserMapper.xml的时候读取所有的sql语句节点，并解析，代码如下
XMLMapperBuilder.java

  private void buildStatementFromContext(List<XNode> list, String requiredDatabaseId) {
    for (XNode context : list) {
      final XMLStatementBuilder statementParser = new XMLStatementBuilder(configuration, builderAssistant, context, requiredDatabaseId);
      try {
        statementParser.parseStatementNode();
      } catch (IncompleteElementException e) {
        configuration.addIncompleteStatement(statementParser);
      }
    }
  }

遍历所得Sql节点，XMLScriptBuilder对sqlNode执行解析生成sqlSource

public SqlSource parseScriptNode() {
    MixedSqlNode rootSqlNode = parseDynamicTags(context);
    SqlSource sqlSource;
    if (isDynamic) {
      sqlSource = new DynamicSqlSource(configuration, rootSqlNode);
    } else {
      sqlSource = new RawSqlSource(configuration, rootSqlNode, parameterType);
    }
    return sqlSource;
  }

这里会判断是否是动态sql,isDynamic的条件如下：
TextSqlNode.java中

  public boolean isDynamic() {
    DynamicCheckerTokenParser checker = new DynamicCheckerTokenParser();
    GenericTokenParser parser = createParser(checker);
    parser.parse(text);
    return checker.isDynamic();
  }

private GenericTokenParser createParser(TokenHandler handler) {? 
   return new GenericTokenParser("${", "}", handler)0;
 }

这里很清楚了，在sql中包含${}的最终会判断为isDynamic的sql
在DynamicSqlSource方法getBoundSql()中rootSqlNode.apply(context)完成sql拼接，
在这里是将参数 1 or 1=1拼接到 select * from user where id = 后面；

  @Override
  public BoundSql getBoundSql(Object parameterObject) {
    DynamicContext context = new DynamicContext(configuration, parameterObject);
    rootSqlNode.apply(context);
    SqlSourceBuilder sqlSourceParser = new SqlSourceBuilder(configuration);
    Class<?> parameterType = parameterObject == null ? Object.class : parameterObject.getClass();
    SqlSource sqlSource = sqlSourceParser.parse(context.getSql(), parameterType, context.getBindings());
    BoundSql boundSql = sqlSource.getBoundSql(parameterObject);
    context.getBindings().forEach(boundSql::setAdditionalParameter);
    return boundSql;
  }

如果是RawSqlSource 中构造方法：

  public RawSqlSource(Configuration configuration, String sql, Class<?> parameterType) {
    SqlSourceBuilder sqlSourceParser = new SqlSourceBuilder(configuration);
    Class<?> clazz = parameterType == null ? Object.class : parameterType;
    sqlSource = sqlSourceParser.parse(sql, clazz, new HashMap<>());
  }

产生SqlSourceBuilder 解析sql的parse()方法中使用ParameterMappingTokenHandler

对象来处理sql

 @Override
    public String handleToken(String content) {
      parameterMappings.add(buildParameterMapping(content));
      return "?";
    }

将#{}部分都用？代替，实现预编译sql。
到这里sql中 #与$的处理梳理完毕。那么既然$会出现sql注入的问题，那么什么场景下可以受那个$动态sql呢？
1 将表名，列名当作参数使用的场景。
select * from ${tableName}；这里就不能使用#了
2 group by的指定字段排序。