CFQUERYPARAM breaking with "+" in URL

Question

I have a query:

  SELECT id FROM table WHERE field1=<cfqueryparam value="#URL.field1#" 
    cfsqltype="cf_sql_varchar">
  AND field2=<cfqueryparam value="#URL.field2#" 
    cfsqltype="cf_sql_varchar">
  AND field3=<cfqueryparam value="#URL.field3#" 
    cfsqltype="cf_sql_varchar">;

Id is an INTEGER in MySQL, but the above query returns an Id that has not come from the table, and is not even an INTEGER, it seems to be a random BIGINT! If I remove the CFQUERYPARAM it works though...

SELECT id FROM table WHERE field1='#URL.field1#' 
        AND field2='#URL.field2#'
        AND field3='#URL.field3#';

Field1, Field2 and Field3 are all VARCHARS in the Database, but in the URL they contain "+", eg Field1=text+moretext

If I remove the "+" from the URL strings, it works fine! I'm aware the "+" is a representation of a space, if I actually type a whitespace into the URL again it works fine. It's only breaking when there is a "+" present.

What have I done wrong? Or what do I do to make CFQUERYPARAM work with "+" from the URL

Answer 1

The Field=text+moretext in the url will come through to Url.Field1 as text moretext, because spaces in URLs can be encoded as pluses.

If you want an actual + sign in the final variable, use %2B, or apply the UrlEncodedFormat to the original link.

If this all works, then I guess the odd behaviour with the random ID may be related to the default database value when the where clause fails to match - try SELECT id FROM table WHERE 1=0 and see if you get the random ids?