Skip to main content
B
Beals
Participant
September 1, 2009
Answered

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

  • September 1, 2009
  • 5 replies
  • 2907 views

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.

This topic has been closed for replies.
Correct answer Adam Cameron.

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

5 replies

A
Anonymous
March 9, 2012

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

J
JR__Bob__Dobbs-qSBHQ2
Inspiring
September 4, 2009

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

J
JR__Bob__Dobbs-qSBHQ2
Inspiring
September 3, 2009

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.

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
September 2, 2009

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

A
Adam Cameron.Correct answer
Inspiring
September 1, 2009

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
Community Expert
BKBKCommunity Expert
Community Expert
September 2, 2009
176[with trailing whitespace here]

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

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