Leave a Comment

Posted on November 8, 2016 by

A beginner’s guide to SQL injection and how you should prevent it

Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?

Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.

So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!

Introduction

One of my readers asked me to answer the following StackOverflow question. Right from the start, I noticed that the entity query was constructed by concatenating strings, which can expose your application to SQL Injection attacks.

Unfortunately, I’ve been seeing this problem over and over throughout my career, and not all developers are aware of the serious risk associated with SQL Injection. For this reason, this post is going to demonstrate what damage can SQL Injection do to your system.

What is SQL Injection?

SQL Injection happens when a rogue attacker can manipulate the query building process so that he can execute a different SQL statement than what the application developer has originally intended.

When executing an SQL statement, you have basically two options:

When building the underlying SQL statement, if you concatenate strings, both java.sql.Statement and java.sql.PreparedStatement are prone to SQL Injection attacks.

When it comes to executing a statement, java.sql.Statement and java.sql.PreparedStatement define two methods:

Depending on the Statement or PreparedStatement and the executeQuery or `executeUpdate’ combination, the SQL Injection attack target can vary, as demonstrated by the following scenarios.

Next, we are going to investigate what happens with both

Statement and executeUpdate

This is the most vulnerable combination. Assuming you have the following data access method which updates the review column of a post_comment table row:

public void updatePostCommentReviewUsingStatement(Long id, String review) {
    doInJPA(entityManager -> {
        Session session = entityManager.unwrap(Session.class);
        session.doWork(connection -> {
            try(Statement statement = connection.createStatement()) {
                statement.executeUpdate(
                    "UPDATE post_comment " +
                    "SET review = '" + review + "' " +
                    "WHERE id = " + id
                );
            }
        });
    });
}

Ans you typically call it like this:

updatePostCommentReviewUsingStatement(
    1L, 
    "Awesome"
);

A rogue attacker can simply do the following SQL Injection attack:

updatePostCommentReviewUsingStatement(
    1L, 
    "'; DROP TABLE post_comment; -- '"
);

And this is what the database is going to execute:

Query:[
    "UPDATE post_comment SET review = ''; DROP TABLE post_comment; -- '' WHERE id = 1"
], 
Params:[]
  • the UPDATE is executed first
  • then comes the DROP statement
  • the -- comment syntax ensures that we ignore the rest of the original query WHERE clause

After executing the SQL Injection attack, when can try to load a post_comment record to see if the SQL Injection attack was successful:

PostComment comment = entityManager
.find(
    PostComment.class, 
    1L
);

assertNotNull(comment);

Oracle

On Oracle 11g, the SQL Injection statement fails because the JDBC driver does not recognize the ; delimiter symbol.

Query:["UPDATE post_comment SET review = ''; DROP TABLE post_comment; -- '' WHERE id = 1"], Params:[]
WARN  [Alice]: o.h.e.j.s.SqlExceptionHelper - SQL Error: 911, SQLState: 22019
ERROR [Alice]: o.h.e.j.s.SqlExceptionHelper - ORA-00911: invalid character

Query:["select p.id as id1_1_0_, p.post_id as post_id3_1_0_, p.review as review2_1_0_ from post_comment p where p.id=?"], Params:[(1)]

SQL Server

On SQL Server 2014, the SQL Injection statement runs just fine and the post_comment table is dropped.

Query:["UPDATE post_comment SET review = ''; DROP TABLE post_comment; -- '' WHERE id = 1"], Params:[]

Query:["select p.id as id1_1_0_, p.post_id as post_id3_1_0_, p.review as review2_1_0_ from post_comment p where p.id=?"], Params:[(1)]

WARN  [Alice]: o.h.e.j.s.SqlExceptionHelper - SQL Error: 208, SQLState: S0002
ERROR [Alice]: o.h.e.j.s.SqlExceptionHelper - Invalid object name 'post_comment'.
INFO  [Alice]: o.h.e.i.DefaultLoadEventListener - HHH000327: Error performing load command : org.hibernate.exception.SQLGrammarException: could not extract ResultSet

PostgreSQL

On PostgreSQL 9.5, the SQL Injection statement runs just fine, and the post_comment table is dropped.

Query:["UPDATE post_comment SET review = ''; DROP TABLE post_comment; -- '' WHERE id = 1"], Params:[]

Query:["select p.id as id1_1_0_, p.post_id as post_id3_1_0_, p.review as review2_1_0_ from post_comment p where p.id=?"], Params:[(1)]
WARN  [Alice]: o.h.e.j.s.SqlExceptionHelper - SQL Error: 0, SQLState: 42P01
ERROR [Alice]: o.h.e.j.s.SqlExceptionHelper - ERROR: relation "post_comment" does not exist

MySQL

On MySQL 5.7, the SQL Injection statement fails because the JDBC driver does not compile properly the multi-statement DML statement.

Query:["UPDATE post_comment SET review = ''; DROP TABLE post_comment; -- '' WHERE id = 1"], Params:[]
WARN  [Alice]: o.h.e.j.s.SqlExceptionHelper - SQL Error: 1064, SQLState: 42000
ERROR [Alice]: o.h.e.j.s.SqlExceptionHelper - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DROP TABLE post_comment; -- '' WHERE id = 1' at line 1

Query:["select p.id as id1_1_0_, p.post_id as post_id3_1_0_, p.review as review2_1_0_ from post_comment p where p.id=?"], Params:[(1)]

Although the first SQL Injection attack did not work on all database, you’ll soon find that every database is prone to at least one SQL Injection variant.

PreparedStatement and executeUpdate

When we change the previous example to use a PreparedStatement while still avoiding bind parameters:

public void updatePostCommentReviewUsingPreparedStatement(Long id, String review) {
    doInJPA(entityManager -> {
        Session session = entityManager.unwrap(Session.class);
        session.doWork(connection -> {
            String sql = 
                "UPDATE post_comment " +
                "SET review = '" + review + "' " +
                "WHERE id = " + id;
            try(PreparedStatement statement = connection.prepareStatement(sql)) {
                statement.executeUpdate();
            }
        });
    });
}

And rerunning our previous test case:

updatePostCommentReviewUsingPreparedStatement(
    1L, "'; DROP TABLE post_comment; -- '");

doInJPA(entityManager -> {
    PostComment comment = entityManager.find(
        PostComment.class, 1L);
    assertNotNull(comment);
});

We get the following results.

Oracle

On Oracle 11g, the SQL Injection statement fails because the JDBC driver does not recognize the ; delimiter symbol.

Query:["UPDATE post_comment SET review = ''; DROP TABLE post_comment; -- '' WHERE id = 1"], Params:[()]
WARN  [Alice]: o.h.e.j.s.SqlExceptionHelper - SQL Error: 911, SQLState: 22019
ERROR [Alice]: o.h.e.j.s.SqlExceptionHelper - ORA-00911: invalid character

Query:["select p.id as id1_1_0_, p.post_id as post_id3_1_0_, p.review as review2_1_0_ from post_comment p where p.id=?"], Params:[(1)]

SQL Server

On SQL Server 2014, the SQL Injection statement runs just fine, and the post_comment table is dropped.

Query:["UPDATE post_comment SET review = ''; DROP TABLE post_comment; -- '' WHERE id = 1"], Params:[()]

Query:["select p.id as id1_1_0_, p.post_id as post_id3_1_0_, p.review as review2_1_0_ from post_comment p where p.id=?"], Params:[(1)]
WARN  [Alice]: o.h.e.j.s.SqlExceptionHelper - SQL Error: 208, SQLState: S0002
ERROR [Alice]: o.h.e.j.s.SqlExceptionHelper - Invalid object name 'post_comment'.

PostgreSQL

On PostgreSQL 9.5, the SQL Injection statement runs just fine because, by default, PreparedStatements are only emulated and the prepare and execute phases are executed in a single shot.

Query:["UPDATE post_comment SET review = ''; DROP TABLE post_comment; -- '' WHERE id = 1"], Params:[()]

Query:["select p.id as id1_1_0_, p.post_id as post_id3_1_0_, p.review as review2_1_0_ from post_comment p where p.id=?"], Params:[(1)]
WARN  [Alice]: o.h.e.j.s.SqlExceptionHelper - SQL Error: 0, SQLState: 42P01
ERROR [Alice]: o.h.e.j.s.SqlExceptionHelper - ERROR: relation "post_comment" does not exist

MySQL

On MySQL 5.7, the SQL Injection statement fails because the JDBC driver does not compile properly the multi-statement DML statement.

Query:["UPDATE post_comment SET review = ''; DROP TABLE post_comment; -- '' WHERE id = 1"], Params:[()]
WARN  [Alice]: o.h.e.j.s.SqlExceptionHelper - SQL Error: 1064, SQLState: 42000
ERROR [Alice]: o.h.e.j.s.SqlExceptionHelper - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DROP TABLE post_comment; -- '' WHERE id = 1' at line 1

Query:["select p.id as id1_1_0_, p.post_id as post_id3_1_0_, p.review as review2_1_0_ from post_comment p where p.id=?"], Params:[(1)]

Prepared statements do not protect you against SQL Injection if you are not using bind parameters.

Statement or PreparedStatement and executeQuery

While the classic DROP table example is much easier to emulate with executeUpdate, with executeQuery, it’s not as easy to send an update statement while the JDBC driver expects a select statement instead.

Considering we have the following data access methods which read the review column for a given post_comment record:

public String getPostCommentReviewUsingStatement(String id) {
    return doInJPA(entityManager -> {
        Session session = entityManager.unwrap(Session.class);
        return session.doReturningWork(connection -> {
            String sql = 
                "SELECT review " +
                "FROM post_comment " +
                "WHERE id = " + id;
            try(Statement statement = connection.createStatement()) {
                try(ResultSet resultSet = statement.executeQuery(sql)) {
                    return resultSet.next() ? resultSet.getString(1) : null;
                }
            }
        });
    });
}

public String getPostCommentReviewUsingPreparedStatement(String id) {
    return doInJPA(entityManager -> {
        Session session = entityManager.unwrap(Session.class);
        return session.doReturningWork(connection -> {
            String sql = 
                "SELECT review " +
                "FROM post_comment " +
                "WHERE id = " + id;
            try(PreparedStatement statement = connection.prepareStatement(sql)) {
                try(ResultSet resultSet = statement.executeQuery()) {
                    return resultSet.next() ? resultSet.getString(1) : null;
                }
            }
        });
    });
}

When trying to execute the following SQL Injection attack:

try {
    getPostCommentReviewUsingStatement("1; DROP TABLE post_comment");
} catch (Exception expected) {
    LOGGER.error("Failure", expected);
}
assertEquals("Good", getPostCommentReviewUsingStatement("1"));

We’ll get the following results:

Oracle

On Oracle 11g, the SQL Injection statement fails because the JDBC driver does not recognize the ; delimiter symbol.

Query:["SELECT review FROM post_comment WHERE id = 1; DROP TABLE post_comment"], Params:[]
WARN  [Alice]: o.h.e.j.s.SqlExceptionHelper - SQL Error: 911, SQLState: 22019
ERROR [Alice]: o.h.e.j.s.SqlExceptionHelper - ORA-00911: invalid character

Query:["SELECT review FROM post_comment WHERE id = 1"], Params:[]

SQL Server

On SQL Server 2014, the SQL Injection statement runs just fine, and the post_comment table is dropped.

Query:["SELECT review FROM post_comment WHERE id = 1; DROP TABLE post_comment"], Params:[]

Query:["SELECT review FROM post_comment WHERE id = 1"], Params:[]
WARN  [Alice]: o.h.e.j.s.SqlExceptionHelper - SQL Error: 208, SQLState: S0002
ERROR [Alice]: o.h.e.j.s.SqlExceptionHelper - Invalid object name 'post_comment'.

PostgreSQL

On PostgreSQL 9.5, the SQL Injection statement fails this time:

Query:["SELECT review FROM post_comment WHERE id = 1; DROP TABLE post_comment"], Params:[()]
WARN  [Alice]: o.h.e.j.s.SqlExceptionHelper - SQL Error: 0, SQLState: 0100E
ERROR [Alice]: o.h.e.j.s.SqlExceptionHelper - Multiple ResultSets were returned by the query.

Query:["SELECT review FROM post_comment WHERE id = 1"], Params:[()]

MySQL

On MySQL 5.7, the SQL Injection statement fails because the JDBC driver does not compile properly the multi-statement DML statement.

Query:["SELECT review FROM post_comment WHERE id = 1; DROP TABLE post_comment"], Params:[]
WARN  [Alice]: o.h.e.j.s.SqlExceptionHelper - SQL Error: 1064, SQLState: 42000
ERROR [Alice]: o.h.e.j.s.SqlExceptionHelper - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DROP TABLE post_comment' at line 1

Query:["SELECT review FROM post_comment WHERE id = 1"], Params:[]

While the classic DROP table example does not work on all databases, it does not mean that all SQL Injection attacks will fail.

Denial-of-service attack – Make a query wait indefinitely

One very effective SQL Injection attack is to make the query wait indefinitely:

try {
    getPostCommentReviewUsingPreparedStatement(
        "1 AND 1 >= ALL ( SELECT 1 FROM pg_locks, pg_sleep(10) )");
} catch (Exception expected) {
    LOGGER.error("Failure", expected);
}
assertEquals("Good", getPostCommentReviewUsingPreparedStatement("1"));

When executing this SQL Injection attack on PostgreSQL, we get the following output:

Time:10126, Query:["SELECT review FROM post_comment WHERE id = 1 AND 1 >= ALL ( SELECT 1 FROM pg_locks, pg_sleep(10) )"], Params:[()]

Query:["SELECT review FROM post_comment WHERE id = 1"], Params:[()]

Check out the time column on the first query. With this SQL Injection attack, we can easily make all connections hang indefinitely and therefore render the whole enterprise system unavailable.

How to prevent the SQL injection attack

The solution is very simple and straight-forward. You just have to make sure that you always use bind parameters:

public PostComment getPostCommentByReview(String review) {
    return doInJPA(entityManager -> {
        return entityManager.createQuery(
            "select p " +
            "from PostComment p " +
            "where p.review = :review", PostComment.class)
        .setParameter("review", review)
        .getSingleResult();
    });
}

Now, when trying to hack this query:

getPostCommentByReview("1 AND 1 >= ALL ( SELECT 1 FROM pg_locks, pg_sleep(10) )");

the SQL Injection attack will be prevented:

Time:1, Query:["select postcommen0_.id as id1_1_, postcommen0_.post_id as post_id3_1_, postcommen0_.review as review2_1_ from post_comment postcommen0_ where postcommen0_.review=?"], Params:[(1 AND 1 >= ALL ( SELECT 1 FROM pg_locks, pg_sleep(10) ))]

javax.persistence.NoResultException: No entity found for query

JPQL Injection

SQL Injection can also happen when using JPQL or HQL queries, as demonstrated by the following example:

public List<Post> getPostsByTitle(String title) {
    return doInJPA(entityManager -> {
        return entityManager.createQuery(
            "select p " +
            "from Post p " +
            "where" +
            "   p.title = '" + title + "'", Post.class)
        .getResultList();
    });
}

The JPQL query above does not use bind parameters, so it’s vulnerable to SQL injection.

Check out what happens when I execute this JPQL query like this:

List<Post> posts = getPostsByTitle(
    "High-Performance Java Persistence' and " +
    "FUNCTION('1 >= ALL ( SELECT 1 FROM pg_locks, pg_sleep(10) ) --',) is '"
);

Hibernate executes the following SQL query:

Time:10003, QuerySize:1, BatchSize:0, Query:["select p.id as id1_0_, p.title as title2_0_ from post p where p.title='High-Performance Java Persistence' and 1 >= ALL ( SELECT 1 FROM pg_locks, pg_sleep(10) ) --()=''"], Params:[()]

Now, for test sake, I made this query wait for 10 seconds. However, a rogue attacker will make it wait for a much longer period of time.

Therefore, back to the original StackOverflow question question.

String hql = " select e.id as id,function('getActiveUser') as name from " + domainClass.getName() + " e ";
Query query=session.createQuery(hql);
return query.list();

While it’s much more difficult to inject a function since the entity query derives the entity type from a java.lang.Class variable, it’s still a very bad mistake to rely on string concatenation.

If you want to use dynamic queries, you need to use Criteria API instead:

String hql = " select e.id as id,function('getActiveUser') as name from " + domainClass.getName() + " e ";
Query query=session.createQuery(hql);
return query.list();

With Criteria API, you can build queries dynamically and in a safe manner:

Class<Post> entityClass = Post.class;
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> query = cb.createTupleQuery();
Root<?> root = query.from(entityClass);
query.select(
    cb.tuple(
        root.get("id"),
        cb.function("now", Date.class)
    )
);

return entityManager.createQuery(query).getResultList();

Writing JPA Criteria API queries is not very easy. The Codota IDE plugin can guide you on how to write such queries, therefore increasing your productivity.

For more details about how you can use Codota to speed up the process of writing Criteria API queries, check out this article.

Therefore, there is no reason why you should expose your data access logic to SQL injection attacks.

I'm running an online workshop on the 20-21 and 23-24 of November about High-Performance Java Persistence.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

SQL Injection is a real problem, and there are many documented real-life incidents that have led to credit card information or Telcommunication network customer data being stolen.

For this reason, you should always use bind parameters, and, for dynamic queries, you should rely on a framework that was designed for such a task, like Criteria API or jOOQ.

You should never resort to using String concatenation to build queries dynamically because this bad practice is prone to SQL injection attacks. To build native SQL queries dynamically, jOOQ is a very good framework that can help you achieve this goal.

The benefit of using prepared statements, it’s that you can better benefit from statement caching both on the client-side and on the database side as well. Some RDBMS allow you to reuse already computed execution plans, therefore providing better performance as well. For the very few cases when you don’t want to reuse an execution plan because that would perform worse than a dynamically-generated SQL statement, you should use the inline SQL statement feature offered by jOOQ.

Transactions and Concurrency Control eBook
Vlad Mihalcea Consulting
Hypersistence Optimizer rocks!

 Category: Hibernate, SQL      Tags: , , , , , ,

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Book
High-Performance Java Persistence Book
Training
High-Performance Java Persistence - Online Workshop
Video Courses
High-Performance Java Persistence Video Course High-Performance SQL Video Course
Hypersistence Optimizer
Hypersistence Optimizer

Vlad Mihalcea

Powered by WordPress.com.