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

[Beals]

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.

[Adam Cameron. · Accepted solution]

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

[BKBK]

176[with trailing whitespace here]

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

[Beals]

Adam,

Thanks for the suggestion.  I encapuslated all cfqueryparam vars in trim() and the problem [seems to have] stopped. I'm going to do more testing with putting the trim() function on the outside of the cfc, but at least I know that works.

While it fixes the issue at hand, 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.  I would think it should not pass cfargument.  Oh well.

Regards,

Jason

[BKBK]

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.

[Adam Cameron.]

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.

[Beals]

Add the isNumeric validation and now there are 3 hands. sigh.

The actual error was:

Diagnostic: Error Executing Database Query. [SQLServer JDBC Driver]Value can not be converted to requested type.

Jason.

[BKBK]

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

[JR__Bob__Dobbs-qSBHQ2]

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.

[JR__Bob__Dobbs-qSBHQ2]

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=functions_in-k_37.html#4996076

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

[Guest]

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