Ins and outs SQL injection

Ins and outs SQL injection

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;

Code:

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;

Code:

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;

Code:

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.

Code:

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.

Code:

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.

Code:

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.

Code:

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;

Code:

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;

Code:

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;

Code:

$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;

Code:

$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.

==========================
Greetz to all my friends

wackwall
==========================