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. 准备待审核的文件，本示例中以基于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. 单击控制台左上角的图标，选择全部功能 > 安全与规范（DBS） > 安全规则。

   说明

   若您使用的是非极简模式的控制台，在顶部菜单栏中，选择安全与规范（DBS） > 安全规则。

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

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

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

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

步骤二：提交SQL审核

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

1. 登录数据管理DMS 5.0。

2. 在顶部菜单栏中，选择数据库开发 > SQL审核 > SQL审核工单。

   说明

   若您使用的是极简模式的控制台，请单击控制台左上角的图标，选择全部功能 > 数据库开发 > SQL审核 > SQL审核工单。

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

   说明

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

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

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

5. 单击提交审批，管理员或DBA将再次确认SQL。
   审批通过后即工单流程结束。

   说明 若存在检测不通过或解析异常状态的SQL语句，系统将在提交审批后报错。