SQL risk detection method and device, electronic equipment, storage medium and product
By building and comparing the execution plan trees in different databases, the problem of low SQL identification in the database replacement process is solved, and more efficient and accurate risk detection is achieved.
Patent Information
- Application Number
- CN202510149690.5
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-02-11
- Publication Date
- 2025-06-24
Smart Images
Figure CN120196643A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the technical field of data processing, and particularly to a method, device, electronic device, storage medium and product for risk detection of SQL. Background Art
[0002] During the process of database replacement or migration, due to the different processing logics of the optimizers of different database software and the possible changes in the data organization and storage modes, the performance of Structured Query Language (SQL) on the new database may degenerate, which will further affect the operation efficiency of the entire system and even damage the main business of the enterprise.
[0003] To ensure that the database software used by the system can be replaced smoothly and to discover in advance the SQL (risk language) that may execute slowly on the new database software, testers and developers will use methods such as system interface stress testing or actual execution of SQL for inspection and verification.
[0004] The system interface stress testing method means that before replacing the database software in the online production of the business system, a simulation of the database replacement operation will be carried out in a separate test environment first, and the response time of each request interface of the system before and after the replacement will be compared. Then, according to the statistical results of the response time, the interfaces with slower response speeds will be found, and then the code will be checked to locate the SQL involved in the interfaces, and then further analysis will be carried out.
[0005] The actual execution of SQL method means that the SQL of the business system is obtained, and then it is executed on the new and old database software before and after the replacement respectively, and then the performance difference of the SQL is judged by manually comparing the execution time of the SQL.
[0006] When the business system replaces the domestic self - controllable database, when using the interface stress testing method to compare the SQL performance before and after the replacement, the development team needs to check the corresponding code according to the interfaces with increased time consumption and locate the specific SQL in the code. Moreover, for the situation where there are multiple SQL statements in one interface, it is still necessary to find out which one of the SQL statements affects the slowdown of the interface.
[0007] For the method of actually executing SQL, since the granularity of the SQL execution duration recorded by some database software is relatively coarse (in seconds), it is impossible to compare some SQL statements with high efficiency and execution time in milliseconds or even microseconds. Moreover, SQL statements with short execution times are vulnerable to external factors, which may affect the accuracy of the results. For example, differences in the CPU or disk performance of the machines used to deploy new and old database software, as well as fluctuations in the machine load during testing, may cause the test results to be distorted. For some data change SQL statements such as deletion and insertion, repeated execution may also cause data pollution.
[0008] In summary, it can be seen that the efficiency of identifying risk languages is low during the process of replacing databases for SQL. Summary of the Invention
[0009] The present invention provides a method, device, electronic device, storage medium and product for risk detection of SQL, to solve the defect of low efficiency in identifying risk languages during the process of replacing databases for SQL in the prior art, and to improve the efficiency of identifying risk languages during the process of replacing databases for SQL.
[0010] The present invention provides a method for risk detection of SQL, including: constructing different execution plan trees for the target SQL based on different execution plans of the target SQL in different databases; the target SQL is any SQL in the database; based on the comparison results of different execution plan trees, it is determined that when the target SQL migrates between different databases, it belongs to risk language.
[0011] In one embodiment, each execution plan includes multiple execution operators with different levels. Constructing different execution plan trees for the target SQL based on different execution plans of the target SQL in different databases includes: for each execution plan, traversing all the execution operators in the execution plan. For the traversed execution operator and the currently traversed execution operator, if the level of the traversed execution operator is 1 level less than the level of the current execution operator, then the traversed execution operator is used as the parent node of the current execution operator, and the current execution operator is used as the child node of the traversed execution operator; if the level of the traversed execution operator is 1 level greater than the level of the current execution operator, then the traversed execution operator is used as the child node of the current execution operator, and the current execution operator is used as the parent node of the traversed execution operator; the parent node with the smallest level is used as the root node of the execution plan tree; based on all the nodes in one execution plan, an execution plan tree is obtained; based on all the nodes in different execution plans, different execution plan trees are obtained, and the nodes include parent nodes, child nodes and root nodes.
[0012] In one embodiment, the number of layers of the execution operator is determined based on the following steps: The number of layers of the execution operator is determined based on the indentation length of the line where the execution operator is located. When the indentation length of the line where the execution operator is located is N times the indentation unit, the number of layers of the execution operator is determined to be N, where N≥0.
[0013] In one embodiment, different execution plan trees include a first execution plan tree and a second execution plan tree. The comparison result is determined based on the following steps: Perform a post-order traversal of all nodes in the first execution plan tree to obtain the first execution information of the first execution plan tree. The first execution information includes the operation order of the nodes of the first execution plan tree and the operation content of the nodes of the first execution plan tree; perform a post-order traversal of all nodes in the second execution plan tree to obtain the second execution information of the second execution plan tree; the second execution information includes the operation order of the nodes of the second execution plan tree and the operation content of the nodes of the second execution plan tree; compare the operation order of the nodes of the first execution plan tree with the operation order of the nodes of the second execution plan tree, and at the same time compare the operation content of the nodes of the first execution plan tree with the operation content of the nodes of the second execution plan tree to obtain the comparison result.
[0014] In one embodiment, based on the comparison result of different execution plan trees, when it is determined that the target SQL migrates between different databases, it belongs to the risk language, including: when the comparison result is that the operation order of the nodes of the first execution plan tree is different from the operation order of the nodes of the second execution plan tree, or the operation content of the nodes of the first execution plan tree is different from the operation content of the nodes of the second execution plan tree, it is determined that when the target SQL migrates between different databases, it belongs to the risk language.
[0015] In one embodiment, the operation content of the node is determined based on the following steps: When the node is the parent node, the operation content of the node is determined based on the connection operator of the node; when the node is the child node, the operation content of the node is determined based on the scan operator of the node.
[0016] The present invention also provides a risk detection device for SQL, including: a construction module for constructing different execution plan trees of the target SQL based on different execution plans of the target SQL in different databases; the target SQL is any SQL in the database; an identification module for determining that when the target SQL migrates between different databases, it belongs to the risk language based on the comparison result of different execution plan trees.
[0017] The present invention also provides an electronic device, including a memory, a processor, and a computer program stored on the memory and executable on the processor. When the processor executes the computer program, it implements any one of the above SQL risk detection methods.
[0018] The present invention also provides a non-transitory computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, it implements any one of the above SQL risk detection methods.
[0019] The present invention also provides a computer program product, including a computer program. When the computer program is executed by a processor, it implements any one of the above SQL risk detection methods.
[0020] The SQL risk detection method, device, electronic device, storage medium and product provided by the present invention construct different execution plan trees through different execution plans, achieving the format unification of different execution plans, facilitating the subsequent comparison of different execution plan trees, and being beneficial to improving the accuracy of identifying risk languages. By comparing different execution plan trees, the comparison of the core execution operators of different execution plans is realized, avoiding the performance differences in manual inspection when the target SQL migrates between different databases, and improving the efficiency of identifying risk languages when the SQL migrates between different databases. BRIEF DESCRIPTION OF THE DRAWINGS
[0021] In order to more clearly illustrate the technical solutions in the present invention or the prior art, the following will briefly introduce the drawings required for the description of the embodiments or the prior art. Obviously, the drawings in the following description are some embodiments of the present invention. For those of ordinary skill in the art, other drawings can be obtained based on these drawings without creative efforts.
[0022] Figure 1 is one of the flow diagrams of the SQL risk detection method provided by the present invention.
[0023] Figure 2 is a schematic diagram of the first execution plan provided by the present invention.
[0024] Figure 3 is a schematic diagram of the first execution plan tree provided by the present invention.
[0025] Figure 4 is the second flow diagram of the SQL risk detection method provided by the present invention.
[0026] Figure 5 is a schematic diagram of the structure of the SQL risk detection device provided by the present invention.
[0027] Figure 6 is a schematic diagram of the structure of the electronic device provided by the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS
[0028] To make the objectives, technical solutions, and advantages of the present invention clearer, the technical solutions in the present invention will be clearly and completely described below with reference to the accompanying drawings in the present invention. Apparently, the described embodiments are some, but not all, of the embodiments of the present invention. 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.
[0029] The following combines Figures 1-6 to describe the SQL risk detection method, device, and electronic device of the present invention.
[0030] Figure 1 is one of the flow diagrams of the SQL risk detection method provided by the present invention. As Figure 1 shown, the method includes steps S100 to S200, and the specific steps are as follows.
[0031] S100: Based on different execution plans of the target SQL in different databases, construct different execution plan trees of the target SQL.
[0032] The target SQL is any SQL in the database.
[0033] The different databases of the present invention include open source relational database management systems such as MySQL databases and openGauss databases.
[0034] For example, the different databases of the present invention include a first database and a second database. The present invention takes the first database as a MySQL database and the second database as an openGauss database as an example for illustration. The different execution plans include a first execution plan of the target SQL in the first database and a second execution plan of the target SQL in the second database.
[0035] The first database and the second database, as software carriers for storing data at the bottom layer of the business system, support applications to efficiently retrieve the required information through the Structured Query Language (SQL). When an enterprise selects and replaces the production system database, in order to prevent the performance degradation of SQL execution after switching to a new database, a SQL performance degradation risk (risk language) detection link is added.
[0036] The detection object of the SQL risk detection method of the present invention is the execution plans of SQL in different types of databases (for example, MySQL databases and openGauss databases).
[0037] The execution plan of SQL is a detailed list of steps generated by the database in response to an SQL query. It describes how the database retrieves data to meet the requirements of the query, including aspects such as the indexes used, the table join order, the table join type, sorting, and aggregation operations. An execution operator is the basic operation unit used to execute the database query plan (execution plan). Before SQL is executed, the query optimizer generates one or more execution plans based on the SQL parsing result and the database statistics (such as the size of the table, the existence and selectivity of the index, etc.), and selects the one with the lowest cost as the execution plan. Then, the database executes the query according to the execution plan selected by the optimizer. If the database selects different execution plans, it will retrieve data from the database along different execution paths. Although the final result values are the same, the time consumption may vary greatly.
[0038] Obtain the first execution plan of the target SQL in the first database (MySQL database). The first execution plan is usually returned in text form and contains multiple lines of key elements (e.g., the first execution operator). These key elements need to be converted into the first execution plan tree. Use the Python programming language to implement this process, read the key elements in the first execution plan, and then construct the first execution plan tree. The first execution operator includes the basic operation unit for the first database to execute the first execution plan of the target SQL.
[0039] Obtain the second execution plan of the target SQL in the second database (openGauss database). The second execution plan contains multiple lines of key elements (e.g., the second execution operator). Convert these key elements into the second execution plan tree. Use the Python programming language to implement this process, read the key elements in the second execution plan, and then construct the second execution plan tree. The second execution operator includes the basic operation unit for the second database to execute the second execution plan of the target SQL.
[0040] S200: Based on the comparison results of different execution plan trees, determine that when the target SQL migrates across different databases, it belongs to the risk language.
[0041] Such as Figure 4As shown, the first execution plan tree includes the execution order of the first execution operator (the order of the nodes in the first execution plan tree) and the execution content (the operation type of the nodes in the first execution plan tree). By comparing the execution order and execution content of the first execution operator and the second execution operator, a comparison result is obtained. If the comparison result is that the execution orders of the first execution operator and the second execution operator are different, or the execution contents are different, it is determined that the target SQL belongs to a risk language when migrating from the first database to the second database. After adding a risk language identifier to this risk language, the database migration is then performed. If the comparison result is that the execution orders and execution contents of the first execution operator and the second execution operator are both the same, it is determined that the target SQL does not belong to a risk language when migrating from the first database to the second database, and a normal database migration is performed on this risk language.
[0042] The risk detection method for SQL provided by the embodiments of the present invention constructs different execution plan trees through different execution plans, realizes the format unification of different execution plans, facilitates the subsequent comparison of different execution plan trees, and is conducive to improving the accuracy of identifying risk languages. By comparing different execution plan trees, the comparison of the core execution operators of different execution plans is realized, avoiding the performance differences in manual inspection when the target SQL migrates between different databases, and improving the efficiency of identifying risk languages when the SQL migrates between different databases.
[0043] Based on the above embodiments, each execution plan includes multiple execution operators with different levels. Based on the different execution plans of the target SQL in different databases, different execution plan trees of the target SQL are constructed, including: for each execution plan, traversing all the execution operators in the execution plan. For the traversed execution operator and the currently traversed current execution operator, if the level of the traversed execution operator is 1 level less than the level of the current execution operator, the traversed execution operator is used as the parent node of the current execution operator, and the current execution operator is used as the child node of the traversed execution operator; if the level of the traversed execution operator is 1 level greater than the level of the current execution operator, the traversed execution operator is used as the child node of the current execution operator, and the current execution operator is used as the parent node of the traversed execution operator; the parent node with the smallest level is used as the root node of the execution plan tree; based on all the nodes in one execution plan, an execution plan tree is obtained; based on all the nodes in different execution plans, different execution plan trees are obtained, and the nodes include parent nodes, child nodes, and root nodes.
[0044] The level of the execution operator is determined based on the following steps: determining the level of the execution operator based on the indentation length of the line where the execution operator is located. When the indentation length of the line where the execution operator is located is N times the indentation unit, it is determined that the level of the execution operator is N, where N ≥ 0.
[0045] AsFigure 2 As shown, the first execution plan is text data, including multiple first execution operators. A first execution operator is finally converted into a node in the first execution plan tree. The level of the first execution operator is determined according to the indentation length of the first execution operator. The indentation length includes the indentation spaces of the line where the first execution operator is located. For example, the indentation unit includes 4 spaces, and every 4 spaces of indentation of the first execution operator represents one level.
[0046] Traverse all the first execution operators in the first execution plan from top to bottom, and then locate the child nodes of each node in turn. The first execution operator with the smallest level is converted into the root node, the first execution operator with one level more than the root node is converted into the child node of the root node, the first execution operator with one level more than the child node is converted into the grandchild node of the root node, and the first execution operator with the same level as the child node is converted into the right sibling node of the child node, and so on, to construct the first execution plan tree.
[0047] For example, the first execution operators of the first execution plan include execution operator A, execution operator B, execution operator C, execution operator D, execution operator E, execution operator F, and execution operator G. The nodes in the finally obtained first execution plan tree include node A, node B, node C, node D, node E, node F, and node G. The indentation length of each line where the first execution operator is located determines the level of the node corresponding to the first execution operator. As Figure 2 shown, traverse all the first execution operators in the first execution plan from top to bottom to obtain the parent node, child node, and root node of the first execution plan tree. For example, according to the indentation length of the line where execution operator A (the traversed execution operator) is located and the indentation length of the line where execution operator B (the current execution operator) is located, it is determined that the level of execution operator A is 1 level less than the level of execution operator B, then execution operator A is the parent node of execution operator B, and execution operator B is the child node of execution operator A. Traverse all the first execution operators of the first execution plan to obtain the first execution plan information table. The first execution plan information table is shown in Table 1.
[0048] Table 1 First execution plan information table
[0049] Obtain all the nodes in the first execution plan and the parent-child relationships between the nodes according to the first execution plan information table. It can be seen from the first execution plan information table that the indentation length of node A is the smallest, and node A is the root node of the first execution plan tree. Construct the first execution plan tree according to all the parent nodes, child nodes, and root nodes in the first execution plan information table. Figure 3 This is the first execution plan tree.
[0050] Further, the method for determining the second execution plan tree is the same as the method for determining the first execution plan tree. The second execution plan includes multiple second execution operators with different levels. The level of the second execution operator is determined according to the indentation length of the line where the second execution operator is located.
[0051] According to the present invention, based on the indentation length, the accuracy of determining the level of the execution operator is improved. By determining the parent node, child node, and root node according to the level of the execution operator, in-depth mining of the relationship between the execution operators in the execution plan is realized, which is beneficial to improving the accuracy of subsequent construction of the execution plan tree.
[0052] Based on the above embodiments, different execution plan trees include the first execution plan tree and the second execution plan tree. The comparison result is determined based on the following steps: perform a post-order traversal on all nodes in the first execution plan tree to obtain the first execution information of the first execution plan tree, where the first execution information includes the operation order of the nodes in the first execution plan tree and the operation content of the nodes in the first execution plan tree; perform a post-order traversal on all nodes in the second execution plan tree to obtain the second execution information of the second execution plan tree; the second execution information includes the operation order of the nodes in the second execution plan tree and the operation content of the nodes in the second execution plan tree; compare the operation order of the nodes in the first execution plan tree with the operation order of the nodes in the second execution plan tree, and at the same time compare the operation content of the nodes in the first execution plan tree with the operation content of the nodes in the second execution plan tree to obtain the comparison result.
[0053] Post-order Traversal is a traversal method of a binary tree. Its traversal order is: left child node -> right child node -> parent node. Figure 3 Taking the first execution plan tree as an example, perform a subsequent traversal on the first execution plan tree to obtain the first execution information. The first execution information is shown in Table 2.
[0054] Table 2 First Execution Information
[0055] According to the same method, perform a subsequent traversal on the second execution plan tree to obtain the second execution information. The second execution information includes the operation order of the nodes in the second execution plan tree and the operation content of the nodes in the second execution plan tree.
[0056] Compare the operation order of the nodes in the first execution plan tree with the operation order of the nodes in the second execution plan tree, and at the same time compare the operation content of the nodes in the first execution plan tree with the operation content of the nodes in the second execution plan tree to obtain the comparison result.
[0057] The present invention realizes an accurate comparison between the execution operators of the first execution plan (the first execution operator) and the execution operators of the second execution plan (the second execution operator) by comparing the operation order and operation content of nodes, which is beneficial to improving the accuracy of subsequent identification of risk languages.
[0058] Based on the above embodiments, based on the comparison results of different execution plan trees, when it is determined that the target SQL migrates between different databases and belongs to a risk language, it includes: when the operation order of the nodes in the first execution plan tree is different from the operation order of the nodes in the second execution plan tree, or the operation content of the nodes in the first execution plan tree is different from the operation content of the nodes in the second execution plan tree, it is determined that when the target SQL migrates between different databases, it belongs to a risk language.
[0059] Compare the operation order and operation content of the nodes in the first execution plan tree and the second execution plan tree. If it is found through comparison that the operation order of the nodes in the first execution plan tree is different from the operation order of the nodes in the second execution plan tree, or the operation content of the nodes in the first execution plan tree is different from the operation content of the nodes in the second execution plan tree, it is determined that when the target SQL migrates from the first database to the second database, performance degradation may occur, and then the target SQL is marked as a risk language.
[0060] The present invention identifies the target SQL as a risk language based on the differences in the operation order and operation content of the nodes in the first execution plan tree and the second execution plan tree, thereby improving the accuracy of identifying risk languages.
[0061] Based on the above embodiments, the operation content of the node is determined based on the following steps: when the node is a parent node, the operation content of the node is determined based on the join operator of the node; when the node is a child node, the operation content of the node is determined based on the scan operator of the node.
[0062] In the execution plan tree, the parent node includes the join operator in the execution plan, which is used to implement various join operations in the target SQL. The join operators include nested loop join, hashjoin, mergejoin, etc. In the execution plan tree, the child node includes the scan operator in the execution plan, which is used to scan objects and obtain data. The scan operators include indexscan, seqscan, bitmapheapscan, bitmapIndexScan, etc.
[0063] The present invention determines the operation content of the nodes of the execution plan tree according to the scan operator of the child node and the connection operator of the parent node, realizes the induction of the core execution operators in the execution plan, and is beneficial to improving the efficiency of subsequent identification of risk languages.
[0064] The risk detection device for SQL provided by the present invention will be described below. The risk detection device for SQL described below can be correspondingly referred to the risk detection method for SQL described above.
[0065] As Figure 5 shown, a risk detection device for SQL includes: a construction module 501, configured to construct different execution plan trees of the target SQL based on different execution plans of the target SQL in different databases; the target SQL is any SQL in the database.
[0066] An identification module 502, configured to determine that it belongs to a risk language when the target SQL migrates between different databases based on the comparison results of different execution plan trees.
[0067] The risk detection device for SQL provided by the embodiment of the present invention constructs different execution plan trees through different execution plans, realizes the format unification of different execution plans, facilitates the subsequent comparison of different execution plan trees, and is beneficial to improving the accuracy of identifying risk languages. By comparing different execution plan trees, the comparison of the core execution operators of different execution plans is realized, the performance difference when manually checking the migration of the target SQL between different databases is avoided, and the efficiency of identifying risk languages when the SQL migrates between different databases is improved.
[0068] In one embodiment, each execution plan includes multiple execution operators with different levels. The construction module 501 is configured to: for each execution plan, traverse all the execution operators in the execution plan. For the traversed execution operator and the currently traversed execution operator, if the level of the traversed execution operator is 1 level less than the level of the currently traversed execution operator, then regard the traversed execution operator as the parent node of the currently traversed execution operator, and regard the currently traversed execution operator as the child node of the traversed execution operator; if the level of the traversed execution operator is 1 level greater than the level of the currently traversed execution operator, then regard the traversed execution operator as the child node of the currently traversed execution operator, and regard the currently traversed execution operator as the parent node of the traversed execution operator; regard the parent node with the smallest level as the root node of the execution plan tree; obtain an execution plan tree based on all the nodes in one execution plan; obtain different execution plan trees based on all the nodes in different execution plans, and the nodes include parent nodes, child nodes and root nodes.
[0069] In one embodiment, the building module 501 is configured to: determine the layer number of the execution operator based on the indentation length of the line where the execution operator is located. When the indentation length of the line where the execution operator is located is N times the indentation unit, determine that the layer number of the execution operator is N, where N≥0.
[0070] In one embodiment, different execution plan trees include a first execution plan tree and a second execution plan tree. The identification module 502 is configured to: perform a post-order traversal on all nodes in the first execution plan tree to obtain first execution information of the first execution plan tree, where the first execution information includes the operation order of the nodes in the first execution plan tree and the operation content of the nodes in the first execution plan tree; perform a post-order traversal on all nodes in the second execution plan tree to obtain second execution information of the second execution plan tree; the second execution information includes the operation order of the nodes in the second execution plan tree and the operation content of the nodes in the second execution plan tree; compare the operation order of the nodes in the first execution plan tree with the operation order of the nodes in the second execution plan tree, and at the same time compare the operation content of the nodes in the first execution plan tree with the operation content of the nodes in the second execution plan tree to obtain a comparison result.
[0071] In one embodiment, the identification module 502 is configured to: when the comparison result is that the operation order of the nodes in the first execution plan tree is different from the operation order of the nodes in the second execution plan tree, or the operation content of the nodes in the first execution plan tree is different from the operation content of the nodes in the second execution plan tree, determine that when the target SQL migrates between different databases, it belongs to risk language.
[0072] In one embodiment, the identification module 502 is configured to: when the node is a parent node, determine the operation content of the node based on the connection operator of the node; when the node is a child node, determine the operation content of the node based on the scan operator of the node.
[0073] Figure 6 An entity structure diagram of an electronic device is exemplified, as Figure 6 shown. The electronic device may include: a processor 610, a communication interface 620, a memory 630, and a communication bus 640. Among them, the processor 610, the communication interface 620, and the memory 630 complete communication with each other through the communication bus 640. The processor 610 can call logical instructions in the memory 630 to execute the risk detection method of SQL, and the method includes: constructing different execution plan trees of the target SQL based on different execution plans of the target SQL in different databases; the target SQL is any SQL in the database; based on the comparison result of different execution plan trees, determine that when the target SQL migrates between different databases, it belongs to risk language.
[0074] In addition, when the logical instructions in the above-mentioned memory 630 can be implemented in the form of software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on such an understanding, the technical solution of the present invention, in essence, or the part that contributes to the prior art, or a part of this technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions for causing a computer device (which can be a personal computer, a server, or a network device, etc.) to execute all or part of the steps of the methods described in various embodiments of the present invention. The aforementioned storage medium includes: various media such as USB flash drives, mobile hard disks, read-only memories (ROMs), random access memories (RAMs), magnetic disks, or optical discs that can store program codes.
[0075] On the other hand, the present invention also provides a computer program product. The computer program product includes a computer program that can be stored on a non-transitory computer-readable storage medium. When the computer program is executed by a processor, the computer can execute the SQL risk detection method provided by the above-mentioned various methods. The method includes: constructing different execution plan trees of the target SQL based on different execution plans of the target SQL in different databases; the target SQL is any SQL in the database; based on the comparison results of different execution plan trees, when it is determined that the target SQL migrates between different databases, it belongs to a risk language.
[0076] On another aspect, the present invention also provides a non-transitory computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, it implements the SQL risk detection method provided by the above-mentioned various methods. The method includes: constructing different execution plan trees of the target SQL based on different execution plans of the target SQL in different databases; the target SQL is any SQL in the database; based on the comparison results of different execution plan trees, when it is determined that the target SQL migrates between different databases, it belongs to a risk language.
[0077] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed to multiple network units. Some or all of the modules can be selected according to actual needs to achieve the purpose of the solution of this embodiment. A person of ordinary skill in the art can understand and implement it without creative labor.
[0078] Through the description of the above embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus a necessary general hardware platform, and of course, it can also be implemented by hardware. Based on such an understanding, the essence of the above technical solution, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions to enable a computer device (which can be a personal computer, server, or network device, etc.) to execute the methods described in each embodiment or some parts of the embodiments.
[0079] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention and are not intended to limit them. Although the present invention has been described in detail with reference to the foregoing embodiments, those of ordinary skill in the art should understand that they can still modify the technical solutions described in the foregoing embodiments, or perform equivalent replacements for some of the technical features. And these modifications or replacements do not make the essence of the corresponding technical solutions deviate from the spirit and scope of the technical solutions of the embodiments of the present invention.
Claims
1. A SQL risk detection method, characterized in that: include: Based on different execution plans of the target SQL in different databases, construct different execution plan trees of the target SQL; The target SQL is any SQL in the database; Based on the comparison results of the different execution plan trees, it is determined that when the target SQL is migrated between the different databases, it belongs to risky language.
2. The SQL risk detection method according to claim 1, characterized in that: Each of the execution plans includes a plurality of execution operators with different numbers of layers. The different execution plans based on the target SQL in different databases are used to construct different execution plan trees of the target SQL, including: For each of the execution plans, all the execution operators in the execution plan are traversed. For the traversed execution operators and the current execution operator being traversed, if the number of levels of the traversed execution operator is one level less than that of the current execution operator, the traversed execution operator is used as the parent node of the current execution operator, and the current execution operator is used as the child node of the traversed execution operator; if the number of levels of the traversed execution operator is one level greater than that of the current execution operator, the traversed execution operator is used as the child node of the current execution operator, and the current execution operator is used as the parent node of the traversed execution operator; the parent node with the smallest number of levels is used as the root node of the execution plan tree; Based on all nodes in one execution plan, an execution plan tree is obtained; based on all nodes in different execution plans, different execution plan trees are obtained, wherein the nodes include the parent node, the child node and the root node.
3. The SQL risk detection method according to claim 2, characterized in that: The number of layers of the execution operator is determined based on the following steps: The number of layers of the execution operator is determined based on the indentation length of the row where the execution operator is located. When the indentation length of the row where the execution operator is located is N times the indentation unit, the number of layers of the execution operator is determined to be N, where N≥0.
4. The SQL risk detection method according to claim 1, characterized in that: The different execution plan trees include a first execution plan tree and a second execution plan tree, and the comparison result is determined based on the following steps: Performing post-order traversal on all nodes in the first execution plan tree to obtain first execution information of the first execution plan tree, wherein the first execution information includes an operation sequence of the nodes of the first execution plan tree and operation contents of the nodes of the first execution plan tree; Performing the post-order traversal on all nodes in the second execution plan tree to obtain second execution information of the second execution plan tree; the second execution information includes the operation sequence of the nodes of the second execution plan tree and the operation content of the nodes of the second execution plan tree; The operation sequence of the nodes of the first execution plan tree is compared with the operation sequence of the nodes of the second execution plan tree, and the operation content of the nodes of the first execution plan tree is compared with the operation content of the nodes of the second execution plan tree to obtain the comparison result.
5. The SQL risk detection method according to claim 4, characterized in that: The determining, based on the comparison results of the different execution plan trees, that the target SQL is a risky language when it is migrated between the different databases includes: When the comparison result is that the operation sequence of the nodes of the first execution plan tree is different from the operation sequence of the nodes of the second execution plan tree, or the operation content of the nodes of the first execution plan tree is different from the operation content of the nodes of the second execution plan tree, it is determined that the target SQL is migrated between different databases, and it belongs to the risk language.
6. The SQL risk detection method according to claim 4, characterized in that: The operation content of the node is determined based on the following steps: When the node is a parent node, determining the operation content of the node based on the connection operator of the node; When the node is a child node, the operation content of the node is determined based on the scan operator of the node.
7. A SQL risk detection device, characterized in that: include: A construction module, used to construct different execution plan trees of the target SQL based on different execution plans of the target SQL in different databases; The target SQL is any SQL in the database; The identification module is used to determine, based on the comparison results of different execution plan trees, that the target SQL belongs to risky language when it is migrated between the different databases.
8. An electronic device comprising a memory, a processor, and a computer program stored in the memory and running on the processor, characterized in that: When the processor executes the computer program, the SQL risk detection method according to any one of claims 1 to 6 is implemented.
9. A non-transitory computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the risk detection method for SQL according to any one of claims 1 to 6 is implemented.
10. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the risk detection method for SQL according to any one of claims 1 to 6 is implemented.
Citation Information
Cited By
Multi-factor database access behavior risk analysis method and system
CN121919906A