CFQUERYPARAM with MS SQL full-text searching

I don’t work with SQL Server much so I thought this might save someone else some effort when trying to figure out the right combination of CFQUERYPARAM and Transact-SQL keywords. We have a search object that stores keywords and want to loop over the keywords to create a dynamic search query against our full-text catalog. The catch is that this original code is not protected from SQL injections:

< !--- loop through each keyword and create CONTAINS statement --->
<cfif arguments.SearchCriteria.countKeywords() gt 0>
AND
(
(
CONTAINS(recommendation_search.*, 'FORMSOF(INFLECTIONAL, #arguments.SearchCriteria.getKeyword(1)#)
<cfloop from="2" to="#arguments.SearchCriteria.countKeywords()#" index="currentPosition">
AND FORMSOF(INFLECTIONAL, #arguments.SearchCriteria.getKeyword(currentPosition)#)
</cfloop>')
)
OR
(
CONTAINS(recommendation_search.*, '"*#arguments.SearchCriteria.getKeyword(1)#*"
<cfloop from="2" to="#arguments.SearchCriteria.countKeywords()#" index="currentPosition">
AND "*#arguments.SearchCriteria.getKeyword(currentPosition)#*"
</cfloop>')
)
)
</cfif>

It’s been a tough couple of weeks for ColdFusion applications and SQL injections but the attack vector is always the same: unprotected SQL values that hackers can exploit. The solution is as simple as using CFQUERYPARAM and I use it religiously. I am working on this project with some other developers and I found this search query with some exposed variables so prior to launch I wanted to clean up this hole by moving to CFQUERYPARAM.

Not knowing much about MSSQL’s full text functions, it took a lot of permutations of CFQUERYPARAM and regular text before I found the working option. The tricky part is the difference between FORMSOF and regular “*keyword*” syntax. Once I had a single keyword working, I found that multiple search terms would break that approach so it was back to the drawing board. My interim attempts generated a lot of errors that looked like:

07009
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver]Invalid parameter binding(s).

To get this working properly, you’ll need to create a temporary variable that holds the entire search string and then parameterize that. The final code looks like:

< !--- loop through each keyword and create CONTAINS statement --->
<cfif arguments.SearchCriteria.countKeywords() gt 0>
AND
(
(
<cfsavecontent variable="cfqp">
FORMSOF(INFLECTIONAL, #arguments.SearchCriteria.getKeyword(1)#)
<cfloop from="2" to="#arguments.SearchCriteria.countKeywords()#" index="currentPosition">
AND FORMSOF(INFLECTIONAL, #arguments.SearchCriteria.getKeyword(currentPosition)#)
</cfloop>
</cfsavecontent>
CONTAINS(*, <cfqueryparam cfsqltype="cf_sql_varchar" value="#cfqp#" />)
)
OR
(
<cfsavecontent variable="cfqp">
"*#arguments.SearchCriteria.getKeyword(1)#*"
<cfloop from="2" to="#arguments.SearchCriteria.countKeywords()#" index="currentPosition">
AND "*#arguments.SearchCriteria.getKeyword(currentPosition)#*"
</cfloop>
</cfsavecontent>
CONTAINS(*, <cfqueryparam cfsqltype="cf_sql_varchar" value="#cfqp#" />)
)
)
</cfif>

Don’t forget to var your CFQP variable!