MyBatis Generator生成的$ sql是否存在注入风险详解
程序员文章站
2022-06-17 22:29:42
目录代理商sql注入问题排查准备测试demoentityproduct.javaproductexample.java控制层productcontroller.javaservice层products...
代理商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');
以上为个人经验，希望能给大家一个参考，也希望大家多多支持。