Protect the Melon, er, the Database!

Little Bobby Tables comic used with permission from xkcd.com.

In the related article Encrypting JDBC Connection Strings I explored one technique to externalize an encrypted username, password, and connection string. Here I want to explore some advanced methods to protect the data source, starting SQL injection attacks and moving forward.

SQL Injection Attacks

Consider the following Groovy&Grails example (it's a lot like Java), where the user is prompted for a student id and an SQL statement is constructed to retrieve that row from the students table. In Grails the form fields arrive at the method as a map of name=value pairs, and Groovy has cool parameter-substitution in strings. It is pretty clear how the student ID ends up in the SQL string:

public Student updateStudent(fieldMap) {

@tab;def sql = "select * from students where studentId = \"${fieldMap['studentId']}\""

@tab;def student = Student.find(sql)
}

Given a student id of '0395869' the SQL created will be:

select * from students where studentId = "0395869"

The process of building the SQL command is the basis of an SQL injection attack, which is not complicated at all.  It originates with the client adding extra SQL commands to data used to build an SQL statement. Given a studentId field with '0395869"; drop table students; --' the resulting SQL statement ends up reading:

select * from students where studentId = "0395869"; drop table students; --"

The attacker has correctly guessed that the id will be inserted at the end of the SQL command and a table in the database is named students, and has cleverly manipulated the quotation marks to add an unintended delete command following the select statement. Whoops, Little Bobby Tables has tricked the database server into dropping the entire students table!

Fixing the Problem

Preventing SQL injection attacks is simple as the attack: use parameterized queries, or stored procedures, which also use parameters! When a query or stored procedures is passed to the database engine with parameter values the database inserts the values but never allows one command to become multiple commands.

In Groovy&Grails this is as simple as constructing a string containing named placeholders for the parameters, and passing a map of name=value pairs to fill those parameters when the database engine executes the statement:

public Student updateStudent(fieldMap) {

@tab;def sql = "select * from students where studentId = :studentId"

@tab;def student = Student.find(sql, [studentId: fieldMap['studentId'])

@tab;// Or, since we already have a map containing the studentID passed to this method...

@tab;def student = Student.find(sql, fieldMap)
}

Take it to the Next Level!

We are not done yet! The next level of defense is to limit what access to the database the application has. This can be accomplished using two mechanisms in a database. The first is to use grant commands to limit access to only those database structures, frequently the tables, that the application must use. That is simply done by denying access to everything for the database user, and then granting those rights it needs.

An even better approach in the database is to turn everything the application requires into stored procedures. A sometimes overlooked feature of database engines is that stored procedures can have access to tables the user executing them does not have. So the application can be given access to the stored procedure without being given access to the tables behind it.

The stored procedures could only allow queries for certain rows of data, and inserts, updates, or deletes to single rows. The application cannot touch anything else in the database, so the wholesale mining or destruction of records from the database becomes much more difficult.

Up One More Notch...

The final touch is to limit where a particular user (the application in this case) can connect to the database from. This could be accomplished in the database engine, with a firewall on the database server, or a firewall in front of the database server. 

With this protection in place it makes it that much more difficult for an attacker to target a database, because it has to be done from the application server. That is potentially more noticeable than a database attack originating from some other computer on the local network. As a general practice, critical database servers should only be accessible from a limited number of computers anyways.

So, I have given you three levels of protection that will build the best wall possible between the application server and the database server. I hope your application server is never compromised, but if it is these techniques will help limit the damage.

References

See the references page.