Database sql auditing method, device and medium

Abstract

The application relates to the technical field of databases, and discloses a database SQL auditing method and device and a medium, which comprises the following steps: configuring connection information of a target database to be audited; binding the connection information with a listening port so as to create and start a corresponding proxy task; receiving a front-end connection initiated and established by a business system and a back-end connection actively established by the proxy task and the target database through the listening port corresponding to the proxy task; performing bidirectional flow forwarding between the front-end connection and the back-end connection, and intercepting a request data stream sent by the business system to the target database during the bidirectional flow forwarding; performing database protocol analysis on the request data stream, restoring a SQL statement to be executed by the business system as an outer SQL; performing analysis on the outer SQL, identifying and extracting an inner DDL which is internally and dynamically executed; and encapsulating the outer SQL and the inner DDL as an auditing event, and performing corresponding processing on the auditing event.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a method, device and medium for auditing database SQL. Background Technology

[0002] With increasingly stringent regulations on centralized enterprise data management and data security, comprehensive, accurate, and real-time auditing of database access operations by business systems has become an essential means to meet the needs of security compliance, behavior tracing, fault diagnosis, and operation and maintenance supervision.

[0003] Existing technical solutions have shortcomings. Most rely on database logs, resulting in poor real-time performance and increased load. Application-side interception solutions are highly intrusive and have high adaptation costs, while general network proxy solutions cannot deeply parse protocols and dynamic SQL, leading to missed DDL audits. Furthermore, existing solutions often focus on the data collection phase, with loosely defined modules for real-time processing, archiving, and statistical analysis of audit events, making it difficult to form an efficient, unified, and manageable integrated closed-loop system. Therefore, a better new method for database SQL auditing is urgently needed. Summary of the Invention

[0004] This specification provides one or more embodiments of a database SQL auditing method, device, and medium to solve the technical problems raised in the background art.

[0005] One or more embodiments of this specification employ the following technical solutions: This specification provides one or more embodiments of a database SQL auditing method, the method comprising: Configure the connection information for the target database to be audited; The connection information is bound to the listening port in order to create and start the corresponding proxy task; The proxy task receives front-end connections initiated and established by the business system and back-end connections actively established by the proxy task with the target database through the listening port corresponding to the proxy task. Bidirectional traffic forwarding is performed between the front-end connection and the back-end connection, and during the bidirectional traffic forwarding, the request data stream sent by the business system to the target database is intercepted; The request data stream is parsed using database protocols to reconstruct the SQL statement to be executed by the business system, which serves as the outer SQL. The outer SQL is parsed to identify and extract the dynamically executed inner DDL. The outer SQL and the inner DDL are encapsulated into audit events, and the audit events are processed accordingly.

[0006] This specification provides one or more embodiments of a database SQL auditing device, comprising: At least one processor and bus; and, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, the instructions being executed by the at least one processor to enable the at least one processor to: Configure the connection information for the target database to be audited; The connection information is bound to the listening port in order to create and start the corresponding proxy task; The proxy task receives front-end connections initiated and established by the business system and back-end connections actively established by the proxy task with the target database through the listening port corresponding to the proxy task. Bidirectional traffic forwarding is performed between the front-end connection and the back-end connection, and during the bidirectional traffic forwarding, the request data stream sent by the business system to the target database is intercepted; The request data stream is parsed using database protocols to reconstruct the SQL statement to be executed by the business system, which serves as the outer SQL. The outer SQL is parsed to identify and extract the dynamically executed inner DDL. The outer SQL and the inner DDL are encapsulated into audit events, and the audit events are processed accordingly.

[0007] This specification provides one or more embodiments of a non-volatile computer storage medium storing computer-executable instructions, which, when executed by a computer, can perform the following: Configure the connection information for the target database to be audited; The connection information is bound to the listening port in order to create and start the corresponding proxy task; The proxy task receives front-end connections initiated and established by the business system and back-end connections actively established by the proxy task with the target database through the listening port corresponding to the proxy task. Bidirectional traffic forwarding is performed between the front-end connection and the back-end connection, and during the bidirectional traffic forwarding, the request data stream sent by the business system to the target database is intercepted; The request data stream is parsed using database protocols to reconstruct the SQL statement to be executed by the business system, which serves as the outer SQL. The outer SQL is parsed to identify and extract the dynamically executed inner DDL. The outer SQL and the inner DDL are encapsulated into audit events, and the audit events are processed accordingly.

[0008] The above-described at least one technical solution adopted in the embodiments of this specification can achieve the following beneficial effects: The database SQL auditing method provided by this invention, by deploying a transparent proxy architecture on the network communication link, fundamentally avoids modification of the business system source code and database server configuration, thereby achieving non-intrusive audit data collection and significantly reducing deployment complexity and impact on the stability of existing systems. The proxy intercepts traffic while forwarding it and, through deep analysis of the database's private communication protocol, can reconstruct various types of SQL, including pre-compiled statements, ensuring the accuracy of basic information collection.

[0009] Furthermore, this invention includes an in-depth analysis step of the captured SQL, which can identify and extract data definition statements generated dynamically through execution in database functions and stored procedures. This solves the audit blind spots caused by statement nesting and dynamic splicing, and significantly improves the completeness of auditing high-risk operations such as structural changes.

[0010] Finally, this invention establishes a standardized process from SQL capture, parsing, and restoration to event encapsulation and processing. This enables audit data to be processed in real time and in a structured manner, and supports multiple utilization methods such as persistent storage, file archiving, and subsequent analysis, forming a closed-loop management system from data collection to value application.

[0011] Therefore, this invention comprehensively achieves non-intrusive audit deployment, accurate and complete information collection, and provides end-to-end processing capabilities, effectively solving the shortcomings of traditional solutions in terms of implementation cost, audit omissions, and functional fragmentation. Attached Figure Description

[0012] To more clearly illustrate the technical solutions in the embodiments or prior art of this specification, the drawings used in the description of the embodiments or prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in this specification. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. In the drawings: Figure 1 A flowchart illustrating a database SQL auditing method provided in one or more embodiments of this specification; Figure 2 This is a schematic diagram of the structure of a database SQL auditing device provided for one or more embodiments of this specification. Detailed Implementation

[0013] This specification provides an example of a method, device, and medium for auditing database SQL.

[0014] With the increasing demand for centralized database operation and maintenance, database security auditing, business system behavior tracking, and data compliance governance, more and more organizations need to fully record and analyze the database access behavior of business systems. Existing database auditing methods can be broadly categorized into three types: The first type is the database logging solution. This typically involves enabling native database logging, auditing plugins, or mechanisms like `log_statement` or `pgaudit` to record SQL statements, which are then collected, filtered, and displayed by a log collection program. While this approach is relatively simple to deploy, it increases database-side logging overhead and is easily affected by factors such as database log levels, log rotation strategies, and plugin installation permissions, resulting in limitations on real-time performance and completeness.

[0015] The second type is application-side interception solutions. These typically intercept SQL queries within business system applications using ORM framework interceptors, JDBC wrapper drivers, AOP aspects, and Agent bytecode enhancement. This approach requires intruding into application code or the runtime environment, resulting in high adaptation costs for different languages, frameworks, and versions, and making it difficult to uniformly cover existing systems.

[0016] The third category is network proxy solutions. These typically add a proxy component between the business system and the database. This component listens on a port, receives client connections, and forwards them to the database. This approach offers inherent link-level transparency, but most existing proxy solutions focus more on connection pooling, load balancing, connection reuse, or high availability management, lacking fine-grained SQL parsing, dynamic DDL reconstruction, archive statistics, and closed-loop auditing design specific to database protocols.

[0017] The existing technology has the following technical drawbacks, and its limitations can be deduced through causal relationships: 1. Since the database logging solution relies on the database's own output logs, when the database log level is lowered, the log policy is restricted, or the auditing plugin cannot be installed, it will directly lead to incomplete SQL capture; at the same time, log writing to disk and the log collection link will bring additional I / O and latency, resulting in poor real-time performance and high resource overhead.

[0018] 2. Since application-side interception solutions rely on business system frameworks, drivers, or runtime instrumentation, if a business system uses multiple languages, multiple middleware, or multiple independent connection management methods, it will lead to high access costs, heavy adaptation work, and incomplete coverage, making it difficult to form a unified audit view.

[0019] 3. Since general network proxies typically only perform byte forwarding and not database protocol semantic parsing, when business systems use PostgreSQL extended query protocols, prepared statements, or procedural languages, the proxy can only see the underlying request packets and cannot recognize the SQL semantics, thus failing to accurately extract the complete SQL.

[0020] 4. Because existing solutions can usually only identify the outer SQL entry point, when the actual database object changes are executed through CALL, SELECT exec_ddl(...), EXECUTE function body, or dynamic concatenation within triggers, only the call entry point can be seen, but the actual CREATE, ALTER, DROP, and other DDL operations cannot be seen, resulting in missed detections in DDL auditing.

[0021] 5. Since most existing solutions separate the processes of "collection, display, archiving, and statistics" and lack a unified event model and a unified task state machine, it is difficult to achieve millisecond-level real-time monitoring, daily file archiving, and multi-dimensional statistical analysis capabilities.

[0022] 6. Due to the lack of port uniqueness verification, data source health checks, and task recovery mechanisms in multi-task scenarios, existing solutions are prone to issues such as task status distortion, business interruption, or slow recovery when the proxy task restarts, the port is occupied, or the target database is temporarily unavailable.

[0023] Therefore, the purpose of this invention is to propose a SQL capture and real-time auditing method and system for transparent database agents. By using database protocol-level parsing, extended query compatible parsing, dynamic DDL recursive restoration, multi-task isolation management, and multi-channel unified consumption mechanism, this invention solves the problems of poor real-time performance, high invasiveness, invisible dynamic DDL, uncontrollable tasks, and incomplete analysis loop in existing technologies, and provides a database auditing technology solution that is both innovative and engineering-applicable.

[0024] To enable those skilled in the art to better understand the technical solutions in this specification, the technical solutions in the embodiments of this specification will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this specification, and not all embodiments. Based on the embodiments of this specification, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of this specification.

[0025] Figure 1This diagram illustrates a process for auditing database SQL according to one or more embodiments of this specification. This process can be executed by a database SQL auditing system. Certain input parameters or intermediate results in the process can be manually adjusted to help improve accuracy.

[0026] The method flow steps of the embodiments in this specification are as follows: S101, Configure the connection information for the target database to be audited.

[0027] In the embodiments described in this specification, within the management service of the auditing system, detailed connection parameters of the target database are manually entered or imported in batches through the data source management interface. These parameters typically include the database type (e.g., PostgreSQL, MySQL), host address, port number, database instance name, username, and password. The system encrypts and stores sensitive information such as passwords. This step establishes the specific target object for auditing and provides the necessary network addressing and authentication basis for subsequent proxy tasks. Administrators can verify and adjust the connection parameters at this stage.

[0028] S102, bind the connection information to the listening port in order to create and start the corresponding proxy task.

[0029] In the embodiments described in this specification, based on the data source configured and verified in S101, the system's agent task management function binds an unused local network listening port to the data source, thereby instantiating an independent agent task. This task has a unique task identifier and enters a startable state. Before starting, the system performs a port uniqueness check to prevent conflicts. This step transforms static connection information into a dynamic running entity with a clearly defined listening entry point (port) and forwarding target (data source).

[0030] S103, through the listening port corresponding to the proxy task, receive the front-end connection initiated and established by the business system, and the back-end connection actively established by the proxy task with the target database.

[0031] In the embodiments described in this specification, after the proxy task starts, its transparent proxy engine begins listening on the designated port. When the business application (client) modifies its database connection configuration to point to this proxy address and port, it initiates a connection to the proxy, which accepts the connection, forming a "front-end connection." Subsequently, the proxy engine immediately uses the target database connection information configured in S101 to actively initiate a connection to the real database server, forming a "back-end connection." At this point, the proxy establishes a complete communication link between the business system and the target database, forwarded by the proxy. The implementation scheme requires special handling of the connection establishment timing, for example, by employing a first-packet caching mechanism to ensure that the first batch of data sent by the business system during the establishment of the back-end connection is not lost.

[0032] S104, bidirectional traffic forwarding is performed between the front-end connection and the back-end connection, and during the bidirectional traffic forwarding, the request data stream sent by the business system to the target database is intercepted.

[0033] In the embodiments described in this specification, the transparent proxy engine performs real-time, transparent bidirectional forwarding of network packets between the established front-end and back-end connections, ensuring the normal operation of business communication. Simultaneously, capture points are set up along the forwarding path to copy and intercept all request packets sent from the business end (front-end connection) to the database end (back-end connection), forming a complete copy of the request data stream for use by subsequent analysis modules. The original data stream continues to be forwarded unaffected, thus achieving seamless capture of business operations.

[0034] S105, perform database protocol parsing on the request data stream to restore the SQL statement to be executed by the business system, which is then used as the outer SQL.

[0035] In the embodiments described in this specification, the protocol parsing module performs in-depth analysis on the copy of the request data stream intercepted by S104. First, based on the type of the target database, the byte stream is split into frames according to its communication protocol specifications to accurately identify each independent protocol message. Then, semantic parsing is performed based on the message type: for simple query messages, the SQL text is directly extracted; for complex extended query protocols (involving pre-compiled statements), a series of related messages such as "parse," "bind," and "execute" are parsed to dynamically reconstruct the final, actually executed, and parameter-bound complete SQL statement. The output of this step is the "outer SQL" directly captured and reconstructed from the network layer.

[0036] S106, parse the outer SQL, identify and extract the inner DDL that is dynamically executed.

[0037] In the embodiments of this specification, the dynamic DDL reconstruction module performs deep syntax and lexical analysis on the outer SQL obtained in S105. The process includes: stripping SQL comments; extracting string literals from the SQL text; identifying the presence of keywords or function calls used for dynamically executing SQL, such as EXECUTE or sp_executesql. If such dynamic execution points are identified, the subsequent expression content is analyzed, and identifiable text fragments are concatenated and reconstructed to reconstruct the SQL statement fragment dynamically generated and executed within the database. This fragment undergoes recursive checking and type determination; if it begins with Data Definition Language (DDL) keywords such as CREATE, ALTER, or DROP, it is identified and extracted as "inner DDL." This step solves the problem of auditing changes to dynamically generated objects within stored procedures and functions.

[0038] S107, the outer SQL and the inner DDL are encapsulated into an audit event, and the audit event is processed accordingly.

[0039] In the embodiments described in this specification, the SQL event distribution module encapsulates the outer SQL restored in S105 and the inner DDL extracted in S106 (if present), along with context information such as timestamps, source tasks, client IPs, and database users, into a standardized "audit event." This event is then placed into an in-memory message queue, decoupling the acquisition and processing links. An independent consumption thread retrieves the event from the queue and asynchronously performs multi-channel processing: the event is structured and persistently stored in the audit database; the SQL text and time information are written to an archive file according to the "agent task-date" dimension; and the event is pushed to the front-end monitoring interface in real time for display using long-connection technologies such as WebSocket. Furthermore, statistical analysis can be performed based on the persistent historical events, such as SQL type distribution and high-frequency statement statistics.

[0040] It should be noted that the database SQL auditing method provided by this invention, by deploying a transparent proxy architecture on the network communication link, fundamentally avoids modification of the business system source code and database server configuration, thereby achieving non-intrusive audit data collection and significantly reducing deployment complexity and impact on the stability of existing systems. The proxy intercepts traffic while forwarding it and, through deep analysis of the database's private communication protocol, can reconstruct various types of SQL, including pre-compiled statements, ensuring the accuracy of basic information collection.

[0041] Furthermore, this invention includes an in-depth analysis step of the captured SQL, which can identify and extract data definition statements generated dynamically through execution in database functions and stored procedures. This solves the audit blind spots caused by statement nesting and dynamic splicing, and significantly improves the completeness of auditing high-risk operations such as structural changes.

[0042] Finally, this invention establishes a standardized process from SQL capture, parsing, and restoration to event encapsulation and processing. This enables audit data to be processed in real time and in a structured manner, and supports multiple utilization methods such as persistent storage, file archiving, and subsequent analysis, forming a closed-loop management system from data collection to value application.

[0043] Therefore, this invention comprehensively achieves non-intrusive audit deployment, accurate and complete information collection, and provides end-to-end processing capabilities, effectively solving the shortcomings of traditional solutions in terms of implementation cost, audit omissions, and functional fragmentation.

[0044] Furthermore, the request packet caching and delayed forwarding mechanism included in this invention directly solves a key technical pain point in network proxy-based auditing schemes. When traditional proxies forward traffic, if the backend database connection is not yet ready, the first batch of request data packets from the business system (usually containing login authentication information) may be lost because they cannot be processed in time. This can lead to business connection failures or missing audit records. The method flow steps in the embodiments of this specification are as follows: S201, before the proxy task actively establishes a backend connection with the target database, it caches the first batch of request packets sent in advance by the business system into the forwarding queue.

[0045] In the embodiments described in this specification, after the proxy task accepts the connection from the business system through the listening port and successfully establishes a "front-end connection," it does not immediately begin automatically reading and forwarding data on that connection. Instead, the proxy task first attempts to initiate a "back-end connection" to the target database. During the brief time window before this back-end connection is successfully established, if the business system client (for performance optimization or protocol characteristics) has already sent data packets (i.e., "initial request packets," typically containing startup and authentication information) through the front-end connection, the proxy task's connection processing module will intercept these early-arriving data packets and temporarily store them in a "forwarding queue" (a memory cache structure) specifically allocated for this session, rather than discarding or blocking them. This ensures that even in the event of network latency or a slightly slower database response, any initial data sent by the business system will not be lost.

[0046] S202, after the proxy task actively establishes a backend connection with the target database, it forwards the first batch of request packets to the target database.

[0047] In the embodiments described in this specification, once the "backend connection" between the proxy task and the target database is successfully established, the proxy task will immediately activate the automatic reading of the "frontend connection". Simultaneously, the proxy task will first check the "forwarding queue" corresponding to the session. If the queue contains "initial request packets" already cached in step S201, the proxy task will strictly follow the order in which these packets were received, retrieving them sequentially from the queue and forwarding them completely to the target database server through the established "backend connection". After this, the proxy task enters a normal bidirectional real-time forwarding state. This design ensures that all operation commands sent by the business system at the initial connection stage are accurately delivered to the database, thus avoiding business connection failures or login errors caused by the proxy forwarding link not being fully ready. It also ensures that the subsequent auditing module can capture 100% complete initial session traffic, laying a reliable data foundation for end-to-end auditing.

[0048] It should be noted that this invention introduces a cache queue to proactively store request packets arriving during connection establishment. These packets are then forwarded sequentially once the bidirectional communication link is stably established, fundamentally eliminating data loss caused by the proxy's own connection establishment timing. This design ensures 100% integrity of audit data collection. Even in scenarios involving proxy task restarts or network interruptions followed by reconnection, every database access initiated by the business system and its corresponding SQL statement can be reliably captured without omission.

[0049] Meanwhile, because caching and forwarding are completely transparent to the business system, this mechanism maintains the continuity and stability of business access while ensuring the integrity of audit data, achieving a balance between audit reliability, integrity, and zero-perception for the business. This gives the entire auditing methodology the robustness necessary for a production-grade system.

[0050] Furthermore, in the process of parsing the request data stream using the database protocol, the method flow steps in this embodiment are as follows: S301, the request data stream is split at the database protocol frame level, and semantic parsing is performed according to the message type. The semantic parsing includes: parsing the startup message to extract connection metadata, parsing the preset query message to directly extract SQL text, and parsing the extended query protocol message. The extended query protocol message includes: parsing the pre-compiled parsing message to establish a mapping relationship between statement names and SQL templates, parsing the binding message to establish a mapping relationship between portal names and statement names, and parsing the execution message to reconstruct the actually executed SQL text as the outer SQL based on the mapping relationship between portal names and statement names and the mapping relationship between statement names and SQL templates.

[0051] In the embodiments described in this specification, an unprocessed raw "request data stream" (i.e., a continuous sequence of bytes) is received from the preceding traffic interception step. The protocol parsing module first performs "frame-level splitting" on the byte stream according to the communication protocol specifications of the target database (such as PostgreSQL). Specifically, the module parses the header of each protocol message, identifies its type identifier and length field, thereby accurately delineating the boundaries of each independent and complete protocol message frame, effectively handling the "packet merging" and "packet splitting" phenomena that may occur in network transmission, and providing correct input units for subsequent semantic analysis.

[0052] Subsequently, the system performs semantic parsing on each extracted protocol message frame based on its message type identifier. This parsing process mainly involves processing the following three core message types: Parsing the startup message: When a connection establishment startup message is detected, its fields are parsed to extract and record connection metadata, such as the client protocol version, the connected database name, and the username. This metadata provides important context for auditing events.

[0053] Parsing simple query messages: When a simple query is detected (corresponding to "simple query message Q" in the documentation), the complete SQL command string carried in the message payload is read directly and extracted as the "outer SQL". This is the most direct way to handle SQL submissions.

[0054] Parsing Extended Query Protocol (ESP) messages: This is the core of handling complex scenarios such as prepared statements and stored procedures. Its parsing is a stateful process involving the coordinated processing of multiple related messages. Parse the pre-compiled parsing message: When a pre-compiled parsing message is detected, its content is parsed, the mapping relationship between the "statement name" and the parameterized SQL template (i.e., SQL text containing placeholders) is recorded, and this mapping is saved in the context of the current session.

[0055] Parsing the binding message: When a binding message is detected, its content is parsed, and the mapping relationship between the "portal name" and the aforementioned "statement name" is recorded and stored in the session context. At this point, the portal is associated with an SQL template through the statement name.

[0056] Parsing the execution message: When an execution message is detected, the "portal name" to be executed is parsed out. The system then searches according to the established mapping relationship: first, it finds the corresponding "statement name" through the "portal name," and then it finds the previously saved "SQL template" through the "statement name." At this point, combined with the specific parameter values ​​that may be carried in the binding message, the system can reconstruct the complete SQL text corresponding to this execution, where the parameters have actually been bound. This text is then output as the "outer SQL."

[0057] Through the above implementation scheme, the system can cover various database access modes from simple queries to complex extended queries, ensuring that all SQL operations intended to be executed by the business system are accurately and completely restored at the network protocol level, providing accurate input for subsequent dynamic DDL restoration and audit event generation.

[0058] It should be noted that the detailed data message protocol parsing mechanism in this invention forms the technical foundation for achieving high-fidelity SQL auditing. Traditional network proxies or logging solutions, when faced with modern application frameworks employing advanced features such as Prepared Statements, often only capture SQL templates with parameter placeholders (e.g., INSERT INTO table(id, name) VALUES ($1, $2)), failing to determine the actual bound parameter values. This results in audit logs lacking readability and investigative value; furthermore, they cannot fully trace the complex database operation sequences generated by the ORM framework and sent via extended query protocols. This invention, through implementing end-to-end processing from byte stream frame-level splitting to semantic-level parsing of various protocol messages, particularly precise parsing of extended query protocols, can dynamically reconstruct the mapping chains between "statement name" and "SQL template," and "portal name" and "statement name," thereby restoring at the protocol layer the complete, parameter-bound, and directly executable SQL text corresponding to each database request. This ensures that regardless of the data access technology used by the business system, the "outer SQL" captured by the audit system can truly and accurately reflect the actual intent and operational details of the application, fundamentally eliminating the distortion or lack of audit information caused by insufficient protocol parsing, and providing a reliable and semantically complete data foundation for subsequent compliance reviews, security incident tracing and performance analysis.

[0059] Furthermore, in the process of parsing the outer SQL, identifying and extracting the dynamically executed inner DDL, the method flow steps in this embodiment are as follows: S401, process the outer SQL to remove the single-line comment and block comment at the beginning of the outer SQL.

[0060] In the embodiments described in this specification, the dynamic DDL restoration module first preprocesses the input outer SQL text. Its "comment stripping submodule" scans the beginning of the SQL text, identifies and removes single-line comments beginning with specific markers (such as "--"), and block comments enclosed in " / *" and "* / ". This step aims to eliminate interference from non-executable comment information on subsequent keyword location and semantic analysis, ensuring that subsequent processing focuses on the actual valid code.

[0061] S402 extracts specified text content from a preset string from the processed outer SQL.

[0062] In the embodiments of this specification, the "literal extraction submodule" of the module scans the SQL text after annotation removal. Its core task is to identify and extract all text content existing in the form of preset strings, mainly including: regular strings enclosed in single quotes ('), and "dollar sign reference" strings supported by specific databases (such as $tag$...$tag$). The text content in these string constants is completely extracted as the "specified text content" for subsequent analysis, because these text blocks may encapsulate dynamically concatenated SQL code fragments.

[0063] S403, identify whether there are execution expression keywords in the specified text content for dynamically executing SQL.

[0064] In the embodiments described in this specification, the "Execution Expression Recognition Submodule" of the module examines one or more specified text contents extracted in step S402 one by one. It searches these text contents for keywords indicating dynamic SQL execution, such as EXECUTE, EXECUTE IMMEDIATE, PERFORM (in PL / pgSQL), or similar commands for a specific database. Identifying such keywords is direct evidence that the text block may contain dynamically generated SQL.

[0065] S404, if the keyword of the execution expression is identified, then the expression content following the specified text content is spliced ​​and reconstructed to restore the inner SQL text.

[0066] In the embodiments described in this specification, when an execution expression keyword is identified in a certain text content, the module's "concatenation and reconstruction submodule" will perform in-depth analysis of the expression portion following that keyword. This expression is typically a string concatenation expression. The module will parse this expression, identify the static text fragments (i.e., the string constants directly written in the code), and concatenate them according to their logical order in the expression. For dynamic parts such as variable references or function calls in the expression, uniform placeholders (such as {var}) are set in the reconstructed text. Through this process, a complete SQL statement text that is as close as possible to the actual execution semantics is generated, i.e., the "inner SQL text".

[0067] S405, recursively check the inner SQL text to determine whether the inner SQL text starts with a predefined keyword. If so, the inner SQL text is determined to be the inner DDL.

[0068] In the embodiments described in this specification, firstly, the "recursive check submodule" of the module checks the "inner SQL text" itself obtained from S404 to determine whether it still contains string literals and whether dynamic SQL execution logic is nested within these literals. If so, the analysis similar to S402 to S404 is recursively performed on the new inner text, with the document mentioning a preferred recursion depth of 4 levels. Then, the "DDL classification submodule" of the module performs type determination on the finally obtained inner SQL text. The module maintains a predefined list of Data Definition Language (DDL) keywords (such as CREATE, ALTER, DROP, TRUNCATE, COMMENT, GRANT, REVOKE, etc.). It checks whether the inner SQL text begins with any keyword in the list. If so, the text is determined to be a data definition statement, and it is finally output as the "inner DDL" that needs to be audited independently.

[0069] It should be noted that the dynamic DDL parsing and restoration process detailed in this invention specifically addresses the long-standing technical challenge of "invisibility of deep operations" in database auditing. Traditional auditing solutions, when encountering operations initiated through database stored procedures, functions, or dynamically executed statements, can only record the outer call entry point, remaining helpless against the core data definition statements dynamically generated and executed through string concatenation and conditional logic, creating an audit blind spot. This invention constructs a mechanism capable of "deep syntax mining" of outer-layer SQL by sequentially executing a series of structured analysis steps: comment stripping, preset string extraction, dynamic execution keyword identification, text fragment reconstruction, and recursive type checking. This mechanism can proactively explore and strip non-core comment information, accurately locate text blocks that may encapsulate internal logic, identify key dynamic execution points, and intelligently concatenate and semantically reconstruct scattered code fragments to restore the hidden, dynamically generated inner-layer SQL statements. Finally, by performing strict type determination based on predefined keywords on the restored statements, the system can accurately filter out the data definition language statements that actually change the database structure. This entire process ensures that the audit system can not only capture SQL operations that are directly initiated on the surface, but also penetrate complex code encapsulation layers to directly trace the root operation instructions that ultimately lead to changes in database tables, indexes, permissions, and other objects. This achieves accurate tracing of database structure change behavior throughout the entire path without omission, greatly improving the depth and credibility of audit results. It provides key technical support that was previously difficult to obtain to meet the requirements of strict compliance audits and root cause analysis of security incidents.

[0070] Furthermore, the corresponding processing of the audit event includes at least one of the following processing methods: S501, the audit event is persistently stored in the audit database.

[0071] In the embodiments described in this specification, the audit system has a dedicated audit database. Once an audit event is encapsulated, the persistence module in the event processing pipeline is responsible for performing this operation. This module parses the structured content of the audit event, including fields such as event ID, associated agent task ID, execution timestamp, client information, SQL type (e.g., DML / DDL), and complete SQL text (including outer SQL and inner DDL). Subsequently, the module maps this information according to a predefined audit table structure (e.g., the captured_sql table) and inserts records into the target table through database operations. This process transforms unstructured network traffic into structured data that can be efficiently queried and analyzed, providing a unique and reliable data source for subsequent historical retrieval, statistical analysis, and compliance report generation. The connection configuration, table structure, and storage strategy of the audit database can be pre-configured on the system management end.

[0072] S502, write the SQL text and execution time information in the audit event into the archive file according to the two dimensions of agent task and date.

[0073] In the embodiments described in this specification, to meet the compliance requirements for offline tracking of raw operation logs, the system synchronously performs file archiving. The file archiving module extracts key information from audit events: the agent task ID, agent task name, the specific date of SQL execution, and the SQL text itself. The module generates an archive file according to a fixed naming rule, such as capture_{task ID}_{task name}_{YYYYMMDD}.sql. Then, the module appends a record to this file. This record typically begins with a timestamp line (format: -- Execution Time: YYYY-MM-DDHH:MM:SS.sss), followed immediately by the corresponding SQL text. Each file strictly corresponds to all SQL operations of an agent task within a specific calendar day, thus forming a sequence of raw operation logs that is directly readable by humans and indexed by both task and date. This archiving method is independent of database storage, ensuring the immutability and long-term traceability of audit evidence.

[0074] S503 pushes the audit events to the monitoring interface in real time for display via a WebSocket long connection.

[0075] In the embodiments described in this specification, to achieve real-time monitoring with millisecond-level latency, the system establishes a WebSocket long-lived connection channel from the front-end monitoring interface to the back-end service. When a new audit event occurs, the real-time push module immediately captures it. This module converts the audit event into a lightweight data format suitable for network transmission (such as JSON), and then actively pushes the event data packet to the front-end browser client connected to the channel through the established WebSocket connection corresponding to the specific monitoring session. After receiving the push, the front-end monitoring interface dynamically updates the event information (such as time, source, abbreviated SQL, etc.) to the real-time monitoring list or dashboard. Typically, to improve user experience, when the front-end interface subscribes to the monitoring of a certain agent task for the first time, the system first pulls recently occurred audit events through an HTTP request to populate the interface, and then relies entirely on WebSocket to receive incremental events, thereby avoiding the historical data gap period when the interface is opened. This allows operations and security personnel to perceive all database access dynamics in near real-time.

[0076] It should be noted that the diversified parallel processing channels set up by this invention for audit events construct a complete utility loop from data collection to final value realization, significantly enhancing the practicality and reliability of the audit system. Persistently storing audit events in a dedicated audit database provides a foundation for structured storage and efficient retrieval of massive audit records, enabling historical behavior tracing, complex condition queries, and correlation analysis, laying the cornerstone for long-term data utilization. Simultaneously, writing the original SQL text and execution time into an archive file based on both the proxy task and date dimensions—an operation independent of database storage—generates an immutable, compliant chain of original operation log evidence, ensuring that even in extreme cases, offline, complete records are available for audit verification, meeting the most stringent data security regulations' requirements for original traceability. Furthermore, pushing audit events to the monitoring interface in real time via a WebSocket long connection allows operations and security personnel to observe ongoing database access behavior with near-zero latency, providing a crucial time window for real-time anomaly detection, immediate fault diagnosis, and dynamic risk intervention. These three processing methods complement each other, transforming raw, instantaneous network traffic data into core information assets with long-term management value, compliance audit value, and real-time operation and maintenance value from three dimensions: long-term traceability, compliance evidence storage, and real-time perception.

[0077] Furthermore, after processing the audit event accordingly, the method flow steps of this embodiment are as follows: S601, based on the persisted audit events, perform statistical analysis, the statistical analysis including at least one of the following: SQL type distribution statistics, high-frequency SQL statement statistics, and SQL execution trend statistics by time dimension.

[0078] In the embodiments described in this specification, this step is a crucial step in extracting the value of audit data. Its input comes from the massive historical audit event records that have been structured and stored in the audit database in step S501. The system's statistical analysis module will periodically (e.g., every minute, every hour) or on demand (e.g., when a user triggers a query on the front-end interface) initiate query and analysis tasks on the audit database.

[0079] The specific statistical analysis operations mainly include the following three dimensions, all of which are based on the aggregation, grouping, and calculation of the various fields of "audit events" in the persistent table: SQL Type Distribution Statistics: The statistical analysis module scans all audit events within a specified time range (such as the last 24 hours or the entire lifecycle of a specific agent task). It extracts the "SQL Type" field from each event (this field is marked during event encapsulation, such as DML, DDL, DCL), and then groups and counts them by type. Finally, it generates a statistical result that visually displays the number of various SQL operations (such as SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, etc.) and their percentage of the total, thus reflecting the database access patterns and load composition.

[0080] High-frequency SQL statement statistics: This module normalizes the "SQL text" field (including outer SQL and inner DDL) in audit events (e.g., removing extra spaces and standardizing case), and then performs deduplication and frequency statistics on completely identical SQL texts. It sorts the SQL statements from highest to lowest execution count and selects the Top N (e.g., the top 10) most frequently executed SQL statements. This statistic can quickly identify hot operations and potential performance bottlenecks in the system.

[0081] SQL Execution Trend Statistics by Time Dimension: This module performs statistical analysis with time as the horizontal axis and SQL execution volume as the vertical axis. It first sets a time granularity (e.g., 1 hour), then groups and aggregates all events according to this time granularity based on the "execution time" field of the audit events, calculating the total number of SQL executions within each time period. Finally, it generates a time series trend chart or data list, clearly displaying the periodic changes, peak periods, and abnormal fluctuations in database access volume.

[0082] The results of all the above statistical analyses will be cached or directly pushed to the system's visualization platform, presented to the administrator in the form of charts (such as pie charts, bar charts, and trend lines) and reports. This allows administrators to go beyond monitoring individual SQL statements and grasp the overall operational status of the database from a macro perspective, identify potential risks and optimization opportunities, thus completing a full audit value loop from "data collection" to "information presentation" to "analysis and insight".

[0083] It should be noted that after completing the basic processing of audit events, this invention further introduces multi-dimensional statistical analysis functions based on persistent data. This essentially upgrades auditing from a passive recording and tracing system to an operation and security management tool with proactive insight and decision support capabilities. Simple audit event storage only solves the problem of verifiability; however, faced with massive historical operation records, it is difficult for humans to quickly identify patterns, discover anomalies, or assess impact. By automatically executing SQL type distribution statistics, managers can intuitively grasp the data operation composition of the system load (such as the proportion of queries, updates, and definition changes), thereby assessing system characteristics and potential risk centers. High-frequency SQL statement statistics can automatically filter out the most active and resource-intensive operations, providing direct data tools for database performance tuning and hotspot access analysis. Furthermore, SQL execution trend statistics by time dimension can clearly reveal the periodic patterns of database access, peak business periods, and abnormal fluctuations. These statistical analysis functions work together to transform the raw, discrete audit logs into highly condensed core indicators and visual reports that reflect the system's behavioral patterns and health. This allows operations and security personnel to quickly gain a systematic understanding of the overall database access status, efficiency bottlenecks, and potential risks without having to manually traverse the original records. This supports them in making forward-looking capacity planning, targeted performance optimization, and evidence-based security policy adjustments, greatly improving the efficiency and scientific nature of using audit data to drive operations and security decisions and security governance.

[0084] Furthermore, in the process of persistently storing the audit event in the audit database, the method flow steps of this embodiment are as follows: S701, name the archive files according to the rules of task ID, task name and date.

[0085] In the embodiments of this specification, when the file archiving module prepares to write a new archive file, it first needs to determine the unique identifier and storage path of the file. This step constitutes the rule for generating the filename. The module extracts three key elements from the audit event to be processed: the agent task ID to which the event belongs, the corresponding agent task name, and the date (usually accurate to the day) when the SQL statement was executed. Subsequently, the module combines these three variables according to the fixed naming template specified in the document to generate the final filename. A preferred naming format is capture_{task ID}_{task name}_{YYYYMMDD}.sql. Here, {task ID} and {task name} come from the task attributes maintained by the agent task management device, and {YYYYMMDD} is taken from the date portion of the audit event timestamp. This naming rule ensures that each archive file can be uniquely mapped to a specific agent task and a specific natural day, forming a clear file organization logic, which facilitates subsequent rapid retrieval and archiving management according to audit objectives and timelines.

[0086] S702, in the archive file, append execution time information in time annotation format to each SQL text.

[0087] In the embodiments described in this specification, after determining the filename and opening (or creating) the corresponding .sql format archive file, the module performs a content writing operation. For each SQL text to be written (from an audit event), the module does not directly write the original SQL. To retain key execution timing information in the offline file, the module first extracts the precise execution timestamp of the SQL from the audit event. Then, it generates a time comment line according to a preset, human-readable comment format. A preferred format example given in the document is --Execution time: 2026-03-15 10:00:01.123. This comment line will be written to the file as a separate line, followed by the corresponding complete SQL text on the next line. Each SQL record is written in the order of "time comment line + SQL text line". This design ensures that the final archive file is itself a complete database operation log script with precise timestamps. Anyone viewing the file can directly and clearly understand the specific time when each operation occurred, without relying on any external database or metadata. This greatly satisfies the stringent requirements of compliance audits for operation log files to be "self-contained, readable, and traceable".

[0088] It should be noted that the specific archiving strategy specified in this invention provides audit data with a standardized evidence carrier that is independent of structured databases and can be directly used for compliance review and offline analysis. By mandating the organization of archive files according to the three elements of agent task identifier, task name, and operation date, all captured SQL operations can be automatically classified and stored in independent files with clear business and time attributes. This greatly facilitates auditors to quickly locate and retrieve evidence as needed (e.g., for specific databases or specific time periods). Furthermore, it requires that each SQL text in the archive file be appended with an execution time annotation in a standard format, ensuring that each archive file itself is a complete and chronologically clear database operation log, containing all the key information from "when" to "which database" where "what operation" was performed. This design allows the archive files to be independently reviewed and understood without relying on any external systems or metadata. Therefore, the archived files generated by this method not only meet the mandatory requirements of data security regulations that operation logs must be long-term, complete, and tamper-proof, but also, due to their good readability and self-containment, become an authoritative electronic evidence that can be directly presented without secondary explanation in scenarios such as security incident investigation, compliance inspection, and fault backtracking, significantly improving the usability, credibility, and legal effect of audit results.

[0089] Furthermore, after encapsulating the outer SQL and the inner DDL into an audit event, the method flow steps of this embodiment are as follows: S801 writes the encapsulated audit event to the memory message queue.

[0090] In the embodiments described in this specification, the previous steps have already generated standardized audit events containing outer SQL, inner DDL, and context. In this step, the module responsible for event distribution first obtains a pre-initialized, thread-safe memory data structure, namely a "memory message queue" (specifically, BlockingQueue, as explicitly mentioned in the documentation). This module (as the producer) places the encapsulated audit event object, as a complete message unit, into the tail of this queue. This operation involves only memory references and is extremely fast. This memory message queue acts as a temporary buffer, its core function being to receive and temporarily store the audit events that surge from the real-time capture link, thereby decoupling the high-speed, unpredictable traffic forwarding threads that generate events from the potentially slower subsequent processing flow in time. The size of the queue can be configured according to the system memory to balance throughput and resource consumption.

[0091] S802, the audit event is asynchronously read from the memory message queue through a consumption thread independent of the traffic forwarding thread, and the corresponding processing is executed.

[0092] In the embodiments described in this specification, upon system startup, one or more independent "consumer threads" are created and run. These threads are logically and resource-wise completely separated from the "traffic forwarding threads" that directly handle database network connections. The core function of the consumer thread is to continuously monitor the memory message queue used in S801. When a new audit event is found in the queue, the consumer thread retrieves an event. Subsequently, without interfering with the traffic forwarding thread, the thread independently and asynchronously performs the "corresponding processing" for that event. This processing refers to driving multiple subsequent consumption channels, such as calling the persistence module to store the event in the database, triggering the archiving module to write to a file, and pushing to the front end via a WebSocket connection. Because consumption and production are completed asynchronously by different threads, even if subsequent database writes, file I / O, and other operations are occasionally delayed, they will not block or slow down the front-end database proxy forwarding performance, thus ensuring the smoothness of business access. At the same time, this design also improves the overall reliability and scalability of the system, allowing each processing module to evolve and be deployed independently.

[0093] It should be noted that the asynchronous message queue processing mechanism introduced in this invention after encapsulating audit events fundamentally resolves the core contradiction between the real-time nature of data acquisition and the reliability of backend processing. In traditional synchronous processing models, if the network proxy needs to immediately complete tasks involving I / O operations or network communication, such as persistent storage of audit events, file writing, or real-time push, while capturing and forwarding SQL traffic, it will inevitably block or prolong the processing time of the proxy forwarding thread. This directly increases the response latency of the business system accessing the database, and may even cause the proxy to freeze when the backend service fluctuates. This invention quickly writes the encapsulated audit events into an in-memory message queue, allowing the thread responsible for traffic forwarding to be released immediately and continue to efficiently process subsequent network requests, ensuring that the impact of the auditing behavior itself on business performance is minimized. At the same time, a specially created, independently running consumer thread asynchronously reads events from this queue and performs subsequent processing. This design achieves complete decoupling of the audit data acquisition link and the data processing link, with their working rhythms not interfering with each other. This not only ensures high performance and low latency for database proxy forwarding, but also allows relatively time-consuming operations such as file archiving and database persistence to be executed smoothly and reliably in the background. Even if a certain processing link is temporarily slowed down or briefly blocked, it will not be transmitted upstream to cause data loss or business interruption, thereby greatly enhancing the throughput, overall stability and business affinity of the entire audit system in high-concurrency scenarios.

[0094] This invention provides a method and system for SQL capture, dynamic DDL reconstruction, and real-time auditing for transparent database proxies. The system can be implemented as a management service, a transparent proxy engine, a protocol parsing module, a dynamic SQL reconstruction module, a message distribution module, an archive statistics module, and a visualization platform. In a preferred embodiment, the management service is implemented using a Spring Boot container, the transparent proxy engine is implemented using the Netty network communication framework, the management platform is implemented using a Vue frontend, and the target database is preferably a PostgreSQL database.

[0095] The system of this invention consists of a management service, a transparent proxy engine, a protocol parsing module, a dynamic DDL restoration module, a message distribution module, an archiving and statistics module, and a visualization platform. The preferred technology stack is as follows: Management service: Spring Boot (Java); Transparent proxy engine: Netty (high-performance network communication); Visualization platform: Vue3 + Element Plus + ECharts; Target database: PostgreSQL (with MySQL / Oracle extended design); Encryption method: AES symmetric encryption (data source password storage).

[0096] The business system replaces the original JDBC connection address with the proxy engine's listening address (database name / username / password remain unchanged). After receiving the connection, the proxy engine completes protocol parsing, SQL extraction, and dynamic DDL restoration, and then forwards the request to the target database. The management service manages the data source / proxy task through the REST interface, pushes real-time SQL events to the front end through WebSocket, and simultaneously completes data persistence, file archiving, and statistical analysis to achieve full-link transparent auditing.

[0097] 1. Data source management device Core functionality: Maintain connection metadata for the audit database (name, type, host, port, database name, username, password, creation / update time), with passwords stored using AES encryption; Engineering details: Provides the ability to add / edit / delete / test connections, and prohibits modification of core parameters (such as host / port / database name) of data sources that are already bound to agent tasks to avoid task anomalies; Innovations: Supports multiple database types (PostgreSQL / MySQL / Oracle), with built-in connection timeout retry and health check mechanisms.

[0098] This module serves as the system entry point. To clarify the audit targets, the data source management device first maintains the connection metadata of the proxied database. This device is responsible for storing the target database's host address, port, database name, authentication information, and database type, and performing connectivity verification during creation or modification. The verified data source information does not directly participate in business traffic processing but serves as the basic input for subsequent proxy task creation, transforming static connection information into the fundamental conditions for runnable tasks, thereby providing the proxy task management device with executable target connection parameters.

[0099] 2. Agent Task Management Device Core functionality: Binds a data source to a unique proxy port to generate an independent proxy task. Task attributes include: task ID, name, data source ID, proxy port, target address, status (RUNNING / STOPPED / ERROR), and exception information. Project details: Port uniqueness verification: Checks for port occupancy when creating a task to avoid conflicts; Task lifecycle management: Supports start / stop / delete, and automatically detects data source reachability upon startup; Real-time status synchronization: When the agent engine malfunctions, the task status is automatically updated to ERROR and the reason is recorded.

[0100] After the data source is confirmed, the system does not immediately begin proxying. Instead, the proxy task management device instantiates a data source as an independently runnable proxy task. This device binds the target database connection parameters to a unique listening port, forming a proxy instance with an independent task identifier, running status, and abnormal status. The purpose of this is to further transform the data source, which originally only represented connection information, into a running entity that can be started, stopped, recovered, and isolated. After the proxy task is created, its listening port, target address, and status information are handed over to the transparent proxy device for actually handling business traffic.

[0101] 3. Transparent Agent Device (Core Innovation) This implementation uses Netty to achieve bidirectional connection forwarding, solving the "first packet loss" problem of traditional proxies. 1) When the business system establishes a client connection, the front-end connection processing module first pauses the automatic reading of client data.

[0102] 2) The transparent proxy device then proactively establishes a backend connection with the target database.

[0103] 3) If the backend connection has not yet been successfully established, the first batch of request packets sent by the business system in advance will be cached in the forwarding queue instead of being discarded directly.

[0104] 4) Once the backend connection is successfully established, enable automatic reading and forwarding of cached request packets in sequence.

[0105] This design avoids the loss of the first packet during the login, connection establishment, and authentication phases of the business system, thereby improving system applicability and stability. Request packets are intercepted in real time during forwarding and sent to the protocol parsing module for processing.

[0106] This module acts as a bridge for the entire system, ensuring it "cannot impact business operations while still capturing traffic." The transparent proxy device is deployed between the business system and the target database, responsible for listening to the port corresponding to the proxy task, receiving database connections initiated by the business system, and establishing a backend connection to the target database. In this process, the transparent proxy device is not a simple byte relay; rather, it takes over the link without altering the business outcome, performing bidirectional forwarding control on the client request stream and the database response stream. To avoid losing the initial requests sent by the business system during login, authentication, or initial connection establishment, the transparent proxy device preferably adopts a method of front-end access first, back-end connection establishment second, initial packet storage, and delayed release after successful connection establishment. Thus, the proxy device not only achieves transparent takeover of database traffic but also provides a stable, complete, and sequentially arriving request data stream for subsequent database protocol parsing devices.

[0107] 4. Database Protocol Parsing Device (Core Innovation) This device is used to perform semantic parsing on PostgreSQL protocol request packets. In a preferred embodiment, the parsing device is able to identify at least the following messages: Startup Message: Extracts connection metadata such as client version, database name, and username; Simple message query Q: Directly extract the SQL text from the message; Precompiled parsing message P: Establishes a mapping relationship between "statement name - SQL template"; Binding message B: Establish a mapping relationship between "portal name - statement name".

[0108] Execution message E: Find the statement name based on the portal name, then find the SQL template based on the statement name, and realize SQL extraction under the extended query protocol; Close message C: Clean up statement name or portal name mappings and free up memory.

[0109] Thus, this invention is compatible not only with ordinary SQL requests, but also with prepared statements, ORM frameworks, and common extended protocol calling methods under connection pools.

[0110] After the transparent proxy device receives the client request stream, the request data first enters the database protocol parsing device. Since database requests are transmitted over the network as a continuous byte stream and cannot be directly identified by SQL text boundaries, the protocol parsing device first performs frame-level segmentation of the byte stream according to the PostgreSQL protocol message format. Preferably, the device iterates through the received ByteBuf buffer frame by frame based on the message type identifier and length field, ensuring that even in cases of packet fragmentation, packet reassembly, or multiple protocol messages arriving consecutively, the boundaries of each complete request message can be accurately recovered.

[0111] After frame-level segmentation, the protocol parsing device performs semantic-level parsing based on different message types. For simple query messages Q, the SQL text can be directly extracted; for parsing messages P, the correspondence between precompiled statement names and SQL templates is recorded; for binding messages B, a mapping between portal names and precompiled statement names is established; for execution messages E, the corresponding SQL template is found by reverse lookup based on the portal name, thereby restoring the SQL corresponding to this execution under the extended query protocol; for closing messages C, statements or portal mappings are cleaned up, and session-level state is released. Through this mechanism, the system can not only recognize ordinary text SQL, but also recognize the actual SQL semantics executed in ORM, connection pool, and precompiled scenarios.

[0112] The output of the protocol parsing device is not immediately regarded as the final audit content. Instead, it first forms an outer SQL candidate text, which is then handed over to the dynamic DDL restoration device to further determine whether it contains hidden inner object definition statements (dynamic DDL, such as SELECT exec_ddl('CREATE TABLE demo_table(id bigint)').

[0113] 5. Dynamic DDL Restoration Device (Core Innovation) This device is an important innovative component of the present invention, used to further extract and restore the internal dynamically executed DDL from the outer SQL.

[0114] In a preferred embodiment, the dynamic DDL restoration device includes the following sub-modules: Comment stripping submodule: Used to strip single-line comments and block comments at the start of an SQL statement.

[0115] The literal extraction submodule is used to extract text content from single-quoted strings and PostgreSQL dollar quote strings.

[0116] The expression recognition submodule is used to identify the EXECUTE keyword in function or procedure bodies and extract its subsequent expression content.

[0117] The splicing and reconstruction submodule is used to approximate the reconstruction of dynamic expressions such as EXECUTE 'CREATE TABLE ' || quote_ident(tbl) ||' (id bigint)', splicing together determinable text fragments and using placeholders to represent the positions of variables that cannot be statically determined.

[0118] Recursive Checking Submodule: Used to recursively check nested literals in outer SQL, function body text, procedure body text, and trigger definition body, with a preferred recursion depth of 4 levels.

[0119] The DDL classification submodule is used to determine the type of the restored SQL text. When it starts with a prefix such as CREATE, ALTER, DROP, TRUNCATE, COMMENT, GRANT, REVOKE, REINDEX, SECURITY LABEL, etc., it is identified as DDL.

[0120] Therefore, the present invention can reconstruct the inner layer DDL in the following typical scenarios (examples): ① SELECT exec_ddl('CREATE TABLE demo_table(id bigint)') ② CALL run_sql($$ALTER TABLE t ADD COLUMN c text$$) ③ EXECUTE 'DROP INDEX idx_demo' in the function body In many scenarios, the outer SQL obtained by the protocol parsing device is not the actual object modification statement that needs to be audited. For example, when the business system executes CALL xxx(), SELECT exec_ddl(...), EXECUTE statements in a function body, or dynamically concatenated statements in a trigger, the proxy chain can only directly see the call entry point, not the final executed object definition statements such as CREATE, ALTER, and DROP. To solve this problem, the system sets up a dynamic DDL restoration device after protocol parsing to further perform lexical analysis and recursive extraction on the outer SQL text. Then, the internal processing is broken down: The device preferably performs the following processing steps in sequence: First, it removes leading comments and non-business-related whitespace characters to avoid comments affecting keyword location; second, it identifies and extracts single-quoted strings, dollar sign strings, and text blocks in function and procedure bodies; then, it checks for the existence of dynamic execution entry points such as EXECUTE, PERFORM, and procedure call wrapper functions; for the discovered dynamic expressions, it then concatenates and reconstructs the static text fragments within them, using placeholders for variable parts to restore SQL fragments that are as close as possible to the actual execution semantics; finally, it performs recursive checks and type determination on the extracted results. When statements starting with prefixes such as CREATE, ALTER, DROP, TRUNCATE, COMMENT, GRANT, and REVOKE are identified, they are considered inner DDL statements, and independent audit results are generated.

[0121] After being processed by this device, the system can simultaneously obtain the outer call statement and the inner actual DDL. The two types of results are then uniformly handed over to the SQL event dispatch device for standardized output.

[0122] 6. SQL event dispatcher Unified event model: An event includes task ID, task name, execution time, SQL type (DML / DDL / DCL), SQL text, source (outer / inner DDL), and client IP; Decoupled dispatch mechanism: A blocking queue (BlockingQueue) is used as the in-memory message queue. After the agent engine writes SQL events to the queue, they are dispatched by multi-threaded consumers to: Persistence module (written to the audit database); File archiving module (generates archive files by dimension); WebSocket push module (real-time push to the front end); Statistical analysis module (real-time calculation of dimensional indicators); Innovation: This design ensures that the agent collection link is decoupled from the platform management link, avoiding the blocking of database request forwarding by front-end access, file writing or statistical calculation.

[0123] To prevent real-time capture from being blocked by database writes, file I / O, or front-end pushes, the system, after obtaining the outer SQL and inner DDL, does not directly perform subsequent processing in the agent thread. Instead, it uniformly delegates the processing to the SQL event dispatcher. This dispatcher encapsulates the capture results into a unified event model. The event content preferably includes fields such as task identifier, data source identifier, session information, capture time, SQL type, original SQL, restored SQL, source type, and exception flag. After the event enters the memory queue or blocking queue, it is asynchronously read by the backend consumer and distributed to the persistence, archiving, real-time push, and statistical analysis modules in the same format. Thus, the front-end agent chain is only responsible for 'capture and delivery', while the back-end consumer chain is responsible for 'processing and display', structurally ensuring the isolation between database business requests and audit platform processing.

[0124] Among these, the most basic consumption direction is the long-term preservation and disk recording of audit results. Therefore, the first core destination after event distribution is persistence and document archiving devices.

[0125] 7. Persistence and document archiving mechanisms The persistent device writes captured events to an audit table, preferably including: datasource: Stores target database information; proxy_task: Stores proxy tasks; captured_sql: Stores the captured SQL content, type, time, and task identifier; system_config: Stores system configuration.

[0126] The file archiving device archives SQL files according to the "task + date" dimension, and the preferred naming convention is: capture_{taskId}_{taskName}_{YYYYMMDD}.sql. Add a timestamp before each SQL statement in the file, for example: -- Execution time: 2026-03-15 10:00:01.123; CREATE TABLE demo_table(id bigint primary key); This satisfies the needs for database behavior logging, offline archiving, audit tracing, and export inspection.

[0127] The persistence and file archiving mechanism transforms the standardized SQL audit results output by the event distribution mechanism into long-term traceable records. On one hand, the persistence sub-device writes captured events into a structured audit table, facilitating subsequent retrieval, filtering, correlation queries, and statistical analysis. On the other hand, the file archiving sub-device writes the SQL content and execution time into archived files in text format, categorized by task, date, or data source, forming an unstructured chain of evidence suitable for offline review, backup export, and compliance documentation. Because this mechanism outputs standardized historical data, its results not only support post-event traceability but also directly form the data foundation for real-time monitoring and statistical analysis.

[0128] 8. Real-time monitoring and statistical analysis device The real-time monitoring device preferably pushes SQL events to the front-end page via a WebSocket persistent connection. After selecting the running task, the front-end first retrieves the most recently captured SQL, and then receives incremental pushes, thus avoiding a blank monitoring page when there are no new SQLs when the page is opened.

[0129] The statistical analysis device performs the following operations on the persisted data: SQL type distribution statistics; high-frequency SQL TOP10 statistics; hourly execution trend statistics; and Excel export.

[0130] The real-time monitoring and statistical analysis device resides in the result presentation layer of the entire audit chain. On one hand, this device receives incremental SQL events from the event distribution device via WebSocket or other real-time push mechanisms, allowing users to view the SQL capture status of currently running tasks in near real-time on the page. On the other hand, it performs multi-dimensional statistical analysis on indicators such as SQL type distribution, high-frequency statements, time trends, and task activity by querying historical audit data in the persistent storage device. Because this device relies on both real-time streams and historical databases, it can reflect both the current state of the system and long-term behavioral characteristics, thus completing the final closed loop from 'capture' to 'display' to 'analysis'.

[0131] Figure 2 A schematic diagram of the structure of a database SQL auditing device provided for one or more embodiments of this specification includes: At least one processor and bus; and, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, the instructions being executed by the at least one processor to enable the at least one processor to: Configure the connection information for the target database to be audited; The connection information is bound to the listening port in order to create and start the corresponding proxy task; The proxy task receives front-end connections initiated and established by the business system and back-end connections actively established by the proxy task with the target database through the listening port corresponding to the proxy task. Bidirectional traffic forwarding is performed between the front-end connection and the back-end connection, and during the bidirectional traffic forwarding, the request data stream sent by the business system to the target database is intercepted; The request data stream is parsed using database protocols to reconstruct the SQL statement to be executed by the business system, which serves as the outer SQL. The outer SQL is parsed to identify and extract the dynamically executed inner DDL. The outer SQL and the inner DDL are encapsulated into audit events, and the audit events are processed accordingly.

[0132] This specification provides one or more embodiments of a non-volatile computer storage medium storing computer-executable instructions, which, when executed by a computer, can perform the following: Configure the connection information for the target database to be audited; The connection information is bound to the listening port in order to create and start the corresponding proxy task; The proxy task receives front-end connections initiated and established by the business system and back-end connections actively established by the proxy task with the target database through the listening port corresponding to the proxy task. Bidirectional traffic forwarding is performed between the front-end connection and the back-end connection, and during the bidirectional traffic forwarding, the request data stream sent by the business system to the target database is intercepted; The request data stream is parsed using database protocols to reconstruct the SQL statement to be executed by the business system, which serves as the outer SQL. The outer SQL is parsed to identify and extract the dynamically executed inner DDL. The outer SQL and the inner DDL are encapsulated into audit events, and the audit events are processed accordingly.

[0133] The various embodiments in this specification are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the embodiments of apparatus, devices, and non-volatile computer storage media are basically similar to the method embodiments, so the descriptions are relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0134] The various embodiments in this specification are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the apparatus embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0135] Those skilled in the art will recognize that the units and algorithm steps of the various examples described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, or a combination of computer software and electronic hardware. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of this application.

[0136] In the embodiments provided in this application, it should be understood that the disclosed apparatus / network devices and methods can be implemented in other ways. For example, the apparatus / network device embodiments described above are merely illustrative. For instance, the division of modules or units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some interfaces; the indirect coupling or communication connection between devices or units may be electrical, mechanical, or other forms.

[0137] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0138] Furthermore, the functional units in the various embodiments of this application can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The aforementioned units can be implemented in hardware or software.

[0139] If the integrated module / unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, all or part of the processes in the methods of the above embodiments can also be implemented by a computer program instructing related hardware. The computer program can be stored in a computer-readable storage medium, and when executed by a processor, it can implement the steps of the various method embodiments described above. The computer program includes computer program code, which can be in the form of source code, object code, executable files, or certain intermediate forms. The computer-readable medium can include: any entity or device capable of carrying the computer program code, recording media, USB flash drives, portable hard drives, magnetic disks, optical disks, computer memory, read-only memory (ROM), random access memory (RAM), electrical carrier signals, telecommunication signals, and software distribution media, etc. It should be noted that the content included in the computer-readable medium can be appropriately added or removed according to the requirements of legislation and patent practice in the jurisdiction. For example, in some jurisdictions, according to legislation and patent practice, computer-readable media do not include electrical carrier signals and telecommunication signals.

[0140] The above-described embodiments are only used to illustrate the technical solutions of this application, and are not intended to limit them. Although this application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of this application, and should all be included within the protection scope of this application.

Claims

1. A method for auditing SQL in a database, characterized in that, Includes the following steps: Configure the connection information for the target database to be audited; The connection information is bound to the listening port in order to create and start the corresponding proxy task; The proxy task receives front-end connections initiated and established by the business system and back-end connections actively established by the proxy task with the target database through the listening port corresponding to the proxy task. Bidirectional traffic forwarding is performed between the front-end connection and the back-end connection, and during the bidirectional traffic forwarding, the request data stream sent by the business system to the target database is intercepted; The request data stream is parsed using database protocols to reconstruct the SQL statement to be executed by the business system, which serves as the outer SQL. The outer SQL is parsed to identify and extract the dynamically executed inner DDL. The outer SQL and the inner DDL are encapsulated into audit events, and the audit events are processed accordingly.

2. The method according to claim 1, characterized in that, The method further includes: Before the proxy task actively establishes a backend connection with the target database, it caches the first batch of request packets sent in advance by the business system into a queue to be forwarded. After the proxy task actively establishes a backend connection with the target database, it forwards the first batch of request packets to the target database.

3. The method according to claim 1, characterized in that, The process of parsing the requested data stream using database protocols includes: The request data stream is split at the database protocol frame level, and semantic parsing is performed based on the message type; The semantic parsing includes: parsing the startup message to extract connection metadata, parsing the preset query message to directly extract SQL text, and parsing the extended query protocol message. The extended query protocol message includes: parsing the pre-compiled parsing message to establish a mapping relationship between statement names and SQL templates, parsing the binding message to establish a mapping relationship between portal names and statement names, and parsing the execution message to restore the actually executed SQL text as the outer SQL based on the mapping relationship between portal names and statement names and the mapping relationship between statement names and SQL templates.

4. The method according to claim 1, characterized in that, The process of parsing the outer SQL, identifying and extracting the dynamically executed inner DDL includes: The outer SQL is processed to remove single-line comments and block comments at the beginning of the outer SQL. Extract the specified text content from the preset string in the processed outer SQL; Identify whether the specified text content contains execution expression keywords used for dynamically executing SQL; If the keyword of the execution expression is identified, the text fragments of the expression content following the specified text content are spliced ​​and reconstructed to restore the inner SQL text; The inner SQL text is recursively checked to determine whether it begins with a predefined keyword. If so, the inner SQL text is identified as the inner DDL.

5. The method according to claim 1, characterized in that, The corresponding processing of the audit event includes at least one of the following processing methods: The audit events are persistently stored in the audit database; The SQL text and execution time information of the audit events are written into the archive file based on both the agent task and the date. The audit events are pushed to the monitoring interface in real time for display via a WebSocket long connection.

6. The method according to claim 5, characterized in that, After processing the audit event accordingly, the process also includes: Based on the persisted audit events, statistical analysis is performed, including at least one of the following: distribution statistics of SQL types, statistics of high-frequency SQL statements, and statistics of SQL execution trends by time dimension.

7. The method according to claim 5, characterized in that, The step of persistently storing the audit events in the audit database includes: Name the archive files according to the rules of task ID, task name, and date; In the archived file, execution time information in time annotation format is appended to each SQL text.

8. The method according to claim 1, characterized in that, After encapsulating the outer SQL and the inner DDL into an audit event, the method further includes: Write the encapsulated audit event to the in-memory message queue; The audit events are read asynchronously from the memory message queue by a consumer thread independent of the traffic forwarding thread, and the corresponding processing is performed.

9. A database SQL auditing device, characterized in that, include: At least one processor and bus; as well as, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions executable by the at least one processor, which, when executed by the at least one processor, enable the at least one processor to: Configure the connection information for the target database to be audited; The connection information is bound to the listening port in order to create and start the corresponding proxy task; The proxy task receives front-end connections initiated and established by the business system and back-end connections actively established by the proxy task with the target database through the listening port corresponding to the proxy task. Bidirectional traffic forwarding is performed between the front-end connection and the back-end connection, and during the bidirectional traffic forwarding, the request data stream sent by the business system to the target database is intercepted; The request data stream is parsed using database protocols to reconstruct the SQL statement to be executed by the business system, which serves as the outer SQL. The outer SQL is parsed to identify and extract the dynamically executed inner DDL. The outer SQL and the inner DDL are encapsulated into audit events, and the audit events are processed accordingly.

10. A non-volatile computer storage medium, characterized in that, It stores computer-executable instructions, which, when executed by a computer, can achieve the following: Configure the connection information for the target database to be audited; The connection information is bound to the listening port in order to create and start the corresponding proxy task; The proxy task receives front-end connections initiated and established by the business system and back-end connections actively established by the proxy task with the target database through the listening port corresponding to the proxy task. Bidirectional traffic forwarding is performed between the front-end connection and the back-end connection, and during the bidirectional traffic forwarding, the request data stream sent by the business system to the target database is intercepted; The request data stream is parsed using database protocols to reconstruct the SQL statement to be executed by the business system, which serves as the outer SQL. The outer SQL is parsed to identify and extract the dynamically executed inner DDL. The outer SQL and the inner DDL are encapsulated into audit events, and the audit events are processed accordingly.

Images