| [ Index ] |
PHP Cross Reference of Joomla 4.2.2 documentation |
[Summary view] [Print] [Text view]
1 <?php 2 /** 3 * Part of the Joomla Framework Database Package 4 * 5 * @copyright Copyright (C) 2005 - 2021 Open Source Matters, Inc. All rights reserved. 6 * @license GNU General Public License version 2 or later; see LICENSE 7 */ 8 9 namespace Joomla\Database\Sqlsrv; 10 11 use Joomla\Database\DatabaseInterface; 12 use Joomla\Database\DatabaseQuery; 13 use Joomla\Database\Query\QueryElement; 14 15 /** 16 * SQL Server Query Building Class. 17 * 18 * @since 1.0 19 */ 20 class SqlsrvQuery extends DatabaseQuery 21 { 22 /** 23 * The list of zero or null representation of a datetime. 24 * 25 * @var array 26 * @since 2.0.0 27 */ 28 protected $nullDatetimeList = ['1900-01-01 00:00:00']; 29 30 /** 31 * Magic function to convert the query to a string. 32 * 33 * @return string The completed query. 34 * 35 * @since 1.0 36 */ 37 public function __toString() 38 { 39 // For the moment if we are given a query string we can't effectively process limits, fix this later 40 if ($this->sql) 41 { 42 return $this->sql; 43 } 44 45 $query = ''; 46 47 switch ($this->type) 48 { 49 case 'select': 50 // Add required aliases for offset or fixGroupColumns method 51 $columns = $this->fixSelectAliases(); 52 53 $query = (string) $this->select; 54 55 if ($this->group) 56 { 57 $this->fixGroupColumns($columns); 58 } 59 60 $query .= (string) $this->from; 61 62 if ($this->join) 63 { 64 // Special case for joins 65 foreach ($this->join as $join) 66 { 67 $query .= (string) $join; 68 } 69 } 70 71 if ($this->where) 72 { 73 $query .= (string) $this->where; 74 } 75 76 if ($this->selectRowNumber === null) 77 { 78 if ($this->group) 79 { 80 $query .= (string) $this->group; 81 } 82 83 if ($this->having) 84 { 85 $query .= (string) $this->having; 86 } 87 88 if ($this->merge) 89 { 90 // Special case for merge 91 foreach ($this->merge as $idx => $element) 92 { 93 $query .= (string) $element . ' AS merge_' . (int) ($idx + 1); 94 } 95 } 96 } 97 98 if ($this->order) 99 { 100 $query .= (string) $this->order; 101 } 102 else 103 { 104 $query .= PHP_EOL . '/*ORDER BY (SELECT 0)*/'; 105 } 106 107 $query = $this->processLimit($query, $this->limit, $this->offset); 108 109 if ($this->alias !== null) 110 { 111 $query = '(' . $query . ') AS ' . $this->alias; 112 } 113 114 break; 115 116 case 'querySet': 117 $query = $this->querySet; 118 119 if ($query->order || $query->limit || $query->offset) 120 { 121 // If ORDER BY or LIMIT statement exist then parentheses is required for the first query 122 $query = PHP_EOL . "SELECT * FROM ($query) AS merge_0"; 123 } 124 125 if ($this->merge) 126 { 127 // Special case for merge 128 foreach ($this->merge as $idx => $element) 129 { 130 $query .= (string) $element . ' AS merge_' . (int) ($idx + 1); 131 } 132 } 133 134 if ($this->order) 135 { 136 $query .= (string) $this->order; 137 } 138 139 $query = $this->processLimit($query, $this->limit, $this->offset); 140 141 break; 142 143 case 'insert': 144 $query .= (string) $this->insert; 145 146 // Set method 147 if ($this->set) 148 { 149 $query .= (string) $this->set; 150 } 151 elseif ($this->values) 152 { 153 // Columns-Values method 154 if ($this->columns) 155 { 156 $query .= (string) $this->columns; 157 } 158 159 $elements = $this->insert->getElements(); 160 $tableName = array_shift($elements); 161 162 $query .= 'VALUES '; 163 $query .= (string) $this->values; 164 165 if ($this->autoIncrementField) 166 { 167 $query = 'SET IDENTITY_INSERT ' . $tableName . ' ON;' . $query . 'SET IDENTITY_INSERT ' . $tableName . ' OFF;'; 168 } 169 170 if ($this->where) 171 { 172 $query .= (string) $this->where; 173 } 174 } 175 176 break; 177 178 case 'delete': 179 $query .= (string) $this->delete; 180 $query .= (string) $this->from; 181 182 if ($this->join) 183 { 184 // Special case for joins 185 foreach ($this->join as $join) 186 { 187 $query .= (string) $join; 188 } 189 } 190 191 if ($this->where) 192 { 193 $query .= (string) $this->where; 194 } 195 196 if ($this->order) 197 { 198 $query .= (string) $this->order; 199 } 200 201 break; 202 203 case 'update': 204 if ($this->join) 205 { 206 $tmpUpdate = $this->update; 207 $tmpFrom = $this->from; 208 $this->update = null; 209 $this->from = null; 210 211 $updateElem = $tmpUpdate->getElements(); 212 $updateArray = explode(' ', $updateElem[0]); 213 214 // Use table alias if exists 215 $this->update(end($updateArray)); 216 $this->from($updateElem[0]); 217 218 $query .= (string) $this->update; 219 $query .= (string) $this->set; 220 $query .= (string) $this->from; 221 222 $this->update = $tmpUpdate; 223 $this->from = $tmpFrom; 224 225 // Special case for joins 226 foreach ($this->join as $join) 227 { 228 $query .= (string) $join; 229 } 230 } 231 else 232 { 233 $query .= (string) $this->update; 234 $query .= (string) $this->set; 235 } 236 237 if ($this->where) 238 { 239 $query .= (string) $this->where; 240 } 241 242 if ($this->order) 243 { 244 $query .= (string) $this->order; 245 } 246 247 break; 248 249 default: 250 $query = parent::__toString(); 251 252 break; 253 } 254 255 return $query; 256 } 257 258 /** 259 * Casts a value to a char. 260 * 261 * Ensure that the value is properly quoted before passing to the method. 262 * 263 * Usage: 264 * $query->select($query->castAs('CHAR', 'a')); 265 * 266 * @param string $type The type of string to cast as. 267 * @param string $value The value to cast as a char. 268 * @param string $length The value to cast as a char. 269 * 270 * @return string SQL statement to cast the value as a char type. 271 * 272 * @since 1.0 273 */ 274 public function castAs(string $type, string $value, ?string $length = null) 275 { 276 switch (strtoupper($type)) 277 { 278 case 'CHAR': 279 if (!$length) 280 { 281 $length = '10'; 282 } 283 284 return 'CAST(' . $value . ' as NVARCHAR(' . $length . '))'; 285 286 case 'INT': 287 return 'CAST(' . $value . ' AS INT)'; 288 } 289 290 return parent::castAs($type, $value, $length); 291 } 292 293 /** 294 * Gets the function to determine the length of a character string. 295 * 296 * @param string $field A value. 297 * @param string|null $operator Comparison operator between charLength integer value and $condition 298 * @param string|null $condition Integer value to compare charLength with. 299 * 300 * @return string The required char length call. 301 * 302 * @since 1.0 303 */ 304 public function charLength($field, $operator = null, $condition = null) 305 { 306 $statement = 'DATALENGTH(' . $field . ')'; 307 308 if ($operator !== null && $condition !== null) 309 { 310 $statement .= ' ' . $operator . ' ' . $condition; 311 } 312 313 return $statement; 314 } 315 316 /** 317 * Concatenates an array of column names or values. 318 * 319 * @param string[] $values An array of values to concatenate. 320 * @param string|null $separator As separator to place between each value. 321 * 322 * @return string The concatenated values. 323 * 324 * @since 1.0 325 */ 326 public function concatenate($values, $separator = null) 327 { 328 if ($separator !== null) 329 { 330 return '(' . implode('+' . $this->quote($separator) . '+', $values) . ')'; 331 } 332 333 return '(' . implode('+', $values) . ')'; 334 } 335 336 /** 337 * Gets the current date and time. 338 * 339 * @return string 340 * 341 * @since 1.0 342 */ 343 public function currentTimestamp() 344 { 345 return 'GETDATE()'; 346 } 347 348 /** 349 * Get the length of a string in bytes. 350 * 351 * @param string $value The string to measure. 352 * 353 * @return integer 354 * 355 * @since 1.0 356 */ 357 public function length($value) 358 { 359 return 'LEN(' . $value . ')'; 360 } 361 362 /** 363 * Add a grouping column to the GROUP clause of the query. 364 * 365 * Usage: 366 * $query->group('id'); 367 * 368 * @param mixed $columns A string or array of ordering columns. 369 * 370 * @return SqlsrvQuery Returns this object to allow chaining. 371 * 372 * @since 1.5.0 373 */ 374 public function group($columns) 375 { 376 if (!($this->db instanceof DatabaseInterface)) 377 { 378 throw new \RuntimeException('JLIB_DATABASE_ERROR_INVALID_DB_OBJECT'); 379 } 380 381 // Transform $columns into an array for filtering purposes 382 \is_string($columns) && $columns = explode(',', str_replace(' ', '', $columns)); 383 384 // Get the _formatted_ FROM string and remove everything except `table AS alias` 385 $fromStr = str_replace(['[', ']'], '', str_replace('#__', $this->db->getPrefix(), str_replace('FROM ', '', (string) $this->from))); 386 387 // Start setting up an array of alias => table 388 list($table, $alias) = preg_split("/\sAS\s/i", $fromStr); 389 390 $tmpCols = $this->db->getTableColumns(trim($table)); 391 $cols = []; 392 393 foreach ($tmpCols as $name => $type) 394 { 395 $cols[] = $alias . '.' . $name; 396 } 397 398 // Now we need to get all tables from any joins 399 // Go through all joins and add them to the tables array 400 foreach ($this->join as $join) 401 { 402 $joinTbl = str_replace( 403 '#__', 404 $this->db->getPrefix(), 405 str_replace( 406 ']', 407 '', 408 preg_replace("/.*(#.+\sAS\s[^\s]*).*/i", '$1', (string) $join) 409 ) 410 ); 411 412 list($table, $alias) = preg_split("/\sAS\s/i", $joinTbl); 413 414 $tmpCols = $this->db->getTableColumns(trim($table)); 415 416 foreach ($tmpCols as $name => $tmpColType) 417 { 418 $cols[] = $alias . '.' . $name; 419 } 420 } 421 422 $selectStr = str_replace('SELECT ', '', (string) $this->select); 423 424 // Remove any functions (e.g. COUNT(), SUM(), CONCAT()) 425 $selectCols = preg_replace("/([^,]*\([^\)]*\)[^,]*,?)/", '', $selectStr); 426 427 // Remove any "as alias" statements 428 $selectCols = preg_replace("/(\sas\s[^,]*)/i", '', $selectCols); 429 430 // Remove any extra commas 431 $selectCols = preg_replace('/,{2,}/', ',', $selectCols); 432 433 // Remove any trailing commas and all whitespaces 434 $selectCols = trim(str_replace(' ', '', preg_replace('/,?$/', '', $selectCols))); 435 436 // Get an array to compare against 437 $selectCols = explode(',', $selectCols); 438 439 // Find all alias.* and fill with proper table column names 440 foreach ($selectCols as $key => $aliasColName) 441 { 442 if (preg_match("/.+\*/", $aliasColName, $match)) 443 { 444 // Grab the table alias minus the .* 445 $aliasStar = preg_replace("/(.+)\.\*/", '$1', $aliasColName); 446 447 // Unset the array key 448 unset($selectCols[$key]); 449 450 // Get the table name 451 $tableColumns = preg_grep("/{$aliasStar}\.+/", $cols); 452 $columns = array_merge($columns, $tableColumns); 453 } 454 } 455 456 // Finally, get a unique string of all column names that need to be included in the group statement 457 $columns = array_unique(array_merge($columns, $selectCols)); 458 $columns = implode(',', $columns); 459 460 // Recreate it every time, to ensure we have checked _all_ select statements 461 $this->group = new QueryElement('GROUP BY', $columns); 462 463 return $this; 464 } 465 466 /** 467 * Aggregate function to get input values concatenated into a string, separated by delimiter 468 * 469 * Usage: 470 * $query->groupConcat('id', ','); 471 * 472 * @param string $expression The expression to apply concatenation to, this may be a column name or complex SQL statement. 473 * @param string $separator The delimiter of each concatenated value 474 * 475 * @return string Input values concatenated into a string, separated by delimiter 476 * 477 * @since 2.0.0 478 */ 479 public function groupConcat($expression, $separator = ',') 480 { 481 return 'string_agg(' . $expression . ', ' . $this->quote($separator) . ')'; 482 } 483 484 /** 485 * Get the function to return a random floating-point value 486 * 487 * Usage: 488 * $query->rand(); 489 * 490 * @return string 491 * 492 * @since 1.5.0 493 */ 494 public function rand() 495 { 496 return ' NEWID() '; 497 } 498 499 /** 500 * Find a value in a varchar used like a set. 501 * 502 * Ensure that the value is an integer before passing to the method. 503 * 504 * Usage: 505 * $query->findInSet((int) $parent->id, 'a.assigned_cat_ids') 506 * 507 * @param string $value The value to search for. 508 * @param string $set The set of values. 509 * 510 * @return string A representation of the MySQL find_in_set() function for the driver. 511 * 512 * @since 1.5.0 513 */ 514 public function findInSet($value, $set) 515 { 516 return "CHARINDEX(',$value,', ',' + $set + ',') > 0"; 517 } 518 519 /** 520 * Add required aliases to columns for select statement in subquery. 521 * 522 * @return array[] Array of columns with added missing aliases. 523 * 524 * @since 2.0.0 525 */ 526 protected function fixSelectAliases() 527 { 528 $operators = [ 529 '+' => '', 530 '-' => '', 531 '*' => '', 532 '/' => '', 533 '%' => '', 534 '&' => '', 535 '|' => '', 536 '~' => '', 537 '^' => '', 538 ]; 539 540 // Split into array and remove comments 541 $columns = $this->splitSqlExpression(implode(',', $this->select->getElements())); 542 543 foreach ($columns as $i => $column) 544 { 545 $size = \count($column); 546 547 if ($size == 0) 548 { 549 continue; 550 } 551 552 if ($size > 2 && strcasecmp($column[$size - 2], 'AS') === 0) 553 { 554 // Alias exists, replace it to uppercase 555 $columns[$i][$size - 2] = 'AS'; 556 557 continue; 558 } 559 560 if ($i == 0 && stripos(' DISTINCT ALL ', " $column[0] ") !== false) 561 { 562 // This words are reserved, they are not column names 563 array_shift($column); 564 $size--; 565 } 566 567 $lastWord = strtoupper($column[$size - 1]); 568 $length = \strlen($lastWord); 569 $lastChar = $lastWord[$length - 1]; 570 571 if ($lastChar == '*') 572 { 573 // Skip on wildcard 574 continue; 575 } 576 577 if ($lastChar == ')' 578 || ($size == 1 && $lastChar == "'") 579 || $lastWord[0] == '@' 580 || $lastWord == 'NULL' 581 || $lastWord == 'END' 582 || is_numeric($lastWord)) 583 { 584 /* 585 * Ends with: 586 * - SQL function 587 * - single static value like 'only '+'string' 588 * - @@var 589 * - NULL 590 * - CASE ... END 591 * - Numeric 592 */ 593 $columns[$i][] = 'AS'; 594 $columns[$i][] = $this->quoteName('columnAlias' . $i); 595 596 continue; 597 } 598 599 if ($size == 1) 600 { 601 continue; 602 } 603 604 $lastChar2 = substr($column[$size - 2], -1); 605 606 // Check if column ends with '- a.x' or '- a. x' 607 if (isset($operators[$lastChar2]) || ($size > 2 && $lastChar2 === '.' && isset($operators[substr($column[$size - 3], -1)]))) 608 { 609 // Ignore plus signs if column start with them 610 if ($size != 2 || ltrim($column[0], '+') !== '' || $column[1][0] === "'") 611 { 612 // If operator exists before last word then alias is required for subquery 613 $columns[$i][] = 'AS'; 614 $columns[$i][] = $this->quoteName('columnAlias' . $i); 615 616 continue; 617 } 618 } 619 elseif ($column[$size - 1][0] !== '.' && $lastChar2 !== '.') 620 { 621 // If columns is like name name2 then second word is alias. 622 // Add missing AS before the alias, exception for 'a. x' and 'a .x' 623 array_splice($columns[$i], -1, 0, 'AS'); 624 } 625 } 626 627 $selectColumns = []; 628 629 foreach ($columns as $i => $column) 630 { 631 $selectColumns[$i] = implode(' ', $column); 632 } 633 634 $this->select = new QueryElement('SELECT', $selectColumns); 635 636 return $columns; 637 } 638 639 /** 640 * Add missing columns names to GROUP BY clause. 641 * 642 * @param array[] $selectColumns Array of columns from splitSqlExpression method. 643 * 644 * @return $this 645 * 646 * @since 2.0.0 647 */ 648 protected function fixGroupColumns($selectColumns) 649 { 650 // Cache tables columns 651 static $cacheCols = []; 652 653 // Known columns of all included tables 654 $knownColumnsByAlias = []; 655 656 $iquotes = ['"' => '', '[' => '', "'" => '']; 657 $nquotes = ['"', '[', ']']; 658 659 // Aggregate functions 660 $aFuncs = [ 661 'AVG(', 662 'CHECKSUM_AGG(', 663 'COUNT(', 664 'COUNT_BIG(', 665 'GROUPING(', 666 'GROUPING_ID(', 667 'MIN(', 668 'MAX(', 669 'SUM(', 670 'STDEV(', 671 'STDEVP(', 672 'VAR(', 673 'VARP(', 674 ]; 675 676 // Aggregated columns 677 $filteredColumns = []; 678 679 // Aliases found in SELECT statement 680 $knownAliases = []; 681 $wildcardTables = []; 682 683 foreach ($selectColumns as $i => $column) 684 { 685 $size = \count($column); 686 687 if ($size === 0) 688 { 689 continue; 690 } 691 692 if ($i == 0 && stripos(' DISTINCT ALL ', " $column[0] ") !== false) 693 { 694 // These words are reserved, they are not column names 695 array_shift($selectColumns[0]); 696 array_shift($column); 697 $size--; 698 } 699 700 if ($size > 2 && $column[$size - 2] === 'AS') 701 { 702 // Save and remove AS alias 703 $alias = $column[$size - 1]; 704 705 if (isset($iquotes[$alias[0]])) 706 { 707 $alias = substr($alias, 1, -1); 708 } 709 710 // Remove alias 711 $selectColumns[$i] = $column = \array_slice($column, 0, -2); 712 713 if ($size === 3 || ($size === 4 && strpos('+-*/%&|~^', $column[0][0]) !== false)) 714 { 715 $lastWord = $column[$size - 3]; 716 717 if ($lastWord[0] === "'" || $lastWord === 'NULL' || is_numeric($lastWord)) 718 { 719 unset($selectColumns[$i]); 720 721 continue; 722 } 723 } 724 725 // Remember pair alias => column expression 726 $knownAliases[$alias] = implode(' ', $column); 727 } 728 729 $aggregated = false; 730 731 foreach ($column as $j => $block) 732 { 733 if (substr($block, -2) === '.*') 734 { 735 // Found column ends with .* 736 if (isset($iquotes[$block[0]])) 737 { 738 // Quoted table 739 $wildcardTables[] = substr($block, 1, -3); 740 } 741 else 742 { 743 $wildcardTables[] = substr($block, 0, -2); 744 } 745 } 746 elseif (str_ireplace($aFuncs, '', $block) != $block) 747 { 748 $aggregated = true; 749 } 750 751 if ($block[0] === "'") 752 { 753 // Shrink static strings which could contain column name 754 $column[$j] = "''"; 755 } 756 } 757 758 if (!$aggregated) 759 { 760 // Without aggregated columns and aliases 761 $filteredColumns[] = implode(' ', $selectColumns[$i]); 762 } 763 764 // Without aliases and static strings 765 $selectColumns[$i] = implode(' ', $column); 766 } 767 768 // If select statement use table.* expression 769 if ($wildcardTables) 770 { 771 // Split FROM statement into list of tables 772 $tables = $this->splitSqlExpression(implode(',', $this->from->getElements())); 773 774 foreach ($tables as $i => $table) 775 { 776 $table = implode(' ', $table); 777 778 // Exclude subquery from the FROM clause 779 if (strpos($table, '(') === false) 780 { 781 // Unquote 782 $table = str_replace($nquotes, '', $table); 783 $table = str_replace('#__', $this->db->getPrefix(), $table); 784 $table = explode(' ', $table); 785 $alias = end($table); 786 $table = $table[0]; 787 788 // Chek if exists a wildcard with current alias table? 789 if (\in_array($alias, $wildcardTables, true)) 790 { 791 if (!isset($cacheCols[$table])) 792 { 793 $cacheCols[$table] = $this->db->getTableColumns($table); 794 } 795 796 if ($this->join || $table != $alias) 797 { 798 foreach ($cacheCols[$table] as $name => $type) 799 { 800 $knownColumnsByAlias[$alias][] = $alias . '.' . $name; 801 } 802 } 803 else 804 { 805 foreach ($cacheCols[$table] as $name => $type) 806 { 807 $knownColumnsByAlias[$alias][] = $name; 808 } 809 } 810 } 811 } 812 } 813 814 // Now we need to get all tables from any joins 815 // Go through all joins and add them to the tables array 816 if ($this->join) 817 { 818 foreach ($this->join as $join) 819 { 820 // Unquote and replace prefix 821 $joinTbl = str_replace($nquotes, '', (string) $join); 822 $joinTbl = str_replace('#__', $this->db->getPrefix(), $joinTbl); 823 824 // Exclude subquery 825 if (preg_match('/JOIN\s+(\w+)(?:\s+AS)?(?:\s+(\w+))?/i', $joinTbl, $matches)) 826 { 827 $table = $matches[1]; 828 $alias = $matches[2] ?? $table; 829 830 // Chek if exists a wildcard with current alias table? 831 if (\in_array($alias, $wildcardTables, true)) 832 { 833 if (!isset($cacheCols[$table])) 834 { 835 $cacheCols[$table] = $this->db->getTableColumns($table); 836 } 837 838 foreach ($cacheCols[$table] as $name => $type) 839 { 840 $knownColumnsByAlias[$alias][] = $alias . '.' . $name; 841 } 842 } 843 } 844 } 845 } 846 } 847 848 $selectExpression = implode(',', $selectColumns); 849 850 // Split into the right columns 851 $groupColumns = $this->splitSqlExpression(implode(',', $this->group->getElements())); 852 853 // Remove column aliases from GROUP statement - SQLSRV does not support it 854 foreach ($groupColumns as $i => $column) 855 { 856 $groupColumns[$i] = implode(' ', $column); 857 $column = str_replace($nquotes, '', $groupColumns[$i]); 858 859 if (isset($knownAliases[$column])) 860 { 861 // Be sure that this is not a valid column name 862 if (!preg_match('/\b' . preg_quote($column, '/') . '\b/', $selectExpression)) 863 { 864 // Replace column alias by column expression 865 $groupColumns[$i] = $knownAliases[$column]; 866 } 867 } 868 } 869 870 // Find all alias.* and fill with proper table column names 871 foreach ($filteredColumns as $i => $column) 872 { 873 if (substr($column, -2) === '.*') 874 { 875 unset($filteredColumns[$i]); 876 877 // Extract alias.* columns into GROUP BY statement 878 $groupColumns = array_merge($groupColumns, $knownColumnsByAlias[substr($column, 0, -2)]); 879 } 880 } 881 882 $groupColumns = array_merge($groupColumns, $filteredColumns); 883 884 if ($this->order) 885 { 886 // Remove direction suffixes 887 $dir = [" DESC\v", " ASC\v"]; 888 889 $orderColumns = $this->splitSqlExpression(implode(',', $this->order->getElements())); 890 891 foreach ($orderColumns as $i => $column) 892 { 893 $column = implode(' ', $column); 894 $orderColumns[$i] = $column = trim(str_ireplace($dir, '', "$column\v"), "\v"); 895 896 if (isset($knownAliases[str_replace($nquotes, '', $column)])) 897 { 898 unset($orderColumns[$i]); 899 } 900 901 if (str_ireplace($aFuncs, '', $column) != $column) 902 { 903 // Do not add aggregate expression 904 unset($orderColumns[$i]); 905 } 906 } 907 908 $groupColumns = array_merge($groupColumns, $orderColumns); 909 } 910 911 // Get a unique string of all column names that need to be included in the group statement 912 $this->group = new QueryElement('GROUP BY', array_unique($groupColumns)); 913 914 return $this; 915 } 916 917 /** 918 * Split a string of sql expression into an array of individual columns. 919 * Single line or line end comments and multi line comments are stripped off. 920 * Always return at least one column. 921 * 922 * @param string $string Input string of sql expression like select expression. 923 * 924 * @return array[] The columns from the input string separated into an array. 925 * 926 * @since 2.0.0 927 */ 928 protected function splitSqlExpression($string) 929 { 930 // Append whitespace as equivalent to the last comma 931 $string .= ' '; 932 933 $colIdx = 0; 934 $start = 0; 935 $open = false; 936 $openC = 0; 937 $comment = false; 938 $endString = ''; 939 $length = \strlen($string); 940 $columns = []; 941 $column = []; 942 $current = ''; 943 $previous = null; 944 $operators = [ 945 '+' => '', 946 '-' => '', 947 '*' => '', 948 '/' => '', 949 '%' => '', 950 '&' => '', 951 '|' => '', 952 '~' => '', 953 '^' => '', 954 ]; 955 956 $addBlock = function ($block) use (&$column, &$colIdx) 957 { 958 if (isset($column[$colIdx])) 959 { 960 $column[$colIdx] .= $block; 961 } 962 else 963 { 964 $column[$colIdx] = $block; 965 } 966 }; 967 968 for ($i = 0; $i < $length; $i++) 969 { 970 $current = substr($string, $i, 1); 971 $current2 = substr($string, $i, 2); 972 $current3 = substr($string, $i, 3); 973 $lenEndString = \strlen($endString); 974 $testEnd = substr($string, $i, $lenEndString); 975 976 if ($current == '[' || $current == '"' || $current == "'" || $current2 == '--' 977 || ($current2 == '/*') 978 || ($current == '#' && $current3 != '#__') 979 || ($lenEndString && $testEnd == $endString)) 980 { 981 if ($open) 982 { 983 if ($testEnd === $endString) 984 { 985 if ($comment) 986 { 987 if ($lenEndString > 1) 988 { 989 $i += ($lenEndString - 1); 990 } 991 992 // Move cursor after close tag of comment 993 $start = $i + 1; 994 $comment = false; 995 } 996 elseif ($current == "'" || $current == ']' || $current == '"') 997 { 998 // Check for escaped quote like '', ]] or "" 999 $n = 1; 1000 1001 while ($i + $n < $length && $string[$i + $n] == $current) 1002 { 1003 $n++; 1004 } 1005 1006 // Jump to the last quote 1007 $i += $n - 1; 1008 1009 if ($n % 2 === 0) 1010 { 1011 // There is only escaped quote 1012 continue; 1013 } 1014 1015 if ($n > 2) 1016 { 1017 // The last right close quote is not escaped 1018 $current = $string[$i]; 1019 } 1020 } 1021 1022 $open = false; 1023 $endString = ''; 1024 } 1025 } 1026 else 1027 { 1028 $open = true; 1029 1030 if ($current == '#' || $current2 == '--') 1031 { 1032 $endString = "\n"; 1033 $comment = true; 1034 } 1035 elseif ($current2 == '/*') 1036 { 1037 $endString = '*/'; 1038 $comment = true; 1039 } 1040 elseif ($current == '[') 1041 { 1042 $endString = ']'; 1043 } 1044 else 1045 { 1046 $endString = $current; 1047 } 1048 1049 if ($comment && $start < $i) 1050 { 1051 // Add string exists before comment 1052 $addBlock(substr($string, $start, $i - $start)); 1053 $previous = $string[$i - 1]; 1054 $start = $i; 1055 } 1056 } 1057 } 1058 elseif (!$open) 1059 { 1060 if ($current == '(') 1061 { 1062 $openC++; 1063 $previous = $current; 1064 } 1065 elseif ($current == ')') 1066 { 1067 $openC--; 1068 $previous = $current; 1069 } 1070 elseif ($current == '.') 1071 { 1072 if ($i === $start && $colIdx > 0 && !isset($column[$colIdx])) 1073 { 1074 // Remove whitespace placed before dot 1075 $colIdx--; 1076 } 1077 1078 $previous = $current; 1079 } 1080 elseif ($openC === 0) 1081 { 1082 if (ctype_space($current)) 1083 { 1084 // Normalize whitespace 1085 $string[$i] = ' '; 1086 1087 if ($start < $i) 1088 { 1089 // Add text placed before whitespace 1090 $addBlock(substr($string, $start, $i - $start)); 1091 $colIdx++; 1092 $previous = $string[$i - 1]; 1093 } 1094 elseif (isset($column[$colIdx])) 1095 { 1096 if ($colIdx > 1 || !isset($operators[$previous])) 1097 { 1098 // There was whitespace after comment 1099 $colIdx++; 1100 } 1101 } 1102 1103 // Move cursor forward 1104 $start = $i + 1; 1105 } 1106 elseif (isset($operators[$current]) && ($current !== '*' || $previous !== '.')) 1107 { 1108 if ($start < $i) 1109 { 1110 // Add text before operator 1111 $addBlock(substr($string, $start, $i - $start)); 1112 $colIdx++; 1113 } 1114 elseif (!isset($column[$colIdx]) && isset($operators[$previous])) 1115 { 1116 // Do not create whitespace between operators 1117 $colIdx--; 1118 } 1119 1120 // Add operator 1121 $addBlock($current); 1122 $previous = $current; 1123 $colIdx++; 1124 1125 // Move cursor forward 1126 $start = $i + 1; 1127 } 1128 else 1129 { 1130 $previous = $current; 1131 } 1132 } 1133 } 1134 1135 if (($current == ',' && !$open && $openC == 0) || $i == $length - 1) 1136 { 1137 if ($start < $i && !$comment) 1138 { 1139 // Save remaining text 1140 $addBlock(substr($string, $start, $i - $start)); 1141 } 1142 1143 $columns[] = $column; 1144 1145 // Reset values 1146 $column = []; 1147 $colIdx = 0; 1148 $previous = null; 1149 1150 // Column saved, move cursor forward after comma 1151 $start = $i + 1; 1152 } 1153 } 1154 1155 return $columns; 1156 } 1157 1158 /** 1159 * Method to modify a query already in string format with the needed additions to make the query limited to a particular number of 1160 * results, or start at a particular offset. 1161 * 1162 * @param string $query The query in string format 1163 * @param integer $limit The limit for the result set 1164 * @param integer $offset The offset for the result set 1165 * 1166 * @return string 1167 * 1168 * @since 2.0.0 1169 */ 1170 public function processLimit($query, $limit, $offset = 0) 1171 { 1172 if ($offset > 0) 1173 { 1174 // Find a position of the last comment 1175 $commentPos = strrpos($query, '/*ORDER BY (SELECT 0)*/'); 1176 1177 // If the last comment belongs to this query, not previous subquery 1178 if ($commentPos !== false && $commentPos + 2 === strripos($query, 'ORDER BY', $commentPos + 2)) 1179 { 1180 // We can not use OFFSET without ORDER BY 1181 $query = substr_replace($query, 'ORDER BY (SELECT 0)', $commentPos, 23); 1182 } 1183 1184 $query .= PHP_EOL . 'OFFSET ' . (int) $offset . ' ROWS'; 1185 1186 if ($limit > 0) 1187 { 1188 $query .= PHP_EOL . 'FETCH NEXT ' . (int) $limit . ' ROWS ONLY'; 1189 } 1190 } 1191 elseif ($limit > 0) 1192 { 1193 $position = stripos($query, 'SELECT'); 1194 $distinct = stripos($query, 'SELECT DISTINCT'); 1195 1196 if ($position === $distinct) 1197 { 1198 $query = substr_replace($query, 'SELECT DISTINCT TOP ' . (int) $limit, $position, 15); 1199 } 1200 else 1201 { 1202 $query = substr_replace($query, 'SELECT TOP ' . (int) $limit, $position, 6); 1203 } 1204 } 1205 1206 return $query; 1207 } 1208 1209 /** 1210 * Add a query to UNION with the current query. 1211 * 1212 * Usage: 1213 * $query->union('SELECT name FROM #__foo') 1214 * $query->union('SELECT name FROM #__foo', true) 1215 * 1216 * @param DatabaseQuery|string $query The DatabaseQuery object or string to union. 1217 * @param boolean $distinct True to only return distinct rows from the union. 1218 * 1219 * @return $this 1220 * 1221 * @since 1.0 1222 */ 1223 public function union($query, $distinct = true) 1224 { 1225 // Set up the name with parentheses, the DISTINCT flag is redundant 1226 return $this->merge($distinct ? 'UNION SELECT * FROM ()' : 'UNION ALL SELECT * FROM ()', $query); 1227 } 1228 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Wed Sep 7 05:41:13 2022 | Chilli.vc Blog - For Webmaster,Blog-Writer,System Admin and Domainer |