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.