c# - Incorrect string value: 'xEFxBFxBD' for column

Question

I have a table I need to handle various characters. The characters include ?, ? etc.

I have set my table to utf-8 as the default collation, all columns use table default, however when I try to insert these characters I get error: Incorrect string value: 'xEFxBFxBD' for column 'buyerName' at row 1

My connection string is defined as

string mySqlConn = "server="+server+";user="+username+";database="+database+";port="+port+";password="+password+";charset=utf8;";

I am at a loss as to why I am still seeing errors. Have I missed anything with either the .net connector, or with my MySQL setup?

--Edit--

My (new) C# insert statement looks like:

MySqlCommand insert = new MySqlCommand( "INSERT INTO fulfilled_Shipments_Data " +
     "(amazonOrderId,merchantOrderId,shipmentId,shipmentItemId,"+
     "amazonOrderItemId,merchantOrderItemId,purchaseDate,"+ ...

      VALUES (@amazonOrderId,@merchantOrderId,@shipmentId,@shipmentItemId,"+
      "@amazonOrderItemId,@merchantOrderItemId,@purchaseDate,"+ 
      "paymentsDate,shipmentDate,reportingDate,buyerEmail,buyerName,"+ ...


       insert.Parameters.AddWithValue("@amazonorderId",lines[0]);
       insert.Parameters.AddWithValue("@merchantOrderId",lines[1]); 
       insert.Parameters.AddWithValue("@shipmentId",lines[2]);
       insert.Parameters.AddWithValue("@shipmentItemId",lines[3]);
       insert.Parameters.AddWithValue("@amazonOrderItemId",lines[4]);
       insert.Parameters.AddWithValue("@merchantOrderItemId",lines[5]);
       insert.Parameters.AddWithValue("@purchaseDate",lines[6]);
       insert.Parameters.AddWithValue("@paymentsDate",lines[7]);

 insert.ExecuteNonQuery();

Assuming that this is the correct way to use parametrized statements, it is still giving an error

 "Incorrect string value: 'xEFxBFxBD' for column 'buyerName' at row 1"

Any other ideas?

Answer 1

xEFxBFxBD is the UTF-8 encoding for the unicode character U+FFFD. This is a special character, also known as the "Replacement character". A quote from the wikipedia page about the special unicode characters:

The replacement character ? (often a black diamond with a white question mark) is a symbol found in the Unicode standard at codepoint U+FFFD in the Specials table. It is used to indicate problems when a system is not able to decode a stream of data to a correct symbol. It is most commonly seen when a font does not contain a character, but is also seen when the data is invalid and does not match any character:

So it looks like your data source contains corrupted data. It is also possible that you try to read the data using the wrong encoding. Where do the lines come from?

If you can't fix the data, and your input indeed contains invalid characters, you could just remove the replacement characters:

lines[n] = lines[n].Replace("xFFFD", "");