经典 ASP、MySQL 或 ODBC UTF8 编码

2022-01-16 00:00:00 utf-8 character-encoding odbc asp-classic mysql

我有一个使用 GoDaddy 托管的网站，包括后端的 MySQL 数据库.该站点是斯洛文尼亚站点，因此使用了特殊字符.

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.

该网站是用经典的 ASP 构建的，我在 Notepad++ 中创建了所有使用 utf-8 编码的页面.在每一页的顶部，我还有 Session.CodePage=65001、Session.LCID=1060 和 Response.Charset="utf-8".MySQL db 和所有的表也是 utf8 编码的.

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.

如果我通过Workbench界面直接查看db中的数据，一切正常，包括我使用的一些特殊的斯洛文尼亚字符，比如:č

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 č

唯一的问题是，在同一页面上，从 MySQL 检索的数据编码不正确，所以字母 č 变成了?

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?

首先我以为是 MySQL ODBC 3.51 驱动程序，我用它来连接数据库.我尝试将 charset=utf8 添加到连接字符串，但没有奏效.我也尝试将 charset=ucs2 添加到连接字符串，这是我在另一个网站上找到的提示，但它也没有帮助.GoDaddy 不支持 MySQL ODBC 5.1 驱动程序，这可能是一个解决方案.

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.

推荐答案

你有机会根据这个 映射和摘自 Windows-1252 维基文章:

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

根据 Microsoft 和 Unicode Consortium 网站上的信息，位置 81、8D、8F、90 和 9D 未使用；但是，Windows APIMultiByteToWideChar 将这些映射到相应的 C1 控制代码.

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.

位置 80 的欧元字符在此代码页的早期版本中不存在，S、s、Z 和 z 也没有带 caron (háček).

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. 使用 UTF-8(无 BOM)编码的文件以防止包含硬编码文本的可能性.(✔已经完成)

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

在服务器端使用 ASP 或在客户端使用元标记为响应字符集指定 UTF-8.(✔已经完成)

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

告诉 MySQL 服务器你的命令是 utf-8 字符集，并且你期望 utf-8 编码的结果集.将初始语句添加到连接字符串:...;stmt=SET NAMES 'utf8';...

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';...

将 Response.CodePage 设置为 1252.

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

平均售价:

<%@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>

最后一句话:

当您需要对从数据库中获取的字符串应用 html 编码时，您不应再使用 Server.HTMLEncode，因为 Response.Codepage 在服务器端是 1252，并且由于 Server.HTMLEncode 是依赖上下文代码页，这将导致乱码输出.
因此，您需要编写自己的 html 编码器来处理这种情况.

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, "&", "&amp;")
    str = Replace(str, "<", "&lt;")
    str = Replace(str, ">", "&gt;")
    str = Replace(str, """", "&quot;")
    MyOwnHTMLEncode = str
End Function
'Response.Write MyOwnHTMLEncode(rs("myfield").value)

相关文章