MySQL Bugs: #3195: ODBC driver binary data type broken

Bug #3195	ODBC driver binary data type broken
Submitted:	16 Mar 2004 22:28	Modified:	21 Jul 2004 20:53
Reporter:	[ name withheld ]	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.07	OS:	Windows (Win2K)
Assigned to:	Peter Harvey	CPU Architecture:	Any

Description:
The ODBC driver sends binary data to the SQL server as quoted strings. This causes the server to interpret the strings under the connection character set, and trashes the binary data. 

This happens in the file execute.c in the function insert_param:

  case SQL_CHAR:
  case SQL_VARCHAR:
  case SQL_LONGVARCHAR:
  case SQL_BINARY:
  case SQL_VARBINARY:
  case SQL_LONGVARBINARY:
  {
    *to++='\'';
    to= mysql_odbc_escape_string(mysql,
				 to, (net->max_packet -
				      (ulong) (to - (char*) net->buff)),
				 data, length,
				 (void*) net, extend_escape_buffer);
    if (to)	/* escape was ok */
    {
      *to++='\'';
    }
    return to;
  }

How to repeat:
Setup database:

create table test (data varchar(16) binary not null) default character set utf8;

Using ADODB and VB:
Dim oDB as new ADODB.Connection
dim oCom as new ADODB.Command
dim oParam as ADODB.Parameter
dim oData() as Byte

oDb.Open("DRIVER=My ODBC"..."; STMT=SET CHARACTER SET UTF8;")
set oCom.ActiveConnection = oDb
oCom.CommandText = "insert into test (data) VALUES (?)"
set oParam = oCom.CreateParameter("param1", adVarBinary, adParamInput, 16)
call oCom.Parameters.Append(oParam)

redim oData(0 to 1)
oData(0) = 65
oData(1) = 65

oParam.Value = oData
call oCom.Execute

This will insert the unicode value AA into the database, not the binary value 0x4141

Suggested fix:
Split the BINARY data into a separate set of cases that send the data to the server as true binary data.

  case SQL_BINARY:
  case SQL_VARBINARY:
  case SQL_LONGVARBINARY:
	  {
	  unsigned long read_pos;
	  char hex_digit[16] = {'0','1','2','3','4','5','6','7','8','9','a','b','c','d','e','f'};
	  to = extend_buffer(net, to, ((length+1) << 1));
	  *to++='0';
	  *to++='x';
	  for (read_pos=0;read_pos<length;read_pos++)
	  {
		  *to++=hex_digit[(data[read_pos] >> 4) & 0xf];
		  *to++=hex_digit[data[read_pos] & 0xf];
	  }
	  return to;
	  }

Suggested fix doesn't work for empty (zero-length) binary data. Here is a modification to deal with zero-length binary data.

	  if (length == 0) 
	  {
		  *to++='\'';
		  *to++='\'';
	  } 
	  else 
	  {
		  *to++='0';
		  *to++='x';
		  for (read_pos=0;read_pos<length;read_pos++)
		  {
			  *to++=hex_digit[(data[read_pos] >> 4) & 0xf];
			  *to++=hex_digit[data[read_pos] & 0xf];
		  }
	  }

c# example... a work around anyone?

private int addDataSegment(int fileID, byte[] data) {
  string cmd = "INSERT INTO ScrapbookData (FileID, FileData) ";
  cmd += "VALUES ('"+fileID+"', '";
  for (int i = 0; i < data.Length; i++) {
    if (data[i] == 0) cmd += "\\0";
    else if (data[i] == 34) cmd += "\\\"";
    else if (data[i] == 39) cmd += "\\'";
    else if (data[i] == 92) cmd += "\\\\";
    else cmd += (char)data[i];
  }
  cmd += "');";
  SQLExecuteNonQuery(cmd); //Execute the string cmd on the mysql server.
  return getLastInsertID(); //Gets the primary key of the inserted data item.
}

Basically, the ascii values from 80 to 100 become '?' because the character set problem.  I tried everything to fix it.  Can anyone provide a work around?  Thanks.

-Shad

I found a solution to my problem.  I'm not sure if this would help the people using the C/C++ connector.  The following c# code works to upload purely binary data.

private int addDataSegment(int fileID, byte[] data) {
 string CmdText = "INSERT INTO ScrapbookData (FileID, FileData) VALUES (?, ?);";
 OdbcCommand cmd = new OdbcCommand(CmdText, conn);
 cmd.Parameters.Add("FileID", OdbcType.Int);
 cmd.Parameters.Add("FileData", OdbcType.VarBinary);
 cmd.Parameters["FileID"].Value = fileID;
 cmd.Parameters["FileData"].Value = data;
 cmd.ExecuteNonQuery();
 return getLastInsertID();
}

-Shad

MyODBC version 3.51 is not designed to work with multiple character sets, and can't be modified to do so.  MyODBC 3.53, which is currently in development, will work with all of the newer features of MySQL, and will fully support the ODBC spec.