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, click Database Development. In the left-side navigation pane, choose 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.
    ParameterDescription
    Project NameThe name of the project.
    DatabaseThe database in which SQL statements are executed.
    Note You must have query permissions on the database. For more information, see View owned permissions.
    Business BackgroundThe purpose or objective of the SQL review. You can specify this parameter to help reduce unnecessary communication.
    Change StakeholderThe 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.
    Upload a fileThe 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.

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

    FailedIndicates 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 DisapproveIndicates 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 ExceptionIndicates 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 AnalysisIndicates 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