When we're developing websites, we often become complacent. Maybe we're too lazy, maybe we're rushing to meet deadlines, or maybe we're just too naive. I'm guilty of this myself. Often times though, this complacency can have disastrous consequences.

I used to think my applications were secure until I heard about a little devil called "SQL Injection." Essentially, it's a type of intrusion technique hackers use to compromise and hijack your system by taking advantage of a loophole in your database. This is initially done simply by appending a malicious SQL code at the end of a url pointing to a page running a database-driven app, in essence, "injecting" the code into your database query.

THE SOURCE OF THE PROBLEM

For example, let's say you have a simple knowledgebase page. Perhaps this page displays data stored in a simple knowledgebase table. One very common way of accessing a specific record from that table would be to pass a parameter via the url like so:

http://www.yoursite.com/knowledgebase/showarticle.cfm?id=12

Consequently, in the showarticle.cfm page, you may have this simple coldfusion query:

SELECT ArticleBody FROM KnowledgeBase WHERE id = #url.id#

Using the right cfoutput tags, that would of course output the content of ArticleBody whose record id equal 12.

"What's wrong with that?", you say.

Well, in an ideal world, nothing. That's a perfectly valid SQL statement...with one tiny flaw.

TIME TO WRECK HAVOC

Suppose a malicious hacker came across your knowledgebase page and determines that it does indeed run an SQL query. Well, just to be a jerk, he might try typing the following on the address bar:

http://www.yoursite.com/knowledgebase/showarticle.cfm?id=12;DELETE FROM KnowledgeBase

Guess what happens when he hits the return key. That's right! Every single record in your KnowledeBase table just went bye-bye. How's this possible? Well, see that little semi-colon after the id number? It enables two or more SQL statements to be executed on the same line. No, it's not a flaw or a bug in SQL. It's a perfectly legal way to write SQL statements and its fine until your expose your query to the world. Sure, in this case, Mr. Hacker still needs to guess the table name but it often doesn't take a genius to guess that most KnowledgeBase tables are named, well, "KnowledgeBase", or that User Account data are stored in the "Users" table with a "username" and "password" as field names.

Now if that makes you cringe, good because that's just the tip of the iceberg. I've only shown you one method of doing doing SQL injection. A skilled attacker can execute a multitude of attacks including displaying sensitive data such as user account information, perform UPDATE and INSERT statements to manipulate data, even execute shell commands which can eventually create a security breach all the way to the very depths of your network.

PARAMETRIZED QUERY TO THE RESCUE

Fortunately, most scripting languages offer a way to protect against this. In Coldfusion, one way of preventing SQL Injection is to parameterize your queries. In layman's term, you're essentially setting a set of conditions to filter the data before it is passed into you SQL statement thereby limiting and ultimately preventing any other data to be passed other than those you expect.

It's not rocket science either. In fact, in Coldfusion, it's as simple as using the cfqueryparam tag in your SQL statements. The syntax is as follows:

<cfqueryparam cf_sql_type="(data type)" value="(string or variable)" maxlength="(character limit)">

So, using our original SQL statement above, you might use it as such:

SELECT ArticleBody FROM KnowledgeBase WHERE id =<cfqueryparam cfsqltype="cf_sql_integer" value="url.id" maxlength="3">

In this case, under you're essentially telling Coldfusion to only accept an integer for the value of url.id with a length no greater than 3 digits. In doing so, you've pretty much limited the ability of the attacker to append extra code to your sql statement. Anything other than an integer value exceeding the maximum character length will be rejected. If your cf_sql_type is a numeric value, you may even add the scale attribute, such as scale="2", to limit the numeric value to a real number with 2 decimal places. If you're expecting a string, simply use cf_sql_varchar for the value of cfsqltype. For more information on this tag, visit:

http://livedocs.adobe.com/coldfusion/6.1/htmldocs/tags-b20.htm

As an extra precaution, make sure you turn off verbose debugging in Coldfusion to prevent the attacker from seeing the details of the error page Coldfusion throws if it in fact detects an invalid data type.

To increase its effectiveness, you should also consider using this method in conjuction with other security measures such as turning off shell access, restricting tags/commands, setting proper database permission, flattening data, and turning off verbose debugging. I will go further into other methods in upcoming articles.

AN OUNCE OF PREVENTION

As you can see, one simple extra step can go a long way. Like the saying goes, "An once of prevention is worth a pound of cure." Next time you're feeling lazy writing that extra code, just think of the risk you're taking.

Now go back to your code and see how many queries you can parameterize!