An SQL injection attack consists of insertion or "injection" of a malicious data via the SQL query input from the client to the application. In our example project we have a small Spring Boot based blog application. This application exposes an endpoint to fetch blog articles based on the author:

@GetMapping("/author/{author}")
fun blogsByAuthorSqlInjection(@PathVariable author: String) = blogRepository.findByAuthor(author)

@Repository
class BlogRepository(val entityManager: EntityManager) {

  fun findByAuthor1(name: String): Object = entityManager
    .createNativeQuery("select * from blogs b where b.author = '" + name + "'", BlogEntry::class.java)
    .resultList as Object
}

When we call the endpoint, we will receive:

$ curl -i http://localhost:8080/blogs/author/Anne%20Wilson

HTTP/1.1 200
Content-Type: application/json;charset=UTF-8
Transfer-Encoding: chunked
Date: Mon, 02 Oct 2017 20:19:50 GMT

[ {
  "title" : "Spring Boot with Kotlin",
  "publishDate" : "2017-09-28",
  "author" : "Anne Wilson",
  "contents" : "Spring 5.0 ..."
}, {
  "title" : "New Spring Boot version is available",
  "publishDate" : "2017-10-02",
  "author" : "Anne Wilson",
  "contents" : "A new version ..."
} ]

If you for example supply the following last name:

$ curl -i "http://localhost:8080/blogs/author/Smith'%20or%20'1'='1"

HTTP/1.1 200
Content-Type: application/json;charset=UTF-8
Transfer-Encoding: chunked
Date: Mon, 02 Oct 2017 20:21:23 GMT

[ {
  "title" : "First blog",
  "publishDate" : "2017-09-27",
  "author" : "Peter Jackson",
  "contents" : "This is ..."
}, {
  "title" : "Spring Boot with Kotlin",
  "publishDate" : "2017-09-28",
  "author" : "Anne Wilson",
  "contents" : "Spring 5.0 ..."
}, {
  "title" : "A new Spring Boot version is available",
  "publishDate" : "2017-10-02",
  "author" : "Anne Wilson",
  "contents" : "Version 2 ..."
}, {
  "title" : "Spring 5 is on its way",
  "publishDate" : "2017-10-02",
  "author" : "Eric William",
  "contents" : "Spring 5 ..."
} ]

The application is not properly escaping the quotes which enables you to modify the query and list all the blogs. When you read how to prevent SQL injections the most common advise is: "Use a prepared statement or parameterized queries." If we change our repository method accordingly we will get:

fun findByAuthor(name: String): List =
  return entityManager.createQuery("select b from BlogEntry b where b.author = :name")
           .setParameter("name", name)
           .resultList as List

Now the previous call will result in an empty JSON message because there is no author with the name Smith'%20or%20'1'='1 You are no longer able to change the meaning of the query. ORM implementations like JPA/Hibernate will help you automatically to prevent SQL injections but as we will see in the next paragraph it might not be enough.

Not enough

Using prepared statements is the first step while preventing SQL injections, there are some cases where this is not enough, suppose we have the following query: “select * from blogs order by author” The order by clause will normally be a column name, however if we look at the SQL grammar definition it can be:

SELECT ...
FROM tableList
[WHERE Expression]
[ORDER BY orderExpression [, ...]]

orderExpression:
{ columnNr | columnAlias | selectExpression }
    [ASC | DESC]

selectExpression:
{ Expression | COUNT(*) | {
    COUNT | MIN | MAX | SUM | AVG | SOME | EVERY |
    VAR_POP | VAR_SAMP | STDDEV_POP | STDDEV_SAMP
} ([ALL | DISTINCT][2]] Expression) } [[AS] label]

Looking at this we see we can use functions inside the order by clause. Let’s change our example and add the following code to our controller and repository:

@GetMapping("/author/{author}")
fun blogsByAuthor(@PathVariable author: String, @RequestParam sortBy: String) =
  blogRepository.findByAuthorContaining(author, sortBy)


fun findByAuthorContaining(name: String, orderBy: String): List =
  return entityManager.createQuery("select b from BlogEntry b where b.author :name order by " + orderBy)
            .setParameter("name", name)
            .resultList as List

The sortBy parameter is passed in to the REST endpoint and directly added to the query. The prepared statement can only deal with query parameters (single value) and cannot be used with column names, table names, expressions etc. This means the order by is just appended to the given query string. You cannot use "?" or ":orderBy" parameters inside the orderBy part of the query. One way to test whether this query is vulnerable for SQL injection is:

(select * from BlogEntry where b.author = :name order by case when true=true then title else contents)
$ curl -i "http://localhost:8080/blogs/author/Anne%20Wilson?sortBy=case%20when%20true=true%20then%20title%20else%20contents%20end"
HTTP/1.1 200
Content-Type: application/json;charset=UTF-8
Transfer-Encoding: chunked
Date: Mon, 02 Oct 2017 21:16:16 GMT

[ {
  "title" : "Spring Boot with Kotlin",
  "publishDate" : "2017-09-28",
  "author" : "Anne Wilson",
  "contents" : "Spring 5.0 ..."
}, {
  "title" : "A new Spring Boot version is available",
  "publishDate" : "2017-10-02",
  "author" : "Anne Wilson",
  "contents" : "Version 2 ..."
} ]

If we flip the when statement the result will be sorted based on the contents instead of the title. This means we can substitute random expressions for the sortBy query parameter, which means we can ask the database questions like:

select * from BlogEntry order by case when substring(h2version(),1,1)='1' then title else contents end
select * from BlogEntry order by case when substring(h2version(),2,1)='.' then title else contents end
select * from BlogEntry order by case when substring(h2version(),3,1)='4' then title else contents end
...

which will give you the database version(1.4.9) of H2 used in our example project. The order by clause is just an example it also applies group by clauses etc. You can also start asking system tables like "INFORMATION_SCHEMA" for other interesting tables in the database. Sqlmap is a tool which can help you automate the extraction process. Using a prepared statement for the complete query is not helping you to prevent the "interesting looking" order by clause, for the query analyzer the query is valid.

Spring Data JPA

Our small example above was also present in an earlier version of Spring Data where you were able to specify a sort expression, like:

@Query("select p from Person p where p.lastname = :lastname") List findByLastname(@Param("lastname") String lastname, Sort sort)

In the old version of Spring Data you could call this method as follows: findByLastname("Johnson", new Sort("LENGTH(firstname)")) which means you can repeat the same SQL injection as we described above. In the newer version of Spring Data an exception is thrown whenever you use a function, you still can use a function but you have to use: JpaSort.unsafe("LENGTH(lastname))" which clearly indicates a potential dangerous operation when the input can be adjusted by an attacker. More details can be found here.

Mitigation

If you need to provide a sorting column in your web application you should implement a whitelist to validate the value of the order by, it should always be limited to something like 'firstname' or 'lastname' or 1,2 etc. Remember it is a combination of both, you should use both always use a prepared statement when dealing with SQL but also use input validation on parts of the query which are not seen as a dynamic query parameter when using a prepared statement.

shadow-left