3

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.

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
rjzii
  • 14,236
  • 12
  • 79
  • 119
  • 2
    _"I suspect something is being mangled in the URL some how."_ - so what's an example query string then? (After any re-writing has occurred.) – Peter Boughton Dec 02 '13 at 13:10
  • 2
    If you cfdump the query, with metadata=yes, do the parameters have the same values you used in sqlplus? – Dan Bracuk Dec 02 '13 at 13:14
  • @PeterBoughton, Dan Brauck - Updated with some more information. – rjzii Dec 02 '13 at 13:36
  • 3
    given that your example values are both integers, is your `cfsqltype="CF_SQL_CHAR"` correct? – duncan Dec 02 '13 at 13:50
  • Are you really linking to your application.cfm file or did you just type a random name into your question? – Dan Bracuk Dec 02 '13 at 13:55
  • @DanBracuk Bah, no, it's the index.cfm, the application isn't in front of me right now so I just threw a random name on there. – rjzii Dec 02 '13 at 13:59
  • @duncan The database type is `CHAR(2)` for compNo and `CHAR(3)` for prodNo. Remember that everything is working fine when the values are posted to `FORM` but not when passed via `URL`. – rjzii Dec 02 '13 at 14:01
  • Since it's a char(2) field, spaces will be relevant - i.e. `1` is different to `1 ` - so maybe the form is submitting the latter? Add `%20` (i.e. url encoded space) to the end of the URL to check if that's the issue. – Peter Boughton Dec 02 '13 at 14:15
  • @PeterBoughton I just looked at the source and the hidden form is showing the value as "1 " so it looks like that is what is going on. Any idea how to force the %20 into URL? `URLEncodedFormat` comes to mind but I'm not sure if it will behave as I want it to. – rjzii Dec 02 '13 at 14:38
  • UrlEncodedFormat should work (and should generally be used any time you're outputting content to a URL), but since this is confirmed as the issue I'd go with switching to varchar (assuming you can update the db), and either trimming all existing values and/or trimming variables when compared. – Peter Boughton Dec 02 '13 at 15:00
  • 1
    A workaround (if you can't change the db), would be to pad the spaces when you compare - i.e. `left(url.whatever & ' ',2)` - but this is definitely not a preferred solution. – Peter Boughton Dec 02 '13 at 15:03
  • @PeterBoughton Updates to the database are pretty much a no-go (legacy) so I'm thinking manually padding the values may be needed. – rjzii Dec 02 '13 at 15:06

1 Answers1

0

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.

Snipe656
  • 845
  • 7
  • 15