Static Checking of Dynamically Generated Queries in Database Applications - Gould, Carl; Su, Zhendong; Devanbu, Premkumar (International Conference on Software Engineering: Proceedings of the 26th International Conference on Software Engineering; 23-28 May 2004, 2004)
Many data-intensive applications dynamically constructqueries in response to client requests and execute them.Java servlets, e.g., can create string representations ofSQL queries and then send the queries, using JDBC, to adatabase server for execution. The servlet programmer enjoysstatic checking via Javaýs strong type system. However,the Java type system does little to check for possible errorsin the dynamically generated SQL query strings. Thus,a type error in a generated selection query (e.g., comparinga string attribute with an integer) can result in an SQLruntime exception. Currently, such defects must be rootedout through careful testing, or (worse) might be found bycustomers at runtime. In this paper, we present a sound,static, program analysis technique to verify the correctnessof dynamically generated query strings. We describe ouranalysis technique and provide soundness results for ourstatic analysis algorithm. We also describe the details of aprototype tool based on the algorithm and present severalillustrative defects found in senior software-engineeringstudent-team projects, online tutorial examples, and a real-worldpurchase order system written by one of the authors.