cfqueryparam to escape quotes and apostrophes 2

[kensington43]

I have major problems with quotes or apostropes copied from either a Word document or a web browser giving me box characters on my Cold Fusion MX 6.1 web pages using Access 2000 database after my Updates or Inserts.

I can either use the ReReplace function to eliminate the characters or use the cfqueryparam function to escape the quotes and apostropes or something called PreservesingleQuotes.

This didnt work because the quotes and apostropes still dont translate right and give me boxes:

  for (i = 128; i LTE 160; i = i + 1) 
     { 
          Text = Replace(Text, Chr(i), "", "All"); 
     }

Please advise what is the most efficient way and if I am doing this right using the cfqueryparam.
Are there any side effects to using cfqueryparam because in the last two years of CF web pages I unfortunately never used it.

<cfquery name="insert_record" datasource="myDSN"> 
     INSERT into tablename ( 
          textareaField, 
          column2, 
          column3 
     VALUES ( 
          <cfqueryparam value="#dynamic_value_1#" cfsqltype="cf_sql_char">, 
          '#FORM.column2#',
          '#FORM.column3#'
 ) 
</cfquery>

 

[ecobb]

Try this:

<cfquery name="insert_record" datasource="myDSN"> 
     INSERT into tablename ( 
          textareaField
     VALUES ( 
          <cfqueryparam value="#PreservesingleQuotes(dynamic_value_1)#" cfsqltype="cf_sql_char">
 ) 
</cfquery>

Are there any side effects to using cfqueryparam...
No, in all honesty you SHOULD use cfqueryparam in ALL queries that pass a dynamic value. It boosts performance and gives an added layer of datatype verification and security. The only thing better is Stored Procedures.



Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[imstillatwork]

read this about the characters your copying.

http://www.alistapart.com/stories/emen/

 

[kensington43]

I did the following for my update statement:

<cfquery name="update_record" datasource="myDSN"> 
update tablename 
set 
textareaField =  
<cfqueryparam value="#PreservesingleQuotes(FORM.textareaField)#" cfsqltype="cf_sql_char">
where
id = #FORM.id#
</cfquery>

And it doesnt work when if I copy Microsoft Word quotes and apostrophes. It gives me an error:

[ODBC Microsoft Access Driver]Invalid Precision value

I also tried it without PreserveSingleQuotes and it still gave me the same error.

It does work if I dont copy Microsoft Word quotes and apoostrophes or if I type in the information in the form.
Please advise.

 

[ecobb]

Change the datatype to Memo in Access, and you may want to change cfsqltype to "cf_sql_varchar" in your cfqueryparam tag.



Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[kensington43]

thanks,

I tried as you suggested and it still gives me same error.
Any other suggestions?

 

[imstillatwork]

did you read the link I sent? Someone else posted this week with a very similar question regarding single qoutes, apostrophes, etc while pasting from word, and there are explainations for it in that link.

 

[kensington43]

Thanks for all the guidance!

I ended up using this in a function:
Text = Replace(Text, Chr(28), """", "All");
Text = Replace(Text, Chr(29), """", "All");

The Ascii Chr(28) replaced the copy value of Microsoft Word forward quotes And the Ascii Chr(29) replaced the copy value of Microsoft Word backward quotes.

The translations got rid of the weird characters on my Cold Fusion page.

Is this something you all have seen before or is this just an issue with my CF front end and Access 2000 Back end?

The msdn site shows Ascii chr(28) and chr(29) as blank chars so I am lost how this is working but glad it solved this problem. I am assuming I will have to do more translations like this for different chars in the future if people copy and paste data to my web pages.

Any comments you can advise on what is happening?

 

[imstillatwork]

It's an issue with the characters that MS Word Internet Explorer (MS...) and some other applications use.

 

[imstillatwork]

that is, MS Word, and Internet Explorer

 

[kensington43]

Thanks,

So I assume anyone building CF MX 6.1 pages using Access 2000 is having same issue I am having and they are solving the problem similiar to how I am doing it?

Thanks if you can advise how you and other developers are dealing with this issue.

 

[imstillatwork]

no, It's a problem with the text your copying in to the form, not a access or cf issue. someone else posted about this not too long ago, and I beleive that they found a solution.

 

[kensington43]

Okay, thanks I will try and find that post to find a solution.

Every user who copies Microsoft Word (using Office 2000 and Office 2003 software) text to a form at my place has the same result with the weird characters showing up.