Wednesday 30 September 2009

SQL Injection is not a Trivial Problem

Dear Junior

If Domain Driven Security is to be useful, then it must provide some value in usual situations – situations that programmers encounter every day.

So, what are those usual security problems in applications? Well, to start let’s note that we are not looking for the hardest problems, neither for the most intricate solutions. We are looking for the kind of stuff that does harm, just because we have no really good way to handle them.

If we have a look at OWASP Top Ten we find Cross-Site Scripting (XSS) at the top. It is a really interesting problem, but as XSS is not so widely understood I’d like to save that for a moment. Instead, let us have a look at number two and see if we can wrap our heads around it. At number two on the list we find Injection Flaws with SQL Injection as its prime representative. Well, SQL Injection is no news so let’s go for that one.

The canonical SQL Injection flaw is a login/authentication service that handles its parameters a little bit too sloppy.

/** Service for management of user accounts,

* authentication etc. */

public class AccountService {

private DataSource accountDs;

/** Authenticates a user with a given password.

* @param username

* @param passwordMD5 hash of password

* @return user id, or null if no matching account


Integer authenticate(String username, String passwordMD5)

throws SQLException {

Connection con = accountDs.getConnection();

Statement stmt = con.createStatement();

String sqlSelect = "SELECT uid FROM Accounts";

String usernameMatch = "username = '" + username + "'";

String passwdHashMatch = "passwdHash = '" + passwordMD5 + "'";

String sql = sqlSelect +

" WHERE " + usernameMatch +

" AND " + passwdHashMatch;

ResultSet rs = stmt.executeQuery(sql);

Integer result;

if( { // found account with matching password

result = rs.getInt("uid");

} else { // no matching account

result = null;


return result;



The string parameters are probably fetched from some form, where the password is hashed with MD5 before sending off for authentication.

Typing in username’ OR 1=1 -- (note the initial single quote) and password doesntmatter, the executed SQL will look like this, with everything after “--“ being an SQL comment..

SELECT uid FROM Accounts WHERE username = ‘’ OR 1=1 -- ‘ AND passwdHash = ‘doesntmatter’

Obviously “1=1” is true for any row, so we will get back all rows in the Account table. From this resultset we will get the user id of the first row. And, there is a fair chance this is the first user that entered the database, which might be the fully authorized “bootstrap init user”. So, presenting yourself as ’ OR 1=1 --/doesntmatter gives you full privileges to the system.

A usual reaction on this attack is “what the ---, but that is not a proper username” often combined with “that is just bad indata validation”, where after the problem is dismissed as trivial, uninteresting, and not worth further efforts.

True, the attack string might not be what we consider a valid user name, and there are problem with the indata handling. However, obviously that analysis does not suffice. If those two sentences of analysis where useful, then SQL Injection would not be number two de facto problem on the OWASP list. Bear in mind that the OWASP Ten Top is not the list of the hardest problem around – it is about what we actually find in code out there, and that are exploited on a daily basis. And most of that code is written by pretty smart people.

The usual advice to solve SQL Injection is to use prepared statements. In Java, the SQL API have a PreparedStatment interface, and using “SELECT uid FROM Accounts WHERE username = ? and passwdHash = ?” solves the problem in this specific case. If all SQL Injections where like that, the story could end there.

Unfortunately, the situation can be more convoluted. The SQL might be inside a stored procedure which we call, or locked up in some part of the application we do not have access to – third part vendor or other department with which we have complicated diplomatic relations (yepp, such happens).

It might also be that we are not working with SQL, but with some (possibly legacy) backend-system with some SQL-like syntax, and where the API (if such exists) does not provide any equivalence of prepared statements.

It is easy to dismiss SQL Injection with “bad indata validation” and “use prepared statements”, but I think that is a mistake made by analysing the simplest example without considering that the situation could be a lot more complicated. Had it been so simple, then it would not be so many problems – so evidence speaks against the “SQL Injection is simple” hypothesis.

SQL Injection is clearly a problem that needs to be taken seriously, analysed more in depth, and target for finding design patterns and mindsets that address and avoid it.



ps Of course, in-data validation is a nice step in a good direction