Keeping The Hackers Away From Your Database

Written by Andy Towler published 8th Mar 2006 | Comment on this article

There's a particular kind of hacking attack that websites can be subjected to, called SQL Injection. If you have a website with a private area, and you have a login page, you could be vulnerable. Read on to learn how to avoid these attacks.

Keeping The Hackers Away From Your Database

The database used by your ASP or PHP site could be at risk from defacers!

There's a particular kind of hacking attack that websites can be subjected to, called SQL Injection. If you have a website with a private admin or intranet area, and you have a login page where people enter their username and password, you could be vulnerable.

To explain, your form takes the username and password fields the user enters, and submits them to an ASP or PHP page, which presumably takes them from the Request.Form object and does something like this (using ASP):

strSQL = "SELECT * FROM users "
strSQL = strSQL & "WHERE users_username='" & strSubmittedUsername & "' "
strSQL = strSQL & "AND users_password='" & strSubmittedPassword & "'"
set objRS = objConn.execute(strSQL)
(get the user's details here)
set objRS = nothing

With this method, there's nothing to stop a hacker entering something in the username field that isn't a valid username but does contain SQL code. If they start by entering an apostrophe into the username box, followed by a semicolon and then a fresh SQL statement, this statement will be executed (because the apostrophe forces the end of the username parameter) and they might be able to deduce all sorts of information about your database from the resulting error messages. This article goes into more detail than I can go into here. Suffice to say they could potentially work out how your access rights system works, what the admin user is, or even how to create a fresh admin user.

Preventative Medicine
So how do you stop this happening? Simple. Whenever you take input from the user which then forms part of an SQL statement, parse it to remove dangerous characters. It does mean that you can't have apostrophes or semicolons as part of a username or password, but I think that's a small price to pay.

A simple way to do this with usernames and passwords is to run each string through a function to strip away any characters you don't want. This one will do the job:

function allowChars(ByVal value,ByVal chars,ByVal casesense)
dim a,result
result = ""
if casesense then
for a = 1 to Len(value)
if InStr(1,chars,mid(value,a,1)) then result = result & mid(value,a,1)
next
else
chars = ucase(chars)
for a = 1 to Len(value)
if InStr(1,chars,ucase(mid(value,a,1))) then result = result & mid(value,a,1)
next
end if
allowChars = result
end function

To make it allow only letters (upper or lower case) and numbers, call it like this:

strSubmittedUsername = allowChars(strSubmittedUsername, "abcdefghijklmnopqrstuvwxyz0123456789", false)

As well as parsing the input once it has been submitted, make sure you restrict how much the user can type, using the MAXLENGTH attribute of the INPUT tag. For usernames and passwords, you can probably set MAXLENGTH to 20 characters or less, which will stop hackers typing in enough bad code to mount an attack.

Other Things You Should Do
Now is probably a good time to look at some other ways you can make a database-enabled website safer from attack. These tips are aimed primarily at Access database users.

The DB Folder
Your OpenHosting webspace has a DB folder at the same level as the WWW folder, which cannot be accessed via an HTTP:// style URL. If you use Access databases, put them in here, not anywhere under the WWW folder.

Database Password & Encryption
Always password-protect your database, even once it's in the DB folder. You can do this by opening the database in exclusive mode in Microsoft Access, and selecting Tools, Security, Set Database Password. While you're at it, encrypt the database as well (Tools, Security, Encrypt/Decrypt Database).

Connecting to the Database
Never put your database password anywhere in your ASP or PHP code. You have a DB folder - use it! Create a small text file with the same name as your database, and store the password in it. So the password for your thing.mdb database will be stored next to it in thing.txt. Each time you need to connect to the database, use a small function to read the password from the file and put it in your connection string. Something like this:

function readTextFile(byval strPath)
dim objFSO,objFile,strFileContents
set objFSO = server.CreateObject("Scripting.FileSystemObject")
if objFSO.FileExists(strPath) then
set objFile = objFSO.OpenTextFile(strPath,1)
strFileContents = objFile.ReadAll
objFile.Close
set objFile = nothing
else
strFileContents = ""
end if
set objFSO = nothing
readTextFile = strFileContents
end function

Whenever you assemble your connection string, pass the complete file path of the password file to this function, and you'll get the password back.

Hiding From The Search Engines
A contradiction in terms? Not for the login page that I mentioned above. You don't want hackers to do a Google search for login pages and find yours - it only encourages them.

One way to prevent this is to insert a suitable META tag onto every page that you don't want indexing - including your login page and all pages which are only available once the user is logged in. The tag will look like:

<meta name="robots" content="noindex,nofollow">


These tips (ideally you should use them all) should go a long way towards preventing people from getting access to your site's database in order to wreak mayhem.