Making SQL Safe in ASP

01 January 2009

A common question I get asked is how to make ASP safe from SQL injection - and it is really quite easy. Lets use a simple login form as an example, this is how a normal login form works:
- User enters "Login/Password"
- Script receives "Login/Password"
- SQL processes and runs the "Login/Password" and tries finds the user

Typical ASP Script To Receive a Login/Password
   Login = Request("Login")
   Password = Request("Password")

   objconn.execute("SELECT * FROM Users WHERE Username = '" & UserName & "' AND Password = '" & Password & "'")

The Problem
The problem we have is that when the script receives the username and password either could contain malicious code, we want to make sure that the username and password only contains text. For example if the user type's in the username as ' OR 1=1--- then SQL will see the following

SELECT * FROM Users WHERE Username = '' OR 1=1--- AND Password = 'asdsad'

If your website isn't protected against SQL injection, this would let a user into your website, or at least error giving them some vital information that they could use for a 2nd attack. Why? Because the user is telling SQL that Username can either = '' (nothing) or 1=1 .. and 1 will always equal 1 so this will always become true. The code in red is completely ignored by SQL because of the "---" (3 dashes) which tells SQL; ignore all code after this.

The Solution
We solve this problem by adding a function that makes sure text is text and numbers are numbers - This is really quite simple.

For Text
Function SafeSQL(fn_Var) IF IsNull(fn_Var) = False THEN
   SafeSQL = Replace(fn_Var,"'","'")
ELSE
   SafeSQL = ""
End IF End Function

For Numbers
Function SafeSQLN(fn_Var) IF IsNumeric(fn_Var) = True then
   SafeSQLN = CDbl(fn_Var)
ELSE
   SafeSQLN = 0
End IF End Function

By wrapping this function around the text, we are making sure that this is 100% text or 100% numeric and does not contain any characters that SQL could perceive as SQL code (').

Now I know what your thinking! "So that prevents SQL injection, but what about when I want to display single quote as single quote?". Well there's a simple solution - all you have to do is write another decode function that replaces ' with ', simple.

End Result
This is what your code would look like with the use of the function

   Username = SafeSQL(Request("UserName"))
   Password = SafeSQL(Request("Password"))

Now if someone was to try to use SQL code this is what SQL would see

   SELECT * FROM Users WHERE Username = ''OR 1=1---' AND Password = 'Password'

As you can see SQL now thinks "'OR 1=1---" is text, so now as SQL doesn't perceive this as SQL code ... it will look for a username called "'OR 1=1---" which in most cases will not be there! Happy days!

Other Security Measures
I also came across a very interesting post on designertalk.com about an extra layer of security called "SweKey" which is a portable USB device which you plug into your computer for added security. It installs a small piece of ActiveX software that generates a random ID and one-time password, as a web developer all you have to do is write some code to talk to the authentication server. A very useful piece of hardware for extra security, as you would have 2 levels of security; Username/Password and the SweKey.

Useful links:
http://www.swekey.com
http://www.designerstalk.com