Fix bug where emojis in contrib title cause SQL error

[battye]

Interesting bug that a few people have noticed @LukeWCS @Crizz0 @vinny @danieltj27 - in Titania, if you're on Windows or Android, and use an emoji like 🆕 in the new contrib name it would spit out an SQL error like Incorrect string value: '\xF0\x9F\x86\x95\x0AS...' when trying to submit. It's hard to reproduce because it seems to be fine on Mac.

My findings were that due to some code deep in Titania, unlike the other fields the actual contrib message/description seemed to be immune from this because it was being parsed to XML with the s9e text formatting library.

I've followed that lead to import s9e and parse/unparse the contrib names.

• [x] Fix contrib name using emojis on edit
• [ ] Check emojis in original submission (create)
  • See /controller/author.php:421
• [ ] Might need to parse/unparse other fields too (see #389)
• [ ] Unparse title on add revision page

[LukeWCS]

@battye

Trials in the test environment were successful. For both the Name and the Description field, it accepted the NEW emoji without an SQL error message.

[danieltj27]

As a note in reference to your first post. The general error I received was when I was using Safari on macOS (latest). So it's not isolated to Windows specifically in case that's helpful to this issue.

[battye]

As a note in reference to your first post. The general error I received was when I was using Safari on macOS (latest). So it's not isolated to Windows specifically in case that's helpful to this issue.

That's very interesting - I tried this quite a few times with @Crizz0 and it was erroring for him (Android) and working for me (Mac).

@danieltj27 Could you please share the precise steps you used when you saw this error on Mac? I would like to try and replicate it on my Mac... then I won't have to keep lugging out my heavy Windows laptop 😆

[danieltj27]

@danieltj27 Could you please share the precise steps you used when you saw this error on Mac? I would like to try and replicate it on my Mac... then I won't have to keep lugging out my heavy Windows laptop 😆

I submitted a new revision of an extension on phpBB.com.

In the message text area where you can include a message to the team, I put a single thumbs up emoji: 👍.

Submitted the revision returned the error I reported.

[battye]

Thanks @danieltj27, I can replicate this on my Mac now with those instructions. This is becoming quite a frustrating bug isn't it!

SQL ERROR [ mysqli ]

Incorrect string value: '\xF0\x9F\x86\x95' for column 'topic_subject' at row 1 [1366]

SQL

INSERT INTO customisation_topics (topic_id, parent_id, topic_type, topic_access, topic_category, topic_url, topic_status, topic_assigned, topic_sticky, topic_locked, topic_approved, topic_reported, topic_time, topic_posts, topic_views, topic_subject, topic_subject_clean, topic_first_post_id, topic_first_post_user_id, topic_first_post_username, topic_first_post_user_colour, topic_first_post_time, topic_last_post_id, topic_last_post_user_id, topic_last_post_username, topic_last_post_user_colour, topic_last_post_time, topic_last_post_subject, phpbb_topic_id) VALUES (0, 1, 3, 0, 8, 'a:1:{s:2:\"id\";i:1;}', 0, '', 0, 0, 1, 0, 1719208902, '', 0, 'Validation - <r>Test edit: <EMOJI seq="1f195" tseq="1f195">🆕', 'validation_r_test_edit:_emoji_seq=_1f195_tseq=_1f195_🆕', 0, 0, '', '', 1719208902, 0, 0, '', '', 1719208902, '', 0)

Could it be that the charset of the Titania tables is different than the rest of phpBB?