Bug #16235 ODBC driver doesn't format parameters correctly
Submitted: 5 Jan 2006 20:38 Modified: 28 May 2013 12:35
Reporter: Jarrad Waterloo Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:3.51 OS:Microsoft Windows (Windows XP Professional)
Assigned to: Bogdan Degtyariov CPU Architecture:Any

[5 Jan 2006 20:38] Jarrad Waterloo
Description:
I get the following error from the following code:

Error:
[MySQL][ODBC 3.51 Driver][mysqld-5.0.18-nt]Unknown column 'Handheld' in 'where clause'

Code:
string Name = "Handheld";
DBConnectionConfiguration dbcc = new DBConnectionConfiguration();
string connectionString = dbcc.ConnectionString;
if(connectionString == string.Empty)
	throw new Exception("The RFID database has not been configured!");
Connection connection = new ConnectionClass();
connection.Open(connectionString, string.Empty, string.Empty, (int)ConnectOptionEnum.adConnectUnspecified);
CommandClass command = new CommandClass();
command.ActiveConnection = connection;
command.CommandText = "SELECT Id FROM Devices WHERE Name = ?;";
command.CommandType = CommandTypeEnum.adCmdText;

Parameter parameter = command.CreateParameter("Name", DataTypeEnum.adBSTR, ParameterDirectionEnum.adParamInput, Name.Length, Name);
command.Parameters.Append(parameter);

object o = Type.Missing;
Recordset recordset = command.Execute(out o, ref o, (int)CommandTypeEnum.adCmdText); // exception gets thrown here
Guid Id;
if(recordset.EOF)
	Id = Guid.Empty;
else
	Id = Helpers.EnsureGuid(recordset.Fields["Id"]);
connection.Close();
return Id;

Cause:

Needed Resolution:

How to repeat:
1) Run the code or
2) run this staement - SELECT Id FROM Devices WHERE Name = Handheld;

Suggested fix:
The problem is that the ODBC driver is not correctly formatting string parameters that are passed to it by putting quotes around it and escaping any quotes in the string parameter. I haven't tested yet but if this formatting problem is occuring with strings than binary and Guid types are also not being formatted/converted correctly. This fix is necessary and critical because most decent ODBC drivers on the market, Microsoft, Oracle and Firebird, handles parameter formatting. That is one chief reason why people use parameters. Further it would defeat the purpose of using an abstraction layer like ODBC if the MySQL ODBC driver is not consistent with the [expected] standard as this would require custom, non standard, coding per provider!
[6 Jan 2006 10:47] Bogdan Degtyariov
It is a known problem related with adBSTR type.
If you use adVarchar it should be ok. The patch for this bug is being prepared, but there are possible some problems.
[6 Jan 2006 14:13] Jarrad Waterloo
How far out is this patch? As I am using this and similar for Microsoft and Oracle, I would rather wait a short period of time than risk breaking something that is working well in other implementations.
[6 Jan 2006 15:05] Jarrad Waterloo
adVarWChar has the same problem as adBSTR
[10 Apr 2006 23:18] Adrian Sandor
I've been (violently) hit by this very same bug, using MySQL 4.1.11 and MyODBC 3.51.12 on Windows XP. The command parameter type was adVarWChar.
I confirm that after changing to adVarChar, there is no error. But I guess this means non-ASCII characters will be lost, which is horrible. Will this be fixed soon?
[28 Nov 2006 11:44] Audun
The bug seems not to be fixed yet. The 3.51 Connector doesn't put single quotes around literal strings, so instead I have to use triple quotes around strings in the programming code. The producer of the programming language says that "the mySQL Connector team must be doing left-hand work."  
So,don't you take ODBC seriously?
Will this bug be fixed in Connector 3.51 at all? Will it be fixed in Connector 5,x?

Regards
Audun
[10 May 2007 2:30] Jim Winstead
Bug #13319 was marked as a duplicate of this bug.
[10 May 2007 2:37] Jim Winstead
As far as we can figure, adBSTR is sometimes mapped the same as adVarWChar which is mapped to SQL_WVARCHAR (or possibly SQL_WLONGVARCHAR), which is not being handled properly. The workaround is to use adVarChar, but this will be fixed in a future release of 3.51.
[10 May 2007 17:28] Jim Winstead
patch to handle SQL_WVARCHAR and SQL_WLONGVARCHAR as we do SQL_WCHAR

Attachment: bug16235.patch (application/octet-stream, text), 2.39 KiB.

[21 May 2007 17:30] Jim Winstead
The fix for this bug has been committed to the source repository, and will be in the next release (3.51.16). Thanks for the bug report.
[6 Jun 2007 11:57] MC Brown
A note has been added to the 3.51.16 changelog.
[24 Sep 2009 10:26] Arno Freisinger
Hello, the Problem appear in windows 7 again.

We need al solution!
[1 Oct 2009 10:15] Tonci Grgin
Arno, this problem is fixed. Can you give us a little bit more details on what you're seeing and how you reproduced it?
[1 Oct 2009 10:27] Arno Freisinger
The Problem ist fixed in Win 2000,WIN XP an Vista. 

Use WIN7 an the Problem appears again.
[1 Oct 2009 10:28] Arno Freisinger
ODBC Connector 3.51.27
[1 Oct 2009 11:02] Tonci Grgin
Reopening based on Arno's comment. We need to check the patch against Win7.
[5 Oct 2009 9:17] Tonci Grgin
Arno.

Using Win7 x64 in VirtualBox VM I am unable to repeat the problem.
Environment:
  Win7 Ultimate x64 running in VirtualBox on W2K8x64 host
  Remote MySQL server 5.1.31x64 on OpenSolaris host

Test case:
create table bug16235 (Id INTEGER NOT NULL PRIMARY KEY, SomeVC VARCHAR(50));

Option Explicit
Const DSN = "Provider=MSDASQL.1;Persist Security Info=False;DSN=351w7onosol"
'Const DSN = "Provider=MSDASQL.1;Persist Security Info=False;DSN=w7osol"

Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
Const adOpenUnspecified = -1

Const adUseNone = 1
Const adUseServer = 2
Const adUseClient = 3

Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3

'---- ParameterDirectionEnum Values ----
Const adParamUnknown = &H0000
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adParamInputOutput = &H0003
Const adParamReturnValue = &H0004

'---- CommandTypeEnum Values ----
Const adCmdUnknown = &H0008
Const adCmdText = &H0001
Const adCmdTable = &H0002
Const adCmdStoredProc = &H0004
Const adCmdFile = &H0100
Const adCmdTableDirect = &H0200

Const adInteger=3
Const adDate=7
Const adVarChar=200
Const adBSTR=8

Dim cnxDatabase
Dim strSQL
Dim strTest
Dim par
Dim cmd

' connecting database
Set cnxDatabase = CreateObject("ADODB.Connection")
cnxDatabase.Open(DSN)
If cnxDatabase.State = 1 Then
  Wscript.Echo "Connected"
  cnxDatabase.Execute("USE test")
End If

' querying data
strSQL = "INSERT INTO bug16235 VALUES(?, ?)"

Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = cnxDatabase
cmd.CommandText = strSQL
Set par = cmd.CreateParameter("", adInteger, adParamInput, , 16)
cmd.Parameters.Append par
Set par = cmd.CreateParameter("", adBSTR, adParamInput, 31, "Handheld`s")
cmd.Parameters.Append par
cmd.Execute

set cmd = Nothing
Set cnxDatabase = Nothing

c/ODBC 3.51.27 from 32bit cmd line (wscript bug16235.vbs) - works
c/ODBC 5.1.5 from 64bit cmd line (wscript bug16235.vbs) - works

mysql> select * from bug16235;
+----+------------+
| Id | SomeVC     |
+----+------------+
|  4 | Handheld   |  c/ODBC 5.1.5  x64
|  8 | Handheld`s |  c/ODBC 3.1.27  32
| 16 | Handheld`s |  c/ODBC 5.1.5  x64
+----+------------+
3 rows in set (0.00 sec)
[6 Nov 2009 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[28 May 2013 12:35] Bogdan Degtyariov
I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.