文档

SQL审核最佳实践

数据管理DMS的SQL审核功能,支持对SQL语句进行批量审核并提供优化建议,避免无索引或不规范的SQL语句,降低SQL注入风险。本文以对XML文件中多个SQL语句进行审核为例,向您展示配置SQL审核规则、审核目标文件中SQL语句的流程。

背景信息

在项目正式发布到线上之前,需要对涉及的SQL进行全面的审核,避免不符合数据库开发规范的SQL发布到线上影响生产服务。而所有SQL都需要人工审核,会极大地降低研发效率。

为解决该问题,DMS推出了SQL审核功能,基于SQL审核规则对SQL语句进行检验并提供优化建议,并且您可以在安全规则中自定义SQL审核规则。本文涉及的SQL审核规则如下:

  • update/delete语句建议指定where条件。

  • SQL注入风险检测。

  • 执行计划索引检查。

  • update语句建议同时更新表上的修改时间列。

  • 强制索引(force index)风险检测。

准备工作

  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. 准备待审核的文件,本示例中以基于MyBatis框架的XML文件为例,其代码如下:

    您也可以单击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审核规则

本示例中“update/delete语句建议指定where条件”安全规则需要进行自定义配置,其他安全规则为系统默认配置无需更改。如下示例在安全协同模式下对该安全规则进行配置。更多信息,请参见配置SQL审核优化建议

  1. 以管理员账号,登录数据管理DMS 5.0

  2. 在顶部菜单栏中,选择安全与规范 > 安全规则

    说明

    若您使用的是极简模式的控制台,请单击控制台左上角的2023-01-28_15-57-17.png图标,选择全部功能 > 安全与规范 > 安全规则

  3. 在左侧导航栏中,单击SQL审核优化建议

  4. 单击目标安全规则右侧操作列下的编辑

  5. 找到update/delete语句建议指定where条件安全规则,单击右侧的编辑

  6. 规则内容配置弹窗中,将行为动作配置为必须改进,并单击确定

步骤二:提交SQL审核

如下示例对基于MyBatis框架的XML文件中的SQL语句进行审核,并根据审核结果进行调整。更多信息,请参见SQL审核

  1. 登录数据管理DMS 5.0
  2. 在顶部菜单栏中,选择数据库开发 > SQL审核 > SQL审核工单

    说明

    若您使用的是极简模式的控制台,请单击控制台左上角的2023-01-28_15-57-17.png图标,选择全部功能 > 数据库开发 > SQL审核 > SQL审核工单

  3. SQL审核工单申请页面,配置相关参数。并单击提交申请

    说明
    • 选择已关联目标安全规则集的数据库实例。

    • 上传准备工作中的示例代码。

  4. 查看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 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审核结果。

  5. 单击提交审批,管理员或DBA将再次确认SQL。
    审批通过后即工单流程结束。
    说明 若存在检测不通过解析异常状态的SQL语句,系统将在提交审批后报错。