TY - GEN
T1 - Understanding and Detecting SQL Function Bugs Using Simple Boundary Arguments to Trigger Hundreds of DBMS Bugs
AU - Fu, Jingzhou
AU - Liang, Jie
AU - Wu, Zhiyong
AU - Zhao, Yanyang
AU - Li, Shanshan
AU - Jiang, Yu
N1 - Publisher Copyright:
© 2025 Copyright held by the owner/author(s).
PY - 2025/3/30
Y1 - 2025/3/30
N2 - Built-in SQL functions are crucial in Database Management Systems (DBMSs), supporting various operations and computations across multiple data types. They are essential for querying, data transformation, and aggregation. Despite their importance, the bugs in SQL functions have caused widespread problems in the real world, from system failures to arbitrary code execution. However, the understanding of the bug characteristics is limited. More importantly, conventional function testing methods struggle to generate semantically correct SQL test cases, while DBMS testing efforts are hard to measure built-in SQL functions. This paper presents a comprehensive study of 318 built-in SQL function bugs, shedding light on their characteristics and root causes. Our investigation reveals that 87.4% of these bugs were caused by improper handling of boundary values of arguments. The boundary values of arguments come from three sources: literal values, type castings, and nested functions. By studying the bugs from three sources, we summarized 10 SQL patterns of bug-inducing queries. Moreover, we designed Soft, a testing tool based on the patterns to test seven widely used DBMSs, including PostgreSQL, MySQL, and ClickHouse. Soft discovered and confirmed 132 previously unknown SQL function bugs. The DBMS vendors took these bugs seriously and fixed 97 bugs in three days. For example, the CTO of ClickHouse commented on one bug: “We must fix it immediately or get rid of this function.”
AB - Built-in SQL functions are crucial in Database Management Systems (DBMSs), supporting various operations and computations across multiple data types. They are essential for querying, data transformation, and aggregation. Despite their importance, the bugs in SQL functions have caused widespread problems in the real world, from system failures to arbitrary code execution. However, the understanding of the bug characteristics is limited. More importantly, conventional function testing methods struggle to generate semantically correct SQL test cases, while DBMS testing efforts are hard to measure built-in SQL functions. This paper presents a comprehensive study of 318 built-in SQL function bugs, shedding light on their characteristics and root causes. Our investigation reveals that 87.4% of these bugs were caused by improper handling of boundary values of arguments. The boundary values of arguments come from three sources: literal values, type castings, and nested functions. By studying the bugs from three sources, we summarized 10 SQL patterns of bug-inducing queries. Moreover, we designed Soft, a testing tool based on the patterns to test seven widely used DBMSs, including PostgreSQL, MySQL, and ClickHouse. Soft discovered and confirmed 132 previously unknown SQL function bugs. The DBMS vendors took these bugs seriously and fixed 97 bugs in three days. For example, the CTO of ClickHouse commented on one bug: “We must fix it immediately or get rid of this function.”
KW - DBMS
KW - SQL Function
KW - Vulnerability Detection
UR - https://www.scopus.com/pages/publications/105002249218
U2 - 10.1145/3689031.3696064
DO - 10.1145/3689031.3696064
M3 - 会议稿件
AN - SCOPUS:105002249218
T3 - EuroSys 2025 - Proceedings of the 2025 20th European Conference on Computer Systems
SP - 1061
EP - 1076
BT - EuroSys 2025 - Proceedings of the 2025 20th European Conference on Computer Systems
PB - Association for Computing Machinery, Inc
T2 - 20th European Conference on Computer Systems, EuroSys 2025, co-located 30th ACM International Conference on Architectural Support for Programming Languages and Operating Systems, ASPLOS 2025
Y2 - 30 March 2025 through 3 April 2025
ER -