SQL Injection and what you can do to help

Hot in the tech news is yet another new SQL injection attack that is compromising websites.  Apparently there’s a new software package with DRM that is available for folks to install on their computer to attack computers.  Yes, you heard right, a software package with DRM (digital rights management).  To anyone who keeps up with this sort of thing, the irony is not lost.  A software package is put out to ‘hack’ websites and yet installs a program on the user’s computer that prevents unauthorized use.  Anyway, this injection attack is apparently giving the attacker rights and privileges to add a little script to website files, which of course attempts to exploit flaws in one’s browser (IE and Firefox are both vulnerable) or activex or Flash.  If it finds a lack of patch, it installs a nice little trojan downloader that installs all sorts of fun password stealing mayhem.

As website programmers it is our job to ensure that we are not vulnerable to this attack.  As I’ve probably mentioned before, there’s a handy dandy Coldfusion tag called <cfqueryparam> that is very helpful in preventing SQL injection attacks.  To explain the attack better, basically whenever you submit a form, or if there’s weird strings in the url other than the site, there is programming that uses that data, and that data is sometimes added to a database.  So lets say you submit some sort of registration form, you put in your information into text boxes and submit, and those text boxes go into databases.

When database programmers are first learning how to work with SQL, they learn things like “select * from users where userid = 12345″ or for an update “update users set username=’myusername’ where userid = 12345″.  What SQL injection tries to do is modify those queries.  So if your form contains a userid for example, an SQL injection attack may consist of someone trying to enter into the userid field “12345 OR 0=0″ which in our first statement may not match a user id but 0=0 will always match, so it will show a listing of all users.  More malicious attacks may take a simple form of “12345; drop table users;” which if executed would destroy the entire user’s table and all data lost.  Generally speaking, the attacks are a bit more sophisticated than that, however this is the basic premise.

So the answer is that as programmers, accepting web data in any form that can be misused or tampered with comes with challenges that we need to deal with.  On the most basic level, as I’ve mentioned before, the use of <cfqueryparam> is a must.  This little tag will ensure that your data is wrapped up in the correct data type as it’s passed in to the SQL query and we can’t then run random additional query strings, it will either fail and error (and be sure not to show your hand here either) or it will result in no data results.  Each piece of data that’s entered should also be tested for a valid data type.  There is a nice function in Coldfusion 7 called ‘isValid’ which can pretty much validate anything you want (as long as you’re working with things inside the US) or you can, of course, write your own validation routines.

On the server side of things, depending on what your form needs to be doing (such as if it’s an administrative form or if it’s just an information form) you can limit the permissions to the user associated with the database.  Coldfusion gives you the option (as do other web programming languages) to pass through the username and password within the query function.  In some cases, perhaps you only want to give insert privileges to the user, so that other SQL statements cannot be run (delete, update, drop, alter etc).

There are many ways to work with your data processing websites and make them more secure on the web.  A few simple tricks could help prevent a mountain of problems.