2

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.

Improve this question
1
  • Can we see the code used to insert the string? Commented May 10, 2011 at 22:47

4 Answers 4

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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

Improve this answer

2 Comments

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.
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?
0

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

Improve this answer

Comments

0

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");
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.