Skip to main content
A
Anonymous
June 6, 2009
Question

<cfquery>

  • June 6, 2009
  • 3 replies
  • 850 views

I have a problem with <cfquery>

I want to pass a variable with my query but its giving me a problem


I have this

<cfquery datasource="mssqlcf_modelprod" dbname="modelprod"  name="get_prememb">


        SET QUOTED_IDENTIFIER OFF
        SELECT *
        FROM precontract
        where name Like '#form.member#%'
        ORDER BY name
   
</cfquery>

And I need it to be

<CFSET SQL = "SELECT * FROM precontract WHERE name LIKE '%moises%' OR email LIKE '%mz%' OR phone LIKE '%954%'"> 

<cfquery datasource="mssqlcf_modelprod" dbname="modelprod"  name="get_prememb">



        SQL   
</cfquery>

Where the Query is a variable passes in.

I am getting a error that reads Invalid column name 'moises'

    This topic has been closed for replies.

    3 replies

    T
    TLC-IT
    Inspiring
    June 8, 2009

    You need to be using the <cfqueryparam> tag.

    Do not construct SQL queries by the blind and naiive concatenation of string values, especially not values obtained from the user himself.

    This tag will present the value as a parameter to the SQL query, magically providing the query-string with a "placeholder" in the right location and then separately providing the value corresponding to that placeholder.  In this way, the values presented by the user can never be misinterpreted as part of the SQL command itself.

    BreakawayPaul
    BreakawayPaul
    Inspiring
    June 7, 2009

    I'm a bit worried about how secure this will be.  If you're passing data from a form, you need to sanitize it somehow.  The best way to do that is by using <cfqueryparam>, but you can only use that inside a <cfquery>.  Make sure however you get this working, you incorporate some sort of method to cleanse the input from the form.

    As far as the original problem goes, it looks like the preservesinglequotes() is making it look for a column name with the quotes in it.  Hmmm.

    Try looking at your <cfoutput>preservesinglequotes(#SQL#) </cfoutput> outside the query and see exactly what's being passed.

    A
    Anonymous
    June 7, 2009

    Hi, and thanks for all your help

    this is what out puts when i do <cfoutput>preservesinglequotes(#SQL#) </cfoutput> out sude the <cfquery>

    preservesinglequotes(SELECT * FROM precontract WHERE name LIKE '%1%' OR email LIKE '%2%' OR phone LIKE '%3%' OR conID LIKE '%4%' )

    and i tested the quesry on the DB and its proper

    A
    Anonymous
    June 7, 2009

    Did you try moving the pound signs around the function?

    #preservesinglequotes(SQL)#

    A
    Anonymous
    June 7, 2009

    Did you look at preservesinglequotes() ?

    A
    Anonymous
    June 7, 2009

    I was looking into it

    now i did

    <cfquery datasource="mssqlcf_modelprod" dbname="modelprod"  name="get_prememb">
            <cfoutput>preservesinglequotes(#SQL#) </cfoutput>
       
    </cfquery>

    Error Executing Database Query.

    Invalid column name 'moi'.
    Resources:
    • Enable Robust Exception Information to provide greater detail about the source of errors. In the Administrator, click Debugging & Logging > Debugging Settings, and select the Robust Exception Information option.
    • Check the ColdFusion documentation to verify that you are using the correct syntax.
    • Search the Knowledge Base to find a solution to your problem.

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