MySQL Bugs: #16235: ODBC driver doesn't format parameters correctly

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:	Windows (Windows XP Professional)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

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!

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.

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.

adVarWChar has the same problem as adBSTR

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?

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

Bug #13319 was marked as a duplicate of this bug.

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.

patch to handle SQL_WVARCHAR and SQL_WLONGVARCHAR as we do SQL_WCHAR

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

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.

A note has been added to the 3.51.16 changelog.

Hello, the Problem appear in windows 7 again.

We need al solution!

Arno, this problem is fixed. Can you give us a little bit more details on what you're seeing and how you reproduced it?

The Problem ist fixed in Win 2000,WIN XP an Vista. 

Use WIN7 an the Problem appears again.

ODBC Connector 3.51.27

Reopening based on Arno's comment. We need to check the patch against Win7.

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)

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".

I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.