Support full unicode in MySQL databases

Hi,

I suggest that MySQL databases should be created with collation utf8mb4_unicode_ci instead of utf8_general_ci.
First, why utf8mb4 instead of utf8?
- utf8 can lead to data loss (truncated data)
- utf8 can lead to critical security issues (see more in the link at the bottom)
- it supports all of Unicode which is required in a worldwide project such as ProjeQtOr to fully support asian languages for example, and also emojis

Then, why _unicode_ci over _general_ci?
- _unicode_ci is better at sorting (you will see almost no differences in languages such as English or French but this should be helpful in other languages, such as German).
- _general_ci has however slightly better performances

What can be done?
1. Make new ProjeQtOr installations utf8mb4 by default if MySQL is selected and MySQL/MariaDB version >= 5.5.3 (released more than 6 years ago). In prerequisities, you can still require MySQL v5+ but you should recommand v5.5.3 or higher.
2. utf8mb4 is backwards compatible with utf8 so existing databases can be migrated if MySQL/MariaDB version >= 5.5.3 (released more than 6 years ago) Edit: Ask the administrator to make a full backup before this step.
When MySQL database is in utf8mb4, connect with SET NAMES 'utf8mb4';

You can complete your reading with mathiasbynens.be/notes/mysql-utf8mb4
It explains why you should use utf8mb4 and how to migrate.

I hope I convinced you,
Thanks for reading,

If migration is too difficult, making utf8mb4 the default for newly created database should be a pretty straightforward task.
Thanks anyway,

It shouldn't be needed to put charset in SQL update scripts, the DBMS will guess it from the database charset and collation. Plus, the "utf8mb4" is only available in MySQL. The "utf8mb4" is the same as "utf8" in PostgreSQL. However "utf8" in MySQL is something different from other DBMS "utf8" such as PostgreSQL.
I think I will try a migration in my pre-prod environment and will tell you how it's going.
Security of our application is important for us.

After investigating this issue, I discovered that you were actually storing cp1252 data into UTF8 tables.
To be more precise, you store "default character set" of the DBMS, which for MySQL/MariaDB is cp1252 if it was never changed.
You have to do a "SET names utf8;" before making any query on the database, which never happens as $enforceUTF8 is never set. This is weird because in your .sql scripts, all your tables are created in UTF8, so nothing should have prevented connecting to database in UTF8 since you were using UTF8 since the beginnings of ProjeQtOr.