| Bug #28708 | MySQL ODBC cannot escape BIG5 strings correctly | ||
|---|---|---|---|
| Submitted: | 27 May 2007 18:36 | Modified: | 27 May 2007 21:12 |
| Reporter: | Michael Lee | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 3.51.X | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | backslash, BIG5, ODBC | ||
[27 May 2007 19:45]
Jim Winstead
This is a duplicate of Bug #9498.
[27 May 2007 21:12]
Michael Lee
Bug #9498 was reported on 2005-3-30 -- it's 2 years ago!! Bug #9498 reported that MySQL ODBC will cause data corruption when MySQL server is in "NO_BACKSLASH_ESCAPE" mode. However, this bug(#28708) I reported is not the same problem because it is not related to "NO_BACKSLASH_ESCAPE" mode. Now, I've download the MySQL ODBC source code, and will sutdy it and try to correct this problem. However, as a Delphi developer, I may not have the expertise to modify the source code. Please let me know your roadmap. If you don't have any plan on fixing bug #9498 or Bug #28708, please let me know. I can then try to modify the ODBC driver by myself (and send it back to you if I succeeded) or switch to another DB server. This is very important to me because there is NO workaround at this time. I cannot work with a system which may fail on certain data. Thanks for your help!
[31 May 2007 17:50]
Jim Winstead
The problem stems from the fact that the driver does not currently understand anything except the default character set (latin1). Once you've issued a "SET NAMES big5" query, what the driver thinks the current character set is and what the server thinks is now out-of-sync. We'll need to add support for supplying the default character set as part of the DSN, and then things should just work correctly.
Description: MySQL ODBC driver cannot escape the BIG5 strings containing a backslash INSIDE a character correctly. I tried to find the workarounds, however, the only thing I can do is to generate the correct SQL by myself. (that is, the '\' inside a BIG5 character should NOT be escaped) However, it doesn't work in most cases because the data content will come from data-aware controls and go through MySQL ODBC to MySQL server directly. There is NO way to intercept the SQL generated by MySQL ODBC. The real problem is the MySQL Server and MySQL ODBC treats backslashes in different manners. MySQL Server is character set aware, but the MySQL ODBC is not character set aware! How to repeat: Here's an ASP sample program Please note that the two bytes after "Line1:" is a Chinese BIG5 character (0xb35c) If you cannot type the BIG5 character, use chr(&Hb35c) instead --------------------- <html><head> <meta http-equiv="Content-Type" content="text/html; charset=big5"> MySQL ODBC testing program<br> <% const adCmdText = 1 const adLockOptimistic = 3 ' set cn=Server.CreateObject("ADODB.Connection") cn.Open "Provider=MSDASQL.1;Password=;Extended Properties=""DATABASE=test;DRIVER={MySQL ODBC 3.51 Driver};OPTION=3;PWD=;SERVER=127.0.0.1;UID=root""" cn.Execute("set names big5") ' create the testing table cn.Execute("DROP TABLE IF EXISTS test_escape_big5") cn.Execute("CREATE TABLE `test_escape_big5` (`str` varchar(40) NOT NULL)") ' try to insert a row set rs = Server.CreateObject("ADODB.RecordSet") sql = "select * from test_escape_big5" rs.Open sql,cn,,adLockOptimistic,adCmdText rs.AddNew 'rs("str")="Line1:許" + vbCrLf + "Line 2" rs("str")="Line1:" +chr(&Hb35c)+ vbCrLf + "Line 2" ' if you cannot type BIG5 character, use chr(&Hb35c) instead rs.Update rs.Close ' reopen the table & write it out rs.Open sql,cn,,adLockOptimistic,adCmdText Response.Write rs("str") %> </body></html> -------------------------- I'm a Delphi developer, however, I provide an ASP sample to make you easier to test this issue. Suggested fix: 1. Provide a mode (option flag) which makes MySQL ODBC NOT to escape any backslash (regardless the character set). Also, set MySQL server to "SQL_MODE='NO_BACKSLASH_ESCAPES'" 2. Make MySQL ODBC "character set aware". That is, escape strings according to the character set settings just like MySQL server does. 3. Always escape backslashes regradless the character set. However, also makes the MySQL server do the same thing. That is, provide a mode, for example, SQL_MODE="ALWAYS_BACKSLASH_ESCAPES"