SQL 审计日志插件

SQL 审计日志插件拦截 StatementHandler.prepare() 和 StatementHandler.query()/update()，捕获 SQL 执行前后的关键信息，实现完整的审计追踪。

审计架构

┌──────────────────────────────────────────────────────┐
│               AuditPlugin                             │
├───────────────────┬──────────────────────────────────┤
│ prepare 拦截       │  query/update 拦截                │
│ - 捕获原始 SQL     │  - 记录执行时间                   │
│ - 解析参数绑定     │  - 记录影响行数                   │
│ - 生成追踪 ID     │  - 记录异常信息                   │
└─────────┬─────────┴──────────────┬───────────────────┘
          │                        │
   ┌──────▼──────┐          ┌──────▼──────┐
   │ AuditRecord │          │ AuditLogger │
   │ traceId     │          │ DB存储       │
   │ sql         │          │ 文件存储     │
   │ params      │          │ MQ异步       │
   │ startTime   │          │              │
   │ endTime     │          └─────────────┘
   │ duration    │
   │ rows/result │
   │ exception   │
   └─────────────┘

核心实现：双拦截点

拦截 StatementHandler.prepare — 捕获 SQL

@Intercepts({
    @Signature(type = StatementHandler.class, method = "prepare",
        args = {Connection.class, Integer.class}),
    @Signature(type = StatementHandler.class, method = "query",
        args = {Statement.class, ResultHandler.class}),
    @Signature(type = StatementHandler.class, method = "update",
        args = {Statement.class})
})
public class AuditPlugin implements Interceptor {

    private static final ThreadLocal<AuditContext> AUDIT_CTX = new ThreadLocal<>();
    private AuditLogger auditLogger;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler handler = (StatementHandler) invocation.getTarget();
        String method = invocation.getMethod().getName();

        if ("prepare".equals(method)) {
            return handlePrepare(invocation, handler);
        } else {
            return handleExecute(invocation, handler, method);
        }
    }

    /**
     * prepare 阶段：捕获 SQL 和参数，记录开始时间
     */
    private Object handlePrepare(Invocation invocation, StatementHandler handler) throws Throwable {
        BoundSql boundSql = handler.getBoundSql();
        String sql = boundSql.getSql();
        Object parameterObject = boundSql.getParameterObject();

        // 构建审计上下文
        AuditContext ctx = new AuditContext();
        ctx.setTraceId(UUID.randomUUID().toString().replace("-", ""));
        ctx.setSql(normalizeSql(sql));
        ctx.setParams(extractParams(boundSql, parameterObject));
        ctx.setStartTime(System.currentTimeMillis());
        ctx.setMapperId(getMapperId(handler));

        AUDIT_CTX.set(ctx);

        // 执行原始 prepare
        return invocation.proceed();
    }

    /**
     * execute 阶段：记录执行结果、耗时、异常
     */
    private Object handleExecute(Invocation invocation, StatementHandler handler, String method) throws Throwable {
        AuditContext ctx = AUDIT_CTX.get();
        if (ctx == null) {
            return invocation.proceed();
        }

        try {
            // 执行原始 SQL
            Object result = invocation.proceed();

            // 记录结果
            ctx.setEndTime(System.currentTimeMillis());
            ctx.setDuration(ctx.getEndTime() - ctx.getStartTime());

            if ("update".equals(method)) {
                ctx.setRowsAffected(result != null ? (int) result : 0);
            } else {
                ctx.setResultSize(result != null ? ((List<?>) result).size() : 0);
            }

            return result;
        } catch (Throwable e) {
            // 记录异常
            ctx.setEndTime(System.currentTimeMillis());
            ctx.setDuration(ctx.getEndTime() - ctx.getStartTime());
            ctx.setException(e.getClass().getSimpleName() + ": " + e.getMessage());
            throw e;
        } finally {
            // 异步写入审计日志
            auditLogger.log(ctx);
            AUDIT_CTX.remove();
        }
    }
}

prepare 拦截点捕获原始 SQL 和参数，query/update 拦截点记录执行结果和耗时，两次拦截共同构成完整审计记录。

审计记录模型

public class AuditContext {
    private String traceId;         // 追踪 ID
    private String sql;             // SQL 文本（已参数化）
    private String params;          // 参数 JSON
    private String mapperId;        // Mapper 方法标识
    private long startTime;         // 开始时间
    private long endTime;           // 结束时间
    private long duration;          // 执行耗时 ms
    private int rowsAffected;       // 影响行数（update）
    private int resultSize;         // 结果集行数（query）
    private String exception;       // 异常信息
    private String database;        // 数据库名
    private String operator;        // 操作人

    // getters and setters...
}

SQL 规范化处理

normalizeSql

private String normalizeSql(String sql) {
    if (sql == null) return "";

    return sql
        // 去除多余空白
        .replaceAll("\\s+", " ")
        // 去除首尾空白
        .trim()
        // 统一关键字大写
        .replaceAll("\\b(select|from|where|insert|into|update|set|delete|join|left|right|inner|outer|on|and|or|order|by|group|having|limit|values)\\b",
            m -> m.group(1).toUpperCase());
}

参数提取

private String extractParams(BoundSql boundSql, Object parameterObject) {
    if (parameterObject == null) return "[]";

    List<ParameterMapping> mappings = boundSql.getParameterMappings();
    if (mappings == null || mappings.isEmpty()) return "[]";

    Map<String, Object> paramMap = new LinkedHashMap<>();
    MetaObject metaObject = SystemMetaObject.forObject(parameterObject);

    for (ParameterMapping mapping : mappings) {
        String propertyName = mapping.getProperty();
        if (metaObject.hasGetter(propertyName)) {
            Object value = metaObject.getValue(propertyName);
            // 敏感参数脱敏
            paramMap.put(propertyName, maskSensitiveParam(propertyName, value));
        } else if (boundSql.hasAdditionalParameter(propertyName)) {
            paramMap.put(propertyName, boundSql.getAdditionalParameter(propertyName));
        }
    }

    try {
        return objectMapper.writeValueAsString(paramMap);
    } catch (Exception e) {
        return paramMap.toString();
    }
}

敏感参数脱敏

private Object maskSensitiveParam(String propertyName, Object value) {
    if (value == null || !(value instanceof String)) return value;

    String str = (String) value;
    String lower = propertyName.toLowerCase();

    // 密码、密钥类参数直接隐藏
    if (lower.contains("password") || lower.contains("secret") || lower.contains("token")) {
        return "******";
    }

    // 手机号、身份证部分脱敏
    if (lower.contains("phone") && str.length() == 11) {
        return str.substring(0, 3) + "****" + str.substring(7);
    }
    if (lower.contains("idcard") && str.length() == 18) {
        return str.substring(0, 6) + "********" + str.substring(14);
    }

    return str;
}

审计日志输出

日志存储策略

public interface AuditLogger {
    void log(AuditContext ctx);
}

数据库存储

public class DatabaseAuditLogger implements AuditLogger {

    private final SqlSession sqlSession;

    @Override
    public void log(AuditContext ctx) {
        // 异步写入，不阻塞主流程
        CompletableFuture.runAsync(() -> {
            try {
                AuditMapper mapper = sqlSession.getMapper(AuditMapper.class);
                AuditRecord record = convertToRecord(ctx);
                mapper.insert(record);
                sqlSession.commit();
            } catch (Exception e) {
                // 审计写入失败不影响主流程
                System.err.println("Audit log write failed: " + e.getMessage());
            }
        });
    }
}

// 审计日志表 Mapper
public interface AuditMapper {
    @Insert("INSERT INTO sql_audit_log " +
        "(trace_id, sql_text, params, mapper_id, duration, " +
        "rows_affected, result_size, exception, create_time) " +
        "VALUES (#{traceId}, #{sqlText}, #{params}, #{mapperId}, " +
        "#{duration}, #{rowsAffected}, #{resultSize}, #{exception}, NOW())")
    void insert(AuditRecord record);
}

文件存储

public class FileAuditLogger implements AuditLogger {

    private final String logDir;
    private final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS");

    @Override
    public void log(AuditContext ctx) {
        String dateStr = LocalDate.now().format(DateTimeFormatter.ISO_LOCAL_DATE);
        Path logFile = Paths.get(logDir, "sql-audit-" + dateStr + ".log");

        String logLine = String.format("[%s] traceId=%s | mapper=%s | duration=%dms | sql=%s | params=%s | rows=%s | exception=%s%n",
            LocalDateTime.now().format(formatter),
            ctx.getTraceId(),
            ctx.getMapperId(),
            ctx.getDuration(),
            ctx.getSql(),
            ctx.getParams(),
            ctx.getRowsAffected() > 0 ? ctx.getRowsAffected() : ctx.getResultSize(),
            ctx.getException() != null ? ctx.getException() : "NONE");

        try {
            Files.write(logFile, logLine.getBytes(StandardCharsets.UTF_8),
                StandardOpenOption.CREATE, StandardOpenOption.APPEND);
        } catch (IOException e) {
            System.err.println("Audit file write failed: " + e.getMessage());
        }
    }
}

MQ 异步存储

public class MqAuditLogger implements AuditLogger {

    private final MqProducer mqProducer;
    private final String topic;

    @Override
    public void log(AuditContext ctx) {
        mqProducer.send(topic, convertToRecord(ctx));
    }
}

组合日志输出

public class CompositeAuditLogger implements AuditLogger {

    private final List<AuditLogger> loggers;

    public CompositeAuditLogger(AuditLogger... loggers) {
        this.loggers = Arrays.asList(loggers);
    }

    @Override
    public void log(AuditContext ctx) {
        for (AuditLogger logger : loggers) {
            logger.log(ctx);
        }
    }
}

// 使用：同时写入数据库和文件
AuditLogger logger = new CompositeAuditLogger(
    new DatabaseAuditLogger(sqlSession),
    new FileAuditLogger("/var/log/mybatis/audit")
);

慢 SQL 告警

public class SlowSqlAuditLogger implements AuditLogger {

    private final long slowThresholdMs;
    private final AuditLogger delegate;
    private final AlertService alertService;

    @Override
    public void log(AuditContext ctx) {
        // 先委托正常记录
        delegate.log(ctx);

        // 判断是否慢 SQL
        if (ctx.getDuration() > slowThresholdMs) {
            alertService.alert(new SlowSqlAlert(
                ctx.getTraceId(),
                ctx.getSql(),
                ctx.getDuration(),
                ctx.getMapperId(),
                ctx.getParams()
            ));
        }
    }
}

SQL 审计 vs 性能监控对比

插件配置

<plugins>
  <plugin interceptor="com.example.AuditPlugin">
    <property name="auditMode" value="database,file"/>
    <property name="slowThreshold" value="1000"/>
    <property name="maskParams" value="true"/>
    <property name="logDir" value="/var/log/mybatis/audit"/>
  </plugin>
</plugins>

@Override
public void setProperties(Properties properties) {
    String mode = properties.getProperty("auditMode", "file");
    String slowThreshold = properties.getProperty("slowThreshold", "1000");
    String maskParams = properties.getProperty("maskParams", "true");
    String logDir = properties.getProperty("logDir", "/var/log/audit");

    // 根据 auditMode 组装 CompositeAuditLogger
    List<AuditLogger> loggers = new ArrayList<>();
    if (mode.contains("database")) loggers.add(new DatabaseAuditLogger(sqlSession));
    if (mode.contains("file")) loggers.add(new FileAuditLogger(logDir));
    if (mode.contains("mq")) loggers.add(new MqAuditLogger(mqProducer, "sql-audit"));

    this.auditLogger = new SlowSqlAuditLogger(
        Long.parseLong(slowThreshold),
        new CompositeAuditLogger(loggers.toArray(new AuditLogger[0])),
        alertService
    );
}

注意事项

1. 异步写入：审计日志必须异步写入，不能阻塞主查询流程
2. 异常容错：审计写入失败不能影响主业务，必须 try-catch 静默处理
3. 参数脱敏：密码、密钥、手机号、身份证等敏感参数必须脱敏后再记录
4. SQL 长度限制：超长 SQL 应截断存储，避免审计表字段溢出
5. 清理策略：审计日志表需定期清理历史数据，设置保留周期

要点总结

• SQL 审计插件双拦截点：prepare 捕获 SQL 和参数，query/update 记录结果和耗时
• 通过 ThreadLocal 在两次拦截间传递 AuditContext，包含 traceId、SQL、参数、时间戳
• SQL 规范化：去除多余空白、统一关键字大写，便于日志检索和去重
• 支持多种存储方式：数据库写入（可查询检索）、文件追加（轻量）、MQ 异步（高吞吐）
• 慢 SQL 告警通过 SlowSqlAuditLogger 装饰器实现，超过阈值触发告警
• 审计写入必须异步 + 异常容错，不影响主业务流程
• 参数记录需脱敏密码、密钥、手机号、身份证等敏感信息
• CompositeAuditLogger 组合多种输出方式，同时满足审计合规和性能分析需求

存放路径：D:\git2\jwdev\articles\MYBATIS\专家\插件开发高级应用\SQL 审计日志插件.md