Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at NCDevCon 2011 (Raleigh, NC) with: Mike Kingery and Jaana Gilbert

Learning ColdFusion 9: Extending The Query.cfc Service Component

By Ben Nadel on
Tags: ColdFusion

Yesterday, I blogged about using CFQuery (and other service tags) in CFScript with ColdFusion 9's new script enhancements. As I mentioned in that post, CFQuery and other service-based tags have been turned into ColdFusion components that you can instantiate and execute within CFScript. Unfortunately, when it comes to Query.cfc, there are some flaws in the way the SQL statements are parsed.

The code for Query.cfc is rather complex and does more than I can easily follow; but from what I could gather, it's injecting the query params by treating the SQL statement as a list with "?" and ":" delimiters. While this works in some cases, you have to be very careful about the types of characters that follow your query param place holders or your code will break. Take a look at this example:

  • <cfscript>
  •  
  • // Create a new insert query.
  • insertQuery = new Query();
  •  
  • // Set up the sql statement.
  • insertQuery.setSQL("
  • INSERT INTO girl
  • (
  • name,
  • hair
  • ) VALUES (
  • :name,
  • :hair
  • )
  • ");
  •  
  • // Add query params.
  • insertQuery.addParam(
  • name = "name",
  • value = "Tricia",
  • cfsqltype="cf_sql_varchar"
  • );
  •  
  • // Add query params.
  • insertQuery.addParam(
  • name = "hair",
  • value = "Brunette",
  • cfsqltype="cf_sql_varchar"
  • );
  •  
  • // Run the insert query.
  • insertQuery.execute();
  •  
  • </cfscript>

When we run this code, we get the following ColdFusion error:

Named Sql parameter 'name,' used in the query not found in the queryparams. The SQL specified in the query:

INSERT INTO girl ( name, hair ) VALUES ( :name, :hair ).

As you can see, by keeping the comma after our ":name" parameter, Query.cfc mistakenly thinks that it is part of the query param name. But, that's not the only problem with this query. We can fix this by putting a space after the ":name" place holder:

:name ,

... but this only highlights the next error:

Named Sql parameter 'hair )' used in the query not found in the queryparams. The SQL specified in the query:

INSERT INTO girl ( name, hair ) VALUES ( :name , :hair ).

In this case, the error behavior is even more funky in that it thinks that the entire string, ":hair )" is the query param name. Why did the space after "name" fix an error, but not the space after "hair"? While I cannot answer that definitively (I couldn't find the reason for this in the code of Query.cfc), I can tell you that the space after "hair" isn't really a space - it's a line return followed by a tab. You can actually fix this error by putting a single space after the ":hair" value.

In addition, if you pick your way through the Query.cfc code, you will see that it deals with "?" and ":" inside of string values by escaping them (replacing them with different characters) before parsing, and then unescaping them during query creation. While this is not an actual error, it just highlights the fact that treating a SQL statement as a delimited list can lead to unnecessary complexity.

As you can see, treating the SQL statement as a delimited list leads to a number of hurdles. All of this could be fixed if the SQL statement was viewed as a collection of tokens that was parsed with a small regular expression. But, how can we make that happen? Quite naturally actually - all we have to do is leverage the inherent extensibility of ColdFusion and extend the Query.cfc component, overriding the Execute() method. This way, we get to use all of the property definitions already in place and just update the method that is causing the bugs.

As a proof of concept of this, I created the ColdFusion component, MyQuery.cfc, which extends com.adobe.coldfusion.Query:

MyQuery.cfc

  • <cfcomponent
  • extends="com.adobe.coldfusion.Query"
  • output="false"
  • hint="I am a customized query object. I simply the SQL parsing and increase the effectiveness of pulling out query params.">
  •  
  •  
  • <cffunction
  • name="execute"
  • access="public"
  • returntype="result"
  • output="true"
  • hint="I execute the query statement.">
  •  
  • <!---
  • Create an array to keep track of named and positioned
  • query params. We will break this up to make it easier
  • to find.
  • --->
  • <cfset local.namedParams = {} />
  • <cfset local.orderedParams = [] />
  •  
  • <!---
  • Loop over the parameters to sort them into the above
  • named and ordered arrays.
  • --->
  • <cfloop
  • index="local.param"
  • array="#variables.parameters#">
  •  
  • <!---
  • Check to see if the parameter has a name. If it
  • does, then it's named. If it doesn't, then it's
  • ordered.
  • --->
  • <cfif !isNull( local.param.name )>
  •  
  • <!---
  • Named param. Index these locall by name so
  • that we can easily access them.
  • --->
  • <cfset local.namedParams[ local.param.name ] = local.param />
  •  
  • <cfelse>
  •  
  • <!---
  • Ordered param. Simply add these in order.
  • We will be deleting these as we use them
  • such that the first one is always the next
  • one to be injected.
  • --->
  • <cfset arrayAppend( local.orderedParams, local.param ) />
  •  
  • </cfif>
  •  
  • </cfloop>
  •  
  •  
  • <!---
  • Now, let's parse our SQL statements into parts.
  • To do so, we are going to create a regular expression
  • that will find only valid values.
  • --->
  • <cfsavecontent variable="local.regex"
  • >(?xi)
  •  
  • (?:
  • ## Raw SQL statement.
  • ([^':?]+)
  • |
  • ## Quoted value.
  • (?:'([^']*(?:''[^']*)*)')
  • |
  • ## Ordered parameter.
  • (\?)
  • |
  • ## Named parameter.
  • (?::([a-z]+))
  • )
  • </cfsavecontent>
  •  
  •  
  • <!---
  • Create a java pattern object to pick apart the SQL
  • statement. We are going to do this because it will
  • give us access to the groups.
  • --->
  • <cfset local.pattern = createObject(
  • "java",
  • "java.util.regex.Pattern"
  • ).compile(
  • javaCast( "string", local.regex )
  • )
  • />
  •  
  • <!---
  • Get the matcher for our pattern using our SQL
  • statement.
  • --->
  • <cfset local.matcher = local.pattern.matcher(
  • javaCast( "string", this.getSQL() )
  • ) />
  •  
  •  
  • <!---
  • Duplicate the tag attributes because we don't want
  • it to execute the SQL statement property.
  • --->
  • <cfset local.tagAttributes = duplicate(
  • this.getAttributes()
  • ) />
  •  
  • <!--- Remove SQL attribute. --->
  • <cfset structDelete( local.tagAttributes, "sql" ) />
  •  
  •  
  • <!--- Run the query. --->
  • <cfquery
  • name="local.query"
  • result="local.result"
  • attributecollection="#local.tagAttributes#">
  •  
  • <!---
  • Keep looping over the matches while we have more
  • to inject into our query statement.
  • --->
  • <cfloop condition="local.matcher.find()">
  •  
  • <!---
  • Get the groups. Remember, each captured group
  • represented a differnt type of token.
  • --->
  • <cfset local.token = {
  • rawSQL = local.matcher.group(
  • javaCast( "int", 1 )
  • ),
  • quotedValue = local.matcher.group(
  • javaCast( "int", 2 )
  • ),
  • orderedParam = local.matcher.group(
  • javaCast( "int", 3 )
  • ),
  • namedParam = local.matcher.group(
  • javaCast( "int", 4 )
  • )
  • } />
  •  
  • <!--- Check to see which SQL token we found. --->
  • <cfif !isNull( local.token.rawSQL )>
  •  
  • <!--- Inject raw SQL. --->
  • #local.token.rawSQL#
  •  
  • <cfelseif !isNull( local.token.quotedValue )>
  •  
  • <!--- Inject quoted value. --->
  • '#local.token.quotedValue#'
  •  
  • <cfelseif !isNull( local.token.orderedParam )>
  •  
  • <!--- Inject ordered parameter. --->
  • <cfqueryparam
  • value="#local.orderedParams[ 1 ].value#"
  • cfsqltype="#local.orderedParams[ 1 ].cfsqltype#"
  • />
  •  
  • <!---
  • Delete the first ordered parameter so that
  • it doesn't get used in future iterations.
  • --->
  • <cfset arrayDeleteAt( local.orderedParams, 1 ) />
  •  
  • <cfelseif !isNull( local.token.namedParam )>
  •  
  • <!--- Inject named parameter. --->
  • <cfqueryparam
  • value="#local.namedParams[ local.token.namedParam ].value#"
  • cfsqltype="#local.namedParams[ local.token.namedParam ].cfsqltype#"
  • />
  •  
  • </cfif>
  •  
  • </cfloop>
  • </cfquery>
  •  
  •  
  • <!--- Create a new final result. --->
  • <cfset local.executionResults = new Result() />
  •  
  • <!---
  • Store the query data. If this was an insert or a
  • delete, there will be no query data.
  • --->
  • <cfif !isNull( local.query )>
  • <cfset local.executionResults.setResult( local.query ) />
  • </cfif>
  •  
  • <!--- Store the prefix data. --->
  • <cfset local.executionResults.setPrefix( local.result ) />
  •  
  •  
  • <!--- Return execution. --->
  • <cfreturn local.executionResults />
  • </cffunction>
  •  
  • </cfcomponent>

If you've looked at the Query.cfc code, you might notice that I am taking a completely different approach to integrating query params. Query.cfc treats named and ordered query params in a completely different way, which I didn't quite understand; in MyQuery.cfc, I am simply matching all query params within the SQL statement and then replacing them as needed. When I find a named query param, I use the matching value. When I find an ordered query param, I use the next available non-named value.

Of course, this query-param-integration simplicity is made possible, in part, because the SQL statement is being tokenized with a regular expression rather than being treated as a list. In doing so, I can easily see what kind of token I have and, if necessary, what kind of query param needs to be injected. Furthermore, I don't have to worry about special characters inside of single quotes as quoted values are being parsed as single tokens.

With this in place, I can now run CFScript-based queries without any syntax errors:

  • <cfscript>
  •  
  • // Create a new insert query.
  • insertQuery = new MyQuery();
  •  
  • // Set up the sql statement.
  • insertQuery.setSQL("
  • INSERT INTO girl
  • (
  • name,
  • hair
  • ) VALUES (
  • :name,
  • :hair
  • )
  • ");
  •  
  • // Add query params.
  • insertQuery.addParam(
  • name = "name",
  • value = "Tricia",
  • cfsqltype="cf_sql_varchar"
  • );
  •  
  • // Add query params.
  • insertQuery.addParam(
  • name = "hair",
  • value = "Brunette",
  • cfsqltype="cf_sql_varchar"
  • );
  •  
  • // Run the insert query.
  • insertResult = insertQuery.execute();
  •  
  • // writeDump( insertResults );
  •  
  •  
  • // ------------------------------------------------------- //
  • // ------------------------------------------------------- //
  •  
  •  
  • // Create a new query to gather girls.
  • selectQuery = new MyQuery();
  •  
  • // Set SQL statement. In this statement, I am using a lot of
  • // extra parameters only to demonstrate the way that the
  • // MyQuery() is parsing the various possible scenarios.
  • selectQuery.setSQL("
  • SELECT
  • id,
  • name,
  • hair
  • FROM
  • girl
  • WHERE
  • name = ?
  • AND
  • hair = :hair
  • AND
  • id = ?
  • AND
  • 'test''s :value' = 'test''s :value'
  • ");
  •  
  • // Add ORDERED query params.
  • selectQuery.addParam(
  • value = "Tricia",
  • cfsqltype="cf_sql_varchar"
  • );
  •  
  • // Add NAMED query params.
  • selectQuery.addParam(
  • name = "hair",
  • value = "Brunette",
  • cfsqltype="cf_sql_varchar"
  • );
  •  
  • // Add ORDERED query params.
  • selectQuery.addParam(
  • value = insertResult.getPrefix().identityCol,
  • cfsqltype="cf_sql_integer"
  • );
  •  
  • // Execute select statement.
  • selectResult = selectQuery.execute();
  •  
  • // Output results.
  • writeDump(
  • var = selectResult,
  • label = "Girls (via MyQuery.cfc)"
  • );
  •  
  • </cfscript>

Notice that I am re-running the INSERT statement that was causing problems above. Then, I execute a SELECT statement with various named and ordered parameters. When we run the above code, we get the following CFDump output:

 
 
 
 
 
 
Extending The Query.cfc In ColdFusion 9 Allows Us To Fix Some Bugs. 
 
 
 

As you can see, all the queries were successfully executed, even when punctuation followed directly after a named parameter. Also notice that the named and ordered parameters were all injected correctly and that special characters within quoted values were ignored.

MyQuery.cfc does not do as much as Query.cfc does; specifically, it doesn't have special code to handle advanced SQL statements like EXEC() and CALL() commands. But, it does simplify the SQL parsing in such a way that it fixes the tokenizing problems that are present in the way that Query.cfc parses the SQL statements. Once again, it seems that regular expressions save the day!

NOTE: This technique does not fix the SQL injection problem in any way. That is not something that can be dealt with easily as the SQL injection has already happend before the SQL statement is parsed. The best we can probably do is add a method to examine the SQL statement before it is parsed and throw an error when somthing *looks* malicious.




Reader Comments

That's a nice bit of work, Ben. Hopefully, Adobe picks this up and doesn't release a mess of a Query component.

One suggestion I have is use (?::([_a-zA-Z]\w*)) as the named-param pattern, so that you can use e.g. ':__idea1_4_ABC' or ':_' as a param name (useful for generated queries with loops, for example).

Reply to this Comment

@Justice,

Nice tip - the underscore is a good idea. I always forget about that one. The A-Z is taken care of by the (?i) case-insensitive flag at the beginning. Number is also a good idea. I like where you're going with it - start out with an alpha character and then allow with any word character to follow. Yeah, that would be nice.

Reply to this Comment

Again I say: righteous hack. I did something like this not a month ago for a DAO where I wanted to add logging to any queries that went through it. But I didn't have CF9 so it's quite a bit nastier.

Reply to this Comment

I'm a bit disappointed that Query.cfc is currently implemented in such a problematic way (though I share Justice's hope that they will fix it).

On the other hand, I really like the ability to extend a built in service. I hadn't seen that before. That could be really handy.

Reply to this Comment

Seems like a lot of work just to run a simple insert. Geez. Here's how I'd do it w/ the EDM:

using (MyDataContext db = new MyDataContext())
{
db.Girls.Add(
new Girl { Name = "Tricia", Hair = "Brunette" });
db.SaveChanges();
}

Jobs done!

Reply to this Comment

I would suggest a small change to the addParam method that coul,d make this easier to adopt to people used to cfqueryparam, have it return the parameter placeholder like this

insertQuery.addParam(name="name",value="Tricia",cfsqltype="cf_sql_varchar") would return ":name", that way you could write you query like:

insertQuery.setSQL("
INSERT INTO girl(name,hair) VALUES (#insertQuery.addParam(name="name",value="Tricia",cfsqltype="cf_sql_varchar")#,#insertQuery.addParam(name="hair",value="Brunette",cfsqltype="cf_sql_varchar")#)
");

insertQuery.setSQL would still be passed
"INSERT INTO girl(name,hair)VALUES(:name,:hair)"

but you would be adding the params as you assembled the querystring, you could build your querystring dynamically like:

insertQuery = new Query();
sqlstring="SELECT * FROM girls WHERE 5=5";
if(IsDefined('Search_Hair'))sqlstring+=" AND hair=#insertQuery.addParam(name="hair",value=Search_Hair,cfsqltype="cf_sql_varchar")#
insertQuery.setSQL(sqlstring)

It would insure that positional params were in the proper/corresponding order as well

I think it would also lend itself to making the pattern matching more flexible, if you had problems with it mismatching you could modify the regex pattern and the result of addParam inside the cfc without rewriting the queries themselves so if you wanted to have params like "<__param-name__>" you could change the regex to
"(?:<__([a-z][a-z0-9_]*?)__>)" <- nongreedy, must start with letter allow numbers and underscores

and have addParam return "<__#Arguments.Name#__>"

(addParam's result could be determined by if it was passed a name or not, ie named or positional placeholder)

the calls to setSQL and addParam would be unchanged but result would be "INSERT INTO girl(name,hair)VALUES(<__name__>,<__hair__>)"

BTW: your regex "(?::([a-z]+))" won't match Address2 which would be a shame

Reply to this Comment

Or better yet instead of modifying addParam create a method called something like addQueryParam and have it wrap around addParam, that way you can avoid inconsistencies with addParam in the other service components.

BTW: can you fix the word wrap with my previous post so it doesn't cut off the long lines?

Reply to this Comment

@David,

That's a cool idea. I didn't think of adding the params in-line as part of a method call. I fooled around with them being parsed, but it was not viable. Your solution, to return the named (or ordered param) would be nice.

Ooops, you are right on the regex. Nice catch. It should probably be something like:

[a-z]\w*

Reply to this Comment

Glad you both seem to like it, it just seemed to me that setting the params after assembling the query string is rife with potential difficulties and counter-intuitive, I like the <cfquery>/<cfqueryparam> method, and this seemed to me to closely replicate that style which I think would make it easier to adopt.

Hope to see you all at MAX! :)

Reply to this Comment

Does using these functions within cfscript require any prerequisite code/includes? I'm running CF9 Enterprise and get the following error on the first line of code being:

Code (obviously within <cfscript>):
getContent = new query();

Error:
Ensure that the name is correct and that the component or interface exists.

Could not find the ColdFusion component or interface query

Reply to this Comment

@Dan,

Hmm, not that I know of. By default, ColdFusion should look in the core custom tags folder for the Query.cfc object. Is it possible that that default mapping is broken in your install.

Worst case scenario, you can either add (re-add) that custom tag mapping in your CF Admin or in the this.customTagPaths in your Application.cfc. The custom tags default folder should be in your install folder somewhere.

Reply to this Comment

@Dan B:
I had that very problem today. I'd executed code at home on my laptop that included new Query() in it without a hitch, then when I got to the office and executed the same code (on the same version of CF), it couldn't find the component. I had weird problems even after adding a mapping. Rebooting the machine fixed it. Odd.

Reply to this Comment

This works very well, although I noticed that this seemed to be ignoring list parameters. I added a small change which seems to solve my problem when I add list="true" to the "addParam" function

  •  
  • <cfif !isNull( local.token.rawSQL )>
  • #local.token.rawSQL#
  • <cfelseif !isNull( local.token.quotedValue )>
  • '#local.token.quotedValue#'
  • <cfelseif !isNull( local.token.orderedParam )>
  • <cfif structKeyExists( local.orderedParams[ 1 ], "list" )>
  • <cfqueryparam value="#local.orderedParams[ 1 ].value#" cfsqltype="#local.orderedParams[ 1 ].cfsqltype#" list="#local.orderedParams[ 1 ].list#" />
  • <cfelse>
  • <cfqueryparam value="#local.orderedParams[ 1 ].value#" cfsqltype="#local.orderedParams[ 1 ].cfsqltype#" />
  • </cfif>
  • <cfset arrayDeleteAt( local.orderedParams, 1 ) />
  • <cfelseif !isNull( local.token.namedParam )>
  • <cfif structKeyExists( local.namedParams[ local.token.namedParam ], "list" )>
  • <cfqueryparam value="#local.namedParams[ local.token.namedParam ].value#" cfsqltype="#local.namedParams[ local.token.namedParam ].cfsqltype#" list="#local.namedParams[ local.token.namedParam ].list#" />
  • <cfelse>
  • <cfqueryparam value="#local.namedParams[ local.token.namedParam ].value#" cfsqltype="#local.namedParams[ local.token.namedParam ].cfsqltype#" />
  • </cfif>
  • </cfif>

Hopefully, Query.cfc will be a little smarter in subsequent releases.

Reply to this Comment

Hi Ben,
I have a question for you.
How would you display the status of the query in Coldfusion?
For example, when the table is being updated, I would like to display as it's being updated by <so and so user>
Please let me know, if you have any ideas.
Thanks
MM

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.