The SQL review feature of Data Management (DMS) reviews the submitted 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 engines is used:
  • MySQL: ApsaraDB RDS for MySQL databases, PolarDB for MySQL databases, MariaDB databases, PolarDB-X databases, AnalyticDB for MySQL databases, and MySQL databases that are not on Alibaba Cloud
  • Oracle
  • PolarDB for Oracle
  • OceanBase

Background information

When you develop a project, you must execute SQL statements to insert, 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 CREATE TABLE statement does not contain a primary key, table comments, or field comments:
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 and 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. Go 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 that are described in the following table.
    Table 1. Parameters
    Parameter Description
    Project Name The name of the project. This reduces unnecessary communication.
    Database The database where 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 stakeholder involved in the SQL review. You can specify one or more stakeholders. 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 each SQL file 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 following table describes five types of review results that are provided by DMS.

    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 SQL statements modified based on the suggestions provided by the SQL review feature still contain historical issues that cannot be resolved or do not need to be resolved. In the Operation column, choose Manual Review > Approve or Manual Review > Disapprove to manually approve or disapprove the review result.
    Parsing Exception Indicates that 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 statements that use indexes in the Index recommendations section of the Details panel. Then, submit a ticket for DDL-based lockless change. For more information, see Perform lock-free DDL operations.
  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.

Related API operations

API operation Description
CreateSQLReviewOrder Creates an SQL review ticket.
GetSQLReviewCheckResultStatus Queries the review results of an SQL review ticket.
ListSQLReviewOriginSQL Queries the information about an SQL review ticket.
GetSQLReviewOptimizeDetail Queries the details of optimization suggestions.

Sample uploaded SQL files