C# ODBC Exception Incorrect String value

I am using C# to parse a chat log and insert messages into a database.

When trying to insert the string "Don't worry, it's unloaded" (with the double quote开发者_运维技巧s) it gives me the following exception:

System.Data.Odbc.OdbcException: ERROR [HY000] [MySQL][ODBC 5.1 Driver][mysqld-5.5.11]Incorrect string value: '\xEF\xBB\xBF it...' for column 'msg' at row 1 at void System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)

The database is using latin-1 default collation for the encoding scheme.

I have tried switching to utf-8 but this gave me the error on the same line.

---

Not sure what it means to your specific error, but EF BB BF is the UTF BOM character which could be causing an issue.

This answer pointed out that the client connection needs to be set to the proper character set as well. It could be that the C# client character isn't matching the MySQL encoding.

---

Depending on your chosen way to insert data (I assume by building the SQL directly), the ' character needs to be escaped. That's because ' is string delimiting character in most databases.

What you need to insert is "Don''t worry, it''s unloaded", with the single quotes escaped by doubling them.

!Important: You need to be careful about raw using raw SQL as it can easily create security holes that can SQL injection. Use parametrized queries whenever possible or fully escape the query sent to the server.

---

Don't forget that if you're constructing the SQL statement (don't! -- use a parameterized query instead), if the construct winds up looking like:

insert foo ( bar ) values( "This is my data" )

The insert will fail because the double quote introduces a quoted column name. It is not the same as a string literal which is quoted with the apostrophe (').

---

User SQL Parameters instead of using raw sql. There can be SQL Injection security issue and these sort of issues.

See how to use sql parameters mysql-c#

Eg:

MySqlCommand mySqlCommand1;
...
mySqlCommand1.CommandText = "INSERT INTO dept (deptno, dname, desc) VALUES (?, ?, ?)";
mySqlCommand1.Parameters.Add("param1", 30);
mySqlCommand1.Parameters.Add("param2", "SALES");
mySqlCommand1.Parameters.Add("param3", @"Don''t worry, it''s unloaded");