MySQL Bugs: #21246: sql-mode parameter NO_BACKSLASH_ESCAPES breaks parameters with apostrophe's (')

Bug #21246	sql-mode parameter NO_BACKSLASH_ESCAPES breaks parameters with apostrophe's (')
Submitted:	24 Jul 2006 6:54	Modified:	28 Jul 2006 11:48
Reporter:	John Baro	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.0.7	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any

Description:
When the sql-mode NO_BACKSLASH_ESCAPES is set and a command is executed containing a string parameter with an apostrophe in the parameter value then a syntax error is raised.

How to repeat:
Create a table in the mysql database:
CREATE TABLE StringTest(StringValue VARCHAR(50));

Set the sql-mode NO_BACKSLASH_ESCAPES either for the session, user or server.

Create a .net project and a command with the command text:
INSERT INTO StringTest(StringValue) VALUES(?StringValue)

Add a parameter named ?StringValue and set the value to "O'Connor" (excluding quotes).

Execute the command.

Suggested fix:
Dunno, the issue is in the EscapeString method in the MySqlString class.
Maybe a flag on the command to specify whether to use the Backslash as an escape character or not and if not just replace single apostrophe's with double apostrophe's.

Hi John and thanks for your problem report.
I was unable to verify it with following code snipet:
        static private void testBug21246(String connectionString, String query)
        {
            MySqlConnection cnn = new MySqlConnection(connectionString);
            cnn.Open();
            MySqlCommand cmd = new MySqlCommand("USE test", cnn);
	    //"SET sql_mode =  NO_BACKSLASH_ESCAPES"
            cmd.ExecuteNonQuery();
            cmd.CommandText = "INSERT INTO StringTest(StringValue) VALUES(?StringValue)";
            MySqlParameter p = cmd.Parameters.Add("StringValue", MySqlDbType.VarString);
            p.Value = "O'Connor3";
            Console.WriteLine("\nSP param definition:");
            Console.WriteLine("  Value:" + p.Value.ToString());
            cmd.Prepare();
            cmd.ExecuteNonQuery();
            Console.WriteLine("\nCLEANING UP");
            cnn.Close();
        }

I have set correct sql_mode and tested several times:
C:\mysql507\bin>mysql -utonci -p -hmunja --port=3307 mysql
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 5.0.25-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> CREATE TABLE StringTest(StringValue VARCHAR(50));
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@session.sql_mode;
+----------------------+
| @@session.sql_mode   |
+----------------------+
| NO_BACKSLASH_ESCAPES |
+----------------------+
1 row in set (0.00 sec)

mysql> select * from StringTest;
+-------------+
| StringValue |
+-------------+
| O'Connor    |
| O'Connor2   |
| O'Connor3   |
| O'Connor3   |
+-------------+
4 rows in set (0.00 sec)

Environment:
  MySQL server 5.0.25-log BK on Suse 10 host
  MySQL connector/NET BK
  WinXP Pro SP2 client with NET 2.0

If you have some other info to provide on this, please feel free to reopen the report.

Hi Tonci,
Some more info if it is relevant:
MySql Server 5.0.22-community-nt on Windows 2003 Server.
Xp Sp2 client running .net framework 2.0 with MySql connector .net 1.0.7.

I made this short program to illustrate the problem.

using System;

using MySql.Data.MySqlClient;

namespace TestMySqlBug21246
{
    class Program
    {
        static void Main(string[] args)
        {
            using (MySqlConnection cn = new MySqlConnection("MySql Connection String"))
            {
                cn.Open();
                using (MySqlCommand cmd = cn.CreateCommand())
                {
                    cmd.CommandText = "SET SESSION sql_mode = 'NO_BACKSLASH_ESCAPES'";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "SELECT @@session.sql_mode";
                    Console.WriteLine((string)cmd.ExecuteScalar());
                    cmd.CommandText = "INSERT INTO StringTest(StringValue) VALUES(?StringValue)";
                    cmd.Parameters.Add(new MySqlParameter("?StringValue", "O'Connor"));
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}

//Table definition
CREATE TABLE `DumpDb`.`StringTest` (
  `StringValue` VARCHAR(50) NOT NULL DEFAULT ''
)

Hi John. A quote from manual:
1.2.5.2.2. Preparing Statements in Connector/NET
To prepare a statement, create a command object and set the .CommandText property to your query.
After entering your statement, call the .Prepare method of the MySqlCommand object. After the
statement is prepared, add parameters for each of the dynamic elements in the query.
After you enter your query and enter parameters, execute the statement using the
.ExecuteNonQuery(), .ExecuteScalar(), or .ExecuteReader methods.
For subsequent executions, you need only modify the values of the parameters and call the execute
method again, there is no need to set the .CommandText property or redefine the parameters.
C# Example
MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;
conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();
conn.ConnectionString = strConnection;
try
{
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO myTable VALUES(NULL, ?number, ?text)";
cmd.Prepare();
cmd.Parameters.Add("?number", 1);
cmd.Parameters.Add("?text", "One");
for (int i=1; i <= 1000; i++)
{
cmd.Parameters["?number"].Value = i;
cmd.Parameters["?text"].Value = "A string value";
cmd.ExecuteNonQuery();
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

So, either use suggested method or simply rewrite your code like this:
  cmd.Parameters.Add(new MySqlParameter("?StringValue", "O'Connor5"));
>>  cmd.Prepare();
  cmd.ExecuteNonQuery();
Note that cmd.Prepare(); can be written before adding parameters.

Thanks for the info.
This is still a bug IMO as according to the .net documentation DbCommand.Prepare()  creates a prepared/compiled version of the command on the database which should only be necessary/desirable if you are going to be reusing the same command with different parameters each time.
It should not be, nor is it in the normal course of things, necessary to call .Prepare() before executing a command, whether the CommandType is StoredProcedure or not.
In fact, I would hazard a guess that it would be performance degrading as it then requires two round trips to the server if each time we execute a command we have to call prepare first.
Can you confirm my interpretation?

Thanks
John

Hi John. Your interpretation is mainly correct except that extra round-trip to server happens only once resulting in no visible performance degradation. Parameter gets parsed by calling Prepare() as clearly stated in manual.