SegmentExpression.php
421 lines
| 1 | <?php |
| 2 | |
| 3 | /** |
| 4 | * Matomo - free/libre analytics platform |
| 5 | * |
| 6 | * @link https://matomo.org |
| 7 | * @license https://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later |
| 8 | */ |
| 9 | namespace Piwik\Segment; |
| 10 | |
| 11 | use Exception; |
| 12 | class SegmentExpression |
| 13 | { |
| 14 | public const AND_DELIMITER = ';'; |
| 15 | public const OR_DELIMITER = ','; |
| 16 | public const MATCH_EQUAL = '=='; |
| 17 | public const MATCH_NOT_EQUAL = '!='; |
| 18 | public const MATCH_GREATER_OR_EQUAL = '>='; |
| 19 | public const MATCH_LESS_OR_EQUAL = '<='; |
| 20 | public const MATCH_GREATER = '>'; |
| 21 | public const MATCH_LESS = '<'; |
| 22 | public const MATCH_CONTAINS = '=@'; |
| 23 | public const MATCH_DOES_NOT_CONTAIN = '!@'; |
| 24 | public const MATCH_STARTS_WITH = '=^'; |
| 25 | public const MATCH_ENDS_WITH = '=$'; |
| 26 | public const BOOL_OPERATOR_OR = 'OR'; |
| 27 | public const BOOL_OPERATOR_AND = 'AND'; |
| 28 | public const BOOL_OPERATOR_END = ''; |
| 29 | // Note: you can't write this in the API, but access this feature |
| 30 | // via field!= <- IS NOT NULL |
| 31 | // or via field== <- IS NULL / empty |
| 32 | public const MATCH_IS_NOT_NULL_NOR_EMPTY = '::NOT_NULL'; |
| 33 | public const MATCH_IS_NULL_OR_EMPTY = '::NULL'; |
| 34 | // Special case, since we look up Page URLs/Page titles in a sub SQL query |
| 35 | public const MATCH_ACTIONS_CONTAINS = 'IN'; |
| 36 | public const MATCH_ACTIONS_NOT_CONTAINS = 'NOTIN'; |
| 37 | /** |
| 38 | * A special match type for segments that require rejecting a visit if any action/conversion/etc. in the visit matches a condition. |
| 39 | * These operands result in `idvisit NOT IN (...)` subqueries. |
| 40 | */ |
| 41 | public const MATCH_IDVISIT_NOT_IN = 'IDVISIT_NOTIN'; |
| 42 | public const INDEX_OPERAND_NAME = 0; |
| 43 | public const INDEX_OPERAND_OPERATOR = 1; |
| 44 | public const INDEX_OPERAND_VALUE = 2; |
| 45 | public const INDEX_OPERAND_JOIN_COLUMN = 3; |
| 46 | public const INDEX_OPERAND_SEGMENT_INFO = 4; |
| 47 | public const SQL_WHERE_DO_NOT_MATCH_ANY_ROW = "(1 = 0)"; |
| 48 | public const SQL_WHERE_MATCHES_ALL_ROWS = "(1 = 1)"; |
| 49 | protected $string; |
| 50 | protected $valuesBind = []; |
| 51 | protected $tree = []; |
| 52 | protected $parsedSubExpressions = []; |
| 53 | public function __construct($string) |
| 54 | { |
| 55 | $this->string = $string; |
| 56 | $this->tree = $this->parseTree(); |
| 57 | } |
| 58 | public function getSegmentDefinition() |
| 59 | { |
| 60 | return $this->string; |
| 61 | } |
| 62 | public function isEmpty() |
| 63 | { |
| 64 | return count($this->tree) == 0; |
| 65 | } |
| 66 | public function getSubExpressionCount() |
| 67 | { |
| 68 | $count = 0; |
| 69 | foreach ($this->parsedSubExpressions as $orExpressions) { |
| 70 | foreach ($orExpressions as $operand) { |
| 71 | $isExpressionColumnPresent = !empty($operand[self::INDEX_OPERAND_NAME]); |
| 72 | if ($isExpressionColumnPresent) { |
| 73 | ++$count; |
| 74 | } |
| 75 | } |
| 76 | } |
| 77 | return $count; |
| 78 | } |
| 79 | /** |
| 80 | * Given the array of parsed filters containing, for each filter, |
| 81 | * the boolean operator (AND/OR) and the operand, |
| 82 | * Will return the array where the filters are in SQL representation |
| 83 | * |
| 84 | * @throws Exception |
| 85 | * @return array |
| 86 | */ |
| 87 | public function parseSubExpressions() |
| 88 | { |
| 89 | $parsedSubExpressions = array_map(function (array $orExpressions) { |
| 90 | return array_map(function (string $operand) { |
| 91 | return $this->parseOperand($operand); |
| 92 | }, $orExpressions); |
| 93 | }, $this->tree); |
| 94 | $this->parsedSubExpressions = $parsedSubExpressions; |
| 95 | return $parsedSubExpressions; |
| 96 | } |
| 97 | private function parseOperand(string $operand) : array |
| 98 | { |
| 99 | $operand = urldecode($operand); |
| 100 | $pattern = '/^(.+?)(' . self::MATCH_EQUAL . '|' . self::MATCH_NOT_EQUAL . '|' . self::MATCH_GREATER_OR_EQUAL . '|' . self::MATCH_GREATER . '|' . self::MATCH_LESS_OR_EQUAL . '|' . self::MATCH_LESS . '|' . self::MATCH_CONTAINS . '|' . self::MATCH_DOES_NOT_CONTAIN . '|' . preg_quote(self::MATCH_STARTS_WITH) . '|' . preg_quote(self::MATCH_ENDS_WITH) . '){1}(.*)/'; |
| 101 | $match = preg_match($pattern, $operand, $matches); |
| 102 | if ($match == 0) { |
| 103 | throw new Exception('The segment condition \'' . $operand . '\' is not valid.'); |
| 104 | } |
| 105 | $leftMember = $matches[1]; |
| 106 | $operation = $matches[2]; |
| 107 | $valueRightMember = urldecode($matches[3]); |
| 108 | // is null / is not null |
| 109 | if ($valueRightMember === '') { |
| 110 | if ($operation == self::MATCH_NOT_EQUAL) { |
| 111 | $operation = self::MATCH_IS_NOT_NULL_NOR_EMPTY; |
| 112 | } elseif ($operation == self::MATCH_EQUAL) { |
| 113 | $operation = self::MATCH_IS_NULL_OR_EMPTY; |
| 114 | } else { |
| 115 | throw new Exception('The segment \'' . $operand . '\' has no value specified. You can leave this value empty ' . 'only when you use the operators: ' . self::MATCH_NOT_EQUAL . ' (is not) or ' . self::MATCH_EQUAL . ' (is)'); |
| 116 | } |
| 117 | } |
| 118 | return [self::INDEX_OPERAND_NAME => $leftMember, self::INDEX_OPERAND_OPERATOR => $operation, self::INDEX_OPERAND_VALUE => $valueRightMember]; |
| 119 | } |
| 120 | /** |
| 121 | * Set the given expression |
| 122 | * @param $parsedSubExpressions |
| 123 | */ |
| 124 | public function setSubExpressionsAfterCleanup($parsedSubExpressions) |
| 125 | { |
| 126 | $this->parsedSubExpressions = $parsedSubExpressions; |
| 127 | } |
| 128 | /** |
| 129 | * @param array $availableTables |
| 130 | */ |
| 131 | public function parseSubExpressionsIntoSqlExpressions(&$availableTables = array()) |
| 132 | { |
| 133 | $this->valuesBind = []; |
| 134 | $sqlSubExpressions = array_map(function (array $orExpressions) use(&$availableTables) { |
| 135 | return array_map(function (array $operandDefinition) use(&$availableTables) { |
| 136 | $operand = $this->getSqlMatchFromDefinition($operandDefinition, $availableTables); |
| 137 | if ($operand[self::INDEX_OPERAND_OPERATOR] !== null) { |
| 138 | if (is_array($operand[self::INDEX_OPERAND_OPERATOR])) { |
| 139 | $this->valuesBind = array_merge($this->valuesBind, $operand[self::INDEX_OPERAND_OPERATOR]); |
| 140 | } else { |
| 141 | $this->valuesBind[] = $operand[self::INDEX_OPERAND_OPERATOR]; |
| 142 | } |
| 143 | } |
| 144 | $operand = $operand[self::INDEX_OPERAND_NAME]; |
| 145 | return $operand; |
| 146 | }, $orExpressions); |
| 147 | }, $this->parsedSubExpressions); |
| 148 | $this->tree = $sqlSubExpressions; |
| 149 | } |
| 150 | /** |
| 151 | * Given an array representing one filter operand ( left member , operation , right member) |
| 152 | * Will return an array containing |
| 153 | * - the SQL substring, |
| 154 | * - the values to bind to this substring |
| 155 | * |
| 156 | * @param array $def |
| 157 | * @param array $availableTables |
| 158 | * @throws Exception |
| 159 | * @return array |
| 160 | */ |
| 161 | protected function getSqlMatchFromDefinition($def, &$availableTables) |
| 162 | { |
| 163 | $field = $def[self::INDEX_OPERAND_NAME]; |
| 164 | $matchType = $def[self::INDEX_OPERAND_OPERATOR]; |
| 165 | $value = $def[self::INDEX_OPERAND_VALUE]; |
| 166 | $join = $def[self::INDEX_OPERAND_JOIN_COLUMN] ?? null; |
| 167 | $segment = $def[self::INDEX_OPERAND_SEGMENT_INFO] ?? null; |
| 168 | // Note: we save the SQL used in the subquery (if there is one), as we don't want to run |
| 169 | // self::checkFieldIsAvailable() on it. |
| 170 | $innerSql = null; |
| 171 | // Segment::getCleanedExpression() may return array(null, $matchType, null) |
| 172 | $operandWillNotMatchAnyRow = empty($field) && is_null($value); |
| 173 | if ($operandWillNotMatchAnyRow) { |
| 174 | if ($matchType == self::MATCH_EQUAL) { |
| 175 | // eg. pageUrl==DoesNotExist |
| 176 | // Equal to NULL means it will match none |
| 177 | $sqlExpression = self::SQL_WHERE_DO_NOT_MATCH_ANY_ROW; |
| 178 | } elseif ($matchType == self::MATCH_NOT_EQUAL) { |
| 179 | // eg. pageUrl!=DoesNotExist |
| 180 | // Not equal to NULL means it matches all rows |
| 181 | $sqlExpression = self::SQL_WHERE_MATCHES_ALL_ROWS; |
| 182 | } elseif ($matchType == self::MATCH_CONTAINS || $matchType == self::MATCH_DOES_NOT_CONTAIN || $matchType == self::MATCH_STARTS_WITH || $matchType == self::MATCH_ENDS_WITH) { |
| 183 | // no action was found for CONTAINS / DOES NOT CONTAIN |
| 184 | // eg. pageUrl=@DoesNotExist -> matches no row |
| 185 | // eg. pageUrl!@DoesNotExist -> matches no rows |
| 186 | $sqlExpression = self::SQL_WHERE_DO_NOT_MATCH_ANY_ROW; |
| 187 | } else { |
| 188 | // it is not expected to reach this code path |
| 189 | throw new Exception("Unexpected match type {$matchType} for your segment. " . "Please report this issue to the Matomo team with the segment you are using."); |
| 190 | } |
| 191 | return array($sqlExpression, $value = null); |
| 192 | } |
| 193 | $alsoMatchNULLValues = \false; |
| 194 | switch ($matchType) { |
| 195 | case self::MATCH_EQUAL: |
| 196 | $sqlMatch = '%s ='; |
| 197 | break; |
| 198 | case self::MATCH_NOT_EQUAL: |
| 199 | $sqlMatch = '%s <>'; |
| 200 | $alsoMatchNULLValues = \true; |
| 201 | break; |
| 202 | case self::MATCH_GREATER: |
| 203 | $sqlMatch = '%s >'; |
| 204 | break; |
| 205 | case self::MATCH_LESS: |
| 206 | $sqlMatch = '%s <'; |
| 207 | break; |
| 208 | case self::MATCH_GREATER_OR_EQUAL: |
| 209 | $sqlMatch = '%s >='; |
| 210 | break; |
| 211 | case self::MATCH_LESS_OR_EQUAL: |
| 212 | $sqlMatch = '%s <='; |
| 213 | break; |
| 214 | case self::MATCH_CONTAINS: |
| 215 | $sqlMatch = '%s LIKE'; |
| 216 | $value = '%' . $this->escapeLikeString($value) . '%'; |
| 217 | break; |
| 218 | case self::MATCH_DOES_NOT_CONTAIN: |
| 219 | $sqlMatch = '%s NOT LIKE'; |
| 220 | $value = '%' . $this->escapeLikeString($value) . '%'; |
| 221 | $alsoMatchNULLValues = \true; |
| 222 | break; |
| 223 | case self::MATCH_STARTS_WITH: |
| 224 | $sqlMatch = '%s LIKE'; |
| 225 | $value = $this->escapeLikeString($value) . '%'; |
| 226 | break; |
| 227 | case self::MATCH_ENDS_WITH: |
| 228 | $sqlMatch = '%s LIKE'; |
| 229 | $value = '%' . $this->escapeLikeString($value); |
| 230 | break; |
| 231 | case self::MATCH_IS_NOT_NULL_NOR_EMPTY: |
| 232 | $sqlMatch = '%s IS NOT NULL AND %s <> \'\' AND %s <> \'0\''; |
| 233 | $value = null; |
| 234 | break; |
| 235 | case self::MATCH_IS_NULL_OR_EMPTY: |
| 236 | $sqlMatch = '%s IS NULL OR %s = \'\' OR %s = \'0\''; |
| 237 | $value = null; |
| 238 | break; |
| 239 | case self::MATCH_ACTIONS_CONTAINS: |
| 240 | // this match type is not accessible from the outside |
| 241 | // (it won't be matched in self::parseSubExpressions()) |
| 242 | // it can be used internally to inject sub-expressions into the query. |
| 243 | // see Segment::getCleanedExpression() |
| 244 | $innerSql = $value['SQL']; |
| 245 | $sqlMatch = '%s IN (%innerSql%)'; |
| 246 | $value = $value['bind']; |
| 247 | break; |
| 248 | case self::MATCH_ACTIONS_NOT_CONTAINS: |
| 249 | // this match type is not accessible from the outside |
| 250 | // (it won't be matched in self::parseSubExpressions()) |
| 251 | // it can be used internally to inject sub-expressions into the query. |
| 252 | // see Segment::getCleanedExpression() |
| 253 | $innerSql = $value['sql']; |
| 254 | $sqlMatch = '%s NOT IN (%innerSql%)'; |
| 255 | $value = $value['bind']; |
| 256 | break; |
| 257 | default: |
| 258 | throw new Exception("Filter contains the match type '" . $matchType . "' which is not supported"); |
| 259 | break; |
| 260 | } |
| 261 | // We match NULL values when rows are excluded only when we are not doing a |
| 262 | $alsoMatchNULLValues = $alsoMatchNULLValues && !empty($value); |
| 263 | if ($matchType === self::MATCH_ACTIONS_CONTAINS || $matchType === self::MATCH_ACTIONS_NOT_CONTAINS || is_null($value)) { |
| 264 | $sqlExpression = "( {$sqlMatch} )"; |
| 265 | } else { |
| 266 | if ($alsoMatchNULLValues) { |
| 267 | $sqlExpression = "( {$field} IS NULL OR {$sqlMatch} ? )"; |
| 268 | } else { |
| 269 | $sqlExpression = "{$sqlMatch} ?"; |
| 270 | } |
| 271 | } |
| 272 | if (!empty($join['field'])) { |
| 273 | $sqlExpression = str_replace('%s', $join['field'], $sqlExpression); |
| 274 | } else { |
| 275 | $sqlExpression = str_replace('%s', $field, $sqlExpression); |
| 276 | } |
| 277 | if (!empty($join['discriminator'])) { |
| 278 | $sqlExpression = '(' . $sqlExpression . ' AND ' . $join['discriminator'] . ')'; |
| 279 | } |
| 280 | $columns = self::parseColumnsFromSqlExpr($sqlExpression); |
| 281 | foreach ($columns as $column) { |
| 282 | $this->checkFieldIsAvailable($column, $availableTables, $join); |
| 283 | } |
| 284 | if (!empty($join['field'])) { |
| 285 | $this->checkFieldIsAvailable($join['field'], $availableTables, $join); |
| 286 | } |
| 287 | if (!empty($join['joinOn'])) { |
| 288 | $joinOnColumns = self::parseColumnsFromSqlExpr($join['joinOn']); |
| 289 | foreach ($joinOnColumns as $column) { |
| 290 | $this->checkFieldIsAvailable($column, $availableTables, $join); |
| 291 | } |
| 292 | } |
| 293 | if ($innerSql) { |
| 294 | $sqlExpression = str_replace('%innerSql%', $innerSql, $sqlExpression); |
| 295 | } |
| 296 | return array($sqlExpression, $value); |
| 297 | } |
| 298 | /** |
| 299 | * @param string $field |
| 300 | * @return string[] |
| 301 | */ |
| 302 | public static function parseColumnsFromSqlExpr($field) |
| 303 | { |
| 304 | preg_match_all('/[^@a-zA-Z0-9_]?`?([@a-zA-Z_][@a-zA-Z0-9_]*`?\\.`?[a-zA-Z0-9_`]+)`?\\b/', $field, $matches); |
| 305 | $result = isset($matches[1]) ? $matches[1] : []; |
| 306 | // remove uses of session vars |
| 307 | $result = array_filter($result, function ($value) { |
| 308 | return strpos($value, '@') === \false; |
| 309 | }); |
| 310 | $result = array_map(function ($item) { |
| 311 | return str_replace('`', '', $item); |
| 312 | }, $result); |
| 313 | $result = array_unique($result); |
| 314 | $result = array_values($result); |
| 315 | return $result; |
| 316 | } |
| 317 | /** |
| 318 | * Check whether the field is available |
| 319 | * If not, add it to the available tables |
| 320 | * |
| 321 | * @param string $field |
| 322 | * @param array $availableTables |
| 323 | */ |
| 324 | private function checkFieldIsAvailable($field, &$availableTables, $join) |
| 325 | { |
| 326 | $fieldParts = explode('.', $field); |
| 327 | $table = count($fieldParts) == 2 ? $fieldParts[0] : \false; |
| 328 | // remove sql functions from field name |
| 329 | // example: `HOUR(log_visit.visit_last_action_time)` gets `HOUR(log_visit` => remove `HOUR(` |
| 330 | $table = preg_replace('/^[A-Z_]+\\(/', '', $table); |
| 331 | $tableExists = !$table || in_array($table, $availableTables); |
| 332 | if ($tableExists) { |
| 333 | return; |
| 334 | } |
| 335 | if (is_array($availableTables)) { |
| 336 | foreach ($availableTables as $availableTable) { |
| 337 | if (is_array($availableTable)) { |
| 338 | if (!isset($availableTable['tableAlias']) && $availableTable['table'] === $table) { |
| 339 | return; |
| 340 | } elseif (isset($availableTable['tableAlias']) && $availableTable['tableAlias'] === $table) { |
| 341 | return; |
| 342 | } |
| 343 | } |
| 344 | } |
| 345 | } |
| 346 | if ($join && (empty($join['tableAlias']) && $table == $join['table'] || $table == $join['tableAlias'])) { |
| 347 | $availableTables[] = $join; |
| 348 | } else { |
| 349 | $availableTables[] = $table; |
| 350 | } |
| 351 | } |
| 352 | /** |
| 353 | * Escape the characters % and _ in the given string |
| 354 | * @param string $str |
| 355 | * @return string |
| 356 | */ |
| 357 | private function escapeLikeString($str) |
| 358 | { |
| 359 | if (\false !== strpos($str, '%')) { |
| 360 | $str = str_replace("%", "\\%", $str); |
| 361 | } |
| 362 | if (\false !== strpos($str, '_')) { |
| 363 | $str = str_replace("_", "\\_", $str); |
| 364 | } |
| 365 | return $str; |
| 366 | } |
| 367 | /** |
| 368 | * Given a segment string, will parse it into a multi-level array with the first level representing AND groups |
| 369 | * and the second level containing OR operands |
| 370 | * |
| 371 | * eg. the segment string 'A,B;C,D,E;F' will return: |
| 372 | * |
| 373 | * [ |
| 374 | * 0 => [0 => 'A', 1 => 'B'], // First AND group containing A and B OR conditions |
| 375 | * 1 => [0 => 'C', 1 => 'D', 2 => 'E'], // Second AND group containing C, D & E |
| 376 | * 2 => [0 => 'F'] // Third AND group containing just F |
| 377 | * ] |
| 378 | * |
| 379 | * @return array |
| 380 | */ |
| 381 | protected function parseTree() |
| 382 | { |
| 383 | $segmentStr = trim($this->string); |
| 384 | if (empty($segmentStr)) { |
| 385 | return []; |
| 386 | } |
| 387 | // split on ; only when there's no end of string after and only when there's no backslash before it |
| 388 | $ands = array_filter(preg_split('/' . self::AND_DELIMITER . '(?!$)(?<!\\\\' . self::AND_DELIMITER . ')/', $segmentStr)); |
| 389 | return array_map(function ($and) { |
| 390 | // split on , only when there's no end of string after and only when there's no backslash before it |
| 391 | $ors = preg_split('/' . self::OR_DELIMITER . '(?!$)(?<!\\\\' . self::OR_DELIMITER . ')/', $and); |
| 392 | // remove backslash from in front of ; and , |
| 393 | return array_map(function ($or) { |
| 394 | return str_replace(['\\' . self::AND_DELIMITER, '\\' . self::OR_DELIMITER], [self::AND_DELIMITER, self::OR_DELIMITER], $or); |
| 395 | }, $ors); |
| 396 | }, $ands); |
| 397 | } |
| 398 | /** |
| 399 | * Given the array of parsed boolean logic, will return |
| 400 | * an array containing the full SQL string representing the filter and the values to bind to the query |
| 401 | * |
| 402 | * @throws Exception |
| 403 | * @return array SQL Query and Bind parameters |
| 404 | */ |
| 405 | public function getSql() |
| 406 | { |
| 407 | if ($this->isEmpty()) { |
| 408 | throw new Exception("Invalid segment, please specify a valid segment."); |
| 409 | } |
| 410 | $andExpressions = $this->tree; |
| 411 | $andExpressions = array_map(function ($orExpressions) { |
| 412 | if (count($orExpressions) == 1) { |
| 413 | return $orExpressions[0]; |
| 414 | } |
| 415 | return '( ' . implode(' OR ', $orExpressions) . ')'; |
| 416 | }, $andExpressions); |
| 417 | $sql = implode(' AND ', $andExpressions); |
| 418 | return array('where' => $sql, 'bind' => $this->valuesBind); |
| 419 | } |
| 420 | } |
| 421 |