1<?php 2 3declare(strict_types=1); 4 5/* 6 * This file is part of the TYPO3 CMS project. 7 * 8 * It is free software; you can redistribute it and/or modify it under 9 * the terms of the GNU General Public License, either version 2 10 * of the License, or any later version. 11 * 12 * For the full copyright and license information, please read the 13 * LICENSE.txt file that was distributed with this source code. 14 * 15 * The TYPO3 project - inspiring people to share! 16 */ 17 18namespace TYPO3\CMS\Core\Database\Query\Expression; 19 20use Doctrine\DBAL\Platforms\TrimMode; 21use TYPO3\CMS\Core\Database\Connection; 22 23/** 24 * ExpressionBuilder class is responsible to dynamically create SQL query parts. 25 * 26 * It takes care building query conditions while ensuring table and column names 27 * are quoted within the created expressions / SQL fragments. It is a facade to 28 * the actual Doctrine ExpressionBuilder. 29 * 30 * The ExpressionBuilder is used within the context of the QueryBuilder to ensure 31 * queries are being build based on the requirements of the database platform in 32 * use. 33 */ 34class ExpressionBuilder 35{ 36 const EQ = '='; 37 const NEQ = '<>'; 38 const LT = '<'; 39 const LTE = '<='; 40 const GT = '>'; 41 const GTE = '>='; 42 43 const QUOTE_NOTHING = 0; 44 const QUOTE_IDENTIFIER = 1; 45 const QUOTE_PARAMETER = 2; 46 47 /** 48 * The DBAL Connection. 49 * 50 * @var Connection 51 */ 52 protected $connection; 53 54 /** 55 * Initializes a new ExpressionBuilder 56 * 57 * @param Connection $connection 58 */ 59 public function __construct(Connection $connection) 60 { 61 $this->connection = $connection; 62 } 63 64 /** 65 * Creates a conjunction of the given boolean expressions 66 * 67 * @param string ...$expressions Optional clause. Requires at least one defined when converting to string. 68 * 69 * @return CompositeExpression 70 */ 71 public function andX(...$expressions): CompositeExpression 72 { 73 return new CompositeExpression(CompositeExpression::TYPE_AND, $expressions); 74 } 75 76 /** 77 * Creates a disjunction of the given boolean expressions. 78 * 79 * @param string ...$expressions Optional clause. Requires at least one defined when converting to string. 80 * 81 * @return CompositeExpression 82 */ 83 public function orX(...$expressions): CompositeExpression 84 { 85 return new CompositeExpression(CompositeExpression::TYPE_OR, $expressions); 86 } 87 88 /** 89 * Creates a comparison expression. 90 * 91 * @param mixed $leftExpression The left expression. 92 * @param string $operator One of the ExpressionBuilder::* constants. 93 * @param mixed $rightExpression The right expression. 94 * 95 * @return string 96 */ 97 public function comparison($leftExpression, string $operator, $rightExpression): string 98 { 99 return $leftExpression . ' ' . $operator . ' ' . $rightExpression; 100 } 101 102 /** 103 * Creates an equality comparison expression with the given arguments. 104 * 105 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically. 106 * @param mixed $value The value. No automatic quoting/escaping is done. 107 * 108 * @return string 109 */ 110 public function eq(string $fieldName, $value): string 111 { 112 return $this->comparison($this->connection->quoteIdentifier($fieldName), static::EQ, $value); 113 } 114 115 /** 116 * Creates a non equality comparison expression with the given arguments. 117 * First argument is considered the left expression and the second is the right expression. 118 * When converted to string, it will generated a <left expr> <> <right expr>. Example: 119 * 120 * [php] 121 * // u.id <> 1 122 * $q->where($q->expr()->neq('u.id', '1')); 123 * 124 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically. 125 * @param mixed $value The value. No automatic quoting/escaping is done. 126 * 127 * @return string 128 */ 129 public function neq(string $fieldName, $value): string 130 { 131 return $this->comparison($this->connection->quoteIdentifier($fieldName), static::NEQ, $value); 132 } 133 134 /** 135 * Creates a lower-than comparison expression with the given arguments. 136 * 137 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically. 138 * @param mixed $value The value. No automatic quoting/escaping is done. 139 * 140 * @return string 141 */ 142 public function lt($fieldName, $value): string 143 { 144 return $this->comparison($this->connection->quoteIdentifier($fieldName), static::LT, $value); 145 } 146 147 /** 148 * Creates a lower-than-equal comparison expression with the given arguments. 149 * 150 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically. 151 * @param mixed $value The value. No automatic quoting/escaping is done. 152 * 153 * @return string 154 */ 155 public function lte(string $fieldName, $value): string 156 { 157 return $this->comparison($this->connection->quoteIdentifier($fieldName), static::LTE, $value); 158 } 159 160 /** 161 * Creates a greater-than comparison expression with the given arguments. 162 * 163 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically. 164 * @param mixed $value The value. No automatic quoting/escaping is done. 165 * 166 * @return string 167 */ 168 public function gt(string $fieldName, $value): string 169 { 170 return $this->comparison($this->connection->quoteIdentifier($fieldName), static::GT, $value); 171 } 172 173 /** 174 * Creates a greater-than-equal comparison expression with the given arguments. 175 * 176 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically. 177 * @param mixed $value The value. No automatic quoting/escaping is done. 178 * 179 * @return string 180 */ 181 public function gte(string $fieldName, $value): string 182 { 183 return $this->comparison($this->connection->quoteIdentifier($fieldName), static::GTE, $value); 184 } 185 186 /** 187 * Creates an IS NULL expression with the given arguments. 188 * 189 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically. 190 * 191 * @return string 192 */ 193 public function isNull(string $fieldName): string 194 { 195 return $this->connection->quoteIdentifier($fieldName) . ' IS NULL'; 196 } 197 198 /** 199 * Creates an IS NOT NULL expression with the given arguments. 200 * 201 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically. 202 * 203 * @return string 204 */ 205 public function isNotNull(string $fieldName): string 206 { 207 return $this->connection->quoteIdentifier($fieldName) . ' IS NOT NULL'; 208 } 209 210 /** 211 * Creates a LIKE() comparison expression with the given arguments. 212 * 213 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically. 214 * @param mixed $value Argument to be used in LIKE() comparison. No automatic quoting/escaping is done. 215 * 216 * @return string 217 */ 218 public function like(string $fieldName, $value): string 219 { 220 return $this->comparison($this->connection->quoteIdentifier($fieldName), 'LIKE', $value); 221 } 222 223 /** 224 * Creates a NOT LIKE() comparison expression with the given arguments. 225 * 226 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically. 227 * @param mixed $value Argument to be used in NOT LIKE() comparison. No automatic quoting/escaping is done. 228 * 229 * @return string 230 */ 231 public function notLike(string $fieldName, $value): string 232 { 233 return $this->comparison($this->connection->quoteIdentifier($fieldName), 'NOT LIKE', $value); 234 } 235 236 /** 237 * Creates a IN () comparison expression with the given arguments. 238 * 239 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically. 240 * @param string|array $value The placeholder or the array of values to be used by IN() comparison. 241 * No automatic quoting/escaping is done. 242 * 243 * @return string 244 */ 245 public function in(string $fieldName, $value): string 246 { 247 return $this->comparison( 248 $this->connection->quoteIdentifier($fieldName), 249 'IN', 250 '(' . implode(', ', (array)$value) . ')' 251 ); 252 } 253 254 /** 255 * Creates a NOT IN () comparison expression with the given arguments. 256 * 257 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically. 258 * @param string|array $value The placeholder or the array of values to be used by NOT IN() comparison. 259 * No automatic quoting/escaping is done. 260 * 261 * @return string 262 */ 263 public function notIn(string $fieldName, $value): string 264 { 265 return $this->comparison( 266 $this->connection->quoteIdentifier($fieldName), 267 'NOT IN', 268 '(' . implode(', ', (array)$value) . ')' 269 ); 270 } 271 272 /** 273 * Returns a comparison that can find a value in a list field (CSV). 274 * 275 * @param string $fieldName The field name. Will be quoted according to database platform automatically. 276 * @param string $value Argument to be used in FIND_IN_SET() comparison. No automatic quoting/escaping is done. 277 * @param bool $isColumn Set when the value to compare is a column on a table to activate casting 278 * @return string 279 * @throws \InvalidArgumentException 280 * @throws \RuntimeException 281 */ 282 public function inSet(string $fieldName, string $value, bool $isColumn = false): string 283 { 284 if ($value === '') { 285 throw new \InvalidArgumentException( 286 'ExpressionBuilder::inSet() can not be used with an empty string value.', 287 1459696089 288 ); 289 } 290 291 if (str_contains($value, ',')) { 292 throw new \InvalidArgumentException( 293 'ExpressionBuilder::inSet() can not be used with values that contain a comma (",").', 294 1459696090 295 ); 296 } 297 298 switch ($this->connection->getDatabasePlatform()->getName()) { 299 case 'postgresql': 300 case 'pdo_postgresql': 301 return $this->comparison( 302 $isColumn ? $value . '::text' : $this->literal($this->unquoteLiteral((string)$value)), 303 self::EQ, 304 sprintf( 305 'ANY(string_to_array(%s, %s))', 306 $this->connection->quoteIdentifier($fieldName) . '::text', 307 $this->literal(',') 308 ) 309 ); 310 case 'oci8': 311 case 'pdo_oracle': 312 throw new \RuntimeException( 313 'FIND_IN_SET support for database platform "Oracle" not yet implemented.', 314 1459696680 315 ); 316 case 'sqlsrv': 317 case 'pdo_sqlsrv': 318 case 'mssql': 319 // See unit and functional tests for details 320 if ($isColumn) { 321 $expression = $this->orX( 322 $this->eq($fieldName, $value), 323 $this->like($fieldName, $value . ' + \',%\''), 324 $this->like($fieldName, '\'%,\' + ' . $value), 325 $this->like($fieldName, '\'%,\' + ' . $value . ' + \',%\'') 326 ); 327 } else { 328 $likeEscapedValue = str_replace( 329 ['[', '%'], 330 ['[[]', '[%]'], 331 $this->unquoteLiteral($value) 332 ); 333 $expression = $this->orX( 334 $this->eq($fieldName, $this->literal($this->unquoteLiteral((string)$value))), 335 $this->like($fieldName, $this->literal($likeEscapedValue . ',%')), 336 $this->like($fieldName, $this->literal('%,' . $likeEscapedValue)), 337 $this->like($fieldName, $this->literal('%,' . $likeEscapedValue . ',%')) 338 ); 339 } 340 return (string)$expression; 341 case 'sqlite': 342 case 'sqlite3': 343 case 'pdo_sqlite': 344 if (strpos($value, ':') === 0 || $value === '?') { 345 throw new \InvalidArgumentException( 346 'ExpressionBuilder::inSet() for SQLite can not be used with placeholder arguments.', 347 1476029421 348 ); 349 } 350 $comparison = sprintf( 351 'instr(%s, %s)', 352 implode( 353 '||', 354 [ 355 $this->literal(','), 356 $this->connection->quoteIdentifier($fieldName), 357 $this->literal(','), 358 ] 359 ), 360 $isColumn ? 361 implode( 362 '||', 363 [ 364 $this->literal(','), 365 // do not explicitly quote value as it is expected to be 366 // quoted by the caller 367 'cast(' . $value . ' as text)', 368 $this->literal(','), 369 ] 370 ) 371 : $this->literal( 372 ',' . $this->unquoteLiteral($value) . ',' 373 ) 374 ); 375 return $comparison; 376 default: 377 return sprintf( 378 'FIND_IN_SET(%s, %s)', 379 $value, 380 $this->connection->quoteIdentifier($fieldName) 381 ); 382 } 383 } 384 385 /** 386 * Returns a comparison that can find a value in a list field (CSV) but is negated. 387 * 388 * @param string $fieldName The field name. Will be quoted according to database platform automatically. 389 * @param string $value Argument to be used in FIND_IN_SET() comparison. No automatic quoting/escaping is done. 390 * @param bool $isColumn Set when the value to compare is a column on a table to activate casting 391 * @return string 392 * @throws \InvalidArgumentException 393 * @throws \RuntimeException 394 */ 395 public function notInSet(string $fieldName, string $value, bool $isColumn = false): string 396 { 397 if ($value === '') { 398 throw new \InvalidArgumentException( 399 'ExpressionBuilder::notInSet() can not be used with an empty string value.', 400 1627573099 401 ); 402 } 403 404 if (str_contains($value, ',')) { 405 throw new \InvalidArgumentException( 406 'ExpressionBuilder::notInSet() can not be used with values that contain a comma (",").', 407 1627573100 408 ); 409 } 410 411 switch ($this->connection->getDatabasePlatform()->getName()) { 412 case 'postgresql': 413 case 'pdo_postgresql': 414 return $this->comparison( 415 $isColumn ? $value . '::text' : $this->literal($this->unquoteLiteral((string)$value)), 416 self::NEQ, 417 sprintf( 418 'ALL(string_to_array(%s, %s))', 419 $this->connection->quoteIdentifier($fieldName) . '::text', 420 $this->literal(',') 421 ) 422 ); 423 case 'oci8': 424 case 'pdo_oracle': 425 throw new \RuntimeException( 426 'negative FIND_IN_SET support for database platform "Oracle" not yet implemented.', 427 1627573101 428 ); 429 case 'sqlsrv': 430 case 'pdo_sqlsrv': 431 case 'mssql': 432 // See unit and functional tests for details 433 if ($isColumn) { 434 $expression = $this->andX( 435 $this->neq($fieldName, $value), 436 $this->notLike($fieldName, $value . ' + \',%\''), 437 $this->notLike($fieldName, '\'%,\' + ' . $value), 438 $this->notLike($fieldName, '\'%,\' + ' . $value . ' + \',%\'') 439 ); 440 } else { 441 $likeEscapedValue = str_replace( 442 ['[', '%'], 443 ['[[]', '[%]'], 444 $this->unquoteLiteral($value) 445 ); 446 $expression = $this->andX( 447 $this->neq($fieldName, $this->literal($this->unquoteLiteral((string)$value))), 448 $this->notLike($fieldName, $this->literal($likeEscapedValue . ',%')), 449 $this->notLike($fieldName, $this->literal('%,' . $likeEscapedValue)), 450 $this->notLike($fieldName, $this->literal('%,' . $likeEscapedValue . ',%')) 451 ); 452 } 453 return (string)$expression; 454 case 'sqlite': 455 case 'sqlite3': 456 case 'pdo_sqlite': 457 if (strpos($value, ':') === 0 || $value === '?') { 458 throw new \InvalidArgumentException( 459 'ExpressionBuilder::inSet() for SQLite can not be used with placeholder arguments.', 460 1627573103 461 ); 462 } 463 $comparison = sprintf( 464 'instr(%s, %s) = 0', 465 implode( 466 '||', 467 [ 468 $this->literal(','), 469 $this->connection->quoteIdentifier($fieldName), 470 $this->literal(','), 471 ] 472 ), 473 $isColumn ? 474 implode( 475 '||', 476 [ 477 $this->literal(','), 478 // do not explicitly quote value as it is expected to be 479 // quoted by the caller 480 'cast(' . $value . ' as text)', 481 $this->literal(','), 482 ] 483 ) 484 : $this->literal( 485 ',' . $this->unquoteLiteral($value) . ',' 486 ) 487 ); 488 return $comparison; 489 default: 490 return sprintf( 491 'NOT FIND_IN_SET(%s, %s)', 492 $value, 493 $this->connection->quoteIdentifier($fieldName) 494 ); 495 } 496 } 497 498 /** 499 * Creates a bitwise AND expression with the given arguments. 500 * 501 * @param string $fieldName The fieldname. Will be quoted according to database platform automatically. 502 * @param int $value Argument to be used in the bitwise AND operation 503 * @return string 504 */ 505 public function bitAnd(string $fieldName, int $value): string 506 { 507 switch ($this->connection->getDatabasePlatform()->getName()) { 508 case 'oci8': 509 case 'pdo_oracle': 510 return sprintf( 511 'BITAND(%s, %s)', 512 $this->connection->quoteIdentifier($fieldName), 513 $value 514 ); 515 default: 516 return $this->comparison( 517 $this->connection->quoteIdentifier($fieldName), 518 '&', 519 $value 520 ); 521 } 522 } 523 524 /** 525 * Creates a MIN expression for the given field/alias. 526 * 527 * @param string $fieldName 528 * @param string|null $alias 529 * @return string 530 */ 531 public function min(string $fieldName, string $alias = null): string 532 { 533 return $this->calculation('MIN', $fieldName, $alias); 534 } 535 536 /** 537 * Creates a MAX expression for the given field/alias. 538 * 539 * @param string $fieldName 540 * @param string|null $alias 541 * @return string 542 */ 543 public function max(string $fieldName, string $alias = null): string 544 { 545 return $this->calculation('MAX', $fieldName, $alias); 546 } 547 548 /** 549 * Creates a AVG expression for the given field/alias. 550 * 551 * @param string $fieldName 552 * @param string|null $alias 553 * @return string 554 */ 555 public function avg(string $fieldName, string $alias = null): string 556 { 557 return $this->calculation('AVG', $fieldName, $alias); 558 } 559 560 /** 561 * Creates a SUM expression for the given field/alias. 562 * 563 * @param string $fieldName 564 * @param string|null $alias 565 * @return string 566 */ 567 public function sum(string $fieldName, string $alias = null): string 568 { 569 return $this->calculation('SUM', $fieldName, $alias); 570 } 571 572 /** 573 * Creates a COUNT expression for the given field/alias. 574 * 575 * @param string $fieldName 576 * @param string|null $alias 577 * @return string 578 */ 579 public function count(string $fieldName, string $alias = null): string 580 { 581 return $this->calculation('COUNT', $fieldName, $alias); 582 } 583 584 /** 585 * Creates a LENGTH expression for the given field/alias. 586 * 587 * @param string $fieldName 588 * @param string|null $alias 589 * @return string 590 */ 591 public function length(string $fieldName, string $alias = null): string 592 { 593 return $this->calculation('LENGTH', $fieldName, $alias); 594 } 595 596 /** 597 * Create a SQL aggregate function. 598 * 599 * @param string $aggregateName 600 * @param string $fieldName 601 * @param string|null $alias 602 * @return string 603 */ 604 protected function calculation(string $aggregateName, string $fieldName, string $alias = null): string 605 { 606 $aggregateSQL = sprintf( 607 '%s(%s)', 608 $aggregateName, 609 $this->connection->quoteIdentifier($fieldName) 610 ); 611 612 if (!empty($alias)) { 613 $aggregateSQL .= ' AS ' . $this->connection->quoteIdentifier($alias); 614 } 615 616 return $aggregateSQL; 617 } 618 619 /** 620 * Creates a TRIM expression for the given field. 621 * 622 * @param string $fieldName Field name to build expression for 623 * @param int $position Either constant out of LEADING, TRAILING, BOTH 624 * @param string $char Character to be trimmed (defaults to space) 625 * @return string 626 */ 627 public function trim(string $fieldName, int $position = TrimMode::UNSPECIFIED, string $char = null) 628 { 629 return $this->connection->getDatabasePlatform()->getTrimExpression( 630 $this->connection->quoteIdentifier($fieldName), 631 $position, 632 ($char === null ? false : $this->literal($char)) 633 ); 634 } 635 636 /** 637 * Quotes a given input parameter. 638 * 639 * @param mixed $input The parameter to be quoted. 640 * @param string|int|null $type The type of the parameter. 641 * @return mixed Often string, but also int or float or similar depending on $input and platform 642 * @todo: Change signature to literal($input, int $type = \PDO::PARAM_STR) as breaking change in v12. 643 */ 644 public function literal($input, $type = \PDO::PARAM_STR) 645 { 646 // @todo: drop this line together with signature change in v12 647 $type = $type ?? \PDO::PARAM_STR; 648 return $this->connection->quote($input, $type); 649 } 650 651 /** 652 * Unquote a string literal. Used to unquote values for internal platform adjustments. 653 * 654 * @param string $value The value to be unquoted 655 * @return string The unquoted value 656 */ 657 protected function unquoteLiteral(string $value): string 658 { 659 $quoteChar = $this->connection 660 ->getDatabasePlatform() 661 ->getStringLiteralQuoteCharacter(); 662 663 $isQuoted = strpos($value, $quoteChar) === 0 && strpos(strrev($value), $quoteChar) === 0; 664 665 if ($isQuoted) { 666 return str_replace($quoteChar . $quoteChar, $quoteChar, substr($value, 1, -1)); 667 } 668 669 return $value; 670 } 671} 672