CFQuery where param

Question

In my CF component, I tried to filter data from user input (getSearchString) and run the code, I having problem with WHERE function. Can suggestion what is the correct way?

<cffunction name="getParks" access="remote" returntype="struct">
<cfargument name="page" required="true" />
<cfargument name="pageSize" required="true" />
<cfargument name="gridsortcolumn" required="true" />
<cfargument name="gridsortdirection" required="true" />
<cfargument name="getSearchString" default="" />

<cfif arguments.gridsortcolumn eq "">
    <cfset arguments.gridsortcolumn = "parkName" />
    <cfset arguments.gridsortdirection = "asc" />
</cfif>

<cfquery name="parks" datasource="cfdocexamples">
    select      parkName, parkType, city, state
    from        parks
    where       <cfqueryPARAM value = "#getSearchString#" CFSQLType = "CF_SQL_VARCHAR">
    order by    #arguments.gridsortcolumn# #arguments.gridsortdirection#
</cfquery>

<cfreturn queryconvertforgrid(parks, page, pagesize) />

Answer 1

It seems to be a simple sql bug. The field which you would like to compare with your searchstring is missing.

Should rather be:

<cfquery name="parks" datasource="cfdocexamples">
    select      parkName, parkType, city, state
    from        parks
    where       parkName = <cfqueryPARAM value = "#getSearchString#" CFSQLType = "CF_SQL_VARCHAR">
    order by    #arguments.gridsortcolumn# #arguments.gridsortdirection#
</cfquery>

Answer 2

acctually not around param but around getSearchString: WHERE parkName LIKE ... "%#getSearchString#%" ... But beware of performance issue with LIKE, also if you have large number of entries, dataGrid doesn't do real paging. Full blown solution depends on your database type.