Data encryption query method and apparatus, device, and storage medium

Abstract

The present application relates to a data encryption query method and apparatus, a device, and a storage medium. The method comprises: parsing an SQL request, and on the basis of the parsed SQL request, obtaining a column field and an information table which correspond to encrypted data carried in the SQL request (S201); performing SQL rewriting on the column field and the information table to obtain a rewritten SQL request (S202); performing a sharding operation on the rewritten SQL request to obtain target shards, and sending the sharded SQL request to a plurality of storage nodes corresponding to the target shards (S203); on the basis of the rewritten SQL request, acquiring service information stored in the corresponding storage nodes, and on the basis of a metadata table and a MySQL protocol packet, determining encrypted data among service data, and decrypting the encrypted data to obtain decrypted data corresponding to the encrypted data (S204); and encapsulating the decrypted data and unencrypted data into response data and returning same (S205).

Description

Data encryption query methods, devices, equipment and storage media

[0001] Related applications

[0002] This application claims priority to Chinese patent application filed on November 28, 2024, with application number 2024117217052, entitled "Data Encryption Query Method, Apparatus, Device and Storage Medium", the entire contents of which are incorporated herein by reference. Technical Field

[0003] This application relates to the field of data processing technology, and in particular to a data encryption query method, apparatus, device, and storage medium. Background Technology

[0004] With the rapid development of information technology, the growth rate of business data is increasing exponentially. Traditional single-database, single-table architectures face performance bottlenecks, poor scalability, and maintenance difficulties when dealing with massive amounts of data. To address these challenges, the industry has gradually reached a consensus on using database middleware to achieve database and table splitting, also known as sharding or database partitioning. This technology splits data originally stored in one database into multiple parts according to certain rules, storing them in different databases or tables, thereby achieving horizontal scaling of data and improving the system's concurrent processing capabilities and query efficiency.

[0005] Database middleware plays a crucial role in database sharding. Located between the application layer and the database layer, it is responsible for data routing, aggregation, sharding, and failover. Through middleware, the application layer can operate on multiple sharded databases, simplifying application development and improving system scalability and flexibility.

[0006] However, since data is scattered across multiple databases or tables after database-table splitting, the tablespace encryption methods used in related technologies, which target the entire database or table, cannot perform fine-grained encryption on the split data, resulting in low security for sensitive data during transmission and storage. Summary of the Invention

[0007] According to various embodiments of this application, a data encryption query method, apparatus, device, and storage medium are provided.

[0008] Firstly, this application provides a data encryption query method, the method comprising:

[0009] Receive an SQL request, parse the SQL request, match the parsed SQL request with the configured metadata table, and obtain the column fields and information table corresponding to the encrypted data carried in the SQL request;

[0010] Based on the metadata table, the column fields and information table are rewritten using SQL. The encryption algorithm and encryption algorithm parameters in the metadata table are embedded into the SQL request to obtain the rewritten SQL request.

[0011] The rewritten SQL request is sharded to obtain the target shard, and the sharded SQL request is sent to multiple storage nodes corresponding to the target shard; the multiple storage nodes distribute business information and its corresponding business data, and the business data includes encrypted data and unencrypted data.

[0012] The modified SQL request retrieves the business information stored in the corresponding storage node, and based on the metadata table and MySQL protocol packet, determines the encrypted data in the business data, decrypts the encrypted data, and obtains the decrypted data corresponding to the encrypted data; and

[0013] The decrypted data and the unencrypted data are encapsulated into response data and returned.

[0014] In one embodiment, before receiving the SQL request, parsing the SQL request, and matching the parsed SQL request with the configured metadata table to obtain the column fields and information table corresponding to the encrypted data carried in the SQL request, the method further includes:

[0015] Configure a metadata table and store the metadata table in middleware; the metadata table includes encryption fields configured based on encrypted data and their corresponding encryption algorithms and encryption algorithm parameters.

[0016] In one embodiment, receiving the SQL request, parsing the SQL request, and matching the parsed SQL request with a configured metadata table to obtain the column fields and information table corresponding to the encrypted data carried in the SQL request includes:

[0017] Receive an SQL request, obtain the SQL statement in the SQL request, parse the SQL statement, and obtain the abstract syntax tree corresponding to the SQL statement;

[0018] Traverse each node of the abstract syntax tree, determine the column name nodes in each node, and match the column name nodes with the encrypted fields to obtain the column fields corresponding to the encrypted data; and

[0019] Based on the matched column fields, determine the information table to which the SQL request corresponding to the column field belongs, push the information table onto the stack using a stack structure, obtain the information table name of the SQL statement corresponding to the column field from the top of the stack structure, and determine the column field and the information table to which it belongs.

[0020] In one embodiment, the type of the SQL request includes at least one of a query request, an insert request, an update request, and a delete request;

[0021] The step of rewriting the SQL based on the metadata table for the column fields and the information table, embedding the encryption algorithm and encryption algorithm parameters from the metadata table into the SQL request, to obtain the rewritten SQL request includes:

[0022] Obtain the metadata table, and based on the encrypted fields in the metadata table that match the column fields, determine the encryption algorithm and encryption algorithm parameters for the column fields; and

[0023] Based on the encryption algorithm, the encryption algorithm parameters, and the type of the SQL request, at least one of the following is rewritten: query request, insert request, update request, and delete request, to obtain the rewritten SQL request; the rewritten SQL request embeds the encryption algorithm and the encryption algorithm parameters.

[0024] In one embodiment, the middleware also stores sharding configuration information;

[0025] The step of performing a sharding operation on the rewritten SQL request to obtain a target shard, and sending the sharded SQL request to multiple storage nodes corresponding to the target shard, includes:

[0026] Obtain sharding configuration information and perform sharding initialization; the sharding configuration information includes sharding keys and sharding algorithms;

[0027] Based on the parsed SQL request, extract the sharding key from the SQL request;

[0028] The sharding key is sharded based on the sharding algorithm to obtain sharded data; and a target shard matching the sharded data is determined based on the sharded data.

[0029] The rewritten SQL request is sent to multiple storage nodes corresponding to the target shard.

[0030] In one embodiment, sending the rewritten SQL request to multiple storage nodes corresponding to the target shard includes:

[0031] Initialize all storage nodes;

[0032] The storage nodes are selected as master nodes, and the other storage nodes besides the master nodes are determined as slave nodes.

[0033] When the SQL request is sent to the target shard, the master node is controlled to perform data encapsulation to encapsulate the SQL request into a log entry and replicate it to the master node. Based on the master node's replication request, each slave node is controlled to replicate the log entry to its corresponding slave node.

[0034] When the number of slave nodes replicating the log entry meets the preset conditions, the master node is controlled to return the replication result so that the rewritten SQL request can be sent to each storage node.

[0035] In one embodiment, the step of obtaining the business information stored in the corresponding storage node according to the rewritten SQL request, determining the encrypted data in the business data based on the metadata table and MySQL protocol packets, and decrypting the encrypted data to obtain decrypted data corresponding to the encrypted data includes:

[0036] Based on the target shard and the log entries in each storage node, query the business data stored in the storage node, obtain and parse the column part of the MySQL protocol packet, and obtain the column information of each column in the business data;

[0037] The fields of the column information are matched with the encrypted fields in the metadata table, and the fields of the column information that match successfully are determined as the decrypted fields;

[0038] The encryption algorithm and encryption algorithm parameters of the decryption field are determined based on the encryption field that successfully matches the decryption field, and the corresponding decryption algorithm is determined based on the encryption algorithm and encryption algorithm parameters; and

[0039] The decryption field is decrypted based on the decryption algorithm to obtain decrypted data.

[0040] Secondly, this application also provides a data encryption query device, the device comprising:

[0041] The request parsing module is used to receive SQL requests, parse the SQL requests, match the parsed SQL requests with the configured metadata table, and obtain the column fields and information table corresponding to the encrypted data carried in the SQL requests.

[0042] The request rewrite module is used to rewrite the column fields and information table based on the metadata table, embedding the encryption algorithm and encryption algorithm parameters in the metadata table into the SQL request to obtain the rewritten SQL request;

[0043] The sharded storage module is used to shard the rewritten SQL request to obtain the target shard, and send the sharded SQL request to multiple storage nodes corresponding to the target shard; the multiple storage nodes distribute and store business information and its corresponding business data, the business data including encrypted data and unencrypted data;

[0044] The data query module is used to obtain business information stored in the corresponding storage node according to the rewritten SQL request, and to determine the encrypted data in the business data based on the metadata table and MySQL protocol packets, decrypt the encrypted data, and obtain decrypted data corresponding to the encrypted data; and

[0045] The data encapsulation module is used to encapsulate the decrypted data and the unencrypted data into response data and return it.

[0046] In one embodiment, the device further includes:

[0047] The data configuration module is used to configure the metadata table and store the metadata table in a middleware; the metadata table includes encrypted fields based on encrypted data configuration and their corresponding encryption algorithms and encryption algorithm parameters.

[0048] In one embodiment, the request parsing module is further configured to:

[0049] Receive an SQL request, obtain the SQL statement in the SQL request, parse the SQL statement, and obtain the abstract syntax tree corresponding to the SQL statement;

[0050] Traverse each node of the abstract syntax tree, determine the column name nodes in each node, and match the column name nodes with the encrypted fields to obtain the column fields corresponding to the encrypted data; and

[0051] Based on the matched column fields, determine the information table to which the SQL request corresponding to the column field belongs, push the information table onto the stack using a stack structure, obtain the information table name of the SQL statement corresponding to the column field from the top of the stack structure, and determine the column field and the information table to which it belongs.

[0052] In one embodiment, the type of the SQL request includes at least one of a query request, an insert request, an update request, and a delete request; the request rewriting module is further configured to:

[0053] Obtain the metadata table, and based on the encrypted fields in the metadata table that match the column fields, determine the encryption algorithm and encryption algorithm parameters for the column fields; and

[0054] Based on the encryption algorithm, the encryption algorithm parameters, and the type of the SQL request, at least one of the following is rewritten: query request, insert request, update request, and delete request, to obtain the rewritten SQL request; the rewritten SQL request embeds the encryption algorithm and the encryption algorithm parameters.

[0055] In one embodiment, the middleware also stores sharding configuration information; the sharding storage module is further used for:

[0056] Obtain sharding configuration information and perform sharding initialization; the sharding configuration information includes sharding keys and sharding algorithms;

[0057] Based on the parsed SQL request, extract the sharding key from the SQL request;

[0058] The sharding key is sharded based on the sharding algorithm to obtain sharded data; and a target shard matching the sharded data is determined based on the sharded data.

[0059] The rewritten SQL request is sent to multiple storage nodes corresponding to the target shard.

[0060] Thirdly, this application also provides a computer device, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to implement the steps of the method described above.

[0061] Fourthly, this application also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the method described above.

[0062] Fifthly, this application also provides a computer program product, including a computer program that, when executed by a processor, implements the steps of the method described above.

[0063] Details of one or more embodiments of this application are set forth in the following drawings and description. Other features, objects, and advantages of this application will become apparent from the specification, drawings, and claims. Attached Figure Description

[0064] To more clearly illustrate the technical solutions in the embodiments of this application or the conventional technology, the drawings used in the description of the embodiments or the conventional technology will be briefly introduced below. Obviously, the drawings described below are only embodiments of this application. For those skilled in the art, other drawings can be obtained based on the disclosed drawings without creative effort.

[0065] Figure 1 shows the application environment of the data encryption query method in some embodiments;

[0066] Figure 2 is a flowchart illustrating the data encryption query method in some embodiments;

[0067] Figure 3 is a flowchart illustrating the process of obtaining column fields and information tables in some embodiments;

[0068] Figure 4 is a schematic diagram of the process of sending SQL requests to storage nodes in some embodiments;

[0069] Figure 5 is a schematic diagram of the data decryption process in some embodiments;

[0070] Figure 6 is a structural block diagram of the data encryption query device in some embodiments;

[0071] Figure 7 is an internal structure diagram of a computer device in some embodiments. Detailed Implementation

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

[0073] Common massive data processing relying solely on database sharding and table partitioning mechanisms is highly susceptible to distributed asynchronous operations and latency issues due to frequent data manipulation. This leads to data inconsistencies, cross-shard queries, and performance bottlenecks in distributed scenarios. Furthermore, server failures, partial downtime of storage nodes, or cluster network failures can result in lost business query requests, severely impacting data retrieval within the distributed database system and consequently affecting business operations. Additionally, database sharding and table partitioning often neglects the encryption of sensitive data; achieving transparent data encryption is a pressing issue that needs to be addressed.

[0074] To circumvent the aforementioned data processing and querying issues in distributed databases and better meet the performance and security requirements for business data queries in distributed database systems, this application provides a data encryption query method that can achieve transparent data encryption and query optimization.

[0075] The data encryption query method provided in this application embodiment can be applied to the application environment shown in Figure 1. Terminal 102 communicates with server 104 via a network. A data storage system can store the data that server 104 needs to process. The data storage system can be integrated on server 104 or placed on a cloud or other network server. Server 104 parses the received SQL (Structured Query Language) request, and based on the parsed SQL request, obtains the fields corresponding to the encrypted data carried in the SQL request and the information table to which those fields belong. By rewriting the fields and information table of the encrypted data, the rewritten SQL request is sent to each storage node. The storage nodes distribute the corresponding business data, and the business data queried by the SQL request is decrypted using the decryption method corresponding to the encryption method of the encrypted data, obtaining the corresponding decrypted data and returning it. The entire encryption and decryption process is imperceptible to the user. Terminal 102 can be, but is not limited to, various personal computers, laptops, smartphones, tablets, IoT devices, and portable wearable devices. IoT devices can include smart speakers, smart TVs, smart air conditioners, smart in-vehicle devices, projection devices, etc. Portable wearable devices can include smartwatches, smart bracelets, etc. Server 104 can be a standalone physical server, a server cluster or distributed system consisting of multiple physical servers, or a cloud server providing cloud computing services.

[0076] In an exemplary embodiment, as shown in FIG2, a data encryption query method is provided. Taking the application of this method to the server in FIG1 as an example, the method includes the following steps S201 to S205. Wherein:

[0077] Step S201: Receive the SQL request, parse the SQL request, match the parsed SQL request with the configured metadata table, and obtain the column fields and information table corresponding to the encrypted data carried in the SQL request.

[0078] An SQL request is an instruction sent by a user or application to a server's database to perform a specific operation. Specific operations corresponding to SQL requests typically include querying (SELECT), inserting (INSERT), updating (UPDATE), and deleting (DELETE).

[0079] The metadata table refers to an encryption configuration information table that is pre-configured based on encrypted data and stored in a middleware. It is used to indicate which data is encrypted and its corresponding encryption algorithm, encryption algorithm parameters, etc.

[0080] Encrypted data refers to sensitive data involving users or applications. This data can be the user's private data or data that the user considers important and needs to be encrypted.

[0081] In this context, an information table is a basic storage unit in the database, containing relevant data rows and columns. Each information table has a specific structure that defines the data types that can be stored and the relationships between data. In SQL requests, the information table typically refers to the database table from which the user wants to retrieve data. In this embodiment, the information table specifically refers to the information table where encrypted data is stored.

[0082] In this context, a column field refers to a column name or field defined in an information table. Each column in the information table represents an attribute or characteristic of a record. For example, an information table storing user information might contain column fields such as "username," "phone number," and "address." In an SQL request, a column field typically refers to the specific information the user wants to retrieve from a particular information table. In this embodiment, the column field specifically refers to the column name corresponding to encrypted data. For instance, if a user's phone number is encrypted data in an information table, then the column name "phone number" corresponding to that number is the column field in this embodiment.

[0083] For example, when a user or an application initiates a query, insert, update, or delete operation on the client, the client will convert the operation into a corresponding SQL request. The server responds to and receives the SQL request, and parses the SQL request to obtain the parsed SQL request. Subsequently, the server matches the parsed SQL request with a pre-configured metadata table to obtain the column fields and information table corresponding to the encrypted data carried in the SQL request.

[0084] The client can exist in the form of a terminal to sense and identify the corresponding requests made by the user or application, and convert the requests into SQL requests and send them to the server.

[0085] Step S202: Based on the metadata table, rewrite the column fields and information table using SQL, embedding the encryption algorithm and encryption algorithm parameters from the metadata table into the SQL request to obtain the rewritten SQL request.

[0086] SQL rewriting refers to modifying or transforming the statements of existing SQL requests based on the metadata table, so as to embed the encryption algorithm and encryption algorithm parameters in the metadata table into the rewritten SQL request, thereby encrypting the column fields and their corresponding data in the SQL request.

[0087] Here, encryption algorithm refers to the algorithm used to encrypt data in column fields. The encryption algorithms used in this embodiment include, but are not limited to, symmetric encryption algorithms and asymmetric encryption algorithms. Symmetric encryption algorithms can be such as AES (Advanced Encryption Standard) and DES (Data Encryption Standard), while asymmetric encryption algorithms can be such as ECC (Elliptic Curve Cryptography).

[0088] The encryption algorithm parameters refer to the parameters required to perform encryption operations on the data. Depending on the encryption algorithm, different encryption algorithm parameters may be required. For example, for the AES algorithm, the encryption algorithm parameters may include, but are not limited to, parameters such as key (encryption_key), encryption mode, and padding method.

[0089] For example, after obtaining the column fields and information table associated with the data to be encrypted, the server retrieves the encryption algorithm and encryption algorithm parameters associated with the encrypted data from the metadata table, and rewrites the SQL request according to the encryption algorithm and encryption algorithm parameters to embed the encryption algorithm and encryption algorithm parameters into the SQL request, thereby encrypting the data. The entire encryption process is imperceptible to the user, thus achieving transparent encryption of the data.

[0090] Step S203: The rewritten SQL request is sharded to obtain the target shard, and the sharded SQL request is sent to multiple storage nodes corresponding to the target shard.

[0091] Sharding refers to the process of dividing a rewritten SQL request into multiple shards in distributed storage or a distributed database. A target shard is a small data fragment obtained after sharding the SQL request. Each shard has the same data structure or format but contains different data content. In distributed storage, each target shard is stored on a different storage node to achieve distributed storage and management of the data (i.e., distributed storage).

[0092] In a distributed storage system, a storage node refers to a computer or server responsible for storing and managing data. Multiple storage nodes distribute business information and its corresponding business data, which includes both encrypted and unencrypted data. This means that by using target shards, different storage modules can be accessed, allowing the retrieval of different business information and its corresponding business data stored within each storage node.

[0093] Here, business information refers to the representation stored in the storage node used to indicate the category of business data or to distinguish different types of business data. It can be an identifier such as a field, ID, or string. Business data refers to the data corresponding to the business information. For example, in phone_number = 'xxx', phone_number is the business information, and 'xxx' is the business data. It is understood that business information can include encrypted information that requires encryption (i.e., the "column fields" mentioned above), as well as regular information that does not require encryption. Correspondingly, business data can also include encrypted and unencrypted data.

[0094] For example, the server performs a sharding operation on the rewritten SQL request to obtain the target shards corresponding to the various types of data that need to be queried in the SQL request. Each target shard can correspond to a field to query different data. Subsequently, the server sends the sharded SQL request to multiple storage nodes corresponding to the target shards to access the corresponding storage nodes.

[0095] Step S204: Obtain the business information stored in the corresponding storage node according to the rewritten SQL request, and determine the encrypted data in the business data based on the metadata table and MySQL protocol packet. Decrypt the encrypted data to obtain the decrypted data corresponding to the encrypted data.

[0096] Among them, the MySQL protocol packet, also known as a message, refers to the data packet used by the client and server to communicate in the MySQL database (i.e., relational database management system). It is used to define the data exchange format and rules between the client and the server to ensure the correct execution of communication.

[0097] Decrypted data refers to the data obtained after decrypting the encrypted data stored in the storage node. Usually, the decrypted data and the encrypted data have the same data content (value).

[0098] For example, the server retrieves the business information stored in the corresponding storage node based on the rewritten SQL request, and determines the encrypted data in the business data based on the encrypted fields in the metadata table and the MySQL protocol packet. The server then decrypts the encrypted data to obtain the decrypted data corresponding to the encrypted data.

[0099] Step S205: Encapsulate the decrypted data and the unencrypted data into response data and return it.

[0100] The response data refers to the data that the user or application needs to query; typically, the response data is encapsulated into a MySQL protocol packet and returned.

[0101] For example, the server integrates the decrypted data and other unencrypted business data into response data, and then encapsulates the response data into a MySQL protocol packet and returns it to the client.

[0102] In the aforementioned data encryption query method, when a user or an application initiates operations such as querying, inserting, updating, or deleting on the client side, the client converts the operation into a corresponding SQL request. The server receives the SQL request, parses it, and obtains the column fields and information tables corresponding to the encrypted data carried in the SQL request based on the parsed SQL request and the configured metadata table. Subsequently, the SQL is rewritten by embedding the encryption algorithm and encryption algorithm parameters from the metadata table into the SQL request, resulting in a rewritten SQL request. The rewritten SQL request is then fragmented to obtain the target fragment, and the fragmented SQL request is sent to the server. The target shard corresponds to multiple storage nodes. Finally, based on the rewritten SQL request, the business information stored in the corresponding storage node is obtained. Based on the metadata table and MySQL protocol packets, the encrypted data in the business data is determined, the encrypted data is decrypted to obtain the decrypted data corresponding to the encrypted data, and the decrypted data and unencrypted data are encapsulated into response data and returned. In this way, while ensuring the consistency and completeness of the business data queried in the distributed database, transparent data encryption and query process are achieved. The entire process is imperceptible to the user. Furthermore, by sharding the rewritten SQL request, the storage node corresponding to the target shard can be quickly located to achieve fast querying of massive amounts of data.

[0103] In one embodiment, before receiving the SQL request, parsing the SQL request, and matching the parsed SQL request with the configured metadata table to obtain the column fields and information table corresponding to the encrypted data carried in the SQL request in step S201, the following steps are also included:

[0104] Configure the metadata table and store it in middleware.

[0105] The metadata table includes encryption fields configured based on encrypted data, along with their corresponding encryption algorithms and parameters. Encryption fields are identifiers used to indicate the type or attribute of encrypted data, such as phone_number.

[0106] Middleware refers to software that provides a connection between system software and application software, facilitating communication between various software components. It enables applications to operate transparently on the database. The data in the database is stored in different local databases (storage nodes). Logically, these data are a unified whole, but physically they are stored on different storage nodes. In this way, the middleware acts as a proxy to distribute the data.

[0107] For example, the server, based on sensitive data determined by the user or system (i.e., data that needs to be encrypted), configures the encryption fields, encryption algorithm, and encryption algorithm parameters for that data, forming a metadata table, which is then stored in the middleware. In this embodiment, the middleware can be implemented using Zookeeper.

[0108] In an optional embodiment, the metadata table may also include permission assignments for middleware users, i.e., which middleware can store or access which data, to further improve the efficiency of data encryption, decryption, and querying.

[0109] In this embodiment, by configuring the metadata table, the encrypted data can be customized, thereby improving the flexibility of the data encryption process.

[0110] In one embodiment, as shown in FIG3, obtaining the column fields and information table in step S201 includes the following steps S301 to S303. Wherein:

[0111] Step S301: Receive the SQL request, obtain the SQL statement in the SQL request, parse the SQL statement, and obtain the abstract syntax tree corresponding to the SQL statement.

[0112] In SQL, a statement is a sentence used to express an SQL request. An SQL statement consists of a series of keywords, identifiers, operators, and other parameters organized according to SQL syntax rules. For example, `SELECT * FROM user_info WHERE phone_number = 'xxx';` where `SELECT` is the operator, meaning "query," i.e., a query request; `FROM` is the keyword used to specify the data source to be queried; `WHERE` is the condition used to filter data and retrieve specific information; `user_info` is the name of the information table; `phone_number` is the column field, meaning `user_info` and `phone_number` are identifiers; and `'xxx'` is the value of `phone_number`, i.e., the data to be encrypted.

[0113] An Abstract Syntax Tree (AST) is an intermediate representation of source code generated after lexical and syntactic analysis. It represents the syntactic structure of a program in the form of a tree.

[0114] For example, when a user or application initiates an SQL request at the application layer, the server obtains the corresponding SQL statement based on the SQL request and sends the SQL statement to the middleware or query engine for processing, so as to parse the SQL statement and obtain the corresponding abstract syntax tree. The abstract syntax tree represents the various components in the SQL statement in a tree structure.

[0115] In an optional example, let's take querying the phone number 'phone_number' in the user information table 'user_info' as an example. The server, based on the received SQL request, converts it into an SQL statement, i.e., SELECT * FROM user_info WHERE phone_number = 'xxx', and sends the SQL statement to the database middleware. The Druid parser parses the SQL statement and converts it into an Abstract Syntax Tree (AST) to represent the various components of the SQL statement, such as the SELECT clause, FROM clause, WHERE clause, etc.

[0116] Step S302: Traverse each node of the abstract syntax tree, determine the column name node in each node, and match the column name node with the encrypted field to obtain the column field corresponding to the encrypted data.

[0117] In the abstract syntax tree (AST), a node refers to a syntax element in the source code, such as an expression, field, statement, or function. A column name node is a node that indicates the column name or column field; for example, in the AST generated by `SELECT * FROM user_info WHERE phone_number = 'xxx'`, `phone_number` is the column name node.

[0118] For example, the server creates a custom visitor class (i.e., the Visitor class) to implement the Visitor port provided by the Druid parser. Then, the server uses the custom Visitor to traverse the abstract syntax tree. During traversal, the Visitor visits each node of the abstract syntax tree and performs corresponding operations based on the node type. When the Visitor visits a column name node (such as phone_number in the example above), the Visitor records the column name node. Based on the column name node determined by the Visitor, the server matches the column name node with encrypted fields in the metadata table. If a match is successful, it indicates that the column name node is an encrypted column field, and the corresponding data is encrypted data. Thus, the column field corresponding to the encrypted data is determined.

[0119] Step S303: Based on the matched column fields, determine the information table to which the SQL request corresponding to the column field belongs, push the information table onto the stack using a stack structure, obtain the information table name of the SQL statement corresponding to the column field from the top of the stack structure, and determine the column field and the information table to which it belongs.

[0120] Among them, the stack structure refers to a data structure that follows the Last-In-First-Out (LIFO) principle, which means that the last element added to the stack will be the first element removed.

[0121] For example, during the AST traversal, the Visitor uses a stack data structure to maintain context information. When the Visitor encounters the FROM user_info clause, it pushes the table name user_info onto the stack. At this point, the top element of the stack is the name of the table currently being processed. When the Visitor accesses a column name node, it retrieves the current table name from the top of the stack and determines which information table the column name belongs to. After processing the relevant operations for that table, it pops the table name from the stack, thus obtaining the column fields and their respective information tables.

[0122] In this embodiment, by parsing the SQL statement corresponding to the SQL request, an abstract syntax tree corresponding to the SQL request can be obtained. This allows for accurate location of column name nodes during Visitor traversal. By matching the column name nodes with the encrypted fields, the column fields corresponding to the encrypted data in the SQL request can be quickly and accurately determined, facilitating the rewriting of the SQL request in subsequent steps. Simultaneously, during Visitor traversal, a stack structure is used to maintain context information, which overcomes the deficiency of missing context in the Druid parser and allows for the rapid acquisition of the information table name from the source at the top of the stack, thereby improving the processing efficiency of the encryption process.

[0123] In one embodiment, the type of SQL request includes at least one of query request, insert request, update request, and delete request.

[0124] The query request, or SELECT, is used to retrieve data from the database. SELECT can retrieve data from one or more fields in a table, or filter records that meet specified conditions.

[0125] An INSERT request is used to insert new rows of data into a database table. It can add one or more new records to a specified table.

[0126] An update request, or UPDATE, is used to modify existing records in a table. It can update field values ​​in records that meet specified conditions.

[0127] A delete request, or DELETE, is used to delete records from a table. It can delete records that meet specified conditions, or delete all records in the table (but retain the table structure).

[0128] In step S202, the column fields and information table are rewritten using SQL, resulting in the following rewritten SQL requests:

[0129] Obtain the metadata table. Based on the encrypted fields in the metadata table that match the column fields, determine the encryption algorithm and encryption algorithm parameters of the column fields. Based on the encryption algorithm, encryption algorithm parameters, and the type of SQL request, rewrite at least one of the following: query request, insert request, update request, and delete request, to obtain the rewritten SQL request.

[0130] The rewritten SQL request embeds an encryption algorithm and encryption algorithm parameters to ensure that when accessing data on the corresponding storage node, the corresponding decryption algorithm can be determined based on the embedded encryption algorithm and encryption algorithm parameters, thereby decrypting the encrypted data.

[0131] In an optional example, the server determines the encryption algorithm and encryption algorithm parameters of the column field based on the encryption field that matches the column field in the metadata table. Based on the encryption algorithm, encryption algorithm parameters, and the type of SQL request, the server rewrites at least one of the following requests: query request, insert request, update request, and delete request, to obtain the rewritten SQL request. During the rewriting process, different SQL requests are processed separately. The core of SELECT processing is the rewriting of the WHERE condition, while DELETE, INSERT, and UPDATE reuse the rewriting of their WHERE conditions.

[0132] For example, consider a SELECT request, denoted as SELECT * FROM user_info WHERE phone_number = 'xxx'. After recognizing the encrypted field to be queried as 'phone_number', if the 'phone_number' field is encrypted using the AES algorithm, then ENCRYPT('xxx', 'encryption_key') can be used. Therefore, the rewritten SELECT request can be represented as SELECT * FROM user_info WHERE phone_number = 'xxx'.<encrypted_value> ,<encrypted_value> It is the encrypted result, which is 'xxx' in the aforementioned SELECT request. Normally, the rewritten SELECT request will not directly contain the encrypted value. Instead, parameterized queries or prepared statements will be used to pass the encrypted value as a parameter to ensure secure data transmission.

[0133] For INSERT and UPDATE requests, let's rewrite them using the example of INSERT request `INSERT INTO user_info(phone_number,...) VALUES('xxx',...)` or UPDATE request `UPDATE user_info SET phone_number='xxx'WHERE...`. After identifying the encrypted field `phone_number` that needs to be inserted or updated, we also use the AES algorithm to encrypt the `phone_number` field. Based on this, the rewritten INSERT request can be represented as `INSERT INTO user_info(phone_number,...) VALUES(...)`.<encrypted_value> The rewritten UPDATE request can be represented as UPDATE user_info SET phone_number =<encrypted_value> WHERE....

[0134] For a DELETE request, taking the example of DELETE FROM user_info WHERE phone_number = 'xxx', we can rewrite it as follows: After identifying the encrypted field 'phone_number' that needs to be deleted, we also use the AES algorithm to encrypt the 'phone_number' field. Based on this, the rewritten DELETE request can be represented as DELETE FROM user_info WHERE phone_number =<encrypted_value> .

[0135] In this embodiment, based on the encryption algorithm, encryption algorithm parameters, and the type of SQL request, at least one of the query request, insert request, update request, and delete request is rewritten for the column field and information table to achieve encryption of the encrypted field. The encryption process is imperceptible to the user, and the user can use the encrypted data seamlessly without having to manually perform encryption and decryption operations. This is beneficial to improving the user experience, enhancing data security, and improving data processing efficiency.

[0136] In one embodiment, the middleware also stores sharding configuration information.

[0137] Sharding configuration information refers to the detailed settings of parameters such as sharding algorithms and sharding keys involved in performing sharding operations. Sharding configuration information includes sharding keys and sharding algorithms. The sharding key is a critical field used when splitting the database, determining how data is distributed across different shards. Sharding algorithms can include, but are not limited to, time-range-based sharding algorithms, hash sharding algorithms, modulo sharding algorithms, and list sharding algorithms.

[0138] In step S203, the rewritten SQL request is sharded to obtain the target shard, and the sharded SQL request is sent to multiple storage nodes corresponding to the target shard, including:

[0139] Obtain sharding configuration information and perform sharding initialization; extract the sharding key from the parsed SQL request; perform sharding calculation on the sharding key based on the sharding algorithm to obtain sharding data; determine the target shard that matches the sharding data based on the sharding data; and send the rewritten SQL request to multiple storage nodes corresponding to the target shard.

[0140] In this context, sharded data refers to the data corresponding to each clause in an SQL request being divided into multiple subsets according to a preset sharding algorithm. By comparing these subsets with each preset shard, the target shard corresponding to each subset can be determined.

[0141] For example, the server retrieves the sharding key and sharding algorithm stored in the middleware (i.e., Zookeeper). Taking the hash sharding algorithm as an example, the sharding key is set to user_id. The server initializes sharding according to the signed sharding configuration information. When the rewritten SQL request arrives, the server extracts the value of the sharding key user_id and performs a hash calculation on the value of user_id according to the hash sharding algorithm to obtain a hash value (i.e., sharded data). Subsequently, the server calculates which shard (i.e., target shard) the sharded data should be stored on based on the hash value and the preset number of shards (or sharding range). If there are 4 shards, the hash value can be used to determine which shard the data should be stored on by taking the modulo operation (%4). After the server determines which shard the sharded data should be stored or queried on, it will send the rewritten SQL request to the storage node corresponding to the target shard.

[0142] In this embodiment, the data is sharded using a sharding algorithm and stored on multiple storage nodes to achieve distributed consistency transformation of the storage nodes, thereby ensuring the concurrency and consistency of data processing and guaranteeing the consistency and synchronization of business data in massive data scenarios.

[0143] In one embodiment, step S203 employs the distributed consensus Raft algorithm to perform consistency transformation on the storage nodes, so as to send the rewritten SQL request to multiple storage nodes corresponding to the target shard. As shown in Figure 4, step S203 includes the following steps S401 to S404. Wherein:

[0144] Step S401: Initialize all storage nodes.

[0145] Step S402: Select the master node for the storage nodes and determine the other storage nodes besides the master node as slave nodes.

[0146] In Raft, the master node is the leader, responsible for accepting client requests, which typically include data writing and reading operations. It adds client requests as new log entries to its own log and replicates log entries to other slave nodes in the cluster by sending AppendEntries RPCs (Remote Procedure Calls) to maintain cluster consistency, ensuring that all slave nodes' logs are consistent with its own. Once the logs are synchronized to a majority of slave nodes, it instructs the slave nodes to commit the logs.

[0147] In Raft, master and slave nodes refer to the follower role. Under normal circumstances, slave nodes do not directly handle client requests but wait for instructions from the master node, accept and persist log entries synchronized from the master node, and commit the logs after the master node indicates that the logs can be committed. If a slave node does not receive a heartbeat message from the master node within a certain period of time, or receives an election request initiated by other nodes, the slave node will participate in the election of a new master node.

[0148] For example, after initialization, there is no master node among the storage nodes, that is, all slave nodes can be candidates for master node selection. The server controls each storage node to select the master node in order to determine the master node. Subsequently, the other storage nodes besides the master node are determined as slave nodes.

[0149] Step S403: When an SQL request is sent to the target shard, control the master node to perform data encapsulation to encapsulate the SQL request into a log entry and replicate it to the master node, and control each slave node to replicate the log entry to the corresponding slave node based on the replication request of the master node.

[0150] Log entries are the basic units for recording operation information on each storage node, and are used for subsequent consistency operations.

[0151] For example, when an SQL request is sent to the target shard, the server controls the master node to first receive the sharding result (i.e., the SQL request), including the shard ID, data range, and any other possible metadata. Subsequently, the server controls the master node to perform data encapsulation to package the SQL request into a log entry containing all the necessary information about the sharding result. The server then controls the master node to send the log entry to all slave nodes via an AppendEntries RPC request. This AppendEntries RPC request includes the master node's current log index, previous log index, and the log entry to be replicated. When each slave node receives the AppendEntries RPC request, the server controls each slave node to check if the log entry sent by the master node is consistent with its own log. If the log entries are consistent, or if the slave node's log is behind the master node's (i.e., there are gaps), the server controls the slave node to append the log entry to its own log and sends a response to the master node indicating that the log entry has been successfully received and appended.

[0152] Step S404: When the number of slave nodes for the replication log entries meets the preset conditions, control the master node to return the replication results so that the rewritten SQL request can be sent to each storage node.

[0153] For example, when the server detects that the master node has received a successful response from a majority (usually more than half) of the slave nodes, it can determine that the number of slave nodes replicating the log entry meets the preset conditions. Subsequently, the server controls the master node to mark the log entry as committed, so that the log entry and all previous log entries are persisted and consistent. Then, the server controls the master node to apply the sharding results (i.e., SQL requests) in the committed log entry to the local state machine, so as to send the rewritten SQL requests to each storage node.

[0154] In this embodiment, the master node is responsible for handling client SQL requests and maintaining cluster consistency, while the slave nodes replicate the master node's log entries to maintain data consistency. The two work together to ensure the consistency and reliability of the distributed system, ensuring that data is not lost after an abnormal switch, thereby guaranteeing the high availability of the system. Furthermore, the data of all nodes remains consistent, avoiding potential problems caused by data inconsistency.

[0155] In one embodiment, as shown in FIG5, step S204, the data decryption process includes the following steps S501 to S504. Wherein:

[0156] Step S501: Based on the target shard and the log entries in each storage node, query the business data stored in the storage node, obtain and parse the column part of the MySQL protocol packet, and obtain the column information of each column in the business data.

[0157] In the MySQL protocol packet, the column part refers to the part of the query result message that describes the information of each column (field) in the query result set; column information refers to the information or data corresponding to that column field.

[0158] For example, the server locates the corresponding storage node based on the target shard, queries the business data stored in the storage node based on the log entries in each storage node, obtains and parses the column part of the MySQL protocol packet, and obtains the column information of each column of each business data in the information table.

[0159] Step S502: Match the fields of the column information with the encrypted fields in the metadata table, and determine the fields of the column information that match successfully as the decrypted fields.

[0160] The decryption field refers to the field that corresponds to the encryption field of the previously encrypted data during the decryption process. Usually, the decryption field and the encryption field refer to the same field, but are two states of the field at different stages. For example, for the phone_number field, it is called the decryption field during the decryption stage, and it is called the encryption field during the encryption stage.

[0161] For example, after obtaining column information, the server matches the fields of the column information with the encrypted fields in the metadata table. If the match is successful, it means that the data corresponding to the column information is encrypted data. Accordingly, in the response, the encrypted data needs to be decrypted and returned to the client. Therefore, the fields of the column information that are successfully matched are determined as decrypted fields.

[0162] Step S503: Determine the encryption algorithm and encryption algorithm parameters of the decryption field based on the encryption field that successfully matches the decryption field, and determine the corresponding decryption algorithm based on the encryption algorithm and encryption algorithm parameters.

[0163] Among them, the decryption algorithm refers to the algorithm used to decrypt encrypted data, which is the counterpart to the encryption algorithm.

[0164] For example, the server determines the encryption algorithm and encryption algorithm parameters of the decryption field based on the encryption field that successfully matches the decryption field, and determines the corresponding decryption algorithm based on the encryption algorithm and encryption algorithm parameters, so as to decrypt the encrypted data.

[0165] Step S504: Decrypt the decryption field based on the decryption algorithm to obtain decrypted data.

[0166] In this embodiment, by decrypting the encrypted data, the encrypted data can be decrypted and returned to the user when the client generates an SQL request, and the user is unaware of the decryption process.

[0167] In one application example, this embodiment proposes a data transparency encryption method based on database middleware. The processing of sensitive data mainly involves a coordinated processing procedure between the compute node module and the storage node module.

[0168] The compute node module is responsible for configuring encrypted tables and processing business SQL requests. The encrypted table configuration includes setting ciphertext columns, encryption algorithms, and middleware user permissions, and this information is stored in ZooKeeper's metadata. SQL request processing mainly includes SQL parsing to obtain the table and column names of the context, matching the encrypted table configuration stored in the metadata with columns, calculating routing shards using a sharding algorithm, and a pipeline of SQL rewriting actions. First, during SQL parsing, a Druid parser with a custom extended visitor is used to traverse the abstract syntax tree and identify the column names of each SQL statement. Second, a stack data structure is used to maintain the context of the syntax tree, overcoming the lack of context in the Druid parser, and obtaining the SQL table name from the source at the top of the stack. Third, combining the encrypted configuration information of the metadata table stored in ZooKeeper and the custom encryption algorithm, SQL rewriting is performed to encrypt specific columns. In the SQL rewriting process, different statements are processed for CRUD operations. The core of SELECT processing is the rewriting of the WHERE condition, while DELETE and UPDATE reuse the rewriting of their WHERE conditions. The entire encryption process is transparent to the user. Then, based on the sharding key and sharding algorithm, the specific shards of the data distribution are calculated, and the rewritten SQL is sent to the underlying layer. Finally, after retrieving the underlying data, the encrypted columns are identified by combining the encryption configuration information of the metadata table and the column information contained in the fields of the MySQL protocol packet. The encrypted column data of the retrieved data is decrypted and encapsulated into a MySQL protocol packet and returned to the client. The decryption process is also transparent to the user.

[0169] The storage node module employs the Raft algorithm for distributed consistency. For the DN cluster of storage nodes, the concepts of node election, data synchronization, and data persistence are applied to the MySQL engine layer. Qualification checks, random sleep cycles, and voting processes are added, significantly reducing communication overhead and achieving eventual consistency in distributed scenarios.

[0170] The specific steps of the transparent data encryption method based on database middleware in this example are as follows:

[0171] Step 1: Preprocessing the SQL request for parsing. First, during SQL parsing, the Druid parser, with a custom extended visitor, traverses the abstract syntax tree to identify the column names of each SQL statement. Second, a stack data structure is used to maintain the context of the syntax tree, overcoming the Druid parser's lack of context, and the name of the SQL table is obtained from the source at the top of the stack. Third, combining the encryption configuration information of the metadata table stored in the Zookeeper middleware with a custom encryption algorithm, the SQL is rewritten to encrypt specific columns. In the SQL rewriting, different statements are processed for CRUD operations (Create, Read, Update, Delete). The core of SELECT processing is the rewriting of the WHERE condition, while DELETE and UPDATE reuse their WHERE condition rewriting. The entire encryption process is imperceptible to the user.

[0172] Step 2: Route calculation for the compute node module. Based on the preprocessing of SQL parsing, the specific shards of the data distribution are calculated according to the sharding keys and sharding algorithms (hash, time range, modulo, etc.) stored on Zookeeper, and the rewritten SQL is sent to the underlying layer.

[0173] Step 3: Storage node module. The distributed consistency Raft algorithm is used to transform the system. The ideas of node election, data synchronization and data persistence are applied to the MySQL engine layer. Qualification election check, random sleep and voting processes are added, which greatly reduces communication overhead and achieves eventual consistency in distributed scenarios.

[0174] Step 4: After the route is calculated, the SQL request is sent and the underlying data is retrieved, the encrypted columns are identified by combining the encrypted configuration information of the metadata table and the column information contained in the field of the MySQL protocol packet. The encrypted column data of the retrieved data is decrypted and encapsulated into a MySQL protocol packet and returned to the client. The decryption process is imperceptible to the user.

[0175] Based on the above process, this example achieves transparent encryption and fast querying of business data in massive data scenarios, ensuring no omissions or duplications in data modification and query results. This is achieved through SQL parsing optimization, routing calculation, and ciphertext column identification and encryption / decryption in the computing module, as well as distributed consistency transformation in the storage module. This balances data processing security, concurrency, and consistency. Comparative experiments demonstrate that this application achieves transparent data encryption while maintaining consistency and synchronization of business data in massive data scenarios, without increasing hardware costs, and improves performance by approximately four times. This further verifies the effectiveness of this application in transparent encryption and query optimization of distributed databases in massive data scenarios.

[0176] Compared to existing distributed databases that rely solely on database sharding and table partitioning, the method presented in this example achieves transparent data encryption and query optimization while ensuring the consistency and completeness of business data queried in a distributed database under sharding and table partitioning scenarios.

[0177] In terms of functionality, this example utilizes the SQL parsing optimization and routing calculation of the computing module to complete the identification of encrypted columns and transparent data encryption; relying on the routing calculation of the computing nodes, the routing information of the data can be quickly located, thereby enabling rapid querying of massive amounts of data.

[0178] To ensure data consistency and accuracy in distributed databases, the distributed consistency transformation of storage modules can be used to achieve distributed consistency cluster disaster recovery even if some of the distributed database connection storage nodes fail, thereby ensuring the security of business data requests for querying distributed databases.

[0179] In terms of performance, this example is compared with existing distributed databases that rely solely on database sharding and table partitioning. Under the condition of a single data query request and a single connection, 100,000 set and get operations were performed on business data with value sizes of 1KB and 10KB respectively. The test results are shown in Table 1 (unit: ms).

[0180] Table 1

[0181] The performance test results in Table 1 verify that the data query optimization scheme proposed in this example is significantly better than the scheme that does not use SQL parsing optimization of computing nodes and consistency transformation of routing and storage modules, with a speed improvement of about 4 times.

[0182] It should be understood that although the steps in the flowcharts of the embodiments described above are shown sequentially according to the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the flowcharts of the embodiments described above may include multiple steps or multiple stages. These steps or stages are not necessarily completed at the same time, but can be executed at different times. The execution order of these steps or stages is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the steps or stages of other steps.

[0183] Based on the same inventive concept, this application also provides a data encryption query device for implementing the data encryption query method described above. The solution provided by this device is similar to the implementation described in the above method; therefore, the specific limitations in one or more data encryption query device embodiments provided below can be found in the limitations of the data encryption query method described above, and will not be repeated here.

[0184] In an exemplary embodiment, as shown in FIG6, a data encryption query device is provided, including: a request parsing module 601, a request rewriting module 602, a fragmented storage module 603, a data query module 604, and a data encapsulation module 605. Wherein:

[0185] The request parsing module 601 is used to receive SQL requests, parse the SQL requests, match the parsed SQL requests with the configured metadata table, and obtain the column fields and information table corresponding to the encrypted data carried in the SQL requests.

[0186] The request rewrite module 602 is used to rewrite the column fields and information table based on the metadata table, embedding the encryption algorithm and encryption algorithm parameters in the metadata table into the SQL request to obtain the rewritten SQL request;

[0187] The sharded storage module 603 is used to shard the rewritten SQL request to obtain the target shard, and send the sharded SQL request to multiple storage nodes corresponding to the target shard; the multiple storage nodes distribute business information and its corresponding business data, including encrypted data and unencrypted data;

[0188] The data query module 604 is used to retrieve business information stored in the corresponding storage node based on the rewritten SQL request, and to determine the encrypted data in the business data based on the metadata table and MySQL protocol packets, decrypt the encrypted data, and obtain the decrypted data corresponding to the encrypted data; and

[0189] The data encapsulation module 605 is used to encapsulate the decrypted data and unencrypted data into response data and return it.

[0190] In an optional embodiment, the above-mentioned data encryption query device further includes:

[0191] The data configuration module is used to configure the metadata table and store the metadata table in a middleware.

[0192] In an optional embodiment, the request parsing module 601 is further configured to receive an SQL request, obtain the SQL statement in the SQL request, parse the SQL statement to obtain the abstract syntax tree corresponding to the SQL statement; traverse each node of the abstract syntax tree, determine the column name node in each node, and match the column name node with the encrypted field to obtain the column field corresponding to the encrypted data; and based on the matched column field, determine the information table to which the SQL request corresponding to the column field belongs, push the information table onto the stack using a stack structure, obtain the information table name of the SQL statement corresponding to the column field at the top of the stack structure, and determine the column field and the information table to which it belongs.

[0193] In an optional embodiment, the request rewriting module 602 is further configured to obtain a metadata table, determine the encryption algorithm and encryption algorithm parameters of the column field based on the encryption field that matches the column field in the metadata table, and rewrite the column field and the information table by at least one of the following: query request, insert request, update request, and delete request, based on the encryption algorithm, encryption algorithm parameters, and the type of SQL request, to obtain a rewritten SQL request; the rewritten SQL request embeds the encryption algorithm and encryption algorithm parameters.

[0194] In an optional embodiment, the sharded storage module 603 is further configured to obtain sharding configuration information and perform sharding initialization; the sharding configuration information includes a sharding key and a sharding algorithm; based on the parsed SQL request, the sharding key is extracted from the SQL request; based on the sharding algorithm, sharding calculation is performed on the sharding key to obtain sharded data; the target shard matching the sharded data is determined based on the sharded data; and the rewritten SQL request is sent to multiple storage nodes corresponding to the target shard.

[0195] In an optional embodiment, the sharded storage module 603 is further configured to initialize all storage nodes; select master nodes for the storage nodes and determine the other storage nodes besides the master nodes as slave nodes; when an SQL request is sent to the target shard, control the master node to perform data encapsulation to encapsulate the SQL request into a log entry and replicate it to the master node, and control each slave node to replicate the log entry to the corresponding slave node based on the replication request of the master node; and when the number of slave nodes replicating the log entry meets the preset conditions, control the master node to return the replication result so as to send the rewritten SQL request to each storage node.

[0196] In an optional embodiment, the data query module 604 is further configured to query the business data stored in the storage nodes based on the target shard and the log entries in each storage node, obtain and parse the column portion of the MySQL protocol packet to obtain the column information of each column in the business data; match the fields of the column information with the encrypted fields in the metadata table, and determine the fields of the successfully matched column information as decryption fields; determine the encryption algorithm and encryption algorithm parameters of the decryption fields based on the encrypted fields that successfully match the decryption fields, determine the corresponding decryption algorithm based on the encryption algorithm and encryption algorithm parameters; and decrypt the decryption fields based on the decryption algorithm to obtain decrypted data.

[0197] Each module in the aforementioned data encryption query device can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in or independent of the processor in a computer device, or stored in the memory of a computer device as software, so that the processor can call and execute the operations corresponding to each module.

[0198] In an exemplary embodiment, a computer device is provided, which may be a server, and its internal structure diagram is shown in Figure 7. The computer device includes a processor, memory, input / output interfaces (I / O), and a communication interface. The processor, memory, and I / O interfaces are connected via a system bus, and the communication interface is connected to the system bus via the I / O interfaces. The processor of the computer device provides computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and internal memory. The non-volatile storage medium stores an operating system, computer programs, and a database. The internal memory provides an environment for the operation of the operating system and computer programs in the non-volatile storage medium. The database of the computer device stores encrypted data, encrypted fields, encryption algorithms, encryption algorithm parameters, various fields, information tables, fragmentation configuration information, and other data. The I / O interfaces of the computer device are used for exchanging information between the processor and external devices. The communication interface of the computer device is used for communication with external terminals via a network connection. When the computer program is executed by the processor, it implements a data encryption query method.

[0199] Those skilled in the art will understand that the structure shown in Figure 7 is a block diagram of a partial structure related to the present application and does not constitute a limitation on the computer device to which the present application is applied. The specific computer device may include more or fewer components than shown in the figure, or combine certain components, or have different component arrangements.

[0200] In one exemplary embodiment, a computer device is provided, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to implement the data encryption query method of the above embodiment.

[0201] In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored, which, when executed by a processor, implements the data encryption query method of the above embodiment.

[0202] In one embodiment, a computer program product is provided, including a computer program that, when executed by a processor, implements the data encryption query method of the above embodiments.

[0203] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of the relevant data must comply with relevant regulations.

[0204] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium, and when executed, it can include the processes of the embodiments of the above methods. Any references to memory, databases, or other media used in the embodiments provided in this application can include at least one of non-volatile memory and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM can take many forms, such as Static Random Access Memory (SRAM) or Dynamic Random Access Memory (DRAM). The databases involved in the embodiments provided in this application may include at least one type of relational database and non-relational database. Non-relational databases may include, but are not limited to, blockchain-based distributed databases. The processors involved in the embodiments provided in this application may be general-purpose processors, central processing units, graphics processing units, digital signal processors, programmable logic devices, quantum computing-based data processing logic devices, artificial intelligence (AI) processors, etc., and are not limited to these.

[0205] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0206] The embodiments described above are merely illustrative of several implementation methods of this application, and while the descriptions are relatively specific and detailed, they should not be construed as limiting the scope of the patent application. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of this application, and these all fall within the protection scope of this application. Therefore, the protection scope of this patent application should be determined by the appended claims.

Claims

1. A data encryption query method, characterized in that, The method includes: Receive an SQL request, parse the SQL request, match the parsed SQL request with the configured metadata table, and obtain the column fields and information table corresponding to the encrypted data carried in the SQL request; Based on the metadata table, the column fields and information table are rewritten using SQL. The encryption algorithm and encryption algorithm parameters in the metadata table are embedded into the SQL request to obtain the rewritten SQL request. The rewritten SQL request is sharded to obtain the target shard, and the sharded SQL request is sent to multiple storage nodes corresponding to the target shard; the multiple storage nodes distribute business information and its corresponding business data, and the business data includes encrypted data and unencrypted data. The modified SQL request retrieves the business information stored in the corresponding storage node, and based on the metadata table and MySQL protocol packet, determines the encrypted data in the business data, decrypts the encrypted data, and obtains the decrypted data corresponding to the encrypted data; and The decrypted data and the unencrypted data are encapsulated into response data and returned.

2. The method according to claim 1, characterized in that, Before receiving the SQL request, parsing the SQL request, and matching the parsed SQL request with the configured metadata table to obtain the column fields and information table corresponding to the encrypted data carried in the SQL request, the process further includes: Configure a metadata table and store the metadata table in middleware; the metadata table includes encryption fields configured based on encrypted data and their corresponding encryption algorithms and encryption algorithm parameters.

3. The method according to claim 2, characterized in that, The process of receiving an SQL request, parsing the SQL request, and matching the parsed SQL request with a configured metadata table to obtain the column fields and information table corresponding to the encrypted data carried in the SQL request includes: Receive an SQL request, obtain the SQL statement in the SQL request, parse the SQL statement, and obtain the abstract syntax tree corresponding to the SQL statement; Traverse each node of the abstract syntax tree, determine the column name nodes in each node, and match the column name nodes with the encrypted fields to obtain the column fields corresponding to the encrypted data; and Based on the matched column fields, determine the information table to which the SQL request corresponding to the column field belongs, push the information table onto the stack using a stack structure, obtain the information table name of the SQL statement corresponding to the column field from the top of the stack structure, and determine the column field and the information table to which it belongs.

4. The method according to claim 2, characterized in that, The SQL request type includes at least one of query request, insert request, update request, and delete request; The step of rewriting the SQL based on the metadata table for the column fields and the information table, embedding the encryption algorithm and encryption algorithm parameters from the metadata table into the SQL request, to obtain the rewritten SQL request includes: Obtain the metadata table, and based on the encrypted fields in the metadata table that match the column fields, determine the encryption algorithm and encryption algorithm parameters for the column fields; and Based on the encryption algorithm, the encryption algorithm parameters, and the type of the SQL request, at least one of the following is rewritten: query request, insert request, update request, and delete request, to obtain the rewritten SQL request; the rewritten SQL request embeds the encryption algorithm and the encryption algorithm parameters.

5. The method according to claim 2, characterized in that, The middleware also stores sharding configuration information; The step of performing a sharding operation on the rewritten SQL request to obtain a target shard, and sending the sharded SQL request to multiple storage nodes corresponding to the target shard, includes: Obtain sharding configuration information and perform sharding initialization; the sharding configuration information includes sharding keys and sharding algorithms; Based on the parsed SQL request, extract the sharding key from the SQL request; The sharding key is sharded based on the sharding algorithm to obtain sharded data; and a target shard matching the sharded data is determined based on the sharded data. The rewritten SQL request is sent to multiple storage nodes corresponding to the target shard.

6. The method according to claim 5, characterized in that, Sending the rewritten SQL request to multiple storage nodes corresponding to the target shard includes: Initialize all storage nodes; The storage nodes are selected as master nodes, and the other storage nodes besides the master nodes are determined as slave nodes. When the SQL request is sent to the target shard, the master node is controlled to perform data encapsulation to encapsulate the SQL request into a log entry and replicate it to the master node. Based on the master node's replication request, each slave node is controlled to replicate the log entry to its corresponding slave node. When the number of slave nodes replicating the log entry meets the preset conditions, the master node is controlled to return the replication result so that the rewritten SQL request can be sent to each storage node.

7. The method according to claim 6, characterized in that, The process involves obtaining business information stored in the corresponding storage node based on the rewritten SQL request, determining encrypted data in the business data based on the metadata table and MySQL protocol packets, decrypting the encrypted data to obtain decrypted data corresponding to the encrypted data, including: Based on the target shard and the log entries in each storage node, query the business data stored in the storage node, obtain and parse the column part of the MySQL protocol packet, and obtain the column information of each column in the business data; The fields of the column information are matched with the encrypted fields in the metadata table, and the fields of the column information that match successfully are determined as the decrypted fields; The encryption algorithm and encryption algorithm parameters of the decryption field are determined based on the encryption field that successfully matches the decryption field, and the corresponding decryption algorithm is determined based on the encryption algorithm and encryption algorithm parameters; and The decryption field is decrypted based on the decryption algorithm to obtain decrypted data.

8. A data encryption query device, characterized in that, The device includes: The request parsing module is used to receive SQL requests, parse the SQL requests, match the parsed SQL requests with the configured metadata table, and obtain the column fields and information table corresponding to the encrypted data carried in the SQL requests. The request rewrite module is used to rewrite the column fields and information table based on the metadata table, embedding the encryption algorithm and encryption algorithm parameters in the metadata table into the SQL request to obtain the rewritten SQL request; The sharded storage module is used to shard the rewritten SQL request to obtain the target shard, and send the sharded SQL request to multiple storage nodes corresponding to the target shard; the multiple storage nodes distribute and store business information and its corresponding business data, the business data including encrypted data and unencrypted data; The data query module is used to obtain business information stored in the corresponding storage node according to the rewritten SQL request, and to determine the encrypted data in the business data based on the metadata table and MySQL protocol packets, decrypt the encrypted data, and obtain decrypted data corresponding to the encrypted data; and The data encapsulation module is used to encapsulate the decrypted data and the unencrypted data into response data and return it.

9. The apparatus according to claim 8, characterized in that, The device further includes: The data configuration module is used to configure the metadata table and store the metadata table in a middleware; the metadata table includes encrypted fields based on encrypted data configuration and their corresponding encryption algorithms and encryption algorithm parameters.

10. The apparatus according to claim 9, characterized in that, The request parsing module is also used for: Receive an SQL request, obtain the SQL statement in the SQL request, parse the SQL statement, and obtain the abstract syntax tree corresponding to the SQL statement; Traverse each node of the abstract syntax tree, determine the column name node in each node, and match the column name node with the encrypted field to obtain the column field corresponding to the encrypted data; and Based on the matched column fields, determine the information table to which the SQL request corresponding to the column field belongs, push the information table onto the stack using a stack structure, obtain the information table name of the SQL statement corresponding to the column field from the top of the stack structure, and determine the column field and the information table to which it belongs.

11. The apparatus according to claim 9, characterized in that, The SQL request type includes at least one of query request, insert request, update request, and delete request; the request rewriting module is also used for: Obtain the metadata table, and based on the encrypted fields in the metadata table that match the column fields, determine the encryption algorithm and encryption algorithm parameters for the column fields; and Based on the encryption algorithm, the encryption algorithm parameters, and the type of the SQL request, at least one of the following is rewritten: query request, insert request, update request, and delete request, to obtain the rewritten SQL request; the rewritten SQL request embeds the encryption algorithm and the encryption algorithm parameters.

12. The apparatus according to claim 9, characterized in that, The middleware also stores sharding configuration information; the sharding storage module is further used for: Obtain sharding configuration information and perform sharding initialization; the sharding configuration information includes sharding keys and sharding algorithms; Based on the parsed SQL request, extract the sharding key from the SQL request; The sharding key is sharded based on the sharding algorithm to obtain sharding data, and the target shard that matches the sharding data is determined based on the sharding data. and The rewritten SQL request is sent to multiple storage nodes corresponding to the target shard.

13. A computer device comprising a memory and a processor, wherein the memory stores a computer program, characterized in that, When the processor executes the computer program, it implements the steps of the method according to any one of claims 1 to 7.

14. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 7.

15. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 7.

Images