I'm using C# to parse a chat log and place messages right into a database.
When attempting to place the string
"Don't worry, it's unloaded"
(using the double quotes) it provides me with the next 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 applying latin-1 default collation for that encoding plan.
I've attempted switching to utf-8 but this provided the mistake on a single line.
Based on your selected method to place data (I suppose because they build the SQL directly), the
' character must be steered clear of. Like
' is string delimiting character in many databases.
What you ought to place is
"Don''t worry, it''s unloaded", using the single quotes steered clear of by doubling them.
!Important: You have to be careful about raw using raw SQL as it can certainly easily create security holes that may SQL injection. Use parametrized queries whenever you can or fully escape the query delivered to the server.
User SQL Parameters rather than using raw sql. There might be SQL Injection security problem which kind of issues.
See using sql parameters mysql-c#
Remember that if you are creating the SQL statement (don't! -- make use of a parameterized query rather), when the construct ends up searching like:
insert foo ( bar ) values( "This is my data" )
The place will fail since the double quote introduces a cited column title. It's different then a string literal that is cited using the apostrophe (