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.
Escaping characters
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.
Conclusion
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).
