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

.

Background information

When you develop a project, you need to execute SQL statements on databases to add, delete, modify, and query data 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 and accordingly impeding the business. For example, the primary key, remarks on the table, remarks on the fields are not specified in the following CREATE TABLE statement:
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, excessive human resources are consumed and the R&D efficiency is low.

The SQL review feature of DMS can review SQL statements and provide optimization suggestions on SQL statements based on security rules. You can configure a security rule to customize SQL specifications. For example, the table must contain the primary key, only specific types of columns can be specified as the primary key, and the number of primary key columns cannot exceed a specific limit. 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. Configure information about an SQL review ticket.
    Parameter Description
    Project Name The name of the project. This reduces unnecessary communication.
    Databases The database on which the SQL statements to be reviewed are executed.
    Note You must have the read permissions on 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

    Optional. The one or more stakeholders of the SQL review ticket. 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 Click Upload to upload an SQL file. You can upload multiple files.
    The following types of files are supported:
    • iBATIS and MyBatis files in the XML format
    • TXT files of SQL statements
    For more information, see Sample SQL files that you can upload.
  4. Click Submit.
    Then, DMS parses the SQL file and reviews the SQL statements one by one based on the security rules of the specified database. You can view the number of optimization suggestions of each type next to SQL audit optimization recommendations. The types are Must Improve, Potential Issue, Suggest Improve, and Index Recommendation. For more information, see SQL review optimization.
  5. View SQL review results.

    The following table describes five types of review results that DMS provides.

    Result Description Action
    Passed The SQL statement does not contain items that must be improved but may contain potential issues or items that can be improved. To view the SQL statement and optimization suggestions, click Details in the Operation column.

    To adjust the SQL statement, click Adjust SQL in the Operation column.

    Failed The SQL statement contains items that must be improved. To view optimization suggestions, click Details in the Operation column. To adjust the SQL statement, click Adjust SQL in the Operation column.
    Approve or Disapprove The SQL statement contains historical issues that cannot be resolved or do not need to be resolved. In this case, the SQL statement requires manual review. To manually review the SQL statement, click Manual Review and select Approve or Disapprove.
    Parsing Exception The SQL statement cannot be parsed by DMS. Click Adjust SQL to adjust the SQL statement. Then, recheck the adjusted SQL statement.
  6. Click Submit for Approval. Then, a DMS administrator or DBA reviews the SQL statement again.
    After the ticket is approved, the SQL review is complete.
    Note If the review result of specific SQL statements is Failed or Parsing Exception, an error message appears after you click Submit for Approval.

Sample SQL files that you can upload