SQL審核最佳實務教程 - Data Management

Data Management的SQL審核功能，支援對SQL語句進行批量審核並提供最佳化建議，避免無索引或不規範的SQL語句，降低SQL注入風險。本文以對XML檔案中多個SQL語句進行審核為例，向您展示配置SQL審核規則、審核目標檔案中SQL語句的流程。

背景資訊

在專案正式發布到線上之前，需要對涉及的SQL進行全面的審核，避免不符合資料庫開發規範的SQL發布到線上影響生產服務。而所有SQL都需要人工審核，會極大地降低研發效率。

為解決該問題，DMS推出了SQL審核功能，基於SQL審核規則對SQL語句進行檢驗並提供最佳化建議，並且您可以在安全規則中自訂SQL審核規則。本文涉及的SQL審核規則如下：

update/delete語句建議指定where條件。
SQL注入風險檢測。
執行計畫索引檢查。
update語句建議同時更新表上的修改時間列。
強制索引（force index）風險檢測。

準備工作

在目標資料庫執行個體下提前建立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;

準備待審核的檔案，本樣本中以基於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審核最佳化建議。

以管理員帳號，登入Data Management 5.0。
單擊控制台左上方的表徵圖，選擇全部功能 > 安全與規範 > 安全規則。
說明
若您使用的是非極簡模式的控制台，在頂部功能表列中，選擇安全與規範 > 安全規則。
在左側導覽列中，單擊SQL審核最佳化建議。
單擊目標安全規則右側操作列下的編輯。
找到Update/delete語句建議指定where條件安全規則，單擊右側的編輯。
在規則內容配置彈窗中，將行為動作配置為必須改進，並單擊確定。

步驟二：提交SQL審核

如下樣本對基於MyBatis架構的XML檔案中的SQL語句進行審核，並根據審核結果進行調整。更多資訊，請參見SQL審核。

登入Data Management 5.0。
在頂部功能表列中，選擇資料庫開發 > SQL審核 > 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審核結果。

單擊提交審批，管理員或DBA將再次確認SQL。
審批通過後即工單流程結束。
說明若存在檢測不通過或解析異常狀態的SQL語句，系統將在提交審批後報錯。