Bug #21972 problem inserting fancy apostrophe

• Submitted: 1 Sep 2006 17:10
• Modified: 12 Oct 2006 8:18
• Reporter: Simon Weijgers
• Status: Can't repeat
• Category: Connector / NET
• Severity: S3 (Non-critical)
• Version: 1.0.7
• OS: Windows (Windows)
• CPU Architecture: Any

[1 Sep 2006 17:10] Simon Weijgers

Description:
the following command text snippet:

(NULL,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,648,'O’Kane')

will go over the wire as:

(NULL,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,648,'O'Kane')

(in other words the fancy apostrophe, for lack of better word, is converted to a normal apostrophe)

This causes a SQL error.

I would imagine that this could potentially be exploited by injecting sql, as the the conversion from fancy apos to normal apos happens after any escaping has taken place.

Important note, this happens if i'm using charset=latin1 in the connectionstring, if set it to utf8 it works fine.

How to repeat:
See above.

Suggested fix:
Not sure, suggested workaround would be to set charset to utf8. (see above)

[11 Sep 2006 8:14] Tonci Grgin

Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Explanation:
Simon, please post complete test case as well as SQL script with table structure.

[11 Oct 2006 23:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

[12 Oct 2006 8:18] Tonci Grgin

Simon, since no feedback came from you I conducted my own test and here are the findings:

With SET sql_mode =  NO_BACKSLASH_ESCAPES
mysql> select * from StringTest;
+-------------+
| StringValue |
+-------------+
| O'Kane      |
+-------------+
1 row in set (0.00 sec)

Without SET sql_mode =  NO_BACKSLASH_ESCAPES
mysql> select * from StringTest;
+-------------+
| StringValue |
+-------------+
| O'Kane      |
+-------------+
1 row in set (0.00 sec)
(NO_BACKSLASH_ESCAPES is here because of Bug #21246)

mysql> show variables like "%char%";
+--------------------------+-----------------------------+
| Variable_name            | Value                       |
+--------------------------+-----------------------------+
| character_set_client     | latin1                      |
| character_set_connection | latin1                      |
| character_set_database   | utf8                        |
| character_set_filesystem | binary                      |
| character_set_results    | latin1                      |
| character_set_server     | utf8                        |
| character_set_system     | utf8                        |
| character_sets_dir       | C:\mysql507\share\charsets\ |
+--------------------------+-----------------------------+
8 rows in set (0.00 sec)

Environment:
  - MySQL server 5.0.24BK on WinXP Pro SP2 localhost
  - c/NET 1.0.7 SVN latest
  - Test case
        static void Main(string[] args)
        {
            Console.WriteLine("Connecting to Mysql DB");
            String connectionString = "Server=localhost;Database=test;Uid=root;Pwd=;Port=3306";
            Console.WriteLine("\nTesting NET Bugreport 21246, `");
            testBug21972(connectionString, "NOT USED");
            Console.WriteLine("\nPress any key to exit");
            Console.ReadKey();
        }

        static private void testBug21972(String connectionString, String query)
        {
            MySqlConnection cnn = new MySqlConnection(connectionString);
            cnn.Open();
            MySqlCommand cmd = new MySqlCommand("USE test", cnn);//SET sql_mode =  NO_BACKSLASH_ESCAPES", cnn);
            cmd.ExecuteNonQuery();
            cmd.CommandText = "SELECT @@session.sql_mode";
            Console.WriteLine((string)cmd.ExecuteScalar());
            cmd.CommandText = "DROP TABLE IF EXISTS StringTest;";
            Console.WriteLine((string)cmd.ExecuteScalar());
            cmd.CommandText = "CREATE TABLE StringTest(StringValue VARCHAR(50));";
            Console.WriteLine((string)cmd.ExecuteScalar());
            cmd.CommandText = "INSERT INTO StringTest(StringValue) VALUES('O’Kane')";
            cmd.ExecuteNonQuery();
            Console.WriteLine("\nCLEANING UP");
            cnn.Close();
        }
  - General query log:
061012 10:03:41	     17 Connect     root@localhost on test
		     17 Change user root@localhost on test
		     17 Query       SHOW VARIABLES
		     17 Query       SHOW COLLATION
		     17 Query       SET character_set_results=NULL
		     17 Init DB     test
		     17 Query       SET sql_mode =  NO_BACKSLASH_ESCAPES
		     17 Query       SELECT @@session.sql_mode
		     17 Query       DROP TABLE IF EXISTS StringTest
		     17 Query       SHOW WARNINGS
		     17 Query       CREATE TABLE StringTest(StringValue VARCHAR(50))
		     17 Query       INSERT INTO StringTest(StringValue) VALUES('O’Kane')
061012 10:05:28	     15 Query       select * from StringTest
061012 10:07:00	     18 Connect     root@localhost on test
		     18 Change user root@localhost on test
		     18 Query       SHOW VARIABLES
		     18 Query       SHOW COLLATION
		     18 Query       SET character_set_results=NULL
		     18 Init DB     test
		     18 Query       USE test
		     18 Query       SELECT @@session.sql_mode
		     18 Query       DROP TABLE IF EXISTS StringTest
		     18 Query       CREATE TABLE StringTest(StringValue VARCHAR(50))
		     18 Query       INSERT INTO StringTest(StringValue) VALUES('O’Kane')
061012 10:07:05	     15 Query       select * from StringTest
061012 10:10:17	     15 Query       show variables like "%char%"