CRM/Upgrade/Incremental/php/FiveThirtyFour.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  * Upgrade logic for FiveThirtyFour */
  14 class CRM_Upgrade_Incremental_php_FiveThirtyFour extends CRM_Upgrade_Incremental_Base {
  15
  16   /**
  17    * Compute any messages which should be displayed beforeupgrade.
  18    *
  19    * Note: This function is called iteratively for each upcoming
  20    * revision to the database.
  21    *
  22    * @param string $preUpgradeMessage
  23    * @param string $rev
  24    *   a version number, e.g. '4.4.alpha1', '4.4.beta3', '4.4.0'.
  25    * @param null $currentVer
  26    */
  27   public function setPreUpgradeMessage(&$preUpgradeMessage, $rev, $currentVer = NULL) {
  28     if ($rev === '5.34.alpha1') {
  29       $xoauth2Value = CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_MailSettings', 'protocol', 'IMAP_XOAUTH2');
  30       if (!empty($xoauth2Value)) {
  31         if ($this->isXOAUTH2InUse($xoauth2Value)) {
  32           $preUpgradeMessage .= '<p>' . $this->getXOAuth2Warning() . '</p>';
  33         }
  34       }
  35     }
  36
  37     if ($rev === '5.34.alpha1') {
  38       if (extension_loaded('mcrypt') && !empty(self::findSmtpPasswords())) {
  39         // NOTE: We don't re-encrypt automatically because the old "civicrm.settings.php" lacks a good key, and we don't keep the old encryption because the format is ambiguous.
  40         // The admin may forget to re-enable. That's OK -- this only affects 1 field, this is a secondary defense, and (in the future) we can remind the admin via status-checks.
  41         $preUpgradeMessage .= '<p>' . ts('This system has an SMTP password which should be migrated to a new encryption mechanism. The upgrader will remove the old encryption. After upgrading, you may enable the new encryption.') . '</p>';
  42       }
  43       foreach ($GLOBALS['civicrm_setting'] ?? [] as $entity => $overrides) {
  44         if (extension_loaded('mcrypt') && !empty($overrides['mailing_backend']['smtpPassword']) && $overrides['mailing_backend']['outBound_option'] == 0) {
  45           // This is a fairly unlikely situation. I'm sure it's *useful* to set smtpPassword via $civicrm_setting (eg for dev or multitenant).
  46           // But historically it had to follow the rules of CRM_Utils_Crypt:
  47           // - For non-mcrypt servers, that was easy/plaintext. That'll work just as well going forward. We don't show any warnings about that.
  48           // - For mcrypt servers, the value had to be encrypted. It's not easy to pick the right value for that. Maybe someone with multitenant would have had
  49           //   enough incentive to figure this out... but they'd probably get stymied by the fact that each tenant has a different SITE_KEY.
  50           // All of which is to say: if someone has gotten into a valid+working scenario of overriding smtpPassword on an mcrypt-enabled system, then they're
  51           // savvy enough to figure out the migration details. We just need to point them at the problem.
  52           $settingPath = sprintf('$civicrm_setting[%s][%s][%s]', var_export($entity, 1), var_export('mailing_backend', 1), var_export('smtpPassword', 1));
  53           $prose = ts('This system has a PHP override for the SMTP password (%1). The override was most likely encrypted with an old mechanism. After upgrading, you must verify and/or revise this setting.', [
  54             1 => $settingPath,
  55           ]);
  56           $preUpgradeMessage = '<p>' . $prose . '</p>';
  57         }
  58       }
  59     }
  60   }
  61
  62   /**
  63    * Compute any messages which should be displayed after upgrade.
  64    *
  65    * @param string $postUpgradeMessage
  66    *   alterable.
  67    * @param string $rev
  68    *   an intermediate version; note that setPostUpgradeMessage is called repeatedly with different $revs.
  69    */
  70   public function setPostUpgradeMessage(&$postUpgradeMessage, $rev) {
  71     if ($rev === '5.34.alpha1') {
  72       $xoauth2Value = CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_MailSettings', 'protocol', 'IMAP_XOAUTH2');
  73       if (!empty($xoauth2Value)) {
  74         if ($this->isXOAUTH2InUse($xoauth2Value)) {
  75           $postUpgradeMessage .= '<div class="crm-error"><ul><li>' . $this->getXOAuth2Warning() . '</li></ul></div>';
  76         }
  77       }
  78     }
  79   }
  80
  81   /*
  82    * Important! All upgrade functions MUST add a 'runSql' task.
  83    * Uncomment and use the following template for a new upgrade version
  84    * (change the x in the function name):
  85    */
  86
  87   /**
  88    * Upgrade function.
  89    *
  90    * @param string $rev
  91    */
  92   public function upgrade_5_34_alpha1(string $rev): void {
  93     $this->addTask(ts('Upgrade DB to %1: SQL', [1 => $rev]), 'runSql', $rev);
  94
  95     if (extension_loaded('mcrypt') && !empty(self::findSmtpPasswords())) {
  96       $this->addTask('Migrate SMTP password', 'migrateSmtpPasswords');
  97     }
  98
  99     $this->addTask('core-issue#365 - Add created_date to civicrm_action_schedule', 'addColumn',
 100       'civicrm_action_schedule', 'created_date', "timestamp NULL  DEFAULT CURRENT_TIMESTAMP COMMENT 'When was the schedule reminder created.'");
 101
 102     $this->addTask('core-issue#365 - Add modified_date to civicrm_action_schedule', 'addColumn',
 103       'civicrm_action_schedule', 'modified_date', "timestamp NULL  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'When was the schedule reminder created.'");
 104
 105     $this->addTask('core-issue#365 - Add effective_start_date to civicrm_action_schedule', 'addColumn',
 106       'civicrm_action_schedule', 'effective_start_date', "timestamp NULL COMMENT 'Earliest date to consider start events from.'");
 107
 108     $this->addTask('core-issue#365 - Add effective_end_date to civicrm_action_schedule', 'addColumn',
 109       'civicrm_action_schedule', 'effective_end_date', "timestamp NULL COMMENT 'Latest date to consider end events from.'");
 110
 111     $this->addTask('Set defaults and required on financial type boolean fields', 'updateFinancialTypeTable');
 112     $this->addTask('Set defaults and required on pledge fields', 'updatePledgeTable');
 113
 114     $this->addTask('Remove never used IMAP_XOAUTH2 option value', 'removeUnusedXOAUTH2');
 115   }
 116
 117   /**
 118    * @return array
 119    *   A list of "civicrm_setting" records which have
 120    *   SMTP passwords, or NULL.
 121    */
 122   protected static function findSmtpPasswords() {
 123     $query = CRM_Utils_SQL_Select::from('civicrm_setting')
 124       ->where('name = "mailing_backend"');
 125
 126     $matches = [];
 127     foreach ($query->execute()->fetchAll() as $setting) {
 128       $value = unserialize($setting['value']);
 129       if (!empty($value['smtpPassword'])) {
 130         $matches[] = $setting;
 131       }
 132     }
 133
 134     return $matches;
 135   }
 136
 137   /**
 138    * Find any SMTP passwords. Remove the CRM_Utils_Crypt encryption.
 139    *
 140    * Note: This task is only enqueued if mcrypt is active.
 141    *
 142    * @param \CRM_Queue_TaskContext $ctx
 143    *
 144    * @return bool
 145    */
 146   public static function migrateSmtpPasswords(CRM_Queue_TaskContext $ctx) {
 147     $settings = self::findSmtpPasswords();
 148     foreach ($settings as $setting) {
 149       $value = unserialize($setting['value']);
 150       $value['smtpPassword'] = CRM_Utils_Crypt::decrypt($value['smtpPassword']);
 151       CRM_Core_DAO::executeQuery('UPDATE civicrm_setting SET value = %2 WHERE id = %1', [
 152         1 => [$setting['id'], 'Positive'],
 153         2 => [serialize($value), 'String'],
 154       ]);
 155     }
 156     return TRUE;
 157   }
 158
 159   /**
 160    * Update financial type table to reflect recent schema changes.
 161    *
 162    * @param \CRM_Queue_TaskContext $ctx
 163    *
 164    * @return bool
 165    */
 166   public static function updateFinancialTypeTable(CRM_Queue_TaskContext $ctx): bool {
 167     // Make sure there are no existing NULL values in the fields we are about to make required.
 168     CRM_Core_DAO::executeQuery('
 169       UPDATE civicrm_financial_type
 170       SET is_active = COALESCE(is_active, 0),
 171           is_reserved = COALESCE(is_reserved, 0),
 172           is_deductible = COALESCE(is_deductible, 0)
 173       WHERE is_reserved IS NULL OR is_active IS NULL OR is_deductible IS NULL
 174     ');
 175     CRM_Core_DAO::executeQuery("
 176       ALTER TABLE civicrm_financial_type
 177       MODIFY COLUMN `is_deductible` tinyint(4) DEFAULT 0 NOT NULL COMMENT 'Is this financial type tax-deductible? If true, contributions of this type may be fully OR partially deductible - non-deductible amount is stored in the Contribution record.',
 178       MODIFY COLUMN `is_reserved` tinyint(4) DEFAULT 0 NOT NULL COMMENT 'Is this a predefined system object?',
 179       MODIFY COLUMN `is_active` tinyint(4) DEFAULT 1 NOT NULL COMMENT 'Is this property active?'
 180     ");
 181
 182     return TRUE;
 183   }
 184
 185   /**
 186    * Update pledge table to reflect recent schema changes making fields required.
 187    *
 188    * @param \CRM_Queue_TaskContext $ctx
 189    *
 190    * @return bool
 191    */
 192   public static function updatePledgeTable(CRM_Queue_TaskContext $ctx): bool {
 193     // Make sure there are no existing NULL values in the fields we are about to make required.
 194     CRM_Core_DAO::executeQuery('
 195       UPDATE civicrm_pledge
 196       SET is_test = COALESCE(is_test, 0),
 197           frequency_unit = COALESCE(frequency_unit, "month"),
 198           # Cannot imagine this would be null but if it were...
 199           installments = COALESCE(installments, 0),
 200           # this does not seem plausible either.
 201           status_id = COALESCE(status_id, 1)
 202       WHERE is_test IS NULL OR frequency_unit IS NULL OR installments IS NULL OR status_id IS NULL
 203     ');
 204     CRM_Core_DAO::executeQuery("
 205       ALTER TABLE civicrm_pledge
 206       MODIFY COLUMN `frequency_unit` varchar(8) DEFAULT 'month' NOT NULL COMMENT 'Time units for recurrence of pledge payments.',
 207       MODIFY COLUMN `installments` int(10) unsigned DEFAULT 1 NOT NULL COMMENT 'Total number of payments to be made.',
 208       MODIFY COLUMN `status_id` int(10) unsigned NOT NULL COMMENT 'Implicit foreign key to civicrm_option_values in the pledge_status option group.',
 209       MODIFY COLUMN `is_test` tinyint(4) DEFAULT 0 NOT NULL
 210     ");
 211     return TRUE;
 212   }
 213
 214   /**
 215    * This option value was never used, but check anyway if someone happens
 216    * to be using it and then ask them to report what they're doing with it.
 217    * There's no way to send a message to the user during the task, so we have
 218    * to check it here and also as a pre/post upgrade message.
 219    * Similar to removeGooglePlusOption from 5.23 except there we know some
 220    * people would have used it.
 221    */
 222   public static function removeUnusedXOAUTH2(CRM_Queue_TaskContext $ctx) {
 223     $xoauth2Value = CRM_Core_PseudoConstant::getKey('CRM_Core_BAO_MailSettings', 'protocol', 'IMAP_XOAUTH2');
 224     if (!empty($xoauth2Value)) {
 225       if (!self::isXOAUTH2InUse($xoauth2Value)) {
 226         CRM_Core_DAO::executeQuery("DELETE ov FROM civicrm_option_value ov
 227 INNER JOIN civicrm_option_group og
 228 ON (og.name = 'mail_protocol' AND ov.option_group_id = og.id)
 229 WHERE ov.value = %1",
 230           [1 => [$xoauth2Value, 'Positive']]);
 231       }
 232     }
 233     return TRUE;
 234   }
 235
 236   /**
 237    * Determine if option value is enabled or used in mail settings.
 238    * @return bool
 239    */
 240   private static function isXOAUTH2InUse($xoauth2Value) {
 241     $enabled = (bool) CRM_Core_DAO::SingleValueQuery("SELECT ov.is_active FROM civicrm_option_value ov
 242 INNER JOIN civicrm_option_group og
 243 ON (og.name = 'mail_protocol' AND ov.option_group_id = og.id)
 244 WHERE ov.value = %1",
 245       [1 => [$xoauth2Value, 'Positive']]);
 246     $usedInMailSettings = (bool) CRM_Core_DAO::SingleValueQuery("SELECT id FROM civicrm_mail_settings WHERE protocol = %1", [1 => [$xoauth2Value, 'Positive']]);
 247     return $enabled || $usedInMailSettings;
 248   }
 249
 250   /**
 251    * @return string
 252    */
 253   private function getXOAuth2Warning():string {
 254     // Leaving out ts() since it's unlikely this message will ever
 255     // be displayed to anyone.
 256     return strtr(
 257       'This system has enabled "IMAP_XOAUTH2" which was experimentally declared in CiviCRM v5.24. CiviCRM v5.33+ includes a supported replacement ("oauth-client"), and the experimental "IMAP_XOAUTH2" should be removed. Please visit %1 to discuss.',
 258       [
 259         '%1' => '<a target="_blank" href="https://lab.civicrm.org/dev/core/-/issues/2264">dev/core#2264</a>',
 260       ]
 261     );
 262   }
 263
 264 }