A Database SQL Control Method and System

Abstract

The present invention belongs to the technical field of data processing, and specifically relates to a database SQL control method and system. The control system is built based on multiple databases. The change request of the database is submitted through the change module, and the change script to be changed is uploaded by the change module to submit a data change work order. The matching syntax module obtains the data change work order uploaded by the change module, extracts the change script therein, and matches the parser containing the adapted syntax rules according to the database type. The change script of the matching syntax module is segmented by the statement segmentation module, and the change script is divided into individual, complete, and executable independent change statements. The present invention is beneficial to avoiding the situation that the entire change script cannot be executed due to abnormal conditions in the change script, affecting the change progress, is beneficial to directly displaying the statements that cannot be changed intuitively, is beneficial to marking and temporarily not executing the risky independent change statements, and avoiding the situation of incorrect entry.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular, to a method and system for controlling database SQL. Background Art

[0002] There are many kinds of databases on the market. The most common databases are traditional databases such as MySQL, Oracle, and SQL Server (also called relational databases or SQL databases).

[0003] Currently, the method for database change is to prepare the change script and upload it to the FTP server, then submit a work order on the internal system PMS. After approval, the operation and maintenance personnel log in to the bastion host on specific 2 - 3 hosts to obtain the change script corresponding to the work order, and then directly connect to the database using traditional database client tools to execute data changes.

[0004] This leads to problems such as copy - paste errors or character set issues during the transfer of change scripts among multiple people due to different operating systems and chat tools. The number of changes involved can reach tens of thousands or even hundreds of thousands. Even if verified in the test environment, it is difficult to completely avoid problems such as syntax errors, non - standardization, and errors in Schema / table / view / field names. Moreover, manual review is slow and errors and omissions are inevitable.

[0005] Furthermore, when executing the change script, once some errors occur, it often causes the entire change script to be unable to complete execution, which will greatly delay the data change progress and requires re - reviewing the change script, undoubtedly delaying the data change time.

[0006] If there are syntax errors or malicious changes in the change script, resulting in the update or deletion of a large amount of data, this will undoubtedly affect the security of the database.

[0007] Therefore, it is necessary to provide a method and system for controlling database SQL to solve the above - mentioned technical problems. Summary of the Invention

[0008] To solve the above - mentioned technical problems, the present invention provides a method and system for controlling database SQL, which splits the change script into independent, complete, and executable independent change statements. This is beneficial to avoiding the situation where the entire change script cannot be executed due to abnormal conditions in the change script, affecting the change progress. It is beneficial to directly display the statements that cannot be changed intuitively, and it is beneficial to mark and temporarily not execute the independent change statements with risks to avoid incorrect input.

[0009] A database SQL control system of the present invention is built based on multiple databases;

[0010] The change request of the database is submitted through the change module. The change module uploads the change script and submits a data change work order.

[0011] The matching syntax module obtains the data change work order uploaded by the change module, extracts the change script therein, and matches a parser with adapted syntax rules according to the database type.

[0012] The change script of the matching syntax module is segmented by the statement segmentation module, and the change script is divided into individual, complete, and executable independent change statements.

[0013] The lexical analysis module forms a tree diagram of the change script and independent change statements in the statement segmentation module, and executes the independent change statements.

[0014] For the independent change statements parsed by the lexical analysis module with reference to the SQL syntax rules, the object analysis module obtains the change objects of the independent change statements and determines whether the change objects involved in the independent change statements exist in the database. If the change objects exist, continue to the next step of review. If the change objects do not exist, mark them as a third-level risk level and give the result.

[0015] The specification analysis module reviews the independent change statements according to the independent change statements reviewed by the lexical analysis module according to the syntax rules, compares one by one the rules of the data operation specification type and some rules in the usage suggestions in the syntax rules, and generates a review result of the second-level risk level for the independent change statements that do not conform to the syntax rules.

[0016] The risk assessment module reviews the execution plan of the independent change statements in the change script, obtains the affected row count of the independent change statements, and generates a review result of the first-level risk level for the independent change statements that exceed the threshold range of the affected row count.

[0017] The audit report module issues a result based on the review of each independent change statement in the change script and summarizes it into a unified audit report.

[0018] Preferably, the database includes the database syntax type, the IP, port, and database user of the database, and the control system can complete data changes for multiple databases simultaneously.

[0019] Preferably, after the statement segmentation module completes the segmentation of the change script to form independent change statements, the independent change statements need to be searched for objects by the object analysis module first, and then executed after being confirmed by the specification analysis module and the risk assessment module.

[0020] Preferably, the statement segmentation method of the statement segmentation module is segmented according to symbols, using semicolons, curly braces, colons, parentheses, quotes, and double quotes as segmentation symbols, and can also be segmented according to whitespace characters.

[0021] Preferably, if no change object is found for the independent change statement by the object analysis module, the review by the specification analysis module and the risk assessment module is not performed, and the review result of the independent change statement is directly made and marked as a third-level risk level.

[0022] Preferably, if a change object is found for the independent change statement by the object analysis module, the review by the specification analysis module and the risk assessment module is performed. After excluding the second-level risk level of the specification analysis module and the first-level risk level of the risk assessment module, the independent change statement is completed through the lexical analysis module and marked in the tree diagram.

[0023] A database SQL control method includes:

[0024] S1: The change module uploads the change script to the matching syntax module and submits a data change work order at the same time;

[0025] S2: The matching syntax module obtains the data change work order uploaded by the change module, extracts the change script therein, and matches a parser containing the adapted syntax rules according to the database type;

[0026] S3: Through the decomposition of the string of the change script input by the parser of the matching syntax module, the change script is segmented by the statement segmentation module, and the change script is divided into individual, complete, and executable independent change statements;

[0027] S31: The object analysis module obtains the change object of the independent change statement and determines whether the change object involved in the independent change statement exists in the database;

[0028] S311: If the involved object exists, the review of this part is completed and the next review is carried out;

[0029] S311: If the involved change object does not exist, the review result of "object does not exist" is output and marked as the review result of the third-level risk level, and the review of other independent change scripts continues;

[0030] S3111: According to the independent change statement reviewed by the lexical analysis module according to the syntax rules, the specification analysis module reviews the independent change statement, compares one by one the rules of the data operation specification type and some rules in the usage suggestions in the syntax rules, and generates a review result of the second-level risk level for the independent change statement that does not conform to the syntax rules;

[0031] S3112: The risk assessment module reviews the execution plan of the independent change statement in the change script, obtains the number of affected rows during the execution of the independent change statement, and generates a review result of the first-level risk level for the independent change statement that exceeds the threshold range of the affected row number;

[0032] S4: Split the independent change statements in the statement splitting module through the lexical analysis module, form a tree diagram for the independent change statements, and complete the execution of the independent change statements;

[0033] S5: According to the review results of each independent change statement in the change script, summarize them into a unified review report through the review report module.

[0034] Compared with related technologies, a database SQL control method and system provided by the present invention have the following beneficial effects:

[0035] By splitting the change script into independent, complete, and executable independent change statements, the present invention can still complete most of the change content when there are abnormal situations in the change script, and at the same time, it can directly display the change content with abnormal situations, which is beneficial to avoiding the entire change script being unable to execute due to abnormal situations in the change script, affecting the change progress, and is beneficial to directly displaying the statements that cannot be changed intuitively.

[0036] The present invention conducts risk review on independent change statements, obtains the change objects of independent change statements through the object analysis module, and judges whether the change objects involved in the independent change statements exist in the database. For independent change statements where the change objects cannot be found, a three-level risk rating review result is formed. The independent change statements are compared one by one according to the SQL review rules, and the independent change statements that do not conform to the SQL review rules are marked to form a second-level risk rating review result. The execution plan of the independent change statements is analyzed to obtain the affected row count of the independent change statements, and the independent change statements with a large range of impacts are marked to form a first-level risk rating review result, which is beneficial to marking and temporarily not executing the independent change statements with risks to avoid incorrect input. Brief Description of the Drawings

[0037] Figure 1 It is a system schematic diagram of a database SQL control method and system provided by the present invention;

[0038] Figure 2 It is a schematic diagram of the statement splitting module of a database SQL control method and system provided by the present invention;

[0039] Figure 3 It is a schematic diagram of the control process of a specific embodiment of a database SQL control method and system provided by the present invention. Detailed Embodiment

[0040] The technical solution of the present invention will be clearly and completely described below in conjunction with the accompanying drawings. Obviously, the described embodiments are part of the embodiments of the present invention, rather than all of the embodiments. All other embodiments obtained by those of ordinary skill in the art based on the embodiments of the present invention without creative efforts shall fall within the protection scope of the present invention.

[0041] Data change is a common work scenario in the production database, and data change is also a high-frequency requirement.

[0042] A database SQL control system: The control system is built based on multiple databases;

[0043] The database includes the database syntax type, the IP, port of the database, and the database user.

[0044] Change module: For the changed database information, upload the script that needs to be changed and submit a data change work order;

[0045] Permission settings are required for logging in to the change module.

[0046] Syntax matching module: Obtain the data change work order, extract the change script therein, and the change script uses SQL language;

[0047] The syntax matching module matches the corresponding syntax parser according to the database type.

[0048] A syntax matching parser is a tool used to analyze and interpret the code structure in a programming language. Its main function is to convert the source code into an internal representation form (such as an abstract syntax tree) so that the compiler or interpreter can further process and execute the code.

[0049] Statement splitting module: Split the change script, and divide the change script into individual, complete, and executable independent change statements;

[0050] After being confirmed by the syntax parser, according to the syntax type of the corresponding database, the change script is split into individual independent change statements;

[0051] The statement splitting method is to split the change script that uses relatively simple semicolons (;), curly braces ({), colons (:), parentheses (()), single quotes and double quotes (' 'and" ") as delimiters, and on this basis, split the complex statements of most stored procedures (procedure), functions (function), rsplit(), etc. programmable objects, and the splitting of the change script can be completed.

[0052] Lexical analysis module: Form a tree diagram from the change script and independent change statements in the statement splitting module, and complete the execution of the independent change statements.

[0053] Divide a completed change script into several independent change statements to form a tree diagram. In case any independent change statement fails to execute or is unrecognizable, it will not interfere with the execution of other independent change statements. Through the tree diagram, the completed independent change statements and the change statements with execution exceptions can be visually viewed, making it more convenient to find the error points.

[0054] Object analysis module: Obtain the change objects of the independent change statements and determine whether the change objects involved in the independent change statements exist in the database.

[0055] If the change objects involved exist, complete the review of this department and proceed to the next review;

[0056] If the change objects involved do not exist, output the review result of "object does not exist", mark it as a third-level risk level, and continue to review other independent change scripts.

[0057] Specification analysis module: Review the independent change statements according to the independent change statements reviewed by the lexical analysis module according to the grammar rules, compare one by one with the rules of the data operation specification type in the grammar rules and some rules in the usage suggestions, and generate a review result with a second-level risk level for the independent change statements that do not conform to the grammar rules.

[0058] SQL review rules: A series of standards and requirements established to ensure the accuracy and security of SQL statements. These rules cover multiple dimensions, including syntax checking, logical judgment, performance limitations, etc. The following are some common SQL review rules:

[0059] Syntax checking: Ensure that the SQL statement conforms to the SQL syntax specification and has no syntax errors.

[0060] Logical judgment: Check whether the logic of the SQL statement is correct to avoid data processing problems caused by logical errors.

[0061] Performance limitation: Limit the SQL statements with too long execution time to prevent affecting the database performance.

[0062] Security audit: Check whether the SQL statement has potential security risks, such as SQL injection attacks, etc.

[0063] Naming specification: Standardize the naming of table names, field names, index names, etc. to improve the readability and maintainability of the code.

[0064] Rules of usage suggestions: Involve the red line of the company's database usage, and prohibit or avoid using some SQL statements that may bring risks.

[0065] Execution plan - based rules: By leveraging the execution plan output, interpret the execution plan characteristics and provide users with prompts for specific SQL statements that affect performance.

[0066] Customized audit rules: According to business requirements and security risks, SQL audit rules can be customized to meet the needs of specific scenarios.

[0067] Risk assessment module: Conduct an audit of the execution plan of individual change statements in the change script to obtain the number of affected rows of the individual change statements. A threshold range can be set for the number of affected rows. For example, if the threshold range exceeds 10 rows, when the number of affected rows of an individual change statement is greater than 10, the individual change statement will be marked and not executed temporarily.

[0068] This risk assessment module mainly targets errors in change scripts. For example, errors in manually copying change scripts;

[0069] A typical example is: for instance, the update statement is incompletely copied, missing the where condition, resulting in a full - table update. Such errors are often very hidden and difficult to detect with the naked eye.

[0070] When an individual change statement belongs to a full - table update, full - table deletion, etc., an audit result with a risk level of first - level is output.

[0071] When an individual change statement belongs to an object deletion statement such as deleting a table or deleting an index, an audit result with a risk level of first - level is output.

[0072] When the number of affected rows of an individual change statement exceeds the threshold set by the business (for example, the set threshold is 500 rows, and executing this statement may affect more than 1000 rows of data), an audit result with a risk level of severe is output.

[0073] Audit report module: Summarize the audit results of each individual change statement in the change script into a unified audit report.

[0074] A method for database SQL control

[0075] This system is built based on a database. The change module uploads the change script to the syntax matching module and submits a data change work order simultaneously. The change script uses the SQL language;

[0076] The syntax matching module is built - in with a syntax parser. The syntax matching module matches the corresponding type of syntax parser according to the database type. There is a separate independent syntax parser for different types of databases, and this independent syntax parser stores the syntax rules of this type of database.

[0077] After being confirmed by the syntax parser, the string of the input change script is decomposed, and the change script is segmented by the statement segmentation module, and the change script is divided into individual, complete, and executable independent change statements.

[0078] The independent change statements in the statement segmentation module are formed into a tree diagram by the lexical analysis module, and the execution of the independent change statements is completed.

[0079] The change objects of the independent change statements are obtained through the object analysis module, and it is judged whether the change objects involved in the independent change statements exist in the database. If the involved objects exist, the review of this part is completed and the next review is carried out;

[0080] If the involved change object does not exist, the review result of "object does not exist" is output and marked as the review result of the third-level risk level, and the review of other independent change scripts continues.

[0081] According to the independent change statements reviewed by the lexical analysis module according to the syntax rules, the independent change statements are reviewed by the specification analysis module, and the rules of the data operation specification type in the syntax rules and some rules in the usage suggestions are compared one by one, and the review results of the second-level risk level are generated for the independent change statements that do not conform to the syntax rules.

[0082] After completing the rule review of the independent change statements, risk control is formed for the SQL statements of the independent change statements, and the syntax errors, logical errors, security risks, naming specifications, and execution rules of the SQL statements are reviewed at the second-level risk level, and the second-level risk review is marked in the tree diagram; the execution plan of the independent change statements in the change script is reviewed by the risk assessment module to obtain the number of affected rows during the execution of the independent change statements.

[0083] When the independent change statement belongs to full-table update, full-table deletion, etc., the review result with the risk level of first level is output.

[0084] When the independent change statement belongs to object deletion statements such as deleting a table, deleting an index, etc., the review result with the risk level of first level is output, and the first-level risk review is marked in the tree diagram.

[0085] For the execution of the independent change statements marked by the second-level risk review and the first-level risk review, the login permissions of the change module need to be verified and a secondary confirmation is made.

[0086] According to the review results of each independent change statement in the change script, a unified review report is summarized through the review report module. Specific embodiments

[0087] By changing the login control system of the module and selecting the data holes that need to have data changed, the change module uploads the change scripts that need to be changed to the syntax matching module, and at the same time submits a data change work order;

[0088] The syntax matching module obtains the data change work order uploaded by the change module, extracts the change scripts therein, matches the parser containing the adaptation syntax rules according to the database type, and can be automatically recognized and manually selected. In this case, the SQL language is adopted;

[0089] Through the string decomposition of the change scripts input by the parser of the syntax matching module, the change scripts are segmented by the statement segmentation module, and the change scripts are divided into individual, complete, and executable independent change statements. For example, the change scripts are segmented into five independent change statements A, B, C, D, and E;

[0090] The statement segmentation method of the statement segmentation module is to segment according to symbols, using semicolons, curly braces, colons, parentheses, single quotes, and double quotes as segmentation symbols, and can also be segmented according to whitespace characters, such as spaces and line breaks.

[0091] The object analysis module obtains the change objects of the five independent change statements A, B, C, D, and E, and judges whether the change objects involved in the independent change statements exist in the database;

[0092] If the objects involved in the four independent change statements A, B, C, and D exist, the review of this part is completed and the next review is carried out;

[0093] If the E independent change statement is marked as having the involved change object not existing, the review result of "object does not exist" is output and marked as a review result with a third-level risk level, and the review of other independent change scripts continues.

[0094] According to the independent change statements reviewed by the lexical analysis module according to the SQL syntax rules, the specification analysis module reviews the independent change statements, compares one by one with the rules of the data operation specification type in the SQL syntax rules and some rules in the usage suggestions, and generates a review result with a second-level risk level for the independent change statements that do not conform to the SQL syntax rules. Among them, the D independent change statement does not conform to the SQL syntax rules;

[0095] Then the D independent change statement is marked with a second-level risk level and a review result is issued.

[0096] The risk assessment module reviews the execution plan of the independent change statements in the change script, obtains the number of affected rows during the execution of the independent change statements, and generates a review result with a first-level risk level for the independent change statements that exceed the threshold range of the affected rows;

[0097] Among them, the number of changed lines of the independent change statement of C exceeds the threshold range, and it is marked as a first-level risk level and the audit result is issued.

[0098] After the above audit is completed, the lexical analysis module is used to split the independent change statements in the statement splitting module, form a tree diagram for the independent change statements, and complete the execution of the independent change statements, that is, complete the independent change statements of A and B.

[0099] According to the audit results of each independent change statement in the change script, a unified audit report is summarized through the audit report module.

[0100] The audit report shows that the independent change statements of A and B have completed data changes. The independent change statement of C is at the first-level risk level, and its number of changed lines is large and requires verification by higher-authority personnel before it can be executed.

[0101] The independent change statement of D is at the second-level risk level, and it has a SQL syntax rule error and needs to be modified.

[0102] The independent change statement of E is at the third-level risk level, and the change object it involves does not exist, and the command needs to be confirmed or the name unified.

[0103] The above are only the embodiments of the present invention, and do not limit the patent scope of the present invention. Any equivalent structure or equivalent process transformation made by using the content of the specification and drawings of the present invention, or directly or indirectly applied in other related technical fields, shall be included in the patent protection scope of the present invention by the same token.

Claims

1. A database SQL management and control system, characterized in that: The management and control system is built based on multiple databases; The database change request is submitted through the change module, which uploads the required change script and submits the data change work order; The matching grammar module obtains the data change work order uploaded by the change module, extracts the change script, and matches the parser containing the adaptive grammar rules according to the database type; The change script matching the syntax module is segmented by the statement segmentation module, and the change script is divided into separate, complete, and executable independent change statements; The lexical analysis module forms a tree diagram of the change script and the independent change statements in the statement segmentation module, and executes the independent change statements; After the lexical analysis module parses the independent change statement according to the SQL grammar rules, the object analysis module obtains the change object of the independent change statement and determines whether the change object involved in the independent change statement exists in the database. If the change object exists, the next step of review will be performed. If the change object does not exist, it will be marked as the third-level risk level and the result will be given; The specification analysis module reviews the independent change statements based on the independent change statements reviewed by the lexical analysis module according to the grammatical rules, compares the data operation specification type rules in the grammatical rules and some rules in the usage suggestions one by one, and generates a second-level risk level review result for the independent change statements that do not comply with the grammatical rules; The risk assessment module reviews the execution plan of the independent change statements in the change script, obtains the number of affected rows of the independent change statements, and generates a first-level risk level review result for the independent change statements that exceed the threshold range of the number of affected rows; The audit report module reviews and issues results based on each independent change statement in the change script and summarizes them into a unified audit report; When the independent change statement belongs to an object deletion statement such as a delete table or delete index, the audit result with a risk level of level 1 is output, and the level 1 risk audit is marked in the tree diagram; The execution of independent change statements marked by the first-level risk review must verify the login permissions of the change module and make a second confirmation.

2. A database SQL management and control system according to claim 1, characterized in that: The database includes the database syntax type, database IP, port and database user. The management and control system completes data changes on multiple databases at the same time.

3. A database SQL management and control system according to claim 1, characterized in that: After the statement segmentation module completes the segmentation of the change script to form independent change statements, the independent change statements must first be searched for objects by the object analysis module, and then confirmed by the specification analysis module and the risk assessment module before execution.

4. A database SQL management and control system according to claim 1, characterized in that: The statement segmentation module's statement segmentation method is based on symbol segmentation, using semicolons, curly braces, colons, parentheses, quotation marks, and double quotation marks as segmentation symbols. It can also segment based on blank characters.

5. A database SQL management and control system according to claim 3, characterized in that: If the object analysis module fails to find the change object for the independent change statement, the standard analysis module and risk assessment module will not be reviewed, and the independent change statement will be directly reviewed and marked as a third-level risk level.

6. A database SQL management and control system according to claim 1, characterized in that: After the object analysis module finds the change object for the independent change statement, the specification analysis module and the risk assessment module are executed for review. After excluding the secondary risk level of the specification analysis module and the primary risk level of the risk assessment module, the independent change statement is executed through the lexical analysis module and marked in the tree diagram.

7. A database SQL management method, applied to a database SQL management system according to any one of claims 1 to 6, characterized in that: include: S1: The change module uploads the script to be changed to the matching syntax module and submits a data change work order; S2: The matching grammar module obtains the data change work order uploaded by the change module, extracts the change script, and matches the parser containing the adaptive grammar rules according to the database type; S3: Decompose the string of the change script input by the parser of the matching grammar module, and segment the change script through the statement segmentation module to divide the change script into separate, complete, and executable independent change statements; S31: obtaining the change object of the independent change statement through the object analysis module, and determining whether the change object involved in the independent change statement exists in the database; S311: If the object involved exists, complete this review and proceed to the next review; S311: If the object involved in the change does not exist, the audit result of "object does not exist" is output and marked as the audit result of the third-level risk level, and the audit of other independent change scripts continues; S3111: Based on the independent change statements reviewed by the lexical analysis module according to the grammatical rules, the specification analysis module reviews the independent change statements, compares the data operation specification type rules in the grammatical rules and some rules in the usage suggestions one by one, and generates a second-level risk level review result for the independent change statements that do not comply with the grammatical rules; S3112: The risk assessment module reviews the execution plan of the independent change statements in the change script, obtains the number of rows affected by the independent change statements during the execution process, and generates a first-level risk level review result for the independent change statements that exceed the threshold range of the number of affected rows; S4: using the lexical analysis module to segment the statements into independent change statements in the module, forming a tree diagram of the independent change statements, and executing the independent change statements; S5: Based on the audit results of each independent change statement in the change script, the audit report module is used to summarize them into a unified audit report.

Images