Enable MySQL Strict mode as best practise and security improvement

[mattab]

The goal of this issue is to enable MySQL strict mode in Piwik.

Why enabling Strict mode?

• MySQL strict mode is a setting that implements a best practise around data management
• MySQL strict mode results in better security for Piwik users. Why? Currently as we are not using Strict mode, sometimes values with special unicode characters could be automatically truncated before being inserted. Why is data truncation a possible security issue? when values are truncated before being inserted in the DB, this can open up the application to certain vulnerabilities such as XSS, under special circumstances. For example see this XSS in Wordpress (tldr; mysql → special characters → truncation → input validation → output sanitisation → xss → time to update WordPress.)

We would like to bring the best security practises to Piwik and strict mode would be a valuable security improvement.

Requirements

• Currently, some plugin's dimensions (database table columns) are not NULLAble. Therefore when inserting a tracking request and if one of these not NULLable dimensions does not have a value, the INSERT in MySQL would fail in strict mode, causing data loss.
  • When we previously added STRICT_TRANS_TABLES in Piwik, this data loss made us revert the change. Reported in https://github.com/piwik/piwik/issues/8853 and fixed in https://github.com/piwik/piwik/pull/8930/files
• To prevent any data loss in Strict Mode, we need to modify all tracker dimensions in core and in all plugins (log_*.* columns) to be NULLable. Covered in #9231 (Make all log_* tables fields NULLable to prevent errors "Field 'X' doesn't have a default value").
  • Maybe we could also preset the dimension's value to non NULL value, when a dimension's value is not defined, to prevent strict mode from issuing a warning and discarding the INSERT?
• To make sure any data loss or warning during tracker is reported to Piwik users/admins, we need a stronger error reporting mechanism to communicate such issues automatically to Piwik administrators. Covered in #7550 (How to detect any failure during Tracker requests and pro-actively inform Piwik admin of such error)

(also refs Require Mysql 5.5 #9107 and making utf8mb4 the collation by default #9785)

[Patta]

+1

[tsteur]

If you can think of a way to exploit this, please report the issue via https://hackerone.com/matomo/ for a bounty.