Quotes issue when passing variables to SQL

[Pattycake245]

I have a cold fusion program that has a couple of calls to SQL. One is to add data the other is to add/modify. Both calls pass a "comments" variable from a form field. The add program may pass a comment like "it's a test". When this gets passed to SQL it goes through fine. If I use the same comment in the add/modify call it errors out saying that there is unclosed quotes, error occurs at "s". I am using cfquery to run both of these SQL stored procs. My senior and I can't figure out for the life of us why this would error out on one and not the other. Just wondering if anyone else has had this happen before.

Tim

 

[TruthInSatire]

some code would be nice. coldfusion usualy escapes the quotes for you automatically but if it isn't you could replace ' with '' to do it manualy.

If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett

 

[Pattycake245]

Sorry, I should know better. I've just stumbled accross one difference between the two. One comment variable is a form field, well actually they both are, but the variable for the second is created through using evaluate.

1st comment:

<cfquery name="sched_shift_insert"
datasource="#sDSNGlobal#"
username="#form.user_id#"
password="#request.FieldTRSpassword#">
exec sched_shift_insert '#scentre#',
#intv_no#,
'#AddDate#',
'#AddStart#',
'#AddStop#',
#continuous_indc#,
'#form.user_id#',
#add_reliability#,
'#AddComments#'

</cfquery>

In the second I currently use replace to replace the single quote with a #Chr(039)# and it works. But here is the old code:

<cfquery name="sched_shift_update"
datasource="#sDSNGlobal#"
username="#form.user_id#"
password="#request.FieldTRSpassword#">
exec sched_shift_update_tp '#scentre#',
#intv_no#,
'#updDate#',
'#updStart#',
'#updStop#',
'#Evaluate("comments_#i#")#',
#Evaluate("reliability_#i#")#,
#Evaluate("old_reliability_#i#")#,
'',
'Y', 'N',
'#form.user_id#'
</cfquery>

Tim

 

[dadrobson]

For consistency, reliability, flexibility, and lots of other good reasons, you should switch from cfquery to cfstoredproc. And, as bombboy suggests, it would be a good idea to use cf's Replace() function to escape any single quotes in text fields.

blog.robsondesign.com

 

[Pattycake245]

Thanks guys, I just answered my own question with my last post. I guess with evaluate, you have to escape the quotes as it won't be done automatically. Can you use Preservesinglequotes to do this as well, or is replace the only option?

Tim

 

[TruthInSatire]

That's exactly right, the auto escaping doesn't work when you use the evaluate function. I've gotten used to using cfset just before the actual field. then use the new variable name.

..
..
..
<cfset fieldName = "comments_" & i>
'#fieldName#',
..
..
..

cfstoredproc is good in place of exec like you're doing here.
however unless you're grasping for every ounce of efficiency in your system, using cfquery and keeping all of your code in the same place is just as nice.

If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett

 

[TruthInSatire]

oops skipped over the preservesinglequotes question.

the PSQ function is used to keep CF from doign the auto escape. if you did the escaping manualy before hand you'd need to use PSQ to it doesn't escape the escaped quotes, if you didn't have the evaluate problem... it's getting confusing i know.

if you do like i suggested above by setting the name of the form field in a variable before you use it, and NOT use the evaluate function, cf will do the auto escaping and solve the problem.

If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett

 

[Pattycake245]

I see what you're saying bombboy, but I need the evaluate in there in order to get the comment. From your suggestion above:

<cfset fieldName = "comments_" & i>
'#fieldName#',

would produce the value comments_20050214, where i is obviously a date, not "it's a test". I think I'll stop thinking about this route and just use the replace.

Tim

 

[TruthInSatire]

er oops

<cfset newField= #Evaluate("comments_"&i)#>
'#newField#',

If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett