Referencing url variable in cfquery that contains # sign

[thuelhorst]

I am using CF 9 and cannot figure out how to reference a url variable in my cfquery that contains a "#" character as part of the string name (i.e. http://somepage.cfm?variable_name=tom#h.

When I try to execute the query, it is only pulling "tom" due to the # sign.  I tried to double up the # sign (##) but that didn't work either.

<cfquery NAME="qryGraph"
DATASOURCE="DB2"
USERNAME=#request.username#
PASSWORD=#request.password#>

SELECT DATE

FROM TABLE
WHERE NAME = '#variable_name#'  

</cfquery>

Any help would be appreciated!

Tom

[ilssac]

Where did you try to double up the pound sign?

You may need to use string replacment on your variable to double the pound sign inside the <cfquery...> block.

[thuelhorst]

I doubled up the pound sign within the url. (i.e. http://somepage.cfm?variable_name=tom##h).  It still only recognized "tom".

Tom

[ilssac]

Have you tried something like:

WHERE Name = '#replace(variable_name,"##","####","ALL")#'

My feeling is that this rather awkword.... but I'm not sure why your code is not working as it is.

[thuelhorst]

No luck with the replace function:

url:

http://127.0.0.1:8500/test/test.cfm?plan_name=AP##BATCH

<cfquery NAME="qryGraph1"

DATASOURCE="DB2P"

USERNAME=#request.username#

PASSWORD=#request.password#>

SELECT CLASS1_CPU_TOTAL

FROM DB2PDBA.DBT_DB2PM_REPORTING

WHERE CORRNAME = 'APK085'

AND PLAN_NAME = '#replace(plan_name,"##","####","ALL")#'

AND DATE = '2010-03-30'

</cfquery>

<cfquery NAME="qryGraph2"

DATASOURCE="DB2P"

USERNAME=#request.username#

PASSWORD=#request.password#>

SELECT CLASS1_CPU_TOTAL

FROM DB2PDBA.DBT_DB2PM_REPORTING

WHERE CORRNAME = 'APK085'

AND PLAN_NAME = 'AP##BATCH'

AND DATE = '2010-03-30'

</cfquery>

qryGraph1 did not return any data.

qryGraph2 returned the expected sum when I hard coded the value with two pound signs.

Message was edited by: thuelhorst

[ilssac]

You would not use both a double # in the URL and the replace function.  If you did you would end up with four #### in your stirng.

Speaking of which are you capturing the SQL statement that is being generated?

[thuelhorst]

I have tried various combinations but SQL capture always truncates after the pound sign:

SELECT CLASS1_CPU_TOTAL
FROM DB2PDBA.DBT_DB2PM_REPORTING
WHERE CORRNAME = 'APK085'
AND PLAN_NAME = 'AP'
AND DATE = '2010-03-30'

[ilssac]

Have you confirmed that it is getting to the server with the fill string?

<cfoutput>#url.variable_name#</cfoutput>

[thuelhorst]

Regardless of whether I the variable name has one or two pound signs, the value gets truncated off after the #.

value for url.variable_name: "AP#BATCH"

<cfouptput #url.variable_name#> results in "AP"

value for url.variable_name: "AP##BATCH"

<cfouptput #url.variable_name#> results in "AP"

[ilssac]

Oh the HELL D'OH.

It is the browser.  A hash mark in a browser is an Anchor!

You need to be escaping the hash mark in the url.

IIRC that would be %23

[thuelhorst]

How do I make the necessary correction in my code?

[ilssac · Accepted solution]

http://somepage.cfm?variable_name=tom%23h.

And it should work with no other modifications.

If you are building this code in your CFML you can use the urlEncodedFormat() function.

[thuelhorst]

Thanks Ian!!!

[ilssac]

P.S.

I hope you are planning to use serious SQL injection protections from code like this that uses input from a user via the URL query string.