Member Avatar for stevenfagan

I'm using ASP to dynamically generate an XML file (for an RSS feed) that pulls information from a SQL DB. Users input the data into the DB using a UI that is a straightforward html form. However, there is a textarea field in that form in which most users seem to paste text from Microsoft word (or some other microsoft product that uses the ms word character set i.e. ms outlook).

My problem is that when this form submits to the database the ms characters are saved in the db as the microsoft character set. When the ASP GETs the information from the DB it seems that it pulls the same microsoft characters that are stored in the db out (as you would expect it would). However, the browser cannot interpret these characters (maybe they are not UTF-8 compatible?) and in place of these characters SOME browsers put a "unidentifiable character" character symbol and other browsers have the xml file (rss feed) break entirely.

I am using MS SQL and there is an option to set a field as xml which requires all information stored in the field to be xml compatible. However, this field does not perform any type of implicit conversion. It spits back an error message to the user on the web page which is unacceptable feedback.

Turning "Paste" off in the textarea is not an option. Users must still retain the ability to paste from something that uses an MS character set.

The input would look something like this:

(in ms character set obviously)
Hello. You're the biggest foo-bar I know.

The output would look something like this:

Hello� You�re the biggest foo�bar I know�


Does anybody have any ideas as to how to address this problem?

Is there some type of converting function in SQL that will convert the microsoft character set into something UTF-8 compatible?
Or is there some way server side to convert the information in this field.

  • 2 Contributors
  • 1 Reply
  • 247 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by ArtistScope

Dani AI

Generated

— this is almost always an encoding/validation mismatch rather than “mystery characters.” As hinted, Word inserts Windows‑1252 “smart” punctuation and sometimes control/non‑breaking characters that look fine in the editor but become invalid when the bytes are interpreted with the wrong encoding or when placed into an XML column. The robust fix is twofold: make your pipeline Unicode-aware end‑to‑end, and strip/normalize characters that XML does not accept.

Practical checklist (in order of safety and impact):

  • Make pages and forms UTF‑8: send a UTF‑8 content header and use <form accept-charset="UTF-8"> (in Classic ASP set Response.Charset = "utf-8").
  • Store text in SQL Server NVARCHAR (or NVARCHAR(MAX)), not VARCHAR. If needed, add a new NVARCHAR column, copy converted data, verify, then swap. Example migration step: ALTER TABLE MyTable ADD MyColUnicode NVARCHAR(MAX); UPDATE MyTable SET MyColUnicode = CONVERT(NVARCHAR(MAX), MyColVarchar); (backup first).
  • Insert Unicode using Unicode parameters (ADO adVarWChar/adLongVarWChar) or prefix literals with N'...'.
  • When outputting RSS/XML include an XML declaration with UTF‑8 and escape XML special characters; invalid control codes (0x00–0x1F except 0x09,0x0A,0x0D) must be removed or the XML column will reject the value.

Server‑side normalization makes this painless for users. Example Classic ASP helpers — normalize common Word punctuation, remove disallowed control chars, then XML‑escape before emitting:

Function NormalizeSmartChars(s)
  If IsNull(s) Then NormalizeSmartChars = "" : Exit Function
  s = Replace(s, ChrW(&H2019), "'")
  s = Replace(s, ChrW(&H2018), "'")
  s = Replace(s, ChrW(&H201C), """")
  s = Replace(s, ChrW(&H201D), """")
  s = Replace(s, ChrW(&H2013), "-")
  s = Replace(s, ChrW(&H2014), "-")
  s = Replace(s, ChrW(&H2026), "...")
  s = Replace(s, ChrW(&HA0), " ")
  NormalizeSmartChars = s
End Function

Function CleanForXml(s)
  Dim i,ch,code,out: out = ""
  For i = 1 To Len(s)
    ch = Mid(s,i,1): code = AscW(ch)
    If code = 9 Or code = 10 Or code = 13 Or code >= 32 Then out = out & ch
  Next
  CleanForXml = out
End Function

Function XmlEscape(s)
  s = Replace(s,"&","&amp;")
  s = Replace(s,"<","&lt;")
  s = Replace(s,">","&gt;")
  s = Replace(s,"""","&quot;")
  s = Replace(s,"'","&apos;")
  XmlEscape = s
End Function

Use XmlEscape(NormalizeSmartChars(CleanForXml(userText))) before inserting into XML fields or emitting RSS. Test by checking HTTP charset headers and validating the RSS with an XML validator; backup and test migrations on a copy of the DB.

Member Avatar for ArtistScope

Does the textarea support rich text, or is it the standard textarea. If it's rich text then you may be getting MSWord font formatting which isn't noticed until checking in code view. If it's a standard text area then some characters like apostrophes and other punctuation marks can be non standard and not supported in other applications. For example I have seen Word produce at least 3 different characters for an apostrophe.

When providing textareas it's always good practice to filter the input anyway, otherwise some nasty code can be sent to the database that will break the function and expose the database to attack.

Edited by ArtistScope because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.