MyBatis Generator生成的$ sql是否存在注入风险详解
代理商sql注入问题排查
经全面排查，代理商中sql层使用'$'获取对象的只有一种类型，代码格式如下：
<sql id="Example_Where_Clause">
<!-- WARNING - @mbggenerated This element is automatically generated by
MyBatis Generator, do not modify. -->
<where>
<foreach collection="oredCriteria" item="criteria" separator="or">
<if test="criteria.valid">
<trim prefix="(" suffix=")" prefixOverrides="and">
<foreach collection="criteria.criteria" item="criterion">
<choose>
<when test="criterion.noValue">
and ${criterion.condition}
</when>
<when test="criterion.singleValue">
and ${criterion.condition} #{criterion.value}
</when>
<when test="criterion.betweenValue">
and ${criterion.condition} #{criterion.value} and
#{criterion.secondValue}
</when>
<when test="criterion.listValue">
and ${criterion.condition}
<foreach collection="criterion.value" item="listItem"
open="(" close=")" separator=",">
#{listItem}
</foreach>
</when>
</choose>
</foreach>
</trim>
</if>
</foreach>
</where>
</sql>
接下来我们在测试demo中复现下情况：
准备测试demo
entity
Product.java
普通实体类，对应数据库中product表，表结构见附录：
package com.zhrb.springcloud.entity;
import lombok.Data;
import lombok.ToString;
/**
* @ClassName Product
* @Description TODO
* @Author Administrator
* @Date 2019/9/3 14:26
* @Version
*/ @Data @ToString public class Product {
//主键
private Long pid;
//产品名称
private String productName;
// 来自哪个数据库，因为微服务架构可以一个服务对应一个数据库，同一个信息被存储到不同数据库
private String dbSource;
}
ProductExample.java
同代理商环境一样的动态条件类：
package com.zhrb.springcloud.entity;
import java.util.ArrayList;
import java.util.List;
/**
* @ClassName ProductExample
* @Description TODO
* @Author Administrator
* @Date 2019/9/20 9:07
* @Version
*/ public class ProductExample {
/**
* This field was generated by MyBatis Generator. * This field corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
protected String orderByClause;
/**
* This field was generated by MyBatis Generator. * This field corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
protected boolean distinct;
/**
* This field was generated by MyBatis Generator. * This field corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
protected List<Criteria> oredCriteria;
/**
* This method was generated by MyBatis Generator. * This method corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
public ProductExample() {
oredCriteria = new ArrayList<Criteria>();
}
/**
* This method was generated by MyBatis Generator. * This method corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
public void setOrderByClause(String orderByClause) {
this.orderByClause = orderByClause;
}
/**
* This method was generated by MyBatis Generator. * This method corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
public String getOrderByClause() {
return orderByClause;
}
/**
* This method was generated by MyBatis Generator. * This method corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
public void setDistinct(boolean distinct) {
this.distinct = distinct;
}
/**
* This method was generated by MyBatis Generator. * This method corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
public boolean isDistinct() {
return distinct;
}
/**
* This method was generated by MyBatis Generator. * This method corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
public List<Criteria> getOredCriteria() {
return oredCriteria;
}
/**
* This method was generated by MyBatis Generator. * This method corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
public void or(Criteria criteria) {
oredCriteria.add(criteria);
}
/**
* This method was generated by MyBatis Generator. * This method corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
public Criteria or() {
Criteria criteria = createCriteriaInternal();
oredCriteria.add(criteria);
return criteria;
}
/**
* This method was generated by MyBatis Generator. * This method corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
public Criteria createCriteria() {
Criteria criteria = createCriteriaInternal();
if (oredCriteria.size() == 0) {
oredCriteria.add(criteria);
}
return criteria;
}
/**
* This method was generated by MyBatis Generator. * This method corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
protected Criteria createCriteriaInternal() {
Criteria criteria = new Criteria();
return criteria;
}
/**
* This method was generated by MyBatis Generator. * This method corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
public void clear() {
oredCriteria.clear();
orderByClause = null;
distinct = false;
}
/**
* This class was generated by MyBatis Generator. * This class corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
protected abstract static class GeneratedCriteria {
protected List<Criterion> criteria;
protected GeneratedCriteria() {
super();
criteria = new ArrayList<Criterion>();
}
public boolean isValid() {
return criteria.size() > 0;
}
public List<Criterion> getAllCriteria() {
return criteria;
}
public List<Criterion> getCriteria() {
return criteria;
}
protected void addCriterion(String condition) {
if (condition == null) {
throw new RuntimeException("Value for condition cannot be null");
}
criteria.add(new Criterion(condition));
}
protected void addCriterion(String condition, Object value, String property) {
if (value == null) {
throw new RuntimeException("Value for " + property + " cannot be null");
}
criteria.add(new Criterion(condition, value));
}
protected void addCriterion(String condition, Object value1, Object value2, String property) {
if (value1 == null || value2 == null) {
throw new RuntimeException("Between values for " + property + " cannot be null");
}
criteria.add(new Criterion(condition, value1, value2));
}
public Criteria andIdIsNull() {
addCriterion("PID is null");
return (Criteria) this;
}
public Criteria andIdIsNotNull() {
addCriterion("PID is not null");
return (Criteria) this;
}
public Criteria andIdEqualTo(String value) {
addCriterion("PID =", value, "pid");
return (Criteria) this;
}
public Criteria andIdNotEqualTo(String value) {
addCriterion("PID <>", value, "pid");
return (Criteria) this;
}
public Criteria andIdGreaterThan(String value) {
addCriterion("PID >", value, "pid");
return (Criteria) this;
}
public Criteria andIdGreaterThanOrEqualTo(String value) {
addCriterion("PID >=", value, "pid");
return (Criteria) this;
}
public Criteria andIdLessThan(String value) {
addCriterion("PID <", value, "pid");
return (Criteria) this;
}
public Criteria andIdLessThanOrEqualTo(String value) {
addCriterion("PID <=", value, "pid");
return (Criteria) this;
}
public Criteria andIdLike(String value) {
addCriterion("PID like", value, "pid");
return (Criteria) this;
}
public Criteria andIdNotLike(String value) {
addCriterion("PID not like", value, "pid");
return (Criteria) this;
}
public Criteria andIdIn(List<String> values) {
addCriterion("PID in", values, "pid");
return (Criteria) this;
}
public Criteria andIdNotIn(List<String> values) {
addCriterion("PID not in", values, "pid");
return (Criteria) this;
}
public Criteria andIdBetween(String value1, String value2) {
addCriterion("PID between", value1, value2, "pid");
return (Criteria) this;
}
public Criteria andIdNotBetween(String value1, String value2) {
addCriterion("PID not between", value1, value2, "pid");
return (Criteria) this;
}
}
/**
* This class was generated by MyBatis Generator. * This class corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated do_not_delete_during_merge
*/ public static class Criteria extends GeneratedCriteria {
protected Criteria() {
super();
}
}
/**
* This class was generated by MyBatis Generator. * This class corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
public static class Criterion {
private String condition;
private Object value;
private Object secondValue;
private boolean noValue;
private boolean singleValue;
private boolean betweenValue;
private boolean listValue;
private String typeHandler;
public String getCondition() {
return condition;
}
public Object getValue() {
return value;
}
public Object getSecondValue() {
return secondValue;
}
public boolean isNoValue() {
return noValue;
}
public boolean isSingleValue() {
return singleValue;
}
public boolean isBetweenValue() {
return betweenValue;
}
public boolean isListValue() {
return listValue;
}
public String getTypeHandler() {
return typeHandler;
}
protected Criterion(String condition) {
super();
this.condition = condition;
this.typeHandler = null;
this.noValue = true;
}
protected Criterion(String condition, Object value, String typeHandler) {
super();
this.condition = condition;
this.value = value;
this.typeHandler = typeHandler;
if (value instanceof List<?>) {
this.listValue = true;
} else {
this.singleValue = true;
}
}
protected Criterion(String condition, Object value) {
this(condition, value, null);
}
protected Criterion(String condition, Object value, Object secondValue, String typeHandler) {
super();
this.condition = condition;
this.value = value;
this.secondValue = secondValue;
this.typeHandler = typeHandler;
this.betweenValue = true;
}
protected Criterion(String condition, Object value, Object secondValue) {
this(condition, value, secondValue, null);
}
}
}
控制层ProductController.java
package com.zhrb.springcloud.controller;
import com.zhrb.springcloud.entity.Product;
import com.zhrb.springcloud.entity.ProductExample;
import com.zhrb.springcloud.service.ProductService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.Collection;
import java.util.List;
/**
* @ClassName ProductController
* @Description TODO
* @Author zhrb
* @Date 2019/9/3 15:18
* @Version
*/ @RestController @RequestMapping("/product")
@MapperScan("com.zhrb.springcloud.mapper")
@Api(value = "/product",description = "商品管理 程序员小圈圈",position = 1)
public class ProductController {
@Autowired
private ProductService productService;
@ApiOperation(value="测试是否预编译", notes="测试是否预编译")
@GetMapping(value = "/testList")
public List<Product> testList() {
ProductExample example = new ProductExample();
example.createCriteria().andIdLike("1' or '1=1");
List<Product> productList = productService.list(example);
for (Product p :productList){
p.setProductName(p.getProductName()+"本条数据来自8001");
}
return productList;
}
}
service层
ProductService.java
package com.zhrb.springcloud.service;
import com.zhrb.springcloud.entity.Product;
import com.zhrb.springcloud.entity.ProductExample;
import java.util.List;
/**
* @ClassName ProductService
* @Description TODO
* @Author Administrator
* @Date 2019/9/3 15:15
* @Version
*/ public interface ProductService {
List<Product> list(ProductExample example);
}
ProductServiceImpl.java
package com.zhrb.springcloud.service.impl;
import com.zhrb.springcloud.entity.Product;
import com.zhrb.springcloud.entity.ProductExample;
import com.zhrb.springcloud.mapper.ProductMapper;
import com.zhrb.springcloud.service.ProductService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* @ClassName ProductServiceImpl
* @Description TODO
* @Author Administrator
* @Date 2019/9/3 15:16
* @Version
*/ @Service public class ProductServiceImpl implements ProductService{
@Autowired
private ProductMapper productMapper;
@Override
public List<Product> list(ProductExample example) {
return productMapper.testList(example);
}
}
mapper
ProductController.java
package com.zhrb.springcloud.mapper;
import com.zhrb.springcloud.entity.Product;
import com.zhrb.springcloud.entity.ProductExample;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
/**
* @ClassName ProductMapper
* @Description TODO
* @Author Administrator
* @Date 2019/9/3 14:55
* @Version
*/
@Mapper
public interface ProductMapper {
List<Product> testList(ProductExample example);
}
ProductController.xml
<?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="com.zhrb.springcloud.mapper.ProductMapper">
<select id="testList" parameterType="com.zhrb.springcloud.entity.ProductExample" resultType="com.zhrb.springcloud.entity.Product">
select
pid, product_name, db_source
from product
<if test="_parameter != null" >
<include refid="Example_Where_Clause" />
</if>
<if test="orderByClause != null" >
order by ${orderByClause}
</if>
</select>
<sql id="Example_Where_Clause" >
<!--
WARNING - @mbggenerated This element is automatically generated by MyBatis Generator, do not modify. --> <where >
<foreach collection="oredCriteria" item="criteria" separator="or" >
<if test="criteria.valid" >
<trim prefix="(" suffix=")" prefixOverrides="and" >
<foreach collection="criteria.criteria" item="criterion" >
<choose >
<when test="criterion.noValue" >
and ${criterion.condition}
</when>
<when test="criterion.singleValue" >
and ${criterion.condition} #{criterion.value}
</when>
<when test="criterion.betweenValue" >
and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
</when>
<when test="criterion.listValue" >
and ${criterion.condition}
<foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
#{listItem}
</foreach>
</when>
</choose>
</foreach>
</trim>
</if>
</foreach>
</where>
</sql>
</mapper>
测试
测试1：正常逻辑测试
首先按照正常代码逻辑测试，校验代码是否成功，测试结果截图如下：
可以看到调用成功，证明代码逻辑没问题，接下来进行异常测试：
测试2：测试不存在的表字段
修改ProductExample.java如下（数据库中字段为pid，无id，故先将pid改为id测试不存在字段编译过程）：
package com.zhrb.springcloud.entity;
import java.util.ArrayList;
import java.util.List;
/**
* @ClassName ProductExample
* @Description TODO
* @Author Administrator
* @Date 2019/9/20 9:07
* @Version
*/ public class ProductExample {
/**
* This field was generated by MyBatis Generator. * This field corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
protected String orderByClause;
/**
* This field was generated by MyBatis Generator. * This field corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
protected boolean distinct;
/**
* This field was generated by MyBatis Generator. * This field corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
protected List<Criteria> oredCriteria;
/**
* This method was generated by MyBatis Generator. * This method corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
public ProductExample() {
oredCriteria = new ArrayList<Criteria>();
}
/**
* This method was generated by MyBatis Generator. * This method corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
public void setOrderByClause(String orderByClause) {
this.orderByClause = orderByClause;
}
/**
* This method was generated by MyBatis Generator. * This method corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
public String getOrderByClause() {
return orderByClause;
}
/**
* This method was generated by MyBatis Generator. * This method corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
public void setDistinct(boolean distinct) {
this.distinct = distinct;
}
/**
* This method was generated by MyBatis Generator. * This method corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
public boolean isDistinct() {
return distinct;
}
/**
* This method was generated by MyBatis Generator. * This method corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
public List<Criteria> getOredCriteria() {
return oredCriteria;
}
/**
* This method was generated by MyBatis Generator. * This method corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
public void or(Criteria criteria) {
oredCriteria.add(criteria);
}
/**
* This method was generated by MyBatis Generator. * This method corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
public Criteria or() {
Criteria criteria = createCriteriaInternal();
oredCriteria.add(criteria);
return criteria;
}
/**
* This method was generated by MyBatis Generator. * This method corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
public Criteria createCriteria() {
Criteria criteria = createCriteriaInternal();
if (oredCriteria.size() == 0) {
oredCriteria.add(criteria);
}
return criteria;
}
/**
* This method was generated by MyBatis Generator. * This method corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
protected Criteria createCriteriaInternal() {
Criteria criteria = new Criteria();
return criteria;
}
/**
* This method was generated by MyBatis Generator. * This method corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
public void clear() {
oredCriteria.clear();
orderByClause = null;
distinct = false;
}
/**
* This class was generated by MyBatis Generator. * This class corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
protected abstract static class GeneratedCriteria {
protected List<Criterion> criteria;
protected GeneratedCriteria() {
super();
criteria = new ArrayList<Criterion>();
}
public boolean isValid() {
return criteria.size() > 0;
}
public List<Criterion> getAllCriteria() {
return criteria;
}
public List<Criterion> getCriteria() {
return criteria;
}
protected void addCriterion(String condition) {
if (condition == null) {
throw new RuntimeException("Value for condition cannot be null");
}
criteria.add(new Criterion(condition));
}
protected void addCriterion(String condition, Object value, String property) {
if (value == null) {
throw new RuntimeException("Value for " + property + " cannot be null");
}
criteria.add(new Criterion(condition, value));
}
protected void addCriterion(String condition, Object value1, Object value2, String property) {
if (value1 == null || value2 == null) {
throw new RuntimeException("Between values for " + property + " cannot be null");
}
criteria.add(new Criterion(condition, value1, value2));
}
public Criteria andIdIsNull() {
addCriterion("id is null");
return (Criteria) this;
}
public Criteria andIdIsNotNull() {
addCriterion("id is not null");
return (Criteria) this;
}
public Criteria andIdEqualTo(String value) {
addCriterion("id =", value, "id");
return (Criteria) this;
}
public Criteria andIdNotEqualTo(String value) {
addCriterion("id <>", value, "id");
return (Criteria) this;
}
public Criteria andIdGreaterThan(String value) {
addCriterion("id >", value, "id");
return (Criteria) this;
}
public Criteria andIdGreaterThanOrEqualTo(String value) {
addCriterion("id >=", value, "id");
return (Criteria) this;
}
public Criteria andIdLessThan(String value) {
addCriterion("id <", value, "id");
return (Criteria) this;
}
public Criteria andIdLessThanOrEqualTo(String value) {
addCriterion("id <=", value, "id");
return (Criteria) this;
}
public Criteria andIdLike(String value) {
addCriterion("id like", value, "id");
return (Criteria) this;
}
public Criteria andIdNotLike(String value) {
addCriterion("id not like", value, "id");
return (Criteria) this;
}
public Criteria andIdIn(List<String> values) {
addCriterion("id in", values, "id");
return (Criteria) this;
}
public Criteria andIdNotIn(List<String> values) {
addCriterion("id not in", values, "id");
return (Criteria) this;
}
public Criteria andIdBetween(String value1, String value2) {
addCriterion("id between", value1, value2, "id");
return (Criteria) this;
}
public Criteria andIdNotBetween(String value1, String value2) {
addCriterion("id not between", value1, value2, "id");
return (Criteria) this;
}
}
/**
* This class was generated by MyBatis Generator. * This class corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated do_not_delete_during_merge
*/ public static class Criteria extends GeneratedCriteria {
protected Criteria() {
super();
}
}
/**
* This class was generated by MyBatis Generator. * This class corresponds to the database table CPT_DLS_CONFIG * * @mbggenerated
*/
public static class Criterion {
private String condition;
private Object value;
private Object secondValue;
private boolean noValue;
private boolean singleValue;
private boolean betweenValue;
private boolean listValue;
private String typeHandler;
public String getCondition() {
return condition;
}
public Object getValue() {
return value;
}
public Object getSecondValue() {
return secondValue;
}
public boolean isNoValue() {
return noValue;
}
public boolean isSingleValue() {
return singleValue;
}
public boolean isBetweenValue() {
return betweenValue;
}
public boolean isListValue() {
return listValue;
}
public String getTypeHandler() {
return typeHandler;
}
protected Criterion(String condition) {
super();
this.condition = condition;
this.typeHandler = null;
this.noValue = true;
}
protected Criterion(String condition, Object value, String typeHandler) {
super();
this.condition = condition;
this.value = value;
this.typeHandler = typeHandler;
if (value instanceof List<?>) {
this.listValue = true;
} else {
this.singleValue = true;
}
}
protected Criterion(String condition, Object value) {
this(condition, value, null);
}
protected Criterion(String condition, Object value, Object secondValue, String typeHandler) {
super();
this.condition = condition;
this.value = value;
this.secondValue = secondValue;
this.typeHandler = typeHandler;
this.betweenValue = true;
}
protected Criterion(String condition, Object value, Object secondValue) {
this(condition, value, secondValue, null);
}
}
}
测试结果如下：
可以看到，编译出错，证明此时虽然用的是$取值，也经过了预编译，继续看下面。
测试3：like注入测试1
代码及结果截图如下：
从上面的图可以得知：
此种注入，在封装Criteria时把传入的参数整体当做一个对象然后传递下去，本次测试如上图1，打了两个断点，但是没执行到第二个断点处即中断执行，后台日志报错，证明此种注入sql有误无法正常执行。
测试3：like注入测试2
代码及结果截图如下：
like注入测试1中我们debug可以看到参数似乎拼接方式有误，那么本次注入即正常注入方式，debug看参数，如果将
andIdLike 值设置为：‘1' or ‘1=1'
数据上执行的sql理论上是：
SELECT * from product WHERE pid LIKE '1' or '1=1';
在数据库中执行此条sql结果如下：
但是demo执行查询结果为空，并且控制台报错，证明此种注入亦不能注入成功。
结论
经以上demo测试，此种$获取值不会受到sql注入的影响，常规sql注入失败。
附录
数据库表结构：
/*
Navicat MySQL Data Transfer
Source Server : BWG-104.225.147.76
Source Server Version : 50644
Source Host : 104.225.147.76:3306
Source Database : springcloud_db01
Target Server Type : MYSQL
Target Server Version : 50644
File Encoding : 65001
Date: 2019-09-20 10:23:41
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
`pid` bigint(20) NOT NULL AUTO_INCREMENT,
`product_name` varchar(50) DEFAULT NULL,
`db_source` varchar(50) DEFAULT NULL,
PRIMARY KEY (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO `product` VALUES ('1', '手机', 'springcloud_db01');
INSERT INTO `product` VALUES ('2', '冰箱', 'springcloud_db01');
INSERT INTO `product` VALUES ('3', '电脑', 'springcloud_db01');
INSERT INTO `product` VALUES ('4', '洗衣机', 'springcloud_db01');
INSERT INTO `product` VALUES ('5', '电视', 'springcloud_db01');
INSERT INTO `product` VALUES ('6', '音响', 'springcloud_db01');
以上为个人经验，希望能给大家一个参考，也希望大家多多支持。