Protecting Against SQL Injection Attack

A SQL injection attack happens when some one tries to add, delete or change data in your online database by making changes to the query string that is passed in an URL.

The best way to deal with passing a variable is to use a session variable, not by passing a query string. But having said that, if you are passing variable values via a query string the following is some of the ways to protect yourself from SQL injections.

Limiting What Can Be In The Query String

Use <cfqueryparam cfsqltype = "cf_sql_integer" value="#variable#"> to wrap your integer variable values that will be passed in the query string. This will only allow numerical characters (0-9) in the variable value. Characters like ' " or ; will product errors.

For alpha-numercial charactors use <cfqueryparam cfsqltype = "cf_sql_varchar" value="#variable#"> to wrap the variables values. This allows only a-zA-Z0-9 in the variable values. Again characters like ' " or ; will product errors.

When you are dealing with numerical or alpha characters (a-zA-Z0-9) in your passed variable values you can add a maxlength="" to the cfqeryparam to limit the length of the variable value.

For example, in the CarsInLondon.Com inventory listing coldfusion app you select from the inventory page http://www.carsinlondon.com/inventory.cfm a vehicle that you want to view the details.

When you select the vehicle the database record ID value of that vehicle is passed. As an average dealership sells up to 50 used vehicles per month, I am quite safe in limiting the length of the record ID value that can be passed to 5 characters. That's good for 100,000 cars.

If some how a SQL injections got by the cfqueryparam cf_sql_interger or cf_sql_varchar, maxlength="5" would cut the length of the injection down to only 5 characters.

There isn't any harmful 5 character SQL commands. maxlength="5" adds one more line of protection.

SQL Injection Examples

In this first example I have added AB to the end of the ID variable value to show that you can't added alpha characters to a variable value that is wrapped with a the cf_sql_interger setting.

http://www.carsinlondon.com/p.cfm?id=123AB

In this next example I have added a SQL command that could delete a complete table. It is over 5 charactors as you can see. Take a look at the error.

http://www.carsinlondon.com/p.cfm?id=123' or 1=1; drop table tblcars; --

Isolate The Passed Variable Values

In my next example I go a step further. Instead of checking variable value that is being passed before it is inserted into the SQL statement, the variable value being passed by the query string is not inserted in SQL statement at all.

There are a number of ways to do this. The method I use takes the variable value being passed in the query string and compares it using the cfswitch tag to a number of values.

The cfswitch tag is put right in the SQL statement. If the query string variable value matches any a cfcase values then the value enclosed in the cfcase tag will be selected. If the query string variable value doesn't match any of the cfcase values then the cfdefaultcase value is selected.

<cfswitch expression="#URL.o#">
<!--check for variable URL.o and compare with cfcase values-->
<cfcase value="Y">
Year #URL.f#, Make ASC, Model ASC
</cfcase>
<cfcase value="MA">
Make #URL.f#, Model ASC, Year ASC
</cfcase>
<cfcase value="MO">
Model #URL.f#, Year ASC, Price ASC
</cfcase>
<cfcase value="V">
tblCars.VN #URL.f#, Make ASC, Model ASC
</cfcase>
<cfcase value="S">
Style #URL.f#, Make ASC, Model ASC
</cfcase>
<cfcase value="O">
Mileage #URL.f#
</cfcase>
<cfcase value="P">
Price #URL.f#
</cfcase>
<!--if no cfcase matches use this as default-->
<cfdefaultcase>
Make #URL.f#, Model ASC, Year ASC
</cfdefaultcase>
</cfswitch>

Check, Pass or Change

The next example is quite a bit simpler. The variable value that is passed in the query string should be either DESC or ASC. This gives the ability to order the data columns displayed either by ascending or descending.

What is put in the URL.f will be inserted if it is ASC or DESC. If there is some other value the variable is set to a default value of ASC.

While this variable value is inserted into the SQL statement, what is done is the variable value that is passed in the query string is checked to make sure it is what is allowed and if not it is changed.

<!--set defaults for vars-->
<cfparam name="URL.f" default="ASC" type="string">
<!--check URL.f and if it is not ASC or DESC set it to ASC-->
<cfif not ((URL.f EQ "ASC") or (URL.f EQ "DESC"))>
<cfset URL.f = "ASC">
</cfif>

You can take a look and see how this works at http://www.carsinlondon.com/inventory.cfm?vn=0&o=MA&f=DESC