Strange effect when mixing escaped variable names + parameters

[dimzon]

UPD. Every time someone use escaped user defined variable names into query all parameter values become NULL;
Just add this to you query and all parameters become NULL:

SET @'example-variable-name':= 0;

OR

SET @`example-variable-name`:= 0;

OR

SET @"example-variable-name":= 0;

All of these commands executed in single transaction in both MySqlConnector and Oracle’s Connector/NET. Every time DbParameter name = foo, value=22, type=int64 is supplied:

---

SET @'var':=1;
SELECT @'var' as R

returns 1 for both providers

---

SELECT @foo as R

returns 22 for both providers

---

SET @'var':=1;
SELECT @foo+@'var' as R

returns 23 for Oracle, DbNull for MySqlConnector

---

SET @'var':=1;
SELECT @foo+1 as R

returns 23 for Oracle, DbNull for MySqlConnector

---

SET @'var':=@foo+1;
SELECT @'var' as R

returns 23 for Oracle, DbNull for MySqlConnector

please note this behavior differs only when I'm using ESCAPED variable names (using "" or '' etc) https://dev.mysql.com/doc/refman/8.0/en/user-variables.html

IE any times I mix BOTH parameters and user-defined variables WITH ESCAPED name I got nulls

[dimzon]

problem in SqlParser.cs line 111. fixed code:

				else if (state == State.AtSign)
				{
					if (IsVariableName(ch))
						state = State.NamedParameter;
					else if (ch == '\'')
						state = State.SingleQuotedString;
					else if (ch == '"')
						state = State.DoubleQuotedString;
					else if (ch == '`')
						state = State.BacktickQuotedString;
					else
						state = State.Statement;
				}
``

[bgrainger]

Thanks for the suggested fix, but I suspect that will break actual escaped named parameters. (Although it's an interesting question of how those should be named on the C# side: should the parameter name include the backticks or not?)

[bgrainger]

This also likely relates to #195.

[bgrainger]

Connector/NET 8.0.13 Behaviour

using (var cmd = new MySqlCommand(STATEMENT, connection))
{
	cmd.Parameters.AddWithValue(C# PARAM NAME, 1);
	cmd.ExecuteScalar();

Statement	C# Param Name	Result	Seems buggy?
SELECT @foo;	@foo	1
SELECT @foo;	foo	1
SELECT @`foo`;	@`foo`	null	✓
SELECT @`foo`;	@foo	null	✓
SELECT @`foo`;	foo	null	✓
SELECT @'foo';	@'foo'	null	✓
SELECT @'foo';	@foo	null	✓
SELECT @'foo';	foo	null	✓
SELECT @"foo";	@"foo"	null	✓
SELECT @"foo";	@foo	null	✓
SELECT @"foo";	foo	null	✓

Desired Behaviour

Just as both @foo and foo are accepted as values for MySqlParameter.ParameterName when referencing @foo in a SQL statement, it seems like @'foo', @foo and foo should all be accepted when referencing a quoted variable name, because C# doesn't need quoting (or the @ sign) to denote the parameter.

In general, MySQL's identifier de-escaping rules should be applied to the MySqlParameter.ParameterName values and to parameters found in the SQL itself, then an exact (case-insensitive) comparison should be used to match them.

[bgrainger]

Fixed in 0.48.0.