MySQL Bugs: #107699: MySQL ODBC 8.0 Unicode Driver Fails to deal with .NET Unsigned Integers.

Bug #107699	MySQL ODBC 8.0 Unicode Driver Fails to deal with .NET Unsigned Integers.
Submitted:	29 Jun 2022 9:57	Modified:	13 Jul 2022 10:23
Reporter:	Robin Hickmott	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	8.0.29	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
The ODBC Windows Clients Seems to be unable to use .NETs Unsigned Integers as a parameter in prepared statements

It throws an exception 
System.InvalidCastException: Specified cast is not valid.

The Resolve (depending on if your happy with only using 32bit Signed Numbers) is to Cast these to 64bit Signed Integers before using them. I'm not sure without testing if the problem exists with the Unsigned 64 data type ( System.ULong or System.Uint64 )

How to repeat:
Update any value that has a UNSIGNED Integer field type in MySQL (InnoDB)

This throws an error

'-- Test
Using mUpdate As New OdbcCommand(" UPDATE `test` SET `test_value` = ? WHERE `id` = 1 ", m_DbConnection)

	'-- Declare Arbatory Value
    	Dim tValue As UInteger = 10

	'-- Add Parameters
	mUpdate.Parameters.AddWithValue(":TEST", tValue)
	mUpdate.ExecuteNonQuery()

End Using

This Succeeds

'-- Test
Using mUpdate As New OdbcCommand(" UPDATE `test` SET `test_value` = ? WHERE `id` = 1 ", m_DbConnection)

	'-- Declare Arbatory Value
    	Dim tValue As UInteger = 10

	'-- Add Parameters
	mUpdate.Parameters.AddWithValue(":TEST",  Convert.ToInt64(tValue) )
	mUpdate.ExecuteNonQuery()

End Using

Suggested fix:
Unsigned Data types should be recognised by the connector

Hello Robin,

Thank you for the bug report.
Verified as described.

Regards,
Ashwini Patil

Posted by developer:
 
It looks like after connecting to MySQL Server the .NET engine is not calling any ODBC driver functions up to the point where the exception is thrown.
The exception and the error are happening because this usage of .AddWithValue() function forces .NET to guess the database type of the parameter.
Sometimes it guesses right and sometimes (like in the present case) it guesses wrong.
The recommended way is to indicate the value type explicitly as:

mUpdate.Parameters.AddWithValue(":TEST", Convert.ToInt64(tValue))

or

mUpdate.Parameters.Add(":Name", OdbcType.Int).Value = tValue ' for 32-bit integers
mUpdate.Parameters.Add(":Name", OdbcType.BigInt).Value = tValue ' for 64-bit integers

This not only improves the performance plus avoids the guessing error, but also it is more secure because not specifying the parameter type leaves the code vulnerable to SQL injections.
It is not a regression because the older versions of ODBC driver behave in the same way. Again, after the connection is established the .NET engine does not make a single call to ODBC driver before throwing the exception.

Marking this report as not a bug.
If you have more evidence that this is actually a bug, we will be happy to investigate more.