CFC SQL help request

[earwig75]

Hello. I am using a CFGRID and I have a search box that filters the results. One database field is called "inactive". The grid should only populate with records where "inactive" is NULL but it shows all records anyway. It does filter out the inactive records but only when a search is run  Below is what I am using in my CFC. Could someone help and tell me what could be wrong? Thank you.

<cffunction name="myfunction" 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" required="true" />
<cfset var data="">

<cfquery name="getdata" datasource="#this.source#">
  SELECT  IDNumber, FirstField, SecondField, ThirdField, ForthField, Inactive
  FROM   masterTable
 
  WHERE Inactive is Null
 
  and lower(SecondField) like <cfqueryparam value="%#lcase(arguments.getSearchString)#%" cfsqltype="cf_sql_varchar">

  or lower(ThirdField) like <cfqueryparam value="%#lcase(arguments.getSearchString )#%" cfsqltype="cf_sql_varchar">

  <cfif len(arguments.gridSortColumn) and len(arguments.gridSortDirection)>
  order by #arguments.gridsortcolumn# #arguments.gridsortdirection#
  </cfif>
 
  </cfquery>
 
  <cfreturn queryConvertForGrid(getdata, arguments.page, arguments.pageSize)>
 
</cffunction>

[Dan_Bracuk]

Probably because of this:

or lower(ThirdField) like <cfqueryparam value="%#lcase(arguments.getSearchString )#%" cfsqltype="cf_sql_varchar">

[-__cfSearching__-]

You have a mix of AND/OR operators which creates ambiguity. (See operator precedence) For example, do you want records where:

       Inactive is Null  AND SecondField LIKE '%xxx%'  

             ... OR

       ThirdField like '%xxx%'

.. or do you want records where

       Inactive is Null 

           ... +AND+

       SecondField LIKE '%xxx%' OR ThirdField like '%xxx%'

?

You need to use parenthesis so the database processes the conditions in the correct order

  ie  WHERE  Inactive is Null  AND

                    ( SecondField LIKE '%xxx%'  OR ThirdField like '%xxx%' )

order by #arguments.gridsortcolumn# #arguments.gridsortdirection#

BTW: That is a sql injection risk. You should validate those values before using them in the sql.

Message was edited by: -==cfSearching==-

[earwig75]

cfSearching, I thought the cfqueryparam took care of the injection risk. Could you explain how I would validate them further? Thanks again.

[-__cfSearching__-]

It does, but only for simple values like strings or numbers. You cannot use it with object names (ie table or column names).

order by #arguments.gridsortcolumn# #arguments.gridsortdirection#

Passing data from the client side directly into sql is always a risk. Either value could potentially contain a malicious sql string and nothing in the code would stop it from executing. Granted exploiting it requires a little more savy than your typical where clause attack. But it is still possible. You should validate the column name and sort direction (ie only "asc" or "desc" allowed) before using those values within the sql.

-Leigh