Method and system for preventing tenants from crossing boundaries when user-defined SQL queries are performed based on druid

Abstract

This application proposes a method and system based on druid to prevent tenants from crossing the boundary when users customize SQL queries. It belongs to the field of multi-tenant data isolation, including data acquisition; parsing query statements; processing query statements involving tenant table names and tenant information; judging the query type represented by the FROM clause, and obtaining all table names involved in the FROM clause according to the query type; obtaining the current tenant information, taking the obtained table name as the processing object, connecting to the corresponding database according to the database connection information, and performing query operations for each table name to obtain the field information of the table; in the field information, checking whether there is a specific tenant identification field, if found, marking the table, and obtaining the list information of the table name and tenant identification field; processing the WHERE clause and parsing the SQL syntax tree; this application can prevent tenant data from crossing the boundary in a shared database multi-tenant scenario on the basis of reducing costs.

Description

Technical Field

[0001] The present application relates to the field of multi-tenant data isolation, and more specifically, to a method and system for preventing tenants from crossing boundaries when performing user-defined SQL queries based on Druid. Background Art

[0002] With the development and construction of cloud computing, multi-tenant technology has also been widely used in the field of cloud computing. Cloud service providers can use multi-tenant architecture to provide cloud computing resources to different users. Under the multi-tenant architecture, each user can get a relatively independent and customized environment. For example, each user can have his own virtual machine instance, which is logically isolated, and users can install and run the software and services they need in it. Users can also configure storage space and network settings according to their needs. In addition, all users share the same physical infrastructure (such as servers, storage devices, network devices, etc.) and management tools (such as monitoring, backup, recovery, etc.), and this sharing greatly reduces the cost of each user.

[0003] Prior art 1 discloses "Cloud Computing System and Cloud Computing Multi-Tenant Data Security Control Method", Publication (Announcement) No.: CN115659414A. This method first obtains a data set, and then allocates the data set to different spaces for storage according to the needs of multiple tenants. In essence, it isolates the data by data address. This method cannot guarantee data security for user-defined queries or customized SQL editing functions, because customized queries or SQL editing functions may bypass the original data isolation mechanism, thereby accessing data that should not be accessed, resulting in tenants crossing the boundary. Moreover, before data is stored, the partitions need to be calculated to determine in which space each data should be stored. This is highly complex and requires more computing resources and time to complete data allocation and isolation.

[0004] Prior art 2 discloses "System and method for realizing multi-tenant data isolation based on container", with publication (announcement) number: CN116719599A. This method is to complete tenants of access subscription data in the completion module through processes such as subscription access module, subscription data module, distribution module, completion module, scheduling module and contact module, which needs to rely on message queue to achieve, and this method involves multiple modules and complex interaction processes, which increases the complexity of the program, large amount of data calculation, low efficiency and complex algorithm.

[0005] The third existing technology is to implement tenant data isolation based on the multi-tenant plug-in TenantLineInnerInterceptor of MyBatis-Plus. The interceptor intercepts SQL and adds the assembled tenantSqlParser. This method relies on the paging interceptor of MySQL, and when the SQL statement contains too many UNION or JOIN operations, the TenantLineInnerInterceptor plug-in may not be able to correctly handle these complex SQL statements, resulting in data isolation failure or error, thereby causing tenant out-of-bounds behavior.

[0006] The above-mentioned existing technologies 1 and 2 both make physical isolation in data storage, that is, to provide each tenant with an independent storage space or database instance, including hardware costs, maintenance costs and operating costs, etc. For small and micro enterprises or small and micro projects, the cost is too high. Since the data is dispersed and stored in multiple independent storage spaces or database instances, when cross-tenant access is required, additional network transmission and data processing time may be required, thereby affecting the data response speed. Technology 3 relies on the multi-tenant plug-in of mybatis-plus, which cannot provide sufficient support and flexibility for complex SQL and user-defined SQL, and when there are data tables in the database that do not need to add tenant identifiers for data sharing, these data tables do not need to add tenant identifiers to distinguish different tenants. However, since technology 3 relies on SQL interception and modification to achieve data isolation, it is necessary to manually add logical processing for these shared data tables, which is not flexible enough and increases development costs. Summary of the invention

[0007] The purpose of this application is to provide a method and system based on druid to prevent tenants from crossing the boundary when users customize SQL queries, which can prevent tenant data from crossing the boundary in a shared database multi-tenant scenario on the basis of reducing costs.

[0008] This application is implemented as follows:

[0009] In a first aspect, the present application provides a method for preventing tenants from crossing boundaries when a user customizes SQL queries based on Druid, including:

[0010] Step 1, data acquisition; including acquiring the user's custom edited SQL query statement and database connection information Connection, the custom edited SQL query statement including the query statement SQLSelectStatement;

[0011] Step 2: Parse the query statement SQLSelectStatement; including dividing the query statement SQLSelectStatement into a UNION query statement and other query statements. For the union query statement, first process the union query statement into a union query object of UnionQuery; obtain the left query and the right query part of the union query statement, and respectively determine whether the left query and the right query part are a union query UnionQuery. If so, recursively process; otherwise, directly execute the recursive parsing syntax tree, parse the tenant's table name and tenant information, and save them;

[0012] Step 3: Process the table name and tenant information of the tenant involved in the query statement; including converting the parsed query statement into a SelectQueryBlock object; calling the getSelectList method of SelectQueryBlock to obtain the query field, traversing the query field, and if the query field has a subquery, recursively processing the subquery; the subquery includes the subquery of the from clause and the subquery of the WHERE clause;

[0013] Step 4: Receive the recursively processed FROM clause from step 3, determine the query type represented by the FROM clause, obtain all table names involved in the FROM clause according to the query type, and store them in tableNameList;

[0014] Step 5: Get the current tenant information, use the table name obtained in step 4 as the processing object, connect to the corresponding database according to the database connection information Connection, and perform a query operation on each table name to obtain the field information of the table; in the obtained field information, check whether there is a specific tenant identification field. If the tenant identification field is found in the table field, mark the table and obtain the list information of the table name and tenant identification field;

[0015] Step six, processing the WHERE clause and parsing the SQL syntax tree; including receiving the WHERE clause from step three, checking whether the WHERE clause contains a subquery, if the WHERE clause contains a subquery, extracting the subquery part, and the extracted subquery is regarded as an independent query and sent to step two for processing; if the WHERE clause does not contain a subquery, reconstructing the WHERE clause according to the information in step five, and the reconstruction method includes checking whether the table name of the WHERE clause exists in the list information in step five, if so, adding tenant conditions in the WHERE clause, these tenant conditions match the current tenant information obtained in step five, thereby limiting the query results to only include data that the current tenant has access to; if the original WHERE clause is empty, creating a new WHERE clause, and the created WHERE clause contains the table name, tenant identification field, and current tenant information in step five as filtering conditions.

[0016] Based on the first aspect, in step 3, if the query field has a subquery, the specific steps of recursively processing the subquery include:

[0017] Call the getFrom method of SelectQueryBlock to obtain the FROM statement of the query, determine whether the FROM clause has a subquery, and if so, recursively process the subquery in the FROM clause, otherwise continue to process the rest of the query; if the subquery in the FROM clause is an associated query SQLJoinTableSource, split the associated query statement, recursively process it, and parse its syntax tree; if the subquery in the FROM clause is a union query SQLUnionQueryTableSource, recursively process the union query and parse its syntax tree; obtain the query WHERE clause of SelectQueryBlock, and add the tenant identifier to the WHERE clause according to the table name list to ensure that the query only returns data for the current tenant.

[0018] Based on the first aspect, in step 4, the query type represented by the FROM clause is determined. According to the query type, the specific steps of obtaining all table names involved in the FROM clause include:

[0019] If the FROM clause represents an associated query SQLJoinTableSource, the left and right clauses of the associated query are processed separately, and the left and right clauses are processed recursively to collect all related table names; if the FROM clause represents a union query SQLUnionQuery, the left and right clauses of the union query are processed separately, and the left and right clauses are processed recursively to collect all related table names; if the FROM clause represents a single-table query, the target table targeted by the query is directly obtained, and the table name is extracted from it.

[0020] In a second aspect, the present application provides a system based on druid to prevent tenants from crossing boundaries when a user customizes SQL queries, characterized by including:

[0021] A data acquisition module is used to acquire a user's custom edited SQL query statement and database connection information Connection, wherein the custom edited SQL query statement includes a query statement SQLSelectStatement;

[0022] The query statement parsing module is used to parse the query statement SQLSelectStatement; including dividing the query statement SQLSelectStatement into a UNION query statement and other query statements. For the union query statement, the union query statement is first processed into a union query object of UnionQuery; the left query and the right query part of the union query statement are obtained, and it is determined whether the left query and the right query part are a union query UnionQuery respectively. If so, they are processed recursively; otherwise, the recursive parsing syntax tree is directly executed to parse the tenant's table name and tenant information and save them;

[0023] The subquery processing module is used to process the table name and tenant information of the tenant involved in the query statement; including converting the parsed query statement into a SelectQueryBlock object; calling the getSelectList method of SelectQueryBlock to obtain the query field, traversing the query field, and recursively processing the subquery if the query field has a subquery; the subquery includes the subquery of the from clause and the subquery of the WHERE clause;

[0024] The FROM clause processing module is used to receive the recursively processed FROM clause, determine the query type represented by the FROM clause, obtain all table names involved in the FROM clause according to the query type, and store them in tableNameList;

[0025] The list information acquisition module is used to obtain the current tenant information, take the table name as the processing object, connect to the corresponding database according to the database connection information Connection, and perform a query operation on each table name to obtain the field information of the table; in the obtained field information, check whether there is a specific tenant identification field. If the tenant identification field is found in the table field, the table is marked and the list information of the table name and tenant identification field is obtained;

[0026] The WHERE clause processing module is used to process the WHERE clause and parse the SQL syntax tree; it includes receiving the WHERE clause, checking whether the WHERE clause contains a subquery, if the WHERE clause contains a subquery, extracting the subquery part, and the extracted subquery is regarded as an independent query and sent to the query statement parsing module for processing; if the WHERE clause does not contain a subquery, reconstructing the WHERE clause according to the information in the list information acquisition module, and the reconstruction method includes checking whether the table name of the WHERE clause exists in the list information, if so, adding tenant conditions in the WHERE clause, these tenant conditions match the current tenant information, thereby limiting the query results to only include data that the current tenant has access to; if the original WHERE clause is empty, creating a new WHERE clause, and the created WHERE clause contains the table name, tenant identification field, and current tenant information in the list information acquisition module as filtering conditions.

[0027] In a third aspect, the present application provides an electronic device, characterized in that it includes:

[0028] A memory for storing one or more programs;

[0029] processor;

[0030] When the one or more programs are executed by the processor, the method according to any one of claims 1 to 3 is implemented.

[0031] In a fourth aspect, the present application provides a computer-readable storage medium having a computer program stored thereon, wherein when the computer program is executed by a processor, the method according to any one of claims 1 to 3 is implemented.

[0032] Compared with the prior art, the present invention has at least the following advantages or beneficial effects:

[0033] 1. By processing the query statement involving the table name of the tenant and the tenant information to obtain the table name and the list information of the tenant identification field, the query results are limited to only include the data that the current tenant has access to, solving the problem of tenant unauthorized access when the user customizes the SQL input.

[0034] 2. By processing the union query statement into a UnionQuery object, obtaining the left query and right query parts of the union query statement, and determining whether the left query and right query parts are UnionQuery, if so, recursively processing; otherwise, directly executing the recursive parsing syntax tree, parsing the tenant's table name and tenant information and saving them. It is possible to reversely check the tables and fields involved, add tenant information, and do not need to manually write code to filter tenant adaptation database tables, which is more flexible and reduces code development.

[0035] 3. There is no need to physically isolate data storage. Instead, the list information of table name and tenant identification field is obtained to limit the query results to only include data that the current tenant has access to. This solves the problems of high data isolation cost and complex algorithms for multiple modular data interaction processing, thus reducing costs.

[0036] 4. By processing the union query statement into a UnionQuery object; obtaining the left query and right query parts of the union query statement, respectively determining whether the left query and right query parts are UnionQuery, if so, recursively processing; otherwise, directly executing the recursive parsing syntax tree, parsing the tenant's table name and tenant information and saving; if the subquery in the FROM clause is an associated query SQLJoinTableSource, split the associated query statement, recursively process it, and parse its syntax tree. It can improve and optimize the database's ability to handle complex SQL queries, especially for queries involving UNION, JOIN and other operations. It can meet the data integration needs in different scenarios, such as adapting to reporting and large-screen display scenarios. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings required for use in the embodiments will be briefly introduced below. It should be understood that the following drawings only show certain embodiments of the present application and therefore should not be regarded as limiting the scope. For ordinary technicians in this field, other related drawings can be obtained based on these drawings without paying creative work.

[0038] Figure 1 This is a flowchart of an embodiment of a method for preventing tenants from crossing boundaries when a user customizes SQL query based on Druid in this application;

[0039] Figure 2 A flowchart of another embodiment of a method for preventing tenants from crossing boundaries when a user customizes SQL query based on Druid in this application;

[0040] Figure 3 A flowchart of another embodiment of a method for preventing tenants from crossing boundaries when a user customizes SQL query based on Druid in this application;

[0041] Figure 4 A structural diagram of a system based on druid to prevent tenants from crossing boundaries when users customize SQL queries for this application;

[0042] Figure 5 The present application provides a structural schematic diagram of an electronic device.

[0043] icon:

[0044] 1. Data acquisition module; 2. Query statement parsing module; 3. Subquery processing module; 4. FROM clause processing module; 5. List information acquisition module; 6. WHERE clause processing module; 7. Processor; 8. Memory; 9. Communication interface. DETAILED DESCRIPTION

[0045] In order to make the purpose, technical solution and advantages of the embodiments of the present application clearer, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, rather than all the embodiments. The components of the embodiments of the present application described and shown in the drawings here can be arranged and designed in various different configurations.

[0046] In conjunction with the accompanying drawings, some embodiments of the present application are described in detail below. In the absence of conflict, the following embodiments and features in the embodiments can be combined with each other.

[0047] Example

[0048] Please refer to Figure 1 , the embodiment of the present application provides a method for preventing tenants from crossing boundaries when a user customizes SQL query based on Druid, including:

[0049] Step 1, data acquisition; including acquiring the user's custom edited SQL query statement and database connection information Connection, the custom edited SQL query statement including the query statement SQLSelectStatement;

[0050] Specifically, the user's custom SQL query statement is written by the user according to his or her own data retrieval needs. Database connection information (Connection) includes data source information: for example, the database URL, user name, password, etc.; connection properties: various connection parameters and configurations, such as timeout settings, character sets; connection status: whether it is open, whether it is active, the last active time, error status, etc. By obtaining the user's custom edited SQL query statement and database connection information Connection, it is possible to establish a database connection and execute query statements.

[0051] Step 2: Parse the query statement SQLSelectStatement; including dividing the query statement SQLSelectStatement into a UNION query statement and other query statements. For the union query statement, first process the union query statement into a union query object of UnionQuery; obtain the left query and the right query part of the union query statement, and respectively determine whether the left query and the right query part are a union query UnionQuery. If so, recursively process; otherwise, directly execute the recursive parsing syntax tree, parse the tenant's table name and tenant information, and save them;

[0052] Specifically, the UNION operator is used to merge the result sets of two or more SELECT queries and remove duplicate rows. Therefore, query statements are divided into two categories: one is a query statement containing a UNION operator (UNION query statement), and the other is a query statement not containing a UNION operator (other query statement). When a UNION query statement is recognized, it is first converted into a specific data structure or object, namely, a "union query object of UnionQuery". When processing the UnionQuery object, the left half (i.e., the first SELECT subquery) and the right half (i.e., the second or subsequent SELECT subquery) of the UNION query are extracted. For the extracted left query and right query parts, it is checked whether they are also UNION queries (i.e., whether they can also be represented as UnionQuery objects). This is because UNION queries can be nested, that is, a subquery of a UNION query itself is also a UNION query. If the left query or the right query (or both) is recognized as a UNION query, the above processing process is recursively applied. That is, these sub-UNION queries are converted into UnionQuery objects again, and their subqueries are continuously extracted and checked until all subqueries are no longer UNION queries. If neither the left query nor the right query is a UNION query (that is, they are both simple SELECT queries), the queries are parsed using the recursive parsing syntax tree method. Recursive parsing syntax trees is a technique for breaking down query statements into smaller components (such as table names, column names, conditional expressions, etc.) and building a tree representing the query structure based on these components. In the process of recursive parsing of the syntax tree, the table names involved in the query and any tenant-related information (such as tenant ID or tenant identifier) ​​are identified and extracted, and the extracted table names and tenant information are saved for use in subsequent processing or execution stages. By enabling union queries to reversely query the tables and fields involved and add tenant information, there is no need to manually write code to filter tenant adaptation database tables, which is more flexible and reduces code development.

[0053] Step 3: Process the table name and tenant information of the tenant involved in the query statement; including converting the parsed query statement into a SelectQueryBlock object; calling the getSelectList method of SelectQueryBlock to obtain the query field, traversing the query field, and if the query field has a subquery, recursively processing the subquery; the subquery includes the subquery of the from clause and the subquery of the WHERE clause;

[0054] Specifically, considering that the query statement contains not only ordinary SQL syntax, but also involves specific information related to the tenant, such as the tenant's table name (in multi-tenancy, the data of different tenants is stored in tables with different table names) and the tenant identifier (used to distinguish the data of different tenants). In the process of processing the query statement, the statement is first parsed and then converted into a specific data structure or object, namely the "SelectQueryBlock object". The SelectQueryBlock object is a data structure that encapsulates all relevant information of the query statement, including the SELECT clause, FROM clause, WHERE clause, etc. The query statement is converted into a SelectQueryBlock object, and the getSelectList method of the object can be called to obtain the list of fields specified in the query. Traverse the query field list and check whether each field contains a subquery. If a field contains a subquery, the subquery will be processed recursively, that is, the same parsing and conversion process will be applied again, but this time for the subquery statement. Subqueries include subqueries in the FROM clause and subqueries in the WHERE clause. The subquery in the FROM clause is used to specify a temporary table or view as the data source for the query. The subquery in the FROM clause is used to specify a temporary table or view as the data source for the query.

[0055] Step 4: Receive the recursively processed FROM clause from step 3, determine the query type represented by the FROM clause, obtain all table names involved in the FROM clause according to the query type, and store them in tableNameList;

[0056] Specifically, recursive processing involves parsing subquery operations in the FROM clause to ensure that all involved tables or data sources are correctly understood and processed. After receiving the recursively processed FROM clause, it is necessary to determine the query type represented by the clause. Query types include simple table selection, table joins (such as INNER JOIN, LEFT JOIN, etc.), subqueries as data sources, etc. This judgment process helps to understand the structure and content of the FROM clause, so as to correctly extract table names. Once the query type of the FROM clause is determined, all involved table names can be extracted according to the type. This includes table names, aliases, join conditions, etc. in the FROM clause to ensure that all related tables are extracted. All extracted table names will be stored in a data structure called "tableNameList", which can be a list, array, or other type of collection, which is used to reference or operate these table names in subsequent query processing stages.

[0057] Step 5: Get the current tenant information, use the table name obtained in step 4 as the processing object, connect to the corresponding database according to the database connection information Connection, and perform a query operation on each table name to obtain the field information of the table; in the obtained field information, check whether there is a specific tenant identification field. If the tenant identification field is found in the table field, mark the table and obtain the list information of the table name and tenant identification field;

[0058] Specifically, the current tenant information is obtained to determine who the tenant of the current operation is. The tenant information includes the tenant ID, tenant name, database connection information, etc. After obtaining the current tenant information and table name, a connection to the corresponding database is established according to the tenant's database connection information (such as database address, port, user name, password, etc.). This connection is implemented through a database connection object (such as the Connection object in Java), which allows the execution of SQL statements, querying data, etc. Once the database connection is established, the previously obtained table name list is traversed. For each table name in the list, a query operation is performed to obtain the field information of the table. The field information includes the field name, data type, whether it is allowed to be empty, the default value, the field description, etc. The field information of each table is traversed to check whether there are one or more specific fields, which are used as tenant identification. The tenant identification field is used to distinguish the data of different tenants to ensure the isolation and security of the data. This inspection process involves matching field names, verifying data types, etc. If a tenant identification field that meets the conditions is found in the field information of a table, the table will be marked in some way (such as setting a flag bit, adding it to a specific list) to indicate that the table contains a tenant identification field. This tag can be used to identify and process the table in subsequent queries, data access, or data processing. While marking the table, the table name and the list information of the tenant identification field are also collected and stored. The table name is used to uniquely identify the table in the database. The list information of the tenant identification field includes the names of all the fields in the table that serve as tenant identification. This information plays a role in subsequent data access, permission control, data isolation and other operations.

[0059] Step six, processing the WHERE clause and parsing the SQL syntax tree; including receiving the WHERE clause from step three, checking whether the WHERE clause contains a subquery, if the WHERE clause contains a subquery, extracting the subquery part, and the extracted subquery is regarded as an independent query and sent to step two for processing; if the WHERE clause does not contain a subquery, reconstructing the WHERE clause according to the information in step five, and the reconstruction method includes checking whether the table name of the WHERE clause exists in the list information in step five, if so, adding tenant conditions in the WHERE clause, these tenant conditions match the current tenant information obtained in step five, thereby limiting the query results to only include data that the current tenant has access to; if the original WHERE clause is empty, creating a new WHERE clause, and the created WHERE clause contains the table name, tenant identification field, and current tenant information in step five as filtering conditions.

[0060] Specifically, during the parsing of the WHERE clause, it is checked whether the clause contains a subquery. A subquery is a query nested in another query, which is used to provide conditional values ​​in the WHERE clause or as part of the filtering condition. Checking the existence of the subquery is to ensure that the query can be executed correctly. If a subquery is found in the WHERE clause, it will be extracted. The extracted subquery is processed and executed separately, and the extracted subquery is now regarded as a new, independent query task. This subquery will be sent to the previous step 2 for processing. If it is found that the WHERE clause does not nest other queries, the WHERE clause is reconstructed according to the information in step 5. During the reconstruction of the WHERE clause, it is checked whether the table name in the clause exists in a certain list information generated in step 5. This list information contains all the table names containing the tenant identification field. By checking the existence of the table name, it can be determined whether it is necessary to add additional tenant conditions in the WHERE clause. If the table name in the WHERE clause does exist in the list information in step 5, then the additional tenant condition will be added to the WHERE clause. These tenant conditions involve the tenant identification field and the current tenant's information, which are used to ensure that the query results only contain data that the current tenant has access to. The added tenant conditions will be constructed based on the current tenant information obtained in step 5. Use the current tenant's information (such as tenant ID, tenant name, etc.) to generate the conditional expression in the WHERE clause to ensure that only data related to the current tenant is queried. By adding these tenant conditions in the WHERE clause, the scope of the query results can be limited to ensure that only data that the current tenant has access to is returned. This helps to achieve data isolation and access control in a multi-tenant environment and prevent data leakage between tenants. It solves the problem of tenant unauthorized access when the user customizes the input sql. It also reduces costs such as high data isolation costs and complex algorithms for multiple modular data interaction processing.

[0061] Please refer to Figure 2 In step 3, if the query field has a subquery, the specific steps of recursively processing the subquery include:

[0062] S31: Call the getFrom method of SelectQueryBlock to obtain the FROM statement of the query, and determine whether the FROM clause has a subquery. If yes, recursively process the subquery in the FROM clause, otherwise continue to process other parts of the query;

[0063] Specifically, the getFrom method is a method provided by the SelectQueryBlock object, which is used to obtain the FROM clause in the query statement. After obtaining the FROM clause, it will check whether the clause contains a subquery. If the FROM clause does contain a subquery, the subquery will not be processed directly, but will be processed recursively. This process will continue until all subqueries are processed or a FROM clause that does not contain a subquery is encountered. If the FROM clause does not contain a subquery, the rest of the query will continue to be processed.

[0064] S32: If the subquery in the FROM clause is a join query SQLJoinTableSource, the join query statement is split, processed recursively, and its syntax tree is parsed;

[0065] Specifically, SQLJoinTableSource is a specific subquery type that represents a query source that contains a JOIN operation. The JOIN operation is used to combine data from two or more tables. When encountering a subquery of the SQLJoinTableSource type, the entire subquery is not directly processed as a whole. The associated query statement is split into smaller parts so that they can be processed one by one. Splitting involves separating the JOIN operation and its related tables, conditions, etc. for separate analysis or processing. After the associated query statement is split, each split part is processed recursively. During the recursive processing, each split part is parsed by the syntax tree. By parsing the syntax tree, you can understand and execute each part of the query, including table names, column names, conditional expressions, JOIN operations, etc. With this setting, you can improve and optimize the database's ability to handle complex SQL queries, especially for queries involving operations such as UNION and JOIN. Meet data integration needs in different scenarios, such as adapting to reports and large-screen display scenarios.

[0066] S33: If the subquery in the FROM clause is a union query SQLUnionQueryTableSource, the union query is processed recursively to parse its syntax tree;

[0067] Specifically, when processing the FROM clause, if it is detected that the subquery is of the SQLUnionQueryTableSource type, that is, a union query, then the union query is processed recursively. During the recursive processing, the syntax tree is parsed for each SELECT statement. By parsing the syntax tree, each part of the query can be understood and executed, thereby generating the correct query result.

[0068] S34: Get the query WHERE clause of SelectQueryBlock, and add the tenant identifier to the WHERE clause according to the table name list to ensure that the query returns only the data of the current tenant.

[0069] Specifically, the WHERE clause is used to specify the conditions of the query to filter out rows that meet specific conditions. Once the table to which the tenant identifier needs to be added is determined, additional conditions are added to the WHERE clause based on the tenant identifier field and the current tenant's information. The tenant identifier field is a column defined in a table that stores the tenant ID or tenant name associated with the row of data. By adding these conditions in the WHERE clause, you can ensure that the query results only contain data rows associated with the current tenant. By modifying the WHERE clause and adding the tenant identifier as a query condition, you can ensure that the query result set only contains data that the current tenant has access to. This is critical to maintaining data isolation and security in multi-tenant applications because it prevents data leakage between tenants.

[0070] Please refer to Figure 3 In step 4, the query type represented by the FROM clause is determined. According to the query type, the specific steps for obtaining all table names involved in the FROM clause include:

[0071] S41: If the FROM clause represents an associated query SQLJoinTableSource, the left and right clauses of the associated query are processed respectively, and the left and right clauses are processed recursively to collect all related table names;

[0072] Specifically, SQLJoinTableSource is a specific type that represents a query source that contains a JOIN operation. The JOIN operation is used to combine data from two or more tables. When processing the FROM clause, if it is detected that it represents a join query (that is, it contains a JOIN operation), the left and right clauses of the join query will be processed separately: the "left and right clauses" refer to the left and right parts of the join query, that is, all the tables or subqueries involved. These tables or subqueries will be processed separately to collect their information, such as table names, column names, etc. The process of recursively processing the left and right clauses. Collect all the table names involved. These table names are very important for subsequent query processing and determine which data needs to be retrieved and processed.

[0073] S42: If the FROM clause represents a union query SQLUnionQuery, the left and right clauses of the union query are processed respectively, and the left and right clauses are processed recursively to collect all related table names;

[0074] Specifically, in the same way, when processing a joint query, collect all the table names involved

[0075] S43: If the FROM clause represents a single-table query, the target table targeted by the query is directly obtained and the table name is extracted therefrom.

[0076] Specifically, when processing the FROM clause and detecting that it is a single-table query, it directly identifies and obtains the table specified by the query. From this table, the table name is extracted. The table name is a unique identifier for the table, which is used to locate a specific table in the database.

[0077] Please refer to Figure 4 The present application provides a system based on druid to prevent tenants from crossing the boundary when a user customizes SQL query, which is characterized by including:

[0078] Data acquisition module 1, used to acquire a user's custom edited SQL query statement and database connection information Connection, wherein the custom edited SQL query statement includes a query statement SQLSelectStatement;

[0079] The query statement parsing module 2 is used to parse the query statement SQLSelectStatement; including dividing the query statement SQLSelectStatement into a UNION query statement and other query statements. For the union query statement, the union query statement is first processed into a union query object of UnionQuery; the left query and the right query part of the union query statement are obtained, and it is determined whether the left query and the right query part are the union query UnionQuery respectively. If so, they are processed recursively; otherwise, the recursive parsing syntax tree is directly executed to parse the tenant's table name and tenant information and save them;

[0080] Subquery processing module 3, used to process the table name and tenant information of the tenant involved in the query statement; including converting the parsed query statement into a SelectQueryBlock object; calling the getSelectList method of SelectQueryBlock to obtain the query field, traversing the query field, and if the query field has a subquery, recursively processing the subquery; the subquery includes the subquery of the from clause and the subquery of the WHERE clause;

[0081] A FROM clause processing module 4 is used to receive the recursively processed FROM clause, determine the query type represented by the FROM clause, obtain all table names involved in the FROM clause according to the query type, and store them in tableNameList;

[0082] List information acquisition module 5 is used to obtain the current tenant information, take the table name as the processing object, connect to the corresponding database according to the database connection information Connection, perform a query operation on each table name to obtain the field information of the table; in the obtained field information, check whether there is a specific tenant identification field, if the tenant identification field is found in the table field, mark the table, and obtain the table name, tenant identification field list information;

[0083] The WHERE clause processing module 6 is used to process the WHERE clause and parse the SQL syntax tree; it includes receiving the WHERE clause, checking whether the WHERE clause contains a subquery, if the WHERE clause contains a subquery, extracting the subquery part, and the extracted subquery is regarded as an independent query and sent to the query statement parsing module 2 for processing; if the WHERE clause does not contain a subquery, reconstructing the WHERE clause according to the information in the list information acquisition module 5, and the reconstruction method includes checking whether the table name of the WHERE clause exists in the list information, if so, adding tenant conditions in the WHERE clause, these tenant conditions match the current tenant information, thereby limiting the query results to only include data that the current tenant has access to; if the original WHERE clause is empty, creating a new WHERE clause, and the created WHERE clause contains the table name, tenant identification field, and current tenant information in the list information acquisition module 5 as filtering conditions.

[0084] Specifically, the specific implementation of the system please refer to the specific implementation of the above method, and no further details will be given here.

[0085] Please refer to Figure 5 The present application provides an electronic device, characterized in that it includes:

[0086] A memory 8, for storing one or more programs;

[0087] Processor 7; processor 7 and memory 8 are connected via a communication interface 9;

[0088] When the one or more programs are executed by the processor 7, all or part of the above method is implemented.

[0089] The present application provides a computer-readable storage medium having a computer program stored thereon, wherein the computer program implements all or part of the above-mentioned method when executed by a processor.

[0090] It will be apparent to those skilled in the art that the present application is not limited to the details of the exemplary embodiments described above, and that the present application can be implemented in other specific forms without departing from the spirit or essential features of the present application. Therefore, the embodiments should be considered exemplary and non-limiting in all respects, and the scope of the present application is defined by the appended claims rather than the above description, and it is intended that all changes falling within the meaning and scope of the equivalent elements of the claims be included in the present application. Any reference numeral in a claim should not be considered as limiting the claim to which it relates.

Claims

1. A method based on druid to prevent tenants from crossing boundaries when a user customizes SQL query, characterized in that: include: Step 1, data acquisition; including acquiring the user's custom edited SQL query statement and database connection information Connection, the custom edited SQL query statement including the query statement SQLSelectStatement; Step 2: parsing the query statement SQLSelectStatement; including dividing the query statement SQLSelectStatement into a UNION query statement and other query statements. For the union query statement, firstly, processing the union query statement into a union query object of UnionQuery; Get the left query and right query parts of the union query statement, and determine whether the left query and right query parts are union queries. If so, process them recursively. Otherwise, directly execute recursive parsing of the syntax tree, parse the tenant's table name and tenant information, and save them; Step 3: Process the table name and tenant information of the tenant involved in the query statement; including converting the parsed query statement into a SelectQueryBlock object; calling the getSelectList method of SelectQueryBlock to obtain the query field, traversing the query field, and if the query field has a subquery, recursively processing the subquery; the subquery includes the subquery of the from clause and the subquery of the WHERE clause; Step 4: Receive the recursively processed FROM clause from step 3, determine the query type represented by the FROM clause, obtain all table names involved in the FROM clause according to the query type, and store them in tableNameList; Step 5: Get the current tenant information, use the table name obtained in step 4 as the processing object, connect to the corresponding database according to the database connection information Connection, and perform a query operation on each table name to obtain the field information of the table; in the obtained field information, check whether there is a specific tenant identification field. If the tenant identification field is found in the table field, mark the table and obtain the list information of the table name and tenant identification field; Step 6: Process the WHERE clause and parse the SQL syntax tree; The method includes receiving the WHERE clause from step three, checking whether the WHERE clause contains a subquery, if the WHERE clause contains a subquery, extracting the subquery part, treating the extracted subquery as an independent query, and sending it to step two for processing; if the WHERE clause does not contain a subquery, reconstructing the WHERE clause according to the information in step five, and the reconstruction method includes checking whether the table name of the WHERE clause exists in the list information in step five, if so, adding tenant conditions in the WHERE clause, and these tenant conditions match the current tenant information obtained in step five, thereby limiting the query results to only include data that the current tenant has access to; if the original WHERE clause is empty, creating a new WHERE clause, and the created WHERE clause includes the table name, tenant identification field, and current tenant information in step five as filtering conditions.

2. According to claim 1, a method for preventing tenants from crossing boundaries when a user customizes SQL query based on Druid is characterized in that: In step 3, if the query field has a subquery, the specific steps of recursively processing the subquery include: Call the getFrom method of SelectQueryBlock to obtain the FROM statement of the query, and determine whether the FROM clause has a subquery. If so, recursively process the subquery in the FROM clause, otherwise continue to process the rest of the query; If the subquery in the FROM clause is a join query SQLJoinTableSource, the join query statement is split, processed recursively, and its syntax tree is parsed; If the subquery in the FROM clause is a union query SQLUnionQueryTableSource, the union query is processed recursively and its syntax tree is parsed; Get the query WHERE clause of SelectQueryBlock, and add the tenant ID to the WHERE clause according to the table name list to ensure that the query only returns data of the current tenant.

3. According to claim 1, a method for preventing tenants from crossing boundaries when a user customizes SQL query based on Druid is characterized in that: In step 4, the query type represented by the FROM clause is determined. According to the query type, the specific steps for obtaining all table names involved in the FROM clause include: If the FROM clause represents a join query SQLJoinTableSource, the left and right clauses of the join query are processed separately, and the left and right clauses are processed recursively to collect all related table names; If the FROM clause represents a SQLUnionQuery, the left and right clauses of the union query are processed separately, and the left and right clauses are processed recursively to collect all related table names; If the FROM clause indicates a single-table query, the target table for this query is directly obtained and the table name is extracted from it.

4. A system based on druid to prevent tenants from crossing boundaries when users customize SQL queries, characterized in that: include: A data acquisition module is used to acquire a user's custom edited SQL query statement and database connection information Connection, wherein the custom edited SQL query statement includes a query statement SQLSelectStatement; The query statement parsing module is used to parse the query statement SQLSelectStatement; including dividing the query statement SQLSelectStatement into a UNION query statement and other query statements. For the union query statement, the union query statement is first processed into a union query object of UnionQuery; Get the left query and right query parts of the union query statement, and determine whether the left query and right query parts are union queries. If so, process them recursively. Otherwise, directly execute recursive parsing of the syntax tree, parse the tenant's table name and tenant information, and save them; The subquery processing module is used to process the table name and tenant information of the tenant involved in the query statement; including converting the parsed query statement into a SelectQueryBlock object; calling the getSelectList method of SelectQueryBlock to obtain the query field, traversing the query field, and recursively processing the subquery if the query field has a subquery; the subquery includes the subquery of the from clause and the subquery of the WHERE clause; The FROM clause processing module is used to receive the recursively processed FROM clause, determine the query type represented by the FROM clause, obtain all table names involved in the FROM clause according to the query type, and store them in tableNameList; The list information acquisition module is used to obtain the current tenant information, take the table name as the processing object, connect to the corresponding database according to the database connection information Connection, and perform a query operation on each table name to obtain the field information of the table; in the obtained field information, check whether there is a specific tenant identification field. If the tenant identification field is found in the table field, the table is marked and the list information of the table name and tenant identification field is obtained; WHERE clause processing module, used to process WHERE clause and parse SQL syntax tree; It includes receiving a WHERE clause, checking whether the WHERE clause contains a subquery, and if the WHERE clause contains a subquery, extracting the subquery part, and the extracted subquery is regarded as an independent query and sent to the query statement parsing module for processing; If the WHERE clause does not contain a subquery, the WHERE clause is reconstructed according to the information in the list information acquisition module. The reconstruction method includes checking whether the table name of the WHERE clause exists in the list information. If so, tenant conditions are added to the WHERE clause. These tenant conditions match the current tenant information, thereby limiting the query results to only include data that the current tenant has access to. If the original WHERE clause is empty, a new WHERE clause is created. The created WHERE clause contains the table name in the list information acquisition module, the tenant identification field, and the current tenant information as filtering conditions.

5. An electronic device, characterized in that: include: A memory for storing one or more programs; processor; When the one or more programs are executed by the processor, the method according to any one of claims 1 to 3 is implemented.

6. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the method according to any one of claims 1 to 3 is implemented.

Images