SQL injection is one of the biggest and most overlooked flaws on the web today, with the advent of millions of hosting companies that offer you your own database and scripting language to play with, anyone can setup their own community, blog or anything in between. Developers who are unskilled tend to leave a lot of flaws in their code, room for SQL injection is one of the most dangerous.
In this tutorial we’ll look at how attackers use SQL injection for the purpose of damage or unauthorized access and how to protect you and your site against it.
What is SQL injection?
SQL injection is an extremely overlooked problem, especially with how easy it is for Joe Bloggs and John Smith to setup their own website and do with it what they wish. SQL injection is the equivalent of letting any old user manipulate your database, be it for malicious purposes or not.This dangerous flaw is easy to prevent, however it is easier to overlook. Every time your website or application commits an SQL query with input that is given to it from the user, it is a possibility for SQL injection if you are not safeguarded properly. Today we’re going to learn how the SQL injection is done and how to prevent it in easy to swallow chunks, here we go…
Right, so how do you do it?
It’s much simpler than it sounds, SQL injection is simply changing the query from what it was intended to do with it what you wish, let’s skip the boring footwork and jump in head first. In order to ‘do’ SQL injection you need a vulnerable website or application, of course to demonstrate prevention and so on we need to use a language, surprise surprise the language we’ll be using today is PHP coupled with it’s wonderful brethren, MySQL. Consider this, you have a page called profile.php that when accessed properly will pull information about a certain user from your wonderfully crafted database. Let’s say the query looks like this;
mysql_query("SELECT first_name, last_name FROM users WHERE user_id = '$_GET['id']'");
Seemingly harmless, when executed properly this query will pull two fields from a table called users. In order to wreak havoc inject SQL into this query we need to perform our own query, let’s say for example; DROP TABLE users, seems only right. Obviously if we visited profile.php?id=123 then the query would look a little like this;
mysql_query("SELECT first_name, last_name FROM users WHERE user_id = '123'");
Simple enough, this query will fetch the first name and last name of a user who has an ID of 123. Obviously not the best designed query as it’d be better to limit the amount of results etc but that is beyond the scope of this tutorial. Now let’s say we change profile.php?id=123 to profile.php?id=DROP TABLE users. The query that is executed now looks something like this;
mysql_query("SELECT first_name, last_name FROM users WHERE user_id = 'DROP TABLE users'");
Pretty useless. All this query is doing is what’s intended of it and searching for a record where the user_id is set to DROP TABLE users. To actually make our command execute, we need to ‘escape’ the friendly SQL query and insert our own query, I’d like to introduce the single quote ( ‘ ). When you search for a string using SQL, in order to prevent the string from interfering with the query, it is wrapped in a set of single quotes. If we use a single quote in our query it suddenly becomes a little more interesting. Let’s try ‘DROP TABLE users.
mysql_query("SELECT first_name, last_name FROM users WHERE user_id = ''DROP TABLE users'");
What we have done is made it so that the string to search for is simply blank, by using a single quote we have closed the string and we are now inside the actual query, exciting isn’t it? If you were to execute the above query all you’d receive back would be an error (although this varies depending on the PHP configuration). ‘Great’ I hear you saying, but one of the golden rules when trying to exploit something is learning to love error messages. One of the quickest ways to find out whether a site can be exploited is to slap a single quote in a few of the $_GET variables and see if you receive an error message. If you do then it’s likely there’s a gaping hole for you to destroy report to the local administrator. Of course this isn’t always true, depending on many factors and should only be used as a quick first resort to check for vulnerability.
So we have an error message, awesome, we can manipulate the SQL query! Now the reason the above query didn’t work is because it is read as a single command to execute, we’re executing a SELECT command to select records from a database, shoving a DROP TABLE command in half way through isn’t going to be expected and therefore it’s going to cause a problem. The way we get round this is to close the SELECT command in order to inject our own SQL. The way to properly end a command in SQL is the same as with most languages, with a semi-colon, so all we need to do is end the previous command and then begin our own. One thing we need to remember is that the query we’re ending mustn’t cause an error because if it does then the error will stop the query and our command won’t be reached. Let’s inject.
mysql_query("SELECT first_name, last_name FROM users WHERE user_id = ''; DROP TABLE users'");
We inserted ‘; DROP TABLE users. What we did was inserted an apostrophe to close the string followed by a semi-colon to end the query that’s searching for the user, as far as anyone is concerned the first command in this query is valid, the second one however is not. Why? Because after our command there is a single apostrophe lingering from the first command where we injected. Uh oh. Our command won’t be executed because there’s an error in it now. Another hurdle that can be jumped, essentially we need to ignore everything after what we’ve injected, we don’t care about it. In order to ignore the rest we have to use an SQL comment signified by two hyphens (–). Once two hyphens are read, the rest of the query is simply ignored and what we have is a successful command, before we comment out the rest of the query however, we need to end our command with the semi-colon. All in all our query now looks like this.
mysql_query("SELECT first_name, last_name FROM users WHERE user_id = ''; DROP TABLE users;--'");
Voila, you’ve just upset a database administrator somewhere, congratulations. Now one thing we should touch on is getting around basic PHP/MySQL authorization with SQL injection.
Correct login OR 1=1?
Some (very) weak PHP login scripts that use a MySQL database use the actual query to check authorization rather than querying the database and then doing some playing with the results. Here’s an example of an extremely weak query;
myqsl_query("SELECT user_id FROM users WHERE username = '".$username."' AND password = '".$password."'");
Now the reason people might use this query for authorization is that when the username and password specified are found in the database the above query will return TRUE, well actually it’ll return the user_id but for our example we’ll just assume that the PHP code just checks for any returned value. If the user isn’t found, the query will evaluate theoretically to FALSE. With this information in mind we already know that in order to get round this authorization, what we need is the query to return true – we can do this with some more SQL injection.
Assuming that the above query is used in the PHP code, we need to inject something that will make the query return true (or a value) no matter what credentials we supply. Well first we need to break into this query, there are two possibilities here; username and password, we’re going to use username. Now we know where we’re going to break into the query we need to make it return true, what will always return true?… 1=1. We need to tell MySQL to evaluate 1=1 rather than the username and password, to do that we’re going to use a little boolean algebra and use OR. Let’s see what this looks like with the username field injected;
myqsl_query("SELECT user_id FROM users WHERE username = '' OR 1=1;--' AND password = '".$password."'");
By inserting a single quote, we escape from the username comparison and we’re now in the SQL query as we’ve previously learned. The next thing we do is insert an OR clause, this checks to see if the username is blank OR 1=1 and of course we then need to end this command and comment out the rest. Voila.
Now it’s all well and good being able to conduct SQL injection, but now it’s time to move on to the more important matter…
Countering SQL injection
It’s important to understand how the attackers will attempt to use SQL injection to attack your website in order to understand where the threats/weaknesses lie so we can use this knowledge to secure these flaws. You might be expecting paragraph upon paragraph of information on countering this threat but in reality you can protect yourself against it easily.
As with all input that PHP uses, it should be sanitized to ensure it can not interfere where it shouldn’t. The obvious method for protection is to simply remove all single quotes from a string or simply display an error if they are used, but this can cause problems when you apply it to a website that needs to display single quotes such as a review website or forum where you need to use words like can’t and don’t etc.
Note: It’s important to remember that the great thing about PHP is people can solve things in their own way, everyone has their own preferred method for countering SQL injection and this just happens to be the way I’ve chosen to convey to you.
In order to use certain characters safely in a query, we need to escape them. This means prepending then evil character with a backslash, so ‘ becomes \’ and for extra safety, \ becomes \\. Now finding all the evil characters and putting backslashes in front of them might seem a bit of a chore, but PHP has a few handy functions that can help us. One of the most common is the addslashes() and stripslashes() functions. It is as simple as it sounds, addslashes() will add slashes before your evil characters and stripslashes() will take them away. Simple as that. Here’s a quick example;
$evil_name = "dan' OR 1=1;--"; $password = "abc123" mysql_query("SELECT * FROM users WHERE username='".addslashes($evil_name)."' AND password='".addslashes($password)."'");
This query should now be safe to run as the quotes in the original name have been escaped, the username now looks like this: dan\’ OR 1=1′– which is not harmful to our query. Although there are many methods in which to prevent SQL injection, we’re just going to look at one more function provided by PHP and that’s mysql_real_escape_string(). This function has a little sister called mysql_escape_string(), the difference is that mysql_real_escape_string() takes into account the current character set used in the connection to the database. Using the same method as above, the query would look like this;
$evil_name = "dan' OR 1=1;--"; $password = "abc123" mysql_query("SELECT * FROM users WHERE username='".mysql_real_escape_string($evil_name)."' AND mysql_real_escape_string ='".addslashes($password)."'");
Another safe query successfully executed on the database.
We’ve learnt today that SQL injection is a major threat if not dealt with correctly and dealing with it isn’t at all hard and is only overlooked by developers who are either not knowledgable in this area or those who are just plain lazy. There are exceptions (human error) when skilled developers forget to apply SQL injection countering and this is why still today flaws exist on millions of sites (as a small example i found one in the United Nations website which was exploited a few months later by turkish hackers).
Remember that all input from users can’t be trusted, and you have to treat everything you use as another possibility for a bad guy to find their way in, don’t get caught out. I hope you enjoyed this tutorial, the first tutorial on this blog, any questions etc are welcome using the comments form below, or by sending me an email at support [at-symbol] coderevolution[dot] ro