Okay, I’m not really going to teach you how to be a hacker, but I am going to share one of the most common hacking techniques used.
In my last blog entry What is PHP and what is MySQL?, I described how PHP and MySQL could be used to get information from a Database, by using
SELECT {data} FROM {database} WHERE {something};
Well, one of the most common uses for this is to get your username and password from the database. So when you typed in your {username} and {password}, then PHP would take that, and build a query to the databse, something like.
SELECT `usernumber`FROM `userdatabase` WHERE `username` ='{username}' AND `password`='{password}';
Which looks pretty straightforwards, it takes what the user has entered as {username} and {password} and puts them into the query, and brings back the usernumber. If there’s a usernumber returned, then you know that the {username} and {password} matched.
So if for example the userdatabase looks like.
usernumber username password 1 bob ice cream 2 jeff hatstand 3 hughbert qwerty
And the user enters their name to be “bob”, and their password to be “ice cream”, then PHP takes their entered data and makes a query which looks like
SELECT `usernumber`FROM `userdatabase` WHERE `username` ='bob' AND `password`='ice cream';
Which returns usernumber 1, because that is the record that matches both the username and password.
If the user enters their name as “jeff” and their password as “qwerty”, then PHP takes that and makes a query
SELECT `usernumber`FROM `userdatabase` WHERE `username` ='jeff' AND `password`='qwerty';
Which will return “null” which is similar to zero, but different in computing terms, but similar enough that we don’t want to have a record 0 in the database to avoid confusion. It returns “null”, as while both “jeff” and “qwerty” are in the database, there is no single record which has both in it.
And that’s the way many websites check whether you are a valid user, by checking that there is a record for both your username and password.
However, what happens if you enter your username as “bob”, and your password as “‘ OR1=1;”. PHP takes that, and makes a query that looks like
SELECT `usernumber`FROM `userdatabase` WHERE `username` ='bob' AND `password`='' OR 1=1;';
So the database returns the usernumber as 1. Because it’s looking for the record where the username is bob, and the password equals ” (which is blank) or 1=1. Well, bob’s password doesn’t equal ” (blank), but 1 always equals 1, so the database has found a match. The database treats a semicolon as the end of the query, so ignores the trailing ‘;
So that lets a hacker access a system if they know a username to match up to, which shouldn’t be too hard, but even that seems like a lot of work, there must be an easier way.
So if that works, what happens if you enter your username as “‘or 1=1;” and your password as “‘or 1=1;”, then PHP makes a query that looks like
SELECT `usernumber`FROM `userdatabase` WHERE `username` ='' or 1=1;' AND `password`=''or 1=1;';
The database returns usernumber 3 (or maybe 1 depending on the method used, but usually 3).
Why?
Because it’s ignoring everything after the first semi colon, so it only looking for the username to be equals to “” (blank) or 1 equalling 1. Which means every single record in the database is valid, and the database returns record after record until it reaches the last one (in this case 3).
So you get logged in as usernumber 3, and you are now an elite hacker (just kidding, but you do now know one of their key tricks to gain access to a website).
This is called a SQL Insertion Attack, and obviously there are ways to avoid this, and every serious website has now put in place protection against this. The most obvious way is to cleanse your inputs, looking at the data that the user has entered into the username and password fields, and not trusting it. Going through it and removing characters such a quotes and other odd symbols, which have other meanings to computers, which is why many websites tell you that your username and password can only consist of a-z and 1-0, so there can be no confusion between a user who has put in a username of “; delete database;” and the command to delete the database.
Other ways consist of changing every character from it’s normal typed version, into the character codes that the computer uses, so that there can be no confusion about what is user entered data, and what is database instruction.
One final trick I like adding to the above, is to count how many results are returned from the database, as there should never be more than 1 person with each username/password combination, so if the database returns multiple records, then this is probably a sign that something like the above attack has returned all the user records from the database, so should probably be blocked as an attempted breach of the system by a hacker.
But despite this attack being well known, and there being a variety of solutions which are equally well known, it’s still remarkably common in websites, I encountered it in a national stores website fairly recently (I of course notified them immediately)
It must also be noted, that cleansing inputs should be done for all user side data, whether it’s entered in a password box, a search box, from a cookie stored on the users computer, or data passed from page to page. As all of these can be altered by a hacker, and be used to insert commands into the database. An old website of mine encountered this problem recently, when the only input on the entire site which wasn’t be cleansed was the result from a user selecting which letter of the alphabet they wanted to see results from. This was enough for hackers to insert commands to the database, and see the full list of usernames and be able to log in.
While PHP and MySQL are powerful tools, which allow construction of websites easily and quickly, the devil is as they say in the details, and securing your website, and your users details against a rogue hacker is important and time consuming, but well worth doing.
