Procedure name is not escaped when using CommandType.StoredProcedure

[kpreisser]

Hi,
I noticed that when using MySqlConnector to call a stored procedure by specifying DbCommand.CommandType = CommandType.StoredProcedure, the procedure name set in DbCommand.CommandText is not automatically escaped. However, MySQL Connector/NET seems to do this. For example:

• In MySQL Workbench, create a procedure with a name that contains spaces like test spaces1:

delimiter //
CREATE PROCEDURE `test spaces1` (IN myVar INT)
    BEGIN
        SELECT 2 * myVar;
    END//
delimiter ;

• Create the following .NET 5 program using Connector/NET (MySql.Data 8.0.26):

static void Main(string[] args)
{
    var conBuilder = new MySqlConnectionStringBuilder() {
        Server = "localhost",
        Port = 3306,
        UserID = "root",
        Password = "xxx",
        SslMode = MySqlSslMode.None,
        AllowPublicKeyRetrieval = true,
        Pooling = false,
        Database = "mydb"
    };

    using var con = new MySqlConnection(conBuilder.ToString());
    con.Open();

    using var cmd = con.CreateCommand();
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    cmd.CommandText = "test spaces1";

    cmd.Parameters.AddWithValue("myVar", 123);
    object result = cmd.ExecuteScalar();    
}

• When running the program, it works as expected. Looking at the network connection, we can see that the following SQL is sent to the server:

CALL `mydb`.`test spaces1`(123)

• Now switch to MySqlConnector 1.4.0-beta.2. Executing the program throw the following exception:

MySqlConnector.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'spaces1(123)' at line 1

• We can see the following SQL is sent to the server (notice the procedure name is not escaped):

CALL test spaces1(123);

Is this intended behavior?
Thank you!

[bgrainger]

Yes, this is intentional. The user is expected to handle the escaping for stored procedure names; this allows multipart names to be provided if desired:

• sproc_name
• `sproc name`
• db_name.sproc_name
• `db name`.sproc_name
• db.`sproc name`
• `db name`.`sproc name`

MySqlCommand.CommandText should be set to the literal strings above to execute those stored procedures. This is similar to #818.

Connector/NET tries to quote the stored procedure names sometimes, but its logic is faulty and leads to multiple bugs (e.g., 84220, 91123.

The MySqlConnector documentation should be updated to clarify this. (And it could be added to the migration page.)

[kpreisser]

Ok, thanks for the clarification! I agree this could be added to the docs/migration page.

However, I also noticed that quoting the stored procedure name doesn't seem to work correctly for the query to information_schema.routines and information_schema.parameters that MySqlConnector executes, when the procedure name contains a backtick:

• Create a procedure like this:

delimiter //
CREATE PROCEDURE `test spaces3.x ``2` (IN myVarStr VARCHAR(2000), OUT myVarStrOut VARCHAR(2000))
    BEGIN
        SELECT CONCAT('Test: ', myVarStr) INTO myVarStrOut;
    END//
delimiter ;

• Execute the following program:

    using var cmd = con.CreateCommand();
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    cmd.CommandText = "`test spaces3.x ``2`"; // Use escaped procedure name

    var output = cmd.Parameters.Add("myVarStrOut", MySqlDbType.VarChar);
    output.Direction = System.Data.ParameterDirection.Output;
    cmd.Parameters.AddWithValue("myVarStr", "Test");
            
    object result = cmd.ExecuteScalar();

• When running, this will throw an exception: MySqlConnector.MySqlException: OUT or INOUT argument 2 for routine mydb.test spaces3.x `2 is not a variable or NEW pseudo-variable in BEFORE trigger
• When looking at the packets, we see the following SQL is sent to the server:

SELECT COUNT(*)
FROM information_schema.routines
WHERE ROUTINE_SCHEMA = 'mydb' AND ROUTINE_NAME = 'test spaces3.x ``2';
SELECT ORDINAL_POSITION, PARAMETER_MODE, PARAMETER_NAME, DTD_IDENTIFIER
FROM information_schema.parameters
WHERE SPECIFIC_SCHEMA = 'mydb' AND SPECIFIC_NAME = 'test spaces3.x ``2'
ORDER BY ORDINAL_POSITION;

CALL `test spaces3.x ``2`(@outParam0, 'Test');SELECT '�����' AS '�����', @outParam0;

Notice that the query specifies ROUTINE_NAME = 'test spaces3.x ``2', so it seems the quotes/backticks at the start and end of the name were removed, but the double backtick wasn't de-escaped to a single backtick. Therefore, this query doesn't yield a result, because the actual procedure name is test spaces3.x `2, and (I assume) because of the missing result, the parameters are then supplied in the wrong order.

Can this be fixed?
Thanks!

[bgrainger]

Fixed in 1.3.12.

Documentation updated here: https://mysqlconnector.net/tutorials/migrating-from-connector-net/#mysqlcommand