SQL审核最佳实践 - 数据管理 DMS

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

背景信息

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

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

准备工作

本示例中已提前创建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;

通过测试数据构建功能，插入1万行数据。更多信息，请参见测试数据构建。

准备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审核规则

登录数据管理DMS 5.0。
在顶部菜单栏中，选择安全与规范 > 安全规则。
单击目标安全规则右侧操作列下的编辑。
说明
- 若实例的管控模式为安全协同，请配置当前数据库的安全规则集。
- 若实例的管控模式为自由操作或稳定变更，请单击对应管控模式右侧的SQL审核优化建议。
在详情页左侧的导航栏中，单击SQL审核优化建议。
找到update/delete语句建议指定where条件安全规则，单击右侧的编辑。

在规则内容配置弹窗中，配置如下信息。


参数名	说明
行为动作	将行为动作设置为必须改进。说明系统初始化规则中不包含必须改进，关于行为动作的更多说明，请参见系统行为动作。
备注	输入此规则的备注信息，如业务背景信息。

单击确定。
在后续的数据开发、数据变更及SQL审核等功能中，SQL审核优化建议将根据配置的安全规则检验SQL语句。
同时，系统也默认开启了UPDATE语句建议同时更新表上的＂修改时间＂列、SQL注入风险检测、执行计划索引检查及强制索引（force index）风险检测等规则，更多信息，请参见配置SQL审核优化建议。

步骤二：提交SQL审核

登录数据管理DMS 5.0。
在顶部菜单栏中，选择数据库开发 > SQL审核 > SQL审核工单。
在SQL审核工单申请页面，配置相关参数。并单击提交申请。
说明关于参数的说明，请参见SQL审核工单参数说明。
- 选择已关联目标安全规则集的数据库实例。
- 上传准备工作中的MyBatis示例代码。
  您也可以单击MyBatis示例代码进行下载。
单击提交申请。
系统将对SQL文件进行解析，并根据当前数据库的安全规则，对SQL语句进行逐条审核，返回的SQL审核优化结果包含必须改进、潜在问题、建议改进及索引推荐。更多信息，请参见配置SQL审核优化建议。

查看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无锁变更执行，更多信息，请参见通过无锁变更工单实现无锁结构变更。例如： `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审核结果。

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