A dynamic semantic mapping privacy protection method for a text-to-SQL conversion system

Abstract

The application discloses a kind of dynamic semantic mapping privacy protection methods for text to SQL conversion system, belong to the cross field of database security and natural language processing.The method is first by offline Schema registration stage, extract the multi-dimensional information of database column to generate semantic reservation shadow name, establish bidirectional mapping relationship and configure desensitization rule;Again in online query processing stage, complete Schema link through local small language model, execute dynamic desensitization in combination with sensitive column density, send Prompt after desensitization to remote large model to generate desensitization SQL, finally restored to executable SQL statement by reverse mapping.The application can guarantee the privacy security of database Schema while effectively maintaining the high accuracy of SQL generation, suitable for enterprise-level text to SQL conversion system security deployment scene.

Description

Technical Field

[0001] This invention belongs to the interdisciplinary field of database security and natural language processing, and specifically relates to a method for protecting privacy through dynamic semantic mapping in text-to-SQL conversion systems. Background Technology

[0002] In recent years, text-to-SQL technology based on Large Language Models (LLMs) has made significant progress, enabling non-technical users to directly query relational databases using natural language. In such systems, the large model needs to receive complete database schema information, including table names, column names, data types, and constraints, in order to correctly understand the database structure and generate executable SQL queries. However, in enterprise deployment scenarios, directly sending the actual schema to a remote large model service poses serious privacy and security risks.

[0003] Specifically, database schemas inherently contain highly sensitive corporate information. First, table and column names can directly expose core business logic. For example, companies might use business-oriented names like "Membership Tiering Rules" and "Patient Medical Records" to organize data tables, directly revealing the company's risk control strategies and sensitive information. Second, if a large model is vulnerable to prompt injection attacks, attackers can induce the model to leak table structure information through dialogue, thereby precisely locating subsequent attack targets. Third, databases often contain highly sensitive fields such as ID card numbers. If unauthorized disclosure occurs during model inference, it will cause irreversible data security incidents. Therefore, directly transmitting schemas with core risks to remote servers presents severe compliance and security challenges.

[0004] To address the above problems, academia and industry have proposed a variety of solutions, but all of them have obvious limitations.

[0005] The first type of scheme is symbol masking, whose core idea is to replace real table and column names with semantically meaningless abstract symbols, uniformly replacing all column names with meaningless number markers. However, this method results in severe semantic loss; large models cannot extract any information about the meaning of a column from the meaningless numbers, thus losing the ability to reason and self-correct using the meaning of column names. Since large models are completely unable to infer the actual meaning of columns during the reasoning process, the accuracy of the generated query statements will drop significantly, making it difficult to meet the requirements of production environments. This shows that deterministic static masking schemes struggle to achieve an effective balance between privacy protection and generation accuracy.

[0006] The second approach is schema pruning. Its core idea is to reduce exposure and transmission overhead by retrieving and sorting only a small number of tables most relevant to the user's question and passing them to the model. However, simple schema pruning cannot solve the core privacy leakage problem. When a user directly queries sensitive fields (such as "check employee salaries"), the system must pass the actual salary field names in plaintext to the model in order to generate the correct query, thus posing a secondary risk of sensitive information leakage.

[0007] In summary, existing solutions suffer from a fundamental flaw: they struggle to maintain high accuracy in SQL statement generation while ensuring strong data privacy protection. Static masks protect privacy but severely compromise accuracy, while schema pruning preserves accuracy but fails to protect core sensitive fields. Therefore, a schema privacy protection method is urgently needed that can anonymize the real database structure while preserving sufficient semantic clues to support high-accuracy SQL generation for the remote large-scale model. Ultimately, this aims to achieve a synergistic balance between strong privacy protection and high SQL generation accuracy. Summary of the Invention

[0008] To address the technical challenge of balancing database schema privacy protection with SQL generation accuracy, this invention proposes a dynamic semantic mapping privacy protection method for text-to-SQL conversion systems. This method deploys a secure middleware layer between the user frontend and a remote large-scale model service, generating a set of semantically preserved alternative names (defined as "shadow names") for the actual table and column names in the database. This ensures that the remote large-scale model can only access the shadow names, not the actual database structure information, during inference. The shadow names embed the data type and semantic category of the column, allowing the large-scale model to extract the basic properties of the column to generate correct query statements. Simultaneously, a deterministic bidirectional mapping relationship exists between the shadow names and the actual column names, allowing the system to restore the shadow names to the actual column names without affecting normal database query usage. The entire method comprises two collaborative stages: offline schema registration and online query processing. The specific technical solution is as follows:

[0009] I. Offline Schema Registration

[0010] Before the system officially processes user queries, a one-time preprocessing registration of the real database schema is required. Specifically, the system analyzes the structure of each table in the database column by column, extracting multi-dimensional information such as the type, semantics, and structure of each column. Based on this, corresponding shadow names are generated and a two-way mapping is established. Simultaneously, the administrator configures de-identification rules according to the enterprise security policy to support subsequent dynamic de-identification decisions. This stage only needs to be re-executed when the database schema changes. The specific process is as follows:

[0011] 1. Multi-dimensional information extraction: Extract descriptive information for each column from the database's Data Definition Language (DDL), including at least the following three dimensions:

[0012] (1) SQL data types: The system parses the type of each column (such as INTEGER, TEXT, DECIMAL and DATE) from the DDL, and then maps it to the system's predefined type code (such as INTEGER mapped to "INT", DECIMAL mapped to "DECIMAL"), which is used to identify in the shadow name what type of operation the column should participate in.

[0013] (2) Semantic category: The system first performs word segmentation on the column name, then calculates the semantic vector of the word segmentation result through the locally deployed Embedding Model, and then calculates the similarity between the vector and the representative vector corresponding to the semantic category (such as “amount”, “time”, “identity identifier” and “ordinary text”). The category with the highest similarity is taken as the semantic category of the column, which helps the remote large model understand the general purpose of the column.

[0014] (3) Structural Role: The system parses the constraint definition clauses in the DDL, identifies structural markers such as primary key, foreign key, and unique constraint, and binds the constraint type to the column name it constrains. For foreign key constraints, the system additionally parses the REFERENCES clause to extract the associated target table name and target column name.

[0015] The SQL data types and structure roles are obtained by parsing the DDL statements.

[0016] Taking the "employee_roster" table (employee_roster) described in the specific implementation as an example, the system's multi-dimensional information extraction results for each column are as follows: 1. The SQL data type of the employee_id column is INTEGER, the semantic category belongs to identity identification, and the structural role is the primary key; 2. The SQL data type of the id_card column is TEXT, the semantic category belongs to identity identification, and the structural role is the unique constraint; the SQL data type of the salary column is DECIMAL, the semantic category belongs to amount, and the structural role is the ordinary column; 3. The SQL data type of the hire_date column is DATE, the semantic category belongs to time, and the structural role is the ordinary column; 4. The SQL data type of the dept_id column is INTEGER, the semantic category belongs to number, and the structural role is the foreign key, and the associated target is the dept_id column of the department table. Among them, "salary" is segmented into "salary", and its semantic vector is closest to the "amount" category label, so it is classified into the "amount" category; id_card is segmented into "id" and "card", and its semantic vector is closest to the "identity identification" category label, so it is classified into the "identity identification" category; hire_date is segmented into "hire" and "date", and is classified into the "time" category.

[0017] 2. Shadow name generation and two-way mapping: Based on the extracted multi-dimensional information, the system generates a shadow name for each column according to the fixed naming rule of "type prefix_semantic category_sequence number". The shadow name contains the data type and semantic category of the column, enabling the remote large model to obtain the basic properties of the column from it, but it cannot know the real column name. At the same time, a complete two-way mapping table is established. The forward mapping is used to replace the real column name with the shadow name, and the reverse mapping is used to restore the shadow name to the real column name.

[0018] Still taking the "employee_roster" table as an example, the shadow names of each column are generated as follows: 1. salary: type prefix DECIMAL, semantic category "amount", shadow name "DECIMAL_amount_2"; 2. id_card: type prefix TEXT, semantic category "identity identification", shadow name "TEXT_identity identification_4". The system also records the mapping entries in the forward and reverse dictionaries. The forward mapping records "salary→DECIMAL_amount_2", and the reverse mapping records "DECIMAL_amount_2→salary", and the rest of the columns are类推.

[0019] 3. Sensitive rule configuration: The administrator configures desensitization rules for the columns in the database according to the enterprise security policy, including the following two methods:

[0020] (1) Forced desensitization of each column: For columns that must be protected at all times in business operations, the administrator directly marks them as fields to be forced to be desensitized.

[0021] (2) Overall desensitization: Administrators can desensitize certain semantic categories as a whole, and all columns belonging to these categories will automatically become fields that are forcibly desensitized.

[0022] All fields marked using the two methods described above that are subject to mandatory de-identification will be replaced with shadow names in any query scenario, unaffected by the confidence level determination in subsequent dynamic de-identification strategies, serving as a fallback guarantee for privacy protection. Whether unmarked columns (such as "department number") are de-identified is determined by the dynamic strategy in the subsequent online phase.

[0023] In the "Employee Roster" table: 1. Column-by-column mandatory anonymization: Administrators mark core columns involving personal privacy, such as "ID Card Number" (id_card), as mandatory anonymization fields; 2. Overall anonymization: Administrators mark semantic categories such as "Amount" and "Identity Identifier" as sensitive. Based on this, the system automatically treats all columns belonging to these categories (e.g., salary and bonus, both belonging to the "Amount" category) as mandatory anonymized fields as well, without needing to mark them individually. Whether other columns (such as dept_id) are anonymized is determined by a dynamic strategy during the subsequent online phase.

[0024] II. Online Inquiry Processing

[0025] When a user submits a natural language query, the system executes the following processing flow in real time, sequentially completing four steps: schema linking, dynamic desensitization, remote inference, and reverse engineering:

[0026] 1. Local Schema Linking: In a secure local environment, a small-scale language model (SLM) identifies the correspondence between natural language fragments in the user's question and the actual database columns. This model is deployed locally, posing no privacy risk, and allows simultaneous viewing of the actual column names and the user's original question. The specific process includes the following sub-steps:

[0027] (1) Reference fragment extraction: SLM extracts all reference fragments involving database columns from the user's natural language questions.

[0028] (2) Semantic matching and binding: For each reference fragment, its semantic vector is calculated using the local embedding model, and the cosine similarity is calculated with the semantic vectors of all candidate column names as the matching score. The column with the highest score is selected as the binding result. For each binding relationship, SLM outputs a confidence score (ranging from 0 to 1), reflecting the system's confidence in the binding result.

[0029] (3) Privacy protection of low confidence columns: When the confidence of a certain binding is lower than the preset threshold, it means that the system is not certain about the current binding. The system only replaces the column corresponding to the low confidence binding with the shadow name, while the other high confidence bindings retain the real column name. This maximizes the preservation of semantic information while protecting privacy, so as to ensure that a high generation accuracy can still be maintained in complex query scenarios.

[0030] Taking the user's submission of "Check the employee IDs and salaries of all employees in Department 5" as an example: 1. SLM extracts three reference fragments: "employee ID", "salary", and "Department 5"; 2. "Employee ID" is bound to employee_id (confidence 0.92), "salary" is bound to salary (confidence 0.95), and "Department 5" is bound to dept_id (confidence 0.88); 3. Since the confidence of all three is higher than the preset threshold (e.g., 0.7), only the fields that are forcibly de-identified need to be replaced according to the rules, while the real column names are retained for the remaining columns.

[0031] 2. Dynamic Masking: Based on the binding results of the schema links and the masking rules configured in the offline stage, dynamic masking processing is performed on the schema. This includes the following sub-steps:

[0032] (1) Forced desensitized fields are always replaced: For fields marked by the administrator as forced desensitized, the shadow name is always used to replace them, regardless of whether they are involved in the current query, as a last resort for privacy protection.

[0033] (2) Sensitive column density calculation: Calculate the proportion of columns that are mandatory desensitization fields in the total number of columns involved in the current query, and record this proportion as the sensitive column density.

[0034] (3) Dynamic desensitization decision: When the density of sensitive columns exceeds the preset threshold, it indicates that the current query set involves a large number of sensitive fields. Attackers may infer the true meaning behind the shadow name through the remaining undesensitized field names. Therefore, the system will perform shadow name replacement desensitization on all fields involved in the current query. When the density is lower than the threshold, only the fields that are forced to be desensitized and the low confidence binding columns are replaced to maximize the accuracy of SQL generation.

[0035] (4) Synchronous replacement of question text and assembly of Prompt: Perform corresponding replacements on the natural language reference fragments involving desensitized columns in the user's question text, and finally send the Prompt that has been desensitized by Schema to the remote large model.

[0036] Taking the query "Check the employee IDs and salaries of all employees in Department 5" as an example: 1. This query involves three columns: employee_id, salary, and dept_id; 2. salary is a mandatory de-identified field belonging to the "amount" category, and is unconditionally replaced with the shadow name "DECIMAL_amount_2"; 3. The density of the sensitive column is 1 / 3, and assuming the density threshold is 0.5, the density does not exceed the limit; 4. The confidence scores of the three bindings are all higher than the preset threshold; 5. In the end, only salary is replaced with "DECIMAL_amount_2", while employee_id and dept_id retain their real column names.

[0037] 3. Remote Large Model Generates Anonymous SQL: The remote large model receives the anonymized prompt and generates SQL query statements using shadow names. Because the shadow names retain type and semantic category hints, the large model can understand the data type and general semantic purpose of the columns, thus generating correctly typed query statements. Throughout the entire remote inference process, the large model only sees the shadow names and the regular column names that are not anonymized; the real names of all anonymized columns are not exposed to external services.

[0038] In the query example above, the salary in the schema received by the large model has been replaced with "DECIMAL_Amount_2". Based on this, the large model generates the following de-identified SQL: SELECT employee_id, DECIMAL_Amount_2 FROM employee_roster WHERE dept_id = 5

[0039] 4. Reverse Mapping Restoration: The security middleware layer queries the reverse mapping table established during the offline phase, replacing all shadow names in the de-identified SQL with the corresponding real table names and column names, thus restoring the SQL statement that can be executed on the real database. The specific process includes the following sub-steps:

[0040] (1) Shadow name recognition: The system uses text pattern matching to scan all identifiers in the de-identified SQL that conform to the naming format of "type prefix_semantic category_serial number" and collects them into a list of shadow names to be restored.

[0041] (2) Reverse table lookup and replacement: For each identified shadow name, the system searches in the reverse mapping table established in the offline stage and replaces it with the corresponding real column name. If a shadow name that conforms to the naming format cannot be found in the mapping table, the identifier is determined to be invalid content generated by the large model illusion, the system stops the current query and returns an error message to the user.

[0042] (3) Foreign Key JOIN Relationship Recovery: When the restored SQL contains multi-table join queries, the system performs syntax parsing on the SQL and identifies the JOIN clauses. For the columns involved in the JOIN conditions, the system queries the foreign key association metadata recorded in the offline stage (i.e., the foreign key target table and target column information extracted in step 1) to verify whether the JOIN conditions generated by the large model are consistent with the recorded foreign key association relationships. If they are inconsistent, the JOIN conditions are corrected according to the foreign key metadata.

[0043] (4) SQL syntax verification: Perform syntax verification on the restored complete SQL to ensure that the restoration operation does not destroy the structural integrity of the SQL.

[0044] After completing the reverse mapping and syntax verification, the system sends the SQL to the real database for execution and returns the query results to the user.

[0045] In the above de-identified SQL: 1. The system identifies "DECIMAL_Amount_2" as a shadow name; 2. The corresponding real column name "salary" is found in the reverse mapping table, and after replacement, the following is obtained: SELECT employee_id, salary FROM employee_roster WHERE dept_id = 53. If the user's query involves the name of the employee's department, the large model generates a JOIN statement between employee_roster and department. After restoration, the system can only query the two tables through the "dept_id column of both parties". It checks whether the JOIN condition meets the rule, and if there is an error, it is corrected.

[0046] Beneficial effects

[0047] This invention achieves an effective balance between schema privacy protection and SQL generation accuracy through a dynamic semantic mapping mechanism. Compared to existing symbol masking schemes, the shadow names generated by this invention retain column type and semantic category information, enabling remote large models to understand the basic properties of each column even without knowing the real column names, effectively mitigating the accuracy loss caused by semantic loss. Compared to schema pruning schemes, this invention can effectively prevent the leakage of sensitive information and eliminate the risk of secondary inference attacks, even when core sensitive fields are queried, through shadow name replacement and dynamic desensitization mechanisms. Furthermore, the local SLM schema linking mechanism completes the binding of natural language fragments to database columns in the local environment, avoiding the risk of sending the real column names to remote services, further enhancing the reliability of privacy protection. Simultaneously, in terms of sensitive rule configuration, in addition to supporting column-by-column marking, administrators can also perform overall desensitization by semantic category, avoiding the exposure of sensitive columns due to omissions in column-by-column configuration. Attached Figure Description

[0048] Figure 1 This is a system architecture diagram of the present invention, illustrating the interaction between the user front-end, the security middleware layer, and the remote LLM service;

[0049] Figure 2 Flowchart for the offline schema registration phase;

[0050] Figure 3 The flowchart for the online query processing stage shows the complete processing pipeline from user query to SQL execution;

[0051] Figure 4 This is a schematic diagram illustrating the decision-making logic of a dynamic desensitization strategy, showing the process of dynamically adjusting the desensitization intensity based on the level of sensitivity. Detailed Implementation

[0052] The technical solutions of the embodiments of the present invention will now be clearly and completely described with reference to the accompanying drawings. Figure 1 As shown, the overall system architecture of this invention consists of three parts. The user front-end is responsible for receiving natural language queries and displaying the query results; the security middleware layer is deployed on the enterprise intranet and is responsible for schema de-identification mapping, schema linking, prompt assembly, and SQL reverse engineering; the remote LLM service (such as a general large model API) only receives the Prompt after schema de-identification processing, generates de-identified SQL, and returns it.

[0053] The hardware environment in this embodiment adopts a typical data center server architecture, configured with dual Xeon Scalable processors (supporting AVX-512 and above deep learning acceleration instruction sets), 256GB of memory, and a deep learning accelerator card (GPU, such as an NVIDIA RTX 4090 or an equivalent enterprise-level graphics card) with at least 24GB of video memory to support local vector computation and model inference. The operating platform is based on the Ubuntu 22.04 LTS operating system, using the Python 3.10 language environment, and using the PyTorch deep learning framework for embedded model and local SLM deployment and inference acceleration. The database uses PostgreSQL 14 (or above).

[0054] The experimental and business scenario data were selected from the structured backend data of an enterprise-level ERP system. This database contains large-scale real business tables with highly sensitive information, such as "Employee Roster" and "Financial Transaction Log".

[0055] The specific operation of this invention corresponds to the method described in the invention content, and is divided into two main stages: offline schema registration and online query processing. The following detailed explanation uses the "Employee Roster" table (employee_roster) in the selected database as an example. This table contains fields such as employee ID (employee_id), ID card number (id_card), salary (salary), hire date (hire_date), and department ID (dept_id), where dept_id is linked to the "Department" table via a foreign key.

[0056] I. Offline Schema Registration

[0057] The specific process of the offline schema registration phase is as follows: Figure 2 As shown. The administrator submits the DDL of the real database to the security middleware layer, and the system performs three sub-steps: parsing, shadow name generation, and sensitive rule annotation. Its pseudocode is as follows:

[0058]

[0059] II. Online Inquiry Processing

[0060] The specific process of the online query processing stage is as follows: Figure 3 As shown. When a user submits a natural language query, the middleware layer sequentially executes schema linking, dynamic data masking, remote inference, and reverse engineering. Taking the user's query "Check the employee IDs and salaries of all employees in Department 5" as an example, the pseudocode is as follows:

[0061]

[0062] The above content provides a detailed description of the technical solution and specific implementation methods of the present invention. Those skilled in the art should recognize that the above embodiments are merely illustrative examples of the present invention and should not be construed as limiting the scope of protection of the present invention. Any modifications, equivalent substitutions, or improvements made within the framework of the present invention's technical concept, as long as they do not exceed the core mapping and dynamic fallback protection concept, should be covered within the scope of protection of the claims of the present invention.

Claims

1. A method for protecting privacy through dynamic semantic mapping in a text-to-SQL conversion system, characterized in that, The method includes: (1) Deploy a local security middleware layer between the user front-end and the remote large model service, and isolate the real database schema information from the remote large model service through the security middleware layer; (2) Perform offline schema registration preprocessing: parse the DDL statements of the target database, extract multi-dimensional feature information of each table and column, generate semantically preserved shadow names for each column based on the extracted feature information, establish a deterministic bidirectional mapping table between real schema elements and shadow names, and configure desensitization rules and mark the fields to be forcibly desensitized according to the enterprise security policy. (3) Execute the entire online query process: Receive the user's natural language query request, complete the semantic binding and confidence assessment of the query content and database columns locally, perform hierarchical dynamic desensitization based on desensitization rules, binding confidence results and sensitive column density calculation, assemble the desensitization prompt and send it to the remote large model, receive the returned desensitization SQL statement, perform reverse restoration and compliance verification on the desensitization SQL through a two-way mapping table, generate an executable standard SQL statement and return the query results.

2. The method according to claim 1, characterized in that, The process of extracting multi-dimensional feature information of each table and column, generating shadow names, and establishing a bidirectional mapping table includes at least the following: (1) Parse and extract the SQL data type of each column from the DDL statement and map it to a predefined type code. Obtain the semantic category of each column through local embedding model matching. At the same time, parse and identify the structural roles of each column such as primary key and foreign key and store the association information. (2) Generate a unique shadow name for each column according to the fixed rule of "type prefix_semantic category_serial number", and simultaneously establish a forward mapping table and a reverse mapping table corresponding to the real column name and the shadow name, which are used for de-identification replacement and SQL restoration respectively.

3. The method according to claim 1, characterized in that, The process of configuring de-identification rules and marking mandatory de-identification fields according to the enterprise security policy specifically includes: (1) Supports column-by-column forced desensitization configuration, which directly marks the single column specified by the administrator as a field to be forcibly desensitized; (2) Supports overall semantic category desensitization configuration, which automatically marks all columns under the semantic category specified by the administrator as fields subject to mandatory desensitization; (3) All fields that are forced to be de-identified are subject to shadow name replacement in all scenarios and are not affected by the confidence level of dynamic de-identification.

4. The method according to claim 1, characterized in that, The process of completing the semantic binding and confidence assessment of query content with database columns locally, and performing dynamic de-identification processing includes: (1) Extract reference fragments of database columns involved in user queries through a small-scale language model deployed locally, complete semantic matching and binding between reference fragments and database columns using a local embedding model, output the confidence score of the corresponding binding relationship, and mark columns with confidence scores below a preset threshold as desensitized objects. (2) Perform shadow name replacement unconditionally on the fields that are forced to be desensitized, count the density of sensitive columns in the columns involved in the current query, perform hierarchical dynamic desensitization based on the density threshold and binding confidence, and synchronously replace the corresponding reference fragments in the user query text to complete the desensitization Prompt assembly.

5. The method according to claim 1, characterized in that, The process of reverse restoration and compliance verification of the de-identified SQL through a two-way mapping table includes: (1) Identify the shadow names in the de-identified SQL and replace them with the real column names through the reverse mapping table. For invalid shadow names without matching mapping, stop the current query and return an error message to the user. (2) Verify the compliance of the foreign key JOIN relationship in the restored SQL and correct the errors. Finally, complete the SQL syntax verification and generate standard SQL statements that can be executed in the real database.

6. A dynamic semantic mapping privacy protection system for a text-to-SQL conversion system implementing the method of any one of claims 1 to 5, characterized in that, The system is deployed in a local secure environment, including: (1) Security middleware control module, responsible for isolating real schema information from remote large model services and scheduling the entire process interaction; (2) Offline Schema Registration Module, responsible for DDL parsing, multi-dimensional feature extraction, shadow name generation, bidirectional mapping table construction and desensitization rule configuration; (3) Local Schema Linking Module, responsible for extracting reference fragments from user queries, semantic matching and binding, and confidence assessment; (4) Dynamic desensitization processing module, which is responsible for performing hierarchical dynamic desensitization based on desensitization rules, binding results and sensitive column density, and assembling desensitization prompts; (5) SQL Reverse Restoration and Verification Module, responsible for restoring the shadow name of the de-identified SQL, verifying and correcting the relationship and verifying the syntax compliance; (6) Result interaction module, which is responsible for sending executable SQL to the database and returning the query results to the user front end.

Images