A SQL code auditing method and system
By replacing manual analysis with automated processing and algorithmic auditing, the problems of low efficiency and accuracy in SQL auditing have been solved, and the system stability and data security have been improved throughout the entire process.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- CHINACCS INFORMATION IND
- Filing Date
- 2026-01-28
- Publication Date
- 2026-05-29
AI Technical Summary
Existing SQL auditing relies on manual analysis, which is inefficient and costly. It cannot intervene in a timely manner during the code writing and execution stages, and it is difficult to comprehensively audit complex SQL, resulting in lag and subjective bias.
By integrating and preprocessing SQL statements, breaking down nested structures, defining a list of audit algorithms, and automating audits based on these algorithms, structured audit results are generated and pushed to the responsible personnel, replacing manual experience-based judgment.
It improved audit efficiency, reduced costs, achieved system stability and data security throughout the entire process, and enhanced audit accuracy and traceability.
Smart Images
Figure CN122111817A_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of database code quality evaluation technology, and in particular to an SQL code auditing method and system. Background Technology
[0002] SQL is a database query and programming language widely used in information systems such as telecommunications, where data security and system stability are critical. In these systems, the quality of SQL code directly impacts database performance. Performance defects or security vulnerabilities in the code can easily lead to database failures and data leaks; therefore, a comprehensive audit of SQL statements is essential.
[0003] In existing technologies, SQL auditing relies on professional analysts who manually determine whether the results of SQL statement execution comply with audit rules. This approach has significant drawbacks: First, it is inefficient and costly. Manual analysis of complex SQL statements is time-consuming and cannot meet the needs of large-scale, high-frequency SQL audits, resulting in high labor costs. Second, existing manual auditing analysis can only detect problems after SQL statement execution, failing to intervene in a timely manner "before (code writing stage) or during (execution process)," leading to a lag in auditing. By the time faults or data breaches are exposed, substantial impacts have already occurred. Finally, the accuracy of manual judgment depends on personnel experience, making it prone to omissions and misjudgments due to subjective bias. Furthermore, it is difficult to conduct comprehensive and detailed audits of complex SQL statements containing multi-level nested subqueries. How to solve the above-mentioned technical problems is the challenge facing this invention. Summary of the Invention
[0004] To address the shortcomings of existing technologies, this invention provides an SQL code auditing method and system that effectively improves auditing efficiency, reduces costs, and solves the problem that existing manual auditing analysis can only detect SQL statements after execution. This method and system ensures the stability and data security of application systems from all stages and improves auditing quality.
[0005] The technical solution adopted by this invention to solve its technical problem is as follows: This invention provides an SQL code auditing method, comprising the following steps: The SQL statements are input and preprocessed to obtain a list of SQL statements to be audited. The SQL statements to be audited in the SQL list to be audited are split and nested to obtain a split SQL list; Define a list of audit algorithms, and audit the split SQL list based on the list of audit algorithms to obtain the audit results; Send the audit results to the person in charge; The data fields of the SQL list to be audited include at least the SQL statement to be audited, the first SQL code, and the estimated number of SQL executions; the data fields of the split SQL list include at least the split SQL statement, the second SQL code, and the estimated number of SQL executions; the data fields of the audit algorithm list include the algorithm code, algorithm type, action, evaluation score, operation type, algorithm name, and algorithm rules; the data fields of the audit result include at least the application system code, the first SQL code, the SQL statement to be audited, the data impact, the audit score, the action, and the audit time.
[0006] Preferably, the step of accessing and preprocessing SQL statements includes building a database, periodically or in real-time accessing SQL statements via an interface, and deduplicating the SQL statements to obtain the SQL statements to be audited. Define the audit batch, execution order, and estimated number of SQL executions for the SQL statements to be audited, and generate the first SQL code based on the audit batch and execution order; The SQL statements to be audited, the first SQL code, and the estimated number of SQL executions are summarized to obtain the list of SQL statements to be audited.
[0007] Preferably, the access SQL statement further includes recording SQL statement source identification information, which includes application system code, application system name, application scenario and access time. The application scenario includes system upgrade, operation optimization, operation operation and code audit.
[0008] Preferably, the process of splitting and nesting the SQL statements to be audited in the list of SQL statements to be audited includes storing the SQL statements to be audited in a root data unit and assigning a root ID to the SQL statements to be audited; identifying and extracting syntax keywords from the SQL statements to be audited, and performing automatic line wrapping according to the syntax keywords to obtain the SQL statements after line wrapping. Identify and extract the content within the parentheses of the SQL statement after a newline, determine whether the content within the parentheses contains a SELECT statement, and if it does, mark the content within the parentheses of the SELECT statement as a separable substatement and store it in the next lower-level data unit. Assign a sub-ID to the separable substatement, record the nesting level of the separable substatement relative to the root data unit, and generate a content replacement tag based on the sub-ID. Replace the content within the parentheses of the SELECT statement with content replacement tags; if the SELECT statement is not included, do not split the content within the parentheses of the SQL statement after the newline. The loop splits and nests the separable sub-statements in the lower-level data unit, and sets a nesting level threshold. If there are still separable sub-statements after reaching the nesting level threshold, the loop is broken and the split SQL statement is obtained. Define the audit batch, execution order, splitting order, and estimated number of SQL executions for the split SQL statements, and generate a second SQL code based on the audit batch, execution order, and splitting order; The split SQL statements, the second SQL code, and the estimated number of SQL executions are summarized to obtain the split SQL list.
[0009] Preferably, the algorithm types include writing standardization, syntax checking, optimization checking, auditing standardization, and business customization; the processing actions include prompts, suggestions, and prohibitions; and the operation types include adding, deleting, modifying, querying, and all types.
[0010] Preferably, the auditing of the split SQL statements based on the audit algorithm list yields audit results including... Based on the audit algorithm list, the syntax keywords in the split SQL statements are matched with the operation types in the audit algorithm list to obtain the algorithm code and processing action of the algorithm to be executed. The algorithm is executed on the split SQL statements according to the algorithm code to obtain the data impact of a single execution and the audit time, and the data impact is calculated by combining the estimated number of SQL executions in the split SQL list; The scores of the split SQL statements are determined by the evaluation scores in the list of audit algorithms. The scores of all algorithms to be executed for the split SQL statements are then calculated to obtain the audit score of the SQL statements to be audited. The audit results are obtained by summarizing the application system code, the SQL statement to be audited, the data impact, the audit score of the SQL statement to be audited, the handling actions, and the audit time according to the first SQL code.
[0011] This invention also provides an SQL code auditing system, comprising: The SQL access processing module is used to access SQL statements and preprocess them to obtain a list of SQL statements to be audited. The nested splitting module is used to split and nest the SQL statements to be audited in the SQL list to obtain a split SQL list; The SQL audit module is used to audit the split SQL list based on the audit algorithm list to obtain audit results; The results push module is used to push audit results to the person in charge.
[0012] The present invention also provides a computer device, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to implement the steps of the method described above.
[0013] The present invention also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the method described above.
[0014] The beneficial effects of this invention are: effectively improving audit efficiency and reducing costs; solving the problem that existing manual audit analysis can only detect errors after SQL statement execution; ensuring the stability and data security of application systems at all stages; and improving audit quality. By accessing SQL statements through an interface and performing deduplication, unnecessary computation is reduced, improving audit efficiency. Nested SQL statements are split, and a list of audit algorithms is defined. Keywords are used to match the algorithms to be executed with the split SQL statements, replacing manual experience-based judgment, avoiding subjective bias, and effectively improving the accuracy of SQL auditing. Attached Figure Description
[0015] Figure 1 This is a diagram illustrating the method steps of the present invention.
[0016] Figure 2 This is a system module diagram of the present invention. Detailed Implementation
[0017] To clearly illustrate the technical features of this solution, the following detailed implementation method will be used to explain the solution.
[0018] Example 1: See Figure 1 As shown, this embodiment is a SQL code auditing method, including the following steps: S1. Input the SQL statements and preprocess them to obtain a list of SQL statements to be audited; The data fields of the SQL audit list should at least include the SQL statement to be audited, the first SQL code, and the estimated number of SQL executions. The process of accessing and preprocessing SQL statements includes setting up a database, periodically or in real-time accessing SQL statements via an interface, and deduplicating the SQL statements to obtain the SQL statements to be audited. The SQL statements accessed in this embodiment are shown in Table 1 below:
[0019] Table 1 List of Access SQL Define the audit batch, execution order, and estimated number of SQL executions for the SQL statements to be audited, and generate the first SQL code based on the audit batch and execution order; The SQL statements to be audited, the first SQL code, and the estimated number of SQL executions are summarized to obtain the list of SQL statements to be audited.
[0020] The list of SQL queries to be audited in this embodiment is shown in Table 2 below:
[0021] Table 2 List of SQL queries to be audited Accessing SQL statements also includes recording the source identification information of the SQL statements. The source identification information of the SQL statements includes the application system code, application system name, application scenario and access time. Application scenarios include system upgrades, operation optimization, operation operations and code auditing.
[0022] It should be noted that by standardizing interface access and generating structured identifiers, the process of managing SQL statements from access to list is automated, replacing the traditional manual collection and organization of SQL statements. This lays the foundation for the traceability and efficiency of subsequent audits. SQL statements are deduplicated using hash algorithms, and if an SQL statement with the same hash value has been accessed within one hour, it is considered a duplicate and removed, avoiding duplicate audits. The first SQL code is generated in a fixed format of "audit batch_execution order," recording the source identifier, application system code, and application scenario. The first SQL code and the source identifier form a dual guarantee of "unique SQL identity + source traceability." If subsequent audits discover problems (such as a SQL statement posing a data leakage risk), the access batch can be quickly located through the code, and the application system code can be used to trace back to the relevant business system, facilitating responsibility identification and problem tracing, and solving the pain point of "ambiguous and untraceable SQL source" in traditional manual audits.
[0023] S2. Split and nest the SQL statements to be audited in the SQL list to obtain the split SQL list; The data fields of the split SQL list should include at least the split SQL statement, the second SQL code, and the estimated number of SQL executions. The process of splitting and nesting the SQL statements to be audited in the SQL list includes storing the SQL statements to be audited into root data units and assigning root IDs to the SQL statements to be audited; identifying and extracting syntax keywords from the SQL statements to be audited, and performing automatic line wrapping according to the syntax keywords to obtain the SQL statements with line breaks; the syntax keywords include SELECT, FROM, LEFT JOIN, INNER JOIN, FULL JOIN, WHERE, AND, GROUP BY, HAVING, and ORDER BY; Identify and extract the content within the parentheses of the SQL statement after a newline, determine whether the content within the parentheses contains a SELECT statement, and if it does, mark the content within the parentheses of the SELECT statement as a separable substatement and store it in the next lower-level data unit. Assign a sub-ID to the separable substatement, record the nesting level of the separable substatement relative to the root data unit, and generate a content replacement tag based on the sub-ID. Replace the content within the parentheses of the SELECT statement with content replacement tags; if the SELECT statement is not included, do not split the content within the parentheses of the SQL statement after the newline. The loop splits and nests the separable sub-statements in the lower-level data unit, and sets a nesting level threshold. If there are still separable sub-statements after reaching the nesting level threshold, the loop is broken and the split SQL statement is obtained. Define the audit batch, execution order, splitting order, and estimated number of SQL executions for the split SQL statements, and generate a second SQL code based on the audit batch, execution order, and splitting order; The split SQL statements, the second SQL code, and the estimated number of SQL executions are summarized to obtain the split SQL list.
[0024] The split SQL list of this embodiment is shown in Table 3 below:
[0025] Table 3. List of SQL statements after splitting It should be noted that, through standardized structured decomposition logic, complex SQL queries containing multiple levels of nested subqueries are broken down into independent, auditable units. This solves the problem of missed or incorrect audits that are easily encountered when manually decomposing complex SQL queries. Simultaneously, tagging and association ensure the correspondence between the main SQL and sub-SQL queries, achieving "seamless decomposition." A syntax parser is used to identify keywords such as "SELECT," "FROM," and "LEFT JOIN," automatically inserting newline characters before these keywords for accurate extraction of content within parentheses. Content replacement tags are generated based on sub-IDs, allowing direct association with the corresponding sub-SQL queries for data traceability. A nesting level threshold is set; if nesting still exists after reaching the threshold, a report indicates that the nesting level is too deep, and SQL optimization is recommended.
[0026] S3. Define a list of audit algorithms, and audit the split SQL list based on the list of audit algorithms to obtain the audit results; The data fields for the audit algorithm list include algorithm code, algorithm type, action, evaluation score, operation type, algorithm name, and algorithm rules; the data fields for the audit results include at least the application system code, first SQL code, SQL statement to be audited, data impact, audit score, action, and audit time.
[0027] Algorithm types include writing style guidelines, syntax checking, optimization checking, auditing standards, and business customization; action actions include prompts, suggestions, and prohibitions; operation types include adding, deleting, modifying, querying, and all types.
[0028] The list of audit algorithms defined in this embodiment is shown in Table 4 below:
[0029] Table 4 List of Audit Algorithms Based on the audit algorithm list, the syntax keywords in the split SQL statements are matched with the operation types in the audit algorithm list to obtain the algorithm code and processing action of the algorithm to be executed. The matching results of the algorithm in this embodiment are shown in Table 5 below:
[0030] Table 5 Algorithm Matching Results The algorithm is executed on the split SQL statements according to the algorithm code to obtain the data impact of a single execution and the audit time, and the data impact is calculated by combining the estimated number of SQL executions in the split SQL list; The scores of the split SQL statements are determined by the evaluation scores in the list of audit algorithms. The scores of all algorithms to be executed for the split SQL statements are then calculated to obtain the audit score of the SQL statements to be audited. The audit results are obtained by summarizing the application system code, the SQL statement to be audited, the data impact, the audit score of the SQL statement to be audited, the handling actions, and the audit time according to the first SQL code.
[0031] The audit results of this embodiment are shown in Table 6 below:
[0032] Table 6 Audit Results It should be noted that by using a structured audit algorithm checklist and quantitative audit logic, the traditional manual "experience-based judgment" audit model is replaced, achieving standardized and quantifiable audit results. Simultaneously, the SQL risk level is intuitively reflected through data impact. The scoring range in the audit algorithm checklist is 0-10, determined by factors such as algorithm type, operation type, and business impact, ensuring that the correlation between the score and SQL risk is interpretable and configurable. For existing SQL queries, the "number of rows affected per execution" (e.g., Rows_affected=500) for the past 7 days is extracted from the database slow query log, and the average value is taken. For new SQL queries, the target table metadata is read, and the number of rows affected per execution is estimated based on the WHERE condition filtering ratio, obtaining the data impact of a single execution. This data impact is then calculated based on the estimated number of SQL executions, quantifying the potential risk of the SQL to business data (e.g., the impact of 3000 rows / day is far higher than 100 rows / day), facilitating the responsible person to handle according to the "high-risk priority" principle.
[0033] In this embodiment, a higher audit score indicates a greater number of non-compliant algorithms matched by the split SQL statement, a higher level of non-compliant risk, and a lower SQL quality. The data impact is used to determine "which SQL statement with the same score has a higher risk and needs to be prioritized"—the greater the data impact, the higher the actual quality risk of the SQL statement even if the audit scores are the same, and the risk level needs to be increased; the smaller the data impact, the lower the risk, and the level can be appropriately decreased.
[0034] S4. Push the audit results to the person in charge.
[0035] By using a "structured results list + targeted delivery" approach, audits are ensured to be implemented effectively, making it easier for responsible persons to quickly identify key points and solving the problems of "low efficiency and easy omissions" in the traditional manual delivery of audit results.
[0036] Example 2: See Figure 2 As shown, this embodiment is an SQL code auditing system, including... The SQL access processing module is used to access SQL statements and preprocess them to obtain a list of SQL statements to be audited. The nested splitting module is used to split and nest the SQL statements to be audited in the SQL list to obtain a split SQL list; The SQL audit module is used to audit the split SQL list based on the audit algorithm list to obtain audit results; The results push module is used to push audit results to the person in charge.
[0037] Example 3: This is the third embodiment of the present invention, which differs from the first two embodiments in that: If the aforementioned functions are implemented as software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this invention, or the part that contributes to the prior art or the current technical solution, can be embodied in the form of a software product. This current computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of this invention. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.
[0038] The logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be embodied in any computer-readable medium for use by, or in conjunction with, an instruction execution system, apparatus, or device (such as a computer-based system, a processor-including system, or other system that can fetch and execute instructions from, an instruction execution system, apparatus, or device). For the purposes of this specification, "computer-readable medium" can be any means that can contain, store, communicate, propagate, or transmit programs for use by, or in conjunction with, an instruction execution system, apparatus, or device.
[0039] More specific examples of computer-readable media (a non-exhaustive list) include: electrical connections (electronic devices) having one or more wires, portable computer disk drives (magnetic devices), random access memory (RAM), read-only memory (ROM), erasable and editable read-only memory (EPROM or flash memory), fiber optic devices, and portable optical disc read-only memory (CDROM). Furthermore, computer-readable media can even be paper or other suitable media on which the program can be printed, because the program can be obtained electronically, for example, by optically scanning the paper or other medium, followed by editing, interpreting, or otherwise processing as necessary, and then stored in computer memory.
[0040] The technical features of this invention not described can be implemented by or using existing technology, and will not be repeated here. Of course, the above description is not a limitation of this invention, and this invention is not limited to the examples above. Any changes, modifications, additions or substitutions made by those skilled in the art within the scope of this invention should also be within the protection scope of this invention.
Claims
1. A method for auditing SQL code, characterized in that, Includes the following steps, The SQL statements are input and preprocessed to obtain a list of SQL statements to be audited. The SQL statements to be audited in the SQL list to be audited are split and nested to obtain a split SQL list; Define a list of audit algorithms, and audit the split SQL list based on the list of audit algorithms to obtain the audit results; Send the audit results to the person in charge; The data fields of the SQL list to be audited include at least the SQL statement to be audited, the first SQL code, and the estimated number of SQL executions; the data fields of the split SQL list include at least the split SQL statement, the second SQL code, and the estimated number of SQL executions; the data fields of the audit algorithm list include the algorithm code, algorithm type, action, evaluation score, operation type, algorithm name, and algorithm rules; the data fields of the audit result include at least the application system code, the first SQL code, the SQL statement to be audited, the data impact, the audit score, the action, and the audit time.
2. The SQL code auditing method according to claim 1, characterized in that, The process of accessing and preprocessing SQL statements includes building a database, periodically or in real-time accessing SQL statements via an interface, and deduplicating the SQL statements to obtain the SQL statements to be audited. Define the audit batch, execution order, and estimated number of SQL executions for the SQL statements to be audited, and generate the first SQL code based on the audit batch and execution order; The SQL statements to be audited, the first SQL code, and the estimated number of SQL executions are summarized to obtain the list of SQL statements to be audited.
3. The SQL code auditing method according to claim 2, characterized in that, The access SQL statement also includes recording the source identification information of the SQL statement. The source identification information of the SQL statement includes the application system code, application system name, application scenario and access time. The application scenario includes system upgrade, operation optimization, operation operation and code audit.
4. The SQL code auditing method according to claim 3, characterized in that, The process of splitting and nesting the SQL statements to be audited in the list of SQL statements to be audited includes storing the SQL statements to be audited into a root data unit and assigning a root ID to the SQL statements to be audited; identifying and extracting syntax keywords from the SQL statements to be audited; performing automatic line wrapping according to the syntax keywords to obtain the SQL statements after line wrapping. Identify and extract the content within the parentheses of the SQL statement after a newline, determine whether the content within the parentheses contains a SELECT statement, and if it does, mark the content within the parentheses of the SELECT statement as a separable substatement and store it in the next lower-level data unit. Assign a sub-ID to the separable substatement, record the nesting level of the separable substatement relative to the root data unit, and generate a content replacement tag based on the sub-ID. Replace the content within the parentheses of the SELECT statement with content replacement tags; if the SELECT statement is not included, do not split the content within the parentheses of the SQL statement after the newline. The loop splits and nests the separable sub-statements in the lower-level data unit, and sets a nesting level threshold. If there are still separable sub-statements after reaching the nesting level threshold, the loop is broken and the split SQL statement is obtained. Define the audit batch, execution order, splitting order, and estimated number of SQL executions for the split SQL statements, and generate a second SQL code based on the audit batch, execution order, and splitting order; The split SQL statements, the second SQL code, and the estimated number of SQL executions are summarized to obtain the split SQL list.
5. The SQL code auditing method according to claim 4, characterized in that, The algorithm types include writing standardization, syntax checking, optimization checking, auditing standardization, and business customization; the processing actions include prompts, suggestions, and prohibitions; the operation types include adding, deleting, modifying, querying, and all types.
6. The SQL code auditing method according to claim 5, characterized in that, The audit results obtained by auditing the split SQL statements based on the audit algorithm list include: Based on the audit algorithm list, the syntax keywords in the split SQL statements are matched with the operation types in the audit algorithm list to obtain the algorithm code and processing action of the algorithm to be executed. The algorithm is executed on the split SQL statements according to the algorithm code to obtain the data impact of a single execution and the audit time, and the data impact is calculated by combining the estimated number of SQL executions in the split SQL list; The scores of the split SQL statements are determined by the evaluation scores in the audit algorithm list. The scores of all algorithms to be executed for the split SQL statements are then calculated to obtain the audit score of the SQL statements to be audited. The audit results are obtained by summarizing the application system code, the SQL statement to be audited, the data impact, the audit score of the SQL statement to be audited, the handling actions, and the audit time according to the first SQL code.
7. An SQL code auditing system, characterized in that, For performing the SQL code auditing method according to any one of claims 1 to 6, comprising: The SQL access processing module is used to access SQL statements and preprocess them to obtain a list of SQL statements to be audited. The nested splitting module is used to split and nest the SQL statements to be audited in the SQL list to obtain a split SQL list; The SQL audit module is used to audit the split SQL list based on the audit algorithm list to obtain audit results; The results push module is used to push audit results to the person in charge.
8. A computer device comprising a memory and a processor, wherein the memory stores a computer program, characterized in that, When the processor executes the computer program, it implements the steps of the SQL code auditing method according to any one of claims 1 to 6.
9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the SQL code auditing method according to any one of claims 1 to 6.