--- title: 'DeKeySQL: Dual Approaches in SQL Processing' url: https://www.emergentmind.com/topics/dekeysql type: topic --- # DeKeySQL: Dual Approaches in SQL Processing DeKeySQL is a name used in arXiv literature for two distinct SQL-oriented systems. In "On-the-fly AES Decryption/Encryption for Cloud SQL Databases" [1512.06423], it denotes an on-the-fly AES approach for outsourced encrypted cloud SQL databases in which client-side AES256 encryption, a UDF-capable cloud DBMS, and a protected DBMS runtime are combined so that query evaluation can proceed over decrypted run-time values while persistent storage remains ciphertext-only. In "DeKeyNLU: Enhancing Natural Language to SQL Generation through Task Decomposition and Keyword Extraction" [2509.14507], it denotes a three-stage Retrieval-Augmented Generation (RAG) pipeline for natural-language-to-SQL (NL2SQL) built around user question understanding, entity retrieval, and SQL generation with iterative revision. The shared label therefore refers to different technical objects: one addresses secure outsourced SQL execution, and the other addresses model-centric SQL synthesis from natural language. ## 1. Terminological scope and research contexts The two uses of DeKeySQL occupy different layers of the database stack. The earlier usage concerns ciphertext-resident cloud databases and asks how a cloud DBMS can retain the functional capabilities of a plaintext SQL DBMS while storing only encrypted data. The later usage concerns NL2SQL and asks how task decomposition and keyword extraction can improve execution accuracy on benchmarks such as BIRD and Spider. The same term thus spans cryptographic database processing and LLM-based query generation [1512.06423] [2509.14507]. | Usage of DeKeySQL | Problem setting | Core elements | |---|---|---| | On-the-fly AES DeKeySQL | Outsourced encrypted cloud SQL databases | Client-side AES256, AESE/AESD UDFs, key-and-plaintext vault, ciphertext-only persistent storage | | RAG-based DeKeySQL | NL→SQL generation | User Question Understanding, entity retrieval, SQL generation, revision, DeKeyNLU | For technical reading, disambiguation by arXiv identifier is essential. The 2015 system is defined by a threat model, runtime protections, and encryption modes. The 2025 system is defined by a pipeline architecture, a supporting dataset, and benchmarked execution accuracy. Any discussion of “DeKeySQL” without this qualification risks conflating security-oriented encrypted query processing with language-model-mediated SQL generation. ## 2. On-the-fly AES DeKeySQL for cloud SQL databases In the 2015 usage, DeKeySQL is an architecture for client-side AES256 encryption in a cloud SQL DB. The high-level components are clients, a TLS/SSL encrypted channel, a cloud DBMS core with a UDF layer and a key-and-plaintext vault, and insecure persistent storage containing ciphertext files and indexes. The SQL engine itself is described as “unmodified,” with AESE and AESD provided at the UDF layer [1512.06423]. The workflow is explicit. First, a client constructs SQL plus metadata, where the metadata consist of one or more 256-bit AES keys and identifiers specifying which columns are deterministic versus probabilistic. Second, SQL and keys are sent through the TLS channel to the parser and UDF loader in the cloud DBMS. Third, the UDF layer instantiates run-time variables \(K_1 \ldots K_m\) and flags `DETERMINISTIC` or `PROBABILISTIC`, with the stipulation that keys never touch disk. Fourth, the execution engine produces a “plaintext plan,” as if the table held clear values. Fifth, when the plan reads a ciphertext column \(C\), deterministic columns may support index lookups or joins directly on \(C\) without decryption, whereas other cases invoke `AESD(C, K_{\mathrm{col}})` to obtain run-time plaintext for comparisons, predicates, or aggregation. Sixth, `UPDATE` and `INSERT` invoke `AESE(plaintext, K_{\mathrm{col}})` to produce fresh ciphertext. Seventh, at the end of the statement, whether commit or rollback, all run-time variables holding keys and decrypted values are zeroed in memory. This design is intended to let the cloud DBMS evaluate SQL over transient plaintext while keeping disk-resident state encrypted. The abstract characterizes the result as functionally offering to a cloud DBMS the capabilities of a plaintext SQL DBMS, subject to a trust assumption about the security of run-time values. ## 3. Threat model, runtime protection, and encryption modes The security model of the on-the-fly AES system trusts clients and their local key-stores, treats cloud storage—including disk and backups—as fully compromised or adversarial, and assumes that the cloud DBMS runtime is protected by a “vault,” implemented either as software-only moving-target defense or as an Intel SGX enclave. The network path is protected by standard TLS/SSL, and the description specifies that there are no plaintext keys on the wire. Runtime defenses include code and data layout randomization with frequent rebases so that an attacker cannot locate the UDF key buffer or decrypted pages, and SGX enclave execution in which UDF calls and key storage run inside enclave pages that cannot be read by ring0 or the hypervisor. After query execution, memory slots that held AES keys or plaintext cells are explicitly overwritten [1512.06423]. Two encryption modes are defined. Deterministic AES is described as ECB-style and uses no randomness: $$ C = E_K(m) $$ Identical plaintext blocks produce identical ciphertext blocks. This mode is intended for equality predicates, joins, and indexed lookups over \(C\), particularly in OLTP queries. The abstract states that deterministic encryption may have no storage overhead. In the detailed formulation, if the block size is \(B = 16\,\mathrm{B}\) and the plaintext cell length is \(\ell \le B\), then the ciphertext-cell size is \(B\), the overhead per cell is \((B-\ell)/\ell\), and the total table size is approximately \(\lceil \mathrm{RawSize}/B \rceil \cdot B\). Probabilistic AES is described as CBC-style and prepends a random IV of size \(B\) bits: $$ C = (IV \parallel E_K(IV \oplus m)) $$ Its stated use cases are high-entropy, confidentiality-first columns such as salary and SSN, and it is said to resist frequency analysis. In this mode, only plaintext queries with the key can perform joins or predicates. If plaintext grouping is per-cell and each \(\ell \le B\), then the ciphertext size becomes \(B\) for the IV plus \(B\) for the encrypted block, or \(2B\) total. The corresponding overhead is \((2B-\ell)/\ell \approx 100\%\)–\(200\%\), and the abstract summarizes this as doubling the DB storage. The limitations are equally direct. The trust boundary shifts to the DBMS runtime, so insider or side-channel attacks on memory could leak keys if defenses fail. The discussion also notes that range queries or `ORDER BY` on deterministic encryption leak order, whereas probabilistic encryption disallows range and index use. ## 4. Performance characteristics, deployment, and comparative position The performance model for AES256 with AES-NI acceleration is linear in the total number of processed bytes \(n\): $$ T_{\mathrm{enc}}(n) = \alpha \cdot n + \beta $$ with \(\alpha \approx 0.25\,\mathrm{ns/byte}\), corresponding to roughly \(4\,\mathrm{GB/s}\), and \(\beta \approx 2\)–\(5\,\mu\mathrm{s}\) for key schedule and call overhead. The empirical benchmark on an Intel Core i5 with AES-NI enabled reports throughput of approximately \(4{,}000\,\mathrm{MB/s}\), or about \(250\) million 16-byte blocks per second. Decrypting \(100{,}000\) blocks is estimated as approximately \(0.405\,\mathrm{ms}\). By comparison, software-only AES such as Twofish is reported at about \(300\,\mathrm{MB/s}\), approximately \(8\times\) slower, and Paillier homomorphic processing is reported as about \(1.2\,\mathrm{s}\) for \(100\mathrm{K}\) additions plus \(50\,\mathrm{ms}\) client decryption and summation, two orders of magnitude slower. Scanning ciphertext rather than plaintext from SSD or HDD is said to add at most \(15\%\) extra cost, while on-the-fly decryption is characterized as negligible relative to disk I/O for large scans [1512.06423]. Deployment is framed as straightforward on a UDF-capable cloud-SQL engine. The specified UDFs are: ```sql AESE(c VARBINARY, key VARBINARY) RETURNS VARBINARY AESD(c VARBINARY, key VARBINARY) RETURNS VARBINARY ``` implemented in C as a shared library backed by a hardware-accelerated AES256 library. The description states that an existing cloud SQL DBMS with UDF support should do. Key management is client-centric: the client holds a master key store in an HSM or local KMS, each column `Tbl.Col` has a column key \(K_{\mathrm{col}}\), and metadata include \((\text{column}, \text{key\_id})\) pairs. Rotation is performed offline by generating a new random key \(K'_{\mathrm{col}}\), re-encrypting with ```sql UPDATE Tbl SET Col = AESE(AESD(Col, Kcol), K′col); ``` dropping the old key, and updating metadata to the new key identifier. The comparative discussion places this DeKeySQL between traditional client-side encryption and homomorphic encryption. Relative to traditional client-side encryption, the cloud can evaluate SPJ and OLAP operators on decrypted streams inside the protected runtime, and only per-statement, per-column keys pass through. Relative to homomorphic schemes such as Paillier and FHE, it is described as orders of magnitude faster and as supporting general SQL, including joins, `GROUP BY`, and `ORDER BY`, at the cost of having to trust the DBMS runtime. ## 5. DeKeySQL as a RAG-based NL→SQL pipeline In the 2025 usage, DeKeySQL is a three-stage Retrieval-Augmented Generation pipeline for NL→SQL. The first stage, User Question Understanding (UQU), takes a raw user question \(Q\) and outputs a task decomposition consisting of a single main task \(T_0\) and a sequence of sub-tasks \(\{T_1,\ldots,T_k\}\), together with keyword sets \(K_{\mathrm{obj}}\) and \(K_{\mathrm{impl}}\). The object keywords map to schema elements, and the implementation keywords map to filter conditions. This module is implemented with an LLM fine-tuned on DeKeyNLU using a two-step Chain-of-Thought prompt that separates main-task generation from sub-task refinement [2509.14507]. The second stage is entity retrieval. Each keyword \(w\) is embedded as a vector \(\mathbf e_w\), and a retriever searches a Chroma vector store for top-\(n\) candidate entities \(D = \{d_1,\ldots,d_n\}\). Two complementary retrieval methods are used. For high-cardinality values, the pipeline uses MinHash together with Jaccard similarity, $$ J(A,B)=\frac{|A\cap B|}{|A\cup B|} $$ approximated by MinHash signatures. For textual schema items, it uses BM25: $$ \mathrm{BM25}(q,D) = \sum_{i=1}^{|q|} \mathrm{IDF}(q_i)\, \frac{f(q_i,D)(k_1+1)}{f(q_i,D)+k_1\left(1 - b + b\frac{|D|}{\mathrm{avgDL}}\right)}. $$ A re-ranker then rescales the top-\(n\) candidates using cosine similarity, $$ \mathrm{sim}(\mathbf e_w,\mathbf e_d) = \frac{\mathbf e_w \cdot \mathbf e_d}{\|\mathbf e_w\|\,\|\mathbf e_d\|}, $$ and returns the two most similar entities for each keyword. The third stage is SQL generation and revision. A second LLM, exemplified in the description by GPT-4o, is prompted with a schema snippet, retrieved entities, task decomposition, and the original question to produce an initial SQL program \(S^0\). If \(S^0\) fails execution, the error message \(\epsilon\) and the failed SQL are fed back to an LLM prompt that outputs a corrected SQL \(S^\*\), iterating up to a fixed threshold, usually at most five rounds. The end-to-end flow is summarized as $$ Q \rightarrow [\mathrm{UQU}] \rightarrow \{T_0,T_1,\ldots;K_{\mathrm{obj}},K_{\mathrm{impl}}\} \rightarrow [\mathrm{Retrieval}] \rightarrow \text{schema snippets} \rightarrow [\mathrm{Generation}] \rightarrow \text{SQL} \rightarrow [\mathrm{Execution} \rightarrow \mathrm{Revision}] \rightarrow \text{final SQL}. $$ This architecture makes task decomposition, schema grounding, and post-hoc execution repair explicit stages of the NL2SQL process rather than leaving them implicit in a single generation prompt. ## 6. DeKeyNLU, fine-tuning procedure, and empirical results The supporting dataset for the NL2SQL DeKeySQL is DeKeyNLU, which contains 1,500 NL/SQL pairs sampled from BIRD. Its annotation schema has two parts. For task decomposition, each question is split into a main task and 1–5 sub-tasks corresponding to elements such as `JOIN`, `WHERE`, and `GROUP BY`. For keyword extraction, annotations distinguish object keywords, which are table or column names, from implementation keywords, which are filter values or actions encoded as key–value pairs. The annotation process begins with GPT-4o pre-annotation using CoT and few-shot prompting, continues through three expert annotators in three rotational rounds, and ends with Likert scoring from 1 to 5, with all cases scoring below 4 collaboratively reworked. Inter-annotator agreement is reported as Krippendorff’s \(\alpha = 0.762\). The train, validation, and test split is \(70\%/20\%/10\%\). For main tasks per question, \(68.2\%\) have 1, \(24.9\%\) have 2, and \(6.9\%\) have at least 3. For sub-tasks, \(31.7\%\) have 1–2, \(60.0\%\) have 3–4, and \(8.3\%\) have at least 5. For keywords per question, \(20.3\%\) have 1–2, \(60.6\%\) have 3–4, and \(19.2\%\) have at least 5 [2509.14507]. Fine-tuning is concentrated in the UQU and generation modules. The UQU module is fine-tuned with supervised LoRA using rank \(=64\), \(\alpha=16\), dropout \(=0.05\), one epoch, batch size \(=1\), learning rate \(=2\times10^{-4}\), and 4-bit precision. The models listed for UQU include GPT-4o-mini, GPT-4, Mistral-7B, LLaMA3-8B, and Baichuan2-7B/13B. The loss is standard sequence cross-entropy: $$ \mathcal{L}_{\mathrm{CE}}(\theta) = -\sum_{t=1}^T \log P_\theta\bigl(y_t \mid y_{