My earliest attempts to counter SQL injection

In 2003, this site was hacked and the content was lost. This is why there are no posts from before 2003, despite my registering the domain and running the site since 2001.

I don’t have anything written down about what happened. The site’s code was probably the very first web application I wrote, likely in 2001. Back then, I’d written dx13’s backend in original ASP, using Javascript (I was writing server-side JS a whole eight years before node.js was even named node, fellow hipsters 😬). It was running on a shared virtual server and backed by a MySQL database. What exactly went wrong is lost to time, but I came across a clue today.

Hugo will add smartquotes and such during the site build, so I’d decided to swap out smartquote HTML entities with plain quotes to make the raw markdown more readable. I discovered that I had several posts where the word drop had been replaced with the HTML entity equivalent, drop. Presumably I’d transformed the posts before writing the post to the database. This stops around the time I moved the site to a Ruby backend.

From this, I guess what I’d seen happen in 2003 was a SQL injection attack. After the attack, I probably saw some SQL code entered into a chat box that I had coded up for the sidebar of the site. A classic SQL injection attack. As I’d never heard of the term “SQL injection attack” in 2003, I suspect I did precisely zero to protect against it. I bet that someone stuck a drop table... into the chatbox, and got lucky that I’d named my post table something obvious like posts. Something of that nature.

So I suspect I saw the drop table and wrote a shared function that was designed to sanitise uploaded content against certain keywords. I had a further look for odd HTML entity strings. Sure enough, further digging turned up a couple more SQL modification keywords replaced with entities:

delete to:
```
&#100;&#101;&#108;&#101;&#116;&#101;
```
insert to:
```
&#105;&#110;&#115;&#101;&#114;&#116;
```

In the intervening 20 years I’ve learned that the right solution for this is SQL parametrised queries. But, overall, given my level of knowledge of databases and web applications in 2003, I’m not that displeased with my past self’s solution to this.