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.
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 V5.0.
- In the top navigation bar, click Database Development. In the left-side navigation pane, choose . Note If you are using the previous version of the DMS console, move the pointer over the More icon in the top navigation bar and choose .
- On the Apply for SQL Review Ticket page, 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. 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 the SQL review. This reduces unnecessary communication.
The stakeholder involved in the SQL review. You can specify one or more stakeholders. 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 each SQL file 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.
- View the SQL review results.
The following table describes five 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 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 SQL statements modified based on the suggestions provided by the SQL review feature still contain historical issues that cannot be resolved or do not need to be resolved. In the Operation column, choose Disapprove to manually approve or disapprove the review result.or Manual Review > 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.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 statements that use indexes in the Index recommendations section of the Details panel. Then, submit a ticket for 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 parsed by DMS, an error message is returned after you click Submit for Approval in the Approval step.
Related API operations
|CreateSQLReviewOrder||Creates an SQL review ticket.|
|GetSQLReviewCheckResultStatus||Queries the review results of an SQL review ticket.|
|ListSQLReviewOriginSQL||Queries the information about an SQL review ticket.|
|GetSQLReviewOptimizeDetail||Queries the details of optimization suggestions.|