当前位置： 首页 > ops >正文

SQL审计、Archery实战记录

ops 2025/7/22 12:31:19

概述

SQL审计，也叫SQL审核，审查。

功能点：

对比ArcheryYearning
GitHubArcheryYearning
官网ArcheryYearning
语言Python等Go
文档中文丰富中文丰富
权限分配支持，粒度细支持，粒度细
支持数据库很多MySQL/PostgreSQL/ClickHouse
SQL审计、审计日志支持支持
检查规则支持支持
回滚支持DDL/DML支持DDL/DML，
数据库备份支持
数据字典支持
慢SQL查询、优化建议支持
通讯工具集成支持企微，飞书未知
Issue(Open/Closed)197/1438133/821
Fork1.7k2k
Star6.3k8.6k

问题

GoInception

提交SQL工单，遇到如下报错：
在这里插入图片描述
解决方法：
在这里插入图片描述
对goInception有兴趣的可以去看其开源GitHub，Archery使用k8s部署。通过k9s查看IP和Port，输入:svc进入Services视图，然后输入Archery执行精确搜索匹配：
在这里插入图片描述
在这里插入图片描述

无法连接GoInception备份库

在这里插入图片描述
解决方法：
在这里插入图片描述

审核失败

提交上线SQL工单，报错如下：
在这里插入图片描述
提示信息：开启binlog日志。解读：Archery对每一个SQL工单都会生成一个回滚语句，而生成回滚语句的功能建立在开启binlog。

一般阿里云RDS默认开启binlog，而自搭建的MySQL集群往往没有开启。

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';

配置审批流程

提交SQL上线工单时，点击【SQL】检测，提示
在这里插入图片描述
解决方法：
以admin用户登录，系统管理-配置项管理，配置项选择【工单审核流配置】-【SQL上线申请】-【选择环境】，选择审批权限组，点击变更：
在这里插入图片描述

修改密码

在这里插入图片描述
解决

在这里插入图片描述

Not Found

提交一个超级复杂的SQL变更工单，【SQL检测】通过，点击【SQL提交】报错
在这里插入图片描述
SQL如下：

UPDATE device_mail_config t SET t.script = 'package com.tesla.admin.service
import cn.hutool.core.date.DateUtil
import com.baomidou.dynamic.datasource.toolkit.DynamicDataSourceContextHolder
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.jdbc.core.JdbcTemplateclass SendMail {@AutowiredJdbcTemplate jdbcTemplate//   {//        mainTitle: \'\', //主标题//        mainContent: \'\', //主内容//        subTitle: \'\',  //副标题//        listing: {     //如果邮件是个列表}//        table: {       //如果邮件是个表格//            name: \'\',//            columns: [//                {//                    title: \'\', //标题//                    width: \'\', //宽度//                    field: \'id\', //数据字段//                }//            ],//            data: [//                {//                    id: \'1\'//                }//            ]//        }//        detail: {//            header: \'\',//            body: \'\',//            footer: \'\'//        }//   }def run() {DynamicDataSourceContextHolder.push("mysql1")try {def results = jdbcTemplate.queryForList("""SELECT  device_positionFROM ems_standard_db_a_06534c91dc504dd9a9f9d3f699b6ddff.energy_consumption_analysisWHERE acquisition_month = month(now()) - 1GROUP BY device_positionORDER BY abs(mom_rate) DESC;""")def tableDef = [name   : \'异常详情\',columns: [[title: \'设备位置\', width: \'250\', field: \'device_position\']],data   : results]return [mainTitle       : \'尊敬的用户，您好！\',mainContent     : "附件是上月（${DateUtil.month(new Date())}月份）内所有用能异常情况汇总，请您下载查阅。",table           : tableDef, // 附件使用hideTableInBody : true, // 模板用以跳过渲染detail          : [footer: \'系统自动发送，请勿回复。\'],]} catch (Exception e) {log.error("groovy run error: ${e.message}")} finally {DynamicDataSourceContextHolder.poll()}}
}
' WHERE t.id = 18

原因猜测：update语句里，'字符前后不能有回车换行特殊字符。

解决方法：

}' WHERE t.id = 18

流程复用

一个比较规范的SQL上线流程应该是这样的：现在预发布环境提交SQL，预发布环境校验各种版本迭代和功能正常后，需要将SQL同步上线到PRO生产环境。因此，同样的SQL脚本会经历过预发布环境和生产环境。
在这里插入图片描述
Archery当然也支持这个流程（功能）。点击SQL工单，进入详情页，然后点击【上线其他实例】，选择实例即可。注意会产生新的工单ID。

查询权限

想把Archery作为一个简单的SQL查询终端，结果遇到下面的报错：
在这里插入图片描述
解决方法：
在这里插入图片描述
如上，找到【用户管理】，添加【用户权限】，搜索查询，选择下图三个，点击
在这里插入图片描述
然后保存。

文件限制

提交SQL工单时，支持在SQL编辑框输入脚本，也支持上传文件，但是文件大小限制为10M：
在这里插入图片描述
解决方法：

排队中

提交的SQL工单：
在这里插入图片描述
经过排查，是Archery部署的节点出现内存异常。

Illegal mix of collations

测试环境里，某个接口突然报错：

    "msg": "\n### Error querying database.  Cause: java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='\n### The error may exist in com/tesla/admin/repository/mapper/UserMapper.java (best guess)\n### The error may involve com.tesla.admin.repository.mapper.UserMapper.getUserBelongDtoByThird-Inline\n### The error occurred while setting parameters\n### SQL: SELECT u.id AS user_id, u.third_party_id AS customer_user_id, t.id AS tenant_id, t.name AS tenant_name, t.third_party_id AS customer_tenant_id, a.app_id AS app_id FROM user u INNER JOIN tenant t ON u.app_id = t.app_id INNER JOIN app a ON t.app_id = a.app_id WHERE u.third_party_id = ? AND t.third_party_id LIKE 'tesla_ems%' AND u.deleted = 0 AND t.deleted = 0 AND a.deleted = 0\n### Cause: java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='\n; uncategorized SQLException; SQL state [HY000]; error code [1267]; Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='; nested exception is java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='"

执行的SQL为：

SELECT u.id AS user_id, u.third_party_id AS customer_user_id, t.id AS tenant_id, t.name AS tenant_name, t.third_party_id AS customer_tenant_id, a.app_id AS app_id FROM user u INNER JOIN tenant t ON u.app_id = t.app_id INNER JOIN app a ON t.app_id = a.app_id WHERE u.third_party_id = ? AND t.third_party_id LIKE 'tesla_ems%' AND u.deleted = 0 AND t.deleted = 0 AND a.deleted = 0;

很简单的3表JOIN查询，之前是好的；突然出现上面的报错。

在DataGrip里使用Ctrl + Alt + G快捷键不管用，获取不到具体每一个字段的编码。

必须要使用SHOW CREATE TABLE语句：

SHOW CREATE TABLE app;CREATE TABLE `app` (`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',`app_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '应用唯一标识符'
)SHOW CREATE TABLE tenant;CREATE TABLE `tenant` (`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',`app_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '应用唯一标识符'
)  
SHOW CREATE TABLE user;CREATE TABLE `user` (`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',`app_id` varchar(35) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户所属应用'
)

发现app.app_id字段和其他表里的该字段不一样，为utf8mb4_0900_ai_ci ，其他表是utf8mb4_general_ci。

解决方法：

ALTER TABLE app MODIFY COLUMN app_id VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '应用唯一标识符';

确实解决问题。

但是！！过一段时间，又出现这个报错！！！

奇奇怪怪。

经过排查，原来是有另外一个同事使用Archery创建新表，SQL工单如下：
在这里插入图片描述
其中subject_type字段注释已经非常清楚，subject_id会与app表的app_id字段JOIN查询。

同样地，看一下建表语句：
在这里插入图片描述
发现没有，使用Archery创建的新表，默认会对（部分）表字段使用utf8mb4_0900_ai_ci 。同事在JOIN查询时遇到和上面报错类似的问题，于是修改表app.app_id的排序规则为utf8mb4_0900_ai_ci ，导致我这边的某个接口功能出现异常。两边来回互相修改app.app_id的排序规则，因此出现前文所述的诡异问题。

排序规则

常见Collation对照表

排序规则MySQL版本说明
utf8mb4_general_ci旧版本默认不支持emoji，不推荐
utf8mb4_0900_ai_ciMySQL 8.0默认推荐使用，支持emoji和更智能的排序
utf8mb4_unicode_ci通用也支持emoji，排序更标准

参考

  • SQL审核平台Yearning和Archery
http://www.xdnf.cn/news/15914.html

相关文章：

  • 代码随想录算法训练营第二十七天
  • 算法训练营DAY37 第九章 动态规划 part05
  • channel_up和lane_up
  • Promise 详解与实现：从原理到实践
  • 【设计模式C#】工厂方法模式（相比简单工厂模式更加具有灵活性和扩展性的工厂模式）
  • Day07_网络编程20250721（网络编程考试试卷）
  • 本地部署Dify、Docker重装
  • JAVA后端开发—— JWT（JSON Web Token）实践
  • 【实践篇】基于.venv 的 ComfyUI 环境同配置迁移：pyvenv.cfg 路径修改法
  • 论文Review Lidar 3DGS Splat-LOAM: Gaussian Splatting LiDAR Odometry and Mapping
  • Ubuntu 22.04 安装 Docker （安装包形式）
  • 使用pymongo进行MongoDB的回收
  • 机器学习中的数据预处理：从入门到实践
  • FastMCP全篇教程以及解决400 Bad Request和session termination的问题
  • IOPaint+CPolar：零公网IP也能搭建专属AI图像编辑平台
  • Git 完全手册：从入门到团队协作实战（3）
  • doker centos7安装1
  • uni-app 鸿蒙平台条件编译指南
  • 【C++11】哈希表与无序容器：从概念到应用
  • 完整的 SquareStudio 注册登录功能实现方案：
  • 亚马逊新品推广关键：如何通过广告数据反馈不断优化关键词
  • 【安全篇 / 反病毒】(7.6) ❀ 01. 查杀HTTPS加密网站病毒 ❀ FortiGate 防火墙
  • Docker安装Elasticsearch 7.17.0和Kibana 7.17.0并配置基础安全
  • 17 BTLO 蓝队靶场 Pretium 解题记录
  • MySQL表的基础操作
  • 微软CEO Satya Nadella提出AI重构法则：从范式跃迁到社会盈余
  • 病历数智化3分钟：AI重构医院数据价值链
  • OpenGL鼠标控制沿着指定轴旋转
  • JSX（JavaScript XML）‌简介
  • wordle game（猜词游戏）小demo【react + ts】