I actually witnessed SQL Injection

SQL injection is one of those hacks you can do on websites with really bad security practices. It can occur whenever your database query includes user input. If the user puts something you don’t expect, they can alter the database in ways that you don’t expect.

A funny example – which is kind of famous in engineering circles – is given in the webcomic XKCD.

Now about 10 years ago, I coded up a site called Stickymap. It was a local search where users can post locations in their neighborhood that are interesting and leave description. It was coded in PHP. You can secure PHP if you’re careful but it’s very difficult to do so. If you use PHP in your organization, there should be very specific rules around running SQL queries.

Well – one of my queries did not escape the user generated data. And, long story short someone changed every single venue name to “Bureau Veritas”. Every single one. In the world.

After I investigated, I don’t think that this was the intent. I think that the user was trying to add a (very spammy) description to a single venue that short-circuited the query so that the “WHERE clause” didn’t make it in. For those of you who don’t know, the WHERE clause in an UPDATE statement tells the database which items to update. If there is no WHERE clause, it’ll update everything. Pretty insane, right?! It should probably update nothing.

I wonder how that person/spammer felt after they did this. Where they shocked? Did they move on to another site? Who knows!?

Fortunately, I had enough backup data to restore the Stickymap database while I was in San Francisco. Of course this always happens when I’m in San Francisco away from my home computer!!

Furthermore, I plugged up the security hole on the site. It’s pretty cool that the security hole was left unexploited for 10 years and then all of a sudden was found. Who knows what problems we have lurking in our more critical systems? I like to hope those are more widely tested. You also want to see systems that hackers are constantly trying to exploit because that means that the owners of that system have been forced to plug the security holes. For example, I would rather trust software that’s been cracked and plugged a few times in the past than software that’s never been hacked but also never left out in the wild either.

Anyway – if someone out there wants to tell me there are more security holes in my site – let me know! But please try not to destroy Stickymap – it’s my fun mid-2000s space on the internet and a reminder of how far we’ve come on local search.

And if you are the accidental culprit and you come forward, I’ll either interview you for the blog, or I’ll owe you a beer!