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