All Products
Search
Document Center

SQL review

Last Updated: May 09, 2020

Data Management Service (DMS) provides the SQL review feature to avoid publishing SQL statements that do not use indexes and SQL statements that do not conform to database development standards, such as SQL injection statements and SELECT *. This topic describes how to use the SQL review feature.

Background

When you develop a project, you need to execute CREATE, READ, UPDATE, and DELETE (CRUD) SQL statements on databases so that you can implement business logic and display data. Before the project is published, it is necessary to review all SQL statements that are updated. This avoids publishing SQL statements that do not conform to database development standards to the production environment, which may impede the business.It is a labor-consuming job for database administrators (DBAs) to manually review all of these SQL statements. Also, manual review reduces the development efficiency. DMS offers the SQL review feature to automatically review SQL statements and provide optimization suggestions.

Important notes

  • SQL review is performed before DMS publishes SQL statements to the production environment. Therefore, this feature is designed for database resources marked with Test in the DMS console.
  • Tables that are involved in the SQL statements must exist in the target database. Otherwise, DMS cannot review these SQL statements and offer suggestions on indexes.
  • The SQL review feature currently supports SQL files in the format of .xml and .txt, and will support the .sql format in the future.

Procedure

  1. Log on to the DMS console.

  2. In the top navigation bar, choose Optimization > SQL Review.

  3. On the SQL Review page that appears, click SQL Review.

  4. On the Create SQL Review page that appears, set the parameters described in the following table.

    Parameter Description
    Project Name The name of the project. We recommend that you enter a project name that reflects the actual requirements so that the ticket can be distinguished from others in subsequent processing.
    Data Source The database used in the project. This example uses a database in the test environment. You are required to have the change permission on the target database.
    Business Description The business description of the project. Enter detailed information about the business scope of the project as required so that stakeholders can quickly know about the project.
    Stakeholder The stakeholders of the project. Enter an at sign (@) and select a target user from the drop-down list that appears. You can select multiple target users.
    Upload Files Click Add, select target files, and then click Upload.

  5. After setting the preceding parameters, click Submit.

  6. View SQL review results.

    • If SQL statements in the ticket conform to database development standards and use indexes, DMS determines these SQL statements as Passed and offers no suggestion on indexes.
    • If SQL statements in the ticket conform to database development standards and use no index, DMS determines these SQL statements as Passed and offers suggestions on indexes.
    • If SQL statements in the ticket do not conform to database development standards, DMS determines these SQL statements as Failed.
  7. Click View Reasons to check reasons for failed SQL files. You can also click Details, Modify SQL, or More in the Actions column to perform other operations.

    Note: After you modify an SQL statement to make it conform to database development standards and click OK, DMS reviews the SQL file again. For dynamic SQL files in the .xml format, you must modify each SQL statement combination.

  8. When all SQL files are in the Passed state, click Check Result. In the dialog box that appears, check your ticket and click Submit for Approval to submit the ticket for approval.

    The approval process is based on the security rules for the target database instance.