All Products
Search
Document Center

Data Management:SQL review

Last Updated:May 16, 2024

The SQL review feature of Data Management (DMS) reviews the submitted SQL statements and provides suggestions to optimize the statements. This feature helps you maintain high-quality code, such as ensuring that SQL statements conform to database development standards and that indexes are used for queries. This also helps reduce the risk of SQL injection attacks.

Prerequisites

  • One of the following database types is used:

    • ApsaraDB RDS for MySQL, PolarDB for MySQL, AnalyticDB for MySQL, and MySQL databases that are not on Alibaba Cloud

    • ApsaraDB for MariaDB

    • PolarDB for Xscale

    • Oracle

    • PolarDB for PostgreSQL(Compatible with Oracle)

    • OceanBase

  • The database instance is managed in Stable Change or Security Collaboration mode in DMS. For more information, see Control modes.

Background information

During the development phase of 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 obtain optimization suggestions. You can customize SQL specifications in security rules. For example, you can specify that a table must have a primary key and restrict the data type and number of primary key columns. For more information, see SQL review optimization.

Procedure

  1. Log on to the DMS console V5.0.
  2. 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 2023-01-28_15-57-17.png icon in the upper-left corner of the console and choose All Features > Database Development > SQL Review > SQL Audit Ticket.

  3. In the upper-right corner of the SQL ReviewTickets page, click SQL Review.

  4. On the Ticket Application page, configure the parameters that are described in the following table.

    Parameter

    Description

    Project Name

    The name of the project.

    Database

    The database in which SQL statements are executed.

    Note

    You must have query permissions on the database. For more information, see View owned permissions.

    Business Background

    The purpose or objective of the SQL review. You can specify this parameter to help reduce unnecessary communication.

    Change Stakeholder

    The stakeholders involved in the SQL review. All specified stakeholders can view the ticket details and take part in the approval process. Other users aside from DMS administrators and DBAs are not allowed to view the ticket details.

    File

    The method used to upload SQL files. You can use one of the following methods:

    • Upload: You can upload one or more SQL files.

    • Enter text: You can enter SQL statements in the XML format based on the iBATIS or MyBatis framework, or enter SQL statements in the TXT format.

    For more information, see the Sample SQL files section of this topic.

  5. 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 suggestions on how to optimize SQL statements. The suggestions are classified into four categories: Must Improve, Potential Issue, Suggest Improve, and Index Recommendation. For more information, see SQL review optimization.

  6. View the results of the SQL review.

    The following table describes different 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 optimized but may contain potential issues or items that can be optimized.

    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 optimized.

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

    Pending Analysis

    Indicates that the submitted SQL statements are waiting to be identified by DMS.

    N/A

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

Sample SQL files