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
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
- Log on to the DMS console V5.0.
In the top navigation bar, choose .
NoteIf you use the DMS console in simple mode, move the pointer over the
icon in the upper-left corner of the console and choose . In the upper-right corner of the SQL ReviewTickets page, click SQL Review.
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.
NoteYou 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
XMLformat based on the iBATIS or MyBatis framework, or enter SQL statements in theTXTformat.
For more information, see the Sample SQL files section of this topic.
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.
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 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
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.
NoteIf 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.