Escaping characters in MySQL

[rolty]

CFMX works fine with MySQL database 99% of the time. However, when a field in
a query includes an apostrophe, CF is escaping it in the Windows SQL server
mode, ie by doubling the apostrophe. MySQL requires a backslash to achieve
this, and consequently any queries involving escaped characters are failing
although the data might actually tally. There must (surely) be a simple setup
for this but I cant find it, any helpers please?

[Jochem van Dieten - TMM]

rolty wrote:

> CFMX works fine with MySQL database 99% of the time. However, when a field in
> a query includes an apostrophe, CF is escaping it in the Windows SQL server
> mode, ie by doubling the apostrophe. MySQL requires a backslash to achieve
> this, and consequently any queries involving escaped characters are failing
> although the data might actually tally. There must (surely) be a simple setup
> for this but I cant find it, any helpers please?

Use cfqueryparam.

Jochem

--
Jochem van Dieten
Team Macromedia Volunteer for ColdFusion, beer and fun.

[rolty]

thanks Jochem
that works with regular queries, but I cant get it to work with query of queries.... still get the same problem there... perhaps there's a nice workaround you know for that?

[Jochem van Dieten - TMM]

rolty wrote:

>
> that works with regular queries, but I cant get it to work with query of queries.... still get the same problem there... perhaps there's a nice workaround you know for that?

Could you show an example?

Jochem

--
Jochem van Dieten
Team Macromedia Volunteer for ColdFusion, beer and fun.

[rolty]

sure! This is the master query: <cfquery name=master datasource=myDB> SELECT
EstablishmentDetails.RecordID, Name, EstType, EstTypeAlt, EstType2, EstType3,
Town, CountyName, EstablishmentDetails.description, website, email,
CountyDetails.Region AS Reg FROM CountyDetails INNER JOIN EstablishmentDetails
ON County = CountyDetails.RecordID OR County2 = CountyDetails.RecordID WHERE
List = 1 AND CountyDetails.RecordID = <cfqueryPARAM value = '#county#'
CFSQLType = 'CF_SQL_INTEGER' maxlength='3'> ORDER BY Name ;</cfquery> this
works fine. however when i query this query like: <cfquery name=QHotel
dbtype='query'> SELECT RecordID, Name, Town, description, website, email FROM
master WHERE Town = <cfqueryPARAM value = '#town#' CFSQLType =
'CF_SQL_VARCHAR'> AND (EstType = 1 OR EstTypeAlt = 1 OR EstType2 = 1 OR EstType
= 12) ;</cfquery> It doesnt return any rows, even if I know the data is there.
It works fine IF the 'town' field does not contain an apostrophe, it only
fails for towns like King's Lynn... have I got the CFSQLType wrong?

[Jochem van Dieten - TMM]

I'm sorry, I have not been able to reproduce this problem.

Jochem


--
Jochem van Dieten
Team Macromedia Volunteer for ColdFusion, beer and fun.