Solved

Replacing special characters for storage in SQL and then output to browser

Posted on 2011-03-22
8
592 Views
Last Modified: 2012-05-11
I have an html form with which the user is creating a title and description of an item. This data is then stored in MS SQL and later retrieved for display in the browser.

The problem is that some users enter names with special characters, such as Spanish names (eg. Pérez or Migüel etc.). There are even instances of users entering open or closed quotes rather than the standard straight quotation marks (probably from cut & pasting from a Word Doc I would guess).

In any event all of these special characters are showing up in the DB exactly as they are entered, but when rendered in the browser they are being displayed as a funky question mark symbol (¿).

I do not want to spend the time writing a replace function to account for all the possible variations of special characters.

How best to handle this?  Does anyone have a ready-made classic ASP function or RegExp that I can use to convert the characters to html for storage or some other solution please?

Needs to be in Classic ASP.  Thanks.
0
Comment
Question by:worthyking1
8 Comments
 
LVL 2

Expert Comment

by:rhawk
ID: 35194279
Are you storing them as a VARCHAR or NVARCHAR? That MAY be the issue.
0
 
LVL 11

Expert Comment

by:G_H
ID: 35194830
Try this function (NOT TESTED) but lurking in my library...

GH

 
Function CleanDodgyChars(str)
	' str = Replace(str, "<", "&lt;")
	' str = Replace(str, ">", "&gt;")
	' str = Replace(str, "&", "&amp;")
	' str = Replace(str, """", "&quot;")
	' str = Replace(str, " ", "&nbsp;")
	str = "" & str
	str = Replace(str, "©", "&copy;")
	str = Replace(str, "Ç", "&Ccedil;")
	str = Replace(str, "ç", "&ccdil;")
	str = Replace(str, "Ñ", "&Ntilde;")
	str = Replace(str, "ñ", "&ntilde;")
	str = Replace(str, "Þ", "&THORN;")
	str = Replace(str, "þ", "&thorn;")
	str = Replace(str, "Ý", "&Yacute;")
	str = Replace(str, "ý", "&yacute;")
	str = Replace(str, "ÿ", "&yuml;")
	str = Replace(str, "ß", "&szlig;")
	str = Replace(str, "Æ", "&AElig;")
	str = Replace(str, "Á", "&Aacute;")
	str = Replace(str, "Â", "&Acirc;")
	str = Replace(str, "À", "&Agrave;")
	str = Replace(str, "Å", "&Aring;")
	str = Replace(str, "Ã", "&Atilde;")
	str = Replace(str, "Ä", "&Auml;")
	str = Replace(str, "æ", "&aelig;")
	str = Replace(str, "á", "&aacute;")
	str = Replace(str, "â", "&acirc;")
	str = Replace(str, "à", "&agrave;")
	str = Replace(str, "å", "&aring;")
	str = Replace(str, "ã", "&atilde;")
	str = Replace(str, "ä", "&auml;")
	str = Replace(str, "Ð", "&ETH;")
	str = Replace(str, "É", "&Eacute;")
	str = Replace(str, "Ê", "&Ecirc;")
	str = Replace(str, "È", "&Egrave;")
	str = Replace(str, "Ë", "&Euml;")
	str = Replace(str, "ð", "&eth;")
	str = Replace(str, "é", "&eacute;")
	str = Replace(str, "ê", "&ecirc;")
	str = Replace(str, "è", "&egrave;")
	str = Replace(str, "ë", "&euml;")
	str = Replace(str, "Í", "&Iacute;")
	str = Replace(str, "Î", "&Icirc;")
	str = Replace(str, "Ì", "&Igrave;")
	str = Replace(str, "Ï", "&Iuml;")
	str = Replace(str, "í", "&iacute;")
	str = Replace(str, "î", "&icirc;")
	str = Replace(str, "ì", "&igrave;")
	str = Replace(str, "ï", "&iuml;")
	str = Replace(str, "Ó", "&Oacute;")
	str = Replace(str, "Ô", "&Ocirc;")
	str = Replace(str, "Ò", "&Ograve;")
	str = Replace(str, "Ø", "&Oslash;")
	str = Replace(str, "Õ", "&Otilde;")
	str = Replace(str, "Ö", "&Ouml;")
	str = Replace(str, "ó", "&oacute;")
	str = Replace(str, "ô", "&ocirc;")
	str = Replace(str, "ò", "&ograve;")
	str = Replace(str, "ø", "&oslash;")
	str = Replace(str, "õ", "&otilde;")
	str = Replace(str, "ö", "&ouml;")
	str = Replace(str, "Ú", "&Uacute;")
	str = Replace(str, "Û", "&Ucirc;")
	str = Replace(str, "Ù", "&Ugrave;")
	str = Replace(str, "Ü", "&Uuml;")
	str = Replace(str, "ú", "&uacute;")
	str = Replace(str, "û", "&ucirc;")
	str = Replace(str, "ù", "&ugrave;")
	str = Replace(str, "ü", "&uuml;")
	str = Replace(str, "®", "&reg;")
	str = Replace(str, "±", "&plusmn;")
	str = Replace(str, "µ", "&micro;")
	str = Replace(str, "¶", "&para;")
	str = Replace(str, "·", "&middot;")
	str = Replace(str, "¢", "&cent;")
	str = Replace(str, "£", "&pound;")
	str = Replace(str, "¥", "&yen;")
	str = Replace(str, "¼", "&frac14;")
	str = Replace(str, "½", "&frac12;")
	str = Replace(str, "¾", "&frac34;")
	str = Replace(str, "¹", "&sup1;")
	str = Replace(str, "²", "&sup2;")
	str = Replace(str, "³", "&sup3;")
	str = Replace(str, "¿", "&iquest;")
	str = Replace(str, "°", "&deg;")
	str = Replace(str, "¦", "&brvbar;")
	str = Replace(str, "§", "&sect;")
	str = Replace(str, "«", "&laquo;")
	str = Replace(str, "»", "&raquo;")
	CleanDodgyChars = str
End Function

Open in new window

0
 
LVL 6

Author Comment

by:worthyking1
ID: 35194982
@rhawk the DB field is NVARCHAR
0
 
LVL 2

Expert Comment

by:rhawk
ID: 35195492
My next thought is that your content type on the page is either not set or wrong. Try each of these and see if one work:

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

<meta http-equiv="Content-Type" content="text/html; charset=utf-8">

<meta http-equiv="Content-Type" content="text/html; charset=windows-1251">


My thought is that the first and/or second will work. Let me know.

Also, can you point me to your web page so I can see it and view the source?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 6

Author Comment

by:worthyking1
ID: 35197757
Currently the page has UTF-8 content type set.  Here's a sample page: http://dizzy.com/aibu/catalog_class.asp?clid=139
0
 
LVL 2

Expert Comment

by:rhawk
ID: 35198305
Okay, I found 2 web sites that will help a lot. One of them IF you have to encode the characters to HTML special characters and the other talks about the special characters in web pages.

http://www.alanwood.net/unicode/unicode_samples.html
http://www.utexas.edu/learn/html/spchar.html
0
 
LVL 2

Expert Comment

by:rhawk
ID: 35198380
Reading more of one of those links... What font and/or font family is the CSS setting the page to? Try one that is friendly to the special characters. We KNOW that this page on Experts-Exchange allowed you to enter "Pérez or Migüel" so we KNOW they are using a font or family that allows it. Try lucida sans and see if that does it.
0
 
LVL 1

Accepted Solution

by:
MikeK70 earned 500 total points
ID: 35225903
1. Make sure the .asp file is saved in UTF-8 encoding
2. Make sure you have the code page for asp set correctly, your first line should read: <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
3. Make sure you have the encoding set correctly in the http header: <meta http-equiv="Content-Type" content="text/html; charset=utf-8">

With these 3 steps, no further HTML encoding of the characters should be necessary.

Cheers,

Mike
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now