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.
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 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.
- Log on to the DMS console.
- In the top navigation bar, choose .
- On the Apply for SQL Review Ticket tab, set the parameters that are described in the following table.
Table 1. Parameters Parameter Description Project Name The name of the project. This reduces unnecessary communication. Databases The database where the 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 the SQL review. This reduces unnecessary communication.
The one or more stakeholders of the SQL review. 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 The SQL statements to be reviewed. Click Upload to upload one or more SQL files.The following types of SQL files are supported:
- SQL files in the
XMLformat based on the iBATIS or MyBatis framework
- SQL files in the
- SQL files in the
- Click Submit.
DMS parses the SQL file and reviews each SQL statement based on the security rules that are configured for this database. After the reviews, 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.
- View the SQL review results.
The following table describes five types of review results that DMS provides.
State 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 Approve or Manual Review >Disapprove to manually approve or disapprove the review result. Parsing Exception Indicates 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.Note DMS analyzes and recommends indexes for the submitted SQL statements. If Index Recommendation is displayed in the Check Result section, you can obtain the SQL statement used to add indexes on the Index recommendations tab of the Details panel. Then, submit a ticket for data definition language (DDL)-based lockless change. For more information, see DDL-based lockless change.
- 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 identified by DMS, an error message is returned after you click Submit for Approval in the Approval step.