Bug #16220 Apostrophe not escaped when using Dataset.Update

• Submitted: 5 Jan 2006 12:30
• Modified: 20 Jul 2006 9:22
• Reporter: Laurie Rhoads
• Status: Can't repeat
• Category: Connector / NET
• Severity: S1 (Critical)
• Version: 1.0.7
• OS: Windows (Windows)
• CPU Architecture: Any

[5 Jan 2006 12:30] Laurie Rhoads

Description:
An apostrophe (sorry - not sure of exact character code) which is treated as a statement start/end is not being escaped when read/written from XML.

Works fine with MySql ODBC connector, fails with /Net connector.

How to repeat:
	
	..open db..

	DataSet ds = new DataSet();					
	ds.ReadXml("demo.xml"); // attached file below...
	MySqlDataAdapter da = new MySqlDataAdapter();
	da.SelectCommand = new MySqlCommand("SELECT * FROM forum_topics", db);
	MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
	da.Update(ds, "forum_topics");

	.. fails because alternate apostrophe is not escaped whereas other apostrophies are..

	e.g.
	  if ' is encountered in the xml, the SQL statement becomes "...\'..."
	  if ´ is encountered in the xml, the SQL statement becomes "...\'..."
	  if ’ is encountered, it is not escaped, and the SQL statement fails as MySQL treats it as a string terminater

  Via the ODBC connector, I can go  "insert into a (a) values ('sssss´ssss’ddddd');
	..same statement fails on MySql.Data connector.

----------- demo xml dataset ---------
Notes: The two instances of the 'bogus' apostrophe below don't get escaped...

<?xml version="1.0" standalone="yes" ?> 
<NewDataSet>
	<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
		<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:Locale="en-AU">
			<xs:complexType>
				<xs:choice maxOccurs="unbounded">
					<xs:element name="forum_topics">
						<xs:complexType>
							<xs:sequence>
								<xs:element name="CAT_ID" type="xs:int" minOccurs="0" /> 
								<xs:element name="FORUM_ID" type="xs:int" minOccurs="0" /> 
								<xs:element name="TOPIC_ID" type="xs:int" minOccurs="0" /> 
								<xs:element name="T_STATUS" type="xs:short" minOccurs="0" /> 
								<xs:element name="T_MAIL" type="xs:short" minOccurs="0" /> 
								<xs:element name="T_SUBJECT" type="xs:string" minOccurs="0" /> 
								<xs:element name="T_MESSAGE" type="xs:string" minOccurs="0" /> 
								<xs:element name="T_AUTHOR" type="xs:int" minOccurs="0" /> 
								<xs:element name="T_REPLIES" type="xs:int" minOccurs="0" /> 
								<xs:element name="T_UREPLIES" type="xs:int" minOccurs="0" /> 
								<xs:element name="T_VIEW_COUNT" type="xs:int" minOccurs="0" /> 
								<xs:element name="T_LAST_POST" type="xs:string" minOccurs="0" /> 
								<xs:element name="T_DATE" type="xs:string" minOccurs="0" /> 
								<xs:element name="T_LAST_POSTER" type="xs:int" minOccurs="0" /> 
								<xs:element name="T_IP" type="xs:string" minOccurs="0" /> 
								<xs:element name="T_LAST_POST_AUTHOR" type="xs:int" minOccurs="0" /> 
								<xs:element name="T_LAST_POST_REPLY_ID" type="xs:int" minOccurs="0" /> 
								<xs:element name="T_ARCHIVE_FLAG" type="xs:int" minOccurs="0" /> 
								<xs:element name="T_LAST_EDIT" type="xs:string" minOccurs="0" /> 
								<xs:element name="T_LAST_EDITBY" type="xs:int" minOccurs="0" /> 
								<xs:element name="T_STICKY" type="xs:short" minOccurs="0" /> 
								<xs:element name="T_SIG" type="xs:short" minOccurs="0" /> 
							</xs:sequence>
						</xs:complexType>
					</xs:element>
				</xs:choice>
			</xs:complexType>
		</xs:element>
	</xs:schema>
	<forum_topics>
		<CAT_ID>5</CAT_ID> 
		<FORUM_ID>23</FORUM_ID> 
		<TOPIC_ID>4105</TOPIC_ID> 
		<T_STATUS>1</T_STATUS> 
		<T_MAIL>0</T_MAIL> 
		<T_SUBJECT>BRAND NEW ’04 Airush 14m Flow!!! and board</T_SUBJECT> 
		<T_MESSAGE>BRAND NEW ’04 Airush 14m Flow!!! (and lines and bar) Plus an Ex.Demo Airush Square 2 board and bag. I have never used the kite and the board was only used for a few runs before I bought it. I have gone back to wind surfing. I’d like $1200 for the kite lines and bar. $500 for the board Please call Ben on ...</T_MESSAGE> 
		<T_AUTHOR>1548</T_AUTHOR> 
		<T_REPLIES>2</T_REPLIES> 
		<T_UREPLIES>0</T_UREPLIES> 
		<T_VIEW_COUNT>223</T_VIEW_COUNT> 
		<T_LAST_POST>20050728191759</T_LAST_POST> 
		<T_DATE>20041217063706</T_DATE> 
		<T_LAST_POSTER>0</T_LAST_POSTER> 
		<T_IP>203.47.147.42</T_IP> 
		<T_LAST_POST_AUTHOR>3350</T_LAST_POST_AUTHOR> 
		<T_LAST_POST_REPLY_ID>30996</T_LAST_POST_REPLY_ID> 
		<T_ARCHIVE_FLAG>1</T_ARCHIVE_FLAG> 
		<T_STICKY>0</T_STICKY> 
		<T_SIG>0</T_SIG> 
	</forum_topics>
	<forum_topics>
		<CAT_ID>5</CAT_ID> 
		<FORUM_ID>10</FORUM_ID> 
		<TOPIC_ID>8578</TOPIC_ID> 
		<T_STATUS>1</T_STATUS> 
		<T_MAIL>0</T_MAIL> 
		<T_SUBJECT>Newb’s in Mackay</T_SUBJECT> 
		<T_MESSAGE>Hey all – just getting into kiteboarding and wanted to post an experience a mate and I had up in Mackay recently if any other newbs are trolling this forum looking for advice / opinion like I was. We are both getting into kiteboarding and after talking to our local guy here in Newcastle – Lachlan, he encouraged us, due to the flakey wind conditions here, to head north where the water is warm (wetsuit? What’s a wetsuit?), the winds are strong and constant (well, mostly) and the water is shallow enough for some learning newbs. Our host and IKO trainer was Joe Millen from Adrenalin Rush Sports up in Mackay and took us out for a couple of days at a place called East Point – a great place for the learner in a South-Easter. In terms of airfares – Jetstar fly from $109 out of Sydney. Accomodation wise – we tried the backpackers but wussed out after 1 night with no sleep (way too much noise), there are heaps of good hotels just south of the main part of town for $55-$65 a night. </T_MESSAGE> 
		<T_AUTHOR>3336</T_AUTHOR> 
		<T_REPLIES>3</T_REPLIES> 
		<T_UREPLIES>0</T_UREPLIES> 
		<T_VIEW_COUNT>141</T_VIEW_COUNT> 
		<T_LAST_POST>20050726023456</T_LAST_POST> 
		<T_DATE>20050725142532</T_DATE> 
		<T_LAST_POSTER>0</T_LAST_POSTER> 
		<T_IP>203.35.224.76</T_IP> 
		<T_LAST_POST_AUTHOR>363</T_LAST_POST_AUTHOR> 
		<T_LAST_POST_REPLY_ID>30723</T_LAST_POST_REPLY_ID> 
		<T_ARCHIVE_FLAG>1</T_ARCHIVE_FLAG> 
		<T_STICKY>0</T_STICKY> 
		<T_SIG>0</T_SIG> 
	</forum_topics>
</NewDataSet>

[20 Jul 2006 9:22] Tonci Grgin

Hi Laurie and thanks for your problem report.
  Via the ODBC connector, I can go  "insert into a (a) values ('sssss´ssss’ddddd'); ..same statement fails on MySql.Data connector.

Sorry I was unable to repeat reported problem with following test case:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;

namespace Bug16220
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Connecting to Mysql DB");
            String connectionString = "Server=munja;Database=test;Uid=root;Pwd=;Port=3307";
            Console.WriteLine("\nTesting NET Bugreport 16220, `");
            testBug16220(connectionString, "insert into a (a) values ('sssss´ssss’ddddd')");

            Console.WriteLine("\nPress any key to exit");
            Console.ReadKey();
        }

        static private void testBug16220(String connectionString, String query)
        {
            MySqlConnection cnn = new MySqlConnection(connectionString);
            cnn.Open();
            MySqlCommand cmd = new MySqlCommand("USE test", cnn);
            cmd.ExecuteNonQuery();

            cmd.CommandText = "DROP TABLE IF EXISTS test.a";
            cmd.ExecuteNonQuery();

            cmd.CommandText = "CREATE TABLE test.a (a VARCHAR(20) NOT NULL PRIMARY KEY)";
            cmd.ExecuteNonQuery();

            cmd.CommandText = query;
            cmd.ExecuteNonQuery();

            Console.WriteLine("\nCLEANING UP");
            //cmd.CommandText = "DROP TABLE IF EXISTS test.a";
            //cmd.ExecuteNonQuery();

            cnn.Close();
        }
    }
}
If you have any more info to provide on this, please reopen the report.
Thanks for your interest in MySQL!