<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>4.3</version>
</dependency>

<dependency>
    <groupId>com.google.guava</groupId>
    <artifactId>guava</artifactId>
    <version>33.2.1-jre</version>
</dependency>

table-interceptor:

  tables:

    sys_user:

      - del_flag

      - a,b,dept_id,user_name,user_id

    <plugins>

        <plugin interceptor="com.ruoyi.framework.TableFieldInterceptor"/>

    </plugins>

    @Resource
    private  TableInterceptorProperties properties;

    @Bean(name = "tableInterceptorPropertiesValue")
    public  Map<String, List<String>> tableInterceptorPropertiesValue(){
        return properties.getTables();
    }

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;

}

package com.ruoyi.framework;

import cn.hutool.core.util.IdUtil;
import com.google.common.collect.ArrayListMultimap;
import lombok.Data;
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.*;

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) {
            ArrayListMultimap<String, Object> multimap = ArrayListMultimap.create();
            PlainSelect plainSelect = (PlainSelect) ((Select) statement).getSelectBody();
            Map<String, String> aliasToTableMap = new HashMap<>();
            Set<String> currentTables = new HashSet<>();

            extractTableAndFields(plainSelect, multimap);

            return new SqlDetails(multimap);
        }
        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, ArrayListMultimap<String, Object> multimap) {
        log.debug("==================,{}", plainSelect);

        String uuid = IdUtil.fastSimpleUUID();

        // 提取表别名映射
        log.debug("提取表名映射");
        if (plainSelect.getFromItem() instanceof Table) {
            Table fromItem = (Table) plainSelect.getFromItem();
            String actualTableName = fromItem.getName();
            if (plainSelect.getWhere() != null) {
                extractWhereFields(plainSelect.getWhere(), actualTableName, multimap);
            } else {
                multimap.put(actualTableName, new HashSet<>());
            }
        }

        // 解析where子句中的字段
        log.debug("解析where字段{}", multimap);

        // 处理子连接
        if (plainSelect.getFromItem() instanceof SubSelect) {
            SubSelect select = (SubSelect) plainSelect.getFromItem();
            if (select.getSelectBody() instanceof PlainSelect)
                extractTableAndFields((PlainSelect) select.getSelectBody(), multimap);
        }
        for (SelectItem expression : plainSelect.getSelectItems()) {
            if (expression instanceof SelectExpressionItem) {
                if (((SelectExpressionItem) expression).getExpression() instanceof SubSelect) {
                    SubSelect select = (SubSelect) ((SelectExpressionItem) expression).getExpression();
                    extractTableAndFields((PlainSelect) select.getSelectBody(), multimap);
                }
            }
        }
        if (plainSelect.getJoins() != null) {
            for (Join join : plainSelect.getJoins()) {
                if (join.getRightItem() instanceof SubSelect) {
                    SubSelect select = (SubSelect) join.getRightItem();
                    if (select.getSelectBody() instanceof PlainSelect)
                        extractTableAndFields((PlainSelect) select.getSelectBody(), multimap);
                }
            }
        }
    }

    /**
     * 提取where子句中的字段，并将其关联到相应的表
     *
     * @author 朱永胜<a href = " mailto:zhuyongsheng @ xxc99.cn ">公众号：【程序员朱永胜】 个人博客：【blog.zysicyj.top】 </a >
     * @since 2024/6/26
     */
    private static void extractWhereFields(Expression expr, String actualTableName, ArrayListMultimap<String, Object> multimap) {
        if (expr instanceof BinaryExpression) {
            BinaryExpression binaryExpression = (BinaryExpression) expr;
            extractWhereFields(binaryExpression.getLeftExpression(), actualTableName, multimap);
            Expression leftExpression = binaryExpression.getRightExpression();
            if (leftExpression instanceof SubSelect) {
                SubSelect subSelect = (SubSelect) leftExpression;
                extractTableAndFields((PlainSelect) subSelect.getSelectBody(), multimap);
            }
            Expression rightExpression = binaryExpression.getRightExpression();
            if (rightExpression instanceof SubSelect) {
                SubSelect subSelect = (SubSelect) rightExpression;
                extractTableAndFields((PlainSelect) subSelect.getSelectBody(), multimap);
            }
            extractWhereFields(binaryExpression.getRightExpression(), actualTableName, multimap);
        } else if (expr instanceof Column) {
            Column column = (Column) expr;
            String tableName = actualTableName;
            log.debug("{},{}", tableName);
            if (tableName != null) {
                multimap.get(tableName).add(column.getColumnName());
            }
            log.debug("Added column {} to table {}", column.getColumnName(), tableName);
        } else if (expr instanceof InExpression) {
            InExpression inExpression = (InExpression) expr;
            extractWhereFields(inExpression.getLeftExpression(), actualTableName, multimap);
        } else if (expr instanceof IsNullExpression) {
            IsNullExpression isNullExpression = (IsNullExpression) expr;
            extractWhereFields(isNullExpression.getLeftExpression(), actualTableName, multimap);
        } else if (expr instanceof Between) {
            Between between = (Between) expr;
            extractWhereFields(between.getLeftExpression(), actualTableName, multimap);
        } else if (expr instanceof LikeExpression) {
            LikeExpression likeExpression = (LikeExpression) expr;
            extractWhereFields(likeExpression.getLeftExpression(), actualTableName, multimap);
        } else if (expr instanceof ExistsExpression) {
            ExistsExpression existsExpression = (ExistsExpression) expr;
            extractWhereFields(existsExpression.getRightExpression(), actualTableName, multimap);
        }
    }

    /**
     * SQL详情数据类，包含表名及其where子句中的字段
     *
     * @author 朱永胜<a href = " mailto:zhuyongsheng @ xxc99.cn ">公众号：【程序员朱永胜】 个人博客：【blog.zysicyj.top】 </a >
     * @since 2024/6/26
     */
    @Data
    public static class SqlDetails {
        private final ArrayListMultimap<String, Object> tableFieldMap;

        public SqlDetails(ArrayListMultimap<String, Object> tableFieldMap) {
            this.tableFieldMap = tableFieldMap;
        }
    }
}

// 类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;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 拦截器类，用于拦截并检查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");
    }

    public static String preprocessSql(String sql) {
        // 第一步：去掉包含AES_DECRYPT的部分并保留别名
        String regexAES = "AES_DECRYPT\\(UNHEX\\(([^)]+)\\), '([^']+)'\\)(?:\\s+AS\\s+([^,\\s]+))?";
        Pattern patternAES = Pattern.compile(regexAES, Pattern.CASE_INSENSITIVE);
        Matcher matcherAES = patternAES.matcher(sql);

        StringBuffer result = new StringBuffer();
        while (matcherAES.find()) {
            String alias = matcherAES.group(3) != null ? matcherAES.group(3).trim() : "";
            matcherAES.appendReplacement(result, alias.isEmpty() ? "''" : "'' AS " + alias);
        }
        matcherAES.appendTail(result);

        // 第二步：去掉包含CONVERT的部分并保留别名
        String regexConvert = "CONVERT\\(([^)]+)\\s+USING\\s+utf8\\)(?:\\s+AS\\s+([^,\\s]+))?";
        Pattern patternConvert = Pattern.compile(regexConvert, Pattern.CASE_INSENSITIVE);
        Matcher matcherConvert = patternConvert.matcher(result.toString());

        result = new StringBuffer();
        while (matcherConvert.find()) {
            String alias = matcherConvert.group(2) != null ? matcherConvert.group(2).trim() : "";
            matcherConvert.appendReplacement(result, alias.isEmpty() ? "''" : "'' AS " + alias);
        }
        matcherConvert.appendTail(result);

        // 处理WHERE子句中的AES_DECRYPT
        regexAES = "AES_DECRYPT\\(UNHEX\\(([^)]+)\\), '([^']+)'\\)";
        patternAES = Pattern.compile(regexAES, Pattern.CASE_INSENSITIVE);
        matcherAES = patternAES.matcher(result.toString());

        result = new StringBuffer();
        while (matcherAES.find()) {
            matcherAES.appendReplacement(result, "''");
        }
        matcherAES.appendTail(result);

        // 处理WHERE子句中的CONVERT
        regexConvert = "CONVERT\\(([^)]+)\\s+USING\\s+utf8\\)";
        patternConvert = Pattern.compile(regexConvert, Pattern.CASE_INSENSITIVE);
        matcherConvert = patternConvert.matcher(result.toString());

        result = new StringBuffer();
        while (matcherConvert.find()) {
            matcherConvert.appendReplacement(result, "''");
        }
        matcherConvert.appendTail(result);

        return result.toString();
    }







    /**
     * 核心逻辑
     *
     * @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 = preprocessSql(sql);

        log.warn("{}", sql);
        // 解析SQL语句，获取表和字段信息
        SqlParserUtil.SqlDetails sqlDetails = null;
        try {
            sqlDetails = SqlParserUtil.parseSql(sql);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("解析失败：id:{},sql{}",mappedStatement.getId(),sql);
//            return invocation.proceed();
            throw new RuntimeException();
        }
        log.warn("sqlDetails：{}", sqlDetails);

        // 日志记录SQL和where子句
        log.debug("Original SQL: {}", sql);

        // 是否应该返回空结果的标志
        AtomicBoolean shouldReturnEmpty = new AtomicBoolean(true);

        // 检查SQL中是否包含配置的表和字段
        try {
            String finalSql = sql;
            SqlParserUtil.SqlDetails finalSqlDetails = sqlDetails;
            tableFieldProperties.forEach((tableName, conditions) -> {
                log.warn("{}{}", tableName, conditions);
                if (StrUtil.containsIgnoreCase(finalSql, tableName)) {
                    conditions.forEach(condition -> {
                        String[] fields = condition.split(",");
                        shouldReturnEmpty.set(false);
                        List< Object> explainedColumns = finalSqlDetails.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) {
    }
}