sql building, apostrophe escaping, behavior diff from CF6.1 & CF8

[dakota81]

Quick Example:
<cfquery datasource="..." name="...">
SELECT * FROM table WHERE field1='#variable#'
</cfquery>

Let's say variable = "foo'bar"

In CF6.1 and CF8, the query passed to the database is:
SELECT * FROM table WHERE field1='foo''bar'


If variable = "foo''bar"

In CF6.1, the query passed to the database is:
SELECT * FROM table WHERE field1='foo''''bar'

In CF8, the query is:
SELECT * FROM table WHERE field1='foo''bar'



If I can, I would prefer ColdFusion 8 to mimic the behavior of ColdFusion 6.1 so I don't have to go back and check all my past work now that my server has been upgraded. Is this even possible? Thanks.

 

[dakota81]

To add, I know I can update the script to be:
<cfquery datasource="..." name="...">
SELECT * FROM table WHERE field1='#Replace(variable,"''","''''","ALL")#'
</cfquery>

But I'd hope I wouldn't have to on each query.

 

[okiiyama]

What about using: cfqueryparam?

select * 
from table 
where field1 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#var#">

I don't think you'll need to worry about apostrophes or double quotes in the variable then.