Wednesday, July 23, 2008

Government IT watch

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:
"UPDATE users SET firstname='" . $new_name . "' WHERE lastname='" . $last_name . "';"
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:
";DELETE FROM users;"
This will give the following SQL query in the back end:
"UPDATE users SET firstname=';DELETE FROM users;' WHERE lastname='" . $last_name . "';"
Since semi colons end SQL statements, that will parse into three separate SQL statements, so far as the database engine is concerned:
1. "UPDATE users SET firstname=';"
2. "DELETE FROM users;"
3. "' WHERE lastname='" . $last_name . "';"
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.
That's because:
Asprox uses SQL injection on vulnerable Active Server pages to compromise weak sites.
Active Server Pages run on Microsoft servers. Two lessons come out of this:

  1. Many government projects lack even the most rudimentary security design specifications and quality control
  2. You have to be mad to use a Microsoft server


Mark Wadsworth said...

Cool. Have you ever done that for a giggle?

Peter Risdon said...

Not maliciously, but I get paid to test security on systems sometimes.

SnoopyTheGoon said...

Two words only:

Bad programming.

Or another two words for solution:

Static SQL

Anonymous said...


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.

Peter Risdon said...

I just like to get in a dig at MS from time to time :-)

cabalamat said...

There's a third lesson to be learned:

3. don't employ incompetent people/contractors.

And a fourth:

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.

Anonymous said...

"You have to be mad to use a Microsoft server"

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".

Peter Risdon said...

"That's a little unfair."

Certainly - I think I acknowledged that already. Making gratuitous snarks about MS is a personal weakness.