4 Replies Latest reply: Mar 13, 2012 12:07 PM by -==cfSearching==- RSS

    CFC SQL help request

    earwig75 Community Member

      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>

        • 1. Re: CFC SQL help request
          Dan Bracuk Community Member

          Probably because of this:

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

          • 2. Re: CFC SQL help request
            -==cfSearching==- Community Member

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

            • 3. Re: CFC SQL help request
              earwig75 Community Member

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

              • 4. Re: CFC SQL help request
                -==cfSearching==- Community Member

                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