Hey Brett,
The only things cfqueryparam does is pretty much escape single quotes
and bind the strings as parameters, preventing injection attacks. If
you want to replace underscores with spaces you will have to do it
manually (like you did ;)).
hth
Francois Lévesque on the road
On 2009-10-26, at 18:46, Brett Davis <d...@bsmuv.com> wrote:
>
> 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:327713
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
