Classic ASP, MySQL, or ODBC UTF8 Encoding

Question

I have a website with GoDaddy, including a MySQL database on the back. The site is a Slovenian site, so special characters are used.

The website is built in classic ASP, and I have all the pages created in Notepad ++ that use utf-8 encoding. At the top of each page, I also have Session.CodePage = 65001, Session.LCID = 1060 and Response.Charset = "utf-8". MySQL db and all tables are also encoded in utf8.

If I view data directly in db via the Workbench interface, everything is fine, including some special Slovenian characters that I use, for example: č

If I go to my website, Slovenian characters will also be printed just fine, including č

The only problem is that on the same page, the data extracted from MySQL is incorrectly encoded, so the letter č becommes?

What could be the problem and how to solve it?

At first I thought it was the MySQL ODBC 3.51 driver, which I use to connect to db. I tried adding charset = utf8 to the connection string, but did not work. I also tried adding charset = ucs2 to the connection string, which is a hint I found on another website, but that didn't help either. GoDaddy does not support MySQL ODBC 5.1 Driver, which may be a solution.

I'm running out of options, so please help.

Answer 1

You have a chance for Slovenian letters according to this mapping and excerpt from the Windows-1252 wiki article :

According to Microsoft and Unicode Consortium websites, items 81, 8D, 8F, 90, and 9D are not used; however, the Windows MultiByteToWideChar API maps them to the corresponding C1 control codes .

The euro symbol at position 80 was absent in earlier versions of this code page, as well as S, s, Z and z with karon (háček).

Here's what you need to do:

• Use UTF-8 encoded files (without specification) versus being able to contain hard-coded text. (✔ already done)

• Specify UTF-8 for the response character set with ASP on the server side or with meta tags on the client side. (✔ already done)

• Tell the MySQL server that your commands are in utf-8 encoding, and you expect utf-8 encoded datasets. Add the original statement to the connection string: ...;stmt=SET NAMES 'utf8';...

• Set Response.CodePage to 1252.

I tested the following script and it works like a charm.

DDL: http://sqlfiddle.com/#!8/c2c35/1

ASP:

 <%@Language=VBScript%> <% Option Explicit Response.CodePage = 1252 Response.LCID = 1060 Response.Charset = "utf-8" Const adCmdText = 1, adVarChar = 200, adParamInput = 1, adLockOptimistic = 3 Dim Connection Set Connection = Server.CreateObject("Adodb.Connection") Connection.Open "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=myDb;User=myUsr;Password=myPwd;stmt=SET NAMES 'utf8';" If Request.Form("name").Count = 1 And Len(Request.Form("name")) Then 'add new Dim rsAdd Set rsAdd = Server.CreateObject("Adodb.Recordset") rsAdd.Open "names", Connection, ,adLockOptimistic rsAdd.AddNew rsAdd("name").Value = Left(Request.Form("name"), 255) rsAdd.Update rsAdd.Close Set rsAdd = Nothing End If Dim Command Set Command = Server.CreateObject("Adodb.Command") Command.CommandType = adCmdText Command.CommandText = "Select name From `names` Order By id Desc" If Request.QueryString("name").Count = 1 And Len(Request.QueryString("name")) Then Command.CommandText = "Select name From `names` Where name = ? Order By id Desc" Command.Parameters.Append Command.CreateParameter(, adVarChar, adParamInput, 255, Left(Request.QueryString("name"), 255)) End If Set Command.ActiveConnection = Connection With Command.Execute While Not .Eof Response.Write "<a href=""?name=" & .Fields("name").Value & """>" & .Fields("name").Value & "</a><br />" .MoveNext Wend .Close End With Set Command.ActiveConnection = Nothing Set Command = Nothing Connection.Close %><hr /> <a href="?">SHOW ALL</a><hr /> <form method="post" action="<%=Request.ServerVariables("SCRIPT_NAME")%>"> Name : <input type="text" name="name" maxlength="255" /> <input type="submit" value="Add" /> </form> 

As a final note:

If you need to apply html encoding to strings retrieved from the database, you should no longer use Server.HTMLEncode due to Response.Codepage - 1252 on the server side, and since Server.HTMLEncode is a dependent contextual code page, this will cause gibberish exits.

Therefore, you need to write your own html encoder to handle this case.

 Function MyOwnHTMLEncode(ByVal str) str = Replace(str, "&", "&amp;") str = Replace(str, "<", "&lt;") str = Replace(str, ">", "&gt;") str = Replace(str, """", "&quot;") MyOwnHTMLEncode = str End Function 'Response.Write MyOwnHTMLEncode(rs("myfield").value)