Best practices for SQL review-Data Management-阿里云帮助中心

The SQL review feature of Data Management DMS allows you to review SQL statements in batches and provides optimization suggestions. This helps you avoid non-standard SQL or queries that lack indexes, and reduces the risk of SQL injection. This topic demonstrates how to configure security rules and review SQL statements in an XML file.

Background information

Before you deploy a project to production, you must review all related SQL statements to prevent non-compliant code from impacting your live services. However, manually reviewing every SQL statement can significantly slow down the development process.

To address this challenge, DMS provides the SQL review feature. This feature inspects SQL statements based on a set of security rules and provides optimization advice. You can also customize these security rules. This topic uses the following security rules:

WHERE clauses are recommended in UPDATE and DELETE statements.
SQL injection risk detection.
Index check in execution plan.
Recommendation to update the modification time column in UPDATE statements.
force index risk detection.

Prerequisites

Create a table named test_sql_review_table in your target database instance. Then, populate it with data using the test data construction feature. Use the following statement 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;

Prepare the file that contains the SQL statements you want to review. This example uses an XML file based on the MyBatis framework.

You can also click MyBatis Sample code to download the file. The file contains the following 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 must be customized. The other security rules use their default configurations. The following example shows how to configure this security rule in security collaboration mode. For more information, see Configure SQL review optimization suggestions.

Log in to the DMS console with an administrator account.
Move the pointer over the icon in the upper-left corner and choose All Features > Security and disaster recovery (DBS) > Security Rules.

Note
If you use the DMS console in normal mode, choose Security and disaster recovery (DBS) > Security Rules in the top navigation bar.
In the left-side navigation pane, click SQL review optimization suggestions.
In the Actions column of the target security rule, click Edit.
Find the WHERE clauses are recommended in UPDATE and DELETE statements security rule and click Edit on the right.
In the Rule content configuration dialog box, set Behavioral action to Must Improve and click OK.

Step 2: Submit SQL for review

This example demonstrates how to review the SQL statements in the MyBatis-based XML file and modify them based on the results. For more information, see SQL review.

Log in to DMS 5.0.
In the top navigation bar, choose Database Development > SQL Review > SQL Audit Ticket.
Note
If you use the DMS console in simple mode, move the pointer over the icon in the upper-left corner of the console and choose All Features > Database Development > SQL Review > SQL Audit Ticket.
On the Apply for SQL Review Ticket page, configure the required parameters and click Submit.
Note
- Select the database instance that is associated with the target security rule set.
- Upload the sample code from the Prerequisites section.

View the SQL review results and make adjustments.

The system returns the following Check Result:

SQL	Review result	Suggestion
`<delete id="deleteXxxx"> DELETE FROM test_sql_review_table </delete>`	Must Improve: A WHERE clause is recommended for DELETE statements. Note By default, this rule is set to Suggest Improve. In Step 1, it was changed to Must Improve.	Add a WHERE clause based on your business requirements. If you intend to delete all data from the table, use `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: Using a dollar sign ($) to concatenate SQL, such as in `${pk}`, poses an SQL injection risk.	Replace `${pk}` with `#{pk}` to prevent SQL injection. 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: UPDATE statements should also update the modification time column: `gmt_modified`. Potential Issue: The execution plan does not use an index. Index Recommendation: DMS recommends adding an index.	Add `gmt_modified = NOW()` to the SET clause of the UPDATE statement. On the Details panel, go to the Index recommendations tab to get the SQL statement for adding the index. Then, submit a DDL-based lock-free change to execute it. For more information, see Perform lock-free schema changes using a lockless change ticket. Example: `UPDATE test_sql_review_table SET db_id=#{dbId}, gmt_modified = NOW() WHERE detail_id=#{detailId} and is_delete='N'`

Note

For dynamic SQL in an XML file, DMS attempts to replace variables to retrieve the Execution plan for the SQL. If successful, you can view the details of the Execution plan on the Details panel.
You can also manually override the automated SQL review results for issues that cannot be fixed or do not require fixing. Choose Manual Review > Approve or Disapprove.

After you make the adjustments, refresh the page and check the SQL review results again.

Click Submit for Approval. An administrator or a DBA will then review the SQL statements again.
Once approved, the ticket process is complete.
Note
The system reports an error if you click Submit for Approval when any SQL statement has a Check Failed or Parsing Error status.