The Anatomy of a SQL Injection
Wednesday, March 26th, 2008We had a client recently come to us to add something onto a project that another developer had done. In the process, we noticed the site was probably susceptible to SQL Injections. We did some testing, and sure enough, SQL Injections were possible.
As any responsible developer would do, we notified the client and provided them some links to some information regarding SQL Injections. The client was interested in knowing how to fix it, however, their server admin also told them that the server was secure and that it probably wasn’t that likely to happen and maybe not that big of an issue.
With that said, let’s get something out of the way: A SQL Injection has absolutely nothing to do with the security of the server. You can have the best security practices in the world for your server, but if it’s hosting an insecure application, there’s a risk. And the risk is not only to the app, but can be to your entire server.
We prepared a quick demo to show the client what is possible via SQL Injection. The code that we discovered was on the login page for this particular application. The code looked something like this:
SELECT * FROM tablename WHERE user = ‘$variable’ AND passwrd = ‘$variable’;
Note: throughout this blog entry, I’m replacing the real table name with ‘tablename’ for ease, as well as to not publicly reveal the actual table name.
The variables are coming straight from the browser and are not ’sanitized’, or filtered, in any way. So, our first demo to the client was to leave the password field empty and enter the following into the username box:
‘ OR ‘1′ = ‘1
When we first showed this to the client, they were a bit confused. Let’s see what that looks like when we insert it into the query above:
SELECT * FROM tablename WHERE user = ” OR ‘1′ = ‘1′ AND passwrd = ‘$variable’;
Wow, so now we’re asking the database for any users where the username is empty OR 1 = 1 AND password = ”. Guess what? The database will return a record because of the fact there’s an OR in there that evaluates true.
At this point, we were successfully logged into the application. That concerned the client enough, however, there’s more. Much more. Part of the scary thing about SQL Injections is that they often allow you to execute more than one query. So, our next step was to try to execute some additional queries. The next thing we entered into the username box looked like this:
‘ OR ‘1′ = ‘1′; DROP TABLE test_table_name;
That threw an error. Here’s what the database saw when we ran that:
SELECT * FROM tablename WHERE user = ” OR ‘1′ = ‘1′; DROP TABLE test_table_name; ‘ AND passwd = ”;
We have an unmatched quote in there. However, the error wasn’t handled in the application and it printed a message to the screen that said something along the lines of:
You have an error in your SQL statement near (SELECT * FROM tablename user = ” OR ‘1′ = ‘1′; DROP TABLE test_table_name; ‘ AND passwd = ”;)
Guess what. Now the error message printed to the screen gave us the actual table name. And field names. That’s dangerous.
So, with that attempt failing, but the important note about the error message revealing the table name, we moved on to the next part of the demo. Our next entry into the username box looked like this:
‘ OR ‘1′ = ‘1′; DROP TABLE test_table_name; SELECT * FROM tablename WHERE ‘1′ = ‘1
Now, when the database sees that query, it looks like this:
SELECT * FROM tablename WHERE user = ” OR ‘1′ = ‘1′; DROP TABLE test_table_name; SELECT * FROM tablename WHERE ‘1′ = ‘1′ AND passwrd = ”;
We now have 3 valid SQL Statements here:
SELECT * FROM tablename WHERE user = ” OR ‘1′ = ‘1′;
DROP TABLE test_table_name;
SELECT * FROM tablename WHERE ‘1′ = ‘1′ AND passwrd = ”;
The first one will log us into this application. Nice, but not really the worst part of this situation. The second statement will delete the database table named ‘test_table_name’. Now, keep in mind that earlier, an error message told us an actual table name. We could easily substitute that, or, we could start guessing table names. Or, we could insert our own record, since we now know table names and field names from the error message earlier.
To give the full effect, we literally created a table named test_table_name, showed the client the table in the database, typed that into the username box, and clicked ‘log in’. We were greeted with an error message. However, when we refreshed the table list, our test_table_name was gone. It had been deleted. Ouch. Just imagine if that was our users table, or some other table full of sensitive information…
Joe Koenig
Creative Anvil
