Skip to main content

Spring Data Java Persistence API

In Spring Data JPA framework you can create SQL queries in many ways:

public interface UserRepository extends JpaRepository<User, Long> {
// Using Java Persistence Query Language (JPQL)
@Query("select u from User u where u.emailAddress = ?1")
List<User> findByEmailAddress1(String emailAddress);
// Using Spring Expression Language (SpEL)
@Query("select u from User u where u.emailAddress = :#{[0]}")
List<User> findByEmailAddress2(@Param("emailAddress") String emailAddress);
}
@Entity
// Using named queries
@NamedQuery(
name = "User.findByEmailAddress3",
query = "select u from User u where u.emailAddress = ?1"
)
public class User { /* ... */ }

Abuse cases#

No framework is silver-bullet, though.

Java Persistence Query Language#

By default, the JPQL engine will escape the following user-supplied input parameters:

  • JPQL Positional Parameters: ?1
  • JPQL Named Parameters: :emailAddress

In other words, emailAddress will be interpreted by the SQL engine as a string literal, with possible special characters in a SQL context escaped.

However, if you write a JPQL query like this one:

@Query("select u from User u where u.emailAddress like %?1")
User findByEmailAddress(String emailAddress);

Notice the % we added in front of the JPQL positional parameter ?1.

An attacker that manages to supply an emailAddress equal to a will fetch all email addresses from the database that end with the letter a. The resulting query will be:

SELECT u FROM User u WHERE u.emailAddress LIKE '%a'

We highly recommend you avoid mixing:

  • LIKE conditions (or similar in its kind)
  • hard-coded special wildcard characters
  • user-supplied input

As noted in the example, sanitizing is no solution at all. The problem is having a hard-coded % in the JPQL statement.

Spring Expression Language#

By default, SpEL Expressions are not escaped. This happens because SpEL is designed as an expression language, not a SQL language.

In other words SpEL Expressions Bindings like :#{[0]} or ?#{[0]} will just copy the value of [0] into the SQL operation to be executed by the database.

If you write a SpEL query like this one:

@Query("select u from User u where u.emailAddress = :#{[0]}")
List<User> findByEmailAddress2(@Param("emailAddress") String emailAddress);

An attacker that manages to supply an emailAddress equal to %, will fetch all email addresses from the database. The evaluated query will be:

SELECT u FROM User u WHERE u.emailAddress LIKE '%'

We highly recommend you to use the escape function from SpEL context as follow:

@Query(
"select u from User u " +
"where u.emailAddress like ?#{escape([0])} " +
"escape ?#{escapeCharacter()}"
)
List<User> findByEmailAddress2(@Param("emailAddress") String emailAddress);

In this case the evaluated query would be:

SELECT u FROM User u WHERE u.emailAddress LIKE '\%' ESCAPE '\'