David Kendall

There are many nasty people out there who have sad little lives and enjoy ruining people’s hard work, that is why when we create a website we need to protect against these, the worst culprit being something called SQL Injection. This blog post will concentrate on protecting your website from SQL Injection using classic asp, although it will be easy to replicate for php or your language of choice.

I will also write another article on how to protect email forms using cdosys (same rules will apply for old school cdonts users) on how to prevent spammers from hijacking your contact forms.

Now I am not trying to show you how to hack a website here, only show you the methods malicious people may use to gain access to yours. If you know how they do it you know how to prevent it.

Ok, SQL Injection! This is basically a relatively simple technique used by a hacker to exploit security vulnerability to gain access to a database application. If we employ a few simple methods we can stop these techniques from being used on your website, thus protecting your content.

Let’s take a login page as an example. We will use a simple login page requesting a username and password. Here is the SQL you would use to query your database with the fields entered on the website

SELECT * FROM t_users WHERE username=’admin’ AND password=’foo’

Just like html, asp, php etc SQL has comment tags at their disposal. All it takes is a simple SQL comment to be inserted into the username field on the website to gain full admin access to the website. For example should you enter admin’– into the username field on the website, the resultant SQL would be sent to the database

SELECT * FROM t_users WHERE username=’admin’–’ AND password=’foo’

HOWEVER, in SQL — means a comment which causes everything after this point to be ignored, so the actual SQL being sent is

SELECT * FROM t_users WHERE username=’admin’

As you can see, this isn`t good! Simply by adding admin’– into the username field it is possible to gain full admin rights to your database. This obviously relies on the user ‘admin’ being set up in the database however it wouldn`t be difficult to write a small script to try thousands of usernames until a working one is found.

Lets take it a step further

Now let us enter into the username field on the website

a’ or ‘d’='d

So, what is this doing? Well, this would create the SQL

SELECT * FROM t_users WHERE username=’a’ OR ‘d’='d’

The key here is the OR within the statement. The OR will return true if either condition is met. We are passing two conditions here;

• username=’a’ - this is unlikely to be met unless there is a user set up in the database as ‘a’
• d=d - ermm, yes, I think d=d, so lets return all the results in the database.

As you can see it doesn`t take a genius to potentially gain access to your database. What if the hacker wanted to remove a table completely? Using this method they simply add into the username field on the website

a’; DROP TABLE t_users

This would create the SQL

SELECT * FROM t_users WHERE username=’a'; DROP TABLE t_users

In this case the semi-colon (;) is used to separate multiple statements, look at what our second statement is doing!! Say goodbye to your users table, I hope you have backups!

How do we prevent SQL Injection?

As we have seen, the key to preventing SQL injection is protecting what is entered into the username and password fields. The main character in SQL injection is the single quote character (’), therefore we need to ensure this doesn`t get anywhere near the database. Fortunately using server side scripting, in this case asp we can use the replace command to strip out a single quote and replace it with anything we want. In SQL two single quotes together tells SQL that one is a character and not an action, so we shall replace a single quote with two single quotes. This will prevent the hacker from being able to execute malicious code. Here is the asp for replace;

strUserName = Replace(Request.Form(”username”),”‘”, “””)

Let’s break it down. We start by defining a variable, in this case strUserName.
We then use the replace function, the replace function requires a string to perform the replace on, Request.Form(”username”)
We then pass the text to be replaced, in this case a single quote
Then the text to replace it with, which is a double quote.

NOTE that the text to be replaced is encased withing double quotes!

As you saw above, the semi-colon (;) may be used to separate SQL statements so you may wish to check for that too, in my scripts a user would not type in the semi-colon so I perform an Instr search to see whether the semi-colon has been entered, if it has then do nothing at all, if it hasn`t then go ahead and process the login.

That is it!

That has taken care of the vast majority of hackers out there. It is a very simple technique that can save you hours upon hours of work. Make sure you use it in each and every logon script you create!