cfquery not returning results when mulitple cfqueryparam are used

Question

The following cfquery is failing in ColdFusion when the multiple cfqueryparams are used; however, when they are not used or only limited to one or the other the query returns the expected results:

<cfquery name="getComponent"
 datasource="#Request.DSN#"
 username="#Request.username#"
 password="#Request.password#">
  SELECT *
  FROM tbComponent
    INNER JOIN tbPart ON tbPart.partNo = tbComponent.partNo
    INNER JOIN tbProduct on tbProduct.prodNo = tbComponent.prodNo
  WHERE tbComponent.prodNo = <cfqueryparam value="#URL.prodNo#"
                              cfsqltype="CF_SQL_CHAR"
                              maxlength="3" />
    AND tbComponent.compNo = <cfqueryparam value="#URL.compNo#"
                              cfsqltype="CF_SQL_CHAR"
                              maxlength="2" />
</cfquery>

The data is passed in via the URL which looks like the following:

http://localhost/index.cfm?prodNo=100&compNo=1

The query has been tested in SQLPlus with valid data and returns the expected results as well. This issue appears to be limited to when the values are passed via URL though as passing them via post and FORM works correctly so I suspect something is being mangled in the URL some how. The data and query has been checked with cfdump and everything looks correct against what is used to run the query in SQLPlus.

The database back-end is Oracle 11g and to the best of my knowledge, all of the relevant drivers are up to date. Any idea as to what might be going on here as it seems the obvious problems have been checked already.

Answer 1

I'd put in Oracle's LPAD function so that when you pass in just one or two lengthed characters it will put in the proper spacing before them since your URL is lacking the %20s. Something like this:

<cfquery name="getComponent"
 datasource="#Request.DSN#"
 username="#Request.username#"
 password="#Request.password#">
  SELECT *
  FROM tbComponent
    INNER JOIN tbPart ON tbPart.partNo = tbComponent.partNo
    INNER JOIN tbProduct on tbProduct.prodNo = tbComponent.prodNo
  WHERE tbComponent.prodNo = LPAD(<cfqueryparam value="#URL.prodNo#"
                              cfsqltype="CF_SQL_CHAR"
                              maxlength="3" />, 3)
    AND tbComponent.compNo = LPAD(<cfqueryparam value="#URL.compNo#"
                              cfsqltype="CF_SQL_CHAR"
                              maxlength="2" />, 2)
</cfquery>

That will then pad things, when needed and up to the max character length for the columns. You could change them to 2 and 1 with the assumption at least single characters will always be passed in for both since I assume an assumption of at least some value exists since no null attribute with a check for it to be yes or no is present on each query param.