When a user adds an emoji to their message, the site shows shows the 'Site Offline' message and the submission is not fully saved to the database. Looking at the log there is an incorrect string, which is the emoji character. The log message shows a box where the emoji character should be but I substituted *** for the emoji character below so I could submit this message.

Any thoughts? I'm not sure where to go from here.

PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x92\x95' for column 'data' at row 1: INSERT INTO {webform_submitted_data} (nid, sid, cid, no, data) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4); Array ( [:db_insert_placeholder_0] => 220 [:db_insert_placeholder_1] => 5858 [:db_insert_placeholder_2] => 4 [:db_insert_placeholder_3] => 0 [:db_insert_placeholder_4] => Test message *** ) in drupal_write_record() (line 7209 of C:\xampp\htdocs\sitename\includes\common.inc).

For reference: http://apps.timwhitlock.info/emoji/tables/unicode

Comments

nairb’s picture

nairb commented
Priority: Major » Normal

After more searching it seems this is a problem with core. 1910376, 2043439

danchadwick’s picture

danchadwick commented
Status: Active » Closed (won't fix)

This is not limited to just weforms. Try putting an emoji into a node title or body and see if you get an error. I did.

This might help. There is a comment about changing the character set. It didn't work for me.
#1140194: SQLSTATE[HY000]: General error: 1366 Incorrect string value for a field with accents

But this seems spot-on:
#1824506: When importing Tweets: SQLSTATE[HY000]: General error: 1366 Incorrect string value

So this seems to be outside the bounds of webform.

If you want to patch webform, you could follow the advice from this patch and find the right place(s) in webform.
https://www.drupal.org/files/FeedsJSONPathParser-invalidcharacter-182450...

The better solution, if it is open to you, would be to use MySQL 5.5.3 or higher and change the character code.

stalecheerios’s picture

stalecheerios commented

The better solution, if it is open to you, would be to use MySQL 5.5.3 or higher and change the character code.

Could you expand on this a little or provide a link that would help me to accomplish this? MySQL is already 5.5.37-0+wheezy1 - just don't know about changing character code.

Thanks!

danchadwick’s picture

danchadwick commented

Google emoji and mysql. That's what I did. There is a new 4-byte character code to handle these.

nairb’s picture

nairb commented

Using the Webform Validation module, I created a custom validation rule to check for emoji.

joelpittet’s picture

joelpittet
he/him
Primary language English
Location Vancouver
commented

The better solution, if it is open to you, would be to use MySQL 5.5.3 or higher and change the character code.

I just tried this without success:( It's really tricky because Drupal core connection sets to UTF-8:
https://api.drupal.org/api/drupal/includes!database!mysql!database.inc/f...

Maybe I didn't go full measure on that but I got a nasty error on staging and ran away from that approach.

In D8 this will get bumped I believe: #2473301: Raise MySQL requirement to 5.5.3

There is another approach for the stripping part:
#2232243: drupagram_emoji() removes valid characters

bribread22’s picture

bribread22 commented

I was having this same issue too. I see this as a minor security vulnerability/leak; for instance, if someone messing around with the form puts an emoji character in the username field in the user login form (for example), they will see the name of the table their information is being entered into (users), and the fields. This is likely because the database field(s) will only store UTF-8 text. I think non-UTF-8 text should be checked and converted to UTF-8. There is a PHP function that checks a string for its encoding and another one that will convert to UTF-8.

danchadwick’s picture

danchadwick commented

That your site is made with Drupal is fairly apparent to all. For production, you should set your error reporting so that users don't see error messages.

This issue is not specific to webform -- it applies to all of Drupal. We're not tackling what is in effect a core issue. The solution is to update your database server and set your character sets appropriately. It is not clear to me that the utf-8 in the connection is an issue. I think you can still set the character set in the tables.

https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html

tripper54’s picture

tripper54 commented

For other googlers out there, this module offers a quick fix:
https://www.drupal.org/project/strip_utf8mb4