CODEWITHSUNDEEP
c#mysql.netdatabasecharacter-encoding
Jonny Obero
Jonny Obero

Reputation: 17

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 quotes) 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.

Upvotes: 2

Views: 1202

Answers (4)

CharithJ
CharithJ

Reputation: 47490

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");

Upvotes: 0

Nicholas Carey
Nicholas Carey

Reputation: 74177

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 (').

Upvotes: 0

Paul DelRe
Paul DelRe

Reputation: 4039

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.

Upvotes: 2

Pop Catalin
Pop Catalin

Reputation: 62920

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.

Upvotes: 0

Related Questions