MySQL Bugs: #99793: Prepared stored procedure command doesn't verify parameter types

Bug #99793	Prepared stored procedure command doesn't verify parameter types
Submitted:	6 Jun 2020 19:51	Modified:	31 Jul 2020 16:26
Reporter:	Bradley Grainger (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	8.0.20	OS:	Windows (10)
Assigned to:		CPU Architecture:	Any

Description:
When a MySqlCommand for a stored procedure is prepared, parameter types are not checked, and a mismatch can result in garbage data being stored. (If MySqlCommand.Prepare is NOT called, then types will be checked and a FormatException will be thrown.)

How to repeat:
Execute the following C# code, making sure to have "IgnorePrepare=false" in the connection string:

var connection = new MySqlConnection("...;IgnorePrepare=false);
connection.Open();

var command = new MySqlCommand(@"DROP PROCEDURE IF EXISTS out_string;
CREATE PROCEDURE out_string(OUT value VARCHAR(100))
BEGIN
	SELECT 'test value' INTO value;
END;", connection);
command.ExecuteNonQuery();

command.CommandText = "out_string";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new MySqlParameter
{
	ParameterName = "@value",
	DbType = DbType.Int32,
	Direction = ParameterDirection.Output,
});

command.Prepare();
command.ExecuteNonQuery(); // this would throw a FormatException if cmd.Prepare() isn't called

// prints "1936028682", which is garbage data
Console.WriteLine(command.Parameters["@value"].Value);

Suggested fix:
MySqlCommand.ExecuteNonQuery should throw a FormatException for incorrect parameter types, whether or not Prepare() is called.

Hello Bradley,

Thank you for the report and test case.

regards,
Umesh

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.22 release, and here's the proposed changelog entry from the documentation team:

A mismatch of data types between the parameter of a stored procedure and
the corresponding MySqlParameter when the Prepare() method was called did
not generate an exception.

Thank you for the bug report.