emoji in forms rise a database error

[COOLak]

If you choose to save form submissions to the database and the user puts some emoji in their name or message, it leads to a fatal error "incorrect string value ... for column". Changing the table's charset to utf8mb4 doesn't help, apparently, it requires the whole database's charset to be set to that. But Bolt's default charset is regular utf8. If Bolt team is not going to change their charset to that (I suppose they're not), then BoltForms must sanitize input. I had to make an extension that uses the regex from there: https://stackoverflow.com/a/35961386/2418445. But you should consider including this by default.

[jadwigo]

This is a database issue.

You need to set the character set of your database to utf8mb4 or another multi byte character set. In single byte utf8 the emoji are invalid characters.

The core bolt has nothing to do with that - you can update the database to utf8mb4 and it is probably good to do that.

[jadwigo]

Sorry about reflexively closing this, that was not helpful...

It's something that will probably come up more often as people can use emoji on more and more devices.

When you create a new database for bolt you should set it up with urf8mb4.

The best way to fix this for existing sites is modifying the database to use multibyte encoding. It should not make much of a difference to bolt core - and if it is a problem there it should definitely be a high priority bug.

You could also do some fixing in the frontend, I've build a client-side javascript that replaced emoji with shortcodes a while ago https://github.com/jadwigo/demoji.js

[COOLak]

@jadwigo I converted the whole database to utf8mb4 and collation utf8mb4_bin. Also configured it as both client and server charset and collation in my mysql's my.ini. I keep getting the same error when saving forms with emoji. And just so you know, I'm not getting this error in one of my extensions where I work with another database directly via PDO. So it's definitely an issue.

[jadwigo]

If the database is changed the tables and columns are not automatically updated.
Did you also update all the tables and columns to an utf8mb4 collation?

According to sources on the internet you should use utf8mb4_unicode_520_ci because all the other ones are apparently outdated. But that's just nitpicking.

[COOLak]

@jadwigo

Did you also update all the tables and columns to an utf8mb4 collation?

Yes, I did. I'm very well aware of that. I'm telling you, everything is fine when I write to a separate database with PDO directly, but not when working with Bolt's database using entities.

[jadwigo]

I would like to look into this a bit further, but for that I need some more information.

How are you saving submission to the database - are you using the content type or a custom table.

• what version of bolt do you use
• what version of boltforms do you use
• what version of mysql server

And what is the boltforms.yml and the relevant contentypes.yml
Also what is the create statement of the relevant sql tables?