Database SQL management and control method and system

Abstract

The invention belongs to the technical field of data processing, and particularly relates to a database SQL management and control method and system, and the management and control system is established based on a plurality of databases. A change request of the database is submitted through a change module, and the change module uploads a script needing to be changed and submits a data change work order; a matching grammar module obtains the data change work order uploaded by the change module, extracts a change script in the data change work order, and matches a parser containing an adaptive grammar rule according to a database type; the change script of the matching grammar module is segmented through the statement segmentation module, and the change script is divided into independent change statements which are independent, complete and executable one by one. According to the method, the situation that the whole change script cannot be executed and the change progress is influenced due to the fact that the change script is abnormal is avoided, the statements which cannot be changed can be visually and directly displayed, the independent change statements with risks can be marked and temporarily not executed, and the situation of wrong input is avoided.

Description

Technical Field

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

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

[0003] The current method for changing the database 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 2-3 specific hosts to obtain the change script corresponding to the work order, and then use traditional database client tools to directly connect to the database to perform data changes.

[0004] This results in copy-paste errors or character set problems when the change script is circulated 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 a test environment, it is difficult to completely avoid problems such as syntax errors, non-standardization, and schema / table / view / field name errors. Manual review is slow and errors are inevitable. Moreover, when executing the change script, if some errors occur, the entire change script will often fail to execute, which will greatly delay the progress of the data change. The change script needs to be reviewed, which will undoubtedly delay the data change time. 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.

[0005] Therefore, it is necessary to provide a database SQL management method and system to solve the above technical problems. Summary of the invention

[0006] In order to solve the above technical problems, the present invention provides a database SQL management method and system, which splits the change script into independent, complete and executable independent change statements, which is conducive to avoiding abnormal situations in the change script that cause the entire change script to be unable to execute, affecting the change progress, and is conducive to directly displaying the statements that cannot be changed intuitively, and is conducive to marking risky independent change statements and temporarily not executing them, so as to avoid erroneous entry.

[0007] The present invention provides a database SQL management and control system, and the monitoring 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.

[0008] Preferably, the database includes the database syntax type, database IP, port and database user, and the management and control system can complete data changes on multiple databases at the same time.

[0009] Preferably, after the statement segmentation module completes segmentation of the change script to form independent change statements, the independent change statements need to first go through the object analysis module for object search, and then be executed after confirmation by the specification analysis module and the risk assessment module.

[0010] Preferably, the sentence segmentation module segments the sentences according to symbols, using semicolons, curly braces, colons, parentheses, quotation marks, and double quotation marks as segmentation symbols, and can also segment according to blank characters.

[0011] Preferably, if the object analysis module does not find the change object for the independent change statement, the review of the specification analysis module and the risk assessment module is not performed, and the independent change statement is directly reviewed and marked as a third-level risk level.

[0012] Preferably, after the independent change statement finds the change object through the object analysis module, 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.

[0013] A database SQL management method, comprising: 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.

[0014] Compared with the related art, the database SQL management method and system provided by the present invention has the following beneficial effects: The present invention splits the change script into independent, complete and executable independent change statements. In this way, when there are abnormal situations in the change script, most of the change content can still be completed, and the change content with abnormal situations can be directly displayed, which is conducive to avoiding the situation where the entire change script cannot be executed due to abnormal situations in the change script, affecting the change progress, and is conducive to intuitively displaying the statements that cannot be changed.

[0015] The present invention performs risk audit on independent change statements, obtains the change object of the independent change statement through the object analysis module, and determines whether the change object involved in the independent change statement exists in the database, forms a three-level risk level audit result for the independent change statement for which the change object cannot be found, compares the independent change statements one by one according to the SQL audit rules, marks the independent change statements that do not comply with the SQL audit rules, forms a second-level risk level audit result, analyzes the execution plan of the independent change statement, obtains the number of affected rows of the independent change statement, marks the independent change statements with a wide range of impact, and forms a first-level risk level audit result, which is conducive to marking and temporarily not executing risky independent change statements to avoid incorrect entry. BRIEF DESCRIPTION OF THE DRAWINGS

[0016] Figure 1 A system schematic diagram of a database SQL management and control method and system provided by the present invention; Figure 2 A schematic diagram of a statement segmentation module of a database SQL management method and system provided by the present invention; Figure 3 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 DESCRIPTION

[0017] The technical solution of the present invention will be described clearly and completely below in conjunction with the accompanying drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0018] Data change is a common working scenario in production databases, and data change is also a high-frequency demand.

[0019] A database SQL management and control system: The management and control system is built based on multiple databases; The database contains the database syntax type, database IP, port, and database user.

[0020] Change module: Change database information, upload the required change script, and submit data change work order; To change the module login, permission settings are required.

[0021] Matching syntax module: obtains data change work orders and extracts change scripts from them. The change scripts use SQL language. The matching grammar module matches the grammar parser of the corresponding type according to the database type.

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

[0023] Statement segmentation module: splits the change script into separate, complete, and executable independent change statements; After confirmation by the syntax parser, the change script is segmented into independent change statements according to the syntax type of the corresponding database; Statement segmentation can be used to segment simple change scripts that use semicolons (;), curly braces ({}), colons (:), parentheses (()), quotation marks, and double quotation marks ('' and "") as delimiters. On this basis, it can also segment complex statements of most stored procedures, functions, rsplit(), and other programmable objects to complete the segmentation of change scripts.

[0024] 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.

[0025] A completed change script is divided into several independent change statements to form a tree diagram. If any independent change statement cannot be executed or recognized, it will not interfere with the execution of other independent change statements. The completed independent change statements and the change statements with abnormal execution can be intuitively viewed through the tree diagram, making it easier to find the error points.

[0026] 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.

[0027] If the object involved in the change exists, complete the review of this department and proceed to the next review; If the object involved in the change does not exist, the audit result of "object does not exist" is output and marked as a level 3 risk, and the audit of other independent change scripts continues.

[0028] Specification analysis module: Audits independent change statements based on the independent change statements audited 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 audit result for independent change statements that do not comply with the grammatical rules.

[0029] SQL audit 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 restrictions, etc. The following are some common SQL audit rules: Syntax check: Ensure that the SQL statement complies with SQL syntax specifications and has no syntax errors.

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

[0031] Performance Limitation: Limit SQL statements that take too long to execute to prevent them from affecting database performance.

[0032] Security audit: Check whether SQL statements have potential security risks, such as SQL injection attacks.

[0033] Naming standards: Follow naming standards for table names, field names, index names, etc. to improve code readability and maintainability.

[0034] Use recommendation rules: Use red lines for the company's database, prohibiting or avoiding the use of certain SQL statements that may bring risks.

[0035] Execution plan rules: With the help of execution plan output, interpret the execution plan characteristics and give users prompts for specific SQL statements that affect performance.

[0036] Customized audit rules: Based on business needs and security risks, you can customize SQL audit rules to meet the needs of specific scenarios.

[0037] Risk Assessment Module: Review the execution plan of independent change statements in the change script to obtain the number of rows affected by the independent change statements. You can set a threshold range for the number of rows affected. For example, if the threshold range exceeds >10, when the number of rows affected by an independent change statement is greater than 10, the independent change statement will be marked and not executed temporarily.

[0038] This risk assessment module focuses on change script errors, for example, human copying of change script errors; A typical example is: for example, the update statement is not copied completely, the where condition is omitted, resulting in the update of the entire table. This type of error is often very hidden and difficult to detect with the naked eye.

[0039] When the independent change statement belongs to the whole table update, whole table delete, etc., the audit result with risk level 1 is output.

[0040] When the independent change statement is an object deletion statement such as deleting a table or deleting an index, the audit result with a risk level of level 1 is output.

[0041] When the number of rows affected by an independent change statement exceeds the threshold set by the business (for example, if the threshold is set to 500 rows, executing the statement may affect more than 1,000 rows of data), the audit result with a serious risk level is output.

[0042] Audit report module: summarizes the audit results of each independent change statement in the change script into a unified audit report.

[0043] A database SQL management method This system is built on the basis of database. The change module uploads the script to be changed to the matching syntax module and submits the data change work order at the same time. The change script adopts SQL language. The matching grammar module has a built-in grammar parser, which matches the grammar parser of the corresponding type according to the database type. There are separate independent grammar parsers for different types of databases, and the independent grammar parsers store the grammar rules of the database of that type.

[0044] After confirmation by the syntax parser, the input change script string is decomposed and segmented by the statement segmentation module to separate the change script into individual, complete, and executable independent change statements.

[0045] The lexical analysis module is used to segment the statements into independent change statements in the statement segmentation module, the independent change statements are formed into a tree diagram, and the independent change statements are executed.

[0046] Obtain the change object of the independent change statement through the object analysis module, and determine whether the change object involved in the independent change statement exists in the database. If the object involved exists, complete the current review and proceed to the next review; 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 risk level, and the audit of other independent change scripts continues.

[0047] 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 rules of the data operation specification type 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.

[0048] Complete the rule review of independent change statements, form risk control for the SQL statements of independent change statements, conduct a secondary risk level review of the syntax errors, logical errors, security risks, naming specifications, and execution rules of SQL statements, and mark the secondary risk review in the tree diagram; the risk assessment module reviews the execution plan of the independent change statements in the change script to obtain the number of rows affected by the independent change statements during the execution process.

[0049] When the independent change statement belongs to the whole table update, whole table delete, etc., the audit result with risk level 1 is output.

[0050] 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 one is output, and the level one risk audit is marked in the tree diagram.

[0051] The execution of independent change statements marked by the second-level risk review and the first-level risk review requires verification of the login permissions of the change module and a second confirmation.

[0052] Based on the audit results of each independent change statement in the change script, they are summarized into a unified audit report through the audit report module. Specific embodiments

[0053] Log in to the control system through the change module and select the data hole that needs to be changed. The change module will upload the script to be changed to the matching syntax module and submit the data change work order at the same time. 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. This can be done through automatic identification and manual selection. This time, SQL language is used; After the parser of the matching grammar module decomposes the string of the change script input, the statement segmentation module segments the change script into separate, complete, and executable independent change statements. For example, the change script is segmented into five independent change statements A, B, C, D, and E. The statement segmentation module's statement segmentation method is based on symbol segmentation, using semicolons, curly braces, colons, parentheses, quotation marks, and double quotes as segmentation symbols. It can also segment based on whitespace characters, such as spaces and newlines.

[0054] Obtain the change objects of five independent change statements A, B, C, D, and E through the object analysis module, and determine whether the change objects involved in the independent change statements exist in the database; If the objects involved in the four independent change statements A, B, C, and D exist, then this review is completed and the next review is carried out; If the E independent change statement is marked as involving a change object that does not exist, the "object does not exist" audit result is output and marked as a level 3 risk audit result, and the audit of other independent change scripts continues.

[0055] According to the independent change statements reviewed by the lexical analysis module according to the SQL grammar rules, the specification analysis module reviews the independent change statements, compares the rules of the data operation specification type in the SQL grammar 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 SQL grammar rules. Among them, D independent change statements do not comply with the SQL grammar rules; Then D independent change statement is marked as level 2 risk and an audit result is issued.

[0056] 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; Among them, the number of change lines of C independent change statement exceeds the threshold range, and it is marked as the first-level risk level and an audit result is issued. After completing the above review, the lexical analysis module is used to divide the statements into independent change statements in the module, the independent change statements are formed into a tree diagram, and the independent change statements are executed, that is, the independent change statements of A and B are completed; Based on the audit results of each independent change statement in the change script, they are summarized into a unified audit report through the audit report module.

[0057] The audit report shows that the independent change statements of A and B have completed the data change. The independent change statement of C is at the first-level risk level. It has a large number of change lines and needs to be checked and confirmed by personnel with higher authority before it can be executed. D Independent change statement is at level 2 risk, it contains SQL syntax rule errors and needs to be modified; E Independent change statements are at the third risk level. The change object involved does not exist and the command needs to be confirmed or the name needs to be unified.

[0058] The above descriptions are merely embodiments of the present invention and are not intended to limit the patent scope of the present invention. Any equivalent structure or equivalent process transformation made using the contents of the present invention specification and drawings, or directly or indirectly applied in other related technical fields, are also included in the patent protection scope of the present invention.

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.

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 can complete 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 quotes 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 7, 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