MyBatis源码阅读--防注入实现

MyBatis源码阅读-总索引

MyBatis源码阅读–防注入实现

什么是Sql注入

百度百科：Sql注入
简单来讲，一条查询语句如下,userName为用户传入的String参数 ：

public String getSql(String userName){
   return "select * from user where user_name =  " + userName;
}
//获取到的sql就是
"select * from user where user_name =  (userName)";

如果输入参数被用户恶意伪造，使得 userName 传入的字符串为 " ‘xxxx’ or 1 =1",

//获取到的sql就是
"select * from user where user_name =  'xxxx' or 1 =1";

那么此时查询条件永远为真，数据就会被查询出来。

jdbc 中的Statement接口

Statement是jdk sql包下的一个接口，其有三个实现类，Statement ，PreparedStatement，CallableStatement其中CallableStatement是PreparedStatement的子类;

• Statement只能执行静态sql 语句
• PreparedStatement可以动态操作sq语句
• CallableStatement在PreparedStatement的基础上，增加了对存储过程的支持。

例子

Statement访问数据库

 public void testSQLInjection() {
        String username = "a' OR PASSWORD = ";
        String password = " OR '1'='1";

        String sql = "SELECT * FROM users WHERE username = '" + username
                + "' AND " + "password = '" + password + "'";
        System.out.println(sql);
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCTools.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCTools.releaseDB(resultSet, statement, connection);
        }
    }

PreparedStatement访问数据库

public void testSQLInjection2() {
        String username = "a' OR PASSWORD = ";
        String password = " OR '1'='1";
        String sql = "SELECT * FROM users WHERE username = ? "
                + "AND password = ?";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCTools.getConnection();
            
            preparedStatement = connection.prepareStatement(sql);
            //单独设置sql语句占位符中的两个参数
            preparedStatement.setString(1, username);
            preparedStatement.setString(2, password);
            resultSet = preparedStatement.executeQuery();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCTools.releaseDB(resultSet, preparedStatement, connection);
        }
    }

可以看到PreparedStatement的sql语句传入参数都是“？”代替，参数可以动态改变，PreparedStatement会对sql语句进行预编译处理。Statement的sql语句属于字符串拼接，Statement不会对sql语句进行预编译处理，很容易出现上述的注入攻击。

StatementType

org.apache.ibatis.mapping.StatementType定义了三个枚举量，分别对应上述的三种Statement。

public enum StatementType {
    STATEMENT,
    PREPARED,
    CALLABLE;

    private StatementType() {
    }
}

mapper.xml 防注入实现

这是mapper 接口中的方法

   SysUser selectByPrimaryKeyAndName(@Param("id") Long id,@Param("userName") String name);

执行查询语句

 sysUser1 = sysUserMapper.selectByPrimaryKeyAndName(1030L,"liqia");

对应的mapper.xml查询语句

通过设置statementType属性来设置不同的实现类。

PREPARED防注入实现

使用PREPARED，最终使用PreparedStatement类来执行，防注入实现。

 <select id="selectByPrimaryKeyAndName"  resultMap="BaseResultMap"   statementType="PREPARED">
    select id, user_name, user_password, user_email, create_time, user_info, head_img
    from sys_user
    where id = #{id,jdbcType=BIGINT} and  user_name = #{userName,jdbcType=VARCHAR}
  </select>

日志输出

DEBUG [main] - ==>  Preparing: select id, user_name, user_password, user_email, 
create_time, user_info, head_img from sys_user where id = ? and user_name = ? 
DEBUG [main] - ==> Parameters: 1030(Long), liqia(String)
TRACE [main] - <==    Columns: id, user_name, user_password, user_email, 
create_time, user_info, head_img
TRACE [main] - <==        Row: 1030, liqia, 789, Mali@qq.com, 2018-03-14 14:53:11.0, 
<<BLOB>>, <<BLOB>>
DEBUG [main] - <==      Total: 1

使用？类作为占位符，最后再将两个参数传入。

STATEMENT存在注入风险

使用STATEMENT，最终使用Statement类来执行，存在注入攻击的风险实现。

<select id="selectByPrimaryKeyAndName"  resultMap="BaseResultMap"  statementType="STATEMENT">
    select id, user_name, user_password, user_email, create_time, user_info, head_img
    from sys_user
    where id = ${id} and  user_name = '${userName}'
  </select>

日志输出

DEBUG [main] - ==>  Executing: select id, user_name, user_password, user_email, 
create_time, user_info, head_img from sys_user where id = 1030 and user_name = 'liqia' 
TRACE [main] - <==    Columns: id, user_name, user_password,
user_email, create_time, user_info, head_img
TRACE [main] - <==        Row: 1030, liqia, 789, Mali@qq.com, 2018-03-14 14:53:11.0,
 <<BLOB>>, <<BLOB>>
DEBUG [main] - <==      Total: 1

没有站位符，直接将Sql语句和参数拼接。

注意两段日志中的不同之处：

//PREPARED
 where id = ? and user_name = ?
 //STATEMENT
 where id = 1030 and user_name = 'liqia' 

注意：
statementType未指定的情况下，默认是“PREPARED”。
需要注意的是

1. PREPARED时，占位符使用"#"。STATEMENT时，占位符使用"$",否则会出现异常。
2. 由于传入的userName是字符串类型，STATEMENT时，${userName}外边需要家引号。
3. STATEMENT时，相当字符串拼接，因此不能再使用jdbcType=BIGINT,jdbcType=VARCHAR等。

什么情况下会出现注入风险

上面的两个例子并不会出现注入攻击风险。
因为上述例子中，’${userName}’ ，userName已经使用单引号包围，不管你的输入参数是什么，都会被解析成一个查询字符串。
比如输入是" xxxx or 1 =1"
sql语句是

select id, user_name, user_password, user_email, create_time,
 user_info, head_img from sys_user where id = 1030 and user_name = 'xxxx or 1 =1' 

当没有单引号时，才会出现注入攻击风险。

<select id="selectByPrimaryKeyAndName"  resultMap="BaseResultMap"  statementType="STATEMENT">
    select id, user_name, user_password, user_email, create_time, user_info, head_img
    from sys_user
    where id = ${id} and  user_name = ${userName}
  </select>

执行查询语句,注意 xxx使用了单引号包围。

 sysUser1 = sysUserMapper.selectByPrimaryKeyAndName(1030L," 'xxx' or 1=1");

输出，注意此时的查询语句：select id, user_name, user_password, user_email, create_time, user_info, head_img from sys_user where id = 1030 and user_name = ‘xxxx’ OR 1=1

DEBUG [main] - ==>  Executing: select id, user_name, user_password, user_email, 
create_time, user_info, head_img from sys_user where id = 1030 
and user_name = 'xxxx' OR 1=1 
TRACE [main] - <==    Columns: id, user_name, user_password, user_email,
 create_time, user_info, head_img
TRACE [main] - <==        Row: 1, admin, 123456, admin@mybatis.tk, 
2018-03-03 11:00:00.0, <<BLOB>>, <<BLOB>>
TRACE [main] - <==        Row: 1030, liqia, 789, Mali@qq.com, 
2018-03-14 14:53:11.0, <<BLOB>>, <<BLOB>>
TRACE [main] - <==        Row: 1054, null, null, null, 2018-09-30 17:16:26.0, 
<<BLOB>>, <<BLOB>>
Exception in thread "main" org.apache.ibatis.exceptions.TooManyResultsException: 
Expected one result (or null) to be returned by selectOne(), but found: 3
DEBUG [main] - <==      Total: 3

我们的数据库中并没有一条数据的user_name是输入参数（" ‘xxx’ or 1=1"），但所有的数据都被查询出来，这就是注入攻击。