数据管理DMS的SQL审核功能,支持对上传的SQL语句进行审核并提供优化建议,避免无索引或不规范的SQL语句,降低SQL注入风险。

背景信息

在项目正式发布到线上之前,需要对涉及的SQL进行全面的审核,避免不符合数据库开发规范的SQL发布到线上影响生产服务。

为解决该问题,DMS推出了SQL审核功能,结合安全规则的SQL审核优化建议,对SQL进行审核并提供优化建议。您可以在安全规则中自定义SQL规范,例如表要有主键、UPDATE或DELETE语句建议指定WHERE条件及SQL注入风险检测等,更多信息,请参见SQL审核优化建议

准备工作

  1. 本示例中已提前创建test_sql_review_table表,建表语句如下:
    CREATE TABLE `test_sql_review_table` (
      `id` BIGINT(20) UNSIGNED  NOT NULL AUTO_INCREMENT,
      `gmt_create` DATETIME NOT NULL,
      `gmt_modified` DATETIME NOT NULL,
      `detail_id` BIGINT(20) UNSIGNED DEFAULT NULL,
      `name` VARCHAR(256) DEFAULT NULL,
      `db_id` BIGINT(20) DEFAULT NULL,
      `is_delete` VARCHAR(1) DEFAULT NULL,
      `file_content_id` BIGINT(20) UNSIGNED DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  2. 通过测试数据构建功能,插入1万行数据。更多信息,请参见测试数据构建
  3. 准备MyBatis框架的代码如下:

    您也可以单击MyBatis示例代码进行下载。

    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
    <mapper namespace="com.xxx.namespace">
    
        <sql id="SELECT_ALL_FROM">
            SELECT
            id,
            gmt_create,
            gmt_modified,
            detail_id,
            name,
            db_id,
            is_delete,
            file_content_id
            FROM test_sql_review_table sf
        </sql>
    
        <select id="getByPK" resultType="com.xxx.TestSQLReviewTableDO">
            <include refid="SELECT_ALL_FROM"/>
            WHERE id=${pk}
        </select>
    
        <select id="getXxxList" resultType="com.xxx.TestSQLReviewTableDO">
            <include refid="SELECT_ALL_FROM"/>
            WHERE
            <foreach collection="pks" open="sf.id in (" item="item" separator="," close=")">
                #{item}
            </foreach>
            <if test="searchKey != null and searchKey!=''">
                AND sd.name like concat('%',#{searchKey}, '%')
            </if>
            AND sf.is_delete='N'
        </select>
    
        <update id="updateAaaa">
            UPDATE test_sql_review_table
            SET
            db_id=#{dbId}
            WHERE detail_id=#{detailId}
            AND is_delete='N'
        </update>
    
        <delete id="deleteXxxx">
            DELETE FROM test_sql_review_table
        </delete>
    
    </mapper>

步骤一:配置SQL审核规则

  1. 登录数据管理DMS 5.0
    说明 如果您需要切换到旧版数据管理DMS,单击页面右下角返回旧版,进入数据管理DMS平台。具体操作,请参见数据管理DMS 5.0切换至旧版
  2. 在顶部菜单栏中,选择安全与规范 > 安全规则
    说明 如果您使用的是旧版数据管理DMS,在顶部菜单栏中,选择全部功能 > 系统管理 > 安全 > 安全规则
  3. 单击目标安全规则右侧操作列下的编辑
    SQL审核优化-编辑安全规则
    说明
    • 若实例的管控模式为安全协同,请配置当前数据库的安全规则集。
    • 若实例的管控模式为自由操作稳定变更,请单击对应管控模式右侧的SQL审核优化建议
  4. 详情页左侧的导航栏中,单击SQL审核优化建议
  5. 找到update/delete语句建议指定where条件安全规则,单击右侧的编辑
  6. 规则内容配置弹窗中,配置如下信息。
    修改为必须改进
    参数名 说明
    行为动作 行为动作设置为必须改进
    说明 系统初始化规则中不包含必须改进,关于行为动作的更多说明,请参见系统行为动作
    备注 输入此规则的备注信息,如业务背景信息。
  7. 单击确定

    在后续的数据开发、数据变更及SQL审核等功能中,SQL审核优化建议将根据配置的安全规则检验SQL语句。

    同时,系统也默认开启了UPDATE语句建议同时更新表上的"修改时间"列、SQL注入风险检测、执行计划索引检查及强制索引(force index)风险检测等规则,更多信息,请参见SQL审核优化建议

步骤二:提交SQL审核

  1. 登录数据管理DMS 5.0
    说明 如果您需要切换到旧版数据管理DMS,单击页面右下角返回旧版,进入数据管理DMS平台。具体操作,请参见数据管理DMS 5.0切换至旧版
  2. 在顶部菜单栏中,选择数据库开发 > SQL审核 > SQL审核工单
    说明 如果您使用的是旧版数据管理DMS,在顶部菜单栏中,选择全部功能 > 优化 > SQL审核
  3. SQL审核工单申请页面,配置相关参数。并单击提交申请
    说明 关于参数的说明,请参见SQL审核工单参数说明
    • 选择已关联目标安全规则集的数据库实例。
    • 上传准备工作中的MyBatis示例代码。

      您也可以单击MyBatis示例代码进行下载。

    申请SQL审核界面
  4. 单击提交申请
    系统将对SQL文件进行解析,并根据当前数据库的安全规则,对SQL语句进行逐条审核,返回的SQL审核优化结果包含必须改进潜在问题建议改进索引推荐,更多信息,请参见SQL审核优化建议
  5. 查看SQL审核结果并调整
    系统的检查结果如下:SQL审核结果
    SQL SQL审核优化结果 调整SQL
     <delete id="deleteXxxx">
         DELETE FROM test_sql_review_table
     </delete>
    必须改进:DELETE语句建议指定WHERE条件为必须改进。
    说明 该规则项默认为建议改进,在步骤一中将其设置成必须改进
    根据业务需求增加WHERE条件,若要删除全表,可以将条件写成WHERE 1 = 1
    例如:
    DELETE FROM test_sql_review_table WHERE id = #{pk}
     <select id="getByPK" resultType="com.xxx.TestSQLReviewTableDO">
         <include refid="SELECT_ALL_FROM"/>
         WHERE id=${pk}
     </select>
    潜在问题:使用"$"拼接SQL,如${pk},可能存在SQL注入风险。 将${pk} 替换为 #{pk},防止SQL注入风险。
    例如:
         <include refid="SELECT_ALL_FROM"/>
         WHERE id=#{pk}
     <update id="updateAaaa">
         UPDATE test_sql_review_table
         SET
         db_id=#{dbId}
         WHERE detail_id=#{detailId}
         AND is_delete='N'
     </update>
    • 建议改进:UPDATE语句建议同时更新表上的“修改时间“列:gmt_modified。
    • 潜在问题:SQL执行计划不走索引。
    • 索引推荐:DMS推荐增加1条索引。
    • 在UPDATE语句SET子句中增加gmt_modified = NOW()
    • 详情面板的索引建议中获取增加索引的SQL语句,并提交DDL无锁变更执行,更多信息,请参见DDL无锁变更
    例如:
            UPDATE test_sql_review_table
            SET
            db_id=#{dbId}
            gmt_modified = NOW()
            WHERE detail_id=#{detailId}
            and is_delete='N'
    说明
    • 当XML文件中存在动态SQL时,DMS会尝试替换变量并获取该SQL的执行计划(Explain Plan),若成功获取,您可以在详情面板中查看执行计划的具体内容。
    • 除DMS审核SQL以外,若存在一些历史问题暂时无法修复或无需修复,您可以选择人工审核 > 人工通过人工不通过,手动标注检测结果。更多信息,请参见SQL审核
    调整后,请刷新界面并再次检查SQL审核结果。
  6. 单击提交审批,管理员或DBA将再次确认SQL。
    审批通过后即流程结束。
    说明 若存在检测不通过解析异常状态的SQL语句,系统将在提交审批后报错。