SQL审计、Archery实战记录

概述

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

功能点：

问题

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.JdbcTemplate

class SendMail {
    @Autowired
    JdbcTemplate 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_position
                    FROM ems_standard_db_a_06534c91dc504dd9a9f9d3f699b6ddff.energy_consumption_analysis
                    WHERE acquisition_month = month(now()) - 1
                    GROUP BY device_position
                    ORDER 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对照表

参考

• SQL审核平台Yearning和Archery