C# ODBC Exception Incorrect String value

Question

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.

Can we see the code used to insert the string? – pickypg May 10 '11 at 22:47 — pickypg, May 10 '11 at 22:47

score 2 · Answer 1 · edited May 23 '17 at 12:29

2

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.

edited May 23 '17 at 12:29

Community

1
1

answered May 11 '11 at 15:30

Paul DelRe

4,003
1
24
26

As @Paul DelRe said, it's the BOM that you're seeing. It's also very important to understand that just because something looks like text in Notepad doesn't mean that it is. – Chris Haas May 11 '11 at 15:34

score 0 · Answer 2 · answered May 10 '11 at 22:49

0

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.

answered May 10 '11 at 22:49

Pop Catalin

61,751
23
87
115

WARNING: Not only apostrophes need to be escaped if you create the query dynamically, also backslashes need to be escaped. Replace backslashes with double backslashes, then apostropes with a backslash and an apostrophe. – Guffa May 10 '11 at 23:02
`com.Parameters.AddWithValue("msg", message.Msg);` I am using parameter queries. The above is my line that adds the message to the insert query. I looked at the actual text and found that there is an obscure character in the sentence. It is unrecognized in my operating system and just shows up as a box. Is there a way to easily filter these characters out? or insert them in their raw form as an unrecognized character? – Jonny Obero May 11 '11 at 14:39

CharithJ · Answer 3 · 2015-07-16T22:57:15.320

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

score 0 · Answer 4 · answered May 11 '11 at 15:39

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

C# ODBC Exception Incorrect String value

4 Answers4