Classic ASP, MySQL or ODBC UTF8 encoding

Question

I have a website hosted with GoDaddy, including MySQL database on the back end. 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++ where utf-8 encoding is used. At the top of every page I also have Session.CodePage=65001, Session.LCID=1060 and Response.Charset="utf-8". MySQL db and all the tables are also utf8 encoded.

If I look at the data directly in db through Workbench interface, everything is ok, including some special Slovenian characters I use, like: č

If I go to my website, the Slovenian characters are also printed just fine, including č

The only problem is, that on the same page, data retrived from MySQL is not coded correctly, so letter č becommes ?

What could be the problem and how to solve it?

First I thought it is the MySQL ODBC 3.51 Driver, which I use to connect to db. I have tried adding charset=utf8 to the connection string, but didn't work. I have also tried adding charset=ucs2 to the connection string, which is a tip I found on another website, but it didn't help either. GoDaddy is not supporting MySQL ODBC 5.1 Driver, which could be a solution.

I am running out of options, so please help.

Solution

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

According to the information on Microsoft's and the Unicode Consortium's websites, positions 81, 8D, 8F, 90, and 9D are unused; however, the Windows API MultiByteToWideChar maps these to the corresponding C1 control codes.

The euro character at position 80 was not present in earlier versions of this code page, nor were the S, s, Z, and z with caron (háček).

Here's the things to do:

1. Use UTF-8 (without BOM) encoded files against the possibility of contain hard-coded text. (✔ already done)

2. Specify UTF-8 for response charset with ASP on server-side or with meta tags on client-side. (✔ already done)

3. Tell the MySQL Server your commands are in charset utf-8, and you expect utf-8 encoded result sets. Add an initial statement to the connection string : ...;stmt=SET NAMES 'utf8';...

4. Set the Response.CodePage to 1252.

I've 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 last remark:

When you need to apply html encoding to strings fetched from the database, you shouldn't use Server.HTMLEncode anymore due to Response.Codepage is 1252 on server-side and since Server.HTMLEncode is dependent context codepage this will cause gibberish outputs.
So you'll need to write your own html encoder to handle the case.

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