MySQL Bugs: #13753: Exception calling stored procedure with special characters in parameters

Bug #13753	Exception calling stored procedure with special characters in parameters
Submitted:	4 Oct 2005 18:38	Modified:	20 Oct 2005 7:17
Reporter:	Csaba Halasz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	1.0.6	OS:	Linux (Linux)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
Exception calling stored procedure if parameter contains special characters,
such as an "at sign" (@) and ANSI_QUOTES are enabled.

Note that it is sufficient to enclose the parameter name in double quotes _without_ any special characters to get an exception, but there is no point in doing so unless you actually want to use special characters.

In case you were wondering, I am porting ms sql server stored procedures, but I want to keep the parameter names.

How to repeat:
1) Create a stored procedure with a parameter name containing special characters, eg.:
CREATE PROCEDURE TestProc("@Param1" text)
BEGIN 
	SELECT "@Param1";
END

2) Try to call it from C#, eg.:
using System;
using System.Data;
using MySql.Data.MySqlClient;

class MainClass
{
    static void Main(string[] Args)
    {
        IDbConnection Conn = new MySqlConnection(Args[0]);
        Conn.Open();
        IDbCommand Cmd = Conn.CreateCommand();
        Cmd.CommandText = "TestProc";
        Cmd.CommandType = CommandType.StoredProcedure;
        IDataParameter Class = Cmd.CreateParameter();
        Class.ParameterName = "@Param1";
        Class.DbType = DbType.String;
        Class.Value = "whatever";
        Cmd.Parameters.Add(Class);
        Console.WriteLine(Cmd.ExecuteScalar() as string);
    }
}

Actual results:
Unhandled Exception: MySql.Data.MySqlClient.MySqlException: Parameter '?"' must be defined

Expected results:
"whatever" printed on console.

Suggested fix:
Maybe use the fix for bug #13036 somehow.
I have hacked command.cs:TokenizeSql to allow @, and StoredProcedure.cs to remove quote signs and use numeric temporary variables for "out" parameters.

my special hack

Attachment: bug.diff (application/octet-stream, text), 1.89 KiB.

I'm sorry...but I was not able to create stored procedure on 5.0.14 rc. I mean your definition of sp:
CREATE PROCEDURE TestProc("@Param1" text)
BEGIN 
	SELECT "@Param1";
END
What version of mysqld do you use ?

I'm using 5.0.12-beta-standard.
Do you mean it is not possible to create such stored procedures in 5.0.14?

The actual script I used was:
DROP PROCEDURE IF EXISTS TestProc;
DELIMITER //
CREATE PROCEDURE TestProc("@Param1" text)
BEGIN 
	SELECT "@Param1";
END
//
DELIMITER ;

You have to enable ANSI_QUOTES for this to work.
I started the server like this:
mysqld --user=mysql --old-passwords --sql-mode=ANSI_QUOTES

It is surely possible to create such a procedure:

mysql> set sql_mode=ANSI_QUOTES;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP PROCEDURE IF EXISTS TestProc;
Query OK, 0 rows affected, 1 warning (0.23 sec)

mysql> DELIMITER //
mysql> CREATE PROCEDURE TestProc("@Param1" text)
    -> BEGIN
    ->  SELECT "@Param1";
    -> END
    -> //
Query OK, 0 rows affected (0.17 sec)

mysql> DELIMITER ;
mysql> call TestProc('whatever');
+----------+
| @Param1" |
+----------+
| whatever |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

There is a small bug in output, that may explain something:

| @Param1" |
^^^^^^^^^

This unpaired closing quote, I mean...

And it works even with PS in mysql client:

mysql> prepare stmt from 'call TestProc(?)';
Query OK, 0 rows affected (0.03 sec)
Statement prepared

mysql> set @a = 'whatever';
Query OK, 0 rows affected (0.05 sec)

mysql> execute stmt using @a;
+----------+
| @Param1" |
+----------+
| whatever |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.13-rc-nt |
+--------------+
1 row in set (0.00 sec)

I have noticed the unmatched quote sign as well. I guess the quotes should be removed entirely. That is a different bug, however.
The prepare stmt example you have given is not relevant, because parameter names are not used there. Executing a similiar parametrized statement through .net connector works OK. Ie, doing something like:
        IDbCommand Cmd = Conn.CreateCommand();
        Cmd.CommandText = "CALL TestProc(?P)";
        IDataParameter Param = Cmd.CreateParameter();
        Param.ParameterName = "P";
        Param.DbType = DbType.String;
        Param.Value = "whatever";
        Cmd.Parameters.Add(Param);
        Console.WriteLine(Cmd.ExecuteScalar() as string);
In this case the parameter name is only used locally on the client, it has no connection to the name used in the stored procedure.
The bug occurs only when the .net connector queries the server for the information it needs to establish correct parameter order and handle "out" parameters.

I was able to reproduce the bug . Thanks for advice. Test case is attached.

Test case

Attachment: 13753.zip (application/force-download, text), 5.64 KiB.

Fixed in 1.0.7

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented in Connector/.NET-1.0.7 changelog. Closed.