全部產品
Search
文件中心

Data Management:SQL審核最佳實務

更新時間:Jun 30, 2024

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)風險檢測。

準備工作

  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. 以管理員帳號,登入Data Management 5.0

  2. 在頂部功能表列中,選擇安全與規範 > 安全規則

    說明

    若您使用的是極簡模式的控制台,請單擊控制台左上方的2023-01-28_15-57-17.png表徵圖,選擇全部功能 > 安全與規範 > 安全規則

  3. 在左側導覽列中,單擊SQL審核最佳化建議

  4. 單擊目標安全規則右側操作列下的編輯

  5. 找到Update/delete語句建議指定where條件安全規則,單擊右側的編輯

  6. 規則內容配置彈窗中,將行為動作配置為必須改進,並單擊確定

步驟二:提交SQL審核

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

  1. 登入Data Management 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語句,系統將在提交審批後報錯。