CN112783916A - SQL statement auditing method and device, storage medium and electronic equipment - Google Patents
SQL statement auditing method and device, storage medium and electronic equipment Download PDFInfo
- Publication number
- CN112783916A CN112783916A CN202110004915.XA CN202110004915A CN112783916A CN 112783916 A CN112783916 A CN 112783916A CN 202110004915 A CN202110004915 A CN 202110004915A CN 112783916 A CN112783916 A CN 112783916A
- Authority
- CN
- China
- Prior art keywords
- sql statement
- sql
- auditing
- database
- rule
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000000034 method Methods 0.000 title claims abstract description 85
- 238000012550 audit Methods 0.000 claims description 39
- 238000010586 diagram Methods 0.000 description 4
- 230000000694 effects Effects 0.000 description 2
- 230000006870 function Effects 0.000 description 2
- 238000012217 deletion Methods 0.000 description 1
- 230000037430 deletion Effects 0.000 description 1
- 238000005516 engineering process Methods 0.000 description 1
- 230000003203 everyday effect Effects 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 230000000750 progressive effect Effects 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/40—Transformation of program code
- G06F8/41—Compilation
- G06F8/42—Syntactic analysis
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- General Engineering & Computer Science (AREA)
- Physics & Mathematics (AREA)
- General Physics & Mathematics (AREA)
- Databases & Information Systems (AREA)
- Data Mining & Analysis (AREA)
- Software Systems (AREA)
- Mathematical Physics (AREA)
- Computational Linguistics (AREA)
- Stored Programmes (AREA)
- Debugging And Monitoring (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application provides a method and a device for auditing SQL statements, a storage medium and electronic equipment, wherein the method comprises the following steps: acquiring an SQL statement set; judging whether the grammar of each SQL statement is correct or not; if the grammars of all the SQL sentences are correct, auditing each SQL sentence according to preset auditing rules; and if all the SQL statements meet the auditing rule, determining that the SQL statement set passes the auditing. According to the technical scheme, the syntax of each SQL statement in the SQL statement set is automatically checked, and the SQL statements are checked based on the checking rules under the condition that the syntax of all the SQL statements is correct, so that the whole process does not need any manual intervention, a large amount of time cost and labor cost are saved, and the SQL statement checking efficiency is greatly improved.
Description
Technical Field
The present application relates to the field of database technologies, and in particular, to a method and an apparatus for auditing SQL statements, a storage medium, and an electronic device.
Background
With the increase of business volume of enterprises, the number of databases of application systems of the enterprises is correspondingly increased, and data in the databases needs to be queried, updated or otherwise operated every day, and the operations are generally realized by using a Structured Query Language (SQL).
In the process of operating the database by using the SQL statement, if the SQL statement is not standardized, the database operation may be wrong, and the overall performance of the database may be reduced. Therefore, a review of the SQL statements that are executed into the database is required.
In the prior art, the SQL statement is checked manually, and under the condition of large data volume of the SQL statement, a large amount of time cost and labor cost are consumed, so that the efficiency is low.
Content of application
The technical problem to be solved by the application is to provide a method and a device for auditing an SQL statement, a storage medium and an electronic device, and the method and the device aim at solving the problems that a large amount of time cost and labor cost are needed to be consumed, and the efficiency is low.
In order to achieve the above object, the present application provides the following technical solutions:
an SQL statement auditing method comprises the following steps:
acquiring an SQL statement set; the SQL statement set comprises at least one SQL statement;
judging whether the grammar of each SQL statement is correct or not;
if the grammars of all the SQL sentences are correct, auditing each SQL sentence according to preset auditing rules;
and if all the SQL statements meet the auditing rule, determining that the SQL statement set passes the auditing.
Optionally, the method for acquiring the SQL statement set includes:
acquiring an SQL file; the SQL file at least comprises an SQL text and a database identifier corresponding to the SQL text;
carrying out SQL statement division on the SQL text to obtain a plurality of SQL statements;
and (4) forming all SQL sentences into an SQL sentence set.
Optionally, the method for determining whether the syntax of each SQL statement is correct includes:
constructing an abstract syntax tree for each SQL statement; judging whether the abstract syntax tree corresponding to the SQL statement is successfully constructed; and if the abstract syntax tree corresponding to the SQL statement is successfully constructed, determining that the syntax of the SQL statement is correct.
Optionally, the method for auditing each SQL statement according to a preset auditing rule includes:
traversing an abstract syntax tree corresponding to each SQL statement to acquire node information of each node in the abstract syntax tree, and acquiring each database object corresponding to the SQL statement based on each node information;
judging whether each database object corresponding to each SQL statement meets a preset auditing rule or not, if any database object does not meet the auditing rule, judging that the SQL statement does not meet the auditing rule, and if each database object corresponding to the SQL statement meets the auditing rule, determining that the SQL statement meets the auditing rule.
Optionally, the method for determining whether each database object corresponding to the SQL statement meets a preset audit rule includes:
checking the existence and the constraint of each database object corresponding to the SQL statement based on a target database; the target database is a database corresponding to the database identification contained in the SQL file;
when all database objects meet the examination of existence and constraint, judging whether the naming rule of each database object meets a preset custom rule;
if all the database correspondences meet the custom rule, counting the performance index value of the SQL statement based on all the database objects corresponding to the SQL statement;
and when the performance index value of the SQL statement is smaller than a preset performance threshold value, determining that each database object corresponding to the SQL statement meets a preset auditing rule.
The above method, optionally, further includes:
if the syntax of any SQL statement is incorrect or any SQL statement does not satisfy the auditing rule, determining that the SQL statement set does not pass the auditing
Optionally, in the method, after it is determined that the SQL statement set passes the audit, the method further includes:
generating audit report information and storing the audit report information;
and visually displaying the audit report information.
An SQL statement auditing device comprises:
the acquisition unit is used for acquiring an SQL statement set; the SQL statement set comprises at least one SQL statement;
the judging unit is used for judging whether the grammar of each SQL statement is correct or not;
the checking unit is used for checking each SQL statement according to a preset checking rule if the syntax of all the SQL statements is correct;
and the determining unit is used for determining that the SQL statement set passes the examination if all the SQL statements meet the examination rule.
A storage medium comprises stored instructions, wherein when the instructions are executed, the storage medium is controlled to be located in a device to execute the SQL statement auditing method.
An electronic device comprising a memory and one or more instructions, wherein the one or more instructions are stored in the memory and configured to be executed by one or more processors to perform the SQL statement auditing method.
Compared with the prior art, the method has the following advantages:
the application provides a method and a device for auditing SQL statements, a storage medium and electronic equipment, wherein the method comprises the following steps: acquiring an SQL statement set; judging whether the grammar of each SQL statement is correct or not; if the grammars of all the SQL sentences are correct, auditing each SQL sentence according to preset auditing rules; and if all the SQL statements meet the auditing rule, determining that the SQL statement set passes the auditing. According to the technical scheme, the syntax of each SQL statement in the SQL statement set is automatically checked, and the SQL statements are checked based on the checking rules under the condition that the syntax of all the SQL statements is correct, so that the whole process does not need any manual intervention, a large amount of time cost and labor cost are saved, and the SQL statement checking efficiency is greatly improved.
Drawings
In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings needed to be used in the description of the embodiments or the prior art will be briefly introduced below, it is obvious that the drawings in the following description are only embodiments of the present application, and for those skilled in the art, other drawings can be obtained according to the provided drawings without creative efforts.
Fig. 1 is a flowchart of a method of an SQL statement auditing method according to the present application;
FIG. 2 is a flowchart of another method of an SQL statement review method provided in the present application;
FIG. 3 is a flowchart of another method of an SQL statement review method provided by the present application;
FIG. 4 is a flowchart of another method of an SQL statement review method provided in the present application;
fig. 5 is a schematic structural diagram of an SQL statement auditing apparatus provided in the present application;
fig. 6 is a schematic structural diagram of an electronic device provided in the present application.
Detailed Description
The technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the drawings in the embodiments of the present application, and it is obvious that the described embodiments are only a part of the embodiments of the present application, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present application.
The embodiment of the application provides an SQL statement auditing method, which can be applied to an SQL quality auditing device based on a cloud platform, the system can adapt to different text editors in a plug-in mode, and a method flow chart of the method is shown in fig. 1, and specifically comprises the following steps:
s101, acquiring an SQL statement set.
And editing the SQL sentences on the text editor by the user according to the requirements, saving the edited SQL sentences as SQL texts, and saving the SQL texts in an SQL file and uploading the SQL files to the SQL quality auditing device. The SQL quality auditing device receives an SQL file and acquires an SQL statement set based on the SQL file, wherein the SQL statement set comprises at least one SQL statement.
Referring to fig. 2, the process of acquiring the SQL statement set specifically includes:
s201, obtaining the SQL file.
The method comprises the steps of obtaining an SQL file uploaded by a user, wherein the SQL file at least comprises an SQL text and a database identifier, the SQL text comprises at least one SQL statement, the SQL statements are separated by preset separators, and the database identifier is information for identifying the database and comprises but is not limited to type information of the database, ip addresses of the database, port information for accessing the database, user names of the database and passwords.
Optionally, the user may upload the SQL file manually, or may upload the SQL file in a timing triggering manner.
S202, SQL sentence division is carried out on the SQL text to obtain a plurality of SQL sentences.
The method comprises the steps of carrying out SQL sentence division on an SQL text according to a preset division mode, specifically, identifying each preset separator contained in the SQL text, and carrying out SQL sentence division on the SQL text based on each preset separator to obtain a plurality of SQL sentences.
S203, forming an SQL statement set by all SQL statements.
In the method provided by the embodiment of the application, SQL sentences are divided for SQL texts in an SQL file, so that a plurality of SQL sentences contained in the SQL text are obtained, and all the SQL sentences form an SQL sentence set.
S102, judging whether the grammar of each SQL statement is correct, if so, executing a step S103, and if not, executing a step S104.
And analyzing the grammar structure of each SQL statement based on a grammar analysis engine to obtain an analysis result, and judging whether the grammar of each SQL statement is correct or not based on the analysis result.
It should be noted that, each SQL statement in the SQL statement set is arranged in a preset sequence, the preset sequence is related to the subsequent execution sequence of each SQL statement in the database, and if any SQL statement has a syntax error, the execution of other SQL statements in the database is affected, so that it is necessary to determine whether the syntax of each SQL statement is correct.
Referring to fig. 3, the process of determining whether the syntax of each SQL statement is correct specifically includes:
s301, constructing an abstract syntax tree for each SQL statement.
For each SQL statement, constructing an abstract syntax tree for the SQL statement based on a syntax parsing engine, specifically, parsing a syntax structure of the SQL statement by the syntax parsing engine to obtain a parsing result, acquiring a preset standard syntax structure corresponding to the statement type of the SQL statement, comparing the parsing result with the standard syntax structure, and if the parsing result is consistent with the standard syntax structure, constructing the abstract syntax tree corresponding to the SQL statement based on the parsing result; and if the analysis result is inconsistent with the standard syntax structure, outputting prompt information of failure in constructing the abstract syntax tree. The abstract syntax tree is used for carrying out abstract representation on the syntax structure of the SQL statement in a tree form; the syntax structure of SQL is a statement form of SQL statements, which are types of operating databases, e.g., create tables, delete tables, and modify tables.
Optionally, the syntax parsing engine may be sql parser, which is a module component in the open source item of aliba, and is implemented by Java, which provides syntax parsing support for various databases.
S302, judging whether the abstract syntax tree corresponding to each SQL statement is successfully constructed or not, if so, executing a step S303, and if not, executing a step S304.
For each SQL statement, if the abstract syntax tree corresponding to the SQL statement is successfully constructed, it indicates that the syntax of the SQL statement is correct, because only the SQL statement with the correct syntax can construct the abstract syntax tree corresponding to the SQL statement.
If the abstract syntax tree corresponding to the SQL statement is not successfully constructed, the SQL statement is proved to have syntax errors.
S303, determining that the syntax of the SQL statement is correct.
S304, determining the syntax error of the SQL statement.
If the abstract syntax tree corresponding to any SQL statement is not successfully constructed, the SQL statement set has the SQL statement with incorrect syntax.
In the method provided by the embodiment of the application, the abstract syntax tree of the SQL sentences is constructed based on the syntax parsing engine, and whether the syntax of all the SQL sentences is correct is judged by judging whether the abstract syntax tree corresponding to each SQL sentence is successfully constructed.
S103, auditing each SQL statement according to a preset auditing rule.
And auditing each SQL statement according to a preset auditing rule, and specifically auditing each database object corresponding to each statement according to the preset auditing rule. The auditing rule is a basis for judging the existence, the constraint and the naming of each database object corresponding to the SQL statement and judging whether the performance of the target database is affected by the execution of the SQL statement. The target database is a database corresponding to the target database identifier, and the target database identifier is a database identifier corresponding to the SQL statement set; the existence of the database object is that the database object exists in the target database, or the SQL statement set comprises a target SQL statement for creating the database object; the target SQL statement is located in the SQL statement set before the SQL statement to which the database object belongs.
The constraint of the database object is that the target database does not have the constraint condition of executing the database object, or the target database has the constraint condition of executing the database object, but the SQL statement set comprises the target SQL statement meeting the constraint condition; the target SQL statement is located in the SQL statement set before the SQL statement to which the database object belongs.
In the method provided by the embodiment of the application, whether the performance of the target database is affected by the execution of the SQL statement is checked based on the performance rule, and the performance rule is a basis for judging whether the performance of the target database is affected by the execution of the SQL statement.
The process of auditing the presence of database objects is illustrated as follows:
the SQL statement is used for deleting data with preset ID in the person table, and the process of auditing the existence of the database object person table contained in the SQL statement comprises the following steps: judging whether the person table does not exist in the target database, if so, determining that the person table of the database object passes the existing examination, if not, judging whether an SQL statement for creating the person table exists before the SQL statement to which the database object belongs in the SQL statement set based on the arrangement sequence of all the SQL statements in the SQL statement set, if so, determining that the examination passes the existing examination, otherwise, failing to pass the existing examination.
The process of auditing the constraints of a database object is illustrated as follows:
the SQL statement is used for deleting data with a preset ID in a target table (such as a student table), and the process of checking the constraint of the data with the preset ID of the deletion mentioned in the SQL statement is as follows: judging whether constraint conditions exist in data of which the IDs in the deleted target table in the target database are preset IDs, if no constraint conditions exist, determining whether constraint audit passes, if constraint conditions exist, judging whether SQL sentences meeting the constraint conditions exist in the SQL sentence set before the SQL sentences to which the database objects belong based on the arrangement sequence of the SQL sentences in the SQL sentence set, if yes, determining that constraint audit passes, and if not, determining that constraint audit does not pass.
Based on the performance rule, the process of checking whether the executed SQL statement affects the performance of the target database is illustrated as follows:
when the SQL statement is used to create a table with a preset number of rows and a preset number of columns (for example, a student table with 100 rows and 100 columns), the initial score of the SQL statement is preset to be 0, the preset performance threshold is 10, the performance rule specifies that creating a table with a preset number of rows and a preset number of columns (for example, a table with 100 rows and 100 columns) affects the performance of the database, and the degree of the effect can be represented by a preset index value (for example, 0.1) corresponding to the preset number of rows and the preset number of columns, so that it can be determined that the degree of the effect of executing the SQL statement on the performance of the database is 0.1.
For each SQL statement, traversing an abstract syntax tree corresponding to the SQL statement, obtaining node information of each node in the abstract syntax, and obtaining each database object corresponding to the SQL statement based on each node information, where the node information includes the database object, and the database object is a component of a database, including but not limited to: tables, views, indices, constraints, and functions.
And judging whether each database object corresponding to the SQL statement meets a preset auditing rule or not for each SQL statement, if any database object does not meet the auditing rule, judging that the SQL statement does not meet the auditing rule, and if all database objects corresponding to the SQL statement meet the auditing rule, judging that the SQL statement meets the auditing rule.
Referring to fig. 4, the process of determining whether each database object corresponding to the SQL statement satisfies the preset audit rule includes:
s401, checking the existence and the constraint of each database object corresponding to the SQL statement based on the target database.
And checking the existence of each database object corresponding to the SQL statement, wherein the existence of the database object is used for representing whether the database object exists in a target database, the target database is a database corresponding to the target database identifier, and the target database identifier is a database identifier corresponding to the SQL statement set. For each database object, the process of auditing the existence of the database object includes: judging whether the database object exists in the target database, if so, indicating that the database object meets the existing examination, if not, judging whether an SQL statement for creating the database object exists in the SQL statement set before the SQL statement to which the database object belongs based on the arrangement sequence of the SQL statements in the SQL statement set, and if so, indicating that the database object meets the existing examination.
For example, two SQL statements exist in the SQL statement set, the first SQL statement is used to create a table a, the second SQL statement is used to insert a record into the table a, and for the database object table a, the table a does not exist in the target database, and since the first SQL statement creates the table a, it is determined that the table a meets the existing audit.
Checking the constraint of each database object corresponding to the SQL statement, wherein the constraint of the database object is used for representing whether a condition exists in the database for executing the database object, and the process of checking the constraint corresponding to the database for each database object comprises the following steps: and judging whether a condition exists in the database object executed in the target database aiming at each database object corresponding to the SQL statement, if not, judging that the database object meets the constraint examination, if so, executing the condition of the database object in the target database, judging whether an SQL statement meeting the condition exists before the SQL statement corresponding to the database object in the SQL statement set based on the arrangement sequence of the SQL statements in the SQL statement set, and if so, judging that the database object meets the constraint examination.
S402, judging whether all the data objects meet the examination of existence and constraint, if so, executing the step S403, and if not, executing the step S404.
And S403, judging whether the naming rule of each database object meets a preset custom rule, if so, executing step S405, and if not, executing step 404.
In the method provided by the embodiment of the application, a custom rule is preset, and the custom rule is a condition for examining and verifying the name of the database object, and it needs to be noted that the custom rule can be modified according to requirements.
Judging whether the naming rule of each database object meets a preset self-defining rule, for example, the first letter of a predefined table name in the self-defining rule needs to be written in uppercase, if the database object is a student table, if the first letter of the table name of the student table is in uppercase, the self-defining rule is met, and if the first letter of the table name of the student table is in lowercase, the self-defining rule is not met.
S404, determining that the database object corresponding to the SQL statement does not meet the preset auditing rule.
And if the existing data object does not meet the existing and/or restrictive examination, or the naming rule of the database object does not meet the preset custom rule, or the performance index value of the SQL statement is not less than the preset performance threshold, determining that the database object corresponding to the SQL statement does not meet the preset examination rule.
S405, counting the performance index values of the SQL statements based on all database objects corresponding to the SQL statements.
In the method provided by the embodiment of the application, a plurality of performance rules are preset, and an index value corresponding to each performance rule is preset.
Acquiring an execution plan of the SQL statement and metadata information of each database object, wherein the execution plan of the SQL statement is a result of executing the SQL statement, and the metadata information of the database object is related information of the table, such as a table name, a field type, a total number of rows of the table, and the like.
The method comprises the steps of determining a preset performance rule met by an SQL statement based on an execution plan of the SQL statement and metadata information of each database object corresponding to the SQL statement, and counting performance index values of the SQL statement based on the preset performance rule met by the SQL statement and index values corresponding to the performance rules, wherein the performance index values of the SQL statement are used for explaining the influence of the execution of the SQL statement on the performance of a target database in a numerical form.
S406, judging whether the performance index value of the SQL statement is smaller than a preset performance threshold value, if so, executing a step S407, and if not, executing a step S404.
And S407, determining that all database objects corresponding to the SQL statement meet preset auditing rules.
In the method provided by the embodiment of the application, whether the database object meets the audit rule or not is judged by auditing the existence and the constraint of the database object, auditing the custom rule of the naming of the database object and comparing the performance index value of the SQL statement with the preset performance threshold, so that whether the SQL statement meets the audit rule or not is judged.
And S104, determining that the SQL statement set does not pass the examination.
And if the grammar of any SQL statement is incorrect or any statement does not meet the auditing rule, judging that the SQL statement set does not pass the auditing.
Optionally, after the SQL statement set is judged to fail to be audited, prompt information that the SQL statement set fails to be audited may be output.
And S105, judging whether all SQL sentences meet the auditing rule, if so, executing step S106, and if not, executing step S104.
And S106, determining that the SQL statement set passes the examination.
And if each SQL statement in the SQL statement set meets the auditing rule, judging that the SQL statement set passes the auditing.
Optionally, after it is determined that the SQL statement set passes the audit, the method may further include:
generating audit report information and storing the audit report information;
and visually displaying the audit report information.
According to the method provided by the embodiment of the application, after the SQL statement set is judged to pass the audit, a preset report template can be called based on the audit result information, audit report information is generated, the audit report information is stored in a disk of a cloud platform and visually displayed, and optionally, a user can download the audit report information.
The SQL statement auditing method provided by the embodiment of the application obtains the SQL statement set, judges whether the syntax of each SQL statement is correct, if the syntax of all SQL statements is correct, audits each SQL statement according to a preset auditing rule, and if all SQL statements meet the auditing rule, determines that the SQL statement set passes the auditing. By applying the SQL statement auditing method provided by the application, the grammar of each SQL statement in the SQL statement set is automatically audited, and the SQL statement is audited based on the auditing rule under the condition that the grammars of all the SQL statements are correct, so that the whole process does not need any manual intervention, thereby saving a large amount of time cost and labor cost and greatly improving the SQL statement auditing efficiency.
Corresponding to the method described in fig. 1, an embodiment of the present application further provides an SQL statement auditing apparatus, which is used for specifically implementing the method in fig. 1, and a schematic structural diagram of the apparatus is shown in fig. 5, and specifically includes:
an obtaining unit 501, configured to obtain an SQL statement set; the SQL statement set comprises at least one SQL statement;
a judging unit 502, configured to judge whether syntax of each SQL statement is correct;
the examination unit 503 is configured to, if the syntax of all the SQL statements is correct, examine each SQL statement according to a preset examination rule;
a determining unit 504, configured to determine that the SQL statement set passes the audit if all the SQL statements meet the audit rule.
The SQL statement auditing device provided by the embodiment of the application acquires the SQL statement set, judges whether the syntax of each SQL statement is correct, audits each SQL statement according to a preset auditing rule if the syntax of all SQL statements is correct, and determines that the SQL statement set passes the auditing if all the SQL statements meet the auditing rule. By applying the SQL statement auditing device provided by the application, the grammar of each SQL statement in the SQL statement set is automatically audited, and the SQL statement is audited based on the auditing rule under the condition that the grammars of all the SQL statements are correct, so that the whole process does not need any manual intervention, thereby saving a large amount of time cost and labor cost and greatly improving the SQL statement auditing efficiency.
In an embodiment of the present application, based on the foregoing scheme, the obtaining unit executes obtaining of an SQL statement set, and is specifically configured to:
acquiring an SQL file; the SQL file at least comprises an SQL text and a database identifier corresponding to the SQL text;
carrying out SQL statement division on the SQL text to obtain a plurality of SQL statements;
and (4) forming all SQL sentences into an SQL sentence set.
In an embodiment of the present application, based on the foregoing scheme, the determining unit 502 performs determining whether the syntax of each SQL statement is correct, specifically to:
constructing an abstract syntax tree for each SQL statement; judging whether the abstract syntax tree corresponding to the SQL statement is successfully constructed; and if the abstract syntax tree corresponding to the SQL statement is successfully constructed, determining that the syntax of the SQL statement is correct.
In an embodiment of the present application, based on the foregoing solution, the auditing unit 503 is configured to:
the acquiring subunit is used for traversing the abstract syntax tree corresponding to each SQL statement, acquiring node information of each node in the abstract syntax tree, and acquiring each database object corresponding to the SQL statement based on each node information;
the judging subunit is configured to judge, for each SQL statement, whether each database object corresponding to the SQL statement satisfies a preset audit rule, determine that the SQL statement does not satisfy the audit rule if any database object does not satisfy the audit rule, and determine that the SQL statement satisfies the audit rule if each database object corresponding to the SQL statement satisfies the audit rule.
In an embodiment of the present application, based on the foregoing scheme, the determining subunit performs determining, by the determining subunit, whether each database object corresponding to the SQL statement satisfies a preset auditing rule, where the determining subunit is specifically configured to:
checking the existence and the constraint of each database object corresponding to the SQL statement based on a target database; the target database is a database corresponding to the database identification contained in the SQL file;
when all database objects meet the examination of existence and constraint, judging whether the naming rule of each database object meets a preset custom rule;
if all the database correspondences meet the custom rule, counting the performance index value of the SQL statement based on all the database objects corresponding to the SQL statement;
and when the performance index value of the SQL statement is smaller than a preset performance threshold value, determining that each database object corresponding to the SQL statement meets a preset auditing rule.
In an embodiment of the present application, based on the foregoing scheme, the method may further include:
an output unit, configured to determine that the SQL statement set fails to pass the audit if the syntax of any SQL statement is incorrect or any SQL statement does not satisfy the audit rule
In an embodiment of the present application, based on the foregoing scheme, the method may further include:
the generating unit is used for generating audit report information and storing the audit report information;
and the display unit is used for visually displaying the audit report information.
The embodiment of the application also provides a storage medium, which comprises a stored instruction, wherein when the instruction runs, the equipment where the storage medium is located is controlled to execute the SQL statement auditing method.
The present embodiment further provides an electronic device, whose schematic structural diagram is shown in fig. 6, specifically including a memory 601, and one or more instructions 602, where the one or more instructions 602 are stored in the memory 601 and configured to be executed by one or more processors 603 to perform the following operations according to the one or more instructions 602:
acquiring an SQL statement set; the SQL statement set comprises at least one SQL statement;
judging whether the grammar of each SQL statement is correct or not;
if the grammars of all the SQL sentences are correct, auditing each SQL sentence according to preset auditing rules;
and if all the SQL statements meet the auditing rule, determining that the SQL statement set passes the auditing.
It should be noted that, in the present specification, the embodiments are all described in a progressive manner, each embodiment focuses on differences from other embodiments, and the same and similar parts among the embodiments may be referred to each other. For the device-like embodiment, since it is basically similar to the method embodiment, the description is simple, and for the relevant points, reference may be made to the partial description of the method embodiment.
Finally, it should also be noted that, herein, relational terms such as first and second, and the like may be used solely to distinguish one entity or action from another entity or action without necessarily requiring or implying any actual such relationship or order between such entities or actions. Also, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising an … …" does not exclude the presence of other identical elements in a process, method, article, or apparatus that comprises the element.
For convenience of description, the above devices are described as being divided into various units by function, and are described separately. Of course, the functionality of the units may be implemented in one or more software and/or hardware when implementing the present application.
From the above description of the embodiments, it is clear to those skilled in the art that the present application can be implemented by software plus necessary general hardware platform. Based on such understanding, the technical solutions of the present application may be essentially or partially implemented in the form of a software product, which may be stored in a storage medium, such as a ROM/RAM, a magnetic disk, an optical disk, etc., and includes several instructions for enabling a computer device (which may be a personal computer, a server, or a network device, etc.) to execute the method according to the embodiments or some parts of the embodiments of the present application.
The SQL statement auditing method and device, the storage medium and the electronic device provided by the application are introduced in detail, specific examples are applied in the description to explain the principle and implementation of the application, and the description of the embodiments is only used to help understand the method and core ideas of the application; meanwhile, for a person skilled in the art, according to the idea of the present application, there may be variations in the specific embodiments and the application scope, and in summary, the content of the present specification should not be construed as a limitation to the present application.
Claims (10)
1. An SQL statement auditing method is characterized by comprising the following steps:
acquiring an SQL statement set; the SQL statement set comprises at least one SQL statement;
judging whether the grammar of each SQL statement is correct or not;
if the grammars of all the SQL sentences are correct, auditing each SQL sentence according to preset auditing rules;
and if all the SQL statements meet the auditing rule, determining that the SQL statement set passes the auditing.
2. The method of claim 1, wherein obtaining the SQL statement set comprises:
acquiring an SQL file; the SQL file at least comprises an SQL text and a database identifier corresponding to the SQL text;
carrying out SQL statement division on the SQL text to obtain a plurality of SQL statements;
and (4) forming all SQL sentences into an SQL sentence set.
3. The method of claim 2, wherein said determining whether the syntax of each SQL statement is correct comprises:
constructing an abstract syntax tree for each SQL statement; judging whether the abstract syntax tree corresponding to the SQL statement is successfully constructed; and if the abstract syntax tree corresponding to the SQL statement is successfully constructed, determining that the syntax of the SQL statement is correct.
4. The method according to claim 3, wherein the auditing each SQL statement according to preset auditing rules comprises:
traversing an abstract syntax tree corresponding to each SQL statement to acquire node information of each node in the abstract syntax tree, and acquiring each database object corresponding to the SQL statement based on each node information;
judging whether each database object corresponding to each SQL statement meets a preset auditing rule or not, if any database object does not meet the auditing rule, determining that the SQL statement does not meet the auditing rule, and if each database object corresponding to the SQL statement meets the auditing rule, determining that the SQL statement meets the auditing rule.
5. The method according to claim 4, wherein the determining whether each database object corresponding to the SQL statement satisfies a preset auditing rule includes:
checking the existence and the constraint of each database object corresponding to the SQL statement based on a target database; the target database is a database corresponding to the database identification contained in the SQL file;
when all database objects meet the examination of existence and constraint, judging whether the naming rule of each database object meets a preset custom rule;
if all the database correspondences meet the custom rule, counting the performance index value of the SQL statement based on all the database objects corresponding to the SQL statement;
and when the performance index value of the SQL statement is smaller than a preset performance threshold value, determining that each database object corresponding to the SQL statement meets a preset auditing rule.
6. The method of claim 1 or 5, further comprising:
and if the grammar of any SQL statement is incorrect or any SQL statement does not meet the auditing rule, determining that the SQL statement set does not pass the auditing.
7. The method according to claim 1, wherein after determining that the SQL statement set passes the audit, the method further comprises:
generating audit report information and storing the audit report information;
and visually displaying the audit report information.
8. An SQL statement auditing apparatus, comprising:
the acquisition unit is used for acquiring an SQL statement set; the SQL statement set comprises at least one SQL statement;
the judging unit is used for judging whether the grammar of each SQL statement is correct or not;
the checking unit is used for checking each SQL statement according to a preset checking rule if the syntax of all the SQL statements is correct;
and the determining unit is used for determining that the SQL statement set passes the examination if all the SQL statements meet the examination rule.
9. A storage medium, characterized in that the storage medium includes stored instructions, and when the instructions are executed, the storage medium controls a device on which the storage medium is located to execute the SQL statement auditing method according to any one of claims 1 to 7.
10. An electronic device comprising a memory and one or more instructions, wherein the one or more instructions are stored in the memory and configured to be executed by one or more processors to perform the SQL statement auditing method of any one of claims 1-7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110004915.XA CN112783916A (en) | 2021-01-04 | 2021-01-04 | SQL statement auditing method and device, storage medium and electronic equipment |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110004915.XA CN112783916A (en) | 2021-01-04 | 2021-01-04 | SQL statement auditing method and device, storage medium and electronic equipment |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN112783916A true CN112783916A (en) | 2021-05-11 |
Family
ID=75755199
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202110004915.XA Pending CN112783916A (en) | 2021-01-04 | 2021-01-04 | SQL statement auditing method and device, storage medium and electronic equipment |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112783916A (en) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN115129746A (en) * | 2022-08-30 | 2022-09-30 | 平安银行股份有限公司 | SQL (structured query language) examination and analysis method, server and SQL examination and analysis system |
| CN117435515A (en) * | 2023-12-21 | 2024-01-23 | 云和恩墨(北京)信息技术有限公司 | SQL statement auditing method, device, equipment and medium based on database |
Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104391995A (en) * | 2014-12-15 | 2015-03-04 | 北京趣拿软件科技有限公司 | SQL (Structured Query Language) statement auditing method, and database operation and maintenance method and system |
| CN110427754A (en) * | 2019-08-12 | 2019-11-08 | 腾讯科技（深圳）有限公司 | Network application attack detection method, device, equipment and storage medium |
| CN110581864A (en) * | 2019-11-11 | 2019-12-17 | 北京安博通科技股份有限公司 | method and device for detecting SQL injection attack |
| CN110795450A (en) * | 2019-09-12 | 2020-02-14 | 连连银通电子支付有限公司 | SQL processing method and device |
| CN111078675A (en) * | 2020-03-23 | 2020-04-28 | 绿漫科技有限公司 | Multidimensional comprehensive database SQL (structured query language) auditing and optimizing method and system |
| CN111522816A (en) * | 2020-04-16 | 2020-08-11 | 云和恩墨(北京)信息技术有限公司 | Data processing method, device, terminal and medium based on database engine |
| CN111917788A (en) * | 2020-08-07 | 2020-11-10 | 四川长虹电器股份有限公司 | HMM model-based SQL injection attack detection method |
-
2021
- 2021-01-04 CN CN202110004915.XA patent/CN112783916A/en active Pending
Patent Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104391995A (en) * | 2014-12-15 | 2015-03-04 | 北京趣拿软件科技有限公司 | SQL (Structured Query Language) statement auditing method, and database operation and maintenance method and system |
| CN110427754A (en) * | 2019-08-12 | 2019-11-08 | 腾讯科技（深圳）有限公司 | Network application attack detection method, device, equipment and storage medium |
| CN110795450A (en) * | 2019-09-12 | 2020-02-14 | 连连银通电子支付有限公司 | SQL processing method and device |
| CN110581864A (en) * | 2019-11-11 | 2019-12-17 | 北京安博通科技股份有限公司 | method and device for detecting SQL injection attack |
| CN111078675A (en) * | 2020-03-23 | 2020-04-28 | 绿漫科技有限公司 | Multidimensional comprehensive database SQL (structured query language) auditing and optimizing method and system |
| CN111522816A (en) * | 2020-04-16 | 2020-08-11 | 云和恩墨(北京)信息技术有限公司 | Data processing method, device, terminal and medium based on database engine |
| CN111917788A (en) * | 2020-08-07 | 2020-11-10 | 四川长虹电器股份有限公司 | HMM model-based SQL injection attack detection method |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN115129746A (en) * | 2022-08-30 | 2022-09-30 | 平安银行股份有限公司 | SQL (structured query language) examination and analysis method, server and SQL examination and analysis system |
| CN117435515A (en) * | 2023-12-21 | 2024-01-23 | 云和恩墨(北京)信息技术有限公司 | SQL statement auditing method, device, equipment and medium based on database |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111522816B (en) | Data processing method, device, terminal and medium based on database engine | |
| US11023367B1 (en) | Systems and methods for testing a software application | |
| CN102804147B (en) | Perform the code check executive system of the code check of ABAP source code | |
| CN105912594B (en) | SQL statement processing method and system | |
| KR101554424B1 (en) | Method and apparatus for auto generation of test case | |
| US9037552B2 (en) | Methods for analyzing a database and devices thereof | |
| CN112783916A (en) | SQL statement auditing method and device, storage medium and electronic equipment | |
| CN105335246B (en) | A kind of program crashing defect self-repairing method based on question and answer web analytics | |
| CN107766353B (en) | Method and device for migrating statistical information of database | |
| CN112328489A (en) | Test case generation method and device, terminal equipment and storage medium | |
| CN116842042A (en) | Universal method, device, electronic equipment and storage medium for dissimilating database | |
| CN111190880B (en) | Database detection method, device and computer readable storage medium | |
| CN115238655A (en) | Json data editing method and device | |
| CN114385679A (en) | Meter structure inspection method, meter structure inspection device and electronic equipment | |
| CN116931951A (en) | SQL code checking method, medium, device and computing equipment | |
| CN116360794A (en) | Database language analysis method, device, computer equipment and storage medium | |
| CN113051262B (en) | Data quality inspection method, device, equipment and storage medium | |
| CA3148074A1 (en) | Text information extracting method, device, computer equipment and storage medium | |
| CN113836164A (en) | Method, system, device and medium for unifying SQL | |
| CN113778997B (en) | Database version management method, system and equipment | |
| CN115687292B (en) | Verifiable database upgrading method irrelevant to database technology | |
| CN111221846B (en) | Automatic translation method and device for SQL sentences | |
| CN116401177B (en) | DDL correctness detection method, device and medium | |
| CN117493391A (en) | Task matching method, device, computer equipment and storage medium | |
| CN117608582A (en) | SQL sentence management method and device, electronic equipment and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20210511 |