Help with single quote please!

[mega_L]

I'm doing a simple varchar insert :

Insert into tablename (column1, column2, etc) Values (' #value1# ', ' #value2# ', etc)

How to avoid value with single quote such as: ' L'Enfant Plaza '

[Joshua_Cyr]

Use CFQueryparam.  Always use cfquerypram in your queries.

<cfqueryparam value="#value1#" cfsqltype="CF_SQL_VARCHAR" /> 

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_18.html

[BKBK]

Cfqueryparam is preferable. However, an alternative way to preserve single quiotes is:

Insert into tablename (column1, column2, etc) Values (' #preserveSingleQuotes(value1)# ', ' #preserveSingleQuotes(value2)# ', etc)

[Dan_Bracuk]

PreserveSingleQuotes didn't work when I tried it.

[BKBK]

Sorry about that, Dan. I overlooked a step, the escaping.

<!-- escape single quote character, for example, by replacing it with 2 single quotes --->

<cfset value1= replace(value1,"'","''","all")>

<cfset value2= replace(value2,"'","''","all")>

Insert into tablename (column1, column2, etc) Values (' #preserveSingleQuotes(value1)# ', ' #preserveSingleQuotes(value2)# ', etc)

In any case, I am surprised Mega L. got that problem. I expected Coldfusion to automatically escape the single quotes within cfquery.

[TLC-IT]

The dual advantage of <cfqueryparam> is that:  (1) any text can be inserted, and (2) the text will never be misunderstood as "part of the SQL" no matter what it contains.

The database engine will receive an SQL string with parameter-placeholders in it, which it will parse.  Then, separately, it is given a list of the values to be substituted for each of those parameters, which it will do.