Preventing a string from escaping single quotes 1

[jwdcfdeveloper]

I need a way to keep single quotes in a form field without blowing up when the value is passed to an Oracle database. Here is an example:

'#trim(evaluate("form.stringValue_"&i))#'.

The value is part of a loop of dynamically generated form names. I use another loop to get the "i" value to make the stringValue value dynamic. However, I have found that if someone adds a single quote in the stringValue (i.e. stringValue = "We're great"), the stringValue causes errors, because it thinks that the single quote between "We" and "re" is another value, and gives me a missing comma error. I tried adding PerserveSingleQuotes:
(e.g. #PreserveSingleQuotes(trim(evaluate("form.stringValue_"&i)))#),

but CF does not like that with this type of string. Anyone have any ideas how I can keep the single quotes in the stringValue variable?

 

[TruthInSatire]

at that point you wouldn't want to preserve the single quote escaping it will produce "we''re great" in the query which will insert "we're great" into the DB.

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)

 

[jwdcfdeveloper]

I'm not sure if I understoode your post. I need to insert 'We're great' into the database. The <b>We'</b> part of the string is causing a db error, what can I do to get around this?

 

[webmigit]

What database are you using?

Try this..

'#Replace(trim(evaluate("form.stringValue_"&i)),"'","''","ALL")#'.

...or...

'#Replace(trim(evaluate("form.stringValue_"&i)),"'","[red]& #[/red]39;","ALL")#'.

Remove the space between the red & and #.. I placed the space so it wasn't translated by the browser and you saw what I intended.

Try the second solution first, as its possible that using the first solution would put double apostroophes in your literal data.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[jwdcfdeveloper]

Thanks webmidgit the second one did the trick.

 

[elpanther]

You may want to look at HTMLEditFormat or HTMLCodeFormat. One of those should do the trick for you.

"The difference between genius and stupidity is genuis has a limit!

 

[imstillatwork]

I know this is old, but I always use htmleditformat(preservesinglequotes()) as a UDF with a shorter name(!) for MySQL. It keeps single quotes single, and line breaks are still in place.

display text from db like replace(query.column,chr(10),"<br />","ALL")

(again, from a UDF that is much shorter to type!)

 

[webmigit]

I think there's more to UDFs than just making shorter names.. I'd imagine too many UDFs might be bad..

I'm sure UDFs are stored like client variables and thus taking up space. And I imagine it takes CF extra effort to call a UDF.. you know, when you call a function, cf probably says "Is it a built in function? No, is it a UDF? Yes, ok call the UDF."

Code however you like, but it doesn't sound like a UDF to combine two basic functions into one might be all that great, especially when you consider that inside your UDF, the two other functions are still being called.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.