Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Procedure name is not escaped when using CommandType.StoredProcedure #1029

kpreisser opened this issue Sep 9, 2021 · 3 comments


Copy link

kpreisser commented Sep 9, 2021

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)
        SELECT 2 * myVar;
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());

    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!

Copy link

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

Copy link
Contributor Author

kpreisser commented Sep 10, 2021

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))
        SELECT CONCAT('Test: ', myVarStr) INTO myVarStrOut;
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:
FROM information_schema.routines
WHERE ROUTINE_SCHEMA = 'mydb' AND ROUTINE_NAME = 'test spaces3.x ``2';
FROM information_schema.parameters
WHERE SPECIFIC_SCHEMA = 'mydb' AND SPECIFIC_NAME = 'test spaces3.x ``2'
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?

Copy link

Fixed in 1.3.12.

Documentation updated here:

@bgrainger bgrainger added this to the 2.0 milestone Oct 16, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

No branches or pull requests

2 participants