> 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;
>
> 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.
>
> --------------------------------------------------------------------------------------------------------------------------------------------
> Don - Balcan crew - December 2008
>
>
> http://www.balcan-crew.org
>
>
> Greetz to all my friends
> --------------------------------------------------------------------------------------------------------------------------------------------
# milw0rm.com [2008-12-01]