title: Detecting bad UTF-8 encoding: list of bad characters to sniff?
body: <p>I have a sql-server 2010 database shared between two applications. One application we have control over, and the other application is a third-party app that created the database in the first place. Our app is a CRM built on top of the third party webmail app. </p>
  
  <p>The database contains varchar columns and is latin-1 encoded. The third-party app is written in php and doesn't care about correctly encoding the data, so it stuffs utf-8 encoded bytes into the varchar columns, where they are interpreted as latin-1 and look like garbage. </p>
  
  <p>Our CRM app is written in .Net, which automagically detects that the database collation is different the the encoding of the string in memory, so when .Net writes to a database, it converts the bytes to match the database encoding.</p>
  
  <p>So... data written to the db from our app looks correct in the db, but data from the third party app doesn't. </p>
  
  <p>when our app writes FirstName = Céline, it is stored in the db as Céline</p>
  
  <p>when the webmail app writes FirstName = Céline it is stored in the db as CÃ©line</p>
  
  <p>Our CRM app needs to display contacts that were created in either system. So I'm writing an EncodingSniffer class that looks for flagged characters that indicate its a poorly encoded string and converts them.</p>
  
  <p>currently I have:</p>
  
  <pre>
  private static string[] _flaggedChars = new string[] { 
              "Ã©" 
          };
  </pre>
  
  <p>which works great for displaying CÃ©line as Céline, but I need to add to the list.</p>
  
  <p>Does anyone know of a resource to get all the possible ways that utf-8 special chars could be interpreted as iso-8859-1?</p>
  
  <p>Thanks</p>
  
  <p><strong>
   Clarification:
  </strong>
  Since i am working in .Net. The string, when loaded into memory from the database, is converted to Unicode UTF-16. So, regardless of if it was encoded correctly in the database. It is now represented as UTF16 bytes. I need to be able to analyze thes UTF-16 bytes, and determine if they are screwed up due to utf-8 bytes being stuffed into an iso-8859-1 database.... clear as mud right?</p>
  
  <p>Here is what i have so far. It has cleaned up the display of most misencoded characters, but I am still having trouble with É
  for instance: Éric is stored in the db by webmail as Ã‰ric, but after detecting bad encoding and changing it back, it displays as �?ric
  Looking at a user who has 2500 contacts, hundreds of which had encoding issues, the É is the only thing that isn't displaying correctly...</p>
  
  <pre><code>public static Regex CreateRegex()
      {
          string specials = "ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö";
  
          List&lt;string&gt; flags = new List&lt;string&gt;();
          foreach (char c in specials)
          {
              string interpretedAsLatin1 = Encoding.GetEncoding("iso-8859-1").GetString(Encoding.UTF8.GetBytes(c.ToString())).Trim();//take the specials, treat them as utf-8, interpret them as latin-1
              if (interpretedAsLatin1.Length &gt; 0)//utf-8 chars made up of 2 bytes, interpreted as two single byte latin-1 chars.
                  flags.Add(interpretedAsLatin1);
          }
  
          string regex = string.Empty;
          foreach (string s in flags)
          {
              if (regex.Length &gt; 0)
                  regex += '|';
              regex += s;
          }
          return new Regex("(" + regex + ")");
      }
  
      public static string CheckUTF(string data)
      {
          Match match = CreateRegex().Match(data);
          if (match.Success)
              return Encoding.UTF8.GetString(Encoding.GetEncoding("iso-8859-1").GetBytes(data));//from iso-8859-1 (latin-1) to utf-8
          else
              return data;
      }
  </code></pre>
  
  <p>So: É is being converted to 195'Ã',8240'‰'</p>