Database SQL (Structured Query Language) statement anomaly detection method and device and medium
By using real-time parsing and risk assessment methods, the problems of resource waste and response lag in traditional SQL statement detection are solved, achieving efficient anomaly detection and enhanced security.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-12-24
- Publication Date
- 2026-04-07
AI Technical Summary
In traditional operation and maintenance models, the separate transmission, storage and analysis of SQL statements leads to resource waste and response delays. It is difficult to efficiently summarize suspicious SQL statement data across nodes, and abnormal SQL statements may cause problems such as sudden drops in database performance and memory overflow.
By parsing SQL statements in real time, matching SQL templates with information repositories, and combining anomaly reasoning models and domain knowledge bases to calculate risk values, SQL statements with risk assessment values exceeding thresholds are intercepted, and alarm description information is generated.
It improves the efficiency of SQL statement anomaly detection, reduces resource usage and risk, enhances the security of SQL statement processing, and realizes anomaly detection at the forefront.
Smart Images
Figure CN121807708A_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of data processing, and in particular to a method, apparatus, and medium for detecting anomalies in database SQL statements. Background Technology
[0002] As the database size increases and business complexity rises, business systems need to issue massive amounts of SQL (Structured Query Language) statements to database nodes for execution.
[0003] In the process of developing this invention, the inventors discovered the following shortcomings in the existing technology: Currently, under the traditional operation and maintenance model, each SQL statement needs to undergo separate transmission, storage, and analysis operations. This not only consumes high transmission bandwidth and storage resources, resulting in a waste of system resources, but also makes it difficult for the operation and maintenance platform to efficiently aggregate suspicious SQL statement data across different nodes because the SQL statement data is scattered across different nodes. At the same time, abnormal SQL statements may cause problems such as sudden drops in database performance and memory overflows. Traditional methods relying on manual inspection or post-event alerts suffer from drawbacks such as delayed response and reliance on experience. Summary of the Invention
[0004] This invention provides a method, apparatus, and medium for detecting SQL statement anomalies in databases, thereby improving the efficiency of SQL statement anomaly detection and reducing resource usage.
[0005] According to one aspect of the present invention, a method for detecting anomalies in database SQL statements is provided, comprising:
[0006] It can acquire and parse the SQL statement to be executed in real time to obtain the current SQL syntax parsing result;
[0007] Based on the current SQL syntax parsing result, it is matched with the pre-built SQL template and information repository. If the match is successful, the current hit SQL template, as well as the key information of the current execution plan and the current execution indicators corresponding to the current hit SQL template are obtained.
[0008] The current hit SQL template, key information of the current execution plan, and current execution metrics are input into the pre-built SQL statement anomaly inference model, and the risk value is calculated by combining it with the preset domain knowledge base information to obtain the risk assessment value of the current SQL statement.
[0009] Obtain the preset target risk assessment threshold, and if the risk assessment value of the current SQL statement is greater than the target risk assessment threshold, intercept the SQL statement to be executed and generate target alarm description information.
[0010] According to another aspect of the present invention, a database SQL statement anomaly detection device is provided, comprising:
[0011] The current SQL syntax parsing result determination module is used to obtain and parse the SQL statement to be executed in real time to obtain the current SQL syntax parsing result;
[0012] The module for determining the current hit SQL template, key information of the current execution plan, and current execution metrics is used to match the current SQL syntax parsing result with a pre-built SQL template and information repository. If the match is successful, the module obtains the current hit SQL template, as well as the key information of the current execution plan and the current execution metrics corresponding to the current hit SQL template.
[0013] The current SQL statement risk assessment value determination module is used to input the current hit SQL template, the current execution plan key information and the current execution indicators into the pre-built SQL statement anomaly inference model, and combine it with the preset domain knowledge base information to calculate the risk value and obtain the current SQL statement risk assessment value.
[0014] The target alarm description information generation module is used to obtain the preset target risk assessment threshold, and if the risk assessment value of the current SQL statement is greater than the target risk assessment threshold, it intercepts the SQL statement to be executed and generates target alarm description information.
[0015] According to another aspect of the present invention, an electronic device is provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the database SQL statement anomaly detection method according to any embodiment of the present invention.
[0016] According to another aspect of the present invention, a computer-readable storage medium is provided, the computer-readable storage medium storing computer instructions for causing a processor to execute and implement the database SQL statement anomaly detection method according to any embodiment of the present invention.
[0017] The technical solution of this invention obtains and parses the SQL statement to be executed in real time to get the current SQL syntax parsing result; based on the current SQL syntax parsing result, it is matched with a pre-built SQL template and information repository. If the match is successful, the currently matched SQL template, the key information of the current execution plan corresponding to the currently matched SQL template, and the current execution indicators are obtained; the currently matched SQL template, the key information of the current execution plan, and the current execution indicators are input into a pre-built SQL statement anomaly inference model, and combined with the preset domain knowledge base information to calculate the risk value and obtain the current SQL statement risk assessment value; a preset target risk assessment threshold is obtained, and if the current SQL statement risk assessment value is greater than the target risk assessment threshold, the currently executed SQL statement is intercepted, and target alarm description information is generated. This solves the problems of wasted resources caused by analyzing each SQL statement individually and the low security and high risk caused by post-event handling, improves the efficiency of SQL statement anomaly detection, reduces resource usage and risk, and improves the security of SQL statement processing by performing SQL statement anomaly detection at the front end.
[0018] It should be understood that the description in this section is not intended to identify key or essential features of the embodiments of the present invention, nor is it intended to limit the scope of the invention. Other features of the invention will become readily apparent from the following description. Attached Figure Description
[0019] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.
[0020] Figure 1 This is a flowchart of a database SQL statement anomaly detection method provided in Embodiment 1 of the present invention;
[0021] Figure 2 This is a schematic diagram of the structure of a database SQL statement anomaly detection device according to Embodiment 2 of the present invention;
[0022] Figure 3 This is a schematic diagram of the structure of an electronic device provided according to Embodiment 3 of the present invention. Detailed Implementation
[0023] To enable those skilled in the art to better understand the present invention, the technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present invention.
[0024] It should be noted that the terms "target," "current," etc., used in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.
[0025] It is worth noting that the information collected in the technical solution of this application is information and data authorized by the user or fully authorized by all parties, and the collection, storage, use, processing, transmission, provision, disclosure and application of the relevant data all comply with the relevant laws, regulations and standards of the relevant countries and regions, take necessary confidentiality measures, do not violate public order and good morals, and provide corresponding operation entry points for users to choose to authorize or refuse; if the user chooses to refuse, the process will proceed to the expert decision-making process.
[0026] Example 1
[0027] Figure 1 The flowchart of a database SQL statement anomaly detection method provided in Embodiment 1 of the present invention is applicable to the situation of anomaly detection and interception of each SQL statement to be executed in the database. The method can be executed by a database SQL statement anomaly detection device, which can be implemented in hardware and / or software.
[0028] Correspondingly, such as Figure 1 As shown, the method includes:
[0029] S110. Real-time acquisition and parsing of the SQL statement to be executed, obtaining the current SQL syntax parsing result.
[0030] In this embodiment, SQL statements submitted by the application system or users can be received in real time through the SQL request entry point in the core business module. The parsing module then parses and processes these SQL statements to obtain the current SQL syntax parsing result. This result can contain syntactic information describing the SQL statement, and a SQL template can be determined based on this result.
[0031] Optionally, before matching the current SQL syntax parsing result with a pre-built SQL template and information repository, and obtaining the currently matched SQL template, key information of the current execution plan, and current execution metrics corresponding to the currently matched SQL template, the method further includes: obtaining multiple historical SQL statements, and key information of historical execution metrics and historical execution plans corresponding to each historical SQL statement; extracting each historical SQL statement using a preset constant extraction and template processing method, and performing template processing operations on each statement to obtain at least one SQL template; and jointly storing each SQL template, and key information of historical execution metrics and historical execution plans corresponding to each SQL template to obtain the SQL template and information repository.
[0032] In this embodiment, it is necessary to first obtain multiple historical SQL statements, as well as the corresponding historical execution metrics and key information of historical execution plans. The historical execution metrics may include parameters such as execution time, number of rows scanned, memory usage, CPU utilization, and execution frequency. The key information of the historical execution plan may include parameters such as associated execution timestamps and database load status, and may also include information such as scan method, join operator, and sorting method.
[0033] Furthermore, it is necessary to extract historical SQL statements using constant extraction and template processing methods. For example, assuming a historical SQL statement is WHERE id = 00xx01, since 00xx01 is a variable and "WHERE id=" is a constant, "WHERE id=" is templated to obtain the corresponding SQL template. SQL statements with the same structure can be uniformly stored as templates, and the unique identifier of the template and the associated set of constant parameters can be recorded. Combined with historical execution metrics and key information from historical execution plans, an SQL template and information repository can be formed.
[0034] The SQL template and information repository includes an execution metric data sub-repository, which stores time-series data of metrics by SQL template dimension (e.g., the memory usage trend of a template over the past 30 days). The time-series data sub-repository can be a time-series database such as InfluxDB or TimescaleDB, storing historical execution metric time-series data for each template.
[0035] Optionally, before inputting the currently hit SQL template, key information of the current execution plan, and current execution metrics into the pre-built SQL statement anomaly inference model and calculating the risk value in conjunction with preset domain knowledge base information to obtain the current SQL statement risk assessment value, the method further includes: acquiring multi-source domain integration information; wherein the multi-source domain integration information includes at least one of the following: domain database operation and maintenance description information, domain expert rule description information, fault case description information, and domain practice description information; performing structured data processing on the multi-source domain integration information to obtain standard structured domain integration information, and constructing the domain knowledge base information based on the standard structured domain integration information; wherein the SQL statement anomaly inference model is used to perform real-time querying and inference calls on the domain knowledge base information.
[0036] Specifically, the domain database operation and maintenance description information can include examples such as: a full table scan can cause a sharp increase in risk when the data volume exceeds a preset threshold. Domain expert rule description information can include examples such as: nested subqueries with a depth greater than or equal to a preset threshold can easily cause performance issues. Fault case description information can include examples such as: certain types of statements can lead to deadlocks under high concurrency. Domain practice description information can include examples such as: MySQL index design specifications, etc. The domain knowledge base information can use graph databases such as Neo4j or ArangoDB to store structured knowledge graphs (operation and maintenance rules, fault cases, etc.).
[0037] In this embodiment, the domain database operation and maintenance description information, domain expert rule description information, fault case description information, and domain practice description information first need to be processed into structured data to obtain standard structured domain integration information. This standard structured domain integration information can be stored in the form of a knowledge graph to construct domain knowledge base information.
[0038] Furthermore, the SQL statement anomaly reasoning model can be used to query and infer information from the domain knowledge base in real time, providing rule support for risk assessment and SQL optimization.
[0039] Optionally, before inputting the currently matched SQL template, key information of the current execution plan, and current execution metrics into a pre-built SQL statement anomaly inference model and combining it with preset domain knowledge base information to calculate the risk value and obtain the current SQL statement risk assessment value, the method further includes: obtaining each historical SQL statement, each historical execution metric, and key information of each historical execution plan from the SQL template and information repository, as well as the historical standard risk assessment value corresponding to each historical SQL statement, and obtaining the standard structured domain integration information from the domain knowledge base information; combining the each historical SQL statement, each historical execution metric, and key information of each historical execution plan, as well as the standard structured domain integration information... The input is fed into the initial SQL statement anomaly inference model for training and inference calculation to determine the risk assessment value of each inference model. The accuracy of each inference model risk assessment value is calculated by comparing it with the corresponding historical standard risk assessment value to obtain the accuracy of the current inference model. If the accuracy of the current inference model meets the requirements of the preset inference model accuracy threshold, then the trained SQL statement anomaly inference model is determined. If the accuracy of the current inference model does not meet the requirements of the preset inference model accuracy threshold, then the operation of obtaining each historical SQL statement, each historical execution indicator, each historical execution plan key information, and the historical standard risk assessment value corresponding to each historical SQL statement from the SQL template and information repository is returned.
[0040] In this embodiment, the SQL statement anomaly inference model needs to be trained. Specifically, the initial SQL statement anomaly inference model needs to be trained based on each historical SQL statement, each historical execution metric, each historical execution plan key information, and by invoking standard structured domain integration information. After training, the corresponding inference model risk assessment value can be obtained.
[0041] Furthermore, the risk assessment values of each inference model are compared with their corresponding historical standard risk assessment values, and accuracy is calculated to obtain the current inference model accuracy. If the current inference model accuracy meets the inference model accuracy threshold, then... For example, assume the current inference model accuracy is T, and the inference model accuracy threshold is TH. If so, then it can be determined that the requirements are met, and therefore it can be determined that the trained SQL statement anomaly inference model has been obtained. Assume... If the requirement is not met, it is necessary to return to the operation of obtaining the historical SQL statements, historical execution indicators, key information of historical execution plans, and historical standard risk assessment values corresponding to each historical SQL statement from the SQL template and information repository, and retrain the model until the accuracy threshold is met.
[0042] S120. Based on the current SQL syntax parsing result, match it with the pre-built SQL template and information repository. If the match is successful, obtain the current hit SQL template, as well as the key information of the current execution plan and the current execution indicators corresponding to the current hit SQL template.
[0043] In this embodiment, the current SQL syntax parsing result is used to match the SQL template and information repository. If the match is successful, the current matched SQL template, key information of the current execution plan, and current execution metrics can be obtained.
[0044] Optionally, after matching the current SQL syntax parsing result with the preset SQL template and information repository, the method further includes: if the matching fails, analyzing the currently to-be-executed SQL statement using a pre-set lightweight AI inference module to generate key information for the current inference plan; assessing the risk of the key information for the current inference plan using a pre-constructed high-risk operating environment; if a risk exists, intercepting the currently to-be-executed SQL statement and generating corresponding SQL statement modification suggestions, and generating a modified SQL statement based on the SQL statement modification suggestions; wherein, the modified SQL statement is used to replace the currently to-be-executed SQL statement using a one-click replacement operation and stored in the SQL template and information repository; if no risk exists, allowing the currently to-be-executed SQL statement to proceed, analyzing it using a preset constant extraction and template processing method, and storing the analysis results in the SQL template and information repository.
[0045] The lightweight AI inference module can be a module that analyzes SQL statements. This module requires less memory and can perform real-time analysis of SQL statements. It can also be a module built based on model distillation technology.
[0046] In this embodiment, it is assumed that the current SQL syntax parsing result is used to match the SQL template and information repository. If the match fails, a lightweight artificial intelligence inference module can be used to analyze the SQL statement to be executed and generate key information for the current inference plan. This key information may include scanning method, join operators, and sorting method.
[0047] Furthermore, high-risk operating environments, such as filtering without indexes or operating on tables with large amounts of data, can be used to assess the risks of key information in the current inference plan. If a risk is identified, the currently pending SQL statement is intercepted, and corresponding SQL statement modification suggestions are generated (e.g., suggestions to add a composite index; rewriting the query as a JOIN). The SQL statement can be modified based on these suggestions to obtain the modified SQL statement. Correspondingly, the modified SQL statement can be used to replace the currently pending SQL statement using a one-click replacement operation, and stored in the SQL template and information repository. Modifications should also be recorded, providing information on the modified SQL statement. If no risk is identified, the currently pending SQL statement is allowed to proceed, and the aforementioned constant extraction and template processing methods are used for analysis. The analysis results are then stored in the SQL template and information repository. This approach provides efficient computing power for rapid inference of new SQL statements while ensuring low latency.
[0048] S130. Input the currently hit SQL template, key information of the current execution plan, and current execution indicators into the pre-built SQL statement anomaly inference model, and combine them with the preset domain knowledge base information to calculate the risk value and obtain the current SQL statement risk assessment value.
[0049] In this embodiment, the risk value is calculated using an SQL statement anomaly inference model based on the currently hit SQL template, key information of the current execution plan, current execution metrics, and domain knowledge base information. This allows for the calculation of the corresponding risk assessment value for the current SQL statement. For example, it can be assumed that the obtained risk assessment value for the current SQL statement is... .
[0050] S140. Obtain the preset target risk assessment threshold, and if the current SQL statement risk assessment value is greater than the target risk assessment threshold, intercept the SQL statement to be executed and generate target alarm description information.
[0051] Optionally, after obtaining the preset target risk assessment threshold, the method further includes: if the current SQL statement risk assessment value is less than or equal to the target risk assessment threshold, then the SQL statement to be executed is allowed to proceed.
[0052] Continuing from the previous example, let's assume the target risk assessment threshold is... ,if If the alert indicates a high risk associated with the pending SQL statement, it needs to be intercepted. The intercepted SQL statement should be logged, and the user should be notified in real-time via SMS, email, or platform notifications. Specifically, Elasticsearch can be used to store the intercepted SQL statements and alert descriptions, supporting log retrieval and analysis to provide data support for troubleshooting and optimization.
[0053] Furthermore, if If the current SQL statement is low-risk, it means that the SQL statement to be executed is low-risk and therefore can be allowed to proceed.
[0054] Specifically, after obtaining the preset target risk assessment threshold, and if the current SQL statement risk assessment value is greater than the target risk assessment threshold, intercepting the SQL statement to be executed and generating target alarm description information, the method further includes: providing real-time feedback of the target alarm description information to the user; updating the current execution metrics of the SQL statement to be executed, and storing the updated execution metrics, as well as the intercepted and modified SQL statement record information, in the SQL template and information repository.
[0055] In this embodiment, it is also necessary to update the current execution metrics of the SQL statement to be executed, such as updating and adding parameters such as execution time, number of rows scanned, memory usage, CPU utilization, and execution frequency to reflect the current time.
[0056] Furthermore, the updated execution metrics, as well as the intercepted and modified SQL statement record information (comprising intercepted SQL statement record information and modified SQL statement record information), need to be updated in the optimization record data sub-database stored in the SQL template and information repository. The optimization record data sub-database can use MySQL to store new SQL optimization suggestions, as well as data such as replacement, interception, and modification records.
[0057] The technical solution of this invention obtains and parses the SQL statement to be executed in real time to get the current SQL syntax parsing result; based on the current SQL syntax parsing result, it is matched with a pre-built SQL template and information repository. If the match is successful, the currently matched SQL template, the key information of the current execution plan corresponding to the currently matched SQL template, and the current execution indicators are obtained; the currently matched SQL template, the key information of the current execution plan, and the current execution indicators are input into a pre-built SQL statement anomaly inference model, and combined with the preset domain knowledge base information to calculate the risk value and obtain the current SQL statement risk assessment value; a preset target risk assessment threshold is obtained, and if the current SQL statement risk assessment value is greater than the target risk assessment threshold, the currently executed SQL statement is intercepted, and target alarm description information is generated. This solves the problems of wasted resources caused by analyzing each SQL statement individually and the low security and high risk caused by post-event handling, improves the efficiency of SQL statement anomaly detection, reduces resource usage and risk, and improves the security of SQL statement processing by performing SQL statement anomaly detection at the front end.
[0058] Example 2
[0059] Figure 2 This is a schematic diagram of a database SQL statement anomaly detection device provided in Embodiment 2 of the present invention. The database SQL statement anomaly detection device provided in this embodiment can be implemented by software and / or hardware, and can be configured in a terminal device or server to implement a database SQL statement anomaly detection method according to the present invention. Figure 2 As shown, the device includes: a current SQL syntax parsing result determination module 210, a current hit SQL template, current execution plan key information and current execution indicators determination module 220, a current SQL statement risk assessment value determination module 230 and a target alarm description information generation module 240.
[0060] Among them, the current SQL syntax parsing result determination module 210 is used to obtain and parse the SQL statement to be executed in real time to obtain the current SQL syntax parsing result;
[0061] The module 220 for determining the current hit SQL template, key information of the current execution plan, and current execution metrics is used to match the current SQL syntax parsing result with a pre-built SQL template and information repository. If the match is successful, the module obtains the current hit SQL template, as well as the key information of the current execution plan and the current execution metrics corresponding to the current hit SQL template.
[0062] The current SQL statement risk assessment value determination module 230 is used to input the current hit SQL template, the current execution plan key information and the current execution indicators into the pre-built SQL statement anomaly reasoning model, and combine it with the preset domain knowledge base information to calculate the risk value and obtain the current SQL statement risk assessment value.
[0063] The target alarm description information generation module 240 is used to obtain a preset target risk assessment threshold, and if the risk assessment value of the current SQL statement is greater than the target risk assessment threshold, it intercepts the SQL statement to be executed and generates target alarm description information.
[0064] The technical solution of this invention obtains and parses the SQL statement to be executed in real time to get the current SQL syntax parsing result; based on the current SQL syntax parsing result, it is matched with a pre-built SQL template and information repository. If the match is successful, the currently matched SQL template, the key information of the current execution plan corresponding to the currently matched SQL template, and the current execution indicators are obtained; the currently matched SQL template, the key information of the current execution plan, and the current execution indicators are input into a pre-built SQL statement anomaly inference model, and combined with the preset domain knowledge base information to calculate the risk value and obtain the current SQL statement risk assessment value; a preset target risk assessment threshold is obtained, and if the current SQL statement risk assessment value is greater than the target risk assessment threshold, the currently executed SQL statement is intercepted, and target alarm description information is generated. This solves the problems of wasted resources caused by analyzing each SQL statement individually and the low security and high risk caused by post-event handling, improves the efficiency of SQL statement anomaly detection, reduces resource usage and risk, and improves the security of SQL statement processing by performing SQL statement anomaly detection at the front end.
[0065] Based on the above embodiments, a template mismatch module is further included, which can be specifically used for: after matching the current SQL syntax parsing result with a preset SQL template and information repository, if the matching fails, analyzing the currently to-be-executed SQL statement through a pre-set lightweight artificial intelligence inference module to generate key information of the current inference plan; using a pre-constructed high-risk operating environment to assess the risk of the key information of the current inference plan; if a risk exists, intercepting the currently to-be-executed SQL statement and generating corresponding SQL statement modification suggestions, and generating a modified SQL statement based on the SQL statement modification suggestions; wherein, using the modified SQL statement, a one-click replacement operation is used to replace the currently to-be-executed SQL statement, and it is stored in the SQL template and information repository; if no risk exists, allowing the currently to-be-executed SQL statement to proceed, analyzing it using a preset constant extraction and template processing method, and storing the analysis results in the SQL template and information repository.
[0066] Based on the above embodiments, a release processing module is also included, which can be specifically used to: after obtaining the preset target risk assessment threshold, if the current SQL statement risk assessment value is less than or equal to the target risk assessment threshold, then the SQL statement to be executed is released.
[0067] Based on the above embodiments, the system further includes an SQL template and information repository determination module, which can be specifically used to: before matching the current SQL syntax parsing result with a pre-built SQL template and information repository, and if the matching is successful, obtain the currently matched SQL template, as well as the current execution plan key information and current execution indicators corresponding to the currently matched SQL template, obtain multiple historical SQL statements, as well as the historical execution indicators and historical execution plan key information corresponding to each of the historical SQL statements; extract each of the historical SQL statements using a preset constant extraction and template processing method, and perform template processing operations on each of them to obtain at least one SQL template; and jointly store each SQL template, as well as the historical execution indicators and historical execution plan key information corresponding to each SQL template, to obtain the SQL template and information repository.
[0068] Based on the above embodiments, a domain knowledge base information construction module is further included, which can be specifically used to: obtain multi-source domain integration information before inputting the current hit SQL template, current execution plan key information, and current execution indicators into the pre-built SQL statement anomaly inference model and calculating the risk value in combination with the preset domain knowledge base information to obtain the current SQL statement risk assessment value; wherein, the multi-source domain integration information includes at least one of the following: domain database operation and maintenance description information, domain expert rule description information, fault case description information, and domain practice description information; perform structured data processing on the multi-source domain integration information to obtain standard structured domain integration information, and construct the domain knowledge base information based on the standard structured domain integration information; wherein, the domain knowledge base information is queried and inferred in real time using the SQL statement anomaly inference model.
[0069] Based on the above embodiments, it further includes an SQL statement anomaly inference model training module, which can be specifically used to: before inputting the currently hit SQL template, current execution plan key information, and current execution indicators into the pre-built SQL statement anomaly inference model, and combining it with preset domain knowledge base information to calculate the risk value and obtain the current SQL statement risk assessment value, obtain each historical SQL statement, each historical execution indicator, and each historical execution plan key information, as well as the historical standard risk assessment value corresponding to each historical SQL statement from the SQL template and information storage repository, and obtain the standard structured domain integration information from the domain knowledge base information; and input the each historical SQL statement, each historical execution indicator, and each historical execution plan key information into the SQL template and information storage repository. Information, as well as standard structured domain integrated information, are input into the initial SQL statement anomaly inference model for training and inference calculation to determine the risk assessment value of each inference model; the accuracy of each inference model risk assessment value is calculated by comparing it with the corresponding historical standard risk assessment value to obtain the accuracy of the current inference model; if the accuracy of the current inference model meets the requirements of the preset inference model accuracy threshold, then the trained SQL statement anomaly inference model is determined; if the accuracy of the current inference model does not meet the requirements of the preset inference model accuracy threshold, then the operation of obtaining each historical SQL statement, each historical execution indicator, each historical execution plan key information, and the historical standard risk assessment value corresponding to each historical SQL statement from the SQL template and information repository is returned.
[0070] Based on the above embodiments, a feedback and storage module is also included, which can be specifically used to: after obtaining the preset target risk assessment threshold, and if the current SQL statement risk assessment value is greater than the target risk assessment threshold, intercept the SQL statement to be executed and generate target alarm description information, provide real-time feedback of the target alarm description information to the user; update the current execution indicators of the SQL statement to be executed, and store the updated execution indicators, as well as the intercepted and modified SQL statement record information, in the SQL template and information storage repository.
[0071] The database SQL statement anomaly detection device provided in this embodiment of the invention can execute the database SQL statement anomaly detection method provided in any embodiment of the invention, and has the corresponding functional modules and beneficial effects of the execution method.
[0072] Example 3
[0073] Figure 3 A schematic diagram of an electronic device 10, which can be used to implement Embodiment 3 of the present invention, is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processors, cellular phones, smartphones, wearable devices (such as helmets, glasses, watches, etc.), and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely illustrative and are not intended to limit the implementation of the invention described and / or claimed herein.
[0074] like Figure 3 As shown, the electronic device 10 includes at least one processor 11 and a memory, such as a read-only memory (ROM) 12 or a random access memory (RAM) 13, communicatively connected to the at least one processor 11. The memory stores computer programs executable by the at least one processor. The processor 11 can perform various appropriate actions and processes based on the computer program stored in the ROM 12 or loaded from storage unit 18 into the RAM 13. The RAM 13 can also store various programs and data required for the operation of the electronic device 10. The processor 11, ROM 12, and RAM 13 are interconnected via a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.
[0075] Multiple components in electronic device 10 are connected to I / O interface 15, including: input unit 16, such as keyboard, mouse, etc.; output unit 17, such as various types of displays, speakers, etc.; storage unit 18, such as disk, optical disk, etc.; and communication unit 19, such as network card, modem, wireless transceiver, etc. Communication unit 19 allows electronic device 10 to exchange information / data with other devices through computer networks such as the Internet and / or various telecommunications networks.
[0076] Processor 11 can be a variety of general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special-purpose artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any suitable processor, controller, microcontroller, etc. Processor 11 performs the various methods and processes described above, such as database SQL statement anomaly detection methods.
[0077] In some embodiments, the database SQL statement anomaly detection method may be implemented as a computer program tangibly contained in a computer-readable storage medium, such as storage unit 18. In some embodiments, part or all of the computer program may be loaded and / or installed on electronic device 10 via ROM 12 and / or communication unit 19. When the computer program is loaded into RAM 13 and executed by processor 11, one or more steps of the database SQL statement anomaly detection method described above may be performed. Alternatively, in other embodiments, processor 11 may be configured to execute the database SQL statement anomaly detection method by any other suitable means (e.g., by means of firmware).
[0078] The method includes: acquiring and parsing the SQL statement to be executed in real time to obtain the current SQL syntax parsing result; matching the current SQL syntax parsing result with a pre-built SQL template and information repository; if the match is successful, obtaining the currently matched SQL template, the key information of the current execution plan corresponding to the currently matched SQL template, and the current execution indicators; inputting the currently matched SQL template, the key information of the current execution plan, and the current execution indicators into a pre-built SQL statement anomaly inference model, and combining it with preset domain knowledge base information to calculate the risk value and obtain the current SQL statement risk assessment value; obtaining a preset target risk assessment threshold, and if the current SQL statement risk assessment value is greater than the target risk assessment threshold, intercepting the currently executed SQL statement and generating target alarm description information.
[0079] Various embodiments of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field-programmable gate arrays (FPGAs), application-specific integrated circuits (ASICs), application-specific standard products (ASSPs), systems-on-a-chip (SoCs), payload-programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include implementations in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a dedicated or general-purpose programmable processor, capable of receiving data and instructions from a storage system, at least one input device, and at least one output device, and transmitting data and instructions to the storage system, the at least one input device, and the at least one output device.
[0080] Computer programs used to implement the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when executed by the processor, the computer programs cause the functions / operations specified in the flowcharts and / or block diagrams to be performed. The computer programs may be executed entirely on a machine, partially on a machine, or as a standalone software package, partially on a machine and partially on a remote machine, or entirely on a remote machine or server.
[0081] In the context of this invention, a computer-readable storage medium can be a tangible medium that may contain or store a computer program for use by or in conjunction with an instruction execution system, apparatus, or device. A computer-readable storage medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination thereof. Alternatively, a computer-readable storage medium may be a machine-readable signal medium. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fibers, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof.
[0082] To provide interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse or trackball) through which the user provides input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including sound input, voice input, or tactile input).
[0083] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as data servers), or middleware components (e.g., application servers), or frontend components (e.g., user computers with graphical user interfaces or web browsers through which users can interact with implementations of the systems and technologies described herein), or any combination of such backend, middleware, or frontend components. The components of the system can be interconnected via digital data communication of any form or medium (e.g., communication networks). Examples of communication networks include local area networks (LANs), wide area networks (WANs), blockchain networks, and the Internet.
[0084] A computing system can include clients and servers. Clients and servers are generally located far apart and typically interact through communication networks. The client-server relationship is created by computer programs running on the respective computers and having a client-server relationship with each other. The server can be a cloud server, also known as a cloud computing server or cloud host, which is a hosting product within the cloud computing service system to address the shortcomings of traditional physical hosts and VPS services, such as high management difficulty and weak business scalability.
[0085] It should be understood that the various forms of processes shown above can be used, with steps reordered, added, or deleted. For example, the steps described in this invention can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution of this invention can be achieved, and this is not limited herein.
[0086] The specific embodiments described above do not constitute a limitation on the scope of protection of this invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this invention should be included within the scope of protection of this invention.
[0087] Example 4
[0088] Embodiment 4 of the present invention also provides a computer-readable storage medium, wherein the computer-readable instructions, when executed by a computer processor, are used to execute a database SQL statement anomaly detection method. The method includes: acquiring and parsing the SQL statement to be executed in real time to obtain the current SQL syntax parsing result; matching the current SQL syntax parsing result with a pre-built SQL template and information repository; if the match is successful, obtaining the currently hit SQL template, and the key information of the current execution plan and the current execution indicators corresponding to the currently hit SQL template; inputting the currently hit SQL template, the key information of the current execution plan, and the current execution indicators into a pre-built SQL statement anomaly inference model, and calculating the risk value in combination with preset domain knowledge base information to obtain the current SQL statement risk assessment value; obtaining a preset target risk assessment threshold, and if the current SQL statement risk assessment value is greater than the target risk assessment threshold, intercepting the currently executed SQL statement and generating target alarm description information.
[0089] Of course, the computer-executable instructions provided in the embodiments of the present invention, which include a computer-readable storage medium, are not limited to the method operations described above, but can also execute related operations in the database SQL statement anomaly detection method provided in any embodiment of the present invention.
[0090] Based on the above description of the implementation methods, those skilled in the art can clearly understand that the present invention can be implemented using software and necessary general-purpose hardware, and of course, it can also be implemented using hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of the present invention, 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 a computer floppy disk, read-only memory (ROM), random access memory (RAM), flash memory, hard disk, or optical disk, etc., including several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods described in the various embodiments of the present invention.
[0091] It is worth noting that in the embodiments of the above-mentioned database SQL statement anomaly detection method, the various units and modules included are only divided according to functional logic, but are not limited to the above division, as long as the corresponding functions can be achieved; in addition, the specific names of each functional unit are only for easy differentiation and are not used to limit the scope of protection of the present invention.
[0092] The specific embodiments described above do not constitute a limitation on the scope of protection of this invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this invention should be included within the scope of protection of this invention.
Claims
1. A method for detecting anomalies in database SQL statements, characterized in that, include: It can acquire and parse the SQL statement to be executed in real time to obtain the current SQL syntax parsing result; Based on the current SQL syntax parsing result, it is matched with the pre-built SQL template and information repository. If the match is successful, the current hit SQL template, as well as the key information of the current execution plan and the current execution indicators corresponding to the current hit SQL template are obtained. The current hit SQL template, key information of the current execution plan, and current execution metrics are input into the pre-built SQL statement anomaly inference model, and the risk value is calculated by combining it with the preset domain knowledge base information to obtain the risk assessment value of the current SQL statement. Obtain the preset target risk assessment threshold, and if the risk assessment value of the current SQL statement is greater than the target risk assessment threshold, intercept the SQL statement to be executed and generate target alarm description information.
2. The method according to claim 1, characterized in that, After matching the current SQL syntax parsing result with the preset SQL template and information repository, the process further includes: If the match fails, the pre-set lightweight artificial intelligence inference module will analyze the SQL statement to be executed and generate key information of the current inference plan. By using a pre-constructed high-risk operating environment, risk assessment is performed on the key information of the current inference plan; If there is a risk, the currently pending SQL statement is intercepted and corresponding SQL statement modification suggestions are generated, and the modified SQL statement is generated based on the SQL statement modification suggestions; Specifically, the modified SQL statement is replaced using a one-click replacement operation, and then stored in the SQL template and information repository. If there is no risk, the SQL statement to be executed is allowed to proceed, and it is analyzed using a preset constant extraction and template processing method. The analysis results are then stored in the SQL template and information repository.
3. The method according to claim 2, characterized in that, After obtaining the preset target risk assessment threshold, the method further includes: If the current SQL statement risk assessment value is less than or equal to the target risk assessment threshold, then the SQL statement to be executed will be allowed to proceed.
4. The method according to claim 3, characterized in that, Before the step of matching the current SQL syntax parsing result with a pre-built SQL template and information repository, and obtaining the currently matched SQL template, key information of the current execution plan, and current execution metrics corresponding to the currently matched SQL template, the process further includes: Obtain multiple historical SQL statements, as well as key information on historical execution metrics and historical execution plans corresponding to each historical SQL statement; By using a preset constant extraction and template processing method, each of the historical SQL statements is extracted and then templated to obtain at least one SQL template. Each SQL template, along with its corresponding historical execution metrics and key historical execution plan information, are stored together to obtain the SQL template and information repository.
5. The method according to claim 4, characterized in that, Before inputting the currently matched SQL template, key information of the current execution plan, and current execution metrics into the pre-built SQL statement anomaly inference model, and combining it with preset domain knowledge base information to calculate the risk value and obtain the current SQL statement risk assessment value, the following steps are also included: Acquire integrated information from multiple sources; The multi-source domain integration information includes at least one of the following: domain database operation and maintenance description information, domain expert rule description information, fault case description information, and domain practice description information; The multi-source domain integration information is subjected to structured data processing to obtain standard structured domain integration information, and the domain knowledge base information is constructed based on the standard structured domain integration information. Specifically, the SQL statement exception reasoning model is used to perform real-time querying and reasoning calls on the domain knowledge base information.
6. The method according to claim 5, characterized in that, Before inputting the currently matched SQL template, key information of the current execution plan, and current execution metrics into the pre-built SQL statement anomaly inference model, and combining it with preset domain knowledge base information to calculate the risk value and obtain the current SQL statement risk assessment value, the following steps are also included: Obtain key information on each historical SQL statement, each historical execution indicator, and each historical execution plan from the SQL template and information repository, as well as the historical standard risk assessment value corresponding to each historical SQL statement, and obtain the standard structured domain integration information from the domain knowledge base information; The historical SQL statements, historical execution metrics, key information of historical execution plans, and standard structured domain integration information are input into the initial SQL statement anomaly inference model for training and inference calculation to determine the risk assessment value of each inference model. The accuracy of the current inference model is obtained by comparing the risk assessment value of each inference model with the corresponding historical standard risk assessment value. If the accuracy of the current inference model meets the preset inference model accuracy threshold, then the trained SQL statement anomaly inference model is determined. If the accuracy of the current inference model does not meet the preset inference model accuracy threshold, then return to the operation of obtaining each historical SQL statement, each historical execution indicator, each historical execution plan key information, and the historical standard risk assessment value corresponding to each historical SQL statement from the SQL template and information repository.
7. The method according to claim 3, characterized in that, After obtaining the preset target risk assessment threshold, and intercepting the currently to-be-executed SQL statement if the current SQL statement's risk assessment value is greater than the target risk assessment threshold, and generating target alarm description information, the method further includes: The target alarm description information will be fed back to the user in real time; The current execution metrics of the SQL statement to be executed are updated, and the updated execution metrics, as well as the intercepted and modified SQL statement record information, are stored in the SQL template and information repository.
8. A database SQL statement anomaly detection device, characterized in that, include: The current SQL syntax parsing result determination module is used to obtain and parse the SQL statement to be executed in real time to obtain the current SQL syntax parsing result; The module for determining the current hit SQL template, key information of the current execution plan, and current execution metrics is used to match the current SQL syntax parsing result with a pre-built SQL template and information repository. If the match is successful, the module obtains the current hit SQL template, as well as the key information of the current execution plan and the current execution metrics corresponding to the current hit SQL template. The current SQL statement risk assessment value determination module is used to input the current hit SQL template, the current execution plan key information and the current execution indicators into the pre-built SQL statement anomaly inference model, and combine it with the preset domain knowledge base information to calculate the risk value and obtain the current SQL statement risk assessment value. The target alarm description information generation module is used to obtain the preset target risk assessment threshold, and if the risk assessment value of the current SQL statement is greater than the target risk assessment threshold, it intercepts the SQL statement to be executed and generates target alarm description information.
9. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements a database SQL statement anomaly detection method as described in any one of claims 1-7.
10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer instructions that cause a processor to execute a database SQL statement anomaly detection method as described in any one of claims 1-7.