I woke up this morning to hundreds of alert emails from one of my websites. It seems while I was sleeping someone or "somebot" tried a SQL Injection attack on this site and they (or the bot) failed miserably. Why did they fail? I properly use CFQUERYPARAM.

The fundamental problem in SQL injection is concatenation of untrusted data (raw user input) to trusted data and the whole strings is being sent to the database for execution. The moment you merge the raw untrusted data to trusted data for execution, you got a problem. You should never allow raw data get to the database.

The nature of this attack was to append to a URL param like this.

view plain print about
1action=song&prod=11866+and+1=0+%20Union+Select+......... (I'm leaving off the encoded SQL)

My alerts told me that the value failed for the data type. Part of the reason for using the CFQUERYPARAM tag is to enforce data protection by using the CFSQLTYPE and MAXLENGTH attributes.

view plain print about
1<!--- borrowed from the ColdFusion 8 LiveDocs
2http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_18.html
3--->

4 <cfquery name = "query name" dataSource = "data source name" ...other attributes...
5 SQL STATEMENT column_name =
6 <cfqueryparam value = "parameter value"
7 CFSQLType = "parameter type"
8 list = "yes|no"
9 maxLength = "maximum parameter length"
10 null = "yes|no"
11 scale = "number of decimal places"
12 separator = "separator character">

13 AND/OR ...additional criteria of the WHERE clause...>
14 </cfquery>

This SQL Injection attack was prevented because the datatype was not integer. If this was a string field then it would have failed due to the MAXLENGTH attribute except for maybe on some very long fields. Next, because CFQUERYPARAM creates a Prepared Statement instead of raw SQL, essentially encapsulating the raw data and preventing it from being executed.

In addition to stopping these types of attacks you need to know when these attack happen. I was sound asleep when these happened, and I really don't feel like reading miles of log files every morning to check to see if something happened. I've setup email notification for these types of errors so that I get alerted to a threat and can then check to make sure nothing else happened. I know a lot of people that never log these things and still many more that log these errors but never review the log files. Trust the logs! Believe the logs! The logs aren't lying. Reading the logs are time consuming (and can cure insomnia) which is why some sort of log scanning tool and notifications should be setup. You can't stop an attack if you don't know about it.

In this case the code blocked the attack, my alerts told me about it and I went on with my morning consuming caffeine.