Or at at least that is the way I am feeling today. Consider this piece of code,
it is contained in a CFC that a user has entered in a Search Term to look up
against the database.
<cfquery name="getSearch" result="DB_getSearch" datasource="#DSN#">
SELECT TOP #APPLICATION.MaxQueryRows# f.*
FROM field f
WHERE 0=0
AND (
f.field_logical_nm LIKE <cfqueryparam cfsqltype="cf_sql_varchar"
value="%#ARGUMENTS.SearchTerm#"/>
OR f.field_logical_nm LIKE <cfqueryparam cfsqltype="cf_sql_varchar"
value="%#ARGUMENTS.SearchTerm#%"/>
OR f.field_logical_nm LIKE <cfqueryparam cfsqltype="cf_sql_varchar"
value="#ARGUMENTS.SearchTerm#%"/>
OR f.field_physical_nm LIKE <cfqueryparam cfsqltype="cf_sql_varchar"
value="%#ARGUMENTS.SearchTerm#"/>
OR f.field_physical_nm LIKE <cfqueryparam cfsqltype="cf_sql_varchar"
value="%#ARGUMENTS.SearchTerm#%"/>
OR f.field_physical_nm LIKE <cfqueryparam cfsqltype="cf_sql_varchar"
value="#ARGUMENTS.SearchTerm#%"/>
)
AND f.field_id NOT IN (
SELECT TOP #ARGUMENTS.StartRow# f.field_ID
FROM field f
WHERE 0=0
AND (
f.field_logical_nm LIKE <cfqueryparam
cfsqltype="cf_sql_varchar" value="%#ARGUMENTS.SearchTerm#"/>
OR f.field_logical_nm LIKE <cfqueryparam
cfsqltype="cf_sql_varchar" value="%#ARGUMENTS.SearchTerm#%"/>
OR f.field_logical_nm LIKE <cfqueryparam
cfsqltype="cf_sql_varchar" value="#ARGUMENTS.SearchTerm#%"/>
OR f.field_physical_nm LIKE <cfqueryparam
cfsqltype="cf_sql_varchar" value="%#ARGUMENTS.SearchTerm#"/>
OR f.field_physical_nm LIKE <cfqueryparam
cfsqltype="cf_sql_varchar" value="%#ARGUMENTS.SearchTerm#%"/>
OR f.field_physical_nm LIKE <cfqueryparam
cfsqltype="cf_sql_varchar" value="#ARGUMENTS.SearchTerm#%"/>
)
ORDER BY f.field_logical_nm
)
ORDER BY f.field_logical_nm
</cfquery>
As it stands it the user enters in the following text as the Search Term: _a_
the following results are returned:
cat
bat
bar
kat
Instead of what was really expected like this:
in_a_boat
can run_a_mile
if_a_bird flies
Ok I think you get the picture. It returns any three letter words that contain
an 'a' in the middle position instead of any phrase that contains the '_a_'
string. However this is not the behavior I would expect using <cfqueryparam>
with the type set to varchar, then again maybe this is a case of me just
needing to RTFM for both ColdFusion & T-SQL.
But to get the expect results I had to escape any underscores in the parameter
passed from the end user form turning the code into this:
<cfif isDefined('ARGUMENTS.SearchTerm')>
<cfset tmpSearchTerm =
ReplaceNoCase(ARGUMENTS.SearchTerm,'_','[_]','all')/>
<cfelseif isDefined('ARGUMENTS.FieldNameText')>
<cfset tmpSearchTerm =
ReplaceNoCase(ARGUMENTS.FieldNameText,'_','[_]','all')/>
</cfif>
<cfquery name="getSearch" result="DB_getSearch" datasource="#DSN#">
SELECT TOP #APPLICATION.MaxQueryRows# f.*
FROM field f
WHERE 0=0
AND (
f.field_logical_nm LIKE <cfqueryparam cfsqltype="cf_sql_varchar"
value="%#VARIABLES.tmpSearchTerm#"/>
OR f.field_logical_nm LIKE <cfqueryparam cfsqltype="cf_sql_varchar"
value="%#VARIABLES.SearchTerm#%"/>
OR f.field_logical_nm LIKE <cfqueryparam cfsqltype="cf_sql_varchar"
value="#VARIABLES.SearchTerm#%"/>
OR f.field_physical_nm LIKE <cfqueryparam cfsqltype="cf_sql_varchar"
value="%#VARIABLES.SearchTerm#"/>
OR f.field_physical_nm LIKE <cfqueryparam cfsqltype="cf_sql_varchar"
value="%#VARIABLES.SearchTerm#%"/>
OR f.field_physical_nm LIKE <cfqueryparam cfsqltype="cf_sql_varchar"
value="#VARIABLES.SearchTerm#%"/>
)
AND f.field_id NOT IN (
SELECT TOP #ARGUMENTS.StartRow# f.field_ID
FROM field f
WHERE 0=0
AND (
f.field_logical_nm LIKE <cfqueryparam
cfsqltype="cf_sql_varchar" value="%#VARIABLES.SearchTerm#"/>
OR f.field_logical_nm LIKE <cfqueryparam
cfsqltype="cf_sql_varchar" value="%#VARIABLES.SearchTerm#%"/>
OR f.field_logical_nm LIKE <cfqueryparam
cfsqltype="cf_sql_varchar" value="#VARIABLES.SearchTerm#%"/>
OR f.field_physical_nm LIKE <cfqueryparam
cfsqltype="cf_sql_varchar" value="%#VARIABLES.SearchTerm#"/>
OR f.field_physical_nm LIKE <cfqueryparam
cfsqltype="cf_sql_varchar" value="%#VARIABLES.SearchTerm#%"/>
OR f.field_physical_nm LIKE <cfqueryparam
cfsqltype="cf_sql_varchar" value="#VARIABLES.SearchTerm#%"/>
)
ORDER BY f.field_logical_nm
)
ORDER BY f.field_logical_nm
</cfquery>
This post wasn't meant to be a tutorial, more of a 'Hey ummm this is the way I
solved this problem, but somehow it feels really really dirty like kissing my
cousin at a Sadie Hawkins dance after spiking the punch' kind of post. I would
really like to know if this is the correct way to fix the problem and is this
the way that <cfqueryparam> is truly suppose to work.
And for those of you wondering what this query is doing and why I would do
something this cluggie, well the query itself is apart of a records pagination
resolution for a client that is running IE 6. It was some code I saw on a blog
[can't remember which one] that I modified for my own evil purposes.
Cheers
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327711
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
