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

Silverlight with PHP

I do not really need to tell about the Silverlight and its power, since it is apparent. But at the same time I cannot keep mouth shut with the features that Silverlight offers. I have been working on WPF and Silverlight these days. And Just wondering the power the Silverlight has over the Web Apps. It drives me crazy especially compared to ASP.NET Ajax.

What an effective and an easy way to build RIAs. Wondering about the Silverlight architecture and how the mixture of XAML and the managed code sit on the back of the Silverlight.js. Really Excellent.

Using the Silverlight with ASPX is OK, what about with the other languages. Of course not a big deal. Here I explained how to use Silverlight in your PHP application with the C# managed code.

Create a simple PHP file that can take your name and say Hello !


The above PHP script gets post to itself and say Hello.

Now build the Silverlight in VS 2010, when creating the Silverlight application project select the temporary hosting in an HTML file rather than the Web project. This will create an HTML where your Silverlight object is embedded; it would be easy to get the code later.

Put the following in the Layout Grid – XAML Code



Code for button1_Click event in the MainPage.xaml.cs



Now we have the PHP file and the Silverlight application, Let’s put them together.

I’m hosting the PHP using the WAMP, Create a folder SilverlightPHP and save the above PHP file as index.php (so it’s easy to access)

Browse your Silverlight application project folder and inside the Bin->Debug there would be a file with the extension of .xap (description says as XACT project file)

Copy that file and put it into the SilverlightPHP folder.

Then open the HTML file in the Silverlight application project folder (it’s also in the Debug folder)

copy the Javascript code and paste it inside the <head> </head> or keep it as separate .js file and link it.

Then copy the Silverlight embedding code from the HTML and paste it inside the body. You can set the size here.


Here rather than copying the .xap file to the target folder you can set the value to as the path of the .xap file.

Save the index.php

Then RUN !@# and have FUN.

The real amazing thing is this a very cool way to bring the RIA capability to our normal PHP apps. You can notice that when you click the Say Hello button of the PHP the page reloads but when you click the Say Hello button of Silverlight it is completely an asynchronous call to the Silverlight object model. (great AJAX feature). More than that here we use C#.NET and the PHP in the same web interface.