Ask-a-Muse: How Can Cfqueryparam Protect Me?
Muse Reader Asks:
If you want to allow someone to search your site by keyword, how do you protect against an SQL injection? CFqueryParam is great if testing for an integer, but what about for a string? Surely there's got to be a way to do it since all kinds of sites let you perform keyword searches. Thanks!
Whoa... slow down there. Do my ears deceive me? Did my reader just indicate that he (or she) thinks that cfqueryparam "tests" for a string? I hate to break it to you, but the purpose of Cfqueryparam is not to insure that the value passed into the tag is one thing or another. The validation that occurs is more of a by-product of binding. Sure, the tag will error out when you try to pass "abc" instead of "123" to a param of the "integer" type, but that is a result of type binding. It's simply trying to bind variables of type for the driver to use, so naturally it errors out. But pass in a decimal like 123.123 and it says "okey dokey - that will work". Testing to see what a form element contains is the job of the developer, not the job of a magic box tag.
But to answer your question more specifically, cfqueryparam will protect you from those malicious hack attempts anyway - even if the attack is passed to the database. Let's examine a working case and see if we can figure out what is happening.
To illustrate we need an SQLi attack that is targeting a character field. Let's borrow one from my previous post on SQLi Using a Character Field. It looks like this:
default="I can\'; drop table ignominiousHits -- t get no satisfaction"/>
<cfquery ...>
SELECT *
FROM IgnominiousHits
WHERE Title = '#form.song#'
</cfquery>
For those of you playing along at home, this attack will succeed if you are using MySQL with backslash escaping turned on (which is the default) and the ability to run multiple statements in a single query turned on. Running this code would result in the following query being run against the DB.
SELECT *Coldfusion plays an unwitting role in this drama by escaping the single quote. Since the backslash escapes the first quote the single quote added by ColdFusion terminates the string. the semi-colon is honored as a statement terminator and the rest of the code is executed - viola! To put it another way, the statement above is the equivalent of the following:
FROM IgnominiousHits
WHERE Title = 'I can\''; drop table ignominiousHits -- t get no satisfaction
SELECT *
FROM IgnominiousHits
WHERE Title = 'I can''';
drop table ignominiousHits -- t get no satisfaction
Obviously this is a bad thing. To prevent it, I have suggested here and many many (many) other places that each parameter being used in the query should be bound to a variable through the use of cfqueryparam like so:
default="I can\'; drop table ignominiousHits -- t get no satisfaction"/>
<cfquery ...>
SELECT *
FROM IgnominiousHits
WHERE Title = <cfqueryparam cfsqltype="CF_SQL_CHAR" value="#form.song#"/>
</cfquery>
In the case of the readers question above, how does this help me? After all, it does allow the malicious string to be passed to the database... doesn't it? Why yes, yes it does. But think about what is really happening here. Let's pretend I'm the MySQL dispatcher and I'm going to spell out what I want the DB to do.
| Dispatcher: | I have a new query for you. |
| DB Hamsters: | Dude, we are spinning this wheel as fast as we can but whatever.. .bring it on. |
| Dispatcher: | Ok... first, I want you to select all the columns from the IgnominiousHits table and... |
| DB Hamsters: | Hang on a minute... (all the columns - a plague on whoever thought up that stupid asterisk)... Let's see, system schema, check, ignoble, ignat (nope that's a view) .. ah here we are - ignominiousHits, all the columns got it. Ok what's next dispatch? |
| Dispatcher: | Right... ok, now we have a variable to work with of the type "character". |
| DB Hamsters: | Check... let's call it @param1 - ready to receive. |
| Dispatcher: | Ok... here's comes the character array... I can\'; drop table ignominiousHits -- t get no satisfaction |
| DB Hamsters: | Ok I got it and it's stored in my little @param1 box. What do we do with it. |
| Dispatcher: | Right.. ok, now compare the column TITLE to @param1 looking for exact matches. |
| DB Hamsters: | Got it, ok... executing now... sorry dispatch but that returns zero records. Tell your user to go back to the drawing board. Meanwhile I have to spit out these seeds - my cheeks are killing me. |
The point is that the string being passed is being "boxed up" in a variable "of type char". The database will not do anything with it unless specifically instructed to evaluate it as if it were valid executable SQL. In short, any variety of malicious code passed into the query through the use of this variable cannot succeed in injecting.
To come full circle back to my user's question, cfqueryparam when used will protect your search from injection by "binding" the values passed as URL or form variables to a specific "type" known to the database. Even if you are under attack and malicious code is being presented to your binding, the attack cannot succeed. The variable cannot be anything other than the declared type (i.e. it cannot be executed), and therefore you are protected.
I hope this reassures you. Keep those questions coming.
5 Comments
-
Great explanation of cfqueryparam!
As it happens, I started listening to the Stones just before reading this post. That song came on just after I finished reading it. Pretty cool synchronicity!
Anyway, I plan to point people here when they have questions about how cfqueryparam works. It is a really solid introduction. -
Really glad to see these CFQUERYPARAM posts keep rolling, both here and elsewhere. It really is just a flat-out *must have* for every CF database call. Sorry for a long response here, but one thing that people may not realize, those who are new to the wonders of CFQUERYPARAM, is that there is a performance boost from its use as well. Forta mentioned it the other day, and it really is true.
Continuing your example, Mark, SQL Server turns that statement into something along these lines:
@param1 nvarchar(20)
SET @param1 = [what is now your BOXED form string]
SELECT *
FROM IgnominiousHits
WHERE Title = @param1
Note that the actual query which gets executed on the server for every single request will be:
SELECT *
FROM IgnominiousHits
WHERE Title = @param1
The key to performance is that no matter what gets put into that form, every single time the search query is run, the server thinks it's the same query. The first time it's run, the server caches the query definition, and every run after that uses the cached copy, because it never changes.
Bottom line: don't think that SQLi attacks are the only reason to be using CFQUERYPARAM. It really is your friend and mine! -
CFParam, on the other hand, does... while mostly it seems to just be used to avoid breaking apps when a referenced variable may or may not exist, it does have additional very useful attributes type, pattern, min, and max.
and of course, if you're using MS SQL, CF will prevent that string attack even without a param. -
The trick in doing this comes in when you want the user to be able to search with multiple text strings. For instance let's say I want them to able to select multiple search options, and be able to enter multiple words with an "AND" or "OR" option. To handle this, I wrote a custom tag that loops through the search string and outputs each of the words into a cfqueryparam and then just call that code from within the query. You can call it multiple times for different fields, setting the column name and type. It would look sort of like this (hope I type this right!)
<cfparam name="attributes.all_words" default="false">
<cfparam name="attributes.sqltype" default="longvarchar">
<cfset attributes.fieldname = "Title">
<cfset variables.numwords = ListLen(attributes.search_string, " ")>
<!--- loop through words separated by spaces --->
<cfoutput>
( <cfloop index="i" from="1" to="#variables.numwords#">
#attributes.fieldname# LIKE
<cfqueryparam cfsqltype="CF_SQL_#attributes.sqltype#" value="%#ListGetAt(attributes.search_string, i, " ")#%">
<cfif i IS NOT variables.numwords AND attributes.all_words> AND <cfelseif i IS NOT variables.numwords> OR </cfif>
</cfloop> )
</cfoutput> -
Drat, sorry about that! Maybe it will like this version better:
<cfparam name="attributes.all_words" default="false">
<cfparam name="attributes.sqltype" default="longvarchar">
<cfset attributes.fieldname = "Title">
<cfset variables.numwords = ListLen(attributes.search_string, " ")>
<!--- loop through words separated by spaces --->
<cfoutput>
( <cfloop index="i" from="1" to="#variables.numwords#">
#attributes.fieldname# LIKE
<cfqueryparam cfsqltype="CF_SQL_#attributes.sqltype#" value="%#ListGetAt(attributes.search_string, i, " ")#%">
<cfif i IS NOT variables.numwords AND attributes.all_words> AND <cfelseif i IS NOT variables.numwords> OR </cfif>
</cfloop> )
</cfoutput>
Sites to Watch
Aggregators
RSS
Related Blog Entries