有的时候博客内容会有变动，首发博客是最新的，其他博客地址可能会未同步,认准
https://blog.zysicyj.top
可点击链接
https://blog-1253652709.cos.ap-guangzhou.myqcloud.com//picgo/202401180921373.png解答疑问
目标：通过配置实现SQL查询必须包含指定字段，防止查询全部数据
流程说明
总体流程
graph TD
A[开始] --> B[通过@Bean加载配置]-->C[读取并加载配置文件]-->D[通用mybatis拦截器拦截查询SQL]-->E[解析SQL并于配置规则匹配]-->F[通过全部规则]-->G[放行，走数据库正常查询]-->I[结束]
F-->H[拦截，直接返回空结果]-->I[结束]
解析SQL核心流程
graph TD
A[开始] --> B[解析SQL语句]
B --> C{当前语句是否是查询语句?}
C -- No --> D[抛出IllegalArgumentException异常]
C -- Yes --> E[初始化aliasToTableMap和tableFieldMap]
E --> F[获取表名、别名、字段等]
F --> G{是否包含子查询?}
G -- Yes --> H[递归子查询]
H --> I[获取连接]
G -- No --> I
I --> J[从where中获取字段]
J --> K[返回解析的结果]
classDef default stroke-width:2px;
class A,B,C,D,E,F,G,H,I,J,K default;
操作手册
添加依赖
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>4.3</version>
</dependency>
新增配置
table-interceptor:
tables:
sys_user:
- del_flag
- a,b,dept_id,user_name,user_id
说明：
table-interceptor和tables是固定的，不要动。
tables下面列出需要拦截的表名，值就是要拦截的字段
如果填写多个那么只要命中一个就会通过
如果有多条规则，那么每个规则都会校验，上面的配置表达的就是要校验sysuser表，该表的where必须添加delflag字段，且条件中必须包含a,b,deptid,username,user_id中的一个
修改mybatis-config.xml
新增插件
<plugins>
<plugin interceptor="com.ruoyi.framework.TableFieldInterceptor"/>
</plugins>
新增配置类TableInterceptorProperties
package com.ruoyi.framework;
import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;
import java.util.List;
import java.util.Map;
/**
* 配置类，将读取的配置转为对象处理
*
* @author 朱永胜 <a href = "mailto:zhuyongsheng@xxc99.cn" >公众号：【程序员朱永胜】 个人博客：【blog.zysicyj.top】 </a >
*/
@Data
@Configuration
@ConfigurationProperties(prefix = "table-interceptor")
public class TableInterceptorProperties {
private Map<String, List<String>> tables;
}
新增解析SQL工具类
package com.ruoyi.framework;
import cn.hutool.core.util.IdUtil;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.BinaryExpression;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.relational.*;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SubSelect;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
/**
* 解析SQL工具类，用于解析SQL并封装成容易处理的对象
*
* @author 朱永胜 <a href = "mailto:zhuyongsheng@xxc99.cn" >公众号：【程序员朱永胜】 个人博客：【blog.zysicyj.top】 </a >
*/
@Slf4j
public class SqlParserUtil {
/**
* 解析SQL，获取每个表名及其where子句中的字段
*
* @author 朱永胜<a href = " mailto:zhuyongsheng @ xxc99.cn ">公众号：【程序员朱永胜】 个人博客：【blog.zysicyj.top】 </a >
* @since 2024/6/26
*/
public static SqlDetails parseSql(String sql) throws Exception {
Statement statement = CCJSqlParserUtil.parse(sql);
if (statement instanceof Select) {
PlainSelect plainSelect = (PlainSelect) ((Select) statement).getSelectBody();
Map<String, String> aliasToTableMap = new HashMap<>();
Map<String, Set<String>> tableFieldMap = new HashMap<>();
extractTableAndFields(plainSelect, aliasToTableMap, tableFieldMap);
return new SqlDetails(tableFieldMap);
}
throw new IllegalArgumentException("Unsupported SQL statement");
}
/**
* 递归提取表别名映射和where子句中的字段
*
* @author 朱永胜<a href = " mailto:zhuyongsheng @ xxc99.cn ">公众号：【程序员朱永胜】 个人博客：【blog.zysicyj.top】 </a >
* @since 2024/6/26
*/
private static void extractTableAndFields(PlainSelect plainSelect, Map<String, String> aliasToTableMap, Map<String, Set<String>> tableFieldMap) {
log.debug("==================,{},{},{}", plainSelect, aliasToTableMap, tableFieldMap);
Set<String> currentTables = new HashSet<>();
String uuid = IdUtil.fastSimpleUUID();
// 提取表别名映射
log.debug("提取表名映射");
extractTableAliases(plainSelect, aliasToTableMap, tableFieldMap, currentTables);
// 解析where子句中的字段
log.debug("解析where字段");
if (plainSelect.getWhere() != null) {
extractWhereFields(plainSelect.getWhere(), tableFieldMap, aliasToTableMap, currentTables);
}
// 处理子查询
log.debug("处理子查询");
if (plainSelect.getFromItem() instanceof SubSelect) {
extractSubSelect((SubSelect) plainSelect.getFromItem(), aliasToTableMap, tableFieldMap, currentTables);
}
//处理子连接
log.debug("处理子连接");
if (plainSelect.getJoins() != null) {
for (Join join : plainSelect.getJoins()) {
if (join.getRightItem() instanceof SubSelect) {
extractSubSelect((SubSelect) join.getRightItem(), aliasToTableMap, tableFieldMap, currentTables);
}
}
}
}
/**
* 提取表别名映射
*
* @author 朱永胜<a href = " mailto:zhuyongsheng @ xxc99.cn ">公众号：【程序员朱永胜】 个人博客：【blog.zysicyj.top】 </a >
* @since 2024/6/26
*/
private static void extractTableAliases(PlainSelect plainSelect, Map<String, String> aliasToTableMap, Map<String, Set<String>> tableFieldMap, Set<String> currentTables) {
if (plainSelect.getFromItem() instanceof Table) {
Table fromItem = (Table) plainSelect.getFromItem();
String actualTableName = fromItem.getName();
String alias = fromItem.getAlias() != null ? fromItem.getAlias().getName() : actualTableName;
currentTables.add(actualTableName);
currentTables.add(alias);
aliasToTableMap.put(alias, actualTableName);
tableFieldMap.put(actualTableName, new HashSet<>());
log.debug("提取别名成功{},{}", actualTableName, alias);
}
if (plainSelect.getFromItem() instanceof SubSelect) {
SubSelect fromItem = (SubSelect) plainSelect.getFromItem();
String alias = fromItem.getAlias().getName();
currentTables.add(alias);
aliasToTableMap.put(alias, alias);
tableFieldMap.put(alias, new HashSet<>());
log.debug("提取子句别名成功{},{}", alias, alias);
}
if (plainSelect.getJoins() != null) {
for (Join join : plainSelect.getJoins()) {
if (join.getRightItem() instanceof Table) {
Table joinTable = (Table) join.getRightItem();
String actualTableName = joinTable.getName();
String alias = joinTable.getAlias() != null ? joinTable.getAlias().getName() : actualTableName;
aliasToTableMap.put(alias, actualTableName);
tableFieldMap.put(actualTableName, new HashSet<>());
log.debug("提取join成功{}{}", actualTableName, alias);
}
}
}
}
/**
* 处理子查询
*
* @author 朱永胜<a href = " mailto:zhuyongsheng @ xxc99.cn ">公众号：【程序员朱永胜】 个人博客：【blog.zysicyj.top】 </a >
* @since 2024/6/26
*/
private static void extractSubSelect(SubSelect subSelect, Map<String, String> aliasToTableMap, Map<String, Set<String>> tableFieldMap, Set<String> currentTables) {
if (subSelect.getSelectBody() instanceof PlainSelect) {
PlainSelect subPlainSelect = (PlainSelect) subSelect.getSelectBody();
extractTableAndFields(subPlainSelect, aliasToTableMap, tableFieldMap);
}
}
/**
* 提取where子句中的字段，并将其关联到相应的表
*
* @author 朱永胜<a href = " mailto:zhuyongsheng @ xxc99.cn ">公众号：【程序员朱永胜】 个人博客：【blog.zysicyj.top】 </a >
* @since 2024/6/26
*/
private static void extractWhereFields(Expression expr, Map<String, Set<String>> tableFieldMap, Map<String, String> aliasToTableMap, Set<String> currentTables) {
if (expr instanceof BinaryExpression) {
BinaryExpression binaryExpression = (BinaryExpression) expr;
extractWhereFields(binaryExpression.getLeftExpression(), tableFieldMap, aliasToTableMap, currentTables);
extractWhereFields(binaryExpression.getRightExpression(), tableFieldMap, aliasToTableMap, currentTables);
} else if (expr instanceof Column) {
Column column = (Column) expr;
String tableName = column.getTable() != null ? column.getTable().getName() : null;
log.debug("{},{}", tableName, aliasToTableMap);
if (tableName == null && currentTables.size() == 1) {
// 如果没有显式的表名且当前上下文只有一个表，则推断为该表
tableName = currentTables.iterator().next();
} else {
tableName = aliasToTableMap.getOrDefault(tableName, tableName);
}
if (tableName != null && tableFieldMap.containsKey(tableName)) {
tableFieldMap.get(tableName).add(column.getColumnName());
log.debug("{}", tableFieldMap);
}
log.debug("Added column {} to table {}", column.getColumnName(), tableName);
} else if (expr instanceof InExpression) {
InExpression inExpression = (InExpression) expr;
extractWhereFields(inExpression.getLeftExpression(), tableFieldMap, aliasToTableMap, currentTables);
} else if (expr instanceof IsNullExpression) {
IsNullExpression isNullExpression = (IsNullExpression) expr;
extractWhereFields(isNullExpression.getLeftExpression(), tableFieldMap, aliasToTableMap, currentTables);
} else if (expr instanceof Between) {
Between between = (Between) expr;
extractWhereFields(between.getLeftExpression(), tableFieldMap, aliasToTableMap, currentTables);
} else if (expr instanceof LikeExpression) {
LikeExpression likeExpression = (LikeExpression) expr;
extractWhereFields(likeExpression.getLeftExpression(), tableFieldMap, aliasToTableMap, currentTables);
} else if (expr instanceof ExistsExpression) {
ExistsExpression existsExpression = (ExistsExpression) expr;
extractWhereFields(existsExpression.getRightExpression(), tableFieldMap, aliasToTableMap, currentTables);
}
}
/**
* SQL详情数据类，包含表名及其where子句中的字段
*
* @author 朱永胜<a href = " mailto:zhuyongsheng @ xxc99.cn ">公众号：【程序员朱永胜】 个人博客：【blog.zysicyj.top】 </a >
* @since 2024/6/26
*/
public static class SqlDetails {
private final Map<String, Set<String>> tableFieldMap;
public SqlDetails(Map<String, Set<String>> tableFieldMap) {
this.tableFieldMap = tableFieldMap;
}
public Map<String, Set<String>> getTableFieldMap() {
return tableFieldMap;
}
@Override
public String toString() {
return "TableFieldMap: " + tableFieldMap;
}
}
}
新增拦截器TableFieldInterceptor（核心逻辑）
// 类A
package com.ruoyi.framework;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.StrUtil;
import com.ruoyi.common.exception.ServiceException;
import com.ruoyi.common.utils.spring.SpringUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import java.util.*;
import java.util.concurrent.atomic.AtomicBoolean;
/**
* 拦截器类，用于拦截并检查SQL语句是否包含特定的表和字段
*
* @author 朱永胜 <a href = "mailto:zhuyongsheng@xxc99.cn" >公众号：【程序员朱永胜】 个人博客：【blog.zysicyj.top】 </a >
*/
@Intercepts({
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class})
})
@Slf4j
public class TableFieldInterceptor implements Interceptor {
// 需要匹配的表和字段的配置
private final Map<String, List<String>> tableFieldProperties;
/**
* 构造函数，初始化配置
*
* @author <a href = "mailto:zhuyongsheng@xxc99.cn" >公众号：【程序员朱永胜】 个人博客：【blog.zysicyj.top】 </a >
* @since 2024/6/26
*/
public TableFieldInterceptor() {
// 从Spring容器中获取配置
this.tableFieldProperties = SpringUtils.getBean("tableInterceptorPropertiesValue");
}
/**
* 核心逻辑
*
* @return java.lang.Object 如果拦截成功直接返回默认值，否则继续走拦截链
* @author 朱永胜<a href = " mailto:zhuyongsheng @ xxc99.cn ">公众号：【程序员朱永胜】 个人博客：【blog.zysicyj.top】 </a >
* @since 2024/6/26
*/
@Override
public Object intercept(Invocation invocation) throws Throwable {
// 如果配置为空，直接执行原始方法
if (CollUtil.isEmpty(tableFieldProperties)) {
return invocation.proceed();
}
// 获取MappedStatement和SQL语句
MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
String sql = mappedStatement.getBoundSql(invocation.getArgs()[1]).getSql();
// 解析SQL语句，获取表和字段信息
SqlParserUtil.SqlDetails sqlDetails = SqlParserUtil.parseSql(sql);
log.warn("{}", sqlDetails);
// 日志记录SQL和where子句
log.debug("Original SQL: {}", sql);
// 是否应该返回空结果的标志
AtomicBoolean shouldReturnEmpty = new AtomicBoolean(true);
// 检查SQL中是否包含配置的表和字段
try {
tableFieldProperties.forEach((tableName, conditions) -> {
log.warn("{}{}", tableName, conditions);
if (StrUtil.containsIgnoreCase(sql, tableName)) {
conditions.forEach(condition -> {
String[] fields = condition.split(",");
shouldReturnEmpty.set(false);
Set<String> explainedColumns = sqlDetails.getTableFieldMap().get(tableName);
if (CollUtil.isNotEmpty(explainedColumns) && ArrayUtil.containsAny(explainedColumns.toArray(), fields)) {
shouldReturnEmpty.set(true);
}
if (!shouldReturnEmpty.get()) {
throw new ServiceException("");
}
});
}
});
} catch (Exception ignored) {
}
// 根据检查结果返回空结果或继续执行原始方法
if (!shouldReturnEmpty.get()) {
log.warn("SQL contains sensitive table and field, returning empty result.");
return handleEmptyResult(mappedStatement);
}
return invocation.proceed();
}
/**
* 处理空结果，根据SQL命令类型返回空结果或0
*
* @param mappedStatement 如果是更新操作，直接返回0；如果是查询操作，直接返回空结果
* @return java.lang.Object
* @author 朱永胜<a href = " mailto:zhuyongsheng @ xxc99.cn ">公众号：【程序员朱永胜】 个人博客：【blog.zysicyj.top】 </a >
* @since 2024/6/26
*/
private Object handleEmptyResult(MappedStatement mappedStatement) {
if (mappedStatement.getSqlCommandType().ordinal() == 2) { // 2表示UPDATE命令
return 0; // 对于更新操作，返回0表示没有行受影响
} else {
return new ArrayList<>(); // 对于查询操作，返回空列表
}
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
}
在Application中注册配置
@Resource
private TableInterceptorProperties properties;
@Bean(name = "tableInterceptorPropertiesValue")
public Map<String, List<String>> tableInterceptorPropertiesValue(){
return properties.getTables();
}
本文是原创文章，采用 CC BY-NC-SA 4.0 协议，完整转载请注明来自 小朱
评论
隐私政策
0/500
滚动到此处加载评论...