Roundcubemail: utf8 icons in signature

[bbcorp]

Hi there,
I tried with different versions (last stable, current dev, etc.)
I noticed HTML signature doesn't accept UTF8 icons like 🌍 for example :

turns into question marks when saving :

This problem seems to be present in HTML email writing.
By the way there's no problem with plaintext signature.

Thanks in advance

[alecpl]

The code for this icon is \xF0\x9F\x8C\x8D. MySQL 5.7 does not even allow it in a signature. The UPDATE query fails with DB Error: [1366] Incorrect string value: '\xF0\x9F\x8C\x8Dte...' for column 'signature'.... It fails also with plain text signature. I guess we could update db schema to use utf8mb4, but that would require MySQL 5.7. Maybe a better solution is to use some escaping/encoding technique instead.

Did you notice any errors in log? What database and PHP version are you using?

[bbcorp]

Hi Alec,
Database version : MariaDB 10.1
Php version : 7.0.30

I noticed no error in log.
When I enable SQL debug we see icon seems to be translated before updating :
Nov 29 22:46:51 vel2 roundcube: <122e4qsu> [3] UPDATEidentitiesSETchanged= now(),name= 'John Doe',email= '[email protected]',organization = '',reply-to= '',bcc= '',standard= '1',signature= '<div class=\"pre\">▒~_~L~M</div>',html_signature= '1' WHEREidentity_id= '2' ANDuser_id= '2' ANDdel<> 1;
The icon is also translated to "▒~_~L~M" when using plaintext signature.

Do not hesitate if you need more log/information

[alecpl]

Actually utf8mb4 charset is supported since MySQL 5.5.3. So, it's more likely we'll change the charset, but not in 1.4 (it's too late).

If you need a fix now, try:

ALTER TABLE identities CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

[bbcorp]

Thanks,
It's not better with utf8mb4 charset (even on the whole database)

I tried to put a utf8 icon directly in identities tables like this :
SELECT * FROM identities WHERE identity_id = 2 AND user_id = 2\G
******** 1. row ********
identity_id: 2
user_id: 2
changed: 2018-12-04 20:39:33
del: 0
standard: 1
name: FirstName LastName
organization:
email: [email protected]
reply-to:
bcc:
signature: 🌍
html_signature: 0

and now there is only one question mark in roundcube like this : ?

Do you have any clue ?
Can I turn more debug log ?

[alecpl]

I made some tests with Roundcube git-master, PHP 7.3, Postgres 10.6 and MySQL 5.7.24.
I inserted this emoji (🌍) as HTML entity in HTML signature editor (using source editor).

No problem on Postgres, so this is definitely a MySQL problem. On MySQL I get: DB Error: [1366] Incorrect string value: '\xF0\x9F\x8C\x8D</...' for column 'signature'....

Indeed, changing the charset to utf8mb4 is not enough. We need to send SET NAMES 'utf8mb4' instead of SET NAMES 'utf8' (what we do in DB driver).

ps. another temp solution might be to set entity_encoding:"numeric" in TinyMCE config. This however will work only with entities and only with HTML signature.

[bbcorp]

Thanks for your answer.
For info, I have no error on my MySQL Mariadb 10.1 when updating signature.

I got it working by putting HTML entity/code directly in table from MySQL CLI :
UPDATE identities SET changed = now(), name = 'FirstName LastName', email = '[email protected]', organization = '', reply-to = '', bcc = '', standard = '1', signature = '&#x1f30d;', html_signature = '1' WHERE identity_id = '2' AND user_id = '2' AND del <> 1;

So this is a workaround for me !
Still, it would be cool if it can fixed 👍

Feel free to ask for help/logs

[seanm]

If one is setting up a brand new install of rc 1.4 with MySQL, should one create the db with utf8 or utf8mb4? Even if there are still issues, would using utf8mb4 be a more future-proof choice? Thanks.

[CRCinAU]

So fast forward to today and version 1.4.0 - If I look via phpmyadmin, I can see that the icons are saved correctly, but composing shows ? instead if the icon.

Is this just presentation in the UI now?

The irony is that I copy & paste things fine with icons showing into the compose UI, but the sig is still shown as ? when composing a new mail.

When trying to update the sig, I get the following error:

[22-Nov-2019 18:12:52 +1100]: <a2glp8vf> DB Error: [1366] Incorrect string value: '\xF0\x9F\x93\xA7 n...' for column `roundcubemail`.`identities`.`signature` at row 1

[alecpl]

Fixed.