arXiv CSopen access

Static Type Checking for Database Access Code

arXiv:2605.02569v1 [cs.DB] 4 May 2026

Static Type Checking for Database Access Code Thomas James Kirz

Werner Dietl

University of Passau Passau, Germany [email protected]

University of Waterloo Waterloo, Canada [email protected]

Mattias Ulbrich

Stefanie Scherzinger

Karlsruhe Institute of Technology Karlsruhe, Germany [email protected]

University of Passau Passau, Germany [email protected]

Abstract JDBC remains a key technology for database access in Java applications. Since the database dictionary and the Java type system have distinct scopes, developers inevitably need to deal with bugs in SQL-to-Java type mappings. We propose an extension of the Java compiler, based on the established Checker Framework, which allows us to bridge this gap. Our approach verifies statically that the correct Java types are used when setting prepared statement parameters or when getting values from result sets. This allows us to lift a practically important class of runtime errors to compile time. Our approach is sound and, therefore, is guaranteed not to produce false negatives. Our prototype implementation also offers a degraded mode for type-checking legacy software, if developers are only interested in a subset of errors. Our experiments show that our approach detects a wide range of type mismatches in realworld application code and can indeed prevent errors which might otherwise surface as runtime errors. From the perspective of the developer, our approach is extremely lightweight: it processes the unmodified Java code, yet developers may add their own annotations. This allows us to perform type-checking even across method boundaries, whereas commercial developer tools are restricted to local checks. Finally, we show that we can type-check real-world JDBC software with reasonable overhead during compilation.

1

Introduction

Database applications form the backbone of modern information technology, and Java is a widely used language for their implementation. Introduced nearly 30 years ago, JDBC [3] remains the go-to framework for Java-to-SQL interaction. JDBC is part of the Java Standard Edition API and is actively maintained by Oracle. Thus, JDBC-based applications enjoy long-term maintainability [10]. This is particularly important in enterprise applications, which often remain in production for decades. In the early 2000s, the first object-relational mappers (ORMs) became popular in response to the verbosity of raw JDBC. ORMs sit on top of JDBC and abstract SQL through object mappings. While the JDBC API has remained relatively unchanged over time, the ORM landscape has seen multiple generations, frameworks, and major redesigns. In consequence, some developers outright dismiss ORMs in favor of plain vanilla JDBC. Developers who do choose to use ORMs oftentimes blend ORM code with raw JDBC code [15, 22, 35], since JDBC allows to formulate complex SQL

queries that mappers may not express as easily or execute as efficiently [9]. Overall, JDBC has stood the test of time and remains crucial in database application development. However, the apparent gap between the Java and SQL type systems makes JDBC code prone to certain kinds of bugs. The examples below illustrate common pitfalls with database access code. Listing 1: Bugs in JDBC database access code. 1 2 3 4 5 6 7 8 9 10 11 12

// 1. Typo in statement , table called " employee " connection . prepareStatement (" Select * from employe "); // 2. Wrong type of setter , 4 th column is VARCHAR (100) PreparedStatement ps = connection . prepareStatement ( " INSERT INTO ROOMS VALUES (? ,? ,? ,?)"); ... ps . set Boolean (4 , room . isBooked ()); // 3. Wrong type of getter , result of count (*) is BIGINT String sql = " select count (*) from USERS "; ... int result = resultSet . get Int (1);

Example 1.1. Listing 1 shows snippets from open source Java projects.1 Line 2 prepares a SQL statement. The typo in the table name causes a runtime exception. Approaches to detecting malformed SQL statements already exist in published work [4, 11, 23, 24, 35, 37] and are even implemented in commercial tools, e.g., “IntelliJ IDEA Ultimate” [26]. However, the other problems above involve JDBC setter- and getter-calls, and are not detectable by state-of-the-art tools. In the second example, a Java Boolean is written into a column with SQL type VARCHAR(100). While not recommended by the JDBC specification, this mapping is nevertheless supported by some JDBC drivers: these may then carry out an implicit type conversion without raising a runtime error, which is obviously risky. This also constitutes a portability problem, as migrating to an new JDBC driver may also cause new runtime errors to materialize. The third example is similarly subtle. The query in line 11 returns a BIGINT (i.e., 64-bit), yet the getter-call retrieving the value only supports 32-bit Java integers. Again, this mapping is not recommended, but may be supported by some drivers. These may silently truncate the value, causing the problem to go unnoticed. A call to getLong(1) would have been the recommended choice. 1

Snippet 1 from https://github.com/sayedabdul- aziz/JDBC- Course, snippets 2 and 3 from https://github.com/iluwatar/java-design-patterns. Snippets edited for readability.

Thomas James Kirz, Werner Dietl, Mattias Ulbrich, and Stefanie Scherzinger

Practical relevance. The fact that that getter- and setter-calls in JDBC application code are not systematically being type-checked is a practically relevant problem: (1) Database access code is somewhat of a blind spot in software engineering. Test coverage for database-related code is often poor [20], so type mismatches may materialize in production systems for the first time. (2) In the software stack, the database schema often acts as a “dependency magnet” [2]: The strong coupling of the database schema with the application code [40] causes SQL queries to “break” when the database schema evolves [14]. (3) Legacy JDBC applications underpin large enterprise systems. During decades of use, they will have to be migrated to new platforms, operating systems, database versions, and database drivers. This is typically done by teams other than the original developers, which amplifies the need for automated tools dedicated to analyze the code of database applications. (4) Via “vibe coding”, database application code is generated by AI coding assistants. Tools that can verify code correctness at compile time will play a crucial role in this new programming paradigm and are expected to become increasingly important. Solution design. In this paper, we describe how to extend the Java type system with new SQL-specific types. This allows for static type-checks of getter/setter calls and lifts a practically important class of JDBC-related runtime errors to compile time: Code that passes type checking is guaranteed not to throw runtime exceptions related to type mismatches in JDBC getter/setter calls, nor will it carry out unsafe type conversions. We further guarantee the absence of getter/setter invocations with invalid parameter indices, or setters that use invalid column names. Existing approaches cannot do this. To be able to carry out these checks, our approach automatically infers SQL-related type information in Java code, as derived from the database schema. That is, application developers can typecheck their code as is, without any changes. To demonstrate the immediate practicality of our solution, we have implemented our static analysis in our prototype tool Oopsie2 . Oopsie has been designed to seamlessly integrate into the developer workflow: The minimal setup required is to simply configure access to the database data dictionary. Oopsie type-checking is then done during code compilation. Contributions. This paper makes the following contributions.

• We present our type checker Oopsie, an extension to the Checker Framework [39]. Oopsie relies on annotation-based type checking and statically detects problems with JDBC getters/setters that go unnoticed in existing analyzers. We describe our approach for automatically inferring type annotations for JDBC database applications, given access to the database schema. • Type-checking with Oopsie is designed to be sound. However, if Oopsie is applied to legacy code, it can be run in degraded mode, in which unsupported, dialect-specific SQL features do not raise a compile time error, but are degraded to a warning. Subsequent getter/setter calls for such queries do not raise any further errors. This excludes the affected queries from the type guaranties, but still provides practical benefits when applied to existing code. 2 Originally, the project was named the “Optional Prepared Statement Checker (OPSC)”, but as the project outgrew this initial scope, we only kept the homophone Oopsie.

• Ours is the first approach for analyzing the SQL-related calls of JDBC code capable of modular, intra-procedural type checking: The type checker analyzes one method at a time, which allows it to scale well. Our approach also supports intra-procedural static analysis where type-checking getter/setter-calls requires an analysis across method boundaries, which is often not supported by other static analyses. Developers may optionally add manual annotations to extend the reach of the tool. • In our experiments, we type-check a diverse set of Java applications, including real-world software. Our experiments show that our approach can detect type mismatches more precisely than the commercial reference tool. We further show that adding manual annotations is a very reasonable effort, enabling us to type-check up to 70%–100% of analyzable getter-calls in the Java projects under study. Finally, we show that runtime and memory overhead during code compilation are reasonable enough for Oopsie to be used in practical software development. Structure This article is structured as follows. We review the preliminaries on JDBC and the Checker Framework in Section 2. We discuss related work in Section 3. In Section 4, we present our static analysis framework Oopsie. Section 5 presents our experiments with working open source Java code. We discuss our insights in Section 6. We then conclude with an outlook on future work.

2 Preliminaries 2.1 JDBC Basics We introduce the JDBC API from the developer point-of-view and refer to the JDBC specification [3] for details. In static code analysis, we target two Java interfaces that represent SQL statements, Statement and PreparedStatement, and further the JDBC interface ResultSet, which represents the results of a SQL query. A Statement, as below, takes a SQL command string and sends it to the database for execution. This may produce a ResultSet. 1 2 3 4 5 6 7 8

Statement stmt = conn . createStatement (); String sql = " SELECT label FROM warehouse "; ResultSet rs = stmt . executeQuery ( sql ); while ( rs . next ()) { int label = rs . getInt (1); // must match SQL type System . out . println ( label ); }

The ResultSet is processed by iterating over its rows with next(), and retrieving individual column values through type-appropriate getter methods. Each getter requires either the column name or the 1-based column index as argument. Thus, the Java type of the getter-call in line 6 must be compatible with the SQL type of the attribute in the database schema. The database system can precompile SQL statements. In such prepared statements, the placeholders (“?”) mark the positions of the parameters. Concrete values are later supplied via setter methods, such as setInt or setDate. The Java type is encoded in the method name and determines how the value is transmitted to the database. Parameters are identified by their position (index) in the SQL statement. Because the statement is precompiled, the execute method does not take any input parameters. This is illustrated by the code listing below. 1 String sql = " SELECT label FROM warehouse WHERE qty = ?";

Static Type Checking for Database Access Code

SQL Type

Recommended getter

Supported getter

CHAR VARCHAR INTEGER BIGINT

getString getString getInt, getLong getLong

getInt, getLong, getBoolean getInt, getLong, getBoolean getString, getBoolean getString, getInt, getBoolean

where the parameter arities in the prepared statement do not match. Such bugs can already be detected by existing developer tools. Example 2.2 (Problems with getters). Below, we attempt to access the third column of a query result and the column id. Since there are only two columns and none of them are called id, a runtime error is raised. We attempt to access the value of the first column as Java int, yet the column has SQL type VARCHAR. This is not JDBC-recommended, yet existing developer tools do not detect this.

Figure 1: JDBC 4.3 type conversions [3] for getters (excerpt). 2 3 4 5 6 7

PreparedStatement ps = conn . prepareStatement ( sql ); ps . setInt (1 , quantity ); // Bind value of parameter '? ' ResultSet rs = ps . executeQuery (); while ( rs . next ()) { System . out . println ( rs . getInt (" label ")); }

When executed repeatedly, with different parameter bindings, prepared statements can improve application performance. In addition, they play a crucial in preventing SQL injection [12]. Type compatibility. The JDBC specification distinguishes recommended and supported conversions between Java and SQL types in getters/setters. A recommended conversion is part of the portable core of JDBC, and all conforming JDBC drivers implement these conversions. A driver may also implement a supported conversion, but is not required to do so. For any other conversion, a JDBC driver is not expected to allow it. Attempting such a conversion usually results in a runtime exception. Figure 1 highlights selected recommended and supported conversions for getters. For example, to access a value of SQL-type CHAR, calling getString is recommended (first row in the table), while getInt is only supported. Likewise, the JDBC specification declares recommended and supported conversions for setters. Note that the actual behavior of supported conversions may vary not only across JDBC drivers but also across database systems. This is because drivers often rely on the underlying DBMS for type coercion, and DBMSs differ in their native type systems and casting semantics. By sticking to recommended conversions, developers ensure portability independent of both the driver and the DBMS. JDBC Metadata. Using JDBC, developers can retrieve metadata about the parameters in a prepared statement, and also the result of a query at run time. This provides information such as column names and types. JDBC itself does not infer the schema of the query result, this task is instead delegated to the DBMS. Consequently, different DBMS products may return different metadata for the same query.

2.2

JDBC Problems and Pitfalls

The examples below illustrate common JDBC pitfalls that cannot be caught by the Java compiler. Example 2.1 (Malformed SQL strings). A malformed SQL statement string inevitably causes a runtime error when the query is executed. In the code snippet below, we illustrate the straightforward case of a SQL syntax error (highlighted in red). stmt . executeQuery (" SELECT * FORM warehouse ");

// Typo

Line 2 in Listing 1 shows another malformed SQL statement. It is syntactically correct but uses an incorrect table identifier. Yet another example of a malformed SQL string is shown in Listing 2,

1 2 3 4 5 6

ResultSet rs = stmt . executeQuery ( " SELECT label , qty FROM warehouse "); rs . next (); rs . get Int (1); // wrong type for VARCHAR column rs . getString ( 3 ); // invalid column index rs . getString (" id "); // invalid column label

Example 2.3 (Problems with setters). Problems with setters are similar. Below, the setter in line 3 uses a non-recommended type conversion. Again, existing developer tools cannot detect this. The setter in line 4 uses an invalid index, causing a runtime error. IDEs like IntelliJ IDEA Ultimate can detect mismatches between the number of placeholders in the query string, and the number of parameters set. This flags the issue in line 4. 1 PreparedStatement ps = conn . prepareStatement ( 2 " SELECT label FROM warehouse WHERE qty > ?"); 3 ps . set String (1 , "5"); // wrong type for integer column 4 ps . setString ( 2 , " abc "); // invalid parameter index

Matters of scope. In the examples shown so far, the dynamic or prepared statement is always declared in the same method scope as the corresponding getter or setter calls. This is highly convenient for static code analysis, and even considered best practice (and is actively encouraged by the Java try-with-resources construct used to prevent resource leakage [7]). However, database access code in real-world software frequently crosses method boundaries, yet tools like IntelliJ cannot perform code analysis under these conditions.

2.3

Checker Framework

The Checker Framework [17, 39] is a powerful open-source framework for Java. It is actively used in real-world software development by major companies in industry and by research teams in academia. It allows the implementation of pluggable type systems [8]: Extended type checkers plug into the normal Java compilation process using the annotation processing mechanism provided by the compiler infrastructure. This can be used to statically enforce properties like null-pointer exception freedom and correct usage of String interning [17]. The SQL Quotes Checker can analyze database applications to detect unescaped single quotes in SQL statements [16], a vulnerability for SQL injection. The Checker Framework further allows extended static analyses, such as inferring units of measurement [47] or the combination of type systems with deductive verification [29] that go beyond the scope of typical type systems. A type system (a “checker”) for the Checker Framework consists of four main components which we have adapted and realized for Oopsie in Section 4.3: (1) the definition of the type qualifiers and the type hierarchy: this encodes the facts that are tracked about the program and their relationships; (2) the type introduction rules

Thomas James Kirz, Werner Dietl, Mattias Ulbrich, and Stefanie Scherzinger

that determine the qualified types for all source and bytecode elements; (3) optionally, the rules for enhanced flow-sensitive type refinement, which determines more specific types depending on the control flow of the program; and (4) the type rules that enforce correct behavior based on refined types, by traversing the Abstract Syntax Tree (AST) of the program. A Java project can make use of multiple type systems using the Checker Framework, and information obtained from one type system can be used within another type system. Type annotations. The type qualifiers of a type system are expressed using Java annotations (denoted by an @ sign). These annotations can be parametrized using elements. For example, the builtin Constant Value Checker, which determines the value of a variable (if possible in static analysis), defines an annotation @IntVal with an array element that tracks the value(s) that an integer variable may have. Java code can be annotated manually, as in line 1 in the example below, or inferred automatically by the checker. 1 x = 3; // OK ( legal assignment )

If an input program cannot be typed according to the typing rules as implemented by the type system, the checker plugin raises a compilation error. In the example below, the inferred type of the right-hand side of the assignment in line 2 does not match the manually annotated variable type (line 1). This constitutes an illegal assignment. The program cannot be typed, and an error is raised. 1 x = 5; 2 // Error : incompatible types in assignment .

The Constant Value Checker also introduces a @StringVal annotation to statically determine String values. Our implementation Oopsie, to be introduced later, uses the @IntVal annotation to determine the column arguments of setter and getter calls, as well as @StringVal to extract SQL statement strings from Java code. Modularity. Explicit type annotations are particularly useful in method declarations to achieve modularity—each method is type checked in isolation from all other code, only relying on local type information or information from other method declarations. This makes the type checks modular and intraprocedural, in contrast to other static analysis approaches that are global, require all source code, and perform interprocedural analyses.

3

Related Work

Typing across software layers. The challenge of reconciling two isolated type systems, one in the application layer and one in the database layer, has been widely recognized. Frameworks such as Microsoft’s LINQ [31] demonstrate that strong type safety can be achieved through tight integration of programming and query languages. Very recently, a new proposal has been made to radically re-design query languages for a more seamless experience in application development (e.g., [18]). While these approaches share our overall objective, they require developers to adopt new languages and paradigms, and ultimately, to rewrite existing database applications. In contrast, we target an established and commercially relevant market: database applications written in Java with JDBC, which includes a large number of legacy applications. Static analysis for JDBC applications. A substantial body of work has addressed the static analysis of SQL queries embedded in JDBC

code. This requires access to both the application code and the database schema, typically to detect syntactic and semantic errors in SQL statement strings (e.g., [4, 11, 23, 24, 35, 37]) like the first issue in Listing 1. The challenge lies in analyzing dynamically constructed SQL statements, by combining automata-based techniques with control flow analysis. This work is complementary to ours. Once SQL statement strings have been extracted, a range of additional analyses becomes possible. These include the detection of “SQL smells” [36, 37], of inefficient queries [37], safeguarding applications against breaking schema changes [33], and most prominently, detecting vulnerabilities related to SQL injection (e.g., [12, 44]). Notably, the Checker Framework used in our work also provides a plugin to detect SQL injection attacks. Commercial tools. Professional developer tools also provide support for JDBC development. For instance, the Java IDE “IntelliJ IDEA Ultimate” [26] offers syntax highlighting and schema-aware autocompletion. In the context of our work, IntelliJ can detect obvious arity mismatches in JDBC prepared statements, as shown in Listing 2, as well as obvious index mismatches in JDBC setter calls. This is achieved by comparing the number of placeholders in prepared statements with the indices used in setter access functions. However, such tools are generally limited to very simple Java constructs, and struggle with non-trivial control flow. Listing 2: Mismatched parameter arities (found in [41]) INSERT INTO CUSTOMER ( CUSTOMER_ID , FIRST_NAME , LAST_NAME , SOCIAL_SECURITY , CRT_CLASS , LUID , LUTS ) VALUES (? , ? , ? , ? , ? , ?)

Summary. Existing approaches do not detect type mismatches in JDBC getter and setter calls, like the second and third examples in Listing 1, which our approach handles. While type checking JDBC getter and setter calls has been identified in prior work as a direction for future research [23], we are not aware of any academic or commercial solution to this problem. While IntelliJ IDEA only detects certain index mismatches for setters, our approach detects index mismatches also for getters, as well as type mismatches. Own previous work. A very early version of our prototype implementation Oopsie was presented at the BTW’25 student track program [28]. The preliminary experiments presented do not involve any real-world software and do not explore manual annotations.

4 Static Analysis Framework We first provide an overview of the system architecture of our prototype implementation Oopsie, and then present its internals.

4.1

System Architecture

Figure 2 shows the Oopsie system architecture. Oopsie is designed 1 to be part of the Java build process (⃝). The heart of Oopsie is 2 an extension to the Checker Framework (⃝), which works as a plugin to the Java compiler and extends its capabilities. This extension parses SQL statement strings from JDBC Statements and PreparedStatements in the sources, and connects to a library with access to the database dictionary. At this point, Oopsie can recognized malformed SQL statement strings. 3 further derives the expected SQL types of all paThis library (⃝) rameters in parameterized statements, as well as the column names and SQL types of the attributes in the query result. Oopsie captures

Static Type Checking for Database Access Code

 Java compiler  Checker

Java sourcecode (as is)

Oopsie extension



 + manual

annotations

Developer

Compiled bytecode

Higherorder SQL type

Framework

Java sourcecode



Java compiler errors



Database dictionary Recommended JDBC type mappings

JDBC type violations (Oopsie error)

Figure 2: Oopsie System architecture. this type information in automatically generated code annotations. For example, this allows Oopsie to resolve “SELECT *”-queries into higher-order SQL types, which list the order and attributes (with name and type) in the result set. The Oopsie extension has access to the JDBC-recommended type 4 mappings (⃝). Given the code annotations, the Java compiler can type-check the Java types in JDBC getter- and setter-calls against the expected SQL types. Thanks to the Checker Framework, this typing information is automatically propagated along the Java control flow (i.e., conditionals and loops). If the type checker encounters a type mismatch, the Oopsie extension reports an Oopsie er5 ror (⃝). These errors are generated in addition to the Java compiler messages. If no errors are detected, the Java code is compiled 6 to bytecode (⃝), yielding the same compilation result as without Oopsie. In most scenarios, the approach does not require any changes to the code, because the type annotations needed by the tool chain can be inferred fully automatically. To enable modular type checking across method boundaries, e.g., when a JDBC prepared statement is passed to or returned from a method, additional type annotations are required. They can be manually declared by the de7 velopers (⃝).

4.2

(3) invoking getters with invalid column names or indices, and (4) invoking getters/setters with types not recommended by the JDBC specification.

 SQL stmt

Guarantees and Limitations

By construction, values computed only at run time are generally inaccessible to static analysis; accordingly, Oopsie requires SQL strings and column references to be known at analysis time. The approach supports literal values and, via the Checker Framework Constant Value Checker, can go beyond literal values and support some statically inferable non-literal values (see Section 2.3). If the checker cannot determine the query string or the getter/setter argument, or if the query is malformed, Oopsie reports an error. Oopsie’s objective is to prevent certain categories of JDBC exceptions. When a Java developer writes a program for which the Oopsie checker does not raise a compiler error, they can be sure that these exceptions will never occur when running the program: Proposition 1. If the Oopsie type checker does not raise a type error for a program 𝑃 , then SQLExceptions will never be thrown at run time when executing 𝑃 as a result of … (1) SQL syntax errors and schema mismatches when executing a Statement or PreparedStatement, (2) invoking setters with invalid parameter indices,

Thus, using Oopsie eliminates these categories of JDBC-related run-time failures. Oopsie is sound in the sense that it does not produce false negatives, although it may produce false positives (e.g., for dynamically computed queries). Oopsie does not prevent all SQLExceptions: failures such as connection errors or uninitialised connections remain possible and must be handled by the application. At present, Oopsie does not enforce that every placeholder of a prepared statement has been assigned a value; this is a very feasible extension that we plan to add in a future version. As discussed in Section 3, other tools also address the first feature in Proposition 1, yet features (2)–(4) are unique to Oopsie. Degraded mode. When a Java project is developed in a greenfield approach and Oopsie is part of the build chain from the start, Oopsie reports compilation errors promptly; developers can then modify the code such that it can be handled by the type checker and thus benefit from the guarantees in Proposition 1. For retrofitting existing code, such strict enforcement may be impractical. In many legacy code bases, not all calls will meet the restrictions required by the checker, and extensive refactoring solely to satisfy the type checker is often infeasible. To accommodate this, Oopsie provides a degraded mode in which JDBC Statements and PreparedStatements whose SQL statement strings cannot be analyzed at compile time remain unchecked together with their associated getter/setter calls. This mode sacrifices global soundness but the type system offers local guarantees that the checker preserves and verifies: Proposition 2. If the Oopsie type checker does not raise an error at compile time for a program 𝑃 in degraded mode, then the exceptions mentioned in Prop. 1 will never be raised by the statements covered by the type checker at run time when executing 𝑃 . The type checking analysis is performed over the entire program modularly, proceeding method by method. This also allows our approach to scale. However, not every type system that ensures run time guarantees enjoys this locality property. Im some type systems, a type constraint that is violated locally may manifest as a runtime error in a different region of the program later. Our experiments in Section 5 examine both scenarios: small, self-contained examples that illustrate the full benefits of Oopsie, and studies that demonstrate the benefits of applying Oopsie to existing code bases, where Oopsie operates in degraded mode yet still yields measurable benefits.

4.3

Annotation-based Type-Checking for SQL

We now describe type-checking of JDBC getter/setter-calls within the Oopsie extension to the Checker Framework. Our description follows the structure introduced in Section 2.3: We describe our type qualifiers and type hierarchy in Section 4.3.1. Usually, Oopsie can automatically assign the @Sql annotation to source code when certain method calls are performed; these type introduction rules are detailed in Section 4.3.2. Flow-sensitive refinement of the types

Thomas James Kirz, Werner Dietl, Mattias Ulbrich, and Stefanie Scherzinger

is then discussed in Section 4.3.3. After annotations have been inferred, setter and getter methods are checked based on the information stored in the annotations, as outlined in Section 4.3.4. Section 4.3.5 describes how, in some cases, manually annotating the application code allows Oopsie to check additional statements. 4.3.1 Oopsie Annotations. At the heart of the Oopsie type system is the annotation type @Sql. Its two elements, in and out, store the SQL types for parameters and the result of a SQL statement.3 We provide an EBNF grammar describing a @Sql annotation with in and out elements below. ⟨sql ⟩ ::= ‘@Sql(’ [ ⟨in_list ⟩ ‘,’ ] ⟨out_list ⟩ ‘)’ ; ⟨in_list ⟩ ::= ‘in = {’ ⟨in_type⟩ { ‘,’ ⟨in_type⟩ } ‘}’ ; ⟨out_list ⟩ ::= ‘out = {’ ⟨out_type⟩ { ‘,’ ⟨out_type⟩ } ‘}’ ; ⟨in_type⟩ ::= ‘"’ ⟨sql_type⟩ ‘"’ ; ⟨out_type⟩ ::= ‘"’ ⟨sql_type⟩ [ ⟨identifier ⟩ ] ‘"’ ; ⟨sql_type⟩ ::= ‘INTEGER’ | ‘VARCHAR’ | ‘TIMESTAMP’ | … ; ⟨identifier ⟩ ::= A result column identifier, e.g., a column name. Example 4.1. The code snippet below illustrates a prepared statement and a dynamic statement with annotations that satisfy the grammar. Note that only PreparedStatements have parameters, so the ⟨in_list ⟩ need not be declared for Statements. For query results, the ⟨out_list ⟩ also states the column names. 1 PreparedStatement ps = conn . prepareStatement ( 2 " SELECT id , salary FROM employee where dob = ?"); 3 4 Statement stmt = conn . createStatement (); 5 ResultSet rs = stmt . executeQuery ( 6 " SELECT username , dob FROM employee ");

A @Sql annotation type A = @Sql(in=in1,out=out1) may be a subtype of a type B = @Sql(in=in2,out=out2), depending on their elements. This is the case if (1) out2 is a prefix of out1, i.e. out2 has 𝑛 entries and the first 𝑛 entries of the two lists are equal, and (2) in1 and in2 are equal. This allows an A-value to be used where a B-value is expected, as all legal getters for B are also legal for A. The in lists must be equal to allow verifying that all parameters have been set in a future feature. The Checker Framework requires the type hierarchy to be a bounded lattice; Oopsie defines the @SqlBottom and @SqlUnknown types as bottom and top types, i.e. universal super- and subtypes at the respective ends of the type hierarchy. To mark statements as unsupported and to avoid redundant error messages, the type @SqlUnsupported is introduced. The annotations @CreatesSqlStatement and @RetrievesSqlResultSet (outside of the @Sql hierarchy) mark methods which produce statement or result set objects according to a provided SQL string, as we will explain further on. This avoids hard-coding the set of these special methods. 4.3.2 Inferring Annotations Automatically. Oopsie automatically introduces @Sql annotations to statements and result sets and can therefore automatically insert annotations like the two blue ones in Example 4.1 during the type checking process. This is done by overriding the visitMethodInvocation method provided by the Checker Framework, which lets us annotate the abstract syntax tree of the application code. Each method invocation found in the syntax tree is analyzed in sequence. Because 3

The Oopsie-internal elements file, line, and column are used to track statements.

we only want to annotate objects created by methods known to produce JDBC statements and result sets, the logic is restricted to invocations of methods annotated with @CreatesSqlStatement or @RetrievesSqlResultSet. We used Checker Framework mechanisms to provide @CreatesSqlStatement annotations. Oopsie detects all statements created by calls to the methods executeQuery, executeUpdate, execute and executeLargeUpdate of the Java interface java.sql.Statement, as well as all prepared statements created by the prepareStatement methods of java.sql.Connection. For methods that are annotated as @CreatesSqlStatement, Oopsie uses the Constant Value Checker (introduced in Section 2.3) to extract the raw SQL statement strings from the corresponding String arguments to these functions. Example 4.2. Figure 3 shows the steps of inferring Oopsie annotations. In the first step, the Constant Value Checker extracts a dynamically concatenated string. This string is captured by the generated annotation @StringVal for further analysis. Next, the result in/out types can be determined for the SQL statement, by accessing the data dictionary. Then, the statement objects are annotated with a @Sql annotation accordingly. Example 4.3. Step 2 in Figure 3 shows the @Sql annotation for the the SQL in/out types derived from the database dictionary. If the in/out types cannot be determined, a @SqlUnsupported annotation is added. The @RetrievesSqlResultSet annotation is used to mark methods that execute a statement and convert it to a result set. Oopsie propagates the @Sql or @SqlUnsupported type information to the result value invocations of methods with this annotation. The result set only contains results, so only the out types of the @Sql annotation are kept. If the receiver type is @SqlUnsupported, then, in sound type checking, Oopsie issues an error; in degraded type checking, such invocations are quietly ignored. We annotated the getResultSet method of the Statement interface and the executeQuery method of the PreparedStatement interface in the JDBC library with @RetrievesSqlResultSet. 4.3.3 Flow-sensitive type refinement. The type introduction rules described annotate the results of method calls that create a statement or result set. There are also certain method calls where the receiver of the call has to be annotated. Consider the following example, which uses method stmt.execute() (returns a boolean) instead of the aforementioned stmt.executeQuery() (returns a ResultSet). 1 Statement stmt = conn . createStatement (); 2 stmt . execute (" SELECT total FROM Invoice "); 3 ResultSet rs = stmt . getResultSet ();

To have the SQL type information available when creating the result set, we need to construct a @Sql annotation and attach it to stmt after the method call in line 2. We ensure that annotations are updated after relevant method calls by defining a custom transfer function that can track these calls and infer annotations accordingly. Having defined the @Sql type hierarchy and the least upper bound for two types, annotations are propagated through the control flow graph and merged at control flow join points (e.g., after if statements or loops), ensuring accurate type checking even across complex execution paths.

PreparedStatement ps = conn . prepareStatement ( sql ); ps . setInt (1 , 40000); ResultSet rs = ps . executeQuery (); int name = rs . getInt (" name ");

⟩

String sql = " SELECT name FROM "; // @StringVal ({" SELECT ... < ?"}); sql += " employee WHERE salary < ?"; PreparedStatement ps = conn . prepareStatement ( sql ); ps . setInt (1 , 40000); ResultSet rs = ps . executeQuery (); int name = rs . getInt (" name ");

⟩

Verification of type system rules

String sql = " SELECT name FROM "; sql += " employee WHERE salary < ?";

@Sql annotation inference

Constant Value Checker annotation inference

Static Type Checking for Database Access Code

String sql = " SELECT name FROM "; // @StringVal ({" SELECT ... < ?"}); sql += " employee WHERE salary < ?"; // @Sql ( in = {" INTEGER "} , // out = {" VARCHAR name "}) PreparedStatement ps = conn . prepareStatement ( sql ); ps . setInt (1 , 40000); // OK ! ResultSet rs = ps . executeQuery (); int name = rs . getInt (" name "); // ERROR !

Figure 3: Steps of inferring Oopsie annotations and checking JDBC access code. The annotations inferred in each step are highlighted in blue and setter/getter-calls identified as correct or buggy are highlighted in green and red, respectively. 4.3.4 Verifying setter and getter calls. Whenever a getter/setter is called on a @Sql-annotated object, the type of the method can then be compared to the expected types stored in the annotation. The JDBC-specified type mapping configuration is used to decide if the types match (i.e., are JDBC-recommended). For getter and setter calls on objects annotated with @SqlUnsupported, in sound type checking, Oopsie issues an error; in degraded type checking, such invocations are quietly ignored, as they are either out of scope for Oopsie (for CallableStatements) or a warning has already been emitted for unparsable or unextractable statements. Example 4.4. Continuing with our running example from Figure 3, the third step shows that the setter-call type-checks successfully, unlike the getter-call (last line of code). We call getter or setter invocations of unannotated methods nonlocal accesses, because this means that they cannot be traced back to the declaration of the statement or result set. 4.3.5 Declaring Annotations Manually. In some cases, when a statement is accessed in multiple methods, the analysis of non-local accesses may nevertheless be enabled by manually annotating a method signature. A @Sql annotation can be added to a parameter or the return type of a method. In case of an annotated parameter, Oopsie will (a) check setter/getter accesses to the parameter within the method according to the written annotation and (b) require that the argument in calls to the method match (i.e., be a subtype of) the annotated type. For methods with an annotated result type, the checker will (i) verify setter/getter accesses to the object returned by the annotated method and (ii) make sure that the method returns a value of a subtype of the annotated parameter type. We illustrate manual annotations in the upcoming Example 4.8.

4.4

Code Gallery

The examples below illustrate the capabilities of Oopsie. Several are based on third-party code that we also analyze in our experiments. Example 4.5 (Control flow). The following code snippet is based on an open source database benchmark [46]. The raw SQL statement string can be extracted by the Constant Value Checker, despite it being concatenated (lines 8 and 9). Some other tools cannot handle such dynamically created SQL statements. By accessing the data dictionary, Oopsie can statically resolve the wildcard (line 8) in the SQL statement. Despite the while-loop and the conditional, Oopsie can correctly check the setter- and getter-calls.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

PreparedStatement statement = null ; ResultSet rs = null ; int _d_id = Integer . parseInt (( String ) obj . get (" did ")); while ( _li_no < _o_ol_cnt ) { statement = con . prepareStatement ( " select * from stock " + " where s_i_id = ? and s_w_id = ?"); statement . setInt (1 , _li_id ); statement . setInt (2 , _li_s_w_id ); rs = statement . executeQuery (); rs . next (); if ( _d_id _s_dist } else if _s_dist }

== 1) { = rs . getString (" s_dist_01 "); // CHAR (24) ( _d_id == 2) { = rs . getString (" s_dist_02 "); // CHAR (24)

}

Example 4.6 (Reassigning prepared statements). The following code snippet is adapted from the open-source project OSCAR [30], the de-facto reference application in academic research on database applications (e.g., OSCAR is studied in [6, 13, 21, 25, 32, 34, 38, 45]). 1 2 3 4 5 6 7 8 9

String sql = " SELECT image_id FROM client_image " + " WHERE image_data IS NOT NULL AND contents IS NULL "; PreparedStatement pst = conn . prepareStatement ( sql ); sql = " SELECT image_data FROM client_image + " WHERE image_id = ?"; pst = conn . prepareStatement ( sql );

"

pst . setLong (1 , id );

Variable pst is first associated with a query that has no parameters and later reassigned to a different query with one parameter. Other tools commonly cannot track this reassignment along the control flow. For example, when IntelliJ IDEA Ultimate encounters pst.setLong(1, id), it assumes that the statement has no placeholders and issues an error. In contrast, Oopsie checks the setter against the correct SQL statement. Like in the previous example, the prepared statement is reassigned. Yet previously, the reassignment happens inside a whileloop (line 7), and the SQL statement string itself remains the same. Now, the SQL statement string changes between assignments. Oopsie can reliably handle both scenarios. Example 4.7 (Sequential parameter binding). Oopsie supports common programming idioms, such as sequential parameter binding.

Thomas James Kirz, Werner Dietl, Mattias Ulbrich, and Stefanie Scherzinger

For the code snippet below, the Constant Value Checker (see Section 2.3) statically determines the index of the setter-calls, even though not supplied as an integer literal. Thus, Oopsie can check the setter-calls with index ctr++, while other developer tools are not able to track the incremented index through the control flow. 1 2 3 4

PreparedStatement ps = conn . prepareStatement ("..."); int ctr = 1; ps1 . setInt ( ctr ++ , quantity ); ps1 . setString ( ctr ++ , id );

Example 4.8 (Non-local type checking). The following code is based on a repository for Java design patterns [42]. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

public Optional < Room > getById ( int id ) throws Exception { //... var statement = connection . prepareStatement ( " SELECT * FROM ROOMS WHERE ID = ?"); statement . setInt (1 , id ); resultSet = statement . executeQuery (); if ( resultSet . next ()) { return Optional . of ( createRoom ( resultSet )); } // ... } private Room createRoom ( ResultSet resultSet ) throws Exception { return new Room ( // calls getInt instead of getBigDecimal resultSet . getInt (" ID ") , resultSet . getString (" ROOM_TYPE ") , resultSet . getInt (" PRICE ") , // calls getBoolean instead of getString resultSet . getBoolean (" BOOKED ") ); }

Method getByID(id) fetches the details of a given hotel room based on the room number. It then calls helper method createRoom that instantiates an instance of class Room, based on the query result. Out of the box, Oopsie cannot type-check these non-local accesses (see previous section). Yet in lines 14 and 15, the signature of the helper method has been manually annotated with the schema of the ResultSet that is passed as a parameter. This enables Oopsie to statically check the getter-calls (lines 20–24): While all column names exist, not all types match. Oopsie will statically check that the query result (or rather, the ResultSet) for the query in line 4 matches the annotation for the helper method createRoom, when called in line 8. Calling the method with a ResultSet that does not contain the columns specified in the annotation would cause a compilation time error. Example 4.9 (Limitations). In the generic helper method below, the setter-call is a non-local access. Here, the index of the parameter required to be compatible with setString depends on the method parameter parameterIndex. Thus, a manual @Sql annotation cannot resolve this.4 Oopsie cannot check this statement, and reports an error in sound mode, or ignores the setter-call in degraded mode. 1 public void bindParam ( PreparedStatement ps , 2 int parameterIndex , String value ) throws SQLException { 3 ps . setString ( parameterIndex , value ); 4 } 4

More expressive dependent types could be used to express this relationship, but we leave this additional complexity for future work, if we see a clearer need for it.

Improvements over state-of-the-art. The code snippet below contains three bugs in setting parameters in a prepared statement. Each causes a runtime error. Existing developer tools like IntelliJ only detect the third error (line 11) (by counting the number of question marks), while Oopsie detects all errors. 1 public void insertGenre ( boolean newInstance ) 2 throws SQLException { 3 String stmt ; 4 stmt = " INSERT INTO genre ( id , name ) VALUES (? ,?)"; 5 6 PreparedStatement ps = conn . prepareStatement ( stmt ); 7 8 // Assignments use wrong index 9 ps . setString (1 , " scary industrial hip hop "); 10 ps . setInt (2 , 1); 11 ps . setString (3 , " hip hop "); // Index out of bounds 12 }

In this code gallery, we studied small code snippets that illustrate the capabilities of Oopsie. In our upcoming experiments, we will put Oopsie to the test and analyze full-sized code repositories.

5 Experiments 5.1 Research Hypotheses Our experiments explore the hypotheses listed below: Hypothesis H1 explores Oopsie in sound mode, Hypothesis H2 explores the degraded mode. A high number of false positives would render Oopsie unusable in practice (H2.1). Moreover, if the manual annotation effort is excessive or is not effective in increasing the reach of the analysis, the effort is not justified (H2.2). Finally, to be integrated into the developer tool chain, code compilation with Oopsie must not impose unnecessary overhead (H2.3). H1 Oopsie is sound; therefore, it produces no false negatives. H2 Type-checking existing JDBC code repositories with Oopsie in degraded mode is practical: H2.1 Oopsie does not excessively produce false positives. H2.2 Manual code annotation increases the reach of static analysis, compared with an unannotated baseline. H2.3 The overhead during code compilation is reasonable. Existing developer tools can reliably detect malformed SQL statement strings in JDBC application code. Therefore, our experiments exclusively target the getter/setter-related exceptions (specifically, the scenarios (2)-(4) in Proposition 1).

5.2

Setup

Implementation. We implemented Oopsie based on the EISOP Checker Framework [19] (version 3.49.5-eisop1). Our checker extension is lightweight and comprises only approx. 3.0k lines of Java code. Oopsie can check Java 17+ and Java 8 applications. Specifically, we use OpenJDK 8u482 to compile (and check) the code repository of the OSCAR project (to be introduced below), and OpenJDK 17.0.18+8 for all other code repositories in our experiments. We implemented type-checking for all JDBC getter- and settercalls, with the exception of setObject, since we did not encounter such calls in the analyzed third-party code. Furthermore, our Oopsie prototype does not cover type-checking for calls to stored procedures or for automatically generated keys. All of these limitations could be easily addressed in productization.

Static Type Checking for Database Access Code

Oopsie supports PostgreSQL with JDBC driver version 42.7.5, and provides limited support for MySQL and MySQL Connector/J 8.2.0. In our experiments, we consider a SQL statement string wellformed if it can be statically extracted from the code (using Constant Value Checker) and successfully validated against the data dictionary using Calcite. To do so, Oopsie requires a library for parsing and type-checking SQL queries. We delegate this task primarily to Apache Calcite [5] (version 1.37.0). Calcite supports a wide variety of SQL dialects, but not all PostgreSQL query constructs. As fallback for unsupported constructs, we request metadata via JDBC.5 Competitor Software. We compare against the professional IDE IntelliJ IDEA Ultimate 2025.1.5.1, with Plugin “Database Tools and SQL” (bundled 251.28293.39). Execution Environments. Unless stated otherwise, all experiments were conducted on a developer notebook (MacBook Air M3 with 16 GB of memory). In measuring overheads during Java compilation, we use a Linux server equipped with two 3.1 GHz Intel Xeon Gold processors and 384 GB of main memory as build server.

5.3

Analyzed Code

Table 1 lists the code repositories analyzed. The first is a handwritten test suite which includes small programs, in the style of Examples 2.2, 2.3, and 4.7. All SQL statement strings are well-formed. The tests comprise 31 positives and 40 negatives. The remaining repositories contain functional third-party Java code and a database schema. One group (T) consists of code from textbooks and tutorials. This code is small, with simple control flow, and showcases a wide range of JDBC features. Group (RW) aims at testing the practicality of using Oopsie in larger-sized, realworld application code. This group includes OSCAR, an electronic medical record system, and OpenNMS, a network monitoring platform. Both applications have non-trivial control flow and have been actively maintained for over 25 years. Last is a Java implementation of the TPC-C database benchmark (BM). We analyze its implementation for MySQL, which includes SQL queries that use wildcards, as illustrated in Example 4.5. Summary Statistics. In Table 1, we state the repository name, origin, and version (git commit hash). We also state its size in lines of code. We exclude directories with unrelated code (not using JDBC), and indicate this by an asterisk. We state the size of the database schema in terms of the number of tables. Regarding SQL, we consider CRUD-statements only. We include WITH in queries, but exclude WITH RECURSIVE (which does not appear in any of the third-party projects), as well as DDL statements, such as DROP TABLE. Again, these limitations are only technical. We state the total number of declarations of JDBC Statement and PreparedStatement, as well as the maximum number of parameters in prepared statements. In OpenNMS, this exceeds 60 parameters in a single statement. We state the maximal lengths of SQL statement strings. The very long statements in OSCAR and

5

The PostgreSQL JDBC driver again provides more type information compared to MySQL’s. However, it does not provide the names of result columns and only limited information about PreparedStatement parameter types.

Actual Pos Actual Neg

Pred. Pos

Pred. Neg

TP: 31 FP: 0

FN: 0 TN: 40

(a) Oopsie

Act. Pos Act. Neg

Pred. Pos

Pred. Neg

TP: 6 FP: 0

FN: 25 TN: 40

(b) IntelliJ

Figure 4: Confusion matrices for handwritten test suite: Predicted vs. actual positives/negatives, (a) Oopsie vs. (b) IntelliJ. OpenNMS are commonly INSERTs with many parameters. We state the total numbers of getter- and setter calls. We state the share of well-formed SQL statement strings. Interestingly, we found malformed SQL strings in the textbook code.6 In the real-world projects, not all SQL commands are recognized by Calcite. Since OSCAR uses MySQL-proprietary SQL syntax, yet Oopsie only provides limited support for MySQL, the share of wellformed SQL statements is lowest for OSCAR. Ground truth. With our handwritten test suite, we can test Oopsie against a known ground truth. However, in analyzing functional third-party code, we cannot expect to find bugs that cause runtime exceptions. However, we were able to identify an older version of the database benchmark repository with a bug in a gettercall that was fixed in a later commit.7 Therefore we choose this specific, older version of EscadaTPC-C for our analysis. SQL dialects. We made minor code changes to account for SQL dialects: For OSCAR, this amounts to 5 lines of Java code out of over 800K, and 45 out of 19K lines of the MySQL schema definition.8 We also changed 5 lines in the java-design-patterns repository.9

5.4

Soundness

In exploring hypothesis H1, we analyze the handwritten test suite. Setup. We analyze the code as is and compare it with IntelliJ. Results. Figure 4a shows the confusion matrix for Oopsie. Oopsie produces no false negatives. IntelliJ (Figure 4b) correctly detects 6 cases when a setter accesses a parameter index position that is “out of bounds”, yet it leaves 25 false negatives: It does not detect when a getter attempts to access a non-existent column (whether by name or index), nor any getter/setter type mismatches. Discussion. Oopsie is sound and therefore does not produce false negatives. Our experiments are in line with hypothesis H1 and also show that Oopsie improves over the state-of-the-art.

5.5

Ad-hoc Code Analysis

We now explore the out-of-the-box experience with Oopsie applied to the third-party code repositories. 6

Listing 2 illustrates an example from the textbook collection, where the number of column names and question marks are off by one. Meanwhile, we have confirmed this problem with the author. In java-design-patterns, six statements are malformed. For four, we could not find any matching SQL dialect, which suggests a bug. Two other statements contain the MySQL-specific SHOW COLUMNS clause. 7 In the commit b3f8f8d, a setString method call is changed to setInt. 8 To provide an idea how small-scale these changes are, we describe two: We replaced MySQL-proprietary data type TEXT with VARCHAR in the data dictionary. Because it is a reserved keyword in Calcite, we escaped a column named datetime in two locations. 9 This code uses a BLOB type which is not supported by PostgreSQL. As a workaround, we changed the type of a single database column from BLOB to PostgreSQL BYTEA and adjusted one setter- and one getter-call accordingly.

Thomas James Kirz, Werner Dietl, Mattias Ulbrich, and Stefanie Scherzinger

Table 1: Analyzed code repositories, including handwritten and third-party code (T: from textbooks, RW: real-world applications, BM: database benchmark). Stating lines of code (LoC), number of tables in the schema (#Tables), and number of JDBC Statements and PreparedStatements (with max. number of parameters). Listing max. SQL string length and number of getters/setters. Finally, share of SQL statement strings that are well-formed w.r.t. the database schema.

250

125 25 0

200

getString/INTEGER setLong/INTEGER getString/BIGINT setString/INTEGER getLong/INTEGER Other

150 100 50

FP

0

0 TP

Setup. We analyze the third-party code repositories ad hoc, without manual annotations. Oopsie runs in degraded mode. We report the following cases for local getter/setter accesses: (1) Positive: Oopsie reports a type mismatch. (2) Negative: Oopsie reports no type mismatch. We manually distinguish true and false positives. We refer to non-local accesses as out-of-scope (abbreviated OOS). We further analyze the same code repositories with IntelliJ. In analyzing OSCAR, IntelliJ ran into timeouts trying to analyze the entire repository, so we checked file-by-file. Results. Table 2 summarizes the results for the textbook code. Oopsie produces no false positives (FP). For two repositories, Oopsie actually detects true positives (TP). Upon inspection, the true positives seem low-risk. For example, in JDBC-Course, getString is called on a SQL INTEGER. Some accesses are not local (denoted OOS) and cannot be checked ad hoc. Figure 5 shows the results for the remaining repositories. The visualization via bar charts provides a sense of scale, and we highlight the most frequent true positives for OSCAR inside the legend. Again, Oopsie produces no false positives. For OSCAR, Oopsie reports approx. 150 positives, which is still in the range that a developer can check one-by-one. The most frequent true positive is calling getString on a ResultSet column of SQL-type INTEGER (like with the textbook code above), which is not likely to be critical in production. As may be expected, the share of getters/setters classified as “negatives” is higher. OSCAR has a considerable share of non-local accesses that could not be checked. In OpenNMS, the share of non-local accesses is smaller. In the benchmark application, there are none at all. For our analysis with IntelliJ, we only report on OSCAR and OpenNMS; for the other projects, the commercial tool does not detect problems beyond malformed SQL statements, which is not the focus here. Every true positive recognized by IntelliJ regarding getters/setters is also recognized by Oopsie. IntelliJ produces one false

≈ 4 0 (b) OpenNMS

250 200

≈

50

0

0

(a) OSCAR

N

OOS 1 21 0

OOS

N 47 44 21

175

≈

0 OOS

FP 0 0 0

WellFormedSQL 45 (100%) 11 ( 79%) 26 (100%) 13 ( 81%) 56 ( 37%) 34 ( 55%) 46 ( 88%)

FP

TP 0 11 2

500

setters 53 31 41 14 197 178 190 225

550

Frequency

Project O’Reilly: bank java-design-patterns JDBC-Course

getters 18 16 14 9 166 29 72

N

Table 2: Ad-hoc analysis of textbook code (degraded mode, stating true/false positive/negatives, and out-of-scope).

MaxParams MaxSQLLen 4 99 9 146 5 80 3 57 29 1402 63 1875 21 268

TP

PrepStmt 43 14 25 5 97 52 51

FP

DynStmt 2 0 14 12 54 10 4

TP

6ca73df 163c301 04ed161 cca70ec dcafd6d ec47ca0

LoC (k) #Tables 1 11 6 4 5∗ 8 1 3 852 558 64∗ 120 2∗ 9

N

Commit

OOS

Repository Handwritten test suite T/O’Reilly: bank [41] T/java-design-patterns [42] T/JDBC-Course [1] RW/OSCAR [30] RW/OpenNMS [43] BM/EscadaTPC-C [46]

(c) EscadaTPC-C

Figure 5: Ad-hoc analysis of functional software: Manually confirmed true positives (TP) and (zero) false positives (FP) demonstrate practical usability. Also reporting accesses classified as negatives (N) and non-local accesses (OOS). positive, by not properly tracking the control flow (this scenario is shown in Example 4.6). Again, the analysis with IntelliJ does not detect any type mismatches in getters/setters. Discussion. The presence of true positives in functional code surprises, yet inspection reveals that they mostly concern lower-risk type mismatches. In degraded mode, it therefore makes sense to enable developers to configure different warning and error levels. However, Oopsie also found access calls that can indeed cause runtime exceptions: (1) One true positive is the confirmed bug in EscadaTPC-C, which originally motivated us to choose this specific code version for analysis (discussed in Section 5.3). Thus, Oopsie found a confirmed bug. (2) In OpenNMS, Oopsie found getter-calls where the column name does not exist. When executed, this would cause runtime exceptions. As these calls are within deprecated classes, inside a conditional branch that is currently not executed, these problems could go undetected so far. Thus, Oopsie found undetected bugs lurking in dead code. Making developers aware of such risks in their codebase is a valuable contribution. Here, Oopsie did not produce any false positives, confirming Hypothesis H2.1. Consequently, developers are unlikely to be overwhelmed by unfounded messages. The high share of reported negatives is to be expected as we analyze functional code. Overall, our results strongly indicate that

Static Type Checking for Database Access Code

Analyzed after manual annotation

OOS

100 80 60 40 20 0 G

(a) OSCAR

Comp. Time (s)

(b) OpenNMS

Memory (MB)

Figure 6: Oopsie in degraded mode. Share of analyzable setters (S) and getters (G) that can be type-checked ad-hoc vs. after manual annotation, or remain out of scope (OOS).

developers are likely to find Oopsie useful out-of-the-box: Compared to tools like IntelliJ, Oopsie finds important errors that existing tools cannot detect. What stands out is the substantial share of non-local accesses in OSCAR, unlike with OpenNMS and the benchmark repository. We suspect that this is due to project-specific programming patterns.

5.6

Table 3: Compilation time and maximum memory usage during code compilation (mean values) for the Java compiler (JC), the value checker (VC) and the Oopsie checker Metric

S

G

100 80 60 40 20 0 S

Percentage

Analyzed ad-hoc

Manual Annotations

Hypothesis H2.2 concerns the effort of manually annotating code. Setup. As mentioned earlier, manual annotations may be required for the intra-procedural case where statements or result sets are passed across method call boundaries. We added manual annotations to the code of OSCAR and OpenNMS as follows. For each case where SQL statement handling spanned over several methods, we inspected the code to determine if the invocations can be described by fixed SQL types. Where this was the case, we added annotations. As a proxy metric for the annotation effort, we measure the string lengths of annotations (excluding whitespaces). We analyze the code thus annotated with Oopsie running in degraded mode. We count the getters/setters that can be type-checked ad-hoc, as well as those that can be type-checked due to manual annotation, and those that nevertheless remain out of scope. Results. The number of code locations requiring annotation is small, only 5 for OSCAR and 6 for OpenNMS. The annotation strings are long, reaching approx. 1.9k characters for OSCAR and 1.3k characters for OpenNMS. Yet, they are not complex: OSCAR uses methods that handle query results from tables with many columns, which must all be listed in the annotations. Annotations could in many cases be copied directly from type information reported in Oopsie’s error messages for the program without annotations. Figure 6 visualizes the share of getters/setters that can be typechecked. For OSCAR, all setter-calls can be type-checked ad-hoc, while more than half of the getter-calls are out of scope for an ad-hoc analysis. Half of these cases type-check with manual annotations. More than half of the OpenNMS setter/getter calls can be typechecked ad-hoc. With annotations, Oopsie type-checks close to 100%. For OSCAR, the additionally type-checked getters/setters do not produce new true positives. For OpenNMS, we identify 17 additional true positives. Oopsie still reports no false positives. Discussion. In quantifying the annotation effort, we count the annotated code locations and lines of code. This is only a crude

Repository OSCAR OpenNMS OSCAR OpenNMS

JC 43 15 2,751 2,076

JC+VC 340 31 3,844 2,153

JC+VC+Oopsie 515 43 4,467 2,133

proxy metric to the time it will takes developers to write the manual annotations. We were able to come up with the required annotations by a straightforward inspection of the method call hierarchy and copying from error messages of Oopsie’s checker. While no guarantee, these observations indicate that there is potential for a (semi-)automatic inter-procedural type inference engine for SQL types that can produce a large part of required annotations. Clearly, the reach of Oopsie depends on how programmers modularize their code: With OSCAR, about 75% of getters can be checked, yet with OpenNMS, it is nearly all getters/setters. All in all, we consider the manual annotation effort to be very reasonable, given the potential to significantly increase the number of getters/setters that can be type-checked. This confirms Hypothesis H2.2.

5.7

Oopsie Overheads

Finally, we monitor the overheads that static analysis with Oopsie adds to the compilation of the Java code. Setup. We compile the two real-world projects on the build server. As performance baselines, we compile the code with (1) the Java compiler, (2) the Checker Framework with the Value Checker plugin (which also invokes the Java compiler, see Section 2.3) and (3) with Oopsie that also relies on the Checker Framework and the compiler. We measure the run time of the compilation using Linux time, and the maximum memory consumption during compilation using /usr/bin/time. For each metric, we perform 5 consecutive runs, discard the minimum and maximum measured values, and compute the mean of the remaining three. Results. We first consider the run time of compilation in Table 3. Oopsie takes under 9 minutes to compile OSCAR’s over 850k lines of code. This is about 12 times slower than the plain vanilla Java compiler, yet compared to the Checker Framework baseline, only a slowdown by a factor of 1.5. For OpenNMS, which has a much smaller code base, the gap between the Java compiler and Oopsie is only a slowdown by factor of 2.9. We next consider the maximum memory usage during code compilation. The memory usage varies between runs, and we report mean values. The difference between checking with Oopsie versus checking with the ValueChecker only is a factor of ca. 1.2. With OpenNMS, these effects are less pronounced. Discussion. Compiling OSCAR with Oopsie enabled takes too long for interactive software development, but compilation time is acceptable to be part of continuous builds, and in any case, nightly builds. This confirms Hypothesis 2.3. Since Oopsie is not

Thomas James Kirz, Werner Dietl, Mattias Ulbrich, and Stefanie Scherzinger

coupled to an interactive IDE, but is part of the Java compilation, integration into the build chain is easily feasible. The memory usage for compiling OSCAR with Oopsie is noticeably higher than the compilation with the Java compiler. Yet for a well-equipped build server, this does not constitute a problem. Moreover, all preceding experiments were successfully conducted using only a developer notebook. Overall, OSCAR as the largest project analyzed stands out w.r.t. compilation overhead. Our manual investigation of the code reveals that OSCAR contains classes with many static strings, which is also demanding on the Value Checker. There is further evidence indicating that OSCAR is an inherently challenging project to analyze: As pointed out in Section 5.5, analyzing the entire OSCAR project with IntelliJ on the developer laptop failed due to timeouts, and we had to resort to checking the project one file at-a-time.

6

Discussion

Our experiments with Oopsie in sound mode demonstrate the behavior formulated in Proposition 1: static analysis does not detect false negatives (Hypothesis H1). We further demonstrated that the commercial competitor only detects a specific subset of errors. Our experiments with Oopsie in degraded mode showcase the practical benefits of static code analysis. We detected true positives, some of them serious issues, in functional real-world software. At the same time, we detected no false positives, so developers should not worry about being overwhelmed with messages (H2.1). Out-of-the-box, a good share of getters/setters can be checked when analyzing existing software, and this share can be further increased by adding manual annotations. We argued that we perceived the annotation effort as reasonable, and that there is even potential for automated support. Given that the reach of Oopsie can be considerably increased via annotations, we conclude that the effort is worthwhile in any case (H2.2). If software is written from scratch, or during refactoring, developers can even take care to modularize their code with Oopsie in mind. For practical usability, the resource overhead during compilation must be reasonable. We can confirm that it is, esp. compared to the baseline cost imposed by the Value Checker, which is already used in professional software development. At the very least, Oopsie is affordable enough to be integrated into offline analysis, like continuous builds or nightly builds (H2.3).

7

Conclusion and Outlook

As our experiments confirm, our Oopsie checker can indeed identify real problems in database access code, even in functional realworld software. The approach presented in this paper builds on the versatile Checker Framework. Exploiting the power of this framework for extended Java type systems allows us to incorporate guarantees that other type extensions in the framework already make. For SQL query statements, this includes the SQL Quotes Checker (outlined in Section 2.3) to detect SQL injection vulnerabilities. By systematically integrating existing checkers with database schema constraints, we may unlock entirely new opportunities in the static analysis of JDBC application code. In the following, we outline several promising avenues for future research.

Nulls. A promising direction is to address the gap between Java’s null and SQL’s NULL. Java NullPointerExceptions are undesirable, and while most IDEs provide nullability warnings for uninitialized variables, these checks are often very basic. The Nullness Checker [17] goes beyond such simplistic analyses: it is aware of control flow, Java generics, and can suppress warnings when they are provably unnecessary. By also considering database schema constraints, we may statically verify, for example, that a JDBC setter call does not assign a null value to a non-nullable database column, or that a getter-call is guaranteed to return a non-null value. Standardization efforts like JSpecify10 further highlight the importance of safe null handling in Java. String lengths. The Index Checker [27] tracks the length of Java strings. By comparing against length constraints derived from the database schema (e.g., the SQL type CHAR(24), line 17 in Example 4.5), we can statically prevent string truncations in setter calls. By propagating such constraints, we may even prevent truncations when strings fetched from the database are displaced in the GUI. This is a common problem when user dialogs are internationalized: Developers in Europe may not notice when a Chinese character is lost, but affected end users will. Result cardinality. A further opportunity is to recognize singleton queries, i.e., queries that return exactly one tuple (e.g. due to aggregation). Such result sets can be safely processed outside of loops. Conversely, if a query may return multiple tuples but only the first is ever processed by the Java code, developers may be advised to extend the SQL query with LIMIT. These queries may then be evaluated more efficiently at the side of the database. Prepared statements. As outlined in Section 4.3, we can extend static analysis in Oopsie to check whether all parameters in a prepared statement are indeed set. This goes beyond what current tools detect and would help identify one-off errors, especially when parameter indices are bound sequentially (see Example 4.7). Extending non-local checks. One limitation of our approach is that non-local accesses cannot always be checked. Here, we see potential to further extend the reach of Oopsie by manually annotating projects with the @CreatesSqlStatement and @RetrievesSqlResultSet method annotations. This could allow support for custom wrapper methods that create statements by calling JDBC methods, as shown in Example 4.9. Extending constraint-based, whole-program type inference [47] to the String-based @Sql annotations would further simplify the annotation effort. Overall, we are confident that extending Oopsie with features as outlined above constitutes valuable contributions, not only for JDBC novices who are prone to make rookie mistakes, but also for experienced developers in charge of maintaining JDBC legacy code, as well as the growing number of “vibe coders” challenged to sign off on AI-generated database application code.

References [1] Sayed Abdul-Aziz. 2021. JDBC-Course. https://github.com/sayedabdulaziz/JDBC-Course/tree/04ed1613c612f8d9ae53ef7629c3cb254d6cad40 [2] Scott W. Ambler and Pramodkumar J. Sadalage. 2006. Refactoring Databases: Evolutionary Database Design. Addison-Wesley Professional. [3] Lance Andersen. 2017. Java Database Connectivity (JDBC) 4.3 Specification (Maintenance Release, JSR-221). https://download.oracle.com/otn-pub/jcp/jdbc4_3-mrel3-eval-spec/jdbc4.3-fr-spec.pdf. Accessed: May 5, 2026. 10

https://jspecify.dev/

Static Type Checking for Database Access Code

[4] Aivar Annamaa, Andrey Breslav, Jevgeni Kabanov, and Varmo Vene. 2010. An Interactive Tool for Analyzing Embedded SQL Queries. In Programming Languages and Systems, Kazunori Ueda (Ed.). Springer Berlin Heidelberg, Berlin, Heidelberg, 131–138. [5] Edmon Begoli, Jesús Camacho-Rodríguez, Julian Hyde, Michael J. Mior, and Daniel Lemire. 2018. Apache Calcite: A Foundational Framework for Optimized Query Processing Over Heterogeneous Data Sources. In Proc. SIGMOD. 10 pages. doi:10.1145/3183713.3190662 [6] Mathieu Beine, Nicolas Hames, Jens H. Weber, and Anthony Cleve. 2014. Bidirectional Transformations in Database Evolution: A Case Study ”At Scale”. In Proceedings of the Workshops of the EDBT/ICDT 2014 Joint Conference (EDBT/ICDT 2014), Athens, Greece, March 28, 2014 (CEUR Workshop Proceedings, Vol. 1133). CEUR-WS.org, 100–107. https://ceur-ws.org/Vol-1133/paper-16.pdf [7] Joshua Bloch. 2008. Effective Java: A Programming Language Guide (2nd revised edition (rev). ed.). Addison-Wesley Longman, Amsterdam. http://www.amaz on.de/Effective-Java-Programming-Language-Guide/dp/0321356683/ref=sr_1 _1?ie=UTF8&qid=1303414280&sr=8-1 [8] Gilad Bracha. 2004. Pluggable type systems. In Proc. OOPSLA’04 Workshop on Revival of Dynamic Languages. [9] Tse-Hsun Chen, Weiyi Shang, Zhen Ming Jiang, Ahmed E. Hassan, Mohamed Nasser, and Parminder Flora. 2014. Detecting performance anti-patterns for applications developed using object-relational mapping. In Proc. ICSE. doi:10.1 145/2568225.2568259 [10] Tse-Hsun Chen, Weiyi Shang, Jinqiu Yang, Ahmed E. Hassan, Michael W. Godfrey, Mohamed Nasser, and Parminder Flora. 2016. An Empirical Study on the Practice of Maintaining Object-Relational Mapping Code in Java Systems. In Proc. MSR. doi:10.1145/2901739.2901758 [11] Aske Simon Christensen, Anders Møller, and Michael I. Schwartzbach. 2003. Precise Analysis of String Expressions. In Static Analysis, Radhia Cousot (Ed.). Springer Berlin Heidelberg, Berlin, Heidelberg, 1–18. [12] Justin Clarke, Kevvie Fowler, Erlend Oftedal, Rodrigo Marcos Alvarez, Dave Hartley, Alexander Kornbrust, Gary O’Leary-Steele, Alberto Revelli, Sumit Siddharth, and Marco Slaviero. 2009. SQL Injection Attacks and Defense (2 ed.). Syngress Publishing. [13] Anthony Cleve, Maxime Gobert, Loup Meurice, Jerome Maes, and Jens H. Weber. 2015. Understanding database schema evolution: A case study. Sci. Comput. Program. 97 (2015), 113–121. doi:10.1016/J.SCICO.2013.11.025 [14] Carlo Curino, Hyun Jin Moon, and Carlo Zaniolo. 2008. Graceful database schema evolution: the PRISM workbench. Proc. VLDB Endow. 1, 1 (2008), 761– 772. doi:10.14778/1453856.1453939 [15] Alexandre Decan, Mathieu Goeminne, and Tom Mens. 2017. On the Interaction of Relational Database Access Technologies in Open Source Java Projects. CoRR abs/1701.00416 (2017). arXiv:1701.00416 http://arxiv.org/abs/1701.00416 [16] Checker Framework developers. 2025. The Checker Framework Manual: Custom pluggable types for Java. https://checkerframework.org/manual/. Version 3.49.1. [17] Werner Dietl, Stephanie Dietzel, Michael D. Ernst, Kivanç Muslu, and Todd Schiller. 2011. Building and Using Pluggable Type-Checkers. In Proc. ICSE. doi:10.1145/1985793.1985889 [18] Jens Dittrich. 2025. How to get Rid of SQL, Relational Algebra, the Relational Model, ERM, and ORMs in a Single Paper - A Thought Experiment. CoRR abs/2504.12953 (2025). arXiv:2504.12953 doi:10.48550/ARXIV.2504.12953 [19] EISOP. 2026. The EISOP Checker Framework. https://eisop.github.io/cf/. Accessed: 2026-04-28. [20] Maxime Gobert, Csaba Nagy, Henrique Rocha, Serge Demeyer, and Anthony Cleve. 2023. Best practices of testing database manipulation code. Inf. Syst. 111, C (Jan. 2023), 15 pages. doi:10.1016/j.is.2022.102105 [21] Mathieu Goeminne, Alexandre Decan, and Tom Mens. 2014. Co-evolving coderelated and database-related changes in a data-intensive software system. In 2014 Software Evolution Week - IEEE Conference on Software Maintenance, Reengineering, and Reverse Engineering, CSMR-WCRE. 353–357. doi:10.1109/CSMRWCRE.2014.6747193 [22] Mathieu Goeminne and Tom Mens. 2015. Towards a survival analysis of database framework usage in Java projects. In Proc. ICSME. doi:10.1109/ICSM.2015. 7332512 [23] C. Gould, Z. Su, and P. Devanbu. 2004. Static checking of dynamically generated queries in database applications. In Proceedings. 26th International Conference on Software Engineering. 645–654. doi:10.1109/ICSE.2004.1317486 [24] Carl Gould, Zhendong Su, and Premkumar T. Devanbu. 2004. JDBC Checker: A Static Analysis Tool for SQL/JDBC Applications. In Proc. ICSE. IEEE Computer Society. doi:10.1109/ICSE.2004.1317494 [25] Mike Hinchey. 2018. Analyzing the Evolution of Database Usage in Data‐Intensive Software Systems. 208–240. doi:10.1002/9781119174240.ch12 [26] JetBrains. 2024. IntelliJ IDEA 2024.3: Database Tools and SQL. JetBrains. https: //www.jetbrains.com/help/idea/2024.3/relational-databases.html [27] Martin Kellogg, Vlastimil Dort, Suzanne Millstein, and Michael D. Ernst. 2018. Lightweight verification of array indexing. In International Symposium on Software Testing and Analysis (ISSTA). 3–14. doi:10.1145/3213846.3213849

[28] Thomas James Kirz. 2025. OPSC: Catching the ”Oops” in JDBC PreparedStatements with Static Code Analysis. In Proc. BTW, Student Track. doi:10.18420/B TW2025-64 [29] Florian Lanzinger, Alexander Weigl, Mattias Ulbrich, and Werner Dietl. 2021. Scalability and Precision by Combining Expressive Type Systems and Deductive Verification. Proc. OOPSLA (2021). doi:10.1145/3485520 [30] McMaster University. 2025. OSCAR-EMR. https://bitbucket.org/oscaremr/osc ar/src/cca70ec9a265370992a8f55d5bcb82d011c4b6ac/ [31] Erik Meijer, Brian Beckman, and Gavin Bierman. 2006. LINQ: reconciling object, relations and XML in the .NET framework. In Proceedings of the 2006 ACM SIGMOD International Conference on Management of Data (Chicago, IL, USA) (SIGMOD ’06). New York, NY, USA, 706. doi:10.1145/1142473.1142552 [32] Loup Meurice and Anthony Cleve. 2014. DAHLIA: A visual analyzer of database schema evolution. In 2014 Software Evolution Week - IEEE Conference on Software Maintenance, Reengineering, and Reverse Engineering, CSMR-WCRE 2014, Antwerp, Belgium, February 3-6, 2014. IEEE Computer Society, 464–468. doi:10.1109/CSMR-WCRE.2014.6747219 [33] Loup Meurice, Csaba Nagy, and Anthony Cleve. 2016. Detecting and Preventing Program Inconsistencies under Database Schema Evolution. In 2016 IEEE International Conference on Software Quality, Reliability and Security (QRS). 262–273. doi:10.1109/QRS.2016.38 [34] Loup Meurice, Csaba Nagy, and Anthony Cleve. 2016. Detecting and Preventing Program Inconsistencies under Database Schema Evolution. In 2016 IEEE International Conference on Software Quality, Reliability and Security, QRS 2016, Vienna, Austria, August 1-3, 2016. IEEE, 262–273. doi:10.1109/QRS.2016.38 [35] Loup Meurice, Csaba Nagy, and Anthony Cleve. 2016. Static Analysis of Dynamic Database Usage in Java Systems. In Proc. CAISE. doi:10.1007/978-3-31939696-5_30 [36] Csaba Nagy and Anthony Cleve. 2017. A Static Code Smell Detector for SQL Queries Embedded in Java Code. In Proc. SCAM. doi:10.1109/SCAM.2017.19 [37] Csaba Nagy and Anthony Cleve. 2018. SQLInspect: a static analyzer to inspect database usage in Java applications. In Proceedings of the 40th International Conference on Software Engineering: Companion Proceeedings, ICSE 2018, Gothenburg, Sweden, May 27 - June 03, 2018. ACM, 93–96. doi:10.1145/3183440.3183496 [38] Csaba Nagy, Loup Meurice, and Anthony Cleve. 2015. Where was this SQL query executed? a static concept location approach. In 2015 IEEE 22nd International Conference on Software Analysis, Evolution, and Reengineering (SANER). 580–584. doi:10.1109/SANER.2015.7081881 [39] Matthew M. Papi, Mahmood Ali, Telmo Luis Correa, Jeff H. Perkins, and Michael D. Ernst. 2008. Practical pluggable types for Java. In Proc. ISSTA. doi:10.1145/1390630.1390656 [40] Dong Qiu, Bixin Li, and Zhendong Su. 2013. An empirical analysis of the coevolution of schema and code in database applications. In Proceedings of the 2013 9th Joint Meeting on Foundations of Software Engineering (ESEC/FSE 2013). New York, NY, USA, 125–135. doi:10.1145/2491411.2491431 [41] George Reese. 2000. Database Programming with JDBC and Java, Second Edition. O’Reilly Media, Inc. [42] Ilkka Seppälä. 2022. Design Patterns Implemented in Java. https://github.com /iluwatar/java-design-patterns/blob/163c3017bb356937d876cd9a05905c012f3b 0af6/transaction-script/src/main/java/com/iluwatar/transactionscript/HotelD aoImpl.java [43] The OpenNMS Group, Inc. 2025. OpenNMS. https://github.com/OpenNMS/ope nnms [44] Stephen Thomas, Laurie A. Williams, and Tao Xie. 2009. On automated prepared statement generation to remove SQL injection vulnerabilities. Inf. Softw. Technol. 51, 3 (2009), 589–598. doi:10.1016/J.INFSOF.2008.08.002 [45] Mario Linares Vásquez, Boyang Li, Christopher Vendome, and Denys Poshyvanyk. 2016. Documenting database usages and schema constraints in databasecentric applications. In Proceedings of the 25th International Symposium on Software Testing and Analysis, ISSTA 2016, Saarbrücken, Germany, July 18-20, 2016. ACM, 270–281. doi:10.1145/2931037.2931072 [46] Ricardo Vilaça. 2013. Escada TPC-C. https://github.com/rmpvilaca/EscadaTPCC/tree/ff15fbf99b39c81725937e11b8eb9665834bfefb [47] Tongtong Xiang, Jeff Y. Luo, and Werner Dietl. 2020. Precise inference of expressive units of measurement types. Proc. OOPSLA (2020). doi:10.1145/3428210

Related documents

• Databases with Missing Values that are Governed by Missingness Mechanisms#1043177
• Proof-of-Retention: A Framework for Auditable Cross-Organization Data Sharing#1043178
• AkasicMEM: Governed Enterprise Memory for Agents#1043179
• Spend Classification Without Leakage: An Evaluation Harness and What It Changed in a Deployed System#1043180
• A Functional Pilot for Certified Freshness-Aware Semantic--Spatial Range Retrieval#1043181