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

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 to 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/PreparedStatement and executeQuery/`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:

doInJPA(entityManager -> {
    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();

If you need to build dynamic SQL queries, then you should use jOOQ instead.

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

If you enjoyed this article, I bet you are going to love my book as well.

Conclusion

SQL Injection is a real problem, and there are many documented real-life incidents that have lead 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.

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.

Enter your email address to follow this blog and receive notifications of new posts by email.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s