Escaping apostrophes in SQL

Hi,

I've just tried adding an entity to our database which has an apostrophe in the content and I receive an error from MySQL indicating that the apostrophe has not been escaped. Pretty-much all other content containing apostrophes gets added correctly so I think it may be a result of one of the less common apostrophe characters (e.g. the curly ones that Microsoft insist on using if you author text in MS Word). I'm using MySQL5.

Here's is the exception:

MySql.Data.MySqlClient.MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Akiapola'au was previously widespread on the island but is now patchily distribu' at line 45
   at MySql.Data.MySqlClient.MySqlStream.OpenPacket()
   at MySql.Data.MySqlClient.NativeDriver.ReadResult(UInt64& affectedRows, Int64& lastInsertId)
   at MySql.Data.MySqlClient.MySqlDataReader.GetResultSet()
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   ...
   at Mindscape.LightSpeed.UnitOfWork.SaveChanges(Boolean reset)
   at Mindscape.LightSpeed.UnitOfWorkBase.SaveChanges()

and here is the (simplified) logged SQL statement:

 INSERT INTO `TextItem`
(
  `Id`,
  `Value`
)
VALUES
(
  'f0eb47be-c7f6-4360-8b16-ecec395f8fa7',
  '<p><warehouse:ref entityType=\"GlossaryTerm\" id=\"81a3a935-9198-4f24-a026-898eb00db4bb\">(Endemic)</warehouse:ref> to Hawaii in the Hawaiian Islands, the 'Akiapola'au was previously widespread on the island but is now patchily distributed.  Surveys undertaken in 1990 to 1995 estimated that only around 1,160 'Akiapola'au remained.</p>'
);

Am I right in thinking this is something that LightSpeed should handle or do I need to handle apostrophes in my application code?

Thanks,

Chris

LightSpeed should handle this if MySQL does.  We send all values in parameters, so escaping shouldn't be an issue: therefore it's possible this is an issue in MySQL (whether in the database or the driver).  Could you try inserting the same text into the same database using a parameterised query in raw ADO.NET?  If it fails, then I think it must be a MySQL issue (and may be bound up with some sort of locale issue, possibly different ideas about locales between the client and the server?).  If it succeeds then it's a LightSpeed issue, in which case please send us the exact text or the Unicode value of the character that's causing the problem and we'll take a look.

Just belatedly following this up in case any one else comes across a similar problem.

A bug in some newer versions of the MySQL .NET client did lead me astray but the version bundled with LightSpeed 2.2 works fine as long as you add "CharSet=utf8" into the connection string.