Skip navigation
Beals
Currently Being Moderated

How to stop CFQUERYPARAM killing requests from search Results & legitmate bots crawling the site?

Sep 1, 2009 12:53 PM

A while ago I converted most queries to use cfqueryaparam to guard against injection attacks, among other things.  However I have recently noticed a slew of cfqueryparam generated errors specifically where a user clicks a URL from a search engine result set or when a crawler bot visits the site.


The function in questions is a straight query in a CFC with the two param beign checked before they get in against CFARGUMENT data types


<cfargument name="editionID" type="numeric" required="no" default="0" hint="Specifying edition ID will in most cases return a back issue">

<cfargument name="publicationID" type="numeric" required="yes">


<cfquery>
        SELECT *
        FROM articles a
            INNER JOIN sections s on a.sectionID = s.sectionID
            INNER JOIN edition e on a.editionID = e.editionID
            INNER JOIN publications p on e.publicationID = p.publicationID
        WHERE p.publicationID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.publicationID#"> AND e.editionID =  <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.editionID#"></cfif>
        ORDER BY  a.isLead desc, a.leadPosition, a.sectionID
    </cfquery>

---

Looking at the error reports, the query string is coming it exactly as it should (e.g., Query: action=3&articleID=1756&editionID=176 -- publication id is set in the request scope; article ID is used as filter), however CF fails to validate "176" as an integer, and the whole thing fails.

 

I take off the cfqueryparam, and the exact same link which didn't work, does.

 

Any insight would be appreciated,

 

Jason.

 
Replies
  • Currently Being Moderated
    Sep 1, 2009 1:51 PM   in reply to Beals

    Is the value of the erroring field "176" or "176[with trailing whitespace here]"?  This is probably what the issue is.

     

    It's interesting that <cfargument> is passing it OK as a numeric, but <cfqueryparam> isn't seeing it as an int.  Not that numeric => integer necessarily, obviously.

     

    What does isValid("integer", arguments.editionID) give you?

     

    --

    Adam

     
    |
    Mark as:
  • Currently Being Moderated
    Sep 2, 2009 12:56 AM   in reply to Adam Cameron.
    176[with trailing whitespace here]

    Or l76, beginning with small L. Also have another look at how you pass URL.editionID as function parameter.

     
    |
    Mark as:
  • Currently Being Moderated
    Sep 2, 2009 1:06 AM   in reply to Beals

    Also, what happens when you change the order to, for example, action=3&editionID=176&articleID=1756?

     
    |
    Mark as:
  • Currently Being Moderated
    Sep 3, 2009 7:21 AM   in reply to Beals

    This is a weird issue that I've never seen before, but I'll offer a theory.  It is possible that the CFARGUMENT tag, which has a type 'numeric', is converting the integer 176 to a real number which CFQUERYPARAM does not recognize as an integer.  You might try converting your numeric argument values to integers when passing them to CFQUERYPARAM.

    Example:
    <cfqueryparam cfsqltype="cf_sql_integer" value="#Int(arguments.publicationID)#">

    I'm curious to see if this helps.  Please post any new information to forum.

     
    |
    Mark as:
  • Currently Being Moderated
    Sep 3, 2009 1:41 PM   in reply to Beals
    I am still mystified by the strange behavior that would let an integer with whitepace get validated successfully by cfargument with a numeric parameter type and NOT by cfqueryparam with an int parameter type.

    You shouldn't be too surprised. The function IsNumeric ignores the whitespace before and after.

     
    |
    Mark as:
  • Currently Being Moderated
    Sep 3, 2009 2:37 PM   in reply to Beals

    It occasionally seems to me that CF's development over the years has been in such a way that the left hand doesn't talk to the right hand, or even that the left hand didn't know there was a right hand to even talk to(*).  So similar things sometimes seem to work slightly differently (for example the difference in type validations between <cfpara> and <cfargument>).

     

    So <cfargument> and <cfqueryparam> validations being different doesn't surprise me too much.

     

    That said, is it the <cfqueryparam> validation failing, or is it the eventual DB call failing?  What's the exact error you were getting?

     

    --

    Adam

     

    (*) talking hands.  Weird.

     
    |
    Mark as:
  • Currently Being Moderated
    Sep 4, 2009 7:37 AM   in reply to Beals

    It appears that the problem lies with what ColdFusion treats as a numeric value.  The IsNumeric documentation says "Determines whether a string can be converted to a numeric value.".  The devil is in the details, "can be converted".  Javascript's parseFloat function will treat '135   ' as a numeric value because the string starts with a number.  Any non-numeric content after the number is ignored.  I suspect that CF's designers did the same so that CF would behave similary to Javascript.

     

    You can work around this with some custom validation.   Here is a sample user defined function you could use.  I assume you are using Microsoft SQL Server.

     

    <cffunction name="IsMsSqlInt" returntype="boolean" access="public" output="no" hint="Checks input to see if it is a valid MS SQL integer">
        <cfargument name="target" type="string" required="yes" hint="input to check" />

     

        <cfset var local=StructNew() />

     

        <cfset local.returnValue = false />

     

        <!--- does input match regular expression pattern for MS SQL integers, expression adapted from http://www.regular-expressions.info/floatingpoint.html  --->
        <cfif ReFind("^[-]{0,1}[0-9]{1,10}$", arguments.target) eq 1>
                <!--- is value within range of MS SQL INT datatype? http://msdn.microsoft.com/en-us/library/ms187745.aspx --->
                <cfif Val(arguments.target) gte -2147483648 and Val(arguments.target) lte 2147483647>
                    <cfset local.returnValue = true />
                </cfif>   
        </cfif>

     

        <cfreturn local.returnValue />

     

    </cffunction>

     

     

     


    ColdFusion IsNumeric
    http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=func tions_in-k_37.html#4996076

     

    Javascript parseFloat
    https://developer.mozilla.org/en/Core_JavaScript_1.5_Reference/Global_ Functions/parseFloat

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 9, 2012 10:37 AM   in reply to Beals

    I know that this post is a few years old, but this issue has been incorrectly solved here.

     

    The cause of this problem revolves around CF's cached SQL.  The correct solution is to explicitly name the selected columns instead of using SELECT *.

     

    Tip of the hat to Ben Nadel: http://www.bennadel.com/blog/194-ColdFusion-Query-Error-Value-Can-Not- Be-Converted-To-Requested-Type.htm

     
    |
    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