<cfquery>

[Guest]

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'

[draves]

Did you look at preservesinglequotes() ?

[Guest]

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.

[BreakawayPaul]

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.

[Guest]

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

[draves]

Did you try moving the pound signs around the function?

#preservesinglequotes(SQL)#

[TLC-IT]

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.