拦截器-代码审计

  1. /**
  2.  * 由于开发人员水平参差不齐，即使订了开发规范很多人也不遵守
  3.  * <p>SQL是影响系统性能最重要的因素，所以拦截掉垃圾SQL语句</p>
  4.  * <br>
  5.  * <p>拦截SQL类型的场景</p>
  6.  * <p>1.必须使用到索引，包含left join连接字段，符合索引最左原则</p>
  7.  * <p>必须使用索引好处，</p>
  8.  * <p>1.1 如果因为动态SQL，bug导致update的where条件没有带上，全表更新上万条数据</p>
  9.  * <p>1.2 如果检查到使用了索引，SQL性能基本不会太差</p>
  10.  * <br>
  11.  * <p>2.SQL尽量单表执行，有查询left join的语句，必须在注释里面允许该SQL运行，否则会被拦截，有left join的语句，如果不能拆成单表执行的SQL，请leader商量在做</p>
  12.  * <p>https://gaoxianglong.github.io/shark</p>
  13.  * <p>SQL尽量单表执行的好处</p>
  14.  * <p>2.1 查询条件简单、易于开理解和维护；</p>
  15.  * <p>2.2 扩展性极强；（可为分库分表做准备）</p>
  16.  * <p>2.3 缓存利用率高；</p>
  17.  * <p>2.在字段上使用函数</p>
  18.  * <br>
  19.  * <p>3.where条件为空</p>
  20.  * <p>4.where条件使用了 !=</p>
  21.  * <p>5.where条件使用了 not 关键字</p>
  22.  * <p>6.where条件使用了 or 关键字</p>
  23.  * <p>7.where条件使用了 使用子查询</p>
  24.  *
  25.  * @author willenfoo
  26.  * @date 2021年7月22日
  27.  * @since 3.4.0
  28.  */
  29. public class IllegalSQLInterceptor extends JsqlParserSupport implements InnerInterceptor {
  31.     /**
  32.      * 缓存验证结果，提高性能
  33.      */
  34.     private static final Set<String> CACHE_VALID_RESULT = new HashSet<>();
  35.     /**
  36.      * 缓存表的索引信息
  37.      */
  38.     private static final Map<String, List<IndexInfo>> INDEX_INFO_MAP = new ConcurrentHashMap<>();
  40.     @Override
  41.     public void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {
  42.         PluginUtils.MPStatementHandler mpStatementHandler = PluginUtils.mpStatementHandler(sh);
  43.         MappedStatement ms = mpStatementHandler.mappedStatement();
  44.         SqlCommandType sct = ms.getSqlCommandType();
  45.         if (sct == SqlCommandType.INSERT || InterceptorIgnoreHelper.willIgnoreIllegalSql(ms.getId())) {
  46.             return;
  47.         }
  48.         BoundSql boundSql = mpStatementHandler.boundSql();
  49.         String originalSql = boundSql.getSql();
  50.         logger.debug("检查SQL是否合规，SQL:" + originalSql);
  51.         String md5Base64 = EncryptUtils.md5Base64(originalSql);
  52.         if (CACHE_VALID_RESULT.contains(md5Base64)) {
  53.             logger.debug("该SQL已验证，无需再次验证，，SQL:" + originalSql);
  54.             return;
  55.         }
  57.         parserSingle(originalSql, connection);
  58.         //缓存验证结果
  59.         CACHE_VALID_RESULT.add(md5Base64);
  60.     }
  62.     @Override
  63.     protected void processSelect(Select select, int index, String sql, Object obj) {
  64.         PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
  65.         // TODO jialei 2021年9月26日19点52分 这个先过滤分页插件对特殊语句包装子查询检测
  66.         if (sql.endsWith("TOTAL")) {
  67.             return;
  68.         }
  69.         Expression where = plainSelect.getWhere();
  70.         Assert.notNull(where, "非法SQL，必须要有where条件");
  71.         Table table = (Table) plainSelect.getFromItem();
  72.         List<Join> joins = plainSelect.getJoins();
  73.         validWhere(where, table, (Connection) obj);
  74.         validJoins(joins, table, (Connection) obj);
  75.     }
  77.     @Override
  78.     protected void processUpdate(Update update, int index, String sql, Object obj) {
  79.         Expression where = update.getWhere();
  80.         Assert.notNull(where, "非法SQL，必须要有where条件");
  81.         Table table = update.getTable();
  82.         List<Join> joins = update.getJoins();
  83.         validWhere(where, table, (Connection) obj);
  84.         validJoins(joins, table, (Connection) obj);
  85.     }
  87.     @Override
  88.     protected void processDelete(Delete delete, int index, String sql, Object obj) {
  89.         Expression where = delete.getWhere();
  90.         Assert.notNull(where, "非法SQL，必须要有where条件");
  91.         Table table = delete.getTable();
  92.         List<Join> joins = delete.getJoins();
  93.         validWhere(where, table, (Connection) obj);
  94.         validJoins(joins, table, (Connection) obj);
  95.     }
  97.     /**
  98.      * 验证expression对象是不是 or、not等等
  99.      *
  100.      * @param expression ignore
  101.      */
  102.     private void validExpression(Expression expression) {
  103.         //where条件使用了 or 关键字
  104.         if (expression instanceof OrExpression) {
  105.             OrExpression orExpression = (OrExpression) expression;
  106.             throw new MybatisPlusException("非法SQL，where条件中不能使用【or】关键字，错误or信息：" + orExpression.toString());
  107.         } else if (expression instanceof NotEqualsTo) {
  108.             NotEqualsTo notEqualsTo = (NotEqualsTo) expression;
  109.             throw new MybatisPlusException("非法SQL，where条件中不能使用【!=】关键字，错误!=信息：" + notEqualsTo.toString());
  110.         } else if (expression instanceof BinaryExpression) {
  111.             BinaryExpression binaryExpression = (BinaryExpression) expression;
  112.             if (binaryExpression.getLeftExpression() instanceof Function) {
  113.                 Function function = (Function) binaryExpression.getLeftExpression();
  114.                 throw new MybatisPlusException("非法SQL，where条件中不能使用数据库函数，错误函数信息：" + function.toString());
  115.             }
  116.             if (binaryExpression.getRightExpression() instanceof SubSelect) {
  117.                 SubSelect subSelect = (SubSelect) binaryExpression.getRightExpression();
  118. //                throw new MybatisPlusException("非法SQL，where条件中不能使用子查询，错误子查询SQL信息：" + subSelect.toString());
  119.             }
  120.         } else if (expression instanceof InExpression) {
  121.             InExpression inExpression = (InExpression) expression;
  122.             if (inExpression.getRightItemsList() instanceof SubSelect) {
  123.                 SubSelect subSelect = (SubSelect) inExpression.getRightItemsList();
  124. //                throw new MybatisPlusException("非法SQL，where条件中不能使用子查询，错误子查询SQL信息：" + subSelect.toString());
  125.             }
  126.         }
  128.     }
  130.     /**
  131.      * 如果SQL用了 left Join，验证是否有or、not等等，并且验证是否使用了索引
  132.      *
  133.      * @param joins      ignore
  134.      * @param table      ignore
  135.      * @param connection ignore
  136.      */
  137.     private void validJoins(List<Join> joins, Table table, Connection connection) {
  138.         //允许执行join，验证jion是否使用索引等等
  139.         if (joins != null) {
  140.             if (joins.size() > 1) {
  141.                 throw new MybatisPlusException("非法SQL，超过 2 个表禁止join");
  142.             }
  143.             for (Join join : joins) {
  144.                 Table rightTable = (Table) join.getRightItem();
  145.                 Expression expression = join.getOnExpression();
  146.                 validWhere(expression, table, rightTable, connection);
  147.             }
  148.         }
  149.     }
  151.     /**
  152.      * 检查是否使用索引
  153.      *
  154.      * @param table      ignore
  155.      * @param columnName ignore
  156.      * @param connection ignore
  157.      */
  158.     private void validUseIndex(Table table, String columnName, Connection connection) {
  159.         //是否使用索引
  160.         boolean useIndexFlag = false;
  162.         String tableInfo = table.getName();
  163.         //表存在的索引
  164.         String dbName = null;
  165.         String tableName;
  166.         String[] tableArray = tableInfo.split("\\.");
  167.         if (tableArray.length == 1) {
  168.             tableName = tableArray[0];
  169.         } else {
  170.             dbName = tableArray[0];
  171.             tableName = tableArray[1];
  172.         }
  173.         List<IndexInfo> indexInfos = getIndexInfos(dbName, tableName, connection);
  174.         for (IndexInfo indexInfo : indexInfos) {
  175.             if (null != columnName && columnName.equalsIgnoreCase(indexInfo.getColumnName())) {
  176.                 useIndexFlag = true;
  177.                 break;
  178.             }
  179.         }
  180.         if (!useIndexFlag) {
  181.             throw new MybatisPlusException("非法SQL，SQL未使用到索引, table:" + table + ", columnName:" + columnName);
  182.         }
  183.     }
  185.     /**
  186.      * 验证where条件的字段，是否有not、or等等，并且where的第一个字段，必须使用索引
  187.      *
  188.      * @param expression ignore
  189.      * @param table      ignore
  190.      * @param connection ignore
  191.      */
  192.     private void validWhere(Expression expression, Table table, Connection connection) {
  193.         validWhere(expression, table, null, connection);
  194.     }
  196.     /**
  197.      * 验证where条件的字段，是否有not、or等等，并且where的第一个字段，必须使用索引
  198.      *
  199.      * @param expression ignore
  200.      * @param table      ignore
  201.      * @param joinTable  ignore
  202.      * @param connection ignore
  203.      */
  204.     private void validWhere(Expression expression, Table table, Table joinTable, Connection connection) {
  205.         validExpression(expression);
  206.         if (expression instanceof BinaryExpression) {
  207.             //获得左边表达式
  208.             Expression leftExpression = ((BinaryExpression) expression).getLeftExpression();
  209.             validExpression(leftExpression);
  211.             //如果左边表达式为Column对象，则直接获得列名
  212.             if (leftExpression instanceof Column) {
  213.                 Expression rightExpression = ((BinaryExpression) expression).getRightExpression();
  214.                 if (joinTable != null && rightExpression instanceof Column) {
  215.                     if (Objects.equals(((Column) rightExpression).getTable().getName(), table.getAlias().getName())) {
  216. //                        validUseIndex(table, ((Column) rightExpression).getColumnName(), connection);
  217. //                        validUseIndex(joinTable, ((Column) leftExpression).getColumnName(), connection);
  218.                     } else {
  219. //                        validUseIndex(joinTable, ((Column) rightExpression).getColumnName(), connection);
  220. //                        validUseIndex(table, ((Column) leftExpression).getColumnName(), connection);
  221.                     }
  222.                 } else {
  223.                     //获得列名
  224. //                    validUseIndex(table, ((Column) leftExpression).getColumnName(), connection);
  225.                 }
  226.             }
  227.             // 如果BinaryExpression，进行迭代
  228.             else if (leftExpression instanceof BinaryExpression) {
  229.                 validWhere(leftExpression, table, joinTable, connection);
  230.             }
  232.             //获得右边表达式，并分解
  233.             Expression rightExpression = ((BinaryExpression) expression).getRightExpression();
  234.             validExpression(rightExpression);
  235.         }
  236.     }
  238.     /**
  239.      * 得到表的索引信息
  240.      *
  241.      * @param dbName    ignore
  242.      * @param tableName ignore
  243.      * @param conn      ignore
  244.      * @return ignore
  245.      */
  246.     public List<IndexInfo> getIndexInfos(String dbName, String tableName, Connection conn) {
  247.         return getIndexInfos(null, dbName, tableName, conn);
  248.     }
  250.     /**
  251.      * 得到表的索引信息
  252.      *
  253.      * @param key       ignore
  254.      * @param dbName    ignore
  255.      * @param tableName ignore
  256.      * @param conn      ignore
  257.      * @return ignore
  258.      */
  259.     public List<IndexInfo> getIndexInfos(String key, String dbName, String tableName, Connection conn) {
  260.         List<IndexInfo> indexInfos = null;
  261.         if (StringUtils.isNotBlank(key)) {
  262.             indexInfos = INDEX_INFO_MAP.get(key);
  263.         }
  264.         if (indexInfos == null || indexInfos.isEmpty()) {
  265.             ResultSet rs;
  266.             try {
  267.                 DatabaseMetaData metadata = conn.getMetaData();
  268.                 String catalog = StringUtils.isBlank(dbName) ? conn.getCatalog() : dbName;
  269.                 String schema = StringUtils.isBlank(dbName) ? conn.getSchema() : dbName;
  270.                 rs = metadata.getIndexInfo(catalog, schema, tableName, false, true);
  271.                 indexInfos = new ArrayList<>();
  272.                 while (rs.next()) {
  273.                     //索引中的列序列号等于1，才有效
  274.                     if (Objects.equals(rs.getString(8), "1")) {
  275.                         IndexInfo indexInfo = new IndexInfo();
  276.                         indexInfo.setDbName(rs.getString(1));
  277.                         indexInfo.setTableName(rs.getString(3));
  278.                         indexInfo.setColumnName(rs.getString(9));
  279.                         indexInfos.add(indexInfo);
  280.                     }
  281.                 }
  282.                 if (StringUtils.isNotBlank(key)) {
  283.                     INDEX_INFO_MAP.put(key, indexInfos);
  284.                 }
  285.             } catch (SQLException e) {
  286.                 e.printStackTrace();
  287.             }
  288.         }
  289.         return indexInfos;
  290.     }
  292.     /**
  293.      * 索引对象
  294.      */
  295.     @Data
  296.     private static class IndexInfo {
  298.         /**
  299.          * dbName
  300.          */
  301.         private String dbName;
  303.         /**
  304.          * tableName
  305.          */
  306.         private String tableName;
  308.         /**
  309.          * columnName
  310.          */
  311.         private String columnName;
  312.     }
  313. }

字段自动填充

  1. public class MyMetaObjectHandler implements MetaObjectHandler {
  3.     @Override
  4.     public void insertFill(MetaObject metaObject) {
  5.         // 判断添加/更新的时候是否给他赋值
  6.         Object createDate = getFieldValByName("createDate", metaObject);
  7.         LocalDateTime now = LocalDateTime.now();
  8.         if (createDate == null) {
  9.             this.strictInsertFill(metaObject, "createDate", LocalDateTime.class, now);
  10.         }
  11.         Object updateDate = getFieldValByName("updateDate", metaObject);
  12.         if (updateDate == null) {
  13.             this.strictInsertFill(metaObject, "updateDate", LocalDateTime.class, now);
  14.         }
  15.         Object invalid = getFieldValByName("invalid", metaObject);
  16.         if (invalid == null) {
  17.             this.strictInsertFill(metaObject, "invalid", Integer.class, 0);
  18.         }
  19.         this.strictInsertFill(metaObject, "del", Long.class, 0L);
  20.     }
  22.     @Override
  23.     public void updateFill(MetaObject metaObject) {
  24.         Object updateDate = getFieldValByName("updateDate", metaObject);
  25.         if (updateDate == null) {
  26.             this.strictUpdateFill(metaObject, "updateDate", LocalDateTime.class, LocalDateTime.now());
  27.         }
  28.     }

添加分库建

  1. public enum CustomSqlMethod {
  2.     /**
  3.      *
  4.      */
  5.     SELECT_BY_ID_WITH_DISTRIBUTE_KEY("selectByIdWithDistributeKey", "根据ID和分库键查询一条数据", "SELECT %s FROM %s WHERE %s=#{%s} %s %s"),
  6.     UPDATE_BY_ID_WITH_DISTRIBUTE_KEY("updateByIdWithDistributeKey", "根据ID 选择修改数据", "<script>\nUPDATE %s %s WHERE %s=#{%s} %s\n</script>"),
  7.     ;
  8.     private final String method;
  9.     private final String desc;
  10.     private final String sql;
  12.     CustomSqlMethod(String method, String desc, String sql) {
  13.         this.method = method;
  14.         this.desc = desc;
  15.         this.sql = sql;
  16.     }
  18.     public String getMethod() {
  19.         return this.method;
  20.     }
  22.     public String getDesc() {
  23.         return this.desc;
  24.     }
  26.     public String getSql() {
  27.         return this.sql;
  28.     }
  29. }
  32. /**
  33.  * UpdateByIdWithDistributeKey简介
  34.  *
  35.  * @author jialei25
  36.  * @date 2021-12-09 09:38
  37.  */
  38. public class UpdateByIdWithDistributeKey extends AbstractMethod {
  39.     public UpdateByIdWithDistributeKey() {
  40.     }
  42.     @Override
  43.     public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
  44.         CustomSqlMethod sqlMethod = CustomSqlMethod.UPDATE_BY_ID_WITH_DISTRIBUTE_KEY;
  45.         String additional = this.optlockVersion(tableInfo) + tableInfo.getLogicDeleteSql(true, true);
  47.         String distributionCodeWhereSql = "";
  48.         for (TableFieldInfo tableFieldInfo : tableInfo.getFieldList()) {
  49.             if ("distribution_code".equals(tableFieldInfo.getColumn())) {
  50.                 distributionCodeWhereSql = "distribution_code=#{distributeKey}";
  51.             }
  52.         }
  54.         if (!additional.isEmpty()) {
  55.             distributionCodeWhereSql = additional + " AND " + distributionCodeWhereSql;
  56.         }
  58.         String sql = String.format(sqlMethod.getSql(),
  59.                 tableInfo.getTableName(),
  60.                 this.sqlSet(tableInfo.isWithLogicDelete(), false, tableInfo, false, "et", "et."),
  61.                 tableInfo.getKeyColumn(),
  62.                 tableInfo.getKeyProperty(),
  63.                 distributionCodeWhereSql);
  64.         SqlSource sqlSource = this.languageDriver.createSqlSource(this.configuration, sql, modelClass);
  65.         return this.addUpdateMappedStatement(mapperClass, modelClass, sqlMethod.getMethod(), sqlSource);
  66.     }
  68. }

自定义sql 注入

  1. /**
  2.  * 自定义Sql注入
  3.  *
  4.  * @author nieqiurong 2018/8/11 20:23.
  5.  */
  6. public class MySqlInjector extends AbstractSqlInjector {
  8.     @Override
  9.     public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
  10.         return Stream.of(new Insert(), new Delete(), new DeleteByMap(), new DeleteById(), new DeleteBatchByIds(), new Update(),
  11.                 new UpdateById(), new UpdateByIdWithDistributeKey(), new SelectById(), new SelectByIdWithDistributeKey(), new SelectBatchByIds(), new SelectByMap(), new SelectOne(), new SelectCount(),
  12.                 new SelectMaps(), new SelectMapsPage(), new SelectObjs(), new SelectList(), new SelectPage(), new LogicDeleteByIdWithFill())
  13.                 .collect(Collectors.toList());
  14.     }
  15. }

根据分库键删除

  2. package com.jd.irt.store.core.mybatis;
  4. import com.baomidou.mybatisplus.core.mapper.BaseMapper;
  5. import org.apache.ibatis.annotations.Param;
  7. import java.io.Serializable;
  9. /**
  10.  * MyBaseMapper <br>
  11.  *
  12.  * @author jialei25 <br>
  13.  * @date 2021-09-27 21:03 <br>
  14.  */
  15. public interface MyBaseMapper<T> extends BaseMapper<T> {
  17.     /**
  18.      * 删除
  19.      * @param entity
  20.      * @return
  21.      */
  22.     int deleteByIdWithFill(T entity);
  24.     /**
  25.      * 根据ID和分库键获取对象
  26.      * @param id
  27.      * @param distributeKey
  28.      * @return
  29.      */
  30.     T selectByIdWithDistributeKey(@Param("id") Serializable id, @Param("distributeKey") Serializable distributeKey);
  32.     /**
  33.      * 根据ID分库键更新
  34.      * @param entity
  35.      * @param id
  36.      * @param distributeKey
  37.      * @return
  38.      */
  39.     int updateByIdWithDistributeKey(@Param("et") T entity, @Param("id") Serializable id, @Param("distributeKey") Serializable distributeKey);
  41. }
  43. /*
  44.  * Copyright (c) 2011-2021, baomidou (jobob@qq.com).
  45.  *
  47. /**
  48.  * 根据 id 逻辑删除数据,并带字段填充功能
  49.  * <p>注意入参是 entity !!! ,如果字段没有自动填充,就只是单纯的逻辑删除</p>
  50.  * <p>
  51.  * 自己的通用 mapper 如下使用:
  52.  * <pre>
  53.  * int deleteByIdWithFill(T entity);
  54.  * </pre>
  55.  * </p>
  56.  *
  57.  * @author miemie
  58.  * @since 2018-11-09
  59.  */
  60. @SuppressWarnings("serial")
  61. public class LogicDeleteByIdWithFill extends AbstractMethod {
  63.     @Override
  64.     public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
  65.         String sql;
  66.         SqlMethod sqlMethod = SqlMethod.LOGIC_DELETE_BY_ID;
  67.         String distributionCodeWhereSql = "";
  68.         String soleCodeSetSql = "";
  69.         List<TableFieldInfo> updateFieldInfoList = new ArrayList<>();
  70.         for (TableFieldInfo tableFieldInfo : tableInfo.getFieldList()) {
  71.             if (tableFieldInfo.isWithUpdateFill()) {
  72.                 updateFieldInfoList.add(tableFieldInfo);
  73.             }
  74.             if ("distribution_code".equals(tableFieldInfo.getColumn())) {
  75.                 distributionCodeWhereSql = " and distribution_code = #{distributionCode}";
  76.             }
  77.             if ("sole_code".equals(tableFieldInfo.getColumn())) {
  78.                 soleCodeSetSql = "sole_code = uuid(), ";
  79.             }
  80.         }
  81.         if (tableInfo.isWithLogicDelete()) {
  82.             if (CollectionUtils.isNotEmpty(updateFieldInfoList)) {
  83.                 String sqlSet = "SET " + soleCodeSetSql + updateFieldInfoList.stream().map(i -> i.getSqlSet(EMPTY)).collect(joining(EMPTY))
  84.                     + tableInfo.getLogicDeleteSql(false, false);
  85.                 sql = String.format(sqlMethod.getSql(), tableInfo.getTableName(), sqlSet, tableInfo.getKeyColumn(),
  86.                         tableInfo.getKeyProperty(), distributionCodeWhereSql + tableInfo.getLogicDeleteSql(true, true));
  87.             } else {
  88.                 String sqlSet = "SET " + soleCodeSetSql
  89.                         + tableInfo.getLogicDeleteSql(false, false);
  90.                 sql = String.format(sqlMethod.getSql(), tableInfo.getTableName(), sqlSet,
  91.                         tableInfo.getKeyColumn(), tableInfo.getKeyProperty(),
  92.                         distributionCodeWhereSql + tableInfo.getLogicDeleteSql(true, true));
  93.             }
  94.         } else {
  95.             sqlMethod = SqlMethod.DELETE_BY_ID;
  96.             sql = String.format("<script>\nDELETE FROM %s WHERE %s=#{%s} %s\n</script>", tableInfo.getTableName(), tableInfo.getKeyColumn(),
  97.                 tableInfo.getKeyProperty(), distributionCodeWhereSql);
  98.         }
  99.         SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);
  100.         return addUpdateMappedStatement(mapperClass, modelClass, getMethod(sqlMethod), sqlSource);
  101.     }
  103.     @Override
  104.     public String getMethod(SqlMethod sqlMethod) {
  105.         // 自定义 mapper 方法名
  106.         return "deleteByIdWithFill";
  107.     }
  108. }