Skip navigation
earwig75
Currently Being Moderated

CFC SQL help request

Mar 12, 2012 9:33 AM

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>

 
Replies
  • Currently Being Moderated
    Mar 12, 2012 12:45 PM   in reply to earwig75

    Probably because of this:

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

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 12, 2012 1:53 PM   in reply to Dan Bracuk

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

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 13, 2012 12:07 PM   in reply to earwig75

    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

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points