?
Solved

Escaping/protecting special string characters

Posted on 2003-03-24
17
Medium Priority
?
524 Views
Last Modified: 2013-12-20
One of the fields in my database contains data with many special characters like single and double quotes and number(#) signs.  Right now, those characters appear as a square on the screen.  I know I need to replace them with a " for example, so I thought I could use something like the Replace or ReplaceList functions but I'm not sure how to tell the machine what it's looking for.  Short of doing the physical replace in the actual database, what can I do?
0
Comment
Question by:MCDery
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +2
17 Comments
 

Expert Comment

by:demarco
ID: 8196440


REReplaceNoCase (string, '[^a-z0-9]', '', 'all')

:)
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8196819
the chars may be appearing bcos u r having a different langauge setting / these characters r not supported by ur locale

change in the locale settings may help u eradicate the problem [if u wish to]

K'Rgds
Anand
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8196850
the chars may be appearing bcos u r having a different langauge setting / these characters r not supported by ur locale

change in the locale settings may help u eradicate the problem [if u wish to]

K'Rgds
Anand
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 11

Expert Comment

by:hart
ID: 8201163
Anands correct, in oracle if you have different locale settings and then try to view inserted data then boxes are shown.

so check your settings or use replace before inserting into the database and also while viewing.

Regards
Hart(Harish)
0
 

Expert Comment

by:demarco
ID: 8202096
yup hart/Anad thats why - I just assumed he wanted to know how to remove ..  :)

- Must Remember golden rule .. dont assume :)
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8208902
hello ???
0
 

Author Comment

by:MCDery
ID: 8210253
Hey guys, sorry for the delay, I got caught up in something else.  I'll try out your suggestions today and will let you know how I fare.
0
 

Author Comment

by:MCDery
ID: 8211498
Hey guys, sorry for the delay, I got caught up in something else.  I'll try out your suggestions today and will let you know how I fare.
0
 

Author Comment

by:MCDery
ID: 8213176
OK, here's another stupid question... how do I change the locale settings?  (Can you tell I'm new at this?:)
0
 
LVL 11

Expert Comment

by:hart
ID: 8215499
before changing anything which database are u using

Oracle, Sql, Access ???

Regards
Hart(Harish)
0
 
LVL 10

Expert Comment

by:substand
ID: 8216042
stop pasting the data in from programs like microsoft word.  or else paste them into notepad and then paste them into the field that goes into the database.  that will fix your problem.  

you may also want to check your locale settings, but likely this will not fix the problem entirely, as those squares will still show up using the english locale on the browser, computer, and db server if you are first copying from msword.

if not using programs other than text editors to input text (or to type and then cut and paste), is not an option, you might consider putting a meta tag in the document (you .cfm file) to force the browser to display in unicode english.

this is almost certainly your problem.



0
 

Author Comment

by:MCDery
ID: 8217819
Well, the database is Access, and it's true that the data was pasted in from a Word document.  Eventually we are looking at creating a data entry form for the client to enter their own information, though they will most likely be copy/pasting anyways.

I tried your suggestion about pasting into notepad before pasting the data in the database, but that didn't work.  The problem is with things like the “smart” left and right quotes, the curved apostrophe, the ellipsis, etc.

Our environment is bilingual French/English and everyone has different settings on their computer and browser.

So I'm now looking at your meta tag suggestion - what goes into it?  - Like I said, I'm new at this!
0
 

Author Comment

by:MCDery
ID: 8270921
Here's what my current file's meta tag contains:  
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

Is this the one you refer to in the previous comment?  This isn't working - what should it say?
0
 
LVL 10

Accepted Solution

by:
substand earned 600 total points
ID: 8273699
I thought that might work (forcing that as you did).  Perhaps it is the wrong charset, but I believe that is the correct one.  (I'd have to look it up)

I have the same problems in my webdev company.  we develop the website and teach the customer minimal html so they can just paste in thier code with <b>'s and <i>'s and <br>'s and it will display in a template we've created... (a simple view of it)

We noticed the same problem you are having, and I propose fixing it in one of 2 ways:

1) when I said paste it into notepad, I neglected to mention to save it.  You will need to save it as a text file, (in normal ascii) to get it to work correctly.

2) Alternatively, if you can implement this, you might want to consider having whoever is pasting in the data to instead upload a .DOC file to the sever, and then convert the DOC to an html file, read the file, and then input that file into the DB.

If you want to implement #2, here's how I would go about it:

a) on the form page, have:

<form ...all your normal attributes... enctype="multipart/form-data">

<input type="file" name="uploadedfile">

b) on the submit page have:

<!---this uploads the file--->
<CFFILE ACTION="Upload" FILEFIELD="uploadedfile" DESTINATION="full_path_name" NAMECONFLICT="overwrite"     ACCEPT="application/msword"
>

<!--- this part from cfcomet.com (i only found the 8 myself) will save as html ---->

<!--- Try to connect to the Word application object --->
<CFTRY>
    <!--- If it exists, connect to it --->
    <CFOBJECT
        ACTION="CONNECT"
        CLASS="Word.Application"
        NAME="objWord"
        TYPE="COM">
  <CFCATCH>
  <!--- The object doesn't exist, so create it --->
        <CFTRY>
            <CFOBJECT
                ACTION="CREATE"
                CLASS="Word.Application"
                NAME="objWord"
                TYPE="COM">
          <!--- Word isn't installed, or ColdFusion doesn't have access to it --->
          <CFCATCH TYPE="Object">
              <FONT COLOR="RED">Cannot create Word Object<BR>
              Make sure Word is installed and that ColdFusion has permissions to
              use the Word COM objects.</FONT>
              <CFABORT>
          </CFCATCH>
        </CFTRY>
  </CFCATCH>
</CFTRY>

The following code will open an HTML file and convert it to a Word document. You can use the "SaveAs()"
method of the "Documents" collection to convert the HTML file to various other formats as well -- HTML
is only one of the options.

<CFSCRIPT>
    // HTML file path
    HTMLFilePath = "d:\inetpub\wwwroot\myFile.html";
   
    // Destination path for new Word document
    // We leave off the extension since we'll declare the file type later on
    WordFilePath = "d:\inetpub\wwwroot\myFile";
   
    // Open Word in the background
    objWord.Visible = false;  
   
    // Disable alerts such as: 'Save this document?'
    objWord.DisplayAlerts = false;              
   
    // Get the 'Documents' collection
    objDoc = objWord.Documents;  
   
    // Open the HTML document
    newDoc = objDoc.open(HTMLFilePath);              
   
    /* Save it as a new Word document -- the extension will automatically
       be appended based on the file type we choose.
       Some of the file types to convert to:
       1 = .dot
       2 = .txt
       6 = .rtf
       9 = .asc
      10 = .ans
      11 = .doc

      8 = html
    */
    newDoc.SaveAs(WordFilePath,Val(11));    
     
    // The next line would convert it to RTF instead
    // newDoc.SaveAs(WordFilePath,Val(8));  
     
    // Close the document
    newDoc.Close();
   
    // Quit Word
    objWord.Quit();
</CFSCRIPT>

 <!--- end convert the doc to html --->

<!--- read the file into var_name --->
<CFFILE ACTION="Read"
    FILE="full_path_name"
    VARIABLE="var_name">

<!--- insert the variable into the db--->
<cfquery name="ins" datasource="datasource">
insert into tablename... etc....
</cfquery>



<!--- delete the doc and html files --->

<CFFILE ACTION="Delete"
    FILE="full_path_name">





0
 
LVL 10

Expert Comment

by:substand
ID: 8273707
hope that helps... its probably best to do the doc2html conversion, since it will allow your customers to be basically ignorant of everything.
0
 

Author Comment

by:MCDery
ID: 8283634
Thanks for all your efforts.  My deadline was last Friday so I ended up doing just a Find/Replace in the database itself.  Temporary solution.  When I get to phase two next month where the user will be entering their own info into the database, I will try your file conversion solution.  If I have problems then, I'll post another question so you can get more points.  Bye for now!
0
 
LVL 10

Expert Comment

by:substand
ID: 8286828
ok, i'll be glad to help.

0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses
Course of the Month12 days, 4 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question