MySQL Bugs: #62728: ODBC Connector 5.1 utf8 stored incorrectly

Bug #62728	ODBC Connector 5.1 utf8 stored incorrectly
Submitted:	13 Oct 2011 22:59	Modified:	23 Apr 2024 10:07
Reporter:	Alon S	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1.8	OS:	Windows
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	ASP, odbc 5.1, utf8

Description:
In some cases utf8 data is not stored correctly in the database.

I have a varchar column and i'm using classic asp.
All table collation and server collations and charsets are utf8_general_ci

How to repeat:
I tried to input foreign language data in 4 ways as follows:
(the hard coded foreign language text in all examples is utf8 because the .asp file is saved in utf-8 encoding)

1. with recordset, without charset=utf8 parameter

    SQL = "SELECT 1 FROM test WHERE 1=0"
    Set rs1 = CreateObject ("ADODB.Recordset")
   
    rs1.CursorType = 0
    rs1.LockType = 3

    ' cn1 is a connection string WITHOUT charset=utf8 parameter
    rs1.open SQL, cn1

    rs1.addNew

    rs1("test") = "Foreign Language Text in hebrew"

    rs1.update

2. with recordset, with charset=utf8 parameter
 
    SQL = "SELECT 1 FROM test WHERE 1=0"
    Set rs1 = CreateObject ("ADODB.Recordset")
   
    rs1.CursorType = 0
    rs1.LockType = 3

    ' cn1 is a connection string WITH charset=utf8 parameter
    rs1.open SQL, cn2

    rs1.addNew

    rs1("test") = "Foreign Language Text in hebrew"

    rs1.update

3. without recordset, without charset=utf8

   SQL = "INSERT INTO test (test) VALUES ('Foreign Language Text in hebrew)"
   cn1.execute SQL

4. without recordset, with charset=utf8

   SQL = "INSERT INTO test (test) VALUES ('Foreign Language Text in hebrew)"
   cn2.execute SQL

Only option 2 is storing the utf8 data currectly.
(verified by viewing the hex data of the column and trying to decode it with a hex decoder, and also by comparing the length of the string in the column in all 4 tests)

THE QUESTION: why doesnt option 4 store the data currectly along with option 2?

by the way, the same this occours ofcourse if i have a text box that i submit data into and my page has a meta tag of utf8. its the same thing as my hard coded text example.

connection definitions:

cn1
----
Set cn1 = CreateObject ("ADODB.Connection")

cn1.ConnectionString = "Driver={MySQL ODBC 5.1 Driver};Port=3306;Option=3;Server=xxxxxx;User ID=xxxxx;Password=xxxxx;Database=xxxxx;"

cn1.open

connection definitions:

cn2
----
Set cn2 = CreateObject ("ADODB.Connection")

cn2.ConnectionString = "Driver={MySQL ODBC 5.1 Driver};Port=3306;Option=3;Server=xxxxxx;User ID=xxxxx;Password=xxxxx;Database=xxxxx;charset=utf8"

cn2.open

As for the question "why doesn't option #4 work". Since 5.1 is a unicode driver MS driver translate all non-unicode calls(SQLExecDirect) to its unicode counterpart(SQLExecDirectW) call. Doing that it converts input string parameters to SQLWCHAR. It assumes that string data is encoded in ANSI charset of the system it is being executed on. And that is unlikely to be UTF8. Thus driver receives wrong utf16 strings and thus wrong data goes to a database.

understood.

so my only option to work with utf8 and asp without opening a recordset object for nothing is to decode the utf8 to ansi before sending it to the execute method?

Yes, that should work.

Alon,

Can you attach a short ASP test case with some utf-8 data you intended to insert?
We have to make sure there is no double string conversion. This happens when each UTF-8 byte is interpreted as wide character (assuming UTF-16) and then converted back to UTF-8.
Thanks.

test case for hebrew text insertion with utf8 encoding

Attachment: test.asp (text/plain), 900 bytes.

I have added a test file.

thank you

* you will have to change the connection string info to your db

Thanks for uploading the test case.

Verified with Connector/ODBC 5.1.9

Posted by developer:
 
This is to be rechecked against MySQL Connector/ODBC 8.0

Posted by developer:
 
Re-checked against the MySQL ODBC driver 8.4.0.
Both ANSI and Unicode versions worked correctly.
The ANSI version requires data strings in UTF8.
The UNICODE version requires data strings in UTF16 (SQLWCHAR), which will be converted by the driver to UTF8.

Closing the ticket.

I wonder if I could also wait 13 years with a bug and then tell my boss that I can't repro..

But good to know you're still alive mate!
Let's meet here in 2037!