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