Code
SQL Injection
2008-11-30 20:42:10
There is a lot of hype around SQL Injections, but very few people seem to know what exactly they are. Essentially, SQL injection is a technique employed to manipulate a legitimate database query in order to return falsified data.
Exploitation
For an example, consider a standard login form on a website. We would have a username and password field. To check if the user's credentials are correct and valid, we would perform a database query such as SELECT * FROM `users` WHERE `user` = 'someusername' AND `pass` = 'somepassword'; . However, what would happen if somepassword or someusername happen to be something other than a username and password which we were expecting? What if, for instance, they happen to be SQL commands?
Let us imagine that some person with malicious intent were to enter in the following into the password field : ' OR 1=1 #. What would the resulting database query look like? Have a look at this: SELECT * FROM `users` WHERE `user` = 'someusername' AND `pass` = '' OR 1=1 #'; .
The # character is used in MySQL to comment out the rest of the line. Thus, we are retrieving all the information from the users table where 1=1, in other words, EVERYTHING. We would effectively become the first user in the table. That is quite scary.
Even worse is if we know what a user's username is. For example, if we know there is a user 'admin', but we don't know what the password is, we could simply enter the following into the username field : admin' #. The resulting database query would be this : SELECT * FROM `users` WHERE `user` = 'admin' #'; . Thus, the query wouldn't even worry about the password. We would automatically become the admin user. That is enough to give any system administrator a mild stroke.
Now, perhaps we are not simply satisfied with getting access, we also want to know the password of one of the user accounts we have discovered. How would we get this? It is a simple two step process:
Step 1: Find the name of the table holding the user accounts.
This is done using an SQL UNION. If we use the username admin and the password ' UNION SELECT CONVERT (table_name USING latin1) FROM INFORMATION_SCHEMA.TABLES WHERE table_name LIKE 'u%' AND NOT table_name = 'USER_PRIVILEDGES. The resulting SQL query would look like this:
SELECT * FROM `users`
WHERE `user` = 'admin' AND `password` = ''
UNION
SELECT CONVERT (table_name USING latin1)
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name LIKE 'u%' AND NOT table_name = 'USER_PRIVILEDGES'.
Once logged in, we see a nice message saying Welcome users. From this, we have established that the table storing the user login details is the `users` table. We can now use this information to access the passwords. To do this, we enter the username of the account we wish to see the password for and then use another UNION. So username = admin and password = ' UNION SELECT CONCAT(`user`, '=', `pass`) FROM `users` WHERE `user` = 'admin. The resulting SQL query would look like this:
SELECT * FROM `users`
WHERE `user` = 'admin' AND `password` = ''
UNION
SELECT CONCAT (`user`, '=', `pass`)
FROM `users`
WHERE `user` = 'admin'.
Once more, we see the nice welcome message saying Welcome admin=AdminPassword. So what can we do about this insanely simply hack?
Prevention
So how do we prevent this? It is fairly simple. For programmers there are three fundamental things you can do :
1 : Properly escape the strings we receive from the users. Alternatively, we could strip out characters we know we shouldn't be receiving, such as quotation marks, semi-colons etc...
2 : Store hash codes only for passwords. Thus, the plaintext password is never used or stored or compared within an SQL query. In the script (perhaps the PHP script), you would generate a hash of the password entered by the user, and compare the resulting hash to the hash stored in the database. If the hash codes match, then authentication has occured, if not, then the passwords do not match, and the user should not have access to further information. This will negate the possibility of the above hack for retrieving passwords.
3 : From the webapp, only access the database with a user with table-only priviledges. You do not want to be using the root user account to be accessing the database. If you are foolish enough to do this, you are opening yourself up for someone to either wipe out your entire database server, or retrieve every single scrap of data on your SQL server.... NOT GOOD. What you should do is this. Say, for example your web app is using a database called `webapp1`. To create a MySQL user with restricted access to that database only, you would do this:
GRANT ALL ON `webapp1`.* TO 'webapp1user'@'localhost' IDENTIFIED BY 'w3b4pp1password';
Now, you can do everything that you need to using the 'webapp1user' account, without the risk of giving access to all other databases on the server to someone who has compromised your webapp.
For system administrators, simply by turning on the magic_quotes flag in the php.ini file will automatically escape any suspicious quotation or apostrophe marks.
Links
SQL Injection Demo
View the Source Code
SlideShow Presentation
