The Perils of SQL Injection

10 06 2011

So what exactly happened to zFire’s site? What is an SQL injection attack? Why was his site so vulnerable?

These are some of the questions some people would like to fill in. This post is in the geeky/technical category, although I will attempt to write it in a way that non technical people can see how appallingly lax was the security on zFire’s site (something I had pointed out on this blog in my comments to zFire when he posted here, but he chose to ignore them).

In short, zFire’s security failings included the following major gaffes (among many others):

  1. His PHP scripts displayed error messages on output pages, where anyone could read them. Many people do not think this a major failing, but I will explain below why this was one of the biggest mistakes he could make.
  2. He was running his SQL server as root. Many people know this is bad, but perhaps many do not realise quite why this is so bad
  3. He did not sanitise input to his scripts (until it was much too late). This is the key to SQL injection attacks
  4. On being hacked, and being alerted to it, he soldiered on with a system in an unknown state when he should have taken the system offline, reverted to a known good state (if such actually existed) and hardened the service before relaunching

Just to be clear, by the time the Mariana video was out, zFire’s days were numbered. I do not think in his case that fixing the database was worth the effort by this stage. Nevertheless for anyone else considering running web based services with PHP and MySQL, I think there is much to be learned from zFire’s example. I therefore offer this post as a guide for strengthening future systems, and hope readers will be interested in it for that reason.

I also add that these are not the total of the failings by zFire. He made many other mistakes, but these seem to be the key ones to the SQL injection attack.

Let’s take a closer look at the issues.

Firstly: Error messages displayed by the web service were highly revealing. When the databases was overloaded (as often happened due to the volume of web requests from RedZone devices), SQL commands would frequently fail. This was most easily spotted on the forums, although I also spotted it when I was researching my “adventures in RedZone” by seeing what false data could be injected into the database through completely legal HTTP GET requests.

These error messages, for instance, revealed the document root of zFire’s scripts, which was my first indication he was running an Apple Mac. But they could also provide information to anyone attempting an SQL injection.

Blind SQL injection (without error messages) is possible, but much harder. Providing error messages to someone knowledgeable in PHP and MySQL was like giving them yes/no answers in 20 questions… is this the name of a table column? no – that one is an error. Ok what about this one? oh yes, that is ok.

Error messages are useful to a programmer to debug a program. They are designed to give as much information as a programmer needs to track down and fix a problem. But that means they give way too much information to a cracker.

If you run PHP/MySQL applications, do not under any circumstances display error messages on the live site.

The second issue was the SQL server was running as root. Someone commented on this blog about that (again, it was revealed in error messages). The person who commented admitted they were no programmer, but even in their experience they knew this was wrong.

Indeed, all the advice for running web services is summed up in the Principle Of Network Administration

Principle 4 (Minimum privilege). Restriction of unnecessary privilege protects a system from accidental and malicious damage, infection by viruses and prevents users from concealing their actions with false identities. It is desirable to restrict users’ privileges for the greater good of everyone on the network

Principles of Network and System Administration – Mark Burgess.

If people applied that principle both for services and for user accounts in use, we would see most security issues vanish away overnight.

But what is specifically wrong with running an SQL service as root? Specifically, pretty much everything. Inexperienced SQL users may think that SQL is all about manipulating tables. They may think that the worst an SQL server can do is drop the tables and kill the database. Inexperienced SQL users like zFire are not aware of the SQL commands that allow the SQL user to read from and write to the filesystem with the privelege of the SQL service user.

If your SQL service runs as root, and someone can run arbitrary SQL on your service, then that cracker can read *every* file on your filesystem.

Yes, even the system password hashes file (if they know where to look). Is your system root password uncrackable? No? Do you allow remote access through SSH or VNC or some other service? Yes?

Bang! You now no longer have a cracker in your database, you have a cracker all over your system.

And even if you do not leave things that wide open, be sure that the cracker can look at all your log files and every other document you thought was private on your system.

Never, ever, ever even consider running a live service under the root user. Run the service with the lowest privelege you can. SQL usres should specifically not have read permissions pretty much anywhere on the filesystem, other than the database directories themselves.

Thirdly Always, Always, Always sanitise your database inputs. To be honest, even this is not really good enough. You should really write your databases differently. Instead of inserting code into SQL statements, send the sanitised input as variables to stored procedures. But zFire would have done so much better if he had taken the minimal step of sanitising input.

Let’s look at some code examples.

Suppose that the web form requests username and password and then passes it to a PHP code snippet similar to this:

$items=@mysql_query("SELECT username, password FROM users WHERE username='$username' AND `password`='$password' );

The problem is that if you allow someone to enter a username something like this, and leave the password blank:

zFire Xue' #

then look what the select statement becomes:

SELECT username, password FROM users WHERE username='zFire Xue' #' AND `password`='$password'

Everything after the # is ignored as a comment (other versions of SQL use different comments, but whatever comment character you use, the implication is the same). This SQL code always returns a row from the database, as long as the name you choose exists in the database. Consequently you are allowed in. With the privelege of the name you chose!

And that is it. The key to breaking into zFires database was to add ‘# after whichever user you wished to impersonate! No password was required and you could control all that user’s redzones. This hole, which falls under the category of “stupidly obvious” would allow you full administrative access to zFire’s redzone account (or anyone elses).

Now suppose that zFire tried to log access to his site (we know he did), his log statement would look like:

INSERT into log (username, password, accesstime)
VALUES ($username, $password, CURDATE())

This, with our input above, becomes:

INSERT into log (username, password, accesstime)
VALUES ('zfire xue' #' , '', '2011-6-4 04:13:54')

The italicised bit after the # is ignored, and the insert fails as it is not properly formed. In other words, no line gets inserted into the log. All such accesses to the database were invisible to the log files.

But, in fact, you can do so much more than gain access to the web forms. For instance, if an SQL statement returns data, you can merge the data returned to the user with, say, the contents of a file from the filesystem.

You could use this to, say, look at the code of the pages themselves – thus finding more security holes – or hidden links to secret pages with videos meant for your girlfriend.

You could look at the SQL connection string file, which includes the SQL password. You could investigate system files and generally wlts all over the system. Which is why we turn to lesson 4.

Fourthly, when hacked, do not paper over the cracks.

Rémy Evard produced a software lifecycle that noted how systems move from a configured state towards an unknown state through an entropic process of constant update, change and debug.

When your system is hacked it takes the fast track route from the configured state to the unknown state.

Sure if some dumb script kiddy downloads software that justs repeatedly crashes your server then its likely you know what is going on. But if you have had a more sophisticated cracker in there – the kind who has been poking your system for weeks or months, and you haven’t spotted them – then really you have no idea what state your system is in.

zFire’s system was vulnerable to attack from day one, and with such obvious security holes in it, it is implausible that no one was inside that system before the script kiddies had a go at it. As we have seen, it is quite likely that those crackers had far more than access to the web site interface. There is no doubt they held a copy of his code, his SQL passwords and pretty much anything else they wanted.

So zFire’s response to being hacked was a lesson in stupidity. (As was the challenge that got the script kiddies running). On realising he has a fundamental security hole in his system, the correct response, in my view would have been to:

  1. Take the system offline to evaluate the extent of intrusion. This is the point you should be contacting the police if it happens to you. Pull out the network cable and call the police. Don’t touch the evidence. However, if you do not wish to involve the police, still take it off line. Look at the logs and the code, and see if you can determine when the crack firts occurred.
  2. Restore the system to a known state – which means restore from a back up before the earliest time a hack could have occurred. This is bad news for zFire – he was using time machine backups and frankly he did not have a backup from that far back. Nevertheless this is good advice. restore to a known good state – which may be your original build. Consider if your system has a kernel root kit installed – it will not be found. You need to wipe everything and start over.
  3. Change ALL your passwords EVERYWHERE. Not just your web site password. Not just the connection string. ALL passwords. And this time don’t make them so crackable. After the crack, zFire changed his RedZone password, although not the passowrds of his alts. After being hacked again, he changed the passwords of users AND alts. When he was promptly hacked again he really should have noticed that passwords were not helping.
  4. Fix the code. this seems obvious. But what is not obvious is how to fix it. zFire’s solution was to try to capture certain SQL strings. What he should have done was rewrite the whole application from scratch. Take your time. You are now a target, so get the rewrite right. Buy in some consultancy if necessary
  5. Don’t trust your data. People have been modifying it. It is no longer correct. restore to the last known good data or just start over

So there you have it – four things zFire really should have done (well the fourth is a list in itself I know, but the point is – never soldier on with a crippled system. Start over).




%d bloggers like this: