Bug #95226 Invalid syntax error on a valid query without spaces.
Submitted: 2 May 2019 17:46 Modified: 14 Mar 2023 15:51
Reporter: Guillaume Mercier Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:8.0.16 OS:Windows (17763.437)
Assigned to: CPU Architecture:x86
Tags: CALL, command, syntax, Validation, White space

[2 May 2019 17:46] Guillaume Mercier
Description:
I've received many syntax errors on very simple queries lately. Knowing that these queries were obviously valid, I've done some digging and found a good old bug.

Here is the query I'll use as my example:
```
SELECT
	COUNT(*)
FROM
	MyTable;
```

Here is the query generated by the connector when executing this command:
```
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 'SELECT
	count(*)
FROM
	MyTable' at line 1
```

After verifying with some colleagues that I hadn't descended into madness again and that this query has valid syntax, I decided to investigate. This somehow bugged me to a level I still can't understand. By trial and error, I've discovered that when a query contains no spaces it will always fail. The key word in that last sentence is "spaces". While the query has no spaces, it does have "white space" in the forms of tabs and new lines.

I then gave a look at the source code of the latest version of the connector (8.0.16). Just as I had expected there is something smelly going on with the way text commands are parsed.

These lines are the source of this unusual issue:
lines 455 to 466 in MySQL.DATA\src\command.cs (public new MySqlDataReader ExecuteReader(CommandBehavior behavior))
```
else if (CommandType == CommandType.Text)
{
    // validates single word statement (maybe is a stored procedure call)
    if (sql.IndexOf(" ") == -1)
    {
        if (AddCallStatement(sql))
            sql = "call " + sql;
    }
}
```
My first reaction was:
For some unknown reason, somebody seems to have forgotten the existence of the "StoredProcedure" command type or misunderstood the meaning of "dude this is a CommandType.Text command".

My second reaction was:
Wow somebody got lazy and/or forgot that space isn't the only white space character.

Sidenote: While I do see the benefits of making it simpler to call simple stored procedures, isn't that the point of the stored procedure command type?

How to repeat:
1. Setup a mysql database

2. Create a simple table called MyTable
```
CREATE TABLE MyTable(
	ID BIGINT UNSIGNED NOT NULL PRIMARY KEY
);
```

3. Setup a C# console project

4. Using the Nuget Package Manager, import the MySql.Data package, version 8.0.16 (or preferably latest available to see if the bug still exists)

5. Create a functional query, without the use of the space character.
```
command.Text = "SELECT\n\tcount(*)\nFROM\n\tMyTable;"
```

6. Run the command

7. You should get the following error:
```
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 'SELECT
	count(*)
FROM
	MyTable' at line 1
```

8. If you replicate the exact command stated in the previous step in my MySql Workbench or the MySQL command environment, the call should succeed.
```
SELECT
	COUNT(*)
FROM
	MyTable;
```

9. If you replace the new lines and tabs with spaces the call should also complete without any issues.
```
SELECT COUNT(*) FROM MyTable;
```

10. put a call in front of the query with spaces, and you should get the exact same type of error code and type obtained during step 7.
```
CALL SELECT
	COUNT(*)
FROM
	MyTable;
```

Step 5 Sample Code:
```
		static void Main(string[] args)
		{
			long output = 0;
			MySqlConnection connection = new MySqlConnection(/*your connection string here*/);
			MySqlCommand command = connection.CreateCommand();
			command.CommandType = CommandType.Text;
			command.CommandText = @"
SELECT
	COUNT(*)
FROM
	MyTable;";
			try
			{
				connection.Open();
				output = (long) command.ExecuteScalar();
			}
			catch (Exception e)
			{
				Console.Error.WriteLine(e.Message + "\n" + e.StackTrace);
			}
			finally
			{
				connection.Close();
				command.Dispose();
				connection.Dispose();
			}
			Console.WriteLine($"The number of rows in mytable is: {output}");
			Console.WriteLine($"Press enter to close");
			Console.ReadLine();
		}
```

Suggested fix:
1. I would suggest using a list of some of the most widely known whitespace characters for testing if a command might be a stored procedure. As a starting point, I would recommend the ones used in the latest revision of XML: space (0x20), (horizontal) tab(\t), new-line(\n) and carriage return(\r).
See this link for more information: https://www.w3.org/TR/xml/#sec-common-syn
```
else if (CommandType == CommandType.Text)
{
    // validates single word statement (maybe is a stored procedure call)
    // sql.IndexOfAny(new char[]{' ', '\t', '\n', '\r'}) would also be viable
    if (sql.IndexOf(" ") == -1 &&
        sql.IndexOf("\t") == -1 &&
        sql.IndexOf("\n") == -1 &&
        sql.IndexOf("\r") == -1)
    {
        if (AddCallStatement(sql))
            sql = "call " + sql;
    }
}
```

2. Removing the test itself as it goes against the meaning of a text command. If a user desires to create a stored procedure call, the "StoredProcedure" call type should be used as its name indicates it's should be used for the 
the exact purpose of calling or interacting with a single stored procedure
[3 May 2019 5:47] MySQL Verification Team
Hello gui mer,

Thank you for the report and test case.

regards,
Umesh
[7 May 2019 0:46] Bradley Grainger
Possible duplicate of #78760 https://bugs.mysql.com/bug.php?id=78760
[14 Mar 2023 15:51] Christine Cole
Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.33 release, and here's the proposed changelog entry from the documentation team:

A valid query formatted with tabs or new lines could return an invalid
syntax error.

Thank you for the bug report.