[ticket/17665] Add automatic admin notifications on security updates - #7012

The attempt to merge branch 3.3.x into master has completed after considering the changes in this PR.

• Merge result: Conflict ❌

A separate PR will be needed to merge 3.3.x into master.

Some findings from looking into the red CI, verified locally against this branch (phpunit 9.6.35, MySQL 8 strict mode, PostgreSQL 16):

1. The whole matrix is red from one failure. migrations_check_config_added_test fails because the two new config options are missing from phpBB/install/schemas/schema_data.sql, and fail-fast then cancels the other jobs. Since the cron rewrites version_check_last_cron on every run, it should also be dynamic like the other cron timestamps (cron_lock, the *_last_gc ones), otherwise each run purges the config cache:

INSERT INTO phpbb_config (config_name, config_value) VALUES ('version_check_interval', '60');
INSERT INTO phpbb_config (config_name, config_value, is_dynamic) VALUES ('version_check_last_cron', '0', 1);

with the matching third argument in the migration:

['config.add', ['version_check_last_cron', 0, true]],

2. The generated item_id does not fit the column on any supported DBMS. In version_check::notify_admins():

'item_id' => (int) sprintf('%u', crc32($template . $update_data['current'])),

notifications.item_id is UINT, which is mediumint (max 16777215) on MySQL and INT4 (max 2147483647) on PostgreSQL. Nearly every crc32 value exceeds mediumint, so the insert fails with "Out of range value" under MySQL strict mode, and about half also exceed INT4 on PostgreSQL. The very next security notification on this branch would produce crc32('update_security' . '3.3.18') = 3943736323, which I verified fails the insert on both MySQL 8 and PostgreSQL 16. Masking the hash to 24 bits keeps it inside every mapping, including Oracle's number(8):

'item_id' => crc32($template . $update_data['current']) & 0xFFFFFF,

The hardcoded 1089886753 in test_run_with_updates becomes 16144929 with the mask. The tests do not catch this today because they only use small fixed ids.

3. The migration has no depends_on(), so the migrator is free to run it at any point in the tree:

public static function depends_on(): array
{
    return ['\phpbb\db\migration\data\v33x\v3317'];
}

4. Failed checks retry on every cron trigger. run() only sets version_check_last_cron on success, and get_update_on_branch(true) bypasses the versioncheck cache, so while the version server is unreachable every eligible cron trigger performs a blocking remote fetch. Setting the timestamp in the catch block as well avoids hammering both ends (test_run_with_exception would then assert greater than 0 instead of 0). There is also a leftover // @todo in that catch.

Minor: $db and $user are injected into the cron task but not used.

I'm having troubles testing this, current feedback

• Editing theversion_check_interval doesn't seem to help. Tried it on one minute and deleted cache multiple times.
• It only returns a notifcation, no mail by default
• After enabling an email notification it still doesn't send out a mail
• The content of the notification is not escaped, I can add HTML to the version that gets parsed

Updated the PR. Cron is working fine now and the default admin will receive the notification via mail by default. When running the migration, all admins with appropriate permissions should get the same setting. I also double checked and the version check data gets verified against the schema and no HTML can be added to inject HTML.