MyBatis Generator生成的$ sql是否存在注入风险详解
目录
- 代理商sql注入问题排查
- 准备测试demo
- entity
- Product.java
- ProductExample.java
- 控制层ProductController.java
- service层
- ProductService.java
- ProductServiceImpl.java
- mapper
- ProductController.java
- ProductController.xml
- 测试
- 测试1:正常逻辑测试
- 测试2:测试不存在的表字段
- 测试3:like注入测试1
- 测试3:like注入测试2
- 结论
- 附录
代理商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');
以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。
赞 (0)