CRM/Utils/SQL.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  * Just another collection of static utils functions.
  14  *
  15  * @package CRM
  16  * @copyright CiviCRM LLC https://civicrm.org/licensing
  17  */
  18 class CRM_Utils_SQL {
  19
  20   /**
  21    * Given a string like "UPDATE some_table SET !field = @value", replace "!field" and "@value".
  22    *
  23    * This is syntactic sugar for using CRM_Utils_SQL_*::interpolate() without an OOP representation of the query.
  24    *
  25    * @param string $expr SQL expression
  26    * @param null|array $args a list of values to insert into the SQL expression; keys are prefix-coded:
  27    *   prefix '@' => escape SQL
  28    *   prefix '#' => literal number, skip escaping but do validation
  29    *   prefix '!' => literal, skip escaping and validation
  30    *   if a value is an array, then it will be imploded
  31    *
  32    * PHP NULL's will be treated as SQL NULL's. The PHP string "null" will be treated as a string.
  33    *
  34    * @return string
  35    */
  36   public static function interpolate($expr, $args) {
  37     if (!isset(Civi::$statics[__CLASS__][__FUNCTION__])) {
  38       Civi::$statics[__CLASS__][__FUNCTION__] = new class extends CRM_Utils_SQL_BaseParamQuery {
  39
  40         public function __construct() {
  41           $this->mode = CRM_Utils_SQL_BaseParamQuery::INTERPOLATE_INPUT;
  42           $this->strict();
  43         }
  44
  45       };
  46     }
  47     /** @var \CRM_Utils_SQL_BaseParamQuery $qb */
  48     $qb = Civi::$statics[__CLASS__][__FUNCTION__];
  49     return $qb->strict()->interpolate($expr, $args);
  50   }
  51
  52   /**
  53    * Helper function for adding the permissioned subquery from one entity onto another
  54    *
  55    * @param string $entity
  56    * @param string $joinColumn
  57    * @return array
  58    */
  59   public static function mergeSubquery($entity, $joinColumn = 'id') {
  60     require_once 'api/v3/utils.php';
  61     $baoName = _civicrm_api3_get_BAO($entity);
  62     $bao = new $baoName();
  63     $clauses = $subclauses = [];
  64     foreach ((array) $bao->addSelectWhereClause() as $field => $vals) {
  65       if ($vals && $field == $joinColumn) {
  66         $clauses = array_merge($clauses, (array) $vals);
  67       }
  68       elseif ($vals) {
  69         $subclauses[] = "$field " . implode(" AND $field ", (array) $vals);
  70       }
  71     }
  72     if ($subclauses) {
  73       $clauses[] = "IN (SELECT `$joinColumn` FROM `" . $bao->tableName() . "` WHERE " . implode(' AND ', $subclauses) . ")";
  74     }
  75     return $clauses;
  76   }
  77
  78   /**
  79    * Get current sqlModes of the session
  80    * @return array
  81    */
  82   public static function getSqlModes() {
  83     $sqlModes = explode(',', CRM_Core_DAO::singleValueQuery('SELECT @@sql_mode'));
  84     return $sqlModes;
  85   }
  86
  87   /**
  88    * Checks if this system enforce the MYSQL mode ONLY_FULL_GROUP_BY.
  89    * This function should be named supportsAnyValueAndEnforcesFullGroupBY(),
  90    * but should be deprecated instead.
  91    *
  92    * @return mixed
  93    * @deprecated
  94    */
  95   public static function supportsFullGroupBy() {
  96     // CRM-21455 MariaDB 10.2 does not support ANY_VALUE
  97     $version = self::getDatabaseVersion();
  98
  99     if (stripos($version, 'mariadb') !== FALSE) {
 100       return FALSE;
 101     }
 102
 103     return version_compare($version, '5.7', '>=');
 104   }
 105
 106   /**
 107    * Disable ONLY_FULL_GROUP_BY for MySQL versions lower then 5.7
 108    *
 109    * @return bool
 110    */
 111   public static function disableFullGroupByMode() {
 112     $sqlModes = self::getSqlModes();
 113
 114     // Disable only_full_group_by mode for lower sql versions.
 115     if (!self::supportsFullGroupBy() || (!empty($sqlModes) && !in_array('ONLY_FULL_GROUP_BY', $sqlModes))) {
 116       if ($key = array_search('ONLY_FULL_GROUP_BY', $sqlModes)) {
 117         unset($sqlModes[$key]);
 118         CRM_Core_DAO::executeQuery("SET SESSION sql_mode = '" . implode(',', $sqlModes) . "'");
 119       }
 120       return TRUE;
 121     }
 122
 123     return FALSE;
 124   }
 125
 126   /**
 127    * CHeck if ONLY_FULL_GROUP_BY is in the global sql_modes
 128    * @return bool
 129    */
 130   public static function isGroupByModeInDefault() {
 131     $sqlModes = explode(',', CRM_Core_DAO::singleValueQuery('SELECT @@global.sql_mode'));
 132     if (!in_array('ONLY_FULL_GROUP_BY', $sqlModes)) {
 133       return FALSE;
 134     }
 135     return TRUE;
 136   }
 137
 138   /**
 139    * Does the DB version support mutliple locks per
 140    *
 141    * https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_get-lock
 142    *
 143    * This is a conservative measure to introduce the change which we expect to deprecate later.
 144    *
 145    * @todo we only check mariadb & mysql right now but maybe can add percona.
 146    */
 147   public static function supportsMultipleLocks() {
 148     static $isSupportLocks = NULL;
 149     if (!isset($isSupportLocks)) {
 150       $version = self::getDatabaseVersion();
 151       if (stripos($version, 'mariadb') !== FALSE) {
 152         $isSupportLocks = version_compare($version, '10.0.2', '>=');
 153       }
 154       else {
 155         $isSupportLocks = version_compare($version, '5.7.5', '>=');
 156       }
 157     }
 158
 159     return $isSupportLocks;
 160   }
 161
 162   /**
 163    * Get the version string for the database.
 164    *
 165    * @return string
 166    */
 167   public static function getDatabaseVersion() {
 168     return CRM_Core_DAO::singleValueQuery('SELECT VERSION()');
 169   }
 170
 171   /**
 172    * Does the DSN indicate the connection should use ssl.
 173    *
 174    * @param string $dsn
 175    *
 176    * @return bool
 177    */
 178   public static function isSSLDSN(string $dsn):bool {
 179     // Note that ssl= below is not an official PEAR::DB option. It doesn't know
 180     // what to do with it. We made it up because it's not required
 181     // to have client-side certificates to use ssl, so here you can specify
 182     // you want that by putting ssl=1 in the DSN string.
 183     //
 184     // Cast to bool in case of error which we interpret as no ssl.
 185     return (bool) preg_match('/[\?&](key|cert|ca|capath|cipher|ssl)=/', $dsn);
 186   }
 187
 188   /**
 189    * If DB_DSN_MODE is auto then we should replace mysql with mysqli if mysqli is available or the other way around as appropriate
 190    * @param string $dsn
 191    *
 192    * @return string
 193    */
 194   public static function autoSwitchDSN($dsn) {
 195     if (defined('DB_DSN_MODE') && DB_DSN_MODE === 'auto') {
 196       if (extension_loaded('mysqli')) {
 197         $dsn = preg_replace('/^mysql:/', 'mysqli:', $dsn);
 198       }
 199       else {
 200         $dsn = preg_replace('/^mysqli:/', 'mysql:', $dsn);
 201       }
 202     }
 203     return $dsn;
 204   }
 205
 206 }