1

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

Pete
  • 4,542
  • 9
  • 43
  • 76

1 Answers1

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?

Peter Boughton
  • 110,170
  • 32
  • 120
  • 176
  • Does that explain why the queries return different results with and without cfqueryparam, though? – Sixten Otto Oct 27 '09 at 18:07
  • Peter, why did you edit the cfqueryparam tags out of his question? – Sixten Otto Oct 28 '09 at 06:45
  • Running the query "SELECT id FROM table WHERE 1=0" just returns 0 rows. As I stated the only problem is when CFQUERYPARAM tags are used. What was your edit Peter? As all it seemed to do was hide the CFQUERYPARAM tags in the question – Pete Oct 28 '09 at 08:16
  • Sorry, I'm don't know what happened to those cfqueryparam tags... it wasn't deliberate! Must have been a SO or browser bug. The edit I was referring to was to my answer - the last paragraph. StackOverflow has a design feature where edits within a certain period don't leave an audit trail, so there is no indication that I added the final paragraph afterwards. – Peter Boughton Oct 28 '09 at 12:45
  • As for the problem itself... it does seem odd. Comcar, can you update the question with the MySQL table definition (i.e. `SHOW CREATE TABLE `), and confirm what CF Admin Datasource settings you are using? – Peter Boughton Oct 28 '09 at 12:47
  • Dammit I forgot to mention this in the first place... this is why I was querying about the "+" in the URL, if I remove it, it works. – Pete Oct 29 '09 at 07:43