The Checker Framework Manual:
Custom pluggable types for Java

Chapter 13 SQL Quotes Checker

The SQL Quotes Checker helps prevent SQL injection vulnerabilities. Many SQL injection attacks involve malicious user input containing unescaped special characters such as the single quote, which can change whether subsequent concatenations are interpreted as SQL command code or as SQL query values.

The SQL Quotes Checker determines whether a string contains an unescaped single quote character. Furthermore, it marks user-supplied Strings as unsafe for SQL query use, because their quoting is unknown; the program must sanitize or validate all such values before concatenating them to a SQL query for execution. Sanitization can be done by quoting user input as necessary to ensure special characters are properly escaped. The SQL Quotes Checker follows the ANSI standard and considers two consecutive single quote characters as one escaped single quote. (Some databases support the use of the backslash as an escape character for single quotes. The SQL Quotes Checker does not support that syntax because it is not safe for all SQL database implementations.)

The SQL Quotes Checker guarantees that no unchecked values are passed to a SQL query execution statement. It does not guarantee that SQL queries written by the code author are safe for execution; e.g., the checker will not warn at compile time about syntax errors or improper uses of DELETE or DROP statements.

When possible, it is better code style to use a prepared statement or other mechanisms that automatically handle quoting without the need for the programmer to do any work. The SQL Quotes Checker is useful for the large quantity of code that still uses string concatenation to create SQL queries.

To run the SQL Quotes Checker, supply the -processor SqlQuotesChecker or -processor org.checkerframework.checker.sqlquotes.SqlQuotesChecker command-line option to javac.

13.1 SQL Quotes annotations

The SQL Quotes type system uses the following annotations:

@SqlEvenQuotes

indicates a String literal that contains an even number of single quotes (possibly zero).

@SqlOddQuotes

indicates a String literal that contains an odd number of single quotes.

@SqlQuotesUnknown

indicates a String whose quoting is unknown. It is the default qualifier for non-literal Strings.

@SqlQuotesBottom

is the bottom qualifier. Programmers rarely need to write it.

The subtyping hierarchy of the SQL Quotes Checker’s qualifiers is shown in Figure 13.1.

13.2 What the SQL Quotes Checker checks

Concatenation of @SqlEvenQuotes and @SqlOddQuotes Strings parallels integer parity over addition. In other words, concatenation of @SqlOddQuotes and @SqlEvenQuotes Strings is most narrowly typed as follows:

    @SqlOddQuotes + @SqlOddQuotes = @SqlEvenQuotes
    @SqlOddQuotes + @SqlEvenQuotes = @SqlOddQuotes
    @SqlEvenQuotes + @SqlOddQuotes = @SqlOddQuotes
    @SqlEvenQuotes + @SqlEvenQuotes = @SqlEvenQuotes

13.3 Library annotations

The programmer needs to write trusted annotations on SQL-related methods. This is already done for you, for commonly-used libraries such as java.sql and org.springframework.security.crypto.bcrypt. (If you annotate other libraries, please contribute them to the Checker Framework project. Thanks!)

A SQL query execution method such as Statement.executeQuery must be annotated to take a @SqlEvenQuotes string as a parameter.

A sanitization or quoting method should be annotated to take a @SqlQuotesUnknown parameter and to return a @SqlEvenQuotes result.

Given such annotations, the type system will issue an error if an un-sanitized value can ever flow into a database query. (You might need to write some annotations in your own code as well.)