For years I’ve known that SQL injection is bad and how to protect against it. In fact, I’d go so far as to say that everything except the very first classic ASP pages I wrote were safe against malicious SQL but for some reason I’d never actually tried attacking a vulnerable page to see what was really so bad about it.
We all know the XKCD comic about little Bobby Tables and yes you could simply drop the tables or database very easily on an unsecured page but what if you aren’t simply bent on destruction? As developers we spend our lives building little hand-holds into application to stop people breaking things, and that’s just for those that aren’t trying to do anything bad! What about those that want to slip in, infiltrate, and hold your organisation to ransom?
Here’s a brief example of how easy (scary) it is for one unchecked variable to be used as the gateway to read your entire database.
Some things to note:
- The pages are classic ASP because .NET does not easily let you be so careless
- PHP, just like Linux, will let you do what you want even if that is pure self-destruction
- The database back-end is MSSQL Express but this doesn’t matter too much as the theory is the same for any DB backend
First, I’m greeted by a login prompt:
Simply enter a single apostrophe into either field to see if this page is vulnerable.
Awesome! Not only is the page vulnerable, it’s got error reporting turned on so I can see the exact part of the query I’m interested in! This error is saying there is “unclosed quotation mark” in the query which means the apostrophe we entered has been taken literally by the server and executed as part of the query. Simply because of one extra quotation mark the query fails because MSSQL can’t figure out where each string starts/ends.
How can this be exploited? Well, commonly a low-tech self-made login process would select a record where the username and password both exist. Now we’ve seen part of the SQL statement this website uses we can see if we can “log in” without a password, just for fun…
Jackpot. We’re technically logged in but apparently only as BasicUser which just so happens to be the first user out of the database in the very simplistic query we tried. At this point any developers should be thinking this is pretty bad and that they’d never leave any unchecked user input. Read on…
I’ve logged in but I don’t know anything about this server or its secrets. I’ve decided I want to know:
- What user tables there are
- What columns each table has
- List all usernames and passwords
So, step one, I’d like to know how many tables there are so I enter this into the username part of the login prompt:
' UNION SELECT 1, count(*), '3' FROM sys.objects WHERE type='u';--
There are only two tables in this database so it shouldn’t take long to list them all, except that I can’t simply list all the tables as I’ve only got one output to work with which seems to be trying to welcome the visitor by name. I guess I’ll have to do it one-by-one:
' UNION SELECT 1, name+'+'+convert(varchar,[object_id]), '3' FROM sys.objects WHERE type='u';--
Hey look there’s a messages table, I guess these users can chat to each other. I’ve outputted the object ID because that’s what I’ll use to get the columns later. But first I want to know what the other table is so I’ll exclude Messages and do the query again:
' UNION SELECT 1, name+'+'+convert(varchar,[object_id]), '3' FROM sys.objects WHERE type='u' AND name NOT IN ('Messages');--
Now I can get the field names for each table by doing a similar query but this time looking at sys.columns:
' UNION SELECT 1,name,3 FROM sys.columns WHERE object_id = 277576027;--
Now I’ve got a column I can use “AND name NOT IN(….)” again to get them all and repeat the process for both tables. Here’s the result:
|Users (ID: 245575913)||Messages (ID: 277576027)|
I wonder if logging in as “admin” will work, assuming the account exists. Due to how there’s completely no security on this page I could simply put ‘ OR Username = ‘admin’;– into the username field so if there is an account with that name it’ll log me in without needing the password but that’s easy, what if I want to actually know the password? Very easy, I just do the same sort of query as above:
' UNION SELECT 1,Password,3 FROM Users WHERE Username = 'admin';--
The password is ‘admin’ !! Not only is the page SQL injection vulnerable the username/password policy is ridiculous too and every good developer knows to NEVER have something like this in their code… don’t you ;)
Also, once logged in as admin it seems something else cropped up and it looks like a message from BasicUser. It’s important to note here that the messages sent to the administrator appear because the username and password are both ‘admin’ and would not have shown up otherwise. This is because the example looks for messages sent to a Username so by using the above query to get the password the application would think the password was the username and find no messages logged against it.
Now I know there is somewhere that does an iterative list to output a record set and I know what columns it will use I can get a little more creative and simply list all usernames and passwords from this database:
' UNION SELECT 1,''' UNION SELECT Id,Username,Password,Password FROM Users;--',3;--
In conclusion, I hope this shows you that the little SQL bug you have in a project which crashes whenever anyone called O’Leary turns up is absolutely worth fixing right now.
NOTE: I have omitted some details explaining exactly what each SQL query does as this post is intended to explain to a developer why SQL injection vulnerabilities are a critical security flaw, not just something that results in a few errors every now-and-then.