FanaticZine

Today I was trying this ColdFusion Code, which derived me crazy for about hour, finally I found the solution for the problem. I’ll post this code, so if anybody go through the same, to make it little easy on them. ok here I go..

in my script I used a SQL query looks something like this,

<cfset strSQL=“SELECT * FROM TestTable WHERE test_id=‘xx’”>

<cfquery datasource=“xxxxxx” name=“xxxxxxx” result=“xxxxx” >
#strSQL#
</cfquery>

If you try something like that, it should should pop you a SQL error, if you look the query closely, you would notice that the CFQUERY executes the following command, which we passed in to it.

SELECT * FROM TestTable WHERE test_id=“xx”

you should see that there is a differance between command which get executed and with the one passed in. Means it converts ” ‘ ” character in to ” “ ” this would pop a sql error,

The Reason for this is, CF has it’s automated character escape feature, if you send the above code, CF DOES NOT ESCAPE those characters, But CF Escape any character which are queted with single-quotes,

so the simple solution for the problem is to tell the CF to escape the special characters which are in the command, so this is how we doing to do it.

<cfset strSQL=“SELECT * FROM TestTable WHERE test_id=‘xx’”>

<cfquery datasource=“xxxxxx” name=“xxxxxxx” result=“xxxxx” >
# ‘#strSQL#’ #
</cfquery>

Problem solved, your query should work now..