MySQL Bugs: #42377: md5 doesn't return valid data in asp

Bug #42377	md5 doesn't return valid data in asp
Submitted:	27 Jan 2009 16:00	Modified:	27 Jan 2009 17:36
Reporter:	Stefano Zilli	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	5.1.5	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	md5

Description:
When you use ODBC v5.1.5 and run a simple md5 query in asp you get a result that can't be used.
If you try to print out with a response.write you get "????????????????", if you try a cast to string you get an internal server error.

How to repeat:
<%
  db_name = "your_db"
  UID = "your_user"
  PWD = "your_password"

  on error resume next
  strDB="DRIVER=MySQL ODBC 5.1 Driver;database=" & db_name & ";UID=" & UID & ";PWD=" & PWD
  Set Conn_DB = Server.CreateObject("ADODB.Connection")
  Conn_DB.Open strDB
  set LoadDB = Conn_DB
  set rs=conn_db.Execute("select md5('try this')")
  response.write rs(0)
  response.write cstring(rs(0))
%>

Hi Stefano and thanks for your report.

Unfortunately, you can not solve your problem in c/NET 5.1. You will need most recent 5.2 versions (5.2.4+) and set "functions return string=yes" in your connection string.

This is a known problem all connectors have with server metadata, see bug#10491 for details.
In short, connectors can't work around it, at least not reliably. Ad hoc user queries like this do not allow connector to distinguish between "SHOW CREATE TABLE", where it should treat BINARY as UTF8, and "SELECT varbinary_col FROM
some_table", where it really should be binary...
For test, start mysql client with -T and see what metadata it gets when doing your select.

Hi Tonci,

I'm not using the c or NET 5.1 connector but the simple ODBC (http://dev.mysql.com/downloads/connector/odbc/5.1.html) and I'm already using the latest one (v5.1.5).
Also I'm using ASP and not ASP.net.

I tryed the "functions return string=yes;" on the connection string but this didn't change the result.

I tryed to run the query from command line as suggested and this is what I get as result :

D:\MySQL\MySQL Server 5.0\bin>mysql -u root -T
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 121
Server version: 5.0.67-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select md5('try this');
Field   1:  `md5('try this')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     32
Max_length: 32
Decimals:   31
Flags:      BINARY

+----------------------------------+
| md5('try this')                  |
+----------------------------------+
| d5becebdd30256e08493fac40accd284 |
+----------------------------------+
1 row in set (0.00 sec)

Are you saying me that this is a bug with no resolution? In this case I need to go back to the old odbc (3.5.12) and Mysql server (5.0.x). Not a good a thing to hear :(

Sorry Stefano.

Anyway, story is exactly the same, just the name of option changes, it is "Always handle binary function result as character data" for c/ODBC. Check it in DSN and retest. Also, you should read manuals before posting bug reports:
SQLExecDirect:
	In:	hstmt = 0x003812F0, szSqlStr = "", cbSqlStr = -3	Return:	SQL_SUCCESS=0

Get Data All:
"md5('try this')"
"d5becebdd30256e08493fac40accd284"
1 row fetched from 1 column.

Thanks for your interest in MySQL.

Thank you for your time.
I have resolved with the option you indicated, now with "option=268435456;" in the connect string I get the correct value of the md5 in my asp page!

Stefano, glad the problem is solved.

This is not so easy to see from the start so, in the future, I advise you to use the "-T" trick and check what server returns. Then you search for option in driver. This can happen to several other field/result types.
Proper ODBC client, like MS ODBCte32(W).exe can help a lot too. If you had used it, you would have noticed your result actually looks like "0xYour_Correct_MD5_Text"!