Skip to main content
E
earwig75
Known Participant
March 12, 2012
Question

CFC SQL help request

  • March 12, 2012
  • 1 reply
  • 974 views

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>

    This topic has been closed for replies.

    1 reply

    D
    Dan_Bracuk
    Inspiring
    March 12, 2012

    Probably because of this:

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

    -
    -__cfSearching__-
    Inspiring
    March 12, 2012

    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==-

    E
    earwig75Author
    Known Participant
    March 13, 2012

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

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices