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