This is more interesting than hacking.

There are several techniques, but again preventing in the sense making  a harder environment for the hacker. And we what we try is to make the environment harder and what the hacker tries is to find a path in the environment.

First you can clean the values like this.

$username = cleanQuery($_POST[‘username’]);

This looks to see if magic quotes is turned on. if so, it may have already added backslash escapes though a POST or GET method used to pass the data. If backslashes were added, they need to be removed prior to running it through the rest of the function.

Using the mysql_real_escape_string

This function puts the backslash for the following \n, \r, \, \x00, \x1a, ‘ and “.

So when the hacker inserts a ‘ the function puts a backslash and makes it as varchar literal. Not as an ending quote.

So if we look at the example in Part 2

username = nobody’ or 1=1;#

password = gotcha…

 

The sql statement now gets the value.

SELECT * FROM Users WHERE username = ‘nobody\’ or 1=1;#’ AND password = ‘gotcha’

So now here the whole username past nobody’ or 1=1;# is treated as a varchar value and queried. This will return an empty set.

Furthermore we can have own implementations to prevent the SQL Injection. Example a black list of keywords such as DELETE, MERGE, DROP. This method has a disadvantage of not allowing these words to be used in search as well.

Another way blocking the # mark. There several websites doing this. (which uses mySQL as their back end) They simply do not allow you to have # in their usernames and passwords. Again this has the advantages and the disadvantages. Simply we cannot have username like C#NUTT. 🙂

Careful design of the system is very much important.

We have created a proper login in Part 1. Proper login in the sense it was syntactically OK but not an efficient one.

Let’s how a hacker can get into a system.

He inserts the

username as nobody or 1=1

password  = idontknow or 1=1

Now the $sql will get the following value

SELECT * FROM Users WHERE username =’nobody or 1=1′ AND password =’idontknow or 1=1’

Here the the or 1=1 part also within the quote and the OR is considered as a varchar literal not as a logical operator by mySQL. This gives a chance to us.

How ?

We introduced the quotes in the statement to make the $sql valid, we did not have the intention of preventing the SQL injection on that time. But it worked well here for the SQL Injection prevention also.

When the above statement is executed in mySQL. It will return an empty set. Because it searches for username ‘nobody or 1=1’ and password ‘idontknow or 1=1’. Which are not available in the Users table.

But the hacker is a smart guy, and he knows that you are using quotes and still wants to make an SQL Injection.

Now he types the following

username = nobody’ or 1=1

password = idontknow’ or 1=1

Then now the $sql gets the value of

SELECT * FROM Users WHERE username =’nobody’ or 1=1′ AND password =’idontknow’ or 1=1′

When this is executed in mySQL

An error comes because the quotes introduced by the hacker and the quotes introduced by the developer.

AGAIN HACKER TRIES

This time he comes with a powerful weapon – COMMENTS.

He tries the following

username = nobody’ or 1=1;#

password = gotcha…

Now the $sql gets the value of

SELECT * FROM Users WHERE username =’nobody’ or 1=1;#’ AND password =’gotcha’

When execute the above in mySQL

BOOM

The hacker succeeds.

Here after the ; mark he can add any statement like insert, update, drop and finally put the #. This ignores all the rest of the part.

Really this is simple and most websites are prone to this attack.

How to Prevent is on Part 3