SQL Injection Part 3– How to Prevent

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.


SQL Injection Part 2– How to Inject

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.


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



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

Blogs Here and There

Based some on going and some few past topics I write this entry here. I summarized some interesting topics that I read from here ant there in the cloud.

As we all know with the launch of Windows 7 (actually windows version 6.1) MS got the sensational hit again which it lost due to the Vista. (personally I don’t find that Vista sucked much but it is really a big fat OS) Another reason that why Vista was not accepted by the users was; MS thought the users are somewhat OK with high security controls in local networks. But really it was not the case. I remember when the launching of Windows 7 there was a blog post saying that Windows  7 will rock, because it is smarter and fool enough. I have another experience with an IT guy complained about the UAC system of Vista. It is really useful indeed and a good security protection. We have this in Windows 7 too, but some people still don’t want to have it. (here most of them complain because they do not know how to stop the UAC – but I recommend you to not to stop that)

Stepping away from the MS and you can see plenty of posts and news around the web about the Oracle’s acquisition of Sun and later part of the Java and mySQL. It was a moment that nobody thought that why Sun can’t stand alone. Of course Sun had the power and the strength to stand alone, but someone from the top rank decided to sell it. After Oracle acquired the Sun first all thought Oracle will bump on the mySQL and bang it. But it won’t happen. There are not any reasonable updates or new releases on any of the Sun’s products after the Oracle acquired Sun. What happened to JavaFX ? It was completely forgotten in the shadow of Silverlight. Now Oracle sue Google on Davlik (a JVM for Andriod). Where the patent rights go? What is the future of the open office ? But still Oracle keeps it competing bob – mySQL alive.

Java is an excellent platform which opened plenty of new trends in modern web standards. No body can deny this, but what is the future. I’m not against Java but the what does the Oracle buggers do? .NET needs a perfect competition.

Apache is funded by the MS in order to get the competition to IIS. (are you shocked ? but it is the truth Apache is funded by MS)