`Parameter '@'%'' must be defined.` executing `ALTER USER` or `CREATE USER` statement

[bgrainger]

trafficstars

Software versions MySqlConnector version: 2.3.7 MySQL Server 8.4.0

Describe the bug The following SQL statements are valid ways to write an ALTER USER statement. (Similarly for CREATE USER.)

ALTER USER test@'%' IDENTIFIED BY 'password';

ALTER USER test @'%' IDENTIFIED BY 'password';

ALTER USER 'test' @'%' IDENTIFIED BY 'password';

ALTER USER test
@'%' IDENTIFIED BY 'password';

ALTER USER test@localhost IDENTIFIED BY 'password';

ALTER USER test @localhost IDENTIFIED BY 'password';

ALTER USER test @'localhost' IDENTIFIED BY 'password';

ALTER USER 'test' @'localhost' IDENTIFIED BY 'password';

ALTER USER test
@localhost IDENTIFIED BY 'password';

Executing one of these statements will generate an exception like the following:

MySqlException: Parameter '@localhost' must be defined. To use this as a variable, set 'Allow User Variables=true' in the connection string.

MySqlException: Parameter '@'%'' must be defined. To use this as a variable, set 'Allow User Variables=true' in the connection string.

Exception

MySqlException: Parameter '@'%'' must be defined. To use this as a variable, set 'Allow User Variables=true' in the connection string.
   at MySqlConnector.Core.StatementPreparer.GetParameterIndex(String name) in /_/src/MySqlConnector/Core/StatementPreparer.cs:line 42
   at MySqlConnector.Core.StatementPreparer.ParameterSqlParser.OnNamedParameter(Int32 index, Int32 length) in /_/src/MySqlConnector/Core/StatementPreparer.cs:line 63
   at MySqlConnector.Core.SqlParser.Parse(String sql) in /_/src/MySqlConnector/Core/SqlParser.cs:line 78
   at MySqlConnector.Core.StatementPreparer.ParseAndBindParameters(ByteBufferWriter writer) in /_/src/MySqlConnector/Core/StatementPreparer.cs:line 32
   at MySqlConnector.Core.SingleCommandPayloadCreator.WriteCommand(IMySqlCommand command, ByteBufferWriter writer, Boolean appendSemicolon, Boolean isFirstCommand, Boolean isLastCommand) in /_/src/MySqlConnector/Core/SingleCommandPayloadCreator.cs:line 266
   at MySqlConnector.Core.SingleCommandPayloadCreator.WriteQueryPayload(IMySqlCommand command, IDictionary`2 cachedProcedures, ByteBufferWriter writer, Boolean appendSemicolon, Boolean isFirstCommand, Boolean isLastCommand) in /_/src/MySqlConnector/Core/SingleCommandPayloadCreator.cs:line 77
   at MySqlConnector.Core.SingleCommandPayloadCreator.WriteQueryCommand(CommandListPosition& commandListPosition, IDictionary`2 cachedProcedures, ByteBufferWriter writer, Boolean appendSemicolon) in /_/src/MySqlConnector/Core/SingleCommandPayloadCreator.cs:line 45
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 44
   at MySqlConnector.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 309

Expected behavior

Since this is legal SQL, it should be allowed to pass through without generating an error.

Workarounds

As the error message says, the user can work around this error by adding AllowUserVariables=true; to the connection string.

It also "just works" if the user name is quoted and there is no space between the ' and the @:

ALTER USER 'test'@'%' IDENTIFIED BY 'password';

ALTER USER 'test'@localhost IDENTIFIED BY 'password';

Additional Context

See https://stackoverflow.com/q/78462746/23633.

[bgrainger]

See https://github.com/mysql-net/MySqlConnector/issues/194#issuecomment-287618489 for why Allow User Variables defaults to false.

[Luuk34]

This bug (?) seems to have been solved when using MySqlConnector 2.4.0.

[bgrainger]

Is it possible that you have Allow User Variables=true in your connection string? That will work around this bug.