Remote sql configuration and secure invocation method and system based on template engine

Abstract

This invention discloses a method and system for remote SQL configuration and secure invocation based on a template engine, belonging to the field of database security access technology. It includes a terminal module, an access gateway module, a template management module, a remote configuration module, a security control module, a template engine module, an SQL execution module, an anomaly monitoring module, and a data storage module. The template management module and the remote configuration module enable unified creation, review, publication, and three-level synchronization of SQL templates across the group headquarters server, regional subsidiary servers, and mining area servers. The anomaly monitoring module is used to monitor abnormal behavior in real time, triggering alarms and recording anomaly details when monitored indicators exceed thresholds. This invention solves the problems of lack of unified management of SQL templates, single-layer security protection, and difficulty in multi-database collaboration in existing technologies.

Description

Technical Field

[0001] This invention relates to the field of database secure access technology, specifically to a method and system for remote SQL configuration and secure invocation based on a template engine. Background Technology

[0002] As enterprises deepen their digital transformation, the demand for remote SQL calls in multi-business system and multi-database environments is growing. In large group enterprises, business systems at various levels frequently need to make remote SQL calls across regions and databases to achieve data aggregation, analysis, and collaboration. However, existing remote SQL call technologies still have significant shortcomings in terms of template management, security protection, and multi-database adaptation.

[0003] Patent CN202211711889.8 discloses a data processing method based on SQL templates. After obtaining the SQL template, the server generates the target SQL statement according to the terminal's query requirements and sends it to the target database for execution. This method achieves dynamic SQL generation through predefined templates, but it does not involve a multi-level review and release mechanism for templates or remote configuration management. Each business system still needs to maintain its own templates, lacking unified control capabilities.

[0004] In addition to the aforementioned patents, existing solutions in terms of security protection mostly employ single permission verification or syntax verification mechanisms, lacking a comprehensive protection system that combines parameterized queries, syntax analysis, injection feature matching, and dynamic desensitization of sensitive data. This makes it difficult to effectively address the increasingly complex risks of SQL injection attacks and data breaches. Regarding permission management, they typically employ simple role-based access control, failing to implement role hierarchy inheritance and separation of responsibilities mechanisms that match multi-level organizational structures, thus failing to meet the hierarchical management needs of large groups. In terms of database adaptation, they are mostly designed for single-type databases, lacking unified adaptation capabilities for relational databases and time-series databases, making it difficult to meet the data collaboration needs in multi-database environments. In terms of operation and maintenance management, they lack a full-process log recording and traceability mechanism for template configuration, remote calls, and security verification, making it difficult to quickly locate the root cause of problems when data query errors or security anomalies occur.

[0005] Therefore, the present invention aims to provide a method and system for remote SQL configuration and secure invocation based on a template engine, in order to solve the defects existing in the prior art. Summary of the Invention

[0006] The purpose of this invention is to provide a method and system for remote SQL configuration and secure invocation based on a template engine, in order to solve the problems in the prior art such as lack of unified management and remote configuration of SQL templates, poor adaptability of security control mechanisms, difficulty in multi-database collaboration, weak traceability, and insufficient scalability.

[0007] To solve the above-mentioned technical problems, the present invention provides the following technical solution, specifically including: A template engine-based remote SQL configuration and secure invocation system, comprising: It includes a terminal module, an access gateway module, a template management module, a remote configuration module, a security control module, a template engine module, an SQL execution module, an exception monitoring module, and a data storage module.

[0008] The terminal module is used to receive SQL call requests initiated by users or business systems. The call request carries template identifier, parameter information and user identity information. The request is sent to the access gateway module through a secure transmission protocol, and the encrypted execution result is received.

[0009] The access gateway module is used to implement unified access, load balancing, transmission encryption and preliminary format verification of requests, and distribute requests to the security management module.

[0010] The template management module is deployed on the group headquarters server and regional subsidiary servers. It uses template engine technology to design template file formats, reserves parameter placeholders in the templates, and supports template version management and review processes. The group-level module is responsible for the unified creation, review and release of SQL templates, while the regional-level module is responsible for receiving templates issued by the group and adapting them to the business needs of the region.

[0011] The remote configuration module is deployed on the group headquarters server, regional subsidiary servers, and mining area servers to achieve template synchronization between the three levels of servers. Long connection technology is used to ensure template consistency. After synchronization is completed, a verification is performed, and an alarm is triggered and automatic retry is performed when synchronization fails.

[0012] The security management module is deployed on the group headquarters server, regional subsidiary servers, and mining area servers. These three levels of servers work together to implement a four-fold security protection mechanism, including hierarchical access control, deep SQL injection protection, special protection for sensitive data, and full-process log auditing. The hierarchical access control is based on a role-based access control model, integrating role hierarchy inheritance and separation of responsibilities, with permissions subdivided into template operation permissions and SQL call permissions. The deep SQL injection protection adopts a triple protection mechanism of parameterized query, syntax verification, and injection feature matching. The special protection for sensitive data dynamically de-identifies the execution results based on a sensitive data dictionary.

[0013] The template engine module, deployed on the mining server, is used to dynamically generate SQL statements by combining remotely configured template files with user-input parameters, and integrates a database adaptation plugin to automatically adjust the SQL syntax format according to the database type.

[0014] The SQL execution module, deployed on the mining server, is responsible for sending dynamically generated SQL statements to the target database for execution. It establishes connections with various types of databases using database connection protocols and obtains the execution results.

[0015] The anomaly monitoring module is deployed on the group headquarters server, regional subsidiary servers, and mining area servers. These three levels of servers work together to monitor abnormal behavior in real time. When the monitored indicators exceed the threshold, an alarm is triggered and the anomaly details are recorded.

[0016] The data storage module is divided into three levels of storage, which respectively store template information, security policies, log data, and business data, and uses encryption algorithms to protect sensitive data.

[0017] The security management module employs a five-table permission system, including a user table, a role table, a permission table, a user-role mapping table, and a role-permission mapping table. Users are divided into multiple levels according to the organizational structure, roles are divided according to departments and responsibilities, and permissions are further subdivided into template operation permissions and SQL call permissions. Each user only obtains the minimum permissions required to complete their own job duties.

[0018] The deep SQL injection protection in the security management module adopts a triple protection mechanism: the first layer of protection is parameterized query, which separates the parameter placeholders in the template from the parameters input by the user; the second layer of protection is syntax verification, which uses a syntax analyzer to perform syntax analysis on dynamically generated SQL statements; and the third layer of protection is injection feature matching, which uses regular expression matching and recognition models to identify SQL injection features.

[0019] The security management module records the template configuration and full-process information of SQL calls in the full-process log audit. The log content includes user identity information, operation time, operation content, template identifier, SQL statement, execution result and exception information, and supports retrieval and tracing by multiple conditions.

[0020] The access gateway module uses reverse proxy technology to achieve unified access and a weighted round-robin algorithm to achieve load balancing; it enables a secure transmission protocol and configures a transport layer security certificate to encrypt the transmission of request and response data.

[0021] The template engine module integrates a database syntax adaptation plugin, which supports SQL syntax adaptation for relational databases and time-series databases. It automatically adjusts the SQL syntax format according to the database type associated with the template, thereby achieving unified template configuration across database types.

[0022] A method for remote SQL configuration and secure invocation based on a template engine includes the following steps: S1: The group administrator creates an SQL template through the template management module, fills in the basic template information, writes the template content, and uses template syntax to reserve parameter placeholders in the template. After creation, the template is submitted for review. S2: The auditor receives the SQL template review request, determines whether the SQL template meets the requirements, returns modification suggestions if the review fails, and sets the template status to approved and publishes it to the servers of the regional subsidiaries if the review passes. S3: The regional administrator receives the approved SQL template issued by the group, makes adaptive modifications according to the business needs of the region, and submits it for regional review after the modifications are completed; S4: After the regional reviewer approves the template, the template status will be set to published and synchronized to the servers of each mining area in this region; S5: The user initiates an SQL call request through the terminal, inputs the template identifier and parameter values, and carries the user's identity information. The request is sent to the access gateway module through a secure transmission protocol. S6: The access gateway module performs a preliminary validation of the request format. If the format passes the validation, the request is distributed to the security control module. S7: The security management module performs permission verification to determine whether the user has permission to call the published SQL template and execute the corresponding SQL operation. If the permission is insufficient, a prompt will be returned and an unauthorized call log will be recorded. S8: The security control module validates the parameter values ​​input by the user. After successful validation, the parameters are passed to the template engine module. S9: The template engine module calls the locally cached template file, fills the parameter values ​​into the template placeholders, automatically adjusts the SQL syntax format according to the database type, and dynamically generates SQL statements; S10: The security control module performs syntax verification and injection feature matching on the generated SQL statement. After the verification is successful, the SQL statement is passed to the SQL execution module. S11: The SQL execution module establishes a connection with the target database based on the database identifier associated with the template, executes SQL statements, and obtains the execution results; S12: The security control module performs sensitive data desensitization processing on the execution results, and encrypts the execution results using an encryption algorithm before returning them to the calling terminal; S13: The terminal receives the encrypted execution result, decrypts it, and displays it to the user or transmits it to the business system.

[0023] The specific process of the security management module performing security verification on the dynamically generated SQL statement in step S10 includes: S10.1: Use a parser to perform lexical and syntactic analysis on SQL statements, construct an abstract syntax tree, and determine whether the SQL statements conform to the syntax specifications of the target database; S10.2: An injection feature matching mechanism is adopted to calculate the injection risk score. When the injection risk score exceeds the preset threshold, it is determined to be an injection attack, the request is intercepted and an alarm is triggered. S10.3: If both syntax validation and injection feature matching pass, the SQL statement is marked as safe and allowed to execute.

[0024] The calculation process for the injected risk score in step S10.2 is as follows: First, extract the feature word sequence from the SQL statement, and construct a feature word vector by taking the operation keywords, concatenation operators and system functions that appear in the SQL statement as feature words. Then, calculate the injection risk score R. inj The injection risk score is calculated as follows: the first weighting coefficient multiplied by the number of sensitive keyword types, plus the second weighting coefficient multiplied by the number of times the join query keyword appears, plus the third weighting coefficient multiplied by the number of times the comment symbol appears, plus the fourth weighting coefficient multiplied by the number of times the dangerous system function is called, summed up, and then divided by the total number of characters in the SQL statement. Here, the number of sensitive keyword types refers to the count of database structure operation keywords appearing in the SQL statement; the number of join query keyword occurrences refers to the total number of times the join query keyword appears in the SQL statement; the number of comment symbol occurrences refers to the total number of times the SQL comment symbol appears in the SQL statement; and the number of dangerous system function calls refers to the number of times function calls capable of performing system-level operations appear in the SQL statement.

[0025] Compared with the prior art, the beneficial effects achieved by the present invention are: By introducing template engine technology to build an SQL template system and designing a three-level remote configuration mechanism, the unified creation, review, publication and reuse of SQL templates are realized. Users only need to select a template and fill in the parameters to initiate SQL calls. Non-technical personnel can also operate it conveniently, which greatly improves the efficiency of SQL calls and reduces the human error rate.

[0026] A four-tiered security protection mechanism has been constructed, which aligns with the hierarchical management architecture and includes tiered access control, deep SQL injection protection, special protection for sensitive data, and full-process log auditing. Among these, techniques such as parameterized queries, syntax analysis for injection detection, and dynamic de-identification of sensitive data can effectively prevent SQL injection attacks and sensitive data leakage risks, meet industry data security compliance requirements, and solve the problem of the single-layer security protection of existing technologies.

[0027] Through the database syntax adaptation plugin integrated into the template engine module, cross-type collaboration between relational databases and time-series databases is achieved, automatically adjusting the SQL syntax format and solving the data collaboration challenges in multi-database environments. Meanwhile, all hardware and software are adapted to the domestic environment, aligning with localization and transformation plans.

[0028] A full-process logging and tracing mechanism for SQL template modification, remote calls, and security verification was designed, enabling real-time monitoring, alarming, and location of abnormal behavior, thereby improving the efficiency of operation and maintenance management.

[0029] It adopts a layered architecture design, supports rapid adaptation to new business systems and new database types, and the template system can be flexibly expanded without large-scale modifications to the core code, reducing system upgrade and maintenance costs and meeting the long-term needs of enterprise digital transformation. Attached Figure Description

[0030] The accompanying drawings are provided to further illustrate the invention and form part of the specification. They are used in conjunction with embodiments of the invention to explain the invention and do not constitute a limitation thereof. In the drawings: Figure 1 This is a schematic diagram of the module connection of the remote SQL configuration and secure call system based on the template engine of the present invention; Figure 2 This is a flowchart of the steps of the remote SQL configuration and secure invocation method based on the template engine of the present invention. Detailed Implementation

[0031] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0032] Please see Figures 1-2 The present invention provides the following technical solution: like Figure 1 As shown, this embodiment provides a remote SQL configuration and secure invocation system based on a template engine, including an invocation terminal module, an access gateway module, a template management module, a remote configuration module, a security control module, a template engine module, an SQL execution module, an anomaly monitoring module, and a data storage module. The modules communicate with each other via a secure transmission protocol.

[0033] The terminal module is deployed on servers in each mining area to receive SQL call requests initiated by users or business systems. These requests carry template identifiers, parameter information, and user identity information, and are sent to the access gateway module via a secure transmission protocol. The module then receives the encrypted execution result. Users can initiate the call by selecting a template and filling in the parameters.

[0034] The access gateway module is deployed on the servers of subsidiaries in various regions, serving as the unified entry point for the system. It enables unified request access, load balancing, transmission encryption, and preliminary format verification. After successful verification, the request is distributed to the security control module. Load balancing uses a weighted round-robin algorithm.

[0035] The template management module is deployed on the group headquarters server and regional subsidiary servers. It uses template engine technology to design template file formats, with reserved parameter placeholders within the templates. The group headquarters server module is responsible for the unified creation, review, and publication of templates, while the regional subsidiary server modules are responsible for receiving templates from the group headquarters server and adapting them to the business needs of their respective regions. Template management supports version control and review processes.

[0036] The remote configuration module is deployed on the group headquarters server, regional subsidiary servers, and mining area servers to achieve template synchronization across the three levels of servers. Long-lived connection technology ensures template consistency, and alarms and automatic retries are triggered in case of synchronization failure.

[0037] The security management module is deployed across the group headquarters server, regional subsidiary servers, and mining area servers, working collaboratively. It is the core security module of this system, implementing a four-tiered security protection mechanism: hierarchical access control, using a role-based access control model that integrates role hierarchy inheritance and separation of responsibilities, with permissions subdivided into template operations and SQL calls; deep SQL injection protection, employing a triple mechanism of parameterized queries, syntax validation, and injection feature matching; dedicated protection for sensitive data, dynamically anonymizing execution results based on a sensitive data dictionary; and full-process log auditing, recording the entire process information of template configuration and SQL calls.

[0038] The template engine module is deployed on the mining server. Its core function is to combine remotely configured template files with user-input parameters and dynamically generate SQL statements using the FreeMarker template engine. This module integrates a database adaptation plugin, which can automatically adjust the SQL syntax format according to the database type associated with the template.

[0039] The SQL execution module is deployed on the mining server and is responsible for sending SQL statements that have passed security verification to the target database for execution. It uses database connection protocols to establish connections with various types of databases, obtains the execution results, and returns them to the security control module.

[0040] The anomaly monitoring module is deployed on the group headquarters server, regional subsidiary servers, and mining area servers to monitor abnormal behavior at all levels of the system in real time. When the monitored indicators exceed the preset threshold, an alarm is triggered and the anomaly details are recorded.

[0041] The data storage module adopts a three-tier storage architecture: the group headquarters server stores global templates, security policies, and log data; regional subsidiary servers store template adaptation information and log data for their respective regions; and mining area servers store template cache, local logs, and business data. All levels of storage employ encryption algorithms to protect sensitive data.

[0042] like Figure 2 As shown, this embodiment provides a method for remote SQL configuration and secure invocation based on a template engine, including a remote SQL template configuration process and a remote SQL secure invocation process.

[0043] The scenario is set as Wang Wu, a business employee of the "Shanxi Region - Jincheng Mining Area" under the group. He needs to query the coal production summary data for January 2026 for his mining area, which is numbered "JC-2024-001". The template is identified as "TPL-COAL-001".

[0044] SQL template remote configuration process; Step S1: The group administrator creates a template and submits it for review. The group headquarters template administrator logs into the template management module, creates an SQL template named "Monthly Summary Query Template for Coal Production", fills in the basic template information, writes the template content using template syntax, and reserves parameter placeholders "${mine area number}", "${query start time}", and "${query end time}". After creation, it is submitted to the group reviewer.

[0045] Step S2: The group auditor reviews and publishes the template to the regions. The group auditor receives the review request and checks whether the template syntax, parameter settings, and scope of application meet the requirements. After the review is approved, the template status is set to "Reviewed," and the review time is 10:30:25 on January 15, 2026. The remote configuration module pushes the template to each region-level configuration node via a long connection.

[0046] Step S3: The regional administrator adapts the template and submits it for review. After receiving the template from the group, the Shanxi regional administrator customizes it according to the actual business needs of the mining area in their region. For example, the table name is adapted to the actual table name used in the mining area, such as "JC". 煤炭产量表 Add the "Working Face Number" field. After modification, change the template version number from 1.0 to 1.1-Regional Adaptation Version, and submit for regional review.

[0047] Step S4: The regional auditor reviews and publishes the template to the mining area. After the regional auditor confirms that the modifications are reasonable and do not change the core business logic, the template status is set to "Published". The remote configuration module pushes the template to the execution node in the Jincheng mining area, and the template engine module caches the template file locally, with the synchronization time being 09:30:00 on January 16, 2026.

[0048] Remote SQL secure call process; Step S5: The user initiates an SQL call request. At 8:30 AM on February 1, 2026, business personnel Wang Wu (username: wangwu, user number: U-JC-0018, role: mine operator) logs into the terminal, selects "Monthly Coal Production Summary Query Template," and enters the mine number "JC-2024-001," the query start time "2026-01-01," and the query end time "2026-01-31." The terminal constructs a request data packet with the request identifier REQ-20260201-083000-0001, carrying the template identifier TPL-COAL-001, parameter information, and user identity token, and sends it to the access gateway module via a secure transmission protocol.

[0049] Step S6: Access Gateway Format Verification and Distribution. The access gateway module verifies the integrity of the request format, the validity of the token, and the timestamp to prevent replay. After successful verification, the access log is recorded, and the request is distributed according to a weighted round-robin algorithm. Let the set of service nodes of the security management module be S = {S1, S2, ..., Sn}, and the weight of each node be W. i The current number of distributed requests is C. i The current configuration and status of the three nodes in the system are as follows: Node S1 weight W1=3, current request count C1=45; Node S2 weight W2=2, current request count C2=28; Node S3 weight W3=1, current request count C3=16.

[0050] Calculate the load ratio C of each node. i / W i : S1 load ratio = 45 / 3 = 15; S2 load ratio = 28 / 2 = 14; S3 load ratio = 16 / 1 = 16.

[0051] Select the node S2 with the lowest load ratio as the target node, distribute the request to node S2, and update its current request count C2 to 29.

[0052] Step S7: Security Management Module Permission Verification. The security management module parses the user's identity and queries the user-role mapping table to obtain the role associated with user "wangwu" as "Mining Operator - Production Department". It then queries the role-permission mapping table to obtain the permission list for this role, including permission codes P-TPL-003 "Template Invocation" and P-SQL-001 "Data Query". Upon verification, the user has the permission to invoke template TPL-COAL-001 and query data; the permission verification passes.

[0053] Step S8: Parameter Verification of the Security Control Module. Verify each user-input parameter: Verify that the "Mining Area Number" JC-2024-001 is a valid number for the Jincheng Mining Area and matches the user's mining area; verify that the "Query Start Time" 2026-01-01 conforms to the "YYYY-MM-DD" standard; verify that the "Query End Time" 2026-01-31 meets the requirements in both format and range; scan all parameter values ​​and no commonly used SQL injection special characters such as single quotes, double quotes, semicolons, double hyphens, or slashes / asterisks are found. Parameter verification passed.

[0054] Step S9: The template engine module dynamically generates SQL. The template engine module queries the local cache based on the template identifier TPL-COAL-001; the cache version is 1.1-region-adapted. After confirming version consistency with the remote configuration module, it calls the FreeMarker engine to fill the parameter values ​​into the template placeholders, replacing "${mine area number}" with "JC-2024-001", "${query start time}" with "2026-01-01", and "${query end time}" with "2026-01-31". The database syntax adaptation plugin confirms the target database is the Kingbase database, uses standard SQL syntax, and generates a complete SQL statement with a total character count of L. sql It contains 256 characters.

[0055] Step S10: The security control module performs dual security checks on the generated SQL statement.

[0056] Sub-step S10.1, Syntax Validation. The ANTLR parser is used to perform lexical and syntactic analysis on the SQL statement. Lexical Analysis Phase: The lexer reads the character stream of the SQL statement and decomposes it into a sequence of lexical units according to predefined lexical rules. Lexical rules define the matching patterns for keywords (such as SELECT, FROM, WHERE, GROUP, BY, ORDER, BETWEEN, AND, AS, SUM), identifiers, string literals, operators, and delimiters in the SQL language. Based on these patterns, the lexer labels each lexical unit with its type and lexical value.

[0057] During the parsing phase, the parser reads the sequence of lexical units and constructs an abstract syntax tree according to predefined SQL syntax rules. The syntax rules are described using extended Backus normal form, defining the structure of a SELECT statement as a SELECT clause followed by a FROM clause, which can be followed by optional WHERE, GROUP BY, and ORDER BY clauses. The parser expands each clause node layer by layer, matching each lexical unit to its corresponding position in the syntax tree, thus constructing a complete abstract syntax tree.

[0058] If the lexical analyzer successfully decomposes all characters into valid lexical units and the syntax analyzer successfully constructs a complete syntax tree, the SQL statement is passed to the next verification stage; if either step fails, the SQL statement is intercepted and a syntax error message is returned.

[0059] Sub-step S10.2: Injection Feature Matching. This involves extracting features and calculating risk scores for the SQL statements.

[0060] First, extract the feature word sequence from the SQL statement and detect the following feature indicators: The number of sensitive keyword types N_keyword refers to the count of the types of keywords DROP, ALTER, TRUNCATE, EXEC, and SHUTDOWN that appear in the SQL statement. Each keyword is counted only once, and repeated occurrences are not accumulated.

[0061] Number of times N combined query keywords union This refers to the total number of times the UNION keyword appears in an SQL statement.

[0062] Number of annotation symbols N comment This refers to the total number of times SQL comment symbols appear in an SQL statement, including double hyphens "--" and forward slashes and asterisks " / *".

[0063] Dangerous function number N func This refers to the total number of function calls in an SQL statement that can perform system-level operations, including LOAD. FILE INTO OUTFILE XP cmdshell wait.

[0064] Total length of SQL statement L sql This refers to the total number of characters in the SQL statement, including keywords, identifiers, spaces, punctuation marks, and literals. In this example, L... sql The value can be 256 characters.

[0065] Then, calculate the injection risk score R. inj R inj The calculation formula is defined as follows:

[0066] The first weighting coefficient, w1, represents the weight of the number of sensitive keyword types. Sensitive keywords, such as DROP, ALTER, and TRUNCATE, can directly damage the database structure or delete data, posing the highest level of danger; therefore, w1 is set to its maximum value of 0.35.

[0067] The second weighting factor, w2, represents the weight of the number of times the query keyword is used in the UNION query. UNION queries are a common method used in SQL injection attacks to steal extra data and are considered highly dangerous; therefore, w2 is set to 0.30.

[0068] The third weighting factor, w3, is the weight of the number of comment symbols. Comment symbols are often used to truncate the original SQL statement to append malicious code; w3 is set to 0.20.

[0069] The fourth weighting coefficient, w4, represents the weight of the number of dangerous functions. Dangerous system functions can execute operating system commands on the database server; they are among the highest-risk operations but have a relatively low probability of occurrence, so w4 is set to 0.15.

[0070] The above weighting coefficients satisfy the constraint: w1 + w2 + w3 + w4 = 1.

[0071] Substitute the values ​​of each parameter in this example into the formula: R inj = (0.35 × 0 + 0.30 × 0 + 0.20 × 0 + 0.15 × 0) / 256 = 0.000 Sub-step S10.3: Comprehensive Judgment. The calculated injection risk score R... inj = 0.000 and the preset injection risk threshold R threshold = 0.04 for comparison. If R = 0.04 inj ≤ R threshold The SQL statement was determined to be safe and posed no risk of injection attacks; if R inj > R threshold The system determines that the SQL statement poses a risk of injection attack, intercepts the request, and triggers a security alert. Because in this example, R... inj ≤ R threshold The SQL statement is deemed safe and poses no risk of injection attacks. Both syntax validation and injection feature matching pass, so the SQL statement is marked as safe and passed to the SQL execution module.

[0072] Suppose a malicious user attempts to launch an SQL injection attack by accessing the terminal and inputs the malicious string "JC-2024-001' OR '1'='1' --" into the "Mining Area Number" parameter. First, the parameter validation phase will detect that the string contains dangerous characters such as single quotes and hyphens, and will directly block it. Even if the malicious user bypasses parameter validation, the parameterized query mechanism of the template engine module will treat the entire malicious string as a string value, and the generated SQL statement will not be injected or polluted. However, in extreme zero-day attack scenarios, suppose a SQL statement also contains the DROP keyword (N... keyword =1), UNION keyword (N) union =1) and SQL comment symbols (N comment If the SQL statement has a length of 300 characters and the value is 1, then its injection risk score is R. inj = (0.35 × 1 + 0.30 × 1 + 0.20 × 1 + 0.15 × 0) / 300 = 0.00283.

[0073] Although the score is still below the threshold of 0.04, if the maliciously constructed SQL statement is relatively short, such as L_sql being only 20 characters, then R... inj =0.85 / 20=0.0425 >0.04, at which point the system will determine that there is a risk of injection attack and block it. The design of the injection risk score calculation formula takes into account the length of the SQL statement: the higher the proportion of the attack payload in the total statement, the higher the risk score, and the easier it is to be identified.

[0074] Step S11: The SQL execution module executes the statement. Based on the database information associated with the template (database instance name KINGBASE_JC_01, IP address 192.168.20.50, port 54321), the SQL execution module obtains an idle connection from the connection pool. It executes the SQL statement through the JDBC interface. After the database completes SQL parsing, execution plan optimization, data retrieval, and aggregation calculations, it returns the result dataset. The result contains four rows of data, with fields for mine area number, longwall face number, production type, total monthly production, and the month of statistics.

[0075] Step S12: Security Control Module Desensitization and Encryption. Sensitive data is identified in the execution results. The sensitive data dictionary is queried: the field "Monthly Total Production" is classified as medium sensitive, with a desensitization strategy of hiding detailed values ​​from users outside the mine and users below level L3; the "Working Face Number" is classified as low sensitive, with no desensitization restrictions. The current user, wangwu, is an L3 level operator in this mine, so no desensitization is required for any fields. Subsequently, the result dataset is encrypted using the AES-256-GCM algorithm. The key number KEY-2026-02-001 is obtained from the key management service, a 12-byte random initialization vector is generated, the serialized JSON string is encrypted, and an authentication tag is generated.

[0076] Step S13: The result is returned to the calling terminal. The encrypted data packet is returned to the calling terminal via a secure transmission protocol. The terminal verifies the authentication tag to confirm data integrity before decrypting. The anomaly monitoring module monitors various indicators throughout the process: request failure rate, SQL execution error rate, number of injection attack attempts, and permission verification failure rate.

[0077] It should be noted that, in this document, relational terms such as "first" and "second" are used only to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such process, method, article, or apparatus.

[0078] Finally, it should be noted that the above descriptions are merely preferred embodiments of the present invention and are not intended to limit the present invention. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art can still modify the technical solutions described in the foregoing embodiments or make equivalent substitutions for some of the technical features. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A template engine based remote SQL configuration and secure invocation system, characterized in that, include: The system includes a terminal module, an access gateway module, a template management module, a remote configuration module, a security control module, a template engine module, an SQL execution module, an exception monitoring module, and a data storage module. The terminal module is used to receive SQL call requests carrying template identifier, parameter information and user identity information; The access gateway module is used to realize unified access, load balancing, transmission encryption and preliminary format verification of requests; the template management module has a group-level module responsible for the unified creation, review and release of templates, and a regional-level module responsible for receiving templates and adapting them to the business needs of the region. The remote configuration module enables template synchronization between the group level, regional level, and mining area level, and triggers alarms and automatic retry when synchronization fails. The security management module has four layers of security protection: hierarchical access control based on role access control, SQL injection protection combining parameterized query and injection detection, dynamic desensitization of sensitive data, and full-process log auditing. The template engine module is used to dynamically generate SQL statements by combining remotely configured template files with user-input parameters. The SQL execution module is responsible for sending SQL statements to the target database for execution and obtaining the results; The anomaly monitoring module is used to monitor abnormal behavior in real time. When the monitored indicators exceed the threshold, an alarm is triggered and the anomaly details are recorded. The data storage module is divided into three levels of storage, which store template information, security policies, log data and business data respectively.

2. The remote SQL configuration and secure invocation system based on a template engine according to claim 1, characterized in that, The security management module employs a five-table permission system, including a user table, a role table, a permission table, a user-role mapping table, and a role-permission mapping table. Users are divided into several levels according to the organizational structure, roles are divided according to departments and responsibilities, and permissions are further subdivided into template operation permissions and SQL call permissions. Each user only obtains the minimum permissions required to complete their own job duties.

3. The remote SQL configuration and secure invocation system based on a template engine according to claim 1, characterized in that, The deep SQL injection protection in the security management module adopts a triple protection mechanism: the first layer of protection is parameterized query, which separates the parameter placeholders in the template from the parameters input by the user; the second layer of protection is syntax verification, which uses a syntax analyzer to perform syntax analysis on dynamically generated SQL statements; and the third layer of protection is injection feature matching, which uses regular expression matching and recognition models to identify SQL injection features.

4. The remote SQL configuration and secure invocation system based on a template engine according to claim 1, characterized in that, The security management module records the entire process of the log audit template configuration and SQL call information. The log content includes user identity information, operation time, operation content, template identifier, SQL statement, execution result and exception information.

5. A remote SQL configuration and secure invocation system based on a template engine according to claim 1, characterized in that, The access gateway module uses reverse proxy technology to achieve unified access, and uses a weighted round-robin algorithm to achieve load balancing; it enables secure transmission protocols and configures transport layer security certificates.

6. A remote SQL configuration and secure invocation system based on a template engine according to claim 1, characterized in that, The template engine module integrates a database syntax adaptation plugin, which supports SQL syntax adaptation for relational databases and time-series databases, and automatically adjusts the SQL syntax format according to the database type associated with the template.

7. A method for remote SQL configuration and secure invocation based on a template engine, characterized in that, Includes the following steps: S1: The group administrator creates an SQL template through the template management module. The template uses template syntax to reserve parameter placeholders. After creation, it is submitted for review. S2: The auditor receives the SQL template review request. If the review is successful, the template status is set to approved and it is published to the servers of the regional subsidiaries. S3: The regional administrator receives the approved SQL template, makes adaptive modifications according to the business needs of the region, and submits it for regional review after the modifications are completed; S4: After the regional reviewer approves the template, the template status will be set to published and synchronized to the servers of each mining area in this region; S5: The user initiates an SQL call request through the terminal, inputs the template identifier and parameter values, carries the user's identity information, and sends it to the access gateway module through a secure transmission protocol; S6: The access gateway module performs a preliminary validation of the request format. If the format passes the validation, the request is distributed to the security control module. S7: The security management module performs permission verification to determine whether the user has permission to call the published SQL template and execute the corresponding SQL operation; S8: The security control module validates the parameter values ​​input by the user. After successful validation, the parameters are passed to the template engine module. S9: The template engine module calls the locally cached template file, fills the parameter values ​​into the template placeholders, automatically adjusts the SQL syntax format according to the database type, and dynamically generates SQL statements; S10: The security control module performs syntax verification and injection feature matching on the generated SQL statement. After the verification is successful, the SQL statement is passed to the SQL execution module. S11: The SQL execution module establishes a connection with the target database, executes SQL statements, and obtains the execution results; S12: The security control module performs sensitive data desensitization processing on the execution results, and encrypts the execution results using an encryption algorithm before returning them to the calling terminal; S13: The terminal receives the encrypted execution result, decrypts it, and displays it to the user or transmits it to the business system.

8. The method for remote SQL configuration and secure invocation based on a template engine according to claim 7, characterized in that, The specific process of the security management module performing security verification on the dynamically generated SQL statement in step S10 includes: S10.1: Use a parser to perform lexical and syntactic analysis on SQL statements, construct an abstract syntax tree, and determine whether the SQL statements conform to the syntax specifications of the target database; S10.2: An injection feature matching mechanism is adopted to calculate the injection risk score. When the injection risk score exceeds the preset threshold, it is determined to be an injection attack, the request is intercepted and an alarm is triggered. S10.3: If both syntax validation and injection feature matching pass, the SQL statement is marked as safe and allowed to execute.

9. A method for remote SQL configuration and secure invocation based on a template engine according to claim 8, characterized in that, The calculation process for the injected risk score in step S10.2 is as follows: Extracting feature word sequences from SQL statements; Calculate the injection risk score R inj The calculation formula is: , where N keyword N represents the number of sensitive keyword categories. union To determine the number of times the keywords are combined in the query, N comment N represents the number of times the annotation symbol is used. func L represents the number of times a dangerous system function is called. sql R represents the total number of characters in the SQL statement. w1, w2, w3, and w4 are preset weighting coefficients that satisfy the condition that the sum of the four coefficients equals 1. inj If a preset threshold is reached, an injection attack risk is detected, and the request is blocked.

10. A method for remote SQL configuration and secure invocation based on a template engine according to claim 7, characterized in that, The load balancing of the access gateway module in the step S6 adopts a weighted round robin algorithm, specifically, assuming that each service node set is S={S1, S2,..., Sn}, the weight of each node is W i , and the current request number is C i ; each time a new request arrives, the load ratio C i / W i of each node is calculated, and the request is distributed to the node with the smallest load ratio.

Images