Abstract
Query reverse engineering (QRE) aims to synthesize a SQL query to connect a given database and result instance. A recent variation of QRE is where an additional input, an opaque executable containing a ground-truth query, is provided, and the goal is to non-invasively extract this specific query through only input-output examples. This variant, called Hidden Query Extraction (HQE), has a spectrum of industrial use-cases including query recovery, database security, and vendor migration. The reverse engineering (RE) tools developed for HQE, which are based on database mutation and generation techniques, can only extract flat queries with key-based equi joins and conjunctive arithmetic filter predicates, making them limited wrt both query structure and query operators. In this paper, we present Xpose, a HQE solution that elevates the extraction scope to realistic complex queries, such as those found in the TPCH benchmark. A two-pronged approach is taken: (1) The existing RE scope is substantially extended to incorporate union connectors, algebraic filter predicates, and disjunctions for both values and predicates. (2) The predictive power of LLMs is leveraged to convert business descriptions of the opaque application into extraction guidance, representing ``forward engineering" (FE). The FE module recognizes common constructs, such as nesting of sub-queries, outer joins, and scalar functions. In essence, FE establishes the broad query contours, while RE fleshes out the fine-grained details. We have evaluated Xpose on (a) E-TPCH, a query suite comprising the complete TPCH benchmark extended with queries featuring unions, diverse join types, and sub-queries; and (b) the real-world STACK benchmark. The experimental results demonstrate that its bi-directional engineering approach accurately extracts these complex queries, representing a significant step forward with regard to HQE coverage.
Abstract (translated)
查询逆向工程（Query Reverse Engineering，QRE）旨在合成一个SQL查询，以连接给定的数据库和结果实例。最近出现的一种QRE变体是提供了一个额外输入——包含真实查询的不透明可执行文件，并且目标是非侵入性地通过仅使用输入输出示例提取该特定查询。这种被称为隐藏查询提取（Hidden Query Extraction，HQE）的方法在包括查询恢复、数据库安全和供应商迁移等众多工业应用场景中都有广泛用途。然而，为HQE开发的逆向工程工具基于数据库变异和生成技术，只能提取具有键值相等连接且带有算术过滤谓词的平面查询，这使得它们在查询结构和操作符方面都受到限制。 本文介绍了一种名为Xpose的新解决方案，它扩展了HQE的提取范围至现实中的复杂查询，例如TPCH基准测试中发现的查询。Xpose采取了一个两管齐下的方法：（1）大幅扩大现有的逆向工程范围以包含联合连接器、代数过滤谓词以及值和谓词的析取；（2）利用大型语言模型（LLMs）的预测能力将不透明应用的商业描述转换为提取指导，这一过程代表了“正向工程”（Forward Engineering，FE）。FE模块可以识别诸如子查询嵌套、外连接和标量函数等常见构造。简而言之，FE确立了整个查询的大致轮廓，而逆向工程则填充详细的细节。 我们已在以下两个方面评估了Xpose：(a) E-TPCH——一个由完整的TPCH基准测试扩展而来的一组查询集，其中包含联合、多样化的连接类型和子查询；(b) 实际世界中的STACK基准。实验结果表明，Xpose的双向工程方法能够准确提取这些复杂查询，并在HQE覆盖范围方面取得了显著进展。
URL
https://arxiv.org/abs/2504.10898
