Static Checking of Dynamically Generated Queries in Database

Abstract Many data-intensive applications dynamically construct and execute que- ries in response to client requests. Java servlets, for example, can create string representations of SQL queries and then, using JDBC, send the que- ries to a database server for execution. The servlet programmer,enjoys static checking via Java’s strong type system. However, the Java type system does little to check,for possible errors in the dynamically generated SQL query strings. Thus, a type error in a generated selection query (e.g., comparing a string attribute with an integer) can result in an SQL runtime exception. Currently, such defects must be rooted out through careful testing; worse yet, if they are not, the customer might discover them at runtime. In this paper, a sound, static, program analysis technique to verify the correctness of dy- namically generated query strings is presented. The analysis technique will be described, and soundness results for the static analysis algorithm will beprovided. The paper also describes the details of a prototype tool based onthe algorithm, and includes several illustrative defects found in senior software-engineering student-team projects, online tutorial examples, and a real world purchase order system written by the author.