Escaping/protecting special string characters

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?
MCDeryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

demarcoCommented:


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

:)
0
anandkpCommented:
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
anandkpCommented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

hartCommented:
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
demarcoCommented:
yup hart/Anad thats why - I just assumed he wanted to know how to remove ..  :)

- Must Remember golden rule .. dont assume :)
0
anandkpCommented:
hello ???
0
MCDeryAuthor Commented:
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
MCDeryAuthor Commented:
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
MCDeryAuthor Commented:
OK, here's another stupid question... how do I change the locale settings?  (Can you tell I'm new at this?:)
0
hartCommented:
before changing anything which database are u using

Oracle, Sql, Access ???

Regards
Hart(Harish)
0
substandCommented:
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
MCDeryAuthor Commented:
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
MCDeryAuthor Commented:
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
substandCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
substandCommented:
hope that helps... its probably best to do the doc2html conversion, since it will allow your customers to be basically ignorant of everything.
0
MCDeryAuthor Commented:
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
substandCommented:
ok, i'll be glad to help.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.