Single quote in a query within cfscript on CFMX7 is being replaced

[VZBCF]

I just recently upgraded from CF5 to CFMX7. The following code worked perfect in CF5 with no problems but no longer works in CFMX7. At the bottom you will see that my query value that has a single quote around it, is now being returned with 2 single quotes in place of one. The code is filled with additional functions, I removed them and left the relevant data. Any help would be greatly appreciated.

<cfscript>
.
.
Map.Request.WorkDesc = StructNew();
Map.Request.WorkDesc.FieldName = "work_desc";
Map.Request.WorkDesc.NoteId = "RLW3";
.
.

{a couple of functions in this area}

function GetGenericRequestEvent_CreateTheSQLStatement (NoteTable, ItemTable, ItemName, ItemOperation, ItemValue, FieldName, NoteId) {
var theSqlStatement = "";

theSqlStatement = "


SELECT
DISTINCT(n.note_id),
n.text,
n.row_index
FROM "
& NoteTable & " n, "
& ItemTable & " i
WHERE
i." & ItemName & ItemOperation & ItemValue & "
AND n.request_id = i.request_id
AND i." & FieldName & " = n.note_id
AND n.note_index = '" & NoteId & "'
ORDER BY
n.row_index";

return(theSqlStatement);
}
</cfscript>

<!---here is what runs in the above sql query. The single quote is not being replaced with 2 single quotes in MX7. This code works fine in CF5---->

SELECT DISTINCT (n.note_id), n.text, n.row_index
FROM note_req n, request_log i
WHERE i.request_id = 989849
AND n.request_id = i.request_id
AND i.work_desc = n.note_id
AND n.note_index = ''RLW3''
ORDER BY n.row_index

 

[pigsie]

use the preserveSingleQuote function - not sure whats changed since cf5 but I do know there was a bug where cf would not always escape the single quote in CF5

 

[pigsie]

sorry I should have been clearer. CF automatically escapes single quotes when in a query - IIRC there was a bug in earlier versions where if you used a variable to contain the string, it would *not* automatically escape the single quote which may be why it used to work

 

[VZBCF]

I've played around with preserveSingleQuotes but could not get it to work. Where exactly should I put the preserveSingleQuotes?

 

[TamedTech]

I think from memory you use it like this;

#preserveSingleQuotes(fieldname)#

I've only ever used it the once but from memory thats how it goes.

Rob

 

[imstillatwork]

the quotes / single quotes are NOT coming from the variable, though, that's what his problem is. if it was quotes in the variable, this would be a no brainer.

for some reason, cf is outputing

"somthing'" & NoteId & "' something"

as

something "noteidvalue" something

instead of

something 'noteidvalue' something


HAVE you tried

AND n.note_index = ''" & NoteId & "''

??

Kevin

http://www.lot-o-nothin.com/cfml/

 

[pigsie]

VZBCF, looking at your code - your function takes a number of arguments and uses them to build up a sql statement - I'm assuming that the statement is then executed within cfquery?

<cfquery name=".." datasource=".." >
#GetGenericRequestEvent_CreateTheSQLStatement(....)#
</cfquery>

or

<cfset somevar = GetGenericRequestEvent_CreateTheSQLStatement(....) />

<cfquery name=".." datasource=".." >
#somevar#
</cfquery>

is that correct or are you doing something else...?