Bug #48247 Connector/net does not properly escape strings with a single quote in them.

• Submitted: 22 Oct 2009 20:54
• Modified: 11 Nov 2009 22:41
• Reporter: Gerald Pöttler
• Status: Closed
• Category: Connector / NET
• Severity: S3 (Non-critical)
• Version: 6.2
• OS: Windows (Windows XP & 7 RC1)
• CPU Architecture: Any
• Tags: Stored Procedure parameterized Quote Escaping

[22 Oct 2009 20:54] Gerald Pöttler

Description:
When executing a stored procedure that contains a Text field with a single quote in it the command object does not seem to escape the query properly.

How to repeat:
Create Table with the following code:
==============================================
DROP TABLE IF EXISTS BlurbTest;

CREATE TABLE BlurbTest
(	
	 ID      SERIAL NOT NULL
	,Blurb   TEXT NOT NULL
);
==============================================

Create Stored Procedure with the following code:
==============================================
DROP PROCEDURE IF EXISTS spInsertBlurbTest;

DELIMITER $$

CREATE PROCEDURE spInsertBlurbTest
(
	 OUT ID INT
	,IN  BlurbParm TEXT
)
BEGIN
	INSERT INTO BlurbTest(
		Blurb
	) VALUES (
		BlurbParm
	);
	SET ID = LAST_INSERT_ID();
END$$

DELIMITER ;
==============================================

Create C# program with the following code:
==============================================
static void Main(string[] args)
		{
			TestThisString(@"let's do it");
			TestThisString(@"let''s do it");
			TestThisString(@"let\'s do it");
			TestThisString(@"lets do it");
			Console.ReadLine();
		}

		private static void TestThisString(string TestString)
		{
			Console.WriteLine("==================================================");
			Console.WriteLine("Testing: " + TestString);
			try
			{
				using (var conn = new MySqlConnection(Settings.ConnectionString))
				{
					conn.Open();
					using (var cmd = conn.CreateCommand())
					{
						cmd.CommandText = "spInsertBlurbTest";
						cmd.CommandType = CommandType.StoredProcedure;
						MySqlCommandBuilder.DeriveParameters(cmd);
						cmd.Parameters["@BlurbParm"].Value = TestString;
						cmd.ExecuteNonQuery();
					}
				}
				Console.WriteLine("Passed.");
			}
			catch (Exception exc)
			{
				Console.WriteLine("Failed: " + exc.Message);
			}
		}

==============================================
Run the C# program. The first three tests fail. The last one works.
All 4 should work, but they should also produce 4 different strings in the database.

Suggested fix:
No Clue

[23 Oct 2009 8:46] Tonci Grgin

Hi Gerald and thanks for your report.

I believe similar problem in helper class was patched for Bug#48101. Can you retry with latest sources from trunk?

[6 Nov 2009 19:52] Tonci Grgin

Gerald, would you mind unchecking private flag on your last post? If you don't my answer will look silly...

Now, go to launchpad.net and check out c/NET trunk (http://bazaar.launchpad.net/%7Emysql-clr-team/connectornet/trunk/). Building is easy and I believe there are instructions in manual. All I have to change in project is to remark following line
[assembly: AssemblyKeyName("ConnectorNet")]
in MySQL.Data/Properties/AssemblyInfo.cs

[11 Nov 2009 22:41] Gerald Pöttler

Thanks, I managed to get the source code and build it with your advice. Using the resultant DLL I can run the procedure as expected.