double single quote problem in cfquery

[Bigio]

Hi, i've this problem with ColdFusion MX 6.1 Updater and MX 7 If i try to
insert a text containing a single quote (for example TEST') all works fine. But
if try to insert a text containing 2 single quote (for example TEST'' only one
quote was stored in the DB and the result is TEST' With 3 single quotes
(TEST''') the result was TEST' With 4 single quotes (TEST'''') two quoes was
stored (TEST'')
--------------------------------------------------------------------------------
--- Sql script for the table (SQL Server
--------------------------------------------------------------------------------
--- CREATE TABLE [dbo].[_TEST] ( [ID] [int] IDENTITY (1, 1) NOT NULL ,
[TESTO] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ) ON [PRIMARY] GO
ALTER TABLE [dbo].[_TEST] WITH NOCHECK ADD CONSTRAINT [PK__TEST] PRIMARY
KEY CLUSTERED ( [ID] ) ON [PRIMARY] GO INSERT INTO _TEST
(testo) values ('')
--------------------------------------------------------------------------------
---
--------------------------------------------------------------------------------
--- Sample code
--------------------------------------------------------------------------------
--- <cfif isdefined('form.btnsubmit')> <cfquery name='write'
datasource='dsource'> update _test set testo = '#form.altext#'
</cfquery> </cfif> <cfquery name='read' datasource='dsource'> select top
1 testo from _test </cfquery> <cfset thetext='SAM'''''PLE'> <cfif read.testo gt
''> <cfset thetext=read.testo> </cfif> <form action='test.cfm' method='post'
name='test' id='test'> <textarea cols='80' rows='5' name='altext'
id='altext'><cfoutput>#htmleditformat(thetext)#</cfoutput></textarea> <input
type='submit' name='btnsubmit' id='btnsubmit' value='ok'> </form>
--------------------------------------------------------------------------------
----- If you run the page and click Ok you will see the single quotas decrese
at any click. This is different from Cold Fusion 5 or earlier behaviour!!!
Thanks Regards

[Bigio]

Hi again...
No one can help me?

[Mike Greider]

It's because the quotes are excaping themselves. You can write a regular expression to replace a single quote with two single quotes. Then the correct number of quotes will be stored in the DB.

[jonwrob]

Better yet, try <cfqueryparam> which replaces the string with a bind variable.

JR

[Bigio]

Originally posted by: jonwrob
Better yet, try <cfqueryparam> which replaces the string with a bind variable.

JR

Thanks.
The problem is that we have thousands of query to "update" with cfqueryparan...
We don't understand the reason of this changing from CF 5 to CF MX...

Regards

[Mike Greider]

Would this hot fix apply?

[url]http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=tn_19108[/url]

If you are patched up through the latest updater than you shouldn't need to
apply this. However, if you haven't applied any updates to a virgin
installation, you may want to look into these. Even so, I'm still not 100% sure
I know what the issue is. It seems like CF is behaving correctly. If you want
to insert single quotes into the database, you have to escape them, usually by
adding another single quote. If you want to store 3 single quotes in the
database, than your insert must contain 6, and so on. AFAIK, most languages
work this way - certainly PHP.

[Bigio]

Originally posted by: Mike Greider
Would this hot fix apply?

[url]http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=tn_19108[/url]

If you are patched up through the latest updater than you shouldn't need to
apply this. However, if you haven't applied any updates to a virgin
installation, you may want to look into these. Even so, I'm still not 100% sure
I know what the issue is. It seems like CF is behaving correctly. If you want
to insert single quotes into the database, you have to escape them, usually by
adding another single quote. If you want to store 3 single quotes in the
database, than your insert must contain 6, and so on. AFAIK, most languages
work this way - certainly PHP.


Hi,
these patch is for MX 6.1 version, but i've upgraded it to MX 6.1 upgrader.
I've tried to install it, but it didn't solve the problem.

Thanks again.

[tzimmerman]

The behavior has changed in CFMX and will be documented:
CF5 behavior - all single-quotes are escaped by changing them into two
single-quotes. Even in a loop, this only gets done once.

CFMX behavior - pairs of single-quotes are considered to be already escaped.
Only un-paired single-quotes get escaped into pairs of single-quotes.

Using cfqueryparam is probably the best way to insure that the single quotes
get inserted into the database.

Ted Zimmerman