Recommendation¶

Usually, it is better to use a SQL prepared statement than to build a complete SQL query with string concatenation. A prepared statement can include a wildcard, written as a question mark (?), for each part of the SQL query that is expected to be filled in by a different value each time it is run. When the query is later executed, a value must be supplied for each wildcard in the query.

In the Java Persistence Query Language, it is better to use queries with parameters than to build a complete query with string concatenation. A Java Persistence query can include a parameter placeholder for each part of the query that is expected to be filled in by a different value when run. A parameter placeholder may be indicated by a colon (:) followed by a parameter name, or by a question mark (?) followed by an integer position. When the query is later executed, a value must be supplied for each parameter in the query, using thesetParameter method. Specifying the query using the@NamedQuery annotation introduces an additional level of safety: the query must be a constant string literal, preventing construction by string concatenation, and the only way to fill in values for parts of the query is by setting positional parameters.

It is good practice to use prepared statements (in SQL) or query parameters (in the Java Persistence Query Language) for supplying parameter values to a query, whether or not any of the parameters are directly traceable to user input. Doing so avoids any need to worry about quoting and escaping.

Example¶

In the following example, the code runs a simple SQL query in two different ways.

The first way involves building a query,query1, by concatenating an environment variable with some string literals. The environment variable can include special characters, so this code allows for SQL injection attacks.

The second way, which shows good practice, involves building a query,query2, with a single string literal that includes a wildcard (?). The wildcard is then given a value by callingsetString. This version is immune to injection attacks, because any special characters in the environment variable are not given any special treatment.

{// BAD: the category might have SQL special characters in itStringcategory=System.getenv("ITEM_CATEGORY");Statementstatement=connection.createStatement();Stringquery1="SELECT ITEM,PRICE FROM PRODUCT WHERE ITEM_CATEGORY='"+category+"' ORDER BY PRICE";ResultSetresults=statement.executeQuery(query1);}{// GOOD: use a prepared queryStringcategory=System.getenv("ITEM_CATEGORY");Stringquery2="SELECT ITEM,PRICE FROM PRODUCT WHERE ITEM_CATEGORY=? ORDER BY PRICE";PreparedStatementstatement=connection.prepareStatement(query2);statement.setString(1,category);ResultSetresults=statement.executeQuery();}

Example¶

The following code shows several different ways to run a Java Persistence query.

The first example involves building a query,query1, by concatenating an environment variable with some string literals. Just like the SQL example, the environment variable can include special characters, so this code allows for Java Persistence query injection attacks.

The remaining examples demonstrate different methods for safely building a Java Persistence query with user-supplied values:

1. query2 uses a single string literal that includes a placeholder for a parameter, indicated by a colon (:) and parameter name (category).

2. query3 uses a single string literal that includes a placeholder for a parameter, indicated by a question mark (?) and position number (1).

3. namedQuery1 is defined using the@NamedQuery annotation, whosequery attribute is a string literal that includes a placeholder for a parameter, indicated by a colon (:) and parameter name (category).

4. namedQuery2 is defined using the@NamedQuery annotation, whosequery attribute includes a placeholder for a parameter, indicated by a question mark (?) and position number (1).The parameter is then given a value by callingsetParameter. These versions are immune to injection attacks, because any special characters in the environment variable or user-supplied value are not given any special treatment.

{// BAD: the category might have Java Persistence Query Language special characters in itStringcategory=System.getenv("ITEM_CATEGORY");Statementstatement=connection.createStatement();Stringquery1="SELECT p FROM Product p WHERE p.category LIKE '"+category+"' ORDER BY p.price";Queryq=entityManager.createQuery(query1);}{// GOOD: use a named parameter and set its valueStringcategory=System.getenv("ITEM_CATEGORY");Stringquery2="SELECT p FROM Product p WHERE p.category LIKE :category ORDER BY p.price"Queryq=entityManager.createQuery(query2);q.setParameter("category",category);}{// GOOD: use a positional parameter and set its valueStringcategory=System.getenv("ITEM_CATEGORY");Stringquery3="SELECT p FROM Product p WHERE p.category LIKE ?1 ORDER BY p.price"Queryq=entityManager.createQuery(query3);q.setParameter(1,category);}{// GOOD: use a named query with a named parameter and set its value@NamedQuery(name="lookupByCategory",query="SELECT p FROM Product p WHERE p.category LIKE :category ORDER BY p.price")privatestaticclassNQ{}...Stringcategory=System.getenv("ITEM_CATEGORY");QuerynamedQuery1=entityManager.createNamedQuery("lookupByCategory");namedQuery1.setParameter("category",category);}{// GOOD: use a named query with a positional parameter and set its value@NamedQuery(name="lookupByCategory",query="SELECT p FROM Product p WHERE p.category LIKE ?1 ORDER BY p.price")privatestaticclassNQ{}...Stringcategory=System.getenv("ITEM_CATEGORY");QuerynamedQuery2=entityManager.createNamedQuery("lookupByCategory");namedQuery2.setParameter(1,category);}

References¶

• OWASP:SQL Injection Prevention Cheat Sheet.

• SEI CERT Oracle Coding Standard for Java:IDS00-J. Prevent SQL injection.

• The Java Tutorials:Using Prepared Statements.

• The Java EE Tutorial:The Java Persistence Query Language.

• Common Weakness Enumeration:CWE-89.

• Common Weakness Enumeration:CWE-564.