An AST-based dynamic rewriting method for complex SQL queries and a server

Abstract

The application discloses an AST-based complex SQL query dynamic rewriting method and server, relates to the field of data rewriting, and comprises the following steps: in response to receiving a target database type, parsing an original SQL query statement into an AST; initializing an SQL processor; recursively traversing a selection statement structure of a main query, and recursively traversing a nested subquery structure in the selection statement structure; recursively traversing each selection statement structure in a joint query; and recursively traversing a condition expression or a nested selection statement structure in an update statement, a delete statement and an insert statement; converting the rewritten AST into a dynamically rewritten SQL query statement; and returning the dynamically rewritten SQL query statement, and rewriting SQL expressions and SQL statement structures in the AST according to preset dynamic modification logic. The application can effectively solve the technical problem that a traditional SQL enhancement or modification mode cannot process a complex nested structure.

Description

TECHNICAL FIELD

[0001] Embodiments of the present application relate to the field of data rewriting, and in particular, to a method for rewriting complex SQL queries based on AST and a server. BACKGROUND

[0002] In today's data-driven era, SQL, as the standard language for relational databases, carries the data access and processing tasks of most business systems. With the increasing complexity of business logic, SQL queries have become more and more sophisticated and complex. In order to achieve efficient data retrieval, complex business logic judgment, data aggregation, and cross-table operations, developers and database administrators often write SQL statements containing multiple levels of nested subqueries (Sub-Select), UNION or UNION ALL joint queries, and various complex expressions.

[0003] Nested subqueries allow embedding another complete SELECT query in the WHERE, FROM, SELECT, or HAVING clause of a query. This structure greatly enhances the expressive power of SQL, for example, it can be used to find a data set that meets certain conditions, or reference the results of a subquery in the main query. Joint queries (UNION or UNION ALL) are used to combine the results of two or more SELECT statements into a result set, which is very useful when similar structured data needs to be obtained from different tables or different conditions. These complex query structures are an indispensable part of modern enterprise-level applications, and they enable developers to handle highly complex data relationships and business rules in a declarative manner.

[0004] However, in many business scenarios, such as data isolation in multi-tenant systems, role-based data permission control, data desensitization, audit log recording, or dynamic query optimization for specific business rules, it is often necessary to dynamically enhance or modify the SQL query without modifying the original business code. This modification usually involves adding additional filtering conditions (such as WHERE clauses), adjusting query projections (SELECT lists), or even rewriting part of the expressions. These dynamic modification requirements require the system to understand the structure of the SQL query and accurately insert or modify SQL elements at the appropriate location.

[0005] Traditional methods of SQL enhancement or modification can usually only handle top-level queries and cannot apply filtering conditions (such as WHERE clauses) to all subqueries and joint queries, resulting in incomplete data filtering or logical errors.

[0006] In the face of the above technical problems, there is currently no effective solution. SUMMARY

[0007] The embodiment of the application provides a complex SQL query dynamic rewriting method and server based on AST, which is used for effectively solving the technical problem that a traditional SQL enhancement or modification method cannot process a complex nested structure, and effectively applying a filter condition to each part of all sub-queries and joint queries, so that data filtering is complete and logical.

[0008] To achieve the above object, the embodiment of the application adopts the following technical scheme:

[0009] In a first aspect, a complex SQL query dynamic rewriting method based on AST is provided, which is applied to a server, the server is used for executing a SQL query, the SQL query includes at least one nested sub-query or joint query, and the method includes the following steps:

[0010] In response to receiving an original SQL query statement and a target database type of the SQL query statement, parsing the original SQL query statement into an AST;

[0011] Initializing a SQL processor, the SQL processor includes a selection statement accessor and an expression accessor, the selection statement accessor is used for traversing a selection statement structure in the AST, and the expression accessor is used for traversing an expression structure in the AST;

[0012] Traversing the AST by using the SQL processor, and rewriting SQL expression and SQL statement structures in the AST according to a preset dynamic modification logic;

[0013] The traversing the AST by using the SQL processor includes the following steps:

[0014] Recursively traversing a selection statement structure of a main query, and recursively traversing a sub-query structure nested in the selection statement structure;

[0015] Recursively traversing each selection statement structure in a joint query; and

[0016] Recursively traversing a condition expression or a nested selection statement structure in an update statement, a delete statement and an insert statement;

[0017] Converting the rewritten AST into a dynamically rewritten SQL query statement; and

[0018] Returning the dynamically rewritten SQL query statement.

[0019] In a possible implementation manner of the first aspect, the rewriting the SQL expression and the SQL statement structure in the AST according to the preset dynamic modification logic includes the following steps:

[0020] in response to at least one operand of a comparison operation expression in the AST containing the preset skip marker, rewriting the comparison operation expression into a preset Boolean constant expression;

[0021] in response to a right side list of an IN expression in the AST containing the preset skip marker, rewriting the IN expression into a preset tautology or contradiction expression.

[0022] In a further possible implementation form of the first aspect, rewriting the SQL expression and SQL statement structure in the AST according to the preset dynamic modification logic further comprises:

[0023] in response to a right side expression of a LIKE expression in the AST containing the preset skip marker, rewriting the right side expression of the LIKE expression into a preset wildcard expression; or

[0024] in response to the right side expression of the LIKE expression being in a parameterized wildcard form, rewriting the right side expression into a string concatenation form corresponding to the target database according to the target database type.

[0025] In a further possible implementation form of the first aspect, rewriting the SQL expression and SQL statement structure in the AST according to the preset dynamic modification logic comprises:

[0026] by the SQL processor, in response to identifying the SQL query statement to be rewritten according to a current user identity, role or data sensitivity level, performing at least one of:

[0027] dynamically injecting a row-level filter condition in a WHERE clause of the SQL query statement to restrict data rows accessible by the user;

[0028] dynamically applying a desensitization function to a sensitive column or removing the sensitive column from a SELECT list of the SQL query statement; and

[0029] dynamically injecting a tenant ID filter condition in the SQL query statement to implement multi-tenant data isolation.

[0030] In a further possible implementation form of the first aspect, recursively traversing the select statement structure of the main query and recursively traversing a subquery structure nested in the select statement structure comprises:

[0031] traversing a WHERE clause of the select statement structure by the expression accessor;

[0032] traversing a subquery nested in a SELECT field of the select statement structure by the expression accessor; and

[0033] traversing, by the expression accessor, a subquery nested in a FROM clause of the SELECT statement structure.

[0034] In a further possible implementation form of the first aspect, the recursively traversing each SELECT statement structure in the joint query comprises:

[0035] traversing each select body in the list of joint query, and recursively traversing the select body by the SELECT statement accessor.

[0036] In a further possible implementation form of the first aspect, the recursively traversing the select body by the SELECT statement accessor comprises:

[0037] determining whether the select body contains a WHERE clause;

[0038] in case that the select body contains a WHERE clause, traversing the WHERE clause by the expression accessor to rewrite SQL expressions in the WHERE clause;

[0039] traversing a list of select items of the select body, and recursively traversing, by the expression accessor, a nested subquery contained in the list of select items to rewrite SQL expressions and SQL statement structures in the nested subquery;

[0040] determining whether a FROM clause of the select body contains a nested subquery;

[0041] in case that the FROM clause contains a nested subquery, traversing the nested subquery by the expression accessor to rewrite SQL expressions and SQL statement structures in the nested subquery.

[0042] In a further possible implementation form of the first aspect, the recursively traversing conditional expressions or nested SELECT statement structures in UPDATE, DELETE and INSERT statements comprises:

[0043] traversing, by the expression accessor, a WHERE clause of the DELETE statement;

[0044] traversing, by the expression accessor, a WHERE clause of the UPDATE statement; and

[0045] traversing, by the SELECT statement accessor, a SELECT statement structure nested in the INSERT statement.

[0046] In a further possible implementation form of the first aspect, the SQL handler is an RtSQLHandler, the select statement visitor is a SelectVisitor, and the expression visitor is an ExpressionVisitor.

[0047] In a second aspect, the present application provides a server, comprising:

[0048] a memory configured to store instructions; and

[0049] a processor configured to invoke the instructions from the memory and implement the above-mentioned AST-based complex SQL query dynamic rewriting method when executing the instructions.

[0050] By the above technical solution, the AST-based complex SQL query dynamic rewriting method effectively solves the technical problem that the traditional SQL enhancement or modification method cannot handle complex nested structures, and effectively applies the filtering conditions to all subqueries and each part of the join query, so that the data filtering is complete and logically correct. Through the accurate syntax tree parsing and recursive traversal mechanism, the method can go deep into each level of the SQL query, including multi-layer nested subqueries, join queries, and nested structures in various DML statements, ensuring that the dynamic modification logic can be consistently applied to all related query parts. Compared with the traditional string replacement or regular expression matching method, the AST-based method has higher accuracy and reliability, avoiding the risk of mis-matching and semantic damage. The technical solution supports multiple database dialects, generates SQL statements that meet the specific database syntax requirements through the adaptation mechanism of the target database type, greatly improving the compatibility and portability of the system. The dynamic rewriting function provides strong technical support for key business requirements such as data isolation, role-based permission control, and data desensitization in multi-tenant systems, achieving flexible data access control without modifying business code. The modular design of the method and the application of the visitor pattern make the system have good scalability, making it easy to add new rewriting rules and support new SQL syntax features, providing reliable technical support for data security and compliance requirements of enterprise-level applications

[0051] Other features and advantages of the embodiments of the present application will be described in detail in the following specific embodiments. BRIEF DESCRIPTION OF DRAWINGS

[0052] Figure 1 A flowchart of an AST-based complex SQL query dynamic rewriting method provided by the embodiments of the present application;

[0053] Figure 2 A system architecture diagram of an AST-based complex SQL query dynamic rewriting method provided by the embodiments of the present application. DETAILED DESCRIPTION

[0054] In order to make the objects, technical solutions and advantages of the embodiments of the present application clearer, the following will clearly and completely describe the technical solutions in the embodiments of the present application with reference to the drawings in the embodiments of the present application. It should be understood that the specific implementation manners described here are only used to explain and describe the embodiments of the present application, and should not be used to limit the embodiments of the present application. Based on the embodiments in the present application, all other embodiments obtained by a person of ordinary skill in the art without creative work fall within the scope of protection of the present application.

[0055] It should be noted that if the embodiments of the present application involve directional indications (such as up, down, left, right, front, back, etc.), the directional indications are only used to explain the relative positional relationship, movement condition, etc. between components in a certain posture (as shown in the drawings), and if the certain posture changes, the directional indications also change accordingly.

[0056] In addition, if the embodiments of the present application involve descriptions such as “first”, “second”, etc., the descriptions of “first”, “second”, etc. are only for description purposes, and should not be understood as indicating or implying the relative importance of the indicated technical features or implicitly indicating the number of the indicated technical features. Therefore, the features limited by “first”, “second” can explicitly or implicitly include at least one of the features. In addition, the technical solutions of each embodiment can be combined with each other, but it must be based on the fact that a person of ordinary skill in the art can realize it, and when the combination of technical solutions contradicts each other or cannot be realized, it should be considered that the combination of technical solutions does not exist, and is also not within the scope of protection claimed by the present application.

[0057] Figure 1 A flowchart of a method for dynamically rewriting a complex SQL query based on AST according to an embodiment of the present application is schematically shown. As shown in Figure 1 The method for dynamically rewriting a complex SQL query based on AST according to an embodiment of the present application is applied to a server, the server is used to execute a SQL query, the SQL query includes at least one nested subquery or a joint query, and the method can include the following steps.

[0058] S110, in response to receiving an original SQL query statement and a target database type of the SQL query statement, parsing the original SQL query statement into an AST;

[0059] S120, initializing a SQL processor, the SQL processor includes a selection statement accessor and an expression accessor, the selection statement accessor is used to traverse a selection statement structure in the AST, and the expression accessor is used to traverse an expression structure in the AST;

[0060] S130, traversing the AST by the SQL processor and rewriting SQL expression and SQL statement structure in the AST according to preset dynamic modification logic;

[0061] The traversing the AST by the SQL processor includes:

[0062] S140, recursively traversing the selection statement structure of the main query and recursively traversing the nested subquery structure in the selection statement structure;

[0063] S150, recursively traversing each selection statement structure in the joint query; and

[0064] S160, recursively traversing the condition expression or nested selection statement structure in the update statement, the delete statement and the insert statement;

[0065] S170, converting the rewritten AST into a dynamically rewritten SQL query statement; and

[0066] S180, returning the dynamically rewritten SQL query statement.

[0067] The server in the embodiment can be a database server, an application server or a distributed computing node, and the server includes a SQL parser, an AST processing module, a SQL rewriting engine and a code generator. The SQL parser is used for processing step S110, the AST processing module is used for processing steps S120 to S160, the SQL rewriting engine is used for processing the dynamic modification logic in step S130, and the code generator is used for processing steps S170 to S180.

[0068] Referring to Figure 2 After receiving the original SQL query statement and the target database type, the server first starts a SQL parsing process to convert the SQL statement in text form into a structured abstract syntax tree (AST). The SQL parser adopts a two-stage processing mode of lexical analysis and syntax analysis. In the lexical analysis stage, the SQL string is decomposed into a token sequence, and basic syntax elements such as keywords (such as SELECT, FROM, WHERE), identifiers (table name, column name), operators ( =, >, < ), literals (string, number) and special symbols (bracket, comma) are recognized. In the syntax analysis stage, the AST is constructed according to the SQL syntax rules. Each node represents a syntax structure, such as the SelectStatement node containing selection list, FROM clause, WHERE clause and other subnodes.

[0069] For complex nested subqueries, the parser recursively creates independent SelectStatement nodes for each subquery and establishes parent-child relationships. For union queries (UNION or UNION ALL), the parser creates a UnionStatement node containing multiple SelectStatement child nodes. Target database type information is used to guide the parser to handle dialect differences specific to certain databases, such as MySQL's backtick identifiers, Oracle's double-quoted identifiers, PostgreSQL's special operators, and more. Syntax validation is also performed during parsing to ensure that the SQL statement adheres to standard syntax rules. Through this precise parsing process, the original SQL query is converted into a tree-like structure of programmable operations, providing a structured data foundation for subsequent dynamic rewriting operations.

[0070] The initialization process of the SQL processor involves creating specialized visitor components to handle different types of AST nodes. The SelectVisitor is responsible for traversing and processing SELECT statement-related nodes, including the main query, subqueries, and individual SELECT statements within union queries. This visitor implements the Visitor pattern, defining methods such as visitSelectStatement, visitFromClause, visitWhereClause, and more to handle different statement structures. The ExpressionVisitor, on the other hand, focuses on processing various expression nodes, such as ComparisonExpression, LogicalExpression, FunctionCallExpression, and more.

[0071] Each visitor maintains traversal state and context information, including the current traversal depth, parent node references, and the application state of rewriting rules. The SQL processor (RtSQLHandler) serves as the overall controller, coordinating the work of the two visitors and maintaining global rewriting configuration and state information. The initialization process also includes loading pre-set dynamic modification logic rules, which can come from configuration files, database storage, or runtime parameters. The processor also initializes the appropriate SQL dialect processor based on the target database type, ensuring that the generated SQL statements adhere to the syntax requirements of specific databases. Through this modular design, the SQL processor can flexibly handle various complex SQL structures while maintaining good scalability and maintainability.

[0072] The traversal of AST by SQL processor adopts a depth-first recursive algorithm to ensure that each node is visited. For the processing of comparison operation expressions, when the preset skip marker (such as the special placeholder "__SKIP__") is detected in the operands, the rewrite engine will replace the entire comparison expression with a Boolean constant expression. For example, the original expression "user_id = '__SKIP__'" will be rewritten as "TRUE" or "FALSE", depending on the business logic requirements.

[0073] For the processing of IN expressions, when the right-side list contains a skip marker, such as "department_id IN ('HR', '__SKIP__', 'IT')", the rewrite engine will convert it to a tautology expression "1=1" or a contradiction expression "1=0" based on the context. The processing of LIKE expressions is more complex, when the right-side expression contains a skip marker, it will be rewritten as a wildcard expression "%" to achieve full matching. For parameterized wildcard forms such as "name LIKE CONCAT('%',?, '%')", the rewrite engine will adapt based on the target database type, MySQL maintains the CONCAT function form, while SQLServer will be rewritten as "name LIKE '%' +? + '%'" form. Dynamic modification logic also includes conditional injection based on user identity and permissions, according to the current user's role and data sensitivity level, dynamically adding row-level filtering conditions in the WHERE clause, applying data desensitization functions or removing sensitive columns in the SELECT list, and injecting tenant ID filtering conditions to achieve multi-tenant data isolation.

[0074] In the recursive traversal of the main query's SelectStatement node, the traversal starts from the main query's SelectStatement node, the SELECT clause is accessed first, and it is checked whether the select list contains a subquery expression, such as the subquery in "SELECT id, (SELECT COUNT( ) FROM orders WHERE customer_id= c.id) as order_count FROM customers c". For such nested subqueries in the SELECT field, the expression accessor recursively creates a new traversal context to deeply process the internal structure of the subquery. Then the FROM clause is accessed to identify the derived table (subquery as table source), such as "SELECT FROM (SELECT FROM users WHERE active = 1) AS active_users".

[0075] For subqueries in the FROM clause, the accessor recursively calls the select statement accessor to handle the inner query. The traversal of the WHERE clause is the most complex, as it needs to handle various forms of nested subqueries, including EXISTS subqueries, IN subqueries, comparison subqueries, and so on. For example, for a structure like "WHERE user_id IN (SELECT user_id FROM permissions WHERE role = 'admin')", the expression accessor recognizes the subquery in the IN expression and recursively rewrites it. The traversal maintains a call stack information to record the current level and context of traversal, ensuring that the rewrite operation is correctly applied to each level of the query. This recursive traversal mechanism ensures that each subquery is correctly handled and rewritten, even in complex queries with multiple levels of nesting.

[0076] In recursive traversal of a union query, the union query combines the results of multiple SELECT statements, each of which needs to be handled independently while ensuring that the rewritten query remains semantically consistent. The traversal process starts from the UnionStatement node and obtains all the select bodies (SelectBody) in the union query list. For each select body, the select statement accessor creates an independent traversal context to avoid interference between the rewrite operations of different select bodies. When processing each select body, the accessor first checks if there is a WHERE clause, and if so, it traverses all the expressions in the WHERE clause through the expression accessor and applies the corresponding rewrite rules. For example, for the union query "SELECT FROM table1 WHERE dept = 'HR' UNION SELECT FROM table2 WHERE dept = 'IT'", the rewrite engine may need to add the same filter condition, such as tenant ID restriction, to both SELECT statements. The traversal of the select list needs to handle the nested subqueries it may contain, ensuring that the subqueries are also correctly rewritten.

[0077] The processing of the FROM clause is similar to the main query, requiring recursive processing of the derived tables and subqueries within it. The rewrite of the union query also needs to consider the semantic differences between UNION and UNION ALL, ensuring that the rewritten query does not change the original deduplication logic. Through this detailed recursive traversal, each component of the union query is consistently and correctly rewritten.

[0078] Recursive traversal of the condition expressions or nested select statement structures in update, delete, and insert statements requires handling the specialities of DML operations. For delete statements (DELETE), the expression accessor mainly focuses on the processing of the WHERE clause, as it determines which records will be deleted. The rewriting process can require the addition of extra security restrictions, such as row-level filtering conditions based on user permissions, to ensure that users can only delete data for which they have permissions. For example, the original delete statement "DELETE FROM documents WHERE status = 'draft'" can be rewritten as "DELETE FROM documents WHERE status = 'draft' AND owner_id =?", where the question mark parameter will be bound to the current user ID at runtime. The processing of update statements (UPDATE) not only needs to handle the WHERE clause but also the expressions in the SET clause.

[0079] The rewriting logic for the WHERE clause is similar to that of delete statements, mainly adding permissions and security restrictions. The SET clause can contain subqueries, such as "UPDATE users SET last_login = (SELECT MAX(login_time) FROM user_sessions WHERE user_id = users.id)", which also need to be recursively processed.

[0080] In this embodiment, the processing of insert statements (INSERT) focuses on the VALUES clause or the SELECT clause, when using the INSERT INTO... SELECT form, the nested SELECT statement needs to be recursively traversed and rewritten by the select statement accessor. The rewriting process also needs to automatically add audit fields, such as creation time, creation user, and other information. Through comprehensive processing of these DML statements, the security and consistency of data operations are effectively guaranteed.

[0081] Converting the rewritten AST into a dynamically rewritten SQL query statement requires re-serializing the tree structure in memory into executable SQL text. The code generator uses the visitor pattern to traverse the rewritten AST, implementing corresponding code generation logic for each type of node. For SelectStatement nodes, the generator will output the SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, and other clauses in the order of SQL syntax.

[0082] During the generation process, the nested relationship of parentheses needs to be handled correctly to ensure that the subquery is correctly contained in the parentheses. For complex expressions such as function calls, arithmetic operations, logical operations, etc., the generator needs to add necessary parentheses according to the operator precedence to avoid semantic ambiguity.

[0083] Different databases have differences in syntax details, such as the way identifiers are referenced (MySQL uses backticks, SQL Server uses square brackets), string concatenation functions (MySQL's CONCAT, SQL Server's + operator), pagination syntax (MySQL's LIMIT, SQL Server's TOP), etc. The generator will choose the corresponding syntax form according to the target database type. The generator needs to maintain the correct position and type information of the parameter placeholder to ensure that the generated SQL can correctly bind parameter values. Formatting options allow the generation of well-readable SQL code, including proper indentation, line breaks, and spaces, for debugging and maintenance. Through the above code generation process, the rewritten AST is converted into a semantically correct and semantically preserved SQL query statement.

[0084] Returning the dynamically rewritten SQL query statement completes the entire processing flow. Before returning, the server will perform syntax verification on the generated SQL statement to ensure that the rewriting process does not introduce syntax errors. The verification process includes basic syntax verification such as bracket matching check, keyword spelling check, and identifier legality check. Semantic verification checks whether the rewritten query maintains the original logical intent, such as ensuring that the added filter conditions do not cause logical contradictions, verifying the column number and type matching of each SELECT statement in the joint query, etc.

[0085] The rewriting process may introduce additional conditions or expressions, and the optimizer will analyze the impact of these changes on query performance, and if necessary, perform query recombination or add index hints. The implementation of the cache mechanism can improve the efficiency of rewriting. For the same original SQL and rewriting rule combination, the cached result can be directly returned, avoiding repeated parsing and rewriting processes. The log recording function records detailed information about the rewriting operation, including the original SQL, rewriting rules, generated SQL, etc., which facilitates problem troubleshooting and audit tracking. The error handling mechanism ensures that clear error information and recovery suggestions are given when encountering exceptions during the rewriting process. Through these perfect post-processing steps, the returned SQL query statement not only has correct functionality, but also has good performance and maintainability.

[0086] The embodiment effectively solves the technical problem that the traditional SQL enhancement technology cannot handle complex nested structures through the AST-based complex SQL query dynamic rewriting method. Through accurate syntax tree parsing and recursive traversal mechanism, the method can go deep into each level of the SQL query, including multi-layer nested subqueries, joint queries, and nested structures in various DML statements, ensuring that the dynamic modification logic can be consistently applied to all relevant query parts. Compared with traditional string replacement or regular expression matching methods, the AST-based method has higher accuracy and reliability, avoiding the risk of mis-matching and semantic damage. The technical solution supports multiple database dialects, generates SQL statements that meet the specific database syntax requirements through the target database type adaptation mechanism, greatly improving the compatibility and portability of the system. The dynamic rewriting function provides strong technical support for key business requirements such as data isolation, role-based permission control, and data desensitization in multi-tenant systems, achieving flexible data access control without modifying business code. The modular design of the method and the application of the visitor pattern make the system have good scalability, making it easy to add new rewriting rules and support new SQL syntax features, providing reliable technical support for data security and compliance requirements of enterprise-level applications.

[0087] In one embodiment of the present embodiment, the SQL expression and SQL statement structure in the AST are rewritten according to the preset dynamic modification logic, including the following steps:

[0088] S210, in response to at least one operand of the comparison operation expression in the AST containing a preset skip marker, rewriting the comparison operation expression into a preset Boolean constant expression;

[0089] S220, in response to the right side list of the IN expression in the AST containing a preset skip marker, rewriting the IN expression into a preset always true or always false expression.

[0090] The embodiment further refines the rewriting processing mechanism for specific expression types in the dynamic modification logic. In response to at least one operand of the comparison operation expression in the AST containing a preset skip marker, the rewriting engine will perform accurate marker identification and expression replacement operations. The comparison operation expression usually contains two operands and a comparison operator, such as equal (=), not equal (<> or!=), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), etc.

[0091] The pre-defined skip marker is a specially designed identifier, usually in the form of a special string such as "__SKIP__", "__NULL_CONDITION__", or "__BYPASS__", which will not appear in normal business data, ensuring the accuracy of marker identification. When the expression accessor traverses to the comparison operation expression node, it checks whether the left and right operands contain these pre-defined markers.

[0092] The checking process combines exact matching and pattern matching to identify both exact matches and parameterized marker forms such as "__SKIP_CONDITION_01__". Once a skip marker is detected, the rewrite engine replaces the entire comparison expression with a Boolean constant expression according to predefined rules. For example, the original expression "user_status = '__SKIP__'" is rewritten as "TRUE", while "department_id <> '__SKIP__'" can be rewritten as "FALSE".

[0093] The choice of Boolean constant is based on business logic requirements and security policies, with TRUE indicating that the condition is always met (i.e., no filtering) and FALSE indicating that the condition is never met (i.e., filtering out all records). This rewriting mechanism is particularly useful in dynamic query construction scenarios, where certain filtering conditions do not need to be applied at runtime. By using skip markers, dynamic disabling of conditions can be achieved, avoiding complex conditional branching logic.

[0094] The rewritten Boolean constant expression can be further optimized by the SQL optimizer, with TRUE conditions being completely removed and FALSE conditions causing the entire query to be optimized to an empty result set, thereby improving query execution efficiency.

[0095] In response to the right side list of the IN expression in the AST containing the pre-defined skip marker, the rewriting process needs to consider the special semantics of the IN expression and the complexity of the list structure. The IN expression is used to check whether the value of the left side expression exists in the right side value list, with the syntax form "column_name IN (value1, value2, value3,...)".

[0096] When the right side list contains skip markers, the rewrite engine needs to analyze the position and number of markers to determine the appropriate rewriting strategy. If the list only contains skip markers without other valid values, such as "department_id IN ('__SKIP__')", the entire IN expression will be rewritten as a tautology "1=1" or a contradiction "1=0", with the specific choice depending on the business logic requirements.

[0097] If the list contains both a skip marker and a valid value, such as "status IN ('active', '__SKIP__', 'pending')", the rewrite engine removes the skip marker and retains the valid value, rewriting to "status IN ('active', 'pending')". In some special cases, if the skip marker has a special semantic meaning, it can be necessary to convert it to a specific conditional expression. For example, the "__SKIP_ALL__" marker can indicate matching all possible values, in which case the IN expression is rewritten to a tautology.

[0098] The rewrite process also needs to handle the case of parameterized queries, when the IN list contains parameter placeholders, the rewrite engine needs to preserve the correctness of parameter binding. For IN expressions in the form of nested subqueries, such as "user_id IN (SELECT user_id FROM temp_table WHERE condition = '__SKIP__')", the rewrite engine recursively processes the skip marker in the subquery, and can rewrite the entire subquery to a query that returns all possible values or an empty result set.

[0099] The generation of tautologies and contradictions needs to consider the compatibility of the target database, most databases support the form of "1=1" and "1=0", but some databases can need to use "TRUE" and "FALSE" keywords. This intelligent IN expression rewriting mechanism greatly simplifies the construction logic of dynamic queries, especially in scenarios that need to dynamically adjust the query range according to user permissions or business rules.

[0100] In one embodiment of the present embodiment, rewriting the SQL expression and SQL statement structure in the AST according to the preset dynamic modification logic further includes: S1, identifying specific SQL patterns in the AST through a SQL processor, and rewriting them to equivalent SQL structures according to preset performance optimization rules, the rewriting including at least one of the following: rewriting IN expressions containing subqueries to JOIN or EXISTS clauses; dynamically injecting database-specific index hints in the FROM clause of the AST; and simplifying redundant or inefficient expressions in the AST.

[0101] Specifically, AST rewriting not only modifies the filtering logic, but also optimizes the structure of SQL statements according to database characteristics or performance rules to improve query efficiency.

[0102] Rewrite certain types of IN(SELECT...) subqueries as JOIN or EXISTS clauses, or convert dependent subqueries to non-dependent ones to avoid repeated execution and improve performance. According to preset rules or external performance analysis results, dynamically inject database-specific index hints (such as MySQL's USE INDEX, Oracle's / ) in the FROM clause to guide the query optimizer to use more optimal indexes. Identify and simplify redundant or inefficient expressions in the AST, such as simplifying WHERE a = 1 AND a = 1 to WHERE a = 1. Dynamically generate SQL that is most suitable for the pagination mechanism of different databases (such as Oracle's ROWNUM, MySQL's LIMIT, PostgreSQL / SQL Server's OFFSET / FETCH). The AST in this embodiment can implement cross-database pagination syntax conversion. +INDEX(...) / ), guide the query optimizer to use more optimal indexes. Identify and simplify redundant or inefficient expressions in the AST, such as simplifying WHERE a = 1 AND a = 1 to WHERE a = 1. Dynamically generate SQL that is most suitable for the pagination mechanism of different databases (such as Oracle's ROWNUM, MySQL's LIMIT, PostgreSQL / SQL Server's OFFSET / FETCH). The AST in this embodiment can implement cross-database pagination syntax conversion.

[0103] In one embodiment of the present embodiment, the SQL expressions and SQL statement structures in the AST are rewritten according to preset dynamic modification logic, which further includes: S2, through the SQL processor, identifying the SQL syntax structure, function or data type specific to the source database in the AST according to the target database type, and rewriting it to the equivalent SQL syntax structure, function or data type of the target database, to realize automatic conversion of SQL dialects.

[0104] Specifically, the sqlLikeConvertByDbType method is used to realize automatic conversion of SQL between different database dialects, greatly simplifying the development work of database migration and multi-database support. The SQL statements of the source database (such as Oracle) are automatically converted to equivalent SQL statements of the target database (such as MySQL, PostgreSQL), including function name conversion, data type conversion, special syntax structure conversion (such as DECODE to CASE), etc. Automatically convert old SQL syntax (such as some deprecated functions or keywords) to new SQL syntax to adapt to database upgrades.

[0105] In one embodiment of the present embodiment, the SQL expressions and SQL statement structures in the AST are rewritten according to preset dynamic modification logic, which further includes: S3, through the SQL processor, performing at least one of the following: identifying an insert statement or an update statement in the AST and dynamically injecting an audit field and its corresponding value; and dynamically injecting a SQL comment containing context information in the AST.

[0106] Specifically, the embodiment can inject audit information or monitoring tags in SQL statements through AST rewriting without modifying the application code, facilitating subsequent log analysis and performance tracking. For INSERT and UPDATE statements, automatically inject audit fields (such as created_by and updated_at) and fill in the current user and timestamp. Insert a comment with the application name, user ID, module information, and other context at the beginning or specific position of the SQL statement to facilitate database administrators or monitoring systems to track query sources. After parsing the AST, analyze the complexity indicators of the SQL statement (such as nesting depth, number of JOINs, number of function calls, etc.) to assess query risks or make performance predictions.

[0107] The embodiment achieves accurate dynamic control and optimization of SQL query conditions through intelligent rewriting of comparison operation expressions and IN expressions. The skip marker mechanism provides a way to handle the dynamic enabling and disabling of condition expressions, avoiding the complex string concatenation and conditional branching logic in traditional methods. The Boolean constant rewriting of comparison operation expressions can effectively simplify the query structure, allowing the SQL optimizer to perform deeper optimization and improve query execution efficiency. The intelligent rewriting of IN expressions not only correctly handles the skip markers in the list but also maintains the consistency and correctness of query semantics. This solution is particularly suitable for multi-tenant systems, permission control systems, and dynamic report systems that require dynamic adjustment of query logic based on runtime conditions. By converting complex conditional logic into simple Boolean expressions, not only is the query performance improved, but the maintainability and readability of the code are also enhanced. The rewritten SQL statement structure is clearer, facilitating the database optimizer to optimize the execution plan, thereby significantly improving the overall performance of the system while ensuring functional correctness.

[0108] In one embodiment of the embodiment, rewriting the SQL expression and SQL statement structure in the AST according to the preset dynamic modification logic further includes the following steps:

[0109] S310, in response to the right side expression of the LIKE expression in the AST containing a preset skip marker, rewriting the right side expression of the LIKE expression into a preset wildcard expression; or

[0110] S320, in response to the right side expression of the LIKE expression being in a parameterized wildcard form, rewriting the right side expression into a string concatenation form corresponding to the target database according to the target database type.

[0111] This embodiment inherits the skip marker identification technique and AST traversal mechanism described above, and is specially optimized for the special needs of fuzzy matching queries. In response to the right side expression of the LIKE expression in the AST containing the preset skip marker, the rewrite engine will perform special fuzzy matching condition optimization processing. The LIKE expression is an important operator for pattern matching in SQL, and its basic syntax form is "column_name LIKE pattern", where pattern can contain wildcards such as percent sign (%) representing zero or more characters, and underscore (_) representing a single character.

[0112] When the right side pattern expression of the LIKE expression contains the preset skip marker, such as "user_name LIKE '__SKIP__'" or "description LIKE '%__SKIP__%'", the rewrite engine needs to determine the appropriate rewrite strategy according to the specific meaning and position of the skip marker. This embodiment can rewrite the pattern expression containing the skip marker to the wildcard expression "%" to achieve full matching effect, i.e. matching all possible string values.

[0113] For example, "product_name LIKE '__SKIP__'" will be rewritten to "product_name LIKE '%'", so that the query condition returns true for all product names. In more complex cases, if the skip marker appears in a composite pattern, such as "title LIKE '%__SKIP__%report%'", the rewrite engine will analyze the structure of the pattern and rewrite it to "title LIKE '%report%'", retaining the valid matching part and removing the skip marker part.

[0114] For special skip markers such as "__SKIP_ALL__", it can be directly rewritten to "%" to achieve full matching, while "__SKIP_NONE__" can be rewritten to a pattern that will never match, such as "__IMPOSSIBLE_MATCH__". The rewriting process also needs to consider the issue of case sensitivity. Some databases' LIKE operation is case sensitive, while some are not, and the rewrite engine will adjust the rewriting strategy according to the characteristics of the target database.

[0115] In addition, for LIKE expressions using the ESCAPE clause, the rewrite engine needs to correctly handle escape characters to ensure that the rewritten pattern expression is semantically consistent. This LIKE expression rewriting mechanism in the dynamic search function can achieve dynamic expansion of the search range when some search conditions do not need to be applied at runtime.

[0116] In response to the right-hand side expression of the LIKE expression being in parameterized wildcard form, the rewrite engine needs to perform database dialect adaptation processing according to the target database type. Parameterized wildcard form refers to the use of parameter placeholders and string concatenation functions in the LIKE pattern to dynamically construct the matching pattern, common forms include "CONCAT('%',?, '%')", "'%' +? + '%'", "'%' ||? || '%'" and so on.

[0117] Different database systems have significant differences in string concatenation syntax, MySQL uses the CONCAT function for string concatenation, such as "name LIKE CONCAT('%',?, '%')"; SQL Server uses the plus operator, such as "name LIKE '%' +? + '%')"; Oracle and PostgreSQL use the double vertical bar operator, such as "name LIKE '%'||? || '%'" ; SQLite supports multiple forms but recommends using the double vertical bar operator.

[0118] When processing parameterized wildcard forms, the rewrite engine first needs to identify the concatenation syntax used in the current expression, and then convert it to the corresponding syntax form according to the target database type. The identification process is achieved by analyzing the function call nodes and operator nodes in the AST, for CONCAT function calls, the rewrite engine checks the function name and parameter list; for operator forms, it analyzes the operator type of the binary expression.

[0119] The conversion process needs to keep the position and type of the parameters unchanged, only change the syntax form of the concatenation. For example, when the target database switches from MySQL to SQL Server, "CONCAT('%',?, '%')" will be rewritten as "'%' +? +'%'". The rewrite engine also needs to handle more complex nested concatenation cases, such as "CONCAT('%', UPPER(?), '%')", in which case the inner function call needs to be processed recursively.

[0120] For complex concatenation expressions containing multiple parameters, the rewrite engine maintains the order and type information of the parameters to ensure that the rewritten expression is functionally equivalent. In addition, the rewrite process also needs to consider the way string literals are referenced, some databases use single quotes and some use double quotes, the rewrite engine will make corresponding adjustments according to the specifications of the target database. This database dialect adaptation mechanism ensures that the application can be seamlessly migrated between different database systems, greatly improving the portability and compatibility of the system.

[0121] The embodiment further improves the dynamic rewriting capability of SQL queries by special processing of LIKE expressions, providing strong support especially in fuzzy matching and string search scenarios. The application of skip markers in LIKE expressions makes the dynamic search function more flexible, enabling dynamic adjustment of search range and seamless switching from exact match to full match according to business requirements. The database dialect adaptation in the form of parameterized wildcard solves an important technical problem in cross-database platform deployment, enabling the same set of application code to run correctly on different database systems without the need to write specific SQL statements for each database. This solution, together with the aforementioned rewriting mechanisms for comparison operation expressions and IN expressions, forms a complete expression processing system that covers the most commonly used conditional expression types in SQL queries. Through a unified skip marker mechanism and rewriting logic, the construction process of dynamic queries is simplified, and the execution efficiency of queries and the maintainability of the system are improved. This expression rewriting capability provides a technical foundation for building a highly flexible and configurable data access layer, and is particularly suitable for enterprise-level application systems that need to support complex search functions and multi-database environments.

[0122] In one implementation of the embodiment, the SQL expression and SQL statement structure in the AST are rewritten according to the preset dynamic modification logic, including the following steps:

[0123] S410, through the SQL processor, in response to identifying the SQL query statement to be rewritten according to the current user identity, role or data sensitivity level, at least one of the following is performed:

[0124] S420, dynamically injecting row-level filtering conditions in the WHERE clause of the SQL query statement to limit the data rows accessible by the user;

[0125] S430, dynamically applying a desensitization function to sensitive columns in the SELECT list of the SQL query statement or removing them from the SELECT list; and

[0126] S440, dynamically injecting tenant ID filtering conditions in the SQL query statement to achieve multi-tenant data isolation.

[0127] When the SQL processor responds to the SQL query statement to be rewritten according to the current user identity, role or data sensitivity level, the rewriting engine performs context analysis and permission evaluation processing. The user identity recognition process first obtains user information from the current execution context, including user ID, username, organization, authentication token and other basic identity data.

[0128] Role identification requires querying the user role mapping table to obtain all the roles the user currently has, such as administrator, ordinary user, auditor, and visitor, each of which is associated with specific data access and operation permissions. Determining the data sensitivity level requires analyzing the tables and columns involved in the SQL query and querying the data classification directory to determine the sensitivity level of each data element, such as public, internal, confidential, and top secret.

[0129] The identification process of the SQL query statement is implemented by analyzing the AST structure. The rewriting engine checks the table name, column name, operation type, and other information involved in the query and matches them with predefined security policy rules. The matching process uses a multi-level rule engine, including table-level rules, column-level rules, operation-level rules, and combined rules. For example, when it is detected that the query involves the employee salary table and the current user does not have the HR role, the query will be marked as needing rewriting.

[0130] The rule engine also supports complex conditional expressions, such as time-based access control (only allow access to certain data during working hours) and IP address-based access control (only allow access to sensitive data from specific networks). The identification process also considers the complexity of the query, and for complex SQL containing multiple table joins, subqueries, and union queries, the security requirements of each component need to be recursively analyzed. Through this identification mechanism, all queries that require security control can be accurately identified and processed accordingly.

[0131] The processing of dynamically injecting row-level filter conditions in the WHERE clause of the SQL query statement requires precise condition construction and logical integration mechanisms. Row-level filtering is a fine-grained data access control method that restricts users to access only the data rows they are authorized to access by adding additional filter conditions to the query conditions.

[0132] The rewriting engine first needs to analyze the existing WHERE clause structure to determine the injection point and injection method. If the original query does not have a WHERE clause, the rewriting engine will create a new WHERE clause; if there is already a WHERE clause, the rewriting engine needs to use the AND logical operator to combine the new filter conditions with the existing conditions.

[0133] The construction of filtering conditions is based on the user's permission configuration and data access policy. Common filtering conditions include user ID-based filtering (e.g., "created_by =?"), department-based filtering (e.g., "department_id IN (SELECT department_id FROM user_departments WHERE user_id =?)"), data status-based filtering (e.g., "status IN ('public', 'internal')"), and so on.

[0134] For hierarchical permission structures, such as the superior-inferior relationship in organizational structures, filtering conditions may require the use of recursive queries or hierarchical queries.

[0135] The injection process also needs to consider the security of parameterized queries to avoid SQL injection attacks. All dynamic values should be passed through parameter binding. For complex nested queries, the rewriting engine needs to recursively inject the corresponding filtering conditions in each subquery to ensure the integrity of data filtering. Time-related filtering conditions need to consider time zone and date format issues to ensure correct execution in different database systems. Through this row-level filtering injection mechanism, fine-grained control of data access is achieved, effectively preventing data leakage and unauthorized access.

[0136] The process of dynamically applying desensitization functions to sensitive columns in the SELECT list of SQL query statements or removing them from the SELECT list requires complex column analysis and conversion mechanisms. The identification of sensitive columns is based on predefined data classification directories and sensitivity markers. Common sensitive columns include mobile phone numbers, email addresses, salary information, and other personal privacy data.

[0137] The rewriting engine identifies sensitive columns by traversing each selection item in the SELECT list. Selection items can be simple column references, expressions, function calls, or complex forms such as subqueries. For simple column references, such as "SELECT name, phone, email FROM users", the rewriting engine checks whether each column name is in the sensitive column list. For column references with table aliases, such as "SELECT u.name, u.phone FROM users u", the table alias needs to be parsed to determine the actual table and column.

[0138] For complex expressions and function calls, such as "SELECT CONCAT(first_name, '', last_name) AS full_name", all columns involved in the expression need to be analyzed. The application of desensitization functions is determined based on data types and sensitivity levels. Mobile phone numbers can use "CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4))" to mask the middle four digits, while email addresses can use "REPLACE(email, '@','[at] ')". Partial masking can be done using ",RIGHT(phone,4))", and email addresses can be masked using "CONCAT(LEFT(email,2),' @',SUBSTRING_INDEX(email,'@',-1))".

[0139] For numerically sensitive data, such as salaries, range-based desensitization can be used, such as "CASE WHEN salary < 5000THEN '<5K' WHEN salary < 10000 THEN '5K-10K' ELSE '>10K' END". During column removal, it's crucial to ensure the query remains valid after removing the sensitive column, especially when using ORDER BY or GROUP BY clauses, where column references in these clauses need to be adjusted accordingly. For calculated columns and aggregate functions, desensitization can be more complex, requiring appropriate obfuscation while maintaining the statistical characteristics of the data.

[0140] Dynamically injecting tenant ID filters into SQL queries to achieve multi-tenant data isolation requires consideration of the complexity of the multi-tenant architecture and the stringent requirements for data isolation. In a multi-tenant system, data from different tenants must be strictly isolated to prevent data leakage and cross-access.

[0141] Tenant IDs are typically retrieved from the current user's session context and can be stored in the user authentication token, session variables, or request headers. The rewrite engine needs to identify all multi-tenant tables involved in the query, which typically contain tenant_id, org_id, or similar tenant identifier fields.

[0142] For single-table queries, the injection process is relatively simple, such as inserting "SELECT ... FROM orders" rewritten as "SELECT FROM orders WHERE tenant_id = ?". For multi-table join queries, you need to add tenant filter conditions to each multi-tenant table, such as "SELECT o." The statement `c.name FROM orders o JOIN customers cON o.customer_id = c.id` needs to be rewritten as `SELECT o`. , c.name FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.tenant_id =? AND c.tenant_id =?".

[0143] For complex queries containing subqueries, the tenant filter condition needs to be injected recursively in each subquery. In some cases, tenant isolation can require more complex logic, such as tenant isolation based on data sharding, which can require dynamic modification of table or database names. For shared tables (such as system configuration tables, code tables, etc.), tenant filter conditions can not need to be added, and the rewriting engine needs to maintain a whitelist of shared tables.

[0144] Tenant ID verification and authorization checks ensure that the current user can only access data belonging to their tenant, preventing tenant ID forgery attacks. For special roles such as super administrators, cross-tenant access capabilities can be required, which requires special permission checks and condition construction logic. Through this tenant isolation mechanism, the security isolation and access control of data in a multi-tenant system are ensured.

[0145] This embodiment realizes the guarantee of data security and access control in enterprise-level applications through a dynamic SQL rewriting mechanism based on user identity, role, and data sensitivity. Dynamic injection of row-level filter conditions ensures that users can only access authorized data rows, effectively preventing data leakage and unauthorized access, especially suitable for financial, medical, government, and other industry applications that require strict data isolation. Dynamic desensitization and removal of sensitive columns protect personal privacy and sensitive information while still allowing business systems to operate normally, achieving a balance between data usability and security. The automated implementation of multi-tenant data isolation greatly simplifies the development and maintenance of SaaS applications, and developers do not need to manually add tenant filter conditions in each query, and the system automatically ensures the integrity and consistency of data isolation. This solution forms a complete SQL security control system with the aforementioned expression rewriting mechanism, covering all aspects of data access control from basic syntax rewriting to business rule application. Through a unified rewriting engine and flexible rule configuration, not only the security of the system is improved, but also the maintainability and scalability are enhanced, providing technical support for building a secure and reliable enterprise-level data platform.

[0146] In one implementation of the embodiment, the SELECT statement structure of the main query is recursively traversed, and the subquery structure nested in the SELECT statement structure is recursively traversed, including the following steps:

[0147] S510, traversing the WHERE clause of the SELECT statement structure through the expression accessor;

[0148] S520, traversing the subquery nested in the SELECT field of the SELECT statement structure by the expression accessor; and

[0149] S530, traversing the subquery nested in the FROM clause of the SELECT statement structure by the expression accessor.

[0150] When traversing the WHERE clause of the SELECT statement structure by the expression accessor, various complex expressions and nested subquery structures that may exist in the WHERE clause need to be handled. The WHERE clause is one of the most complex parts of a SQL query, and can contain multiple layers of nested logical expressions, comparison expressions, function calls, and various forms of subqueries.

[0151] The expression accessor employs a depth-first traversal strategy, starting from the root expression node of the WHERE clause, and recursively visits each sub-expression node. For logical expressions (such as AND, OR, NOT), the accessor recursively traverses their left and right operands; for comparison expressions (such as =, >, <, LIKE, IN), it processes the expressions on both sides separately; for function call expressions, it traverses all the argument expressions.

[0152] During the traversal, when the accessor encounters a subquery expression node, it identifies the type of the subquery, including scalar subquery (returns a single value), EXISTS subquery (checks existence), IN subquery (membership check), comparison subquery (compares with a single value), etc. For each type of subquery, the accessor creates a new traversal context and recursively calls the SELECT statement accessor to handle the internal structure of the subquery.

[0153] For example, for an IN subquery like "WHERE user_id IN (SELECT user_id FROM permissions WHERE role = 'admin')", the expression accessor identifies the subquery part in the IN expression, and then recursively performs a complete traversal and rewriting process on the subquery "SELECT user_id FROM permissions WHERE role = 'admin'".

[0154] Scope information also needs to be maintained during the traversal to ensure that column references in the subquery can be correctly resolved, especially when the subquery references columns from the outer query. The expression accessor also handles complex nesting cases, such as multiple layers of subqueries within subqueries, by recursively calling the SELECT statement accessor to ensure that each level of query is correctly processed.

[0155] When traversing a SELECT field with nested subqueries, the expression accessor needs to pay special attention to the special nature and processing requirements of subqueries in the SELECT list. Subqueries in SELECT fields are usually scalar subqueries, used to compute derived columns or for data correlation, such as "SELECT id, name, (SELECT COUNT( ) FROM orders WHERE customer_id = c.id) AS order_count FROM customers c".

[0156] When traversing the SELECT list, the expression accessor examines each selected item one by one, which can be a simple column reference, an expression, a function call, or a subquery. When a subquery selected item is encountered, the accessor needs to ensure that the subquery returns a single value (a scalar value), as multi-value subqueries are not allowed in SELECT fields.

[0157] For complex expression selected items, such as "SELECT CONCAT(first_name,'- ', (SELECT department_name FROM departments WHERE id = e.department_id)) AS full_info FROM employees e", the accessor needs to recursively analyze each component of the expression, identify nested subqueries, and handle them accordingly.

[0158] Rewriting of subqueries needs to take into account their special requirements in the SELECT context, such as ensuring the return type of the subquery matches the expected type of the outer query, handling possible NULL values, and so on. For subqueries nested in aggregate functions, such as "SELECT SUM((SELECT price FROM products WHERE id = oi.product_id) quantity) FROM order_items oi", the accessor needs to correctly handle the rewriting of subqueries in the aggregate context.

[0159] When a subquery is rewritten, it is necessary to ensure that the aliases remain valid and do not cause naming conflicts. The expression accessor also needs to handle subqueries in CASE expressions, such as "SELECT CASE WHEN (SELECT status FROM orders WHERE id = 1) = 'completed' THEN 'Done' ELSE 'Pending' END", ensuring that the subquery in each WHEN and THEN branch is handled correctly.

[0160] When traversing a subquery nested in the FROM clause of a SELECT statement structure through the expression accessor, it is necessary to handle the complex cases and special requirements of derived tables (subqueries used as table sources). Subqueries in the FROM clause are often used as derived tables, providing a data source for the main query, such as "SELECT FROM (SELECT user_id, COUNT( ) as login_count FROM user_sessions GROUP BY user_id) AS user_stats WHERE login_count >10".

[0161] When traversing the FROM clause, the expression accessor checks each table reference and identifies subquery table references within it. For derived table subqueries, the accessor needs to create an independent traversal context, as derived tables have their own scope and column space.

[0162] Rewriting a subquery needs to ensure that the structure of its result set (number of columns, column types, column names) is consistent with the expectations of the outer query, especially when the outer query references columns of the derived table by column position or column name. For complex nested cases, such as multiple levels of nested derived tables within a derived table, the accessor needs to recursively handle each level to ensure consistency and correctness of the rewriting operation.

[0163] When a derived table is given an alias, it is necessary to ensure that the rewritten subquery can still be correctly referenced by the alias. For FROM clauses containing multiple table references, such as in join operations with derived tables, the accessor needs to correctly handle the derived table column references that may be involved in the join conditions.

[0164] CTEs (Common Table Expressions) are not syntactically in the FROM clause, but are logically similar to derived tables, and the accessor needs to be able to recognize and handle CTE subqueries defined in the WITH clause. For recursive CTEs, the accessor needs to be especially careful to ensure that the rewriting operation does not disrupt the logical structure of the recursion. With this FROM clause traversal mechanism, both derived tables and subqueries are ensured to be correctly rewritten in a variety of complex scenarios.

[0165] This embodiment achieves coverage and deep processing of SQL query structures by precise traversal and processing of different clauses in SELECT statements. Expression traversal in the WHERE clause ensures that all nested subqueries in the query conditions are correctly rewritten, including complex logical expressions and multi-layer nested structures. Special processing of subqueries in the SELECT fields guarantees the accuracy of derived column calculations and the consistency of data types, especially in scenarios involving complex expressions and aggregate functions. The traversal mechanism of derived tables in the FROM clause ensures that rewriting operations at the data source level can be correctly performed, maintaining the structural integrity of the query result set. The coordinated processing of these three aspects forms a complete SELECT statement traversal system, which, combined with the aforementioned expression rewriting and security control mechanisms, builds a functional complete, accurate processing SQL dynamic rewriting system. This scheme can not only handle simple single-table queries, but also cope with complex multi-table joins, multi-layer nested subqueries and other scenarios commonly encountered in enterprise-level applications, providing technical support for building high-performance, high-security data access layers.

[0166] In one implementation of the embodiment, each SELECT statement structure in the union query is recursively traversed, including the following steps:

[0167] S610, traverse each select body in the union query list and recursively traverse the select body through the SELECT statement accessor.

[0168] The processing of traversing each select body in the union query list and recursively traversing the select body through the SELECT statement accessor needs to consider the special semantics and structural complexity of the union query. The union query combines the results of two or more SELECT statements into a unified result set through the UNION or UNION ALL operator, and each SELECT statement participating in the union is called a select body.

[0169] In the AST structure, the union query is usually represented as a UnionStatement node, which contains a list of select bodies and a corresponding list of union operators. The traversal process starts from the root node of UnionStatement, first obtains the list of all select bodies, and then processes each select body in left-to-right order.

[0170] For each select body, the traverser needs to create an independent processing context, as different select bodies can involve different tables, different data sources, and even can need to apply different rewrite rules. The select statement accessor performs a complete SELECT statement traversal and rewriting process for each select body, including the traversal and rewriting of all components such as SELECT clause, FROM clause, WHERE clause, GROUP BY clause, HAVING clause, ORDER BY clause, etc.

[0171] It is particularly important to note that each select body in a union query must have the same number of columns and compatible data types, and the rewriting operation cannot break this structural consistency. For example, for a union query like "SELECT id, name FROM users WHERE active = 1 UNION SELECT id, title FROM products WHERE status = 'available'", the rewrite engine needs to ensure that the rewriting rules applied in both select bodies remain consistent, and if a tenant filter condition "AND tenant_id =?" is added in the first select body, the corresponding tenant filter condition needs to be added in the second select body as well.

[0172] For nested union queries like "(SELECT... UNION SELECT...) UNION SELECT...", the traverser needs to recursively process the inner union query structure. The select bodies can contain subqueries, which also need to be recursively traversed and rewritten by the corresponding accessors.

[0173] The traversal process also needs to handle special cases for the ORDER BY clause. In a union query, the ORDER BY clause can only appear in the outermost layer and is used to sort the entire union result. The rewriting operation needs to ensure that the columns referenced in the ORDER BY clause are valid in the union result. Through this systematic traversal mechanism, each component in the union query is ensured to be consistently and correctly rewritten, maintaining the integrity of the query semantics and the correctness of the result.

[0174] The embodiment further improves the dynamic rewriting capability of complex SQL queries by special processing of the union query structure, especially providing reliable support in scenarios requiring the combination of multiple data sources or multiple query conditions. The uniform traversal mechanism of the union query ensures that each select body in the UNION and UNION ALL operations is consistently rewritten, avoiding the problem of result set structure mismatch caused by inconsistent rewriting. The scheme forms a complete query structure processing system with the aforementioned main query traversal and subquery processing mechanisms, and can handle various complex SQL query scenarios. Through recursive calls of the SELECT statement accessor, the integrity of the processing is guaranteed, and the modularity and maintainability of the code are maintained. This joint query processing capability provides technical support for building a data access layer that supports complex business logic, and is particularly suitable for enterprise-level application scenarios that require the integration of data from multiple business systems or support for complex report queries.

[0175] In one implementation of the embodiment, the SELECT statement accessor recursively traverses the select body, including the following steps:

[0176] S710, determining whether the select body contains a WHERE clause;

[0177] S720, in the case where the select body contains a WHERE clause, traversing the WHERE clause through the expression accessor to rewrite the SQL expressions in the WHERE clause;

[0178] S730, traversing the select item list of the select body, and recursively traversing the nested subqueries contained in the select item list through the expression accessor to rewrite the SQL expressions and SQL statement structures in the nested subqueries;

[0179] S740, determining whether the FROM clause of the select body contains a nested subquery;

[0180] S750, in the case where the FROM clause contains a nested subquery, traversing the nested subquery through the expression accessor to rewrite the SQL expressions and SQL statement structures in the nested subquery.

[0181] The process of determining whether the select body contains a WHERE clause requires precise node inspection and type identification of the AST structure of the select body. The select body (SelectBody) is typically represented as a SelectStatement node in the AST, which contains multiple optional child nodes such as SelectClause (SELECT clause), FromClause (FROM clause), WhereClause (WHERE clause), GroupByClause (GROUP BY clause), HavingClause (HAVING clause), OrderByClause (ORDER BY clause), etc.

[0182] The presence of the WHERE clause is determined by checking whether the whereClause property of the SelectStatement node is null. In some SQL dialects, the WHERE clause can have different representations, and the judgment logic needs to consider these differences. For example, some databases support a constant true condition such as WHERE 1=1, and some support an existence check such as WHERE EXISTS(...), and the judge needs to be able to recognize these different forms of WHERE clauses.

[0183] For complex select bodies, the WHERE clause can contain multiple layers of nested logical expressions, and the judgment process not only confirms the presence of the WHERE clause but also analyzes its complexity to provide guidance information for subsequent traversal and rewriting operations. The judgment result will affect the subsequent processing flow. If the WHERE clause does not exist, the rewriting engine can need to create a new WHERE clause to add necessary filtering conditions; if the WHERE clause already exists, the new conditions need to be logically combined with the existing conditions.

[0184] The judgment process also needs to consider the syntactic correctness of the WHERE clause to ensure that subsequent traversal operations will not fail due to syntax errors. Through this precise judgment mechanism, a reliable foundation is laid for the subsequent processing of the WHERE clause.

[0185] In the case where the select body contains a WHERE clause, the process of traversing the WHERE clause to rewrite SQL expressions in the WHERE clause has been described in detail above. This step inherits the aforementioned WHERE clause traversal techniques, including recursive processing of various complex expressions and nested subqueries, ensuring that all expressions in the WHERE clause are correctly rewritten. The rewriting operation is based on the pre-set dynamic modification logic, such as the skip marker processing described in S210 to S440, the LIKE expression adaptation, the row-level filter condition injection, the sensitive column desensitization, or the tenant ID filtering, etc.

[0186] The process of traversing the select list of a select body and recursively traversing the nested subqueries contained in the select list to rewrite SQL expressions and SQL statement structures in the nested subqueries requires a deep analysis of the complex structure of the SELECT clause. The select list (SelectItemList) contains one or more select items (SelectItem), each of which can be a simple column reference, a complex expression, a function call, a subquery, or a wildcard ( ).

[0187] The traversal process starts with the first element of the select list and examines the type and structure of each select item in turn. For simple column references such as "column_name" or "table.column_name", the traverser checks if the column name needs to be rewritten, such as the de-identification of sensitive columns. For expression select items such as "CONCAT(first_name,'', last_name) AS full_name", the traverser needs to recursively analyze each component of the expression, identifying any subqueries that may be contained within.

[0188] For function call select items such as "COUNT(DISTINCT user_id)", the traversal of all arguments of the function is required to check if any of the arguments contain subqueries. Most importantly, the handling of subquery select items such as "(SELECT COUNT( )FROM orders WHERE customer_id = c.id) AS order_count", which contain complete SELECT statements, requires recursive traversal by the expression accessor.

[0189] The rewriting of subqueries needs to ensure that their return value types match the expected types of the select items, especially when numerical calculations or string operations are involved. For complex nested expressions such as "CASE WHEN (SELECT status FROM orders WHERE id = 1) = 'completed' THEN (SELECT total FROM orders WHERE id = 1) ELSE 0 END", the traverser needs to recursively handle each subquery in the CASE expression.

[0190] When subqueries are rewritten, it is necessary to ensure that aliases remain valid and descriptive. The traversal process also needs to handle subqueries in aggregate functions such as "SUM((SELECT price FROM products WHERE id = oi.product_id) quantity)", ensuring that subquery rewriting in the aggregation context does not affect the correctness of the aggregation result.

[0191] The process of determining whether the FROM clause of a select body contains a nested subquery requires analysis and recognition of the complex structure of the FROM clause. The FROM clause can contain various forms of table references, including simple table name references, table alias references, derived tables (subqueries as table sources), join operations, table-valued function calls, and so on.

[0192] The recognition of nested subqueries mainly targets derived table forms, such as the subquery part in "FROM (SELECT FROM usersWHERE active = 1) AS active_users". The judgment process needs to traverse all table references in the FROM clause and check the type of each table reference. For nodes of type SubqueryTableReference, it indicates that this position contains a nested subquery.

[0193] For join operations, it is necessary to recursively check whether the left and right sides of the join contain derived tables. For complex multi-table joins, such as "FROM table1 t1 JOIN (SELECT id, name FROM table2 WHERE status = 'active') t2 ON t1.id = t2.id LEFT JOIN table3 t3 ON t2.id = t3.ref_id", the judge needs to be able to identify each derived table in the join chain.

[0194] CTEs (Common Table Expressions) may not directly appear in the FROM clause in syntax, but are logically similar to derived tables, and the judge also needs to be able to identify CTEs defined in the WITH clause. For nested derived tables, such as a derived table containing another derived table, the judge needs to be able to recognize multi-layer nested structures.

[0195] The judgment result not only confirms the existence of nested subqueries, but also records their position and structure information, providing accurate navigation for subsequent traversal operations. Through this judgment mechanism, it is ensured that all nested structures in the FROM clause can be accurately recognized and processed.

[0196] When the FROM clause contains nested subqueries, the process of traversing the nested subqueries using an expression accessor to rewrite the SQL expressions and SQL statement structures within them has been described in detail above. This step inherits the aforementioned FROM clause traversal technique, including recursive processing of derived tables and complex nested structures, ensuring that all nested subqueries in the FROM clause are correctly rewritten. The rewriting operation also follows a pre-defined dynamic modification logic, including but not limited to the various expression rewriting and security policy injection methods detailed in S210 to S440.

[0197] This embodiment achieves precise control and rewriting of each component of the SELECT statement through systematic traversal and processing of the internal structure of the select body. Conditional judgment and traversal of the WHERE clause ensure the correct application of query filtering logic, deep traversal of the select list guarantees accurate processing of derived columns and nested subqueries, and structural analysis and rewriting of the FROM clause maintains the integrity of the data source layer. This fine-grained traversal mechanism, combined with the aforementioned expression rewriting and security control functions, constructs a fully functional and precisely processed SQL dynamic rewriting system. This solution can handle not only simple query structures but also complex nested queries and multi-table joins common in enterprise applications. Through modular design and recursive processing strategies, the system has good scalability and maintainability, providing technical support for building a high-performance, high-security data access layer, and is particularly suitable for enterprise application systems requiring fine-grained data control and complex business logic support.

[0198] In one embodiment of this invention, recursively traversing the conditional expressions or nested selection statement structures in update, delete, and insert statements includes the following steps:

[0199] S810. Traverse the WHERE clause of the delete statement using the expression accessor;

[0200] S820, Traversing the WHERE clause of an update statement using an expression accessor; and

[0201] S830. Traverse the nested selection statement structure in the insert statement using the selection statement accessor.

[0202] The processing of the WHERE clause of a DELETE statement by iterating through it using an expression accessor requires special attention to the security and integrity requirements of the data deletion operation. The WHERE clause of a DELETE statement determines which data records will be deleted, therefore its rewriting process directly affects data security and the correctness of business logic.

[0203] The AST structure of a delete statement usually contains target table information and an optional WHERE clause, and the expression visitor needs to perform more strict security checks when traversing the WHERE clause than a SELECT query. First, the visitor checks if the WHERE clause exists, and if the delete statement has no WHERE clause (i.e. in the form of DELETE FROM table_name), the rewrite engine automatically adds necessary restrictions based on the security policy to prevent accidental full-table deletion.

[0204] For example, in a multi-tenant environment, a "WHERE tenant_id =?" condition can be automatically added to ensure that a user can only delete data from their own tenant. For delete statements with an existing WHERE clause, the expression visitor recursively traverses each component of the conditional expression and applies the same rewrite rules as for a SELECT query, including handling of skip markers, rewriting of comparison expressions, optimization of IN expressions, etc.

[0205] Especially important is the injection of row-level filtering conditions based on user permissions to ensure that a user can only delete data records for which they have permission. For example, the original delete statement "DELETE FROM documents WHERE status = 'draft'" can be rewritten as "DELETE FROM documents WHERE status = 'draft' AND (owner_id =? OR creator_id =?)", where the parameters are bound to the current user's IDs.

[0206] For delete conditions containing subqueries, such as "DELETE FROM orders WHERE customer_id IN(SELECT id FROM customers WHERE region = 'inactive')", the visitor needs to recursively process the expressions in the subquery to ensure that the subquery also applies the corresponding security restrictions. Audit and logging are important aspects of delete operations, and the rewrite engine can automatically add audit-related conditions or trigger the recording of audit logs.

[0207] Error handling mechanisms need to ensure that if an unsafe delete operation is found during the rewriting process (such as a condition that may cause a large amount of data loss), it can be prevented or warned in time. Through this delete statement WHERE clause traversal mechanism, the safety and controllability of data deletion operations are ensured.

[0208] The processing of traversing the WHERE clause of an update statement through expression accessors is similar to that of a delete statement in terms of security control, but additional considerations are needed for expression processing in the SET clause. An update statement (UPDATE) not only determines the range of records to update through the WHERE clause, but also defines the specific updates through the SET clause, so the rewriting process needs to consider both aspects.

[0209] The traversal processing of the WHERE clause is basically the same as for a delete statement, including the injection of security conditions, permission checks, recursive processing of subqueries, etc. The rewriting engine ensures that users can only update data records that they have permission to, by adding filtering conditions based on user identity, role, or data ownership to achieve fine-grained access control. For example, "UPDATE user_profiles SET phone =? WHERE user_id =?" can be rewritten as "UPDATE user_profiles SET phone =? WHERE user_id =? AND (user_id =? OR? IN (SELECT user_id FROM admin_users))", ensuring that users can only update their own profiles or administrators can update any user's profile.

[0210] The processing of the SET clause, although not within the scope of the WHERE clause, is also an important part of the rewriting of update statements and can contain subqueries, such as "UPDATE products SET price = (SELECT AVG(price) FROM competitor_products WHERE category = products.category) WHERE category = 'electronics'", which also need to be traversed and rewritten through the corresponding accessors.

[0211] The rewriting engine can add additional verification conditions to ensure that the update operation does not violate business rules or data constraints. Version control and concurrency control can also be achieved through the rewriting of the WHERE clause, such as automatically adding version number check conditions "AND version =?" to prevent concurrent update conflicts.

[0212] The processing of traversing the nested SELECT statement structure within an INSERT statement through the SELECT statement accessor requires attention to various forms of INSERT statements and complex data source handling. INSERT statements come in various syntax forms, including VALUES form (INSERT INTO table VALUES (...)), SELECT form (INSERT INTO table SELECT...), and mixed forms, among others.

[0213] When the INSERT statement uses the SELECT form, i.e., "INSERT INTO target_table SELECT... FROM source_table WHERE...", which contains a complete SELECT query statement, this nested SELECT statement needs to be fully traversed and rewritten by the SELECT statement accessor. The SELECT statement accessor performs the same processing flow as an independent SELECT query on the nested SELECT statement, including traversal of the WHERE clause, processing of SELECT fields, analysis of the FROM clause, and application of various security controls and permission checks. For example, "INSERT INTO user_backup SELECT FROM users WHERE last_login < '2023-01-01'" can be rewritten as "INSERT INTO user_backup SELECT FROM users WHERE last_login < '2023-01-01' AND tenant_id =? AND status IN ('active', 'inactive')", ensuring that only the eligible user data of the current tenant is copied.

[0214] For complex INSERT queries, such as SELECT statements containing multi-table joins, subqueries, aggregate functions, etc., the accessor needs to recursively process all nested structures.

[0215] Permission checks not only verify the user's insert permissions on the target table but also verify the read permissions on the source table. Performance optimization for batch insert operations also needs to be considered, and the rewritten query should maintain good execution efficiency. For special syntaxes such as INSERT... ON DUPLICATE KEY UPDATE or INSERT... ON CONFLICT, the rewriting engine needs to correctly handle these database-specific extended syntaxes. Through this INSERT statement traversal mechanism, the safety, integrity, and efficiency of data insertion operations are ensured.

[0216] The embodiment realizes complete security control and standardized processing of data operation by traversing and rewriting the condition expression and nested query structure in the DML statement. The security rewriting of the WHERE clause of the delete and update statement ensures the controllability and security of the data modification operation, effectively preventing security risks such as accidental deletion and unauthorized modification. The processing of the nested SELECT in the insert statement ensures the standardization and consistency of the data replication and migration operation. The scheme forms a complete SQL operation control system with the aforementioned SELECT query rewriting mechanism, covering all aspects of database operation. Through the unified accessor architecture and rewriting rules, the implementation of security control is simplified, and the maintainability and scalability of the system are improved. This DML statement processing capability provides technical support for building a safe and reliable data access layer, and is particularly suitable for enterprise-level application systems that require strict data security control and audit tracking, effectively ensuring the compliance and business continuity of data operation.

[0217] In one embodiment of the embodiment, the SQL processor is RtSQLHandler, the select statement accessor is SelectVisitor, and the expression accessor is ExpressionVisitor.

[0218] The SQL processor uses RtSQLHandler (Real-time SQL Handler) as the specific implementation class, which embodies the core function positioning of real-time SQL processing. RtSQLHandler, as the overall controller and coordination center of the entire SQL rewriting system, is responsible for managing and coordinating the workflow of the two specialized accessors, SelectVisitor and ExpressionVisitor.

[0219] The processor realizes the complete SQL rewriting life cycle management, from receiving the original SQL query statement and the target database type to returning the rewritten SQL query statement. RtSQLHandler maintains a rewriting configuration manager inside, which is used to load and manage various dynamic modification logic rules, including skip marker processing rules, security policy configurations, database dialect adaptation rules, etc.

[0220] The processor also realizes the context management function, maintaining the context data required in the rewriting process such as current user identity information, permission configuration, tenant information, etc. Error handling and exception management are also important responsibilities of RtSQLHandler. When encountering syntax errors, permission conflicts, configuration missing, etc. in the rewriting process, it can provide clear error information and recovery suggestions.

[0221] The exception handling mechanism includes capturing specific types of parsing exceptions, business rule checking exceptions, and runtime rewriting exceptions, and logging according to preset error levels (such as warnings, errors, fatal errors), in some cases, it can trigger rollback or return to default security query.

[0222] Performance monitoring and statistics functions help system administrators understand the efficiency of rewriting operations and resource consumption. Cache management mechanisms improve system performance by caching commonly used rewriting results, especially for repeated SQL patterns and rewriting rule combinations. RtSQLHandler also provides a plug-in extension interface, allowing developers to add custom rewriting logic and processing rules based on specific needs.

[0223] The select statement accessor uses SelectVisitor as the concrete implementation class, which is responsible for traversing and processing various SELECT statement structures in SQL queries. SelectVisitor implements the standard Visitor Pattern, providing specialized access methods for different types of SELECT-related nodes in the AST.

[0224] The accessor defines the visitSelectStatement method to process the SELECT statement of the main query, the visitSubquery method to process nested subqueries, and the visitUnionStatement method to process union queries. Each access method implements the traversal logic and rewriting rules for specific types of nodes.

[0225] SelectVisitor maintains internal traversal state information, including the current traversal depth, parent node reference, scope information, etc., to ensure that rewriting rules can be correctly applied in complex nested structures. The accessor also implements traversal optimization, which analyzes the complexity of the query structure to choose the optimal traversal strategy and avoid unnecessary repeated traversal.

[0226] Context information is passed between accessors through parameter passing or shared context objects, ensuring that rewriting logic can make decisions based on a unified runtime environment and configuration.

[0227] For different types of SELECT statements (such as simple queries, join queries, aggregation queries, window function queries, etc.), SelectVisitor provides targeted processing logic to ensure that each query type is correctly rewritten. The accessor also supports conditional traversal, which determines whether to traverse specific substructures based on rewriting rule configurations, improving processing efficiency.

[0228] The expression accessor takes an ExpressionVisitor as the concrete implementation class, which is responsible for traversing and processing various expression structures in the SQL query. The ExpressionVisitor also implements the visitor pattern, providing specialized processing methods for different types of expression nodes in the AST.

[0229] The accessor defines methods such as visitComparisonExpression to handle comparison expressions, visitLogicalExpression to handle logical expressions, visitFunctionCallExpression to handle function call expressions, visitInExpression to handle IN expressions, visitLikeExpression to handle LIKE expressions, and so on. Each method implements the overridden logic for the corresponding expression type, including the identification and handling of skip markers, database dialect adaptation, security condition injection, and more.

[0230] The ExpressionVisitor internally implements an expression type inference mechanism, which can automatically identify the data types and semantic meanings of expressions, providing accurate type information for the overridden operations. The accessor also implements expression optimization functionality, which can simplify and optimize expressions during the rewriting process, such as simplifying a constant true condition to TRUE and a constant false condition to FALSE.

[0231] For complex nested expressions, the ExpressionVisitor provides recursive processing capabilities to ensure that each node of the expression tree is correctly processed. The accessor also supports semantic verification of expressions, ensuring that the rewritten expressions are both syntactically and semantically correct.

[0232] These three core components collaborate through well-defined interfaces, forming a complete SQL rewriting processing chain. The RtSQLHandler serves as the overall controller, selecting the appropriate accessor based on the structure type of the AST; the SelectVisitor calls the ExpressionVisitor to process expressions within the SELECT statement while traversing the SELECT statement; and the ExpressionVisitor can call back the SelectVisitor to process nested subqueries when dealing with complex expressions. This modular design not only improves code maintainability and testability but also provides a good foundation for system expansion and customization.

[0233] The embodiment realizes the engineering implementation and product deployment of the SQL dynamic rewriting system through the explicit component naming and the standardized architecture design. The explicit definition of the three core components, RtSQLHandler, SelectVisitor and ExpressionVisitor, provides a clear technical framework for the development, testing, deployment and maintenance of the system. The standardized class naming and interface design improve the readability and maintainability of the code, facilitating team collaborative development. The modular architecture design makes the system have good scalability, and new rewriting rules and processing logic can be flexibly added according to business requirements.

[0234] The embodiment of the application also provides a server, comprising:

[0235] a memory configured to store instructions; and

[0236] a processor configured to call the instructions from the memory and capable of realizing the AST-based complex SQL query dynamic rewriting method when executing the instructions.

[0237] The embodiment of the application also provides a machine readable storage medium, which has instructions stored thereon, the instructions being used to make a machine execute the AST-based complex SQL query dynamic rewriting method.

[0238] Those skilled in the art should understand that the embodiments of the application can be provided as a method, a system or a computer program product. Therefore, the application can adopt a completely hardware embodiment, a completely software embodiment or an embodiment combining software and hardware aspects. Moreover, the application can adopt the form of a computer program product implemented on one or more computer usable storage media containing computer usable program code (including but not limited to disk storage, CD-ROM, optical storage, etc.).

[0239] The application is described with reference to the flowcharts and / or block diagrams according to the embodiments of the application. It should be understood that each flow and / or block in the flowcharts and / or block diagrams and the combination of the flows and / or blocks can be realized by computer program instructions. These computer program instructions can be provided to the processor of a general-purpose computer, a special-purpose computer, an embedded processor or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device produce a machine that implements the functions specified in the flowcharts and / or block diagrams. Figure 1 The apparatus for realizing the functions specified in one flow or multiple flows and / or blocks Figure 1 The apparatus for realizing the functions specified in one flow or multiple flows and / or blocks

[0240] These computer program instructions can also be stored in a computer- readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instructions which implement the Figure 1 function specified in the flow or flows and / or blocks Figure 1 of the block or blocks.

[0241] These computer program instructions can also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the Figure 1 function specified in the flow or flows and / or blocks Figure 1 of the block or blocks.

[0242] In a typical configuration, a computing device includes one or more processors (CPUs), input / output interfaces, network interfaces, and memory.

[0243] The memory can include non-persistent memory and / or volatile memory, such as a random access memory (RAM) including a cache area for the temporary storage of data. The memory can also include non-volatile memory, such as read only memory (ROM), electrically programmable read only memory (EPROM), electrically erasable programmable read only memory (EEPROM), flash memory, or a combination of non-volatile memories in different forms. The memory is an example of computer readable storage media.

[0244] Computer readable media includes permanent and non-permanent, removable and non-removable media implemented in any method or technology for storage of information such as computer readable instructions, data structures, program modules or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read only memory (ROM), electrically programmable read only memory (EEPROM), flash memory or other memory technology, compact disc read only memory (CD-ROM), digital versatile disc (DVD), or other optical storage, magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other non-transmission medium that can be used to store information accessible to a computing device. According to the definition herein, computer readable media does not include transitory media, such as modulated data signals and carrier waves.

[0245] It should also be noted that the terms "comprising", "comprises" or other variations thereof are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but can also include other elements not expressly listed or inherent to such process, method, article, or apparatus.

[0246] The above embodiments are only used to illustrate the present application, but not to limit it. Instead of the above, various modifications and changes can be made to the application by those skilled in the art. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the application shall fall into the scope of the claims of the application.

Claims

1. A method for dynamically rewriting complex SQL queries based on AST, characterized in that, Applied to a server, the server executes SQL queries, which include at least one nested subquery or union query, and the method includes: In response to receiving the original SQL query statement and the target database type of the SQL query statement, the original SQL query statement is parsed into an AST; Initialize the SQL processor, which includes a selection statement accessor and an expression accessor. The selection statement accessor is used to traverse the selection statement structure in the AST, and the expression accessor is used to traverse the expression structure in the AST. The SQL processor traverses the AST and rewrites the SQL expressions and SQL statement structures in the AST according to the preset dynamic modification logic. The process of traversing the AST using the SQL processor includes: Recursively traverse the main query's selection statement structure, and recursively traverse the nested subquery structures within the selection statement structure; Recursively traverse each select statement structure in a join query; and Recursively traverse conditional expressions or nested selection statement structures in update, delete, and insert statements; Convert the rewritten AST into dynamically rewritten SQL query statements; and Returns the dynamically rewritten SQL query statement; Rewrite the SQL expressions and SQL statement structures in the AST according to the preset dynamic modification logic, including: In response to identifying the SQL query to be rewritten based on the current user identity, role, or data sensitivity level, the SQL processor executes at least one of the following: Dynamically inject row-level filtering conditions into the WHERE clause of an SQL query to restrict the data rows that users can access; In the SELECT list of an SQL query, dynamically apply desensitization functions to sensitive columns or remove them from the SELECT list; and Extract the tenant ID from the current user's session context and identify all multi-tenant tables involved in the SQL query statement; For single-table queries, inject the tenant ID filter condition into the WHERE clause; For multi-table join queries, add a tenant ID filter condition to each multi-tenant table to ensure that tenant isolation is applied to all tables in the join operation; For queries containing subqueries, the tenant ID filter condition is recursively injected into each subquery; All dynamic values ​​are passed through parameter binding to prevent SQL injection attacks. In this case, all dynamic values ​​during the injection process are passed through parameter binding.

2. The method according to claim 1, characterized in that, Rewrite the SQL expressions and SQL statement structures in the AST according to the preset dynamic modification logic, including: In response to at least one operand of a comparison operation expression in the AST containing a preset skip flag, the comparison operation expression is rewritten as a preset Boolean constant expression; The right-hand list of the IN expression in the AST contains preset skip flags, rewriting the IN expression as a preset always true or always false expression.

3. The method according to claim 2, characterized in that, Rewriting the SQL expressions and SQL statement structures in the AST according to the preset dynamic modification logic also includes: In response to a LIKE expression in the AST containing a default skip flag, the right-hand side of the LIKE expression is rewritten as a default wildcard expression; or In response to the LIKE expression, the right-hand expression is in parameterized wildcard form. Based on the target database type, the right-hand expression is rewritten into a string concatenation form corresponding to the target database.

4. The method according to claim 1, characterized in that, The recursive traversal of the main query's selection statement structure, and the recursive traversal of nested subquery structures within the selection statement structure, including: Iterate through the WHERE clause of the statement structure using the expression accessor; Iterate through nested subqueries within the SELECT fields of the selection statement structure using expression accessors; and Iterate through nested subqueries within the FROM clause of a selection statement structure using expression accessors.

5. The method according to claim 1, characterized in that, Recursively traverse the structure of each select statement in the join query, including: Iterate through each selection body in the union query list and recursively traverse the selection bodies using the selection statement accessor.

6. The method according to claim 5, characterized in that, The selection body is recursively traversed using the selection statement accessor, including: Determine if the selection body contains a WHERE clause; When the select body contains a WHERE clause, the WHERE clause is traversed through the expression accessor to rewrite the SQL expression in the WHERE clause; Iterate through the list of options in the selection body and recursively traverse the nested subqueries contained in the list of options using the expression accessor to rewrite the SQL expressions and SQL statement structures in the nested subqueries. Determine if the FROM clause of the select body contains nested subqueries; When the FROM clause contains nested subqueries, the nested subqueries are traversed using an expression accessor to rewrite the SQL expressions and SQL statement structure within the nested subqueries.

7. The method according to claim 1, characterized in that, Recursively traversing conditional expressions or nested selection statement structures in update, delete, and insert statements, including: Iterate through the WHERE clause of the delete statement using the expression accessor; Iterating through the WHERE clause of an update statement using an expression accessor; and Iterate through the nested selection statement structure within the insert statement using the selection statement accessor.

8. The method according to claim 1, characterized in that, The SQL processor is RtSQLHandler, the selection statement accessor is SelectVisitor, and the expression accessor is ExpressionVisitor.

9. A server, characterized in that, include: The memory is configured to store instructions; as well as A processor is configured to retrieve the instructions from the memory and, when executing the instructions, to implement the AST-based dynamic rewriting method for complex SQL queries according to any one of claims 1 to 8.

Images