SQL Injections Suck

Maybe the most common way how websites get hacked today is through SQL injections. These kind of vulnerabilities are easy to find for people with too much time on their hands (and trust me, there are lot of those).

The idea is simple. A website uses SQL to retrieve data from a database. Queries are built dynamically to retrieve the data that the user requested. For example when a user requests article.php?id=123 the query to get the requested articles is something like SELECT * FROM articles WHERE articleid = 123;. Just simply inserting the id value from the request string into the query, though the obvious solution, is very dangerous, though. For example, what if I do this request? article.php?id=0;SELECT%20password%20FROM%20users%20WHERE%20isadmin=1. This results in the query SELECT * FROM articles WHERE articleid = 0;SELECT password FROM users WHERE isadmin=1; It very well may be that somebody just obtained your admin’s password. Not something that you want. You can avoid this kind of thing by checking all your input for valid values. Numbers should have numeric values, the apostrophes (‘) in strings should be escaped before being inserted into a query, things like that. Many programming APIs provide support for this through parameterized queries.

Parameterized queries have two advantages. The first one is that they will handle any kind of escaping for you, they will simply not allow any SQL injection (as this vulnerability is called). The second advantage is that the query can be parsed once and not on every call. This make repeated use of a parameterized (or prepared) query faster. A parameterized queries looks something like this SELECT * FROM articles WHERE articleid = ?. The ? will be replaced with a value on execution. Some APIs use other ways to mark the places where values should be inserted. For example, some use named ones: SELECT * FROM articles WHERE articleid = @id, useful with many parameters.

Why I mention this now? Because somebody recently found an SQL injection bug in “KeyTopic”:http://www.keytopic.com. I know about SQL injections of course, and tried my best to make sure they cannot be used on KeyTopic, but apparantly forgot one place. On login you could login as me by using this as your username: Zef’ or 1=’1. As you can see, these hacks can be done really easily. I should have used parameterized queries in KeyTopic, but I didn’t really know of them when I wrote it.

You can find “a good article about SQL Injection attacks here”:http://www.unixwiz.net/techtips/sql-injection.html.