MyBatis Generator生成的$ sql是否存在注入风险详解

目录

代理商sql注入问题排查准备测试demoentityproduct.javaproductexample.java控制层productcontroller.javarvice层productrvice.javaproductrviceimpl.javamapperproductcontroller.javaproductcontroller.xml测试测试1：正常逻辑测试测试2：测试不存在的表字段测试3：like注入测试1测试3：like注入测试2结论附录

代理商sql注入问题排查

经全面排查，代理商中sql层使用’$’获取对象的只有一种类型，代码格式如下：

<sql id="example_where_clau">   <!-- warning - @mbggenerated this element is automatically generated by       mybatis generator, do not modify. -->  <where>      <foreach collection="oredcriteria" item="criteria" parator="or">         <if test="criteria.valid">            <trim prefix="(" suffix=")" prefixoverrides="and">               <foreach collection="criteria.criteria" item="criterion">                  <choo>                     <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.condvalue}                     </when>                     <when test="criterion.listvalue">                        and ${criterion.condition}                        <foreach collection="criterion.value" item="listitem"  open="(" clo=")" parator=",">                           #{listitem}                        </foreach>                     </when>                  </choo>               </foreach>            </trim>         </if>      </foreach>   </where></sql>

接下来我们在测试demo中复现如何设置ip地址下情况：

准备测试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 databa table cpt_dls_config * * @mbggenerated*/protected string orderbyclau;/*** this field was generated by mybatis generator. * this field corresponds to the databa table cpt_dls_config * * @mbggenerated*/protected boolean distinct;/*** this field was generated by mybatis generator. * this field corresponds to the databa table cpt_dls_config * * @mbggenerated*/protected list<criteria> oredcriteria;/*** this method was generated by mybatis generator. * this method corresponds to the databa table cpt_dls_config * * @mbggenerated*/public productexample() {oredcriteria = new arraylist<criteria>();}/*** this method was generated by mybatis generator. * this method corresponds to the databa table cpt_dls_config * * @mbggenerated*/public void torderbyclau(string orderbyclau) {this.orderbyclau = orderbyclau;}/*** this method was generated by mybatis generator. * this method corresponds to the databa table cpt_dls_config * * @mbggenerated*/public string getorderbyclau() {return orderbyclau;}/*** this method was generated by mybatis generator. * this method corresponds to the databa table cpt_dls_config * * @mbggenerated*/public void tdistinct(boolean distinct) {this.distinct = distinct;}/*** this method was generated by mybatis generator. * this method corresponds to the databa table cpt_dls_config * * @mbggenerated*/public boolean isdistinct() {return distinct;}/*** this method was generated by mybatis generator. * this method corresponds to the databa table cpt_dls_config * * @mbggenerated*/public list<criteria> getoredcriteria() {return oredcriteria;}/*** this method was generated by mybatis generator. * this method corresponds to the databa 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 databa 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 databa table cpt_dls_config * * @mbggenerated*/publ学生学习机ic 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 databa 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 databa table cpt_dls_config * * @mbggenerated*/public void clear() {oredcriteria.clear();orderbyclau = null;distinct = fal;}/*** this class was generated by mybatis generator. * this class corresponds to the databa 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 databa 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 databa table cpt_dls_config * * @mbggenerated*/public static class criterion {private string condition;private object value;private object condvalue;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 getcondvalue() {return condvalue;}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;} el {this.singlevalue = true;}}protected criterion(string condition, object value) {this(condition, value, null);}protected criterion(string condition, object value,一路向西3 object condvalue, string typehandler) {super();this.condition = condition;this.value = value;this.condvalue = condvalue;this.typehandler = typehandler;this.betweenvalue = true;}protected criterion(string condition, object value, object condvalue) {this(condition, value, condvalue, null);}}}

控制层productcontroller.java

package com.zhrb.springcloud.controller;import com.zhrb.springcloud.entity.product;import com.zhrb.springcloud.entity.productexample;import com.zhrb.springcloud.rvice.productrvice;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 {@autowiredprivate productrvice productrvice;@apioperation(value="测试是否预编译", notes="测试是否预编译")@getmapping(value = "/testlist")public list<product> testlist() {productexample example = new productexample();example.createcriteria().andidlike("1' or '1=1");list<product> productlist = productrvice.list(example);for (product p :productlist){p.tproductname(p.getproductname()+"本条数据来自8001");}return productlist;}}

rvice层

productrvice.java

package com.zhrb.springcloud.rvice;import com.zhrb.springcloud.entity.product;import com.zhrb.springcloud.entity.productexample;import java.util.list;/*** @classname productrvice* @description todo* @author administrator* @date 2019/9/3 15:15* @version*/ public interface productrvice {list<product> list(productexample example);}

productrviceimpl.java

package com.zhrb.springcloud.rvice.impl;import com.zhrb.springcloud.entity.product;import com.zhrb.springcloud.entity.productexample;import com.zhrb.springcloud.mapper.productmapper;import com.zhrb.springcloud.rvice.productrvice;import org.springframework.beans.factory.annotation.autowired;import org.springframework.stereotype.rvice;import java.util.list;/*** @classname productrviceimpl* @description todo* @author administrator* @date 2019/9/3 15:16* @version*/   @rvice public class productrviceimpl implements productrvice{@autowiredprivate productmapper productmapper;@overridepublic 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"><lect id="testlist" parametertype="com.zhrb.springcloud.entity.productexample" resulttype="com.zhrb.springcloud.entity.product">lectpid, product_name, db_sourcefrom product<if test="_parameter != null" ><include refid="example_where_clau" /></if><if test="orderbyclau != null" >order by ${orderbyclau}</if></lect><sql id="example_where_clau" ><!--warning - @mbggenerated this element is automatically generated by mybatis generator, do not modify. -->  <where ><foreach collection="oredcriteria" item="criteria" parator="or" ><if test="criteria.valid" ><trim prefix="(" suffix=")" prefixoverrides="and" ><foreach collection="criteria.criteria" item="criterion" ><choo ><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.condvalue}</when><when test="criterion.listvalue" >and ${criterion.condition}<foreach collection="criterion.value" item="listitem" open="(" clo=")" parator="," >#{listitem}</foreach></when></choo></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 databa table cpt_dls_config * * @mbggenerated*/protected string orderbyclau;/*** this field was generated by mybatis generator. * this field corresponds to the databa table cpt_dls_config * * @mbggenerated*/protected boolean distinct;/*** this field was generated by mybatis generator. * this field corresponds to the databa table cpt_dls_config * * @mbggenerated*/protected list<criteria> oredcriteria;/*** this method was generated by mybatis generator. * this method 青春的征途corresponds to the databa table cpt_dls_config * * @mbggenerated*/public productexample() {oredcriteria = new arraylist<criteria>();}/*** this method was generated by mybatis generator. * this method corresponds to the databa table cpt_dls_config * * @mbggenerated*/public void torderbyclau(string orderbyclau) {this.orderbyclau = orderbyclau;}/*** this method was generated by mybatis generator. * this method corresponds to the databa table cpt_dls_config * * @mbggenerated*/public string getorderbyclau() {return orderbyclau;}/*** this method was generated by mybatis generator. * this method corresponds to the databa table cpt_dls_config * * @mbggenerated*/public void tdistinct(boolean distinct) {this.distinct = distinct;}/*** this method was generated by mybatis generator. * this method corresponds to the databa table cpt_dls_config * * @mbggenerated*/public boolean isdistinct() {return distinct;}/*** this method was generated by mybatis generator. * this method corresponds to the databa table cpt_dls_config * * @mbggenerated*/public list<criteria> getoredcriteria() {return oredcriteria;}/*** this method was generated by mybatis generator. * this method corresponds to the databa 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 databa 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 databa 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 databa 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 databa table cpt_dls_config * * @mbggenerated*/public void clear() {oredcriteria.clear();orderbyclau = null;distinct = fal;}/*** this class was generated by mybatis generator. * this class corresponds to the databa 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 databa 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 databa table cpt_dls_config * * @mbggenerated*/public static class criterion {private string condition;private object value;private object condvalue;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 getcondvalue() {return condvalue;}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;} el {this.singlevalue = t关于天气的农谚rue;}}protected criterion(string condition, object value) {this(condition, value, null);}protected criterion(string condition, object value, object condvalue, string typehandler) {super();this.condition = condition;this.value = value;this.condvalue = condvalue;this.typehandler = typehandler;this.betweenvalue = true;}protected criterion(string condition, object value, object condvalue) {this(condition, value, condvalue, null);}}}

测试结果如下：

可以看到，编译出错，证明此时虽然用的是$取值，也经过了预编译，继续看下面。

测试3：like注入测试1

代码及结果截图如下：

从上面的图可以得知：

此种注入，在封装criteria时把传入的参数整体当做一个对象然后传递下去，本次测试如上图1，打了两个断点，但是没执行到第二个断点处即中断执行，后台日志报错，证明此种注入sql有误无法正常执行。

测试3：like注入测试2

代码及结果截图如下：

like注入测试1中我们debug可以看到参数似乎拼接方式有误，那么本次注入即正常注入方式，debug看参数，如果将

andidlike 值设置为：‘1′ or ‘1=1′

数据上执行的sql理论上是：

lect * from product where pid like '1' or '1=1';

在数据库中执行此条sql结果如下：

但是demo执行查询结果为空，并且控制台报错，证明此种注入亦不能注入成功。

结论

经以上demo测试，此种$获取值不会受到sql注入的影响，常规sql注入失败。

附录

数据库表结构：

/*navicat mysql data transfersource rver         : bwg-104.225.147.76source rver version : 50644source host           : 104.225.147.76:3306source databa       : springcloud_db01target rver type    : mysqltarget rver version : 50644file encoding         : 65001date: 2019-09-20 10:23:41*/t 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 chart=utf8;-- ------------------------------ records of product-- ----------------------------inrt into `product` values ('1', '手机', 'springcloud_db01');inrt into `product` values ('2', '冰箱', 'springcloud_db01');inrt into `product` values ('3', '电脑', 'springcloud_db01');inrt into `product` values ('4', '洗衣机', 'springcloud_db01');inrt into `product` values ('5', '电视', 'springcloud_db01');inrt into `product` values ('6', '音响', 'springcloud_db01');

以上为个人经验，希望能给大家一个参考，也希望大家多多支持www.887551.com。