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:
  • ApsaraDB RDS for MySQL databases, PolarDB for MySQL databases, AnalyticDB for MySQL databases, and MySQL databases that are not on Alibaba Cloud
  • ApsaraDB RDS for MariaDB
  • PolarDB-X
  • Oracle
  • PolarDB O
  • 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. 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, set the parameters that are described in the following table.
    Parameter Description
    Project Name The name of the project.
    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 this export. You can specify this parameter to help reduce unnecessary communication.
    Change Stakeholder The stakeholders involved in the change. 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.
    An object is uploaded The method in which SQL files are uploaded.
    • Upload: You can upload multiple SQL files.
    • Enter text: You can enter SQL statements in the XML format based on the iBatis or MyBatis framework or SQL statements in the TXT format.
    For more information, see Sample uploaded SQL files.
  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 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.
  6. View the result of the SQL review.

    The following table describes five types of review results that are provided by DMS.

    Status 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 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 is 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 uploaded SQL files