Bug #111089 MySqlDataReader in C# does not recognize double backslash correctly

• Submitted: 19 May 2023 16:27
• Modified: 24 Jul 2023 13:52
• Reporter: Siehe eMail
• Status: Can't repeat
• Category: MySQL Server: Windows
• Severity: S3 (Non-critical)
• Version: 8.0.33
• OS: Windows
• CPU Architecture: Any
• Tags: query double backslash

[19 May 2023 16:27] Siehe eMail

Description:
The MySqlDataReader is not correctly handling the escape character backslash in a query.

I have a data table called Steps, which is defined like this:

CREATE TABLE IF NOT EXISTS `dbname`.`Step` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `Stepname` VARCHAR(255) NOT NULL,
  `Description` VARCHAR(255) NULL,
  PRIMARY KEY (`ID`),
  UNIQUE INDEX `ID_UNIQUE` (`ID` ASC) VISIBLE)
ENGINE = InnoDB;

If the string 'Stepname' contains a backslash the reader will not query it correctly.

For example the exact same statement:

SELECT Step.ID FROM Step WHERE Step.Stepname = 'do some action under \\Desktop\\someFolder, \\Desktop\\anotherFolder'

will return null in C#, but run in SQLDeveloper return the correct Step.ID.

Running an UPDATE statement works fine:

UPDATE Step set Stepname = 'do some action under \\Desktop\\someFilder, \\Desktop\\anotherFolder' where ID = <number>

Then querying the table in SQLDeveloper will show

Row N
ID    Stepname                                                            Description
<ID>  'do some action under \Desktop\someFilder, \Desktop\anotherFolder'  (null)

So the backslash is recognized and the value updated as expected.

As above stated running the SELECT will return the ID in SQLDeveloper, but not in C#.

How to repeat:
Create a table with a string column in which you put a string with a backslash.

Then query the table with putting the string column into the WHERE clause.

[24 Jul 2023 13:04] MySQL Verification Team

Hello!

Thank you for the bug report .
I tried to reproduce your issue on windows 11 with workbench 8.0.33 using details provided but I am not seeing any issues at my end.
Running the SELECT is not returning ID neither in Workbench nor in C#.
If you can provide the C# test case, feel free to add to this bug. Thanks.

Regards,
Ashwini Patil

[24 Jul 2023 13:52] Siehe eMail

Hey Ashwini,

thanks for your reply and checking.

This is the C# method used to query the database:

private string get_StepID(string checkBox_String)
        {
            string StepID = "";

            try
            {
                string query = "";

                query += " SELECT";
                query += "    Step.ID";
                query += " FROM";
                query += "    Step";
                query += " WHERE";

                if (checkBox_String.Contains("\\"))
                {
                    checkBox_String.Replace("\\", "\\\\");
                }

                query += "    Step.Stepname = '" + checkBox_String + "'";

                MySqlDataReader reader = broker.parallelQuery(query);

                if ((reader is null) || (!reader.HasRows)) return "";

                while (reader.Read())
                {
                    if (!reader.IsDBNull(0)) { StepID = reader.GetString(0); }
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                pmLogWriter.AddLogEntry(ex.Message, ex.StackTrace, "Error", ex);
            }

            return StepID;
        }

While the same query 

SELECT 
    Step.ID
FROM 
    Step
WHERE 
    Step.Stepname = 'ggf. Links unter \\Desktop\\Fachverfahren, \\Desktop\\OtherFolder bzw. Browserfavoriten IE/FF in GPO anpassen';

works in SQLDeveloper and returns the ID, it does return null in C# for me.

I'll attach a picture of SQLDeveloper too.

Many thanks,
Frank