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.
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,
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
into the chatbox, and got lucky that I’d named my post table something obvious
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:
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.