Description:
When a StringBuilder contains emoji (or non-BMP) characters, using it as the value of MySqlParameter.Value fails to transmit the characters successfully to the MySQL server.
Workarounds include calling .ToString() first before assigning it to the Value property.
How to repeat:
Create a table:
CREATE TABLE emoji(value TEXT COLLATE utf8mb4_bin);
Execute the following C# code:
using var connection = new MySqlConnection("...");
connection.Open();
var sb = new StringBuilder("\U0001F600");
// StringBuilder works fine for BMP characters:
// var sb = new StringBuilder("ab");
using var command = connection.CreateCommand();
command.CommandText = "insert into emoji(value) values(@value);";
command.Parameters.AddWithValue("@value", sb);
// workaround:
// command.Parameters.AddWithValue("@value", sb.ToString());
// throws MySqlException: Incorrect string value: '\xF0\x9F' for column 'value' at row 1
command.ExecuteNonQuery();
Suggested fix:
It appears that only the first two UTF-8 bytes (F0 9F) are being transmitted; all four UTF-8 bytes (F0 9F 98 80) for U+1F600 should be sent when the StringBuilder is converted to UTF-8.
Description: When a StringBuilder contains emoji (or non-BMP) characters, using it as the value of MySqlParameter.Value fails to transmit the characters successfully to the MySQL server. Workarounds include calling .ToString() first before assigning it to the Value property. How to repeat: Create a table: CREATE TABLE emoji(value TEXT COLLATE utf8mb4_bin); Execute the following C# code: using var connection = new MySqlConnection("..."); connection.Open(); var sb = new StringBuilder("\U0001F600"); // StringBuilder works fine for BMP characters: // var sb = new StringBuilder("ab"); using var command = connection.CreateCommand(); command.CommandText = "insert into emoji(value) values(@value);"; command.Parameters.AddWithValue("@value", sb); // workaround: // command.Parameters.AddWithValue("@value", sb.ToString()); // throws MySqlException: Incorrect string value: '\xF0\x9F' for column 'value' at row 1 command.ExecuteNonQuery(); Suggested fix: It appears that only the first two UTF-8 bytes (F0 9F) are being transmitted; all four UTF-8 bytes (F0 9F 98 80) for U+1F600 should be sent when the StringBuilder is converted to UTF-8.