Wednesday, July 23, 2008

SQL injection is one of the most common types of security problem with database-backed websites. I'm going to give a brief and slightly simplified technical description for those who don't know what it is, because that's the only way I can show how obvious - and easily avoided - it is.

A SQL query is almost plain English. This one:

"UPDATE users SET firstname='Fred' WHERE lastname='Bloggs';"

updates a database table called 'users', changing the value of the column 'firstname' to 'Fred' in every row in which the last name is 'Bloggs'. The query ends with a semi colon. Another example of an SQL query is this:

"DELETE FROM users;"

which will delete every record from the users table - there's no WHERE condition, so it just does the lot.

If a website allows people to change their first names, then it will have a form on a web page in which people can type the new name to use. So if Fred Bloggs has a sex change, and wants to update her record to Frederika Bloggs, she could type this new first name into the box, click "SUBMIT", and the database will be updated.

Behind the scenes, a server-side script of some kind will have the new name she typed available to it as a variable. For the sake of argument, I'll call this variable $new_name - for these purposes a variable is like an envelope in which some value can be stored; the value can vary but the word on the outside of the envelope is always the same, so it's easy to use. The WHERE clause will also need to know what the relevant surname is, so we'd need a variable called $last_name too.

This script will then bung the variable in the right place in the SQL query that needs to be executed, maybe a bit like this:

That's fine when Fred changes her name to Frederika. But a malicious user might type this in the web form where the new name is supposed to go. Instead of a name, it could be some sort of new SQL that starts with a semi colon to terminate the query it will get placed inside:

The first and third are incomplete and will generate errors. The middle query will wipe all the user records from your database. Malicious SQL has been injected into the website backend.

Not rocket science, is it? How do you prevent this sort of attack from succeeding? You mistrust all user-supplied data and do stuff to sanitise it when you develop a website. This is a first principles thing. It isn't advanced and any non-technical reader here could, I believe, understand it.

If that's you, then you now know more, apparently, than the developers the government hires to build some of their websites. That's what this really boils down to:

In the past fortnight Eastern European hackers have infected more than a thousand British websites with the virus, known as Asprox, including those belonging to local government offices and the NHS, it has been claimed.

It's basically a classic SQL injection attack, albeit that SQL Server has features to iterate around table and column names that mean that such a payload can be delivered without knowing the table and column names, so every value in the database can be filled with something to include some javascript). Normally, rather useful features if you're a DBA...

The thing is, this happened before in late April, and whilst someone should do the full checking for SQL injection, there's certainly a trivial way to switch off the table/column iteration by denying rights on the user to do a select on sysobjects/syscolumns.

What most developers on MS kit do is to wrap their DB logic in stored procedures with parameters and then the sql calls will escape the inputs for you, so it's not a problem. The DBA will then deny rights to the web user to do anything but call through the stored procedures for added protection.

4. the people managing the project need to have enough technical competence to to be able to judge whether those they employ are competent. I bet most ministers and senoir civil servants have never heard of SQL, let alone an SQL injection attack.

That's a little unfair. I am sure that if you were as incompetent as the State, you could allow your ingres/sybase/db2/os400/(insert database engine of your choice here) system to behave the same way when presented with crap input.

What you could have said more fairly might be "You have to be mad to allow idiot developers to make schoolboy errors when creating your production web site".