Commit | Line | Data |
---|---|---|
d1d3c04a CW |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
bc77d7c0 | 4 | | Copyright CiviCRM LLC. All rights reserved. | |
d1d3c04a | 5 | | | |
bc77d7c0 TO |
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 | | |
d1d3c04a CW |
9 | +--------------------------------------------------------------------+ |
10 | */ | |
11 | ||
12 | /** | |
13 | * Just another collection of static utils functions. | |
14 | * | |
15 | * @package CRM | |
ca5cec67 | 16 | * @copyright CiviCRM LLC https://civicrm.org/licensing |
d1d3c04a CW |
17 | */ |
18 | class CRM_Utils_SQL { | |
19 | ||
8d61954a TO |
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 | ||
d1d3c04a CW |
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') { | |
4f0e32e0 | 60 | require_once 'api/v3/utils.php'; |
d1d3c04a CW |
61 | $baoName = _civicrm_api3_get_BAO($entity); |
62 | $bao = new $baoName(); | |
be2fb01f | 63 | $clauses = $subclauses = []; |
d1d3c04a CW |
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 | ||
712e729f SL |
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 | /** | |
2c40ef6b ML |
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 | * | |
712e729f | 92 | * @return mixed |
2c40ef6b | 93 | * @deprecated |
712e729f SL |
94 | */ |
95 | public static function supportsFullGroupBy() { | |
2c40ef6b | 96 | // CRM-21455 MariaDB 10.2 does not support ANY_VALUE |
79ee443c | 97 | $version = self::getDatabaseVersion(); |
2c40ef6b | 98 | |
6454ff54 | 99 | if (stripos($version, 'mariadb') !== FALSE) { |
2c40ef6b ML |
100 | return FALSE; |
101 | } | |
102 | ||
103 | return version_compare($version, '5.7', '>='); | |
712e729f SL |
104 | } |
105 | ||
b07c960a | 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 | ||
a2ed62b3 SL |
126 | /** |
127 | * CHeck if ONLY_FULL_GROUP_BY is in the global sql_modes | |
128 | * @return bool | |
129 | */ | |
130 | public static function isGroupByModeInDefault() { | |
a2ed62b3 SL |
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 | ||
79ee443c | 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 | * | |
79ee443c | 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() { | |
79ee443c | 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 | ||
fa1e300b | 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 | ||
58d1e21e SL |
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 | ||
9787e1bb SL |
206 | /** |
207 | * Filter out Emojis in where clause if the database (determined by checking the create table for civicrm_contact) | |
208 | * cannot support emojis | |
209 | * @param mixed $criteria - filter criteria to check | |
210 | * | |
211 | * @return bool|string | |
212 | */ | |
213 | public static function handleEmojiInQuery($criteria) { | |
214 | if (!CRM_Core_BAO_SchemaHandler::databaseSupportsUTF8MB4()) { | |
215 | foreach ((array) $criteria as $criterion) { | |
216 | if (!empty($criterion) && !is_numeric($criterion) | |
217 | // The first 2 criteria are redundant but are added as they | |
218 | // seem like they would | |
219 | // be quicker than this 3rd check. | |
220 | && max(array_map('ord', str_split($criterion))) >= 240) { | |
221 | // String contains unsupported emojis. | |
222 | // We return a clause that resolves to false as an emoji string by definition cannot be saved. | |
223 | // note that if we return just 0 for false if gets lost in empty checks. | |
224 | // https://stackoverflow.com/questions/16496554/can-php-detect-4-byte-encoded-utf8-chars | |
225 | return '0 = 1'; | |
226 | } | |
227 | } | |
228 | return TRUE; | |
229 | } | |
230 | } | |
231 | ||
d1d3c04a | 232 | } |