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');
以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。
推薦閱讀：
- Mybatis重置Criteria的正確姿勢分享
- idea使用Mybatis逆向工程插件詳情
- mybatis example如何自動生成代碼 排序語句
- Mybatis Plus 逆向工程介紹
- MyBatis中criteria的or(或查詢)語法說明