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. This reduces the risk of SQL injection attacks.

Prerequisites

one of the following database types: MySQL, ApsaraDB RDS for MySQL, PolarDB for MySQL, MariaDB, PolarDB-X, AnalyticDB for MySQL, and ApsaraDB OceanBase for MySQL.

Background information

When you develop a project, you must execute SQL statements to add, delete, modify, and query data in a database so that you can implement business logic and display data. Before the 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. For example, the following table creation statement does not contain a primary key, table remarks, or field description:
CREATE TABLE `test_sql_review_table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `gmt_create` datetime NOT NULL,
  `name` varchar(256) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

If database administrators (DBAs) manually review all SQL statements one by one, a large number of labor resources are required. This reduces the development efficiency.

In view of this, 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 data type of a primary key column must be restricted, and the number of primary key columns must be restricted. For more information, see SQL review optimization.

Procedure

  1. Log on to the DMS console.
  2. In the top navigation bar, choose All functions > Optimization > SQL Review.
  3. On the Apply for SQL Review Ticket tab, set the parameters that are described in the following table.
    Table 1. Parameters
    Parameter Description
    Project Name The name of the project. This reduces unnecessary communication.
    Databases The database where the SQL statements are executed.
    Note You must have permissions to query the database. For more information, see View owned permissions.
    Business Background

    The purpose or objective of the SQL review. This reduces unnecessary communication.

    Change Stakeholder

    The one or more stakeholders of the SQL review. All specified stakeholders can view the ticket details and assist in the approval process. Irrelevant users other than DMS administrators and DBAs are not allowed to view the ticket details.

    Upload a file The SQL statements to be reviewed. Click Upload to upload one or more SQL files.
    The following types of SQL files are supported:
    • SQL files in the XML format based on the iBATIS or MyBatis framework
    • SQL files in the TXT format
    For more information, see Sample uploaded SQL files.
  4. Click Submit.
    DMS parses the SQL file and reviews each SQL statement based on the security rules that are configured for this database. After the reviews, 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 following table describes five types of review results that DMS provides.

    State Description Action
    Passed Indicates that the submitted SQL statements do not contain items that must be improved but may contain potential issues or items that can be improved. To view the SQL statements and optimization suggestions, click Details in the Operation column.

    To modify the SQL statements, click Adjust SQL in the Operation column.

    Failed Indicates that the submitted SQL statements contain items that must be improved. To view optimization suggestions, click Details in the Operation column. To modify the SQL statements, click Adjust SQL in the Operation column.
    Approve or Disapprove Indicates that the submitted SQL statements contain specific historical SQL issues that cannot or do not need to be resolved, in addition to the SQL review results that are provided by the SQL review feature of DMS. In the Operation column, choose Manual Review Approve or Manual Review >Disapprove to manually approve or disapprove the review result.
    Parsing Exception Indicates the submitted SQL statements cannot be identified by DMS. Click Adjust SQL in the Operation column to modify the SQL statements. Then, submit the modified SQL statements for precheck again.
    Note DMS analyzes and recommends indexes for the submitted SQL statements. If Index Recommendation is displayed in the Check Result section, you can obtain the SQL statement used to add indexes on the Index recommendations tab of the Details panel. Then, submit a ticket for data definition language (DDL)-based lockless change. For more information, see DDL-based lockless change.
  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 identified by DMS, an error message is returned after you click Submit for Approval in the Approval step.

Sample uploaded SQL files