The SQL review feature of Data Management (DMS) reviews SQL statements and provides optimization suggestions. You can use this feature to prevent SQL statements that do not use indexes or do not conform to database development standards from being executed. This reduces SQL injection risks.

Background information

Before a project is published, you must review all SQL statements that are used. This prevents SQL statements that do not conform to database development standards from being published to an online environment. Therefore, business is not affected.

DMS provides the SQL review feature that integrates the SQL review optimization feature in the security rule module. You can use the SQL review feature to review SQL statements and acquire optimization suggestions. You can customize SQL specifications in security rules. For example, you can specify that a table must have a primary key, the WHERE clause is recommended in an UPDATE or DELETE statement, or SQL injection risks need to be detected. For more information, see SQL review optimization.

Prerequisites

  1. In this example, a table named test_sql_review_table is created in advance. The table is created based on the following statement:
    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. 10,000 rows of data are inserted by using the Test Data Generate feature of DMS. For more information, see Generate test data.
  3. The sample code based on the MyBatis framework is prepared.

    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 for a database

  1. Log on to the DMS console V5.0.
  2. In the top navigation bar, click Security and Specifications > Security Rules.
  3. Find the security rule set that you want to modify and click Edit in the Actions column.
    Note
    • If the database instance is managed in Security Collaboration mode, configure the security rule set for the database that you want to manage.
    • If the database instance is managed in Flexible Management or Stable Change mode, find the security rule set of the control mode and click SQL audit optimization recommendations in the Actions column.
  4. In the left-side pane of the Details page, click the SQL audit optimization recommendations tab.
  5. Find the security rule named update/delete statements recommend specifying a where condition and click Edit in the Actions column.
  6. In the Rule content configuration dialog box, set the parameters that are described in the following table.
    Set the Behavioral action parameter to Must Improve
    ParameterDescription
    Behavioral actionThe type of action to be performed based on the security rule. In this example, set the Behavioral action parameter to Must Improve.
    Note Default SQL review rules in DMS do not contain Must Improve. For more information about the Behavioral action parameter, see Behavioral actions.
    RemarksThe description of the behavioral action for this rule, such as the background information.
  7. Click OK.

    If you use the features such as data development, data change, and SQL review, the SQL review optimization feature verifies SQL statements based on the configured security rules.

    In addition, DMS also enables other security rules for the database. Examples: In the UPDATE statement, we recommend that you also update the Modify Time column in tables. SQL injection risks need to be detected. Indexes need to be checked in SQL execution plans. Force index risks need to be detected. For more information, see SQL review optimization.

Step 2: Submit SQL statements for review

  1. Log on to the DMS console V5.0.
  2. In the top navigation bar, click Database Development. In the left-side navigation pane, choose SQL Review > SQL Audit Ticket.
  3. On the Apply for SQL Review Ticket page, set the parameters as required. Then, click Submit.
    Note For more information about the parameters, see SQL review.
    • Select the database instance that is associated with the security rule set that you have configured.
    • Upload the MyBatis sample code that you obtained in the prerequisites.

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

    Apply for SQL review
  4. Click Save.
    DMS parses the SQL files and reviews each SQL statement based on the security rules that are configured for this database. After the review, DMS returns SQL optimization suggestions. The suggestions are classified into four categories: Must Improve, Potential Issue, Suggest Improve, and Index Recommendation. For more information, see SQL review optimization.
  5. View the SQL review results.
    The SQL review results are displayed in the Check Result section.SQL review results
    SQL statementSQL review resultSQL optimization suggestion
     <delete id="deleteXxxx">
     DELETE FROM test_sql_review_table
 </delete>
    		Must Improve: The DELETE statement must use the WHERE clause.
    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 Step 6 of the "Step 1: Configure SQL review rules for a database" section.
    		Add the WHERE clause as needed. If you need to delete the whole 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: The SQL statement contains "$" in ${pk}. This may cause SQL injection risks. Replace ${pk} with #{pk} in the SQL statement. This can 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: In the UPDATE statement, we recommend that you also update the gmt_modified column in the table.
    • Potential Issue: The SQL execution plan does not use indexes.
    • Index Recommendation: An index can be added to the SQL statement.
    • 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 lockless 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 SQL file contains dynamic SQL statements, DMS attempts to replace variables and queries the execution plans of the SQL statements. To query more information about the execution plan of an SQL statement, click Details in the Operation column.
    • In addition to the SQL review results that are provided by the SQL review feature of DMS, SQL statements may contain historical issues that cannot be or do not need to be resolved. In this case, these SQL statements require manual review. In the Operation column, choose Manual Review > Approve or Manual Review > Disapprove. For more information, see SQL review.
    After the modification, refresh the page in the DMS console and check the SQL review results again.
  6. 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.