This site has been archived. To learn more about our current products Ibexa Content, Ibexa Experience, Ibexa Commerce head over to the Ibexa Developer Portal

eZ Community » Security Advisories » EZSA-2018-003: 4-byte UTF-8 in...

EZSA-2018-003: 4-byte UTF-8 in MySQL/MariaDB

Publication date : 24/05/2018
Severity : High
Affected versions : 2018.03, 5.4 - 5.3, all community versions at time of writing (legacy and new stack)
Resolving versions : Requires taking manual action, see description.

This security advisory affects installations using MySQL or MariaDB, and installations using eZ Publish Legacy, either stand-alone, or as part of eZ Platform 5.x, or in eZ Platform 1.11 and newer using LegacyBridge, or in a corresponding Community release. If you are not using Legacy in any way, and not using MySQL/MariaDB, you are not affected.

Summary

We recommend to change the character set and collation of your database tables to one supporting 4-byte UTF-8, if you're not already using this. This change may require some downtime, depending on your installation and size of your database.
If you cannot make this change (yet) we recommend to block the use of 4-byte characters in usernames in Legacy. This is a quick and simple change.

Change character set and collation

The MySQL/MariaDB character set 'utf8' supports only 1-3 byte UTF-8 characters, not 4-byte. The 4-byte characters are used for some languages, like Chinese, and emoticons (emoji). Depending on your database type and settings, and on your site configuration, this may lead to undesired consequences, crashes, or even security issues, because the database may truncate (cut off) strings where they contain 4-byte characters. The 'utf8mb4' character set solves this problem by offering full 4-byte support. It was introduced in MySQL 5.5.3, and MariaDB 5.5. It is the default character set in eZ Platform 2.2 and newer.

When changing the character set you must also use a compatible collation (collation rules are used when comparing strings). If you use MySQL 5.5, you can use the 'utf8mb4_unicode_ci' collation. This has the limitation that it cannot tell the difference between emoticon characters, so an SQL query for one emoticon may return matches for all emoticons. If you use MySQL 5.6 or newer, you can use 'utf8mb4_unicode_520_ci' which collates emoticons properly.

Beware that InnoDB has a 767 bytes limit on indexes. This means that when VARCHAR columns longer than 191 are indexed, then either the column or the index must be shortened to 191 characters BEFORE the charset can be changed. We recommend to replace the index with a new, shortened index. Here's how you can do that:

ALTER TABLE `ezbasket` DROP KEY `ezbasket_session_id`;
ALTER TABLE `ezbasket` ADD KEY `ezbasket_session_id` (`session_id` (191));

ALTER TABLE `ezcollab_group` DROP KEY `ezcollab_group_path`;
ALTER TABLE `ezcollab_group` ADD KEY `ezcollab_group_path` (`path_string` (191));

ALTER TABLE `ezcontent_language` DROP KEY `ezcontent_language_name`;
ALTER TABLE `ezcontent_language` ADD KEY `ezcontent_language_name` (`name` (191));

ALTER TABLE `ezcontentobject_attribute` DROP KEY `sort_key_string`;
ALTER TABLE `ezcontentobject_attribute` ADD KEY `sort_key_string` (`sort_key_string` (191));

ALTER TABLE `ezcontentobject_name` DROP KEY `ezcontentobject_name_name`;
ALTER TABLE `ezcontentobject_name` ADD KEY `ezcontentobject_name_name` (`name` (191));

ALTER TABLE `ezcontentobject_trash` DROP KEY `ezcobj_trash_path`;
ALTER TABLE `ezcontentobject_trash` ADD KEY `ezcobj_trash_path` (`path_string` (191));

ALTER TABLE `ezcontentobject_tree` DROP KEY `ezcontentobject_tree_path`;
ALTER TABLE `ezcontentobject_tree` ADD KEY `ezcontentobject_tree_path` (`path_string` (191));

ALTER TABLE `ezimagefile` DROP KEY `ezimagefile_file`;
ALTER TABLE `ezimagefile` ADD KEY `ezimagefile_file` (`filepath` (191));

ALTER TABLE `ezkeyword` DROP KEY `ezkeyword_keyword`;
ALTER TABLE `ezkeyword` ADD KEY `ezkeyword_keyword` (`keyword` (191));

ALTER TABLE `ezorder_status` DROP KEY `ezorder_status_name`;
ALTER TABLE `ezorder_status` ADD KEY `ezorder_status_name` (`name` (191));

ALTER TABLE `ezpolicy_limitation_value` DROP KEY `ezpolicy_limitation_value_val`;
ALTER TABLE `ezpolicy_limitation_value` ADD KEY `ezpolicy_limitation_value_val` (`value` (191));

ALTER TABLE `ezprest_authcode` DROP PRIMARY KEY;
ALTER TABLE `ezprest_authcode` ADD PRIMARY KEY (`id` (191));

ALTER TABLE `ezprest_authcode` DROP KEY `authcode_client_id`;
ALTER TABLE `ezprest_authcode` ADD KEY `authcode_client_id` (`client_id` (191));

ALTER TABLE `ezprest_clients` DROP KEY `client_id_unique`;
ALTER TABLE `ezprest_clients` ADD UNIQUE KEY `client_id_unique` (`client_id` (191),`version`);

ALTER TABLE `ezprest_token` DROP PRIMARY KEY;
ALTER TABLE `ezprest_token` ADD PRIMARY KEY (`id` (191));

ALTER TABLE `ezprest_token` DROP KEY `token_client_id`;
ALTER TABLE `ezprest_token` ADD KEY `token_client_id` (`client_id` (191));

ALTER TABLE `ezsearch_object_word_link` DROP KEY `ezsearch_object_word_link_identifier`;
ALTER TABLE `ezsearch_object_word_link` ADD KEY `ezsearch_object_word_link_identifier` (`identifier` (191));

ALTER TABLE `ezsearch_search_phrase` DROP KEY `ezsearch_search_phrase_phrase`;
ALTER TABLE `ezsearch_search_phrase` ADD UNIQUE KEY `ezsearch_search_phrase_phrase` (`phrase` (191));

ALTER TABLE `ezurl` DROP KEY `ezurl_url`;
ALTER TABLE `ezurl` ADD KEY `ezurl_url` (`url` (191));

ALTER TABLE `ezurlalias` DROP KEY `ezurlalias_desturl`;
ALTER TABLE `ezurlalias` ADD KEY `ezurlalias_desturl` (`destination_url` (191));

ALTER TABLE `ezurlalias` DROP KEY `ezurlalias_source_url`;
ALTER TABLE `ezurlalias` ADD KEY `ezurlalias_source_url` (`source_url` (191));

If you use DFS, then also the following script should be run:

ALTER TABLE `ezdfsfile` DROP KEY `ezdfsfile_name`;
ALTER TABLE `ezdfsfile` ADD KEY `ezdfsfile_name` (`name` (191));

ALTER TABLE `ezdfsfile` DROP KEY `ezdfsfile_name_trunk`;
ALTER TABLE `ezdfsfile` ADD KEY `ezdfsfile_name_trunk` (`name_trunk` (191));

ALTER TABLE `ezdfsfile` DROP KEY `ezdfsfile_expired_name`;
ALTER TABLE `ezdfsfile` ADD KEY `ezdfsfile_expired_name` (`expired`, `name` (191));

ALTER TABLE `ezdfsfile_cache` DROP KEY `ezdfsfile_name`;
ALTER TABLE `ezdfsfile_cache` ADD KEY `ezdfsfile_name` (`name` (191));

ALTER TABLE `ezdfsfile_cache` DROP KEY `ezdfsfile_name_trunk`;
ALTER TABLE `ezdfsfile_cache` ADD KEY `ezdfsfile_name_trunk` (`name_trunk` (191));

ALTER TABLE `ezdfsfile_cache` DROP KEY `ezdfsfile_expired_name`;
ALTER TABLE `ezdfsfile_cache` ADD KEY `ezdfsfile_expired_name` (`expired`, `name` (191));

Beware also that these upgrade statements may fail due to index collisions. This is because the indexes have been shortened, so duplicates may occur. If that happens, you must remove the duplicates manually, and then repeat the statements that failed.

After successfully shortening the indexes, you should change the table character set, and update your platform/legacy settings accordingly. The 7.2 kernel upgrade documentation describes the steps.
Please see: https://github.com/ezsystems/ezpublish-kernel/blob/5f2a94517267298fba58e066420107d112721bd3/doc/upgrade/7.2.md#mysqlmariadb-database-tables-character-set-change

For legacy, if you encounter errors of this kind:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '???????????????'
...then you will also need to apply the following fix:
https://github.com/ezsystems/ezpublish-legacy/commit/c1d42f751663af3d8731587363856a9868aa7723

Block 4-byte usernames in legacy

If you cannot (yet) change your character set to 'utf8mb4', and you use legacy, we strongly recommend to block the use of 4-byte usernames. An attacker could use 4-byte characters to bypass username validation in certain ways, which can result in usernames that are otherwise not allowed. This change can be made by adding the following in your site.ini configuration file:

[UserSettings]
# Enable these two lines if your MySQL database is not using the utf8mb4 character set.
UserNameValidationRegex[utf8mb4]=%(?:\xF0[\x90-\xBF][\x80-\xBF]{2}|[\xF1-\xF3][\x80-\xBF]{3}|\xF4[\x80-\x8F][\x80-\xBF]{2})%xs
UserNameValidationErrorText[utf8mb4]=The username cannot contain 4-byte characters.

If you come across a security issue in our products, here is how you can report it to us: https://doc.ez.no/Security

36 542 Users on board!

Community Project menu

Proudly Developed with from