SQL injection: when a prepared statement is not enough…

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:

When we call the endpoint, we will receive:

If you for example supply the following last name:

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:

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:

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:

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:

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:

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(http://sqlmap.org/) 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:

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.


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.

Leave a Reply

Your email address will not be published. Required fields are marked *