Wednesday, August 8, 2012

The Dangers of SQL Injection

If you're not participating in the world of web sites development then you will probably find this post of no interest - though if you own a web site that somebody else has made for you, you might want to ask them about this phenomenon.

Somewhere in my memory there lay the briefest of mentions of 'SQL Injection', something that I had stored away into the category of 'it won't happen to me'. But then it did. This post is just to stir the memory of folk like me, and remind them that actually it could very easily happen to them, and when it does, you won't like it...
 
My site, DriveArchive, has been running quite happily for ten years now, which is half a lifetime when it comes to the Internet. I check it most days, just to see if it's actually still there. One day it was indeed there, but it was a mess. The site itself is based on a complicated SQL Server database, very nearly every page has content that exists not in HTML, the underlying code of web pages, but as records in the database which are used to generate the HTML as the page loads.

The site was all over the place, and so I looked at my database. It had become corrupted, in a very odd way. Some of it was fine, some of it was not. The bits that were not had had text data replaced with the name of a web site which it turned out was a dangerous, malware-infested place to go to.

How the heck had that happened? My research began. Well... first job was to fix it. I found to my horror that the last time I had backed up the database was months ago. What a plonker. Though in my defense I have to say that the only method provided by Fasthosts, my hosting company, was so lengthy, difficult and unsatisfactory that it does not encourage frequent backups, quite the opposite.

And, just while I've mentioned them, and I have no reason to believe other hosting companies are better or worse at this, my pleas to them to restore my database fell on deaf ears. It was pointed out to me that they expressly do NOT ever restore your data for you. Nice. Maybe I really should have read the fine print. Thanks, Fasthosts. If I looked in my control panel the database was flagged as being backed up the day before. I'm not entirely sure why they bother if they won't restore it for you!

It took me 10 solid hours of work to fix the database to something near its former glory. I looked into how it had happened, and the likely way was 'SQL Injection'. If you Google that, you'll find out all about it, I won't duplicate all that on-line knowledge here. I sat and coded a defense to the attack, and went to bed.

Next morning, what did I find : it had happened again! What a pain... This time I looked through the logs to my site, reams of relatively meaningless drivel, but there, in the middle of it all was the answer, I found the way in. I fixed it. The hacker is still trying the same trick, but to no avail now.

So... I know all this is a massive IT cliche, but listen to one who now knows... firstly, no matter how awkward, DO YOUR BACKUPS. And secondly, if you have a web site that depends on an SQL based database, find out about SQL Injection and how to stop it - before it's too late.

If you'd like to discuss anything more about this topic, feel free to contact me