MyBatis和SQL注入

MyBatis和SQL注入

什么是SQL注入？

在正常的sql里拼上“drop或delete”等危险sql，导致执行正常sql的时候，对数据库数据造成危害。

如何防止SQL注入？

1、尽量避免使用常见的数据库名和数据库结构。SQL注入需要攻击者本身对于数据库的结构有足够的了解才能成功，必须写对sql语句才生效。

2、使用正则表达式等字符串过滤手段限制数据项的格式、字符数目。

MyBatis中的SQL注入

MyBatis传参如果用${}，相当于直接将参数拼接到sql里。

select * from student where name = ${name}; 

等价于：

select * from student where name = "小明"; 

MyBatis传参如果用 #{}，上述sql等价于：

select * from student where name = ?; 

String sql = "SELECT * FROM student WHERE name=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,"小明");

#{}方式传参能够很大程度防止sql注入。

Statement 和 PreparedStatement区别

关系：PreparedStatement继承自Statement,都是接口
区别：PreparedStatement可以使用占位符，是预编译的，批处理比Statement效率高

${}传参sql注入实例1

mapper源文件：

@Select({"select * from employees where emp_no = ${empNo}"})
List<Employees> sqlInjection1(String empNo);

调用方法：

EmployeesMapper employeesMapper = sqlSession.getMapper(EmployeesMapper.class);
employeesMapper.sqlInjection1("'500000' or 1 = 1");

执行结果：

16:56:26.148 [main] DEBUG c.s.m.EmployeesMapper.sqlInjection1 - ==>  Preparing: select * from employees where emp_no = '500000' or 1 = 1 
16:56:26.217 [main] DEBUG c.s.m.EmployeesMapper.sqlInjection1 - ==> Parameters: 
16:56:28.250 [main] DEBUG c.s.m.EmployeesMapper.sqlInjection1 - <==      Total: 300024

可以看到把所有的300024条记录都查出来了。如果将$改成#之后：

mapper源文件：

@Select({"select * from employees where emp_no = #{empNo}"})
List<Employees> sqlInjection1(String empNo);

执行结果是查询出来0条记录。

16:53:26.456 [main] DEBUG c.s.m.EmployeesMapper.sqlInjection1 - ==>  Preparing: select * from employees where emp_no = ? 
16:53:26.490 [main] DEBUG c.s.m.EmployeesMapper.sqlInjection1 - ==> Parameters: '500000' or 1 = 1(String)
16:53:26.551 [main] DEBUG c.s.m.EmployeesMapper.sqlInjection1 - <==      Total: 0

源码分析

public class RoutingStatementHandler implements StatementHandler {

  private final StatementHandler delegate;

  public RoutingStatementHandler(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) {
    switch (ms.getStatementType()) {
      case STATEMENT:
        delegate = new SimpleStatementHandler(executor, ms, parameter, rowBounds, resultHandler, boundSql);
        break;
      case PREPARED:
        delegate = new PreparedStatementHandler(executor, ms, parameter, rowBounds, resultHandler, boundSql);
        break;
      case CALLABLE:
        delegate = new CallableStatementHandler(executor, ms, parameter, rowBounds, resultHandler, boundSql);
        break;
      default:
        throw new ExecutorException("Unknown statement type: " + ms.getStatementType());
    }

  }

1、STATEMENT:直接操作sql，不进行预编译
2、PREPARED:预处理，默认值
3、CALLABLE:执行存储过程

public enum StatementType {
  STATEMENT, PREPARED, CALLABLE
}

其中PreparedStatementHandler中的方法都强制将Statement转为PreparedStatement：

public class PreparedStatementHandler extends BaseStatementHandler {

  public PreparedStatementHandler(Executor executor, MappedStatement mappedStatement, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) {
    super(executor, mappedStatement, parameter, rowBounds, resultHandler, boundSql);
  }

  @Override
  public int update(Statement statement) throws SQLException {
    PreparedStatement ps = (PreparedStatement) statement;
    略
  }

  @Override
  public void batch(Statement statement) throws SQLException {
    PreparedStatement ps = (PreparedStatement) statement;
    略
  }

  @Override
  public <E> List<E> query(Statement statement, ResultHandler resultHandler) throws SQLException {
    PreparedStatement ps = (PreparedStatement) statement;
    略
  }

  @Override
  public <E> Cursor<E> queryCursor(Statement statement) throws SQLException {
    PreparedStatement ps = (PreparedStatement) statement;
    略
  }

RoutingStatementHandler里根据StatementType来判断初始化哪一个StatementHandler接口的实现类。

那么StatementType从哪里来？

Configuration对象

​ mappedStatements属性

​ MappedStatement属性

​ StatementType属性

public class Configuration {
    protected final Map<String, MappedStatement> mappedStatements = new StrictMap<MappedStatement>("Mapped Statements collection")
      .conflictMessageProducer((savedValue, targetValue) ->
          ". please check " + savedValue.getResource() + " and " + targetValue.getResource());

mappedStatements的key是sql的id，value是MappedStatement。如下图：

StatementType属性的值是在下面的地方初始化的：

public final class MappedStatement {
  private StatementType statementType;
    
    public static class Builder {
    private MappedStatement mappedStatement = new MappedStatement();

    public Builder(Configuration configuration, String id, SqlSource sqlSource, SqlCommandType sqlCommandType) {
      mappedStatement.configuration = configuration;
      mappedStatement.id = id;
      mappedStatement.sqlSource = sqlSource;
      mappedStatement.statementType = StatementType.PREPARED;
      略
    }

    略.这里用到了建造者模式。
  }

通过在mapper的xml文件里设置：

<update id="updateByEmpNo" statementType="STATEMENT">
<update id="updateByEmpNo" statementType="PREPARED">

可以指定statementType值，进而决定StatementHandler接口的实现类。

如果statementType使用STATEMENT，那么传参就必须用$，否则会报错。