Skip to main content
T
thuelhorst
Participating Frequently
April 8, 2010
Answered

Referencing url variable in cfquery that contains # sign

  • April 8, 2010
  • 2 replies
  • 2564 views

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

    This topic has been closed for replies.
    Correct answer ilssac

    How do I make the necessary correction in my code?


    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.

    2 replies

    ilssac
    ilssac
    Inspiring
    April 8, 2010

    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.

    ilssac
    ilssac
    Inspiring
    April 8, 2010

    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.

    T
    thuelhorstAuthor
    Participating Frequently
    April 8, 2010

    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
    ilssac
    Inspiring
    April 8, 2010

    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


    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?

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices