1<?php 2 3declare(strict_types=1); 4 5namespace PhpMyAdmin; 6 7use PhpMyAdmin\SqlParser\Parser; 8use PhpMyAdmin\SqlParser\Statements\DeleteStatement; 9use PhpMyAdmin\SqlParser\Statements\InsertStatement; 10use PhpMyAdmin\SqlParser\Statements\ReplaceStatement; 11use PhpMyAdmin\SqlParser\Statements\UpdateStatement; 12use PhpMyAdmin\SqlParser\Utils\Query; 13use function abs; 14use function count; 15use function explode; 16use function function_exists; 17use function htmlspecialchars; 18use function implode; 19use function is_array; 20use function is_numeric; 21use function max; 22use function mb_chr; 23use function mb_ord; 24use function mb_stripos; 25use function mb_strlen; 26use function mb_strpos; 27use function mb_strtoupper; 28use function mb_substr; 29use function mb_substr_count; 30use function pow; 31use function preg_match; 32use function preg_replace; 33use function sprintf; 34use function strcmp; 35use function strlen; 36use function strncmp; 37use function strpos; 38use function strtoupper; 39use function substr; 40use function time; 41use function trim; 42 43/** 44 * Library that provides common import functions that are used by import plugins 45 */ 46class Import 47{ 48 /* MySQL type defs */ 49 public const NONE = 0; 50 public const VARCHAR = 1; 51 public const INT = 2; 52 public const DECIMAL = 3; 53 public const BIGINT = 4; 54 public const GEOMETRY = 5; 55 /* Decimal size defs */ 56 public const M = 0; 57 public const D = 1; 58 public const FULL = 2; 59 /* Table array defs */ 60 public const TBL_NAME = 0; 61 public const COL_NAMES = 1; 62 public const ROWS = 2; 63 /* Analysis array defs */ 64 public const TYPES = 0; 65 public const SIZES = 1; 66 public const FORMATTEDSQL = 2; 67 68 public function __construct() 69 { 70 global $dbi; 71 72 $GLOBALS['cfg']['Server']['DisableIS'] = false; 73 74 $checkUserPrivileges = new CheckUserPrivileges($dbi); 75 $checkUserPrivileges->getPrivileges(); 76 } 77 78 /** 79 * Checks whether timeout is getting close 80 * 81 * @return bool true if timeout is close 82 * 83 * @access public 84 */ 85 public function checkTimeout(): bool 86 { 87 global $timestamp, $maximum_time, $timeout_passed; 88 if ($maximum_time == 0) { 89 return false; 90 } 91 92 if ($timeout_passed) { 93 return true; 94 95 /* 5 in next row might be too much */ 96 } 97 98 if (time() - $timestamp > $maximum_time - 5) { 99 $timeout_passed = true; 100 101 return true; 102 } 103 104 return false; 105 } 106 107 /** 108 * Runs query inside import buffer. This is needed to allow displaying 109 * of last SELECT, SHOW or HANDLER results and similar nice stuff. 110 * 111 * @param string $sql query to run 112 * @param string $full query to display, this might be commented 113 * @param array $sql_data SQL parse data storage 114 * 115 * @access public 116 */ 117 public function executeQuery(string $sql, string $full, array &$sql_data): void 118 { 119 global $sql_query, $my_die, $error, $reload, $result, $msg, $cfg, $sql_query_disabled, $db, $dbi; 120 121 $result = $dbi->tryQuery($sql); 122 123 // USE query changes the database, son need to track 124 // while running multiple queries 125 $is_use_query = mb_stripos($sql, 'use ') !== false; 126 127 $msg = '# '; 128 if ($result === false) { // execution failed 129 if (! isset($my_die)) { 130 $my_die = []; 131 } 132 $my_die[] = [ 133 'sql' => $full, 134 'error' => $dbi->getError(), 135 ]; 136 137 $msg .= __('Error'); 138 139 if (! $cfg['IgnoreMultiSubmitErrors']) { 140 $error = true; 141 142 return; 143 } 144 } else { 145 $a_num_rows = (int) @$dbi->numRows($result); 146 $a_aff_rows = (int) @$dbi->affectedRows(); 147 if ($a_num_rows > 0) { 148 $msg .= __('Rows') . ': ' . $a_num_rows; 149 } elseif ($a_aff_rows > 0) { 150 $message = Message::getMessageForAffectedRows( 151 $a_aff_rows 152 ); 153 $msg .= $message->getMessage(); 154 } else { 155 $msg .= __( 156 'MySQL returned an empty result set (i.e. zero ' 157 . 'rows).' 158 ); 159 } 160 161 if (($a_num_rows > 0) || $is_use_query) { 162 $sql_data['valid_sql'][] = $sql; 163 if (! isset($sql_data['valid_queries'])) { 164 $sql_data['valid_queries'] = 0; 165 } 166 $sql_data['valid_queries']++; 167 } 168 } 169 if (! $sql_query_disabled) { 170 $sql_query .= $msg . "\n"; 171 } 172 173 // If a 'USE <db>' SQL-clause was found and the query 174 // succeeded, set our current $db to the new one 175 if ($result != false) { 176 [$db, $reload] = $this->lookForUse( 177 $sql, 178 $db, 179 $reload 180 ); 181 } 182 183 $pattern = '@^[\s]*(DROP|CREATE)[\s]+(IF EXISTS[[:space:]]+)' 184 . '?(TABLE|DATABASE)[[:space:]]+(.+)@im'; 185 if ($result == false 186 || ! preg_match($pattern, $sql) 187 ) { 188 return; 189 } 190 191 $reload = true; 192 } 193 194 /** 195 * Runs query inside import buffer. This is needed to allow displaying 196 * of last SELECT, SHOW or HANDLER results and similar nice stuff. 197 * 198 * @param string $sql query to run 199 * @param string $full query to display, this might be commented 200 * @param array $sql_data SQL parse data storage 201 * 202 * @access public 203 */ 204 public function runQuery( 205 string $sql = '', 206 string $full = '', 207 array &$sql_data = [] 208 ): void { 209 global $import_run_buffer, $go_sql, $complete_query, $display_query, $sql_query, $msg, 210 $skip_queries, $executed_queries, $max_sql_len, $read_multiply, $sql_query_disabled, $run_query; 211 $read_multiply = 1; 212 if (! isset($import_run_buffer)) { 213 // Do we have something to push into buffer? 214 $import_run_buffer = $this->runQueryPost( 215 $import_run_buffer, 216 $sql, 217 $full 218 ); 219 220 return; 221 } 222 223 // Should we skip something? 224 if ($skip_queries > 0) { 225 $skip_queries--; 226 // Do we have something to push into buffer? 227 $import_run_buffer = $this->runQueryPost( 228 $import_run_buffer, 229 $sql, 230 $full 231 ); 232 233 return; 234 } 235 236 if (! empty($import_run_buffer['sql']) 237 && trim($import_run_buffer['sql']) != '' 238 ) { 239 $max_sql_len = max( 240 $max_sql_len, 241 mb_strlen($import_run_buffer['sql']) 242 ); 243 if (! $sql_query_disabled) { 244 $sql_query .= $import_run_buffer['full']; 245 } 246 247 $executed_queries++; 248 249 if ($run_query && $executed_queries < 50) { 250 $go_sql = true; 251 252 if (! $sql_query_disabled) { 253 $complete_query = $sql_query; 254 $display_query = $sql_query; 255 } else { 256 $complete_query = ''; 257 $display_query = ''; 258 } 259 $sql_query = $import_run_buffer['sql']; 260 $sql_data['valid_sql'][] = $import_run_buffer['sql']; 261 $sql_data['valid_full'][] = $import_run_buffer['full']; 262 if (! isset($sql_data['valid_queries'])) { 263 $sql_data['valid_queries'] = 0; 264 } 265 $sql_data['valid_queries']++; 266 } elseif ($run_query) { 267 /* Handle rollback from go_sql */ 268 if ($go_sql && isset($sql_data['valid_full'])) { 269 $queries = $sql_data['valid_sql']; 270 $fulls = $sql_data['valid_full']; 271 $count = $sql_data['valid_queries']; 272 $go_sql = false; 273 274 $sql_data['valid_sql'] = []; 275 $sql_data['valid_queries'] = 0; 276 unset($sql_data['valid_full']); 277 for ($i = 0; $i < $count; $i++) { 278 $this->executeQuery( 279 $queries[$i], 280 $fulls[$i], 281 $sql_data 282 ); 283 } 284 } 285 286 $this->executeQuery( 287 $import_run_buffer['sql'], 288 $import_run_buffer['full'], 289 $sql_data 290 ); 291 } 292 } elseif (! empty($import_run_buffer['full'])) { 293 if ($go_sql) { 294 $complete_query .= $import_run_buffer['full']; 295 $display_query .= $import_run_buffer['full']; 296 } elseif (! $sql_query_disabled) { 297 $sql_query .= $import_run_buffer['full']; 298 } 299 } 300 // check length of query unless we decided to pass it to /sql 301 // (if $run_query is false, we are just displaying so show 302 // the complete query in the textarea) 303 if (! $go_sql && $run_query && ! empty($sql_query)) { 304 if (mb_strlen($sql_query) > 50000 305 || $executed_queries > 50 306 || $max_sql_len > 1000 307 ) { 308 $sql_query = ''; 309 $sql_query_disabled = true; 310 } 311 } 312 313 // Do we have something to push into buffer? 314 $import_run_buffer = $this->runQueryPost($import_run_buffer, $sql, $full); 315 316 // In case of ROLLBACK, notify the user. 317 if (! isset($_POST['rollback_query'])) { 318 return; 319 } 320 321 $msg .= __('[ROLLBACK occurred.]'); 322 } 323 324 /** 325 * Return import run buffer 326 * 327 * @param array $import_run_buffer Buffer of queries for import 328 * @param string $sql SQL query 329 * @param string $full Query to display 330 * 331 * @return array Buffer of queries for import 332 */ 333 public function runQueryPost( 334 ?array $import_run_buffer, 335 string $sql, 336 string $full 337 ): ?array { 338 if (! empty($sql) || ! empty($full)) { 339 return [ 340 'sql' => $sql . ';', 341 'full' => $full . ';', 342 ]; 343 } 344 345 unset($GLOBALS['import_run_buffer']); 346 347 return $import_run_buffer; 348 } 349 350 /** 351 * Looks for the presence of USE to possibly change current db 352 * 353 * @param string $buffer buffer to examine 354 * @param string $db current db 355 * @param bool $reload reload 356 * 357 * @return array (current or new db, whether to reload) 358 * 359 * @access public 360 */ 361 public function lookForUse(?string $buffer, ?string $db, ?bool $reload): array 362 { 363 if (preg_match('@^[\s]*USE[[:space:]]+([\S]+)@i', (string) $buffer, $match)) { 364 $db = trim($match[1]); 365 $db = trim($db, ';'); // for example, USE abc; 366 367 // $db must not contain the escape characters generated by backquote() 368 // ( used in buildSql() as: backquote($db_name), and then called 369 // in runQuery() which in turn calls lookForUse() ) 370 $db = Util::unQuote($db); 371 372 $reload = true; 373 } 374 375 return [ 376 $db, 377 $reload, 378 ]; 379 } 380 381 /** 382 * Returns next part of imported file/buffer 383 * 384 * @param int $size size of buffer to read (this is maximal size function will return) 385 * 386 * @return string|bool part of file/buffer 387 */ 388 public function getNextChunk(?File $importHandle = null, int $size = 32768) 389 { 390 global $charset_conversion, $charset_of_file, $read_multiply; 391 392 // Add some progression while reading large amount of data 393 if ($read_multiply <= 8) { 394 $size *= $read_multiply; 395 } else { 396 $size *= 8; 397 } 398 $read_multiply++; 399 400 // We can not read too much 401 if ($size > $GLOBALS['read_limit']) { 402 $size = $GLOBALS['read_limit']; 403 } 404 405 if ($this->checkTimeout()) { 406 return false; 407 } 408 if ($GLOBALS['finished']) { 409 return true; 410 } 411 412 if ($GLOBALS['import_file'] === 'none') { 413 // Well this is not yet supported and tested, 414 // but should return content of textarea 415 if (mb_strlen($GLOBALS['import_text']) < $size) { 416 $GLOBALS['finished'] = true; 417 418 return $GLOBALS['import_text']; 419 } 420 421 $r = mb_substr($GLOBALS['import_text'], 0, $size); 422 $GLOBALS['offset'] += $size; 423 $GLOBALS['import_text'] = mb_substr($GLOBALS['import_text'], $size); 424 425 return $r; 426 } 427 428 if ($importHandle === null) { 429 return false; 430 } 431 432 $result = $importHandle->read($size); 433 $GLOBALS['finished'] = $importHandle->eof(); 434 $GLOBALS['offset'] += $size; 435 436 if ($charset_conversion) { 437 return Encoding::convertString($charset_of_file, 'utf-8', $result); 438 } 439 440 /** 441 * Skip possible byte order marks (I do not think we need more 442 * charsets, but feel free to add more, you can use wikipedia for 443 * reference: <https://en.wikipedia.org/wiki/Byte_Order_Mark>) 444 * 445 * @todo BOM could be used for charset autodetection 446 */ 447 if ($GLOBALS['offset'] == $size) { 448 $result = $this->skipByteOrderMarksFromContents($result); 449 } 450 451 return $result; 452 } 453 454 /** 455 * Skip possible byte order marks (I do not think we need more 456 * charsets, but feel free to add more, you can use wikipedia for 457 * reference: <https://en.wikipedia.org/wiki/Byte_Order_Mark>) 458 * 459 * @param string $contents The contents to strip BOM 460 * 461 * @todo BOM could be used for charset autodetection 462 */ 463 public function skipByteOrderMarksFromContents(string $contents): string 464 { 465 // Do not use mb_ functions they are sensible to mb_internal_encoding() 466 467 // UTF-8 468 if (strncmp($contents, "\xEF\xBB\xBF", 3) === 0) { 469 return substr($contents, 3); 470 471 // UTF-16 BE, LE 472 } 473 474 if (strncmp($contents, "\xFE\xFF", 2) === 0 475 || strncmp($contents, "\xFF\xFE", 2) === 0 476 ) { 477 return substr($contents, 2); 478 } 479 480 return $contents; 481 } 482 483 /** 484 * Returns the "Excel" column name (i.e. 1 = "A", 26 = "Z", 27 = "AA", etc.) 485 * 486 * This functions uses recursion to build the Excel column name. 487 * 488 * The column number (1-26) is converted to the responding 489 * ASCII character (A-Z) and returned. 490 * 491 * If the column number is bigger than 26 (= num of letters in alphabet), 492 * an extra character needs to be added. To find this extra character, 493 * the number is divided by 26 and this value is passed to another instance 494 * of the same function (hence recursion). In that new instance the number is 495 * evaluated again, and if it is still bigger than 26, it is divided again 496 * and passed to another instance of the same function. This continues until 497 * the number is smaller than 26. Then the last called function returns 498 * the corresponding ASCII character to the function that called it. 499 * Each time a called function ends an extra character is added to the column name. 500 * When the first function is reached, the last character is added and the complete 501 * column name is returned. 502 * 503 * @param int $num the column number 504 * 505 * @return string The column's "Excel" name 506 * 507 * @access public 508 */ 509 public function getColumnAlphaName(int $num): string 510 { 511 $A = 65; // ASCII value for capital "A" 512 $col_name = ''; 513 514 if ($num > 26) { 515 $div = (int) ($num / 26); 516 $remain = $num % 26; 517 518 // subtract 1 of divided value in case the modulus is 0, 519 // this is necessary because A-Z has no 'zero' 520 if ($remain == 0) { 521 $div--; 522 } 523 524 // recursive function call 525 $col_name = $this->getColumnAlphaName($div); 526 // use modulus as new column number 527 $num = $remain; 528 } 529 530 if ($num == 0) { 531 // use 'Z' if column number is 0, 532 // this is necessary because A-Z has no 'zero' 533 $col_name .= mb_chr($A + 26 - 1); 534 } else { 535 // convert column number to ASCII character 536 $col_name .= mb_chr($A + $num - 1); 537 } 538 539 return $col_name; 540 } 541 542 /** 543 * Returns the column number based on the Excel name. 544 * So "A" = 1, "Z" = 26, "AA" = 27, etc. 545 * 546 * Basically this is a base26 (A-Z) to base10 (0-9) conversion. 547 * It iterates through all characters in the column name and 548 * calculates the corresponding value, based on character value 549 * (A = 1, ..., Z = 26) and position in the string. 550 * 551 * @param string $name column name(i.e. "A", or "BC", etc.) 552 * 553 * @return int The column number 554 * 555 * @access public 556 */ 557 public function getColumnNumberFromName(string $name): int 558 { 559 if (empty($name)) { 560 return 0; 561 } 562 563 $name = mb_strtoupper($name); 564 $num_chars = mb_strlen($name); 565 $column_number = 0; 566 for ($i = 0; $i < $num_chars; ++$i) { 567 // read string from back to front 568 $char_pos = $num_chars - 1 - $i; 569 570 // convert capital character to ASCII value 571 // and subtract 64 to get corresponding decimal value 572 // ASCII value of "A" is 65, "B" is 66, etc. 573 // Decimal equivalent of "A" is 1, "B" is 2, etc. 574 $number = (int) (mb_ord($name[$char_pos]) - 64); 575 576 // base26 to base10 conversion : multiply each number 577 // with corresponding value of the position, in this case 578 // $i=0 : 1; $i=1 : 26; $i=2 : 676; ... 579 $column_number += $number * pow(26, $i); 580 } 581 582 return $column_number; 583 } 584 585 /** 586 * Obtains the precision (total # of digits) from a size of type decimal 587 * 588 * @param string $last_cumulative_size Size of type decimal 589 * 590 * @return int Precision of the given decimal size notation 591 * 592 * @access public 593 */ 594 public function getDecimalPrecision(string $last_cumulative_size): int 595 { 596 return (int) substr( 597 $last_cumulative_size, 598 0, 599 (int) strpos($last_cumulative_size, ',') 600 ); 601 } 602 603 /** 604 * Obtains the scale (# of digits to the right of the decimal point) 605 * from a size of type decimal 606 * 607 * @param string $last_cumulative_size Size of type decimal 608 * 609 * @return int Scale of the given decimal size notation 610 * 611 * @access public 612 */ 613 public function getDecimalScale(string $last_cumulative_size): int 614 { 615 return (int) substr( 616 $last_cumulative_size, 617 strpos($last_cumulative_size, ',') + 1, 618 strlen($last_cumulative_size) - strpos($last_cumulative_size, ',') 619 ); 620 } 621 622 /** 623 * Obtains the decimal size of a given cell 624 * 625 * @param string $cell cell content 626 * 627 * @return array Contains the precision, scale, and full size 628 * representation of the given decimal cell 629 * 630 * @access public 631 */ 632 public function getDecimalSize(string $cell): array 633 { 634 $curr_size = mb_strlen($cell); 635 $decPos = mb_strpos($cell, '.'); 636 $decPrecision = $curr_size - 1 - $decPos; 637 638 $m = $curr_size - 1; 639 $d = $decPrecision; 640 641 return [ 642 $m, 643 $d, 644 $m . ',' . $d, 645 ]; 646 } 647 648 /** 649 * Obtains the size of the given cell 650 * 651 * @param string|int $last_cumulative_size Last cumulative column size 652 * @param int|null $last_cumulative_type Last cumulative column type 653 * (NONE or VARCHAR or DECIMAL or INT or BIGINT) 654 * @param int $curr_type Type of the current cell 655 * (NONE or VARCHAR or DECIMAL or INT or BIGINT) 656 * @param string $cell The current cell 657 * 658 * @return string|int Size of the given cell in the type-appropriate format 659 * 660 * @access public 661 * @todo Handle the error cases more elegantly 662 */ 663 public function detectSize( 664 $last_cumulative_size, 665 ?int $last_cumulative_type, 666 int $curr_type, 667 string $cell 668 ) { 669 $curr_size = mb_strlen($cell); 670 671 /** 672 * If the cell is NULL, don't treat it as a varchar 673 */ 674 if (! strcmp('NULL', $cell)) { 675 return $last_cumulative_size; 676 } 677 678 if ($curr_type == self::VARCHAR) { 679 /** 680 * What to do if the current cell is of type VARCHAR 681 */ 682 /** 683 * The last cumulative type was VARCHAR 684 */ 685 if ($last_cumulative_type == self::VARCHAR) { 686 if ($curr_size >= $last_cumulative_size) { 687 return $curr_size; 688 } 689 690 return $last_cumulative_size; 691 } 692 693 if ($last_cumulative_type == self::DECIMAL) { 694 /** 695 * The last cumulative type was DECIMAL 696 */ 697 $oldM = $this->getDecimalPrecision($last_cumulative_size); 698 699 if ($curr_size >= $oldM) { 700 return $curr_size; 701 } 702 703 return $oldM; 704 } 705 706 if ($last_cumulative_type == self::BIGINT || $last_cumulative_type == self::INT) { 707 /** 708 * The last cumulative type was BIGINT or INT 709 */ 710 if ($curr_size >= $last_cumulative_size) { 711 return $curr_size; 712 } 713 714 return $last_cumulative_size; 715 } 716 717 if (! isset($last_cumulative_type) || $last_cumulative_type == self::NONE) { 718 /** 719 * This is the first row to be analyzed 720 */ 721 return $curr_size; 722 } 723 724 /** 725 * An error has DEFINITELY occurred 726 */ 727 /** 728 * TODO: Handle this MUCH more elegantly 729 */ 730 731 return -1; 732 } 733 734 if ($curr_type == self::DECIMAL) { 735 /** 736 * What to do if the current cell is of type DECIMAL 737 */ 738 /** 739 * The last cumulative type was VARCHAR 740 */ 741 if ($last_cumulative_type == self::VARCHAR) { 742 /* Convert $last_cumulative_size from varchar to decimal format */ 743 $size = $this->getDecimalSize($cell); 744 745 if ($size[self::M] >= $last_cumulative_size) { 746 return $size[self::M]; 747 } 748 749 return $last_cumulative_size; 750 } 751 752 if ($last_cumulative_type == self::DECIMAL) { 753 /** 754 * The last cumulative type was DECIMAL 755 */ 756 $size = $this->getDecimalSize($cell); 757 758 $oldM = $this->getDecimalPrecision($last_cumulative_size); 759 $oldD = $this->getDecimalScale($last_cumulative_size); 760 761 /* New val if M or D is greater than current largest */ 762 if ($size[self::M] > $oldM || $size[self::D] > $oldD) { 763 /* Take the largest of both types */ 764 return (string) (($size[self::M] > $oldM ? $size[self::M] : $oldM) 765 . ',' . ($size[self::D] > $oldD ? $size[self::D] : $oldD)); 766 } 767 768 return $last_cumulative_size; 769 } 770 771 if ($last_cumulative_type == self::BIGINT || $last_cumulative_type == self::INT) { 772 /** 773 * The last cumulative type was BIGINT or INT 774 */ 775 /* Convert $last_cumulative_size from int to decimal format */ 776 $size = $this->getDecimalSize($cell); 777 778 if ($size[self::M] >= $last_cumulative_size) { 779 return $size[self::FULL]; 780 } 781 782 return $last_cumulative_size . ',' . $size[self::D]; 783 } 784 785 if (! isset($last_cumulative_type) || $last_cumulative_type == self::NONE) { 786 /** 787 * This is the first row to be analyzed 788 */ 789 /* First row of the column */ 790 $size = $this->getDecimalSize($cell); 791 792 return $size[self::FULL]; 793 } 794 795 /** 796 * An error has DEFINITELY occurred 797 */ 798 /** 799 * TODO: Handle this MUCH more elegantly 800 */ 801 802 return -1; 803 } 804 805 if ($curr_type == self::BIGINT || $curr_type == self::INT) { 806 /** 807 * What to do if the current cell is of type BIGINT or INT 808 */ 809 /** 810 * The last cumulative type was VARCHAR 811 */ 812 if ($last_cumulative_type == self::VARCHAR) { 813 if ($curr_size >= $last_cumulative_size) { 814 return $curr_size; 815 } 816 817 return $last_cumulative_size; 818 } 819 820 if ($last_cumulative_type == self::DECIMAL) { 821 /** 822 * The last cumulative type was DECIMAL 823 */ 824 $oldM = $this->getDecimalPrecision($last_cumulative_size); 825 $oldD = $this->getDecimalScale($last_cumulative_size); 826 $oldInt = $oldM - $oldD; 827 $newInt = mb_strlen((string) $cell); 828 829 /* See which has the larger integer length */ 830 if ($oldInt >= $newInt) { 831 /* Use old decimal size */ 832 return $last_cumulative_size; 833 } 834 835 /* Use $newInt + $oldD as new M */ 836 return ($newInt + $oldD) . ',' . $oldD; 837 } 838 839 if ($last_cumulative_type == self::BIGINT || $last_cumulative_type == self::INT) { 840 /** 841 * The last cumulative type was BIGINT or INT 842 */ 843 if ($curr_size >= $last_cumulative_size) { 844 return $curr_size; 845 } 846 847 return $last_cumulative_size; 848 } 849 850 if (! isset($last_cumulative_type) || $last_cumulative_type == self::NONE) { 851 /** 852 * This is the first row to be analyzed 853 */ 854 return $curr_size; 855 } 856 857 /** 858 * An error has DEFINITELY occurred 859 */ 860 /** 861 * TODO: Handle this MUCH more elegantly 862 */ 863 864 return -1; 865 } 866 867 /** 868 * An error has DEFINITELY occurred 869 */ 870 /** 871 * TODO: Handle this MUCH more elegantly 872 */ 873 874 return -1; 875 } 876 877 /** 878 * Determines what MySQL type a cell is 879 * 880 * @param int $last_cumulative_type Last cumulative column type 881 * (VARCHAR or INT or BIGINT or DECIMAL or NONE) 882 * @param string|null $cell String representation of the cell for which 883 * a best-fit type is to be determined 884 * 885 * @return int The MySQL type representation 886 * (VARCHAR or INT or BIGINT or DECIMAL or NONE) 887 * 888 * @access public 889 */ 890 public function detectType(?int $last_cumulative_type, ?string $cell): int 891 { 892 /** 893 * If numeric, determine if decimal, int or bigint 894 * Else, we call it varchar for simplicity 895 */ 896 897 if (! strcmp('NULL', (string) $cell)) { 898 if ($last_cumulative_type === null || $last_cumulative_type == self::NONE) { 899 return self::NONE; 900 } 901 902 return $last_cumulative_type; 903 } 904 905 if (! is_numeric($cell)) { 906 return self::VARCHAR; 907 } 908 909 if ($cell == (string) (float) $cell 910 && mb_strpos((string) $cell, '.') !== false 911 && mb_substr_count((string) $cell, '.') === 1 912 ) { 913 return self::DECIMAL; 914 } 915 916 if (abs((int) $cell) > 2147483647) { 917 return self::BIGINT; 918 } 919 920 if ($cell !== (string) (int) $cell) { 921 return self::VARCHAR; 922 } 923 924 return self::INT; 925 } 926 927 /** 928 * Determines if the column types are int, decimal, or string 929 * 930 * @link https://wiki.phpmyadmin.net/pma/Import 931 * 932 * @param array $table array(string $table_name, array $col_names, array $rows) 933 * 934 * @return array|bool array(array $types, array $sizes) 935 * 936 * @access public 937 * @todo Handle the error case more elegantly 938 */ 939 public function analyzeTable(array &$table) 940 { 941 /* Get number of rows in table */ 942 $numRows = count($table[self::ROWS]); 943 /* Get number of columns */ 944 $numCols = count($table[self::COL_NAMES]); 945 /* Current type for each column */ 946 $types = []; 947 $sizes = []; 948 949 /* Initialize $sizes to all 0's */ 950 for ($i = 0; $i < $numCols; ++$i) { 951 $sizes[$i] = 0; 952 } 953 954 /* Initialize $types to NONE */ 955 for ($i = 0; $i < $numCols; ++$i) { 956 $types[$i] = self::NONE; 957 } 958 959 /* If the passed array is not of the correct form, do not process it */ 960 if (! is_array($table) 961 || is_array($table[self::TBL_NAME]) 962 || ! is_array($table[self::COL_NAMES]) 963 || ! is_array($table[self::ROWS]) 964 ) { 965 /** 966 * TODO: Handle this better 967 */ 968 969 return false; 970 } 971 972 /* Analyze each column */ 973 for ($i = 0; $i < $numCols; ++$i) { 974 /* Analyze the column in each row */ 975 for ($j = 0; $j < $numRows; ++$j) { 976 $cellValue = $table[self::ROWS][$j][$i]; 977 /* Determine type of the current cell */ 978 $curr_type = $this->detectType($types[$i], $cellValue === null ? null : (string) $cellValue); 979 /* Determine size of the current cell */ 980 $sizes[$i] = $this->detectSize( 981 $sizes[$i], 982 $types[$i], 983 $curr_type, 984 (string) $cellValue 985 ); 986 987 /** 988 * If a type for this column has already been declared, 989 * only alter it if it was a number and a varchar was found 990 */ 991 if ($curr_type == self::NONE) { 992 continue; 993 } 994 995 if ($curr_type == self::VARCHAR) { 996 $types[$i] = self::VARCHAR; 997 } elseif ($curr_type == self::DECIMAL) { 998 if ($types[$i] != self::VARCHAR) { 999 $types[$i] = self::DECIMAL; 1000 } 1001 } elseif ($curr_type == self::BIGINT) { 1002 if ($types[$i] != self::VARCHAR && $types[$i] != self::DECIMAL) { 1003 $types[$i] = self::BIGINT; 1004 } 1005 } elseif ($curr_type == self::INT) { 1006 if ($types[$i] != self::VARCHAR 1007 && $types[$i] != self::DECIMAL 1008 && $types[$i] != self::BIGINT 1009 ) { 1010 $types[$i] = self::INT; 1011 } 1012 } 1013 } 1014 } 1015 1016 /* Check to ensure that all types are valid */ 1017 $len = count($types); 1018 for ($n = 0; $n < $len; ++$n) { 1019 if (strcmp((string) self::NONE, (string) $types[$n])) { 1020 continue; 1021 } 1022 1023 $types[$n] = self::VARCHAR; 1024 $sizes[$n] = '10'; 1025 } 1026 1027 return [ 1028 $types, 1029 $sizes, 1030 ]; 1031 } 1032 1033 /** 1034 * Builds and executes SQL statements to create the database and tables 1035 * as necessary, as well as insert all the data. 1036 * 1037 * @link https://wiki.phpmyadmin.net/pma/Import 1038 * 1039 * @param string $db_name Name of the database 1040 * @param array $tables Array of tables for the specified database 1041 * @param array|null $analyses Analyses of the tables 1042 * @param array|null $additional_sql Additional SQL statements to be executed 1043 * @param array|null $options Associative array of options 1044 * @param array $sql_data 2-element array with sql data 1045 * 1046 * @access public 1047 */ 1048 public function buildSql( 1049 string $db_name, 1050 array &$tables, 1051 ?array &$analyses = null, 1052 ?array &$additional_sql = null, 1053 ?array $options = null, 1054 array &$sql_data = [] 1055 ): void { 1056 global $import_notice, $dbi; 1057 1058 /* Needed to quell the beast that is Message */ 1059 $import_notice = null; 1060 1061 /* Take care of the options */ 1062 if (isset($options['db_collation']) && $options['db_collation'] !== null) { 1063 $collation = $options['db_collation']; 1064 } else { 1065 $collation = 'utf8_general_ci'; 1066 } 1067 1068 if (isset($options['db_charset']) && $options['db_charset'] !== null) { 1069 $charset = $options['db_charset']; 1070 } else { 1071 $charset = 'utf8'; 1072 } 1073 1074 if (isset($options['create_db'])) { 1075 $create_db = $options['create_db']; 1076 } else { 1077 $create_db = true; 1078 } 1079 1080 /** 1081 * Create SQL code to handle the database 1082 * 1083 * @var array<int,string> $sql 1084 */ 1085 $sql = []; 1086 1087 if ($create_db) { 1088 $sql[] = 'CREATE DATABASE IF NOT EXISTS ' . Util::backquote($db_name) 1089 . ' DEFAULT CHARACTER SET ' . $charset . ' COLLATE ' . $collation 1090 . ';'; 1091 } 1092 1093 /** 1094 * The calling plug-in should include this statement, 1095 * if necessary, in the $additional_sql parameter 1096 * 1097 * $sql[] = "USE " . backquote($db_name); 1098 */ 1099 1100 /* Execute the SQL statements create above */ 1101 $sql_len = count($sql); 1102 for ($i = 0; $i < $sql_len; ++$i) { 1103 $this->runQuery($sql[$i], $sql[$i], $sql_data); 1104 } 1105 1106 /* No longer needed */ 1107 unset($sql); 1108 1109 /* Run the $additional_sql statements supplied by the caller plug-in */ 1110 if ($additional_sql != null) { 1111 /* Clean the SQL first */ 1112 $additional_sql_len = count($additional_sql); 1113 1114 /** 1115 * Only match tables for now, because CREATE IF NOT EXISTS 1116 * syntax is lacking or nonexisting for views, triggers, 1117 * functions, and procedures. 1118 * 1119 * See: https://bugs.mysql.com/bug.php?id=15287 1120 * 1121 * To the best of my knowledge this is still an issue. 1122 * 1123 * $pattern = 'CREATE (TABLE|VIEW|TRIGGER|FUNCTION|PROCEDURE)'; 1124 */ 1125 $pattern = '/CREATE [^`]*(TABLE)/'; 1126 $replacement = 'CREATE \\1 IF NOT EXISTS'; 1127 1128 /* Change CREATE statements to CREATE IF NOT EXISTS to support 1129 * inserting into existing structures 1130 */ 1131 for ($i = 0; $i < $additional_sql_len; ++$i) { 1132 $additional_sql[$i] = preg_replace( 1133 $pattern, 1134 $replacement, 1135 $additional_sql[$i] 1136 ); 1137 /* Execute the resulting statements */ 1138 $this->runQuery($additional_sql[$i], $additional_sql[$i], $sql_data); 1139 } 1140 } 1141 1142 if ($analyses != null) { 1143 $type_array = [ 1144 self::NONE => 'NULL', 1145 self::VARCHAR => 'varchar', 1146 self::INT => 'int', 1147 self::DECIMAL => 'decimal', 1148 self::BIGINT => 'bigint', 1149 self::GEOMETRY => 'geometry', 1150 ]; 1151 1152 /* TODO: Do more checking here to make sure they really are matched */ 1153 if (count($tables) != count($analyses)) { 1154 exit; 1155 } 1156 1157 /* Create SQL code to create the tables */ 1158 $num_tables = count($tables); 1159 for ($i = 0; $i < $num_tables; ++$i) { 1160 $num_cols = count($tables[$i][self::COL_NAMES]); 1161 $tempSQLStr = 'CREATE TABLE IF NOT EXISTS ' 1162 . Util::backquote($db_name) 1163 . '.' . Util::backquote($tables[$i][self::TBL_NAME]) . ' ('; 1164 for ($j = 0; $j < $num_cols; ++$j) { 1165 $size = $analyses[$i][self::SIZES][$j]; 1166 if ((int) $size == 0) { 1167 $size = 10; 1168 } 1169 1170 $tempSQLStr .= Util::backquote( 1171 $tables[$i][self::COL_NAMES][$j] 1172 ) . ' ' 1173 . $type_array[$analyses[$i][self::TYPES][$j]]; 1174 if ($analyses[$i][self::TYPES][$j] != self::GEOMETRY) { 1175 $tempSQLStr .= '(' . $size . ')'; 1176 } 1177 1178 if ($j == count($tables[$i][self::COL_NAMES]) - 1) { 1179 continue; 1180 } 1181 1182 $tempSQLStr .= ', '; 1183 } 1184 $tempSQLStr .= ') DEFAULT CHARACTER SET ' . $charset 1185 . ' COLLATE ' . $collation . ';'; 1186 1187 /** 1188 * Each SQL statement is executed immediately 1189 * after it is formed so that we don't have 1190 * to store them in a (possibly large) buffer 1191 */ 1192 $this->runQuery($tempSQLStr, $tempSQLStr, $sql_data); 1193 } 1194 } 1195 1196 /** 1197 * Create the SQL statements to insert all the data 1198 * 1199 * Only one insert query is formed for each table 1200 */ 1201 $tempSQLStr = ''; 1202 $col_count = 0; 1203 $num_tables = count($tables); 1204 for ($i = 0; $i < $num_tables; ++$i) { 1205 $num_cols = count($tables[$i][self::COL_NAMES]); 1206 $num_rows = count($tables[$i][self::ROWS]); 1207 1208 $tempSQLStr = 'INSERT INTO ' . Util::backquote($db_name) . '.' 1209 . Util::backquote($tables[$i][self::TBL_NAME]) . ' ('; 1210 1211 for ($m = 0; $m < $num_cols; ++$m) { 1212 $tempSQLStr .= Util::backquote($tables[$i][self::COL_NAMES][$m]); 1213 1214 if ($m == $num_cols - 1) { 1215 continue; 1216 } 1217 1218 $tempSQLStr .= ', '; 1219 } 1220 1221 $tempSQLStr .= ') VALUES '; 1222 1223 for ($j = 0; $j < $num_rows; ++$j) { 1224 $tempSQLStr .= '('; 1225 1226 for ($k = 0; $k < $num_cols; ++$k) { 1227 // If fully formatted SQL, no need to enclose 1228 // with apostrophes, add slashes etc. 1229 if ($analyses != null 1230 && isset($analyses[$i][self::FORMATTEDSQL][$col_count]) 1231 && $analyses[$i][self::FORMATTEDSQL][$col_count] == true 1232 ) { 1233 $tempSQLStr .= (string) $tables[$i][self::ROWS][$j][$k]; 1234 } else { 1235 if ($analyses != null) { 1236 $is_varchar = ($analyses[$i][self::TYPES][$col_count] === self::VARCHAR); 1237 } else { 1238 $is_varchar = ! is_numeric($tables[$i][self::ROWS][$j][$k]); 1239 } 1240 1241 /* Don't put quotes around NULL fields */ 1242 if (! strcmp((string) $tables[$i][self::ROWS][$j][$k], 'NULL')) { 1243 $is_varchar = false; 1244 } 1245 1246 $tempSQLStr .= $is_varchar ? "'" : ''; 1247 $tempSQLStr .= $dbi->escapeString( 1248 (string) $tables[$i][self::ROWS][$j][$k] 1249 ); 1250 $tempSQLStr .= $is_varchar ? "'" : ''; 1251 } 1252 1253 if ($k != $num_cols - 1) { 1254 $tempSQLStr .= ', '; 1255 } 1256 1257 if ($col_count == $num_cols - 1) { 1258 $col_count = 0; 1259 } else { 1260 $col_count++; 1261 } 1262 1263 /* Delete the cell after we are done with it */ 1264 unset($tables[$i][self::ROWS][$j][$k]); 1265 } 1266 1267 $tempSQLStr .= ')'; 1268 1269 if ($j != $num_rows - 1) { 1270 $tempSQLStr .= ",\n "; 1271 } 1272 1273 $col_count = 0; 1274 /* Delete the row after we are done with it */ 1275 unset($tables[$i][self::ROWS][$j]); 1276 } 1277 1278 $tempSQLStr .= ';'; 1279 1280 /** 1281 * Each SQL statement is executed immediately 1282 * after it is formed so that we don't have 1283 * to store them in a (possibly large) buffer 1284 */ 1285 $this->runQuery($tempSQLStr, $tempSQLStr, $sql_data); 1286 } 1287 1288 /* No longer needed */ 1289 unset($tempSQLStr); 1290 1291 /** 1292 * A work in progress 1293 */ 1294 1295 /* Add the viewable structures from $additional_sql 1296 * to $tables so they are also displayed 1297 */ 1298 $view_pattern = '@VIEW `[^`]+`\.`([^`]+)@'; 1299 $table_pattern = '@CREATE TABLE IF NOT EXISTS `([^`]+)`@'; 1300 /* Check a third pattern to make sure its not a "USE `db_name`;" statement */ 1301 1302 $regs = []; 1303 1304 $inTables = false; 1305 1306 $additional_sql_len = $additional_sql === null ? 0 : count($additional_sql); 1307 for ($i = 0; $i < $additional_sql_len; ++$i) { 1308 preg_match($view_pattern, $additional_sql[$i], $regs); 1309 1310 if (count($regs) === 0) { 1311 preg_match($table_pattern, $additional_sql[$i], $regs); 1312 } 1313 1314 if (count($regs)) { 1315 for ($n = 0; $n < $num_tables; ++$n) { 1316 if (! strcmp($regs[1], $tables[$n][self::TBL_NAME])) { 1317 $inTables = true; 1318 break; 1319 } 1320 } 1321 1322 if (! $inTables) { 1323 $tables[] = [self::TBL_NAME => $regs[1]]; 1324 } 1325 } 1326 1327 /* Reset the array */ 1328 $regs = []; 1329 $inTables = false; 1330 } 1331 1332 $params = ['db' => $db_name]; 1333 $db_url = Url::getFromRoute('/database/structure', $params); 1334 $db_ops_url = Url::getFromRoute('/database/operations', $params); 1335 1336 $message = '<br><br>'; 1337 $message .= '<strong>' . __( 1338 'The following structures have either been created or altered. Here you can:' 1339 ) . '</strong><br>'; 1340 $message .= '<ul><li>' . __( 1341 "View a structure's contents by clicking on its name." 1342 ) . '</li>'; 1343 $message .= '<li>' . __( 1344 'Change any of its settings by clicking the corresponding "Options" link.' 1345 ) . '</li>'; 1346 $message .= '<li>' . __('Edit structure by following the "Structure" link.') 1347 . '</li>'; 1348 $message .= sprintf( 1349 '<br><li><a href="%s" title="%s">%s</a> (<a href="%s" title="%s">' 1350 . __('Options') . '</a>)</li>', 1351 $db_url, 1352 sprintf( 1353 __('Go to database: %s'), 1354 htmlspecialchars(Util::backquote($db_name)) 1355 ), 1356 htmlspecialchars($db_name), 1357 $db_ops_url, 1358 sprintf( 1359 __('Edit settings for %s'), 1360 htmlspecialchars(Util::backquote($db_name)) 1361 ) 1362 ); 1363 1364 $message .= '<ul>'; 1365 1366 unset($params); 1367 1368 foreach ($tables as $table) { 1369 $params = [ 1370 'db' => $db_name, 1371 'table' => (string) $table[self::TBL_NAME], 1372 ]; 1373 $tbl_url = Url::getFromRoute('/sql', $params); 1374 $tbl_struct_url = Url::getFromRoute('/table/structure', $params); 1375 $tbl_ops_url = Url::getFromRoute('/table/operations', $params); 1376 1377 unset($params); 1378 1379 $_table = new Table($table[self::TBL_NAME], $db_name); 1380 if (! $_table->isView()) { 1381 $message .= sprintf( 1382 '<li><a href="%s" title="%s">%s</a> (<a href="%s" title="%s">' . __( 1383 'Structure' 1384 ) . '</a>) (<a href="%s" title="%s">' . __('Options') . '</a>)</li>', 1385 $tbl_url, 1386 sprintf( 1387 __('Go to table: %s'), 1388 htmlspecialchars( 1389 Util::backquote($table[self::TBL_NAME]) 1390 ) 1391 ), 1392 htmlspecialchars($table[self::TBL_NAME]), 1393 $tbl_struct_url, 1394 sprintf( 1395 __('Structure of %s'), 1396 htmlspecialchars( 1397 Util::backquote($table[self::TBL_NAME]) 1398 ) 1399 ), 1400 $tbl_ops_url, 1401 sprintf( 1402 __('Edit settings for %s'), 1403 htmlspecialchars( 1404 Util::backquote($table[self::TBL_NAME]) 1405 ) 1406 ) 1407 ); 1408 } else { 1409 $message .= sprintf( 1410 '<li><a href="%s" title="%s">%s</a></li>', 1411 $tbl_url, 1412 sprintf( 1413 __('Go to view: %s'), 1414 htmlspecialchars( 1415 Util::backquote($table[self::TBL_NAME]) 1416 ) 1417 ), 1418 htmlspecialchars($table[self::TBL_NAME]) 1419 ); 1420 } 1421 } 1422 1423 $message .= '</ul></ul>'; 1424 1425 $import_notice = $message; 1426 } 1427 1428 /** 1429 * Handles request for Simulation of UPDATE/DELETE queries. 1430 */ 1431 public function handleSimulateDmlRequest(): void 1432 { 1433 global $dbi; 1434 1435 $response = Response::getInstance(); 1436 $error = false; 1437 $error_msg = __('Only single-table UPDATE and DELETE queries can be simulated.'); 1438 $sql_delimiter = $_POST['sql_delimiter']; 1439 $sql_data = []; 1440 $queries = explode($sql_delimiter, $GLOBALS['sql_query']); 1441 foreach ($queries as $sql_query) { 1442 if (empty($sql_query)) { 1443 continue; 1444 } 1445 1446 // Parsing the query. 1447 $parser = new Parser($sql_query); 1448 1449 if (empty($parser->statements[0])) { 1450 continue; 1451 } 1452 1453 $statement = $parser->statements[0]; 1454 1455 $analyzed_sql_results = [ 1456 'query' => $sql_query, 1457 'parser' => $parser, 1458 'statement' => $statement, 1459 ]; 1460 1461 if (! ($statement instanceof UpdateStatement 1462 || $statement instanceof DeleteStatement) 1463 || ! empty($statement->join) 1464 ) { 1465 $error = $error_msg; 1466 break; 1467 } 1468 1469 $tables = Query::getTables($statement); 1470 if (count($tables) > 1) { 1471 $error = $error_msg; 1472 break; 1473 } 1474 1475 // Get the matched rows for the query. 1476 $result = $this->getMatchedRows($analyzed_sql_results); 1477 $error = $dbi->getError(); 1478 1479 if ($error) { 1480 break; 1481 } 1482 1483 $sql_data[] = $result; 1484 } 1485 1486 if ($error) { 1487 $message = Message::rawError($error); 1488 $response->addJSON('message', $message); 1489 $response->addJSON('sql_data', false); 1490 } else { 1491 $response->addJSON('sql_data', $sql_data); 1492 } 1493 } 1494 1495 /** 1496 * Find the matching rows for UPDATE/DELETE query. 1497 * 1498 * @param array $analyzed_sql_results Analyzed SQL results from parser. 1499 * 1500 * @return array 1501 */ 1502 public function getMatchedRows(array $analyzed_sql_results = []): array 1503 { 1504 $statement = $analyzed_sql_results['statement']; 1505 1506 $matched_row_query = ''; 1507 if ($statement instanceof DeleteStatement) { 1508 $matched_row_query = $this->getSimulatedDeleteQuery($analyzed_sql_results); 1509 } elseif ($statement instanceof UpdateStatement) { 1510 $matched_row_query = $this->getSimulatedUpdateQuery($analyzed_sql_results); 1511 } 1512 1513 // Execute the query and get the number of matched rows. 1514 $matched_rows = $this->executeMatchedRowQuery($matched_row_query); 1515 1516 // URL to matched rows. 1517 $_url_params = [ 1518 'db' => $GLOBALS['db'], 1519 'sql_query' => $matched_row_query, 1520 'sql_signature' => Core::signSqlQuery($matched_row_query), 1521 ]; 1522 $matched_rows_url = Url::getFromRoute('/sql', $_url_params); 1523 1524 return [ 1525 'sql_query' => Html\Generator::formatSql($analyzed_sql_results['query']), 1526 'matched_rows' => $matched_rows, 1527 'matched_rows_url' => $matched_rows_url, 1528 ]; 1529 } 1530 1531 /** 1532 * Transforms a UPDATE query into SELECT statement. 1533 * 1534 * @param array $analyzed_sql_results Analyzed SQL results from parser. 1535 * 1536 * @return string SQL query 1537 */ 1538 public function getSimulatedUpdateQuery(array $analyzed_sql_results): string 1539 { 1540 $table_references = Query::getTables( 1541 $analyzed_sql_results['statement'] 1542 ); 1543 1544 $where = Query::getClause( 1545 $analyzed_sql_results['statement'], 1546 $analyzed_sql_results['parser']->list, 1547 'WHERE' 1548 ); 1549 1550 if (empty($where)) { 1551 $where = '1'; 1552 } 1553 1554 $columns = []; 1555 $diff = []; 1556 foreach ($analyzed_sql_results['statement']->set as $set) { 1557 $columns[] = $set->column; 1558 $not_equal_operator = ' <> '; 1559 if (strtoupper($set->value) === 'NULL') { 1560 $not_equal_operator = ' IS NOT '; 1561 } 1562 $diff[] = $set->column . $not_equal_operator . $set->value; 1563 } 1564 if (! empty($diff)) { 1565 $where .= ' AND (' . implode(' OR ', $diff) . ')'; 1566 } 1567 1568 $order_and_limit = ''; 1569 1570 if (! empty($analyzed_sql_results['statement']->order)) { 1571 $order_and_limit .= ' ORDER BY ' . Query::getClause( 1572 $analyzed_sql_results['statement'], 1573 $analyzed_sql_results['parser']->list, 1574 'ORDER BY' 1575 ); 1576 } 1577 1578 if (! empty($analyzed_sql_results['statement']->limit)) { 1579 $order_and_limit .= ' LIMIT ' . Query::getClause( 1580 $analyzed_sql_results['statement'], 1581 $analyzed_sql_results['parser']->list, 1582 'LIMIT' 1583 ); 1584 } 1585 1586 return 'SELECT ' . implode(', ', $columns) . 1587 ' FROM ' . implode(', ', $table_references) . 1588 ' WHERE ' . $where . $order_and_limit; 1589 } 1590 1591 /** 1592 * Transforms a DELETE query into SELECT statement. 1593 * 1594 * @param array $analyzed_sql_results Analyzed SQL results from parser. 1595 * 1596 * @return string SQL query 1597 */ 1598 public function getSimulatedDeleteQuery(array $analyzed_sql_results): string 1599 { 1600 $table_references = Query::getTables( 1601 $analyzed_sql_results['statement'] 1602 ); 1603 1604 $where = Query::getClause( 1605 $analyzed_sql_results['statement'], 1606 $analyzed_sql_results['parser']->list, 1607 'WHERE' 1608 ); 1609 1610 if (empty($where)) { 1611 $where = '1'; 1612 } 1613 1614 $order_and_limit = ''; 1615 1616 if (! empty($analyzed_sql_results['statement']->order)) { 1617 $order_and_limit .= ' ORDER BY ' . Query::getClause( 1618 $analyzed_sql_results['statement'], 1619 $analyzed_sql_results['parser']->list, 1620 'ORDER BY' 1621 ); 1622 } 1623 1624 if (! empty($analyzed_sql_results['statement']->limit)) { 1625 $order_and_limit .= ' LIMIT ' . Query::getClause( 1626 $analyzed_sql_results['statement'], 1627 $analyzed_sql_results['parser']->list, 1628 'LIMIT' 1629 ); 1630 } 1631 1632 return 'SELECT * FROM ' . implode(', ', $table_references) . 1633 ' WHERE ' . $where . $order_and_limit; 1634 } 1635 1636 /** 1637 * Executes the matched_row_query and returns the resultant row count. 1638 * 1639 * @param string $matched_row_query SQL query 1640 * 1641 * @return int Number of rows returned 1642 */ 1643 public function executeMatchedRowQuery(string $matched_row_query): int 1644 { 1645 global $dbi; 1646 1647 $dbi->selectDb($GLOBALS['db']); 1648 // Execute the query. 1649 $result = $dbi->tryQuery($matched_row_query); 1650 // Count the number of rows in the result set. 1651 $result = $dbi->numRows($result); 1652 1653 return $result; 1654 } 1655 1656 /** 1657 * Handles request for ROLLBACK. 1658 * 1659 * @param string $sql_query SQL query(s) 1660 */ 1661 public function handleRollbackRequest(string $sql_query): void 1662 { 1663 global $dbi; 1664 1665 $sql_delimiter = $_POST['sql_delimiter']; 1666 $queries = explode($sql_delimiter, $sql_query); 1667 $error = false; 1668 $error_msg = __( 1669 'Only INSERT, UPDATE, DELETE and REPLACE ' 1670 . 'SQL queries containing transactional engine tables can be rolled back.' 1671 ); 1672 foreach ($queries as $sql_query) { 1673 if (empty($sql_query)) { 1674 continue; 1675 } 1676 1677 // Check each query for ROLLBACK support. 1678 if ($this->checkIfRollbackPossible($sql_query)) { 1679 continue; 1680 } 1681 1682 $global_error = $dbi->getError(); 1683 if ($global_error) { 1684 $error = $global_error; 1685 } else { 1686 $error = $error_msg; 1687 } 1688 break; 1689 } 1690 1691 if ($error) { 1692 unset($_POST['rollback_query']); 1693 $response = Response::getInstance(); 1694 $message = Message::rawError($error); 1695 $response->addJSON('message', $message); 1696 exit; 1697 } 1698 1699 // If everything fine, START a transaction. 1700 $dbi->query('START TRANSACTION'); 1701 } 1702 1703 /** 1704 * Checks if ROLLBACK is possible for a SQL query or not. 1705 * 1706 * @param string $sql_query SQL query 1707 */ 1708 public function checkIfRollbackPossible(string $sql_query): bool 1709 { 1710 $parser = new Parser($sql_query); 1711 1712 if (empty($parser->statements[0])) { 1713 return true; 1714 } 1715 1716 $statement = $parser->statements[0]; 1717 1718 // Check if query is supported. 1719 if (! (($statement instanceof InsertStatement) 1720 || ($statement instanceof UpdateStatement) 1721 || ($statement instanceof DeleteStatement) 1722 || ($statement instanceof ReplaceStatement)) 1723 ) { 1724 return false; 1725 } 1726 1727 // Get table_references from the query. 1728 $tables = Query::getTables($statement); 1729 1730 // Check if each table is 'InnoDB'. 1731 foreach ($tables as $table) { 1732 if (! $this->isTableTransactional($table)) { 1733 return false; 1734 } 1735 } 1736 1737 return true; 1738 } 1739 1740 /** 1741 * Checks if a table is 'InnoDB' or not. 1742 * 1743 * @param string $table Table details 1744 */ 1745 public function isTableTransactional(string $table): bool 1746 { 1747 global $dbi; 1748 1749 $table = explode('.', $table); 1750 if (count($table) === 2) { 1751 $db = Util::unQuote($table[0]); 1752 $table = Util::unQuote($table[1]); 1753 } else { 1754 $db = $GLOBALS['db']; 1755 $table = Util::unQuote($table[0]); 1756 } 1757 1758 // Query to check if table exists. 1759 $check_table_query = 'SELECT * FROM ' . Util::backquote($db) 1760 . '.' . Util::backquote($table) . ' ' 1761 . 'LIMIT 1'; 1762 1763 $result = $dbi->tryQuery($check_table_query); 1764 1765 if (! $result) { 1766 return false; 1767 } 1768 1769 // List of Transactional Engines. 1770 $transactional_engines = [ 1771 'INNODB', 1772 'FALCON', 1773 'NDB', 1774 'INFINIDB', 1775 'TOKUDB', 1776 'XTRADB', 1777 'SEQUENCE', 1778 'BDB', 1779 ]; 1780 1781 // Query to check if table is 'Transactional'. 1782 $check_query = 'SELECT `ENGINE` FROM `information_schema`.`tables` ' 1783 . 'WHERE `table_name` = "' . $dbi->escapeString($table) . '" ' 1784 . 'AND `table_schema` = "' . $dbi->escapeString($db) . '" ' 1785 . 'AND UPPER(`engine`) IN ("' 1786 . implode('", "', $transactional_engines) 1787 . '")'; 1788 1789 $result = $dbi->tryQuery($check_query); 1790 1791 return $dbi->numRows($result) == 1; 1792 } 1793 1794 /** @return string[] */ 1795 public static function getCompressions(): array 1796 { 1797 global $cfg; 1798 1799 $compressions = []; 1800 1801 if ($cfg['GZipDump'] && function_exists('gzopen')) { 1802 $compressions[] = 'gzip'; 1803 } 1804 if ($cfg['BZipDump'] && function_exists('bzopen')) { 1805 $compressions[] = 'bzip2'; 1806 } 1807 if ($cfg['ZipDump'] && function_exists('zip_open')) { 1808 $compressions[] = 'zip'; 1809 } 1810 1811 return $compressions; 1812 } 1813 1814 /** 1815 * @param array $importList List of plugin instances. 1816 * 1817 * @return false|string 1818 */ 1819 public static function getLocalFiles(array $importList) 1820 { 1821 $fileListing = new FileListing(); 1822 1823 $extensions = ''; 1824 foreach ($importList as $importPlugin) { 1825 if (! empty($extensions)) { 1826 $extensions .= '|'; 1827 } 1828 $extensions .= $importPlugin->getProperties()->getExtension(); 1829 } 1830 1831 $matcher = '@\.(' . $extensions . ')(\.(' . $fileListing->supportedDecompressions() . '))?$@'; 1832 1833 $active = isset($GLOBALS['timeout_passed'], $GLOBALS['local_import_file']) && $GLOBALS['timeout_passed'] 1834 ? $GLOBALS['local_import_file'] 1835 : ''; 1836 1837 return $fileListing->getFileSelectOptions( 1838 Util::userDir((string) ($GLOBALS['cfg']['UploadDir'] ?? '')), 1839 $matcher, 1840 $active 1841 ); 1842 } 1843} 1844