Data Management:Use the SQL review feature

Preparations

1. Create a table named test_sql_review_table in the destination database instance and insert data to the table by using the test data construction feature. The following statement is used to create the 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. Prepare the file to be reviewed. In this example, an XML file based on the MyBatis framework that contains the following sample code is used.

   You can click MyBatis Sample code to download the sample code.

   <?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>

Step 1: Configure SQL review rules

In this example, the WHERE clauses are recommended in UPDATE and DELETE statements security rule needs to be customized. Use the default settings for the other security rules. The following example shows how to configure the security rule in the security collaboration mode. For more information, see SQL review optimization.

1. Log on to the DMS console V5.0 as a DMS administrator.

2. Move the pointer over the icon in the upper-left corner and choose All functions > Security and Specifications > Security Rules.

   Note

   If you use the DMS console in normal mode, choose Security and Specifications > Security Rules in the top navigation bar.

3. In the left-side navigation pane, click SQL audit optimization recommendations.

4. Find the security rule set that you want to modify and click Edit in the Actions column.

5. Find the update/delete statements recommend specifying a where condition security rule and click Edit in the Actions column.

6. In the Rule content configuration dialog box that appears, set Behavioral action to Must Improve and click OK.

Step 2: Submit SQL statements for review

In the following example, SQL statements in an XML file in accordance with the MyBatis framework are reviewed and modified based on the check results. For more information, see SQL review.

1. Log on to the DMS console V5.0.

2. Move the pointer over the icon in the upper-left corner of the DMS console and choose All Features > Database Development > SQL Review > SQL Audit Ticket.

   Note

   If you use the DMS console in normal mode, choose Database Development > SQL Review > SQL Audit Ticket in the top navigation bar.

3. On the Apply for SQL Review Ticket page, click SQL Review and configure the parameters in the Application section. Click Submit.

   Note

   • Select the database instance that is associated with the security rule set that you configured.

   • In this example, upload the sample code obtained from the Preparations section of this topic.

4. View the SQL review results and modify the statements.

   The following Check Result is returned:

   SQL	SQL review result	SQL optimization suggestion
   <delete id="deleteXxxx"> DELETE FROM test_sql_review_table </delete>	Must Improve: WHERE clauses are recommended in DELETE statements. Note By default, the Behavioral action parameter of this security rule is set to Suggest Improve. In this example, the Behavioral action parameter is set to Must Improve in the "Step 1: Configure SQL review rules for a database" section.	Add the WHERE clause as needed. If you need to delete the entire table, use the following WHERE clause: WHERE 1 = 1. Example: 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>	Potential Issue: Dollar signs ($) are used to concatenate SQL statements, which causes SQL injection risks.	Replace ${pk} with #{pk} in the SQL statement to prevent SQL injection risks. Example: <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>	Suggest Improve: Updates to modification time columns are recommended in UPDATE statements: gmt_modified Potential Issue: The SQL execution plan does not use indexes. Index Recommendation: DMS recommends that you add 1 index.	Add gmt_modified = NOW() to the SET clause of the UPDATE statement. Obtain the SQL statement that uses an index on the Index recommendations tab of the Details panel. Then, submit a ticket for DDL-based lock-free change. For more information, see Perform lock-free DDL operations Example: UPDATE test_sql_review_table SET db_id=#{dbId}, gmt_modified = NOW() WHERE detail_id=#{detailId} and is_delete='N'

   Note

   • If an XML file contains dynamic SQL statements, DMS attempts to replace variables and queries the Execution plan of the SQL statements. To view more information about the Execution plan of an SQL statement, click Details in the Operation column.

   • Due to historical reasons, your SQL statements may contain issues that currently cannot be or do not need to be resolved. In this case, you can manually review these SQL statements and tag them as approved or disapproved. In the Operation column, choose Manual Review > Approve or Manual Review > Disapprove.

   After modification, refresh the page in the DMS console and check the SQL review results again.

5. In the Approval step, click Submit for Approval. The DMS administrator or DBA checks the SQL statements again.

   After the ticket is approved, the ticket is closed.

   Note

   If the modified SQL statements still fail the check or cannot be parsed by DMS, an error message is returned after you click Submit for Approval in the Approval step.