test/classes/ImportTest.php

   1 <?php
   2
   3 declare(strict_types=1);
   4
   5 namespace PhpMyAdmin\Tests;
   6
   7 use PhpMyAdmin\Html\Generator;
   8 use PhpMyAdmin\Import;
   9 use PhpMyAdmin\SqlParser\Parser;
  10 use PhpMyAdmin\Url;
  11 use const PHP_INT_MAX;
  12 use function time;
  13
  14 /**
  15  * Tests for import functions
  16  */
  17 class ImportTest extends AbstractTestCase
  18 {
  19     /** @var Import $import */
  20     private $import;
  21
  22     /**
  23      * Prepares environment for the test.
  24      */
  25     protected function setUp(): void
  26     {
  27         parent::setUp();
  28         $GLOBALS['server'] = 0;
  29         $GLOBALS['cfg']['ServerDefault'] = '';
  30         $this->import = new Import();
  31     }
  32
  33     /**
  34      * Test for checkTimeout
  35      */
  36     public function testCheckTimeout(): void
  37     {
  38         global $timestamp, $maximum_time, $timeout_passed;
  39
  40         //Reinit values.
  41         $timestamp = time();
  42         $maximum_time = 0;
  43         $timeout_passed = false;
  44
  45         $this->assertFalse($this->import->checkTimeout());
  46
  47         //Reinit values.
  48         $timestamp = time();
  49         $maximum_time = 0;
  50         $timeout_passed = true;
  51
  52         $this->assertFalse($this->import->checkTimeout());
  53
  54         //Reinit values.
  55         $timestamp = time();
  56         $maximum_time = 30;
  57         $timeout_passed = true;
  58
  59         $this->assertTrue($this->import->checkTimeout());
  60
  61         //Reinit values.
  62         $timestamp = time() - 15;
  63         $maximum_time = 30;
  64         $timeout_passed = false;
  65
  66         $this->assertFalse($this->import->checkTimeout());
  67
  68         //Reinit values.
  69         $timestamp = time() - 60;
  70         $maximum_time = 30;
  71         $timeout_passed = false;
  72
  73         $this->assertTrue($this->import->checkTimeout());
  74     }
  75
  76     /**
  77      * Test for lookForUse
  78      */
  79     public function testLookForUse(): void
  80     {
  81         $this->assertEquals(
  82             [
  83                 null,
  84                 null,
  85             ],
  86             $this->import->lookForUse(null, null, null)
  87         );
  88
  89         $this->assertEquals(
  90             [
  91                 'myDb',
  92                 null,
  93             ],
  94             $this->import->lookForUse(null, 'myDb', null)
  95         );
  96
  97         $this->assertEquals(
  98             [
  99                 'myDb',
 100                 true,
 101             ],
 102             $this->import->lookForUse(null, 'myDb', true)
 103         );
 104
 105         $this->assertEquals(
 106             [
 107                 'myDb',
 108                 true,
 109             ],
 110             $this->import->lookForUse('select 1 from myTable', 'myDb', true)
 111         );
 112
 113         $this->assertEquals(
 114             [
 115                 'anotherDb',
 116                 true,
 117             ],
 118             $this->import->lookForUse('use anotherDb', 'myDb', false)
 119         );
 120
 121         $this->assertEquals(
 122             [
 123                 'anotherDb',
 124                 true,
 125             ],
 126             $this->import->lookForUse('use anotherDb', 'myDb', true)
 127         );
 128
 129         $this->assertEquals(
 130             [
 131                 'anotherDb',
 132                 true,
 133             ],
 134             $this->import->lookForUse('use `anotherDb`;', 'myDb', true)
 135         );
 136     }
 137
 138     /**
 139      * Test for getColumnAlphaName
 140      *
 141      * @param string $expected Expected result of the function
 142      * @param int    $num      The column number
 143      *
 144      * @dataProvider provGetColumnAlphaName
 145      */
 146     public function testGetColumnAlphaName(string $expected, int $num): void
 147     {
 148         $this->assertEquals($expected, $this->import->getColumnAlphaName($num));
 149     }
 150
 151     /**
 152      * Data provider for testGetColumnAlphaName
 153      *
 154      * @return array
 155      */
 156     public function provGetColumnAlphaName(): array
 157     {
 158         return [
 159             [
 160                 'A',
 161                 1,
 162             ],
 163             [
 164                 'Z',
 165                 0,
 166             ],
 167             [
 168                 'AA',
 169                 27,
 170             ],
 171             [
 172                 'AZ',
 173                 52,
 174             ],
 175             [
 176                 'BA',
 177                 53,
 178             ],
 179             [
 180                 'BB',
 181                 54,
 182             ],
 183         ];
 184     }
 185
 186     /**
 187      * Test for getColumnNumberFromName
 188      *
 189      * @param int         $expected Expected result of the function
 190      * @param string|null $name     column name(i.e. "A", or "BC", etc.)
 191      *
 192      * @dataProvider provGetColumnNumberFromName
 193      */
 194     public function testGetColumnNumberFromName(int $expected, ?string $name): void
 195     {
 196         $this->assertEquals($expected, $this->import->getColumnNumberFromName($name));
 197     }
 198
 199     /**
 200      * Data provider for testGetColumnNumberFromName
 201      *
 202      * @return array
 203      */
 204     public function provGetColumnNumberFromName(): array
 205     {
 206         return [
 207             [
 208                 1,
 209                 'A',
 210             ],
 211             [
 212                 26,
 213                 'Z',
 214             ],
 215             [
 216                 27,
 217                 'AA',
 218             ],
 219             [
 220                 52,
 221                 'AZ',
 222             ],
 223             [
 224                 53,
 225                 'BA',
 226             ],
 227             [
 228                 54,
 229                 'BB',
 230             ],
 231         ];
 232     }
 233
 234     /**
 235      * Test for getDecimalPrecision
 236      *
 237      * @param int         $expected Expected result of the function
 238      * @param string|null $size     Size of field
 239      *
 240      * @dataProvider provGetDecimalPrecision
 241      */
 242     public function testGetDecimalPrecision(int $expected, ?string $size): void
 243     {
 244         $this->assertEquals($expected, $this->import->getDecimalPrecision($size));
 245     }
 246
 247     /**
 248      * Data provider for testGetDecimalPrecision
 249      *
 250      * @return array
 251      */
 252     public function provGetDecimalPrecision(): array
 253     {
 254         return [
 255             [
 256                 2,
 257                 '2,1',
 258             ],
 259             [
 260                 6,
 261                 '6,2',
 262             ],
 263             [
 264                 6,
 265                 '6,0',
 266             ],
 267             [
 268                 16,
 269                 '16,2',
 270             ],
 271         ];
 272     }
 273
 274     /**
 275      * Test for getDecimalScale
 276      *
 277      * @param int         $expected Expected result of the function
 278      * @param string|null $size     Size of field
 279      *
 280      * @dataProvider provGetDecimalScale
 281      */
 282     public function testGetDecimalScale(int $expected, ?string $size): void
 283     {
 284         $this->assertEquals($expected, $this->import->getDecimalScale($size));
 285     }
 286
 287     /**
 288      * Data provider for testGetDecimalScale
 289      *
 290      * @return array
 291      */
 292     public function provGetDecimalScale(): array
 293     {
 294         return [
 295             [
 296                 1,
 297                 '2,1',
 298             ],
 299             [
 300                 2,
 301                 '6,2',
 302             ],
 303             [
 304                 0,
 305                 '6,0',
 306             ],
 307             [
 308                 20,
 309                 '30,20',
 310             ],
 311         ];
 312     }
 313
 314     /**
 315      * Test for getDecimalSize
 316      *
 317      * @param array       $expected Expected result of the function
 318      * @param string|null $cell     Cell content
 319      *
 320      * @dataProvider provGetDecimalSize
 321      */
 322     public function testGetDecimalSize(array $expected, ?string $cell): void
 323     {
 324         $this->assertEquals($expected, $this->import->getDecimalSize($cell));
 325     }
 326
 327     /**
 328      * Data provider for testGetDecimalSize
 329      *
 330      * @return array
 331      */
 332     public function provGetDecimalSize(): array
 333     {
 334         return [
 335             [
 336                 [
 337                     2,
 338                     1,
 339                     '2,1',
 340                 ],
 341                 '2.1',
 342             ],
 343             [
 344                 [
 345                     2,
 346                     1,
 347                     '2,1',
 348                 ],
 349                 '6.2',
 350             ],
 351             [
 352                 [
 353                     3,
 354                     1,
 355                     '3,1',
 356                 ],
 357                 '10.0',
 358             ],
 359             [
 360                 [
 361                     4,
 362                     2,
 363                     '4,2',
 364                 ],
 365                 '30.20',
 366             ],
 367         ];
 368     }
 369
 370     /**
 371      * Test for detectType
 372      *
 373      * @param int         $expected Expected result of the function
 374      * @param int|null    $type     Last cumulative column type (VARCHAR or INT or
 375      *                              BIGINT or DECIMAL or NONE)
 376      * @param string|null $cell     String representation of the cell for which a
 377      *                              best-fit type is to be determined
 378      *
 379      * @dataProvider provDetectType
 380      */
 381     public function testDetectType(int $expected, ?int $type, ?string $cell): void
 382     {
 383         $this->assertEquals($expected, $this->import->detectType($type, $cell));
 384     }
 385
 386     /**
 387      * Data provider for testDetectType
 388      *
 389      * @return array
 390      */
 391     public function provDetectType(): array
 392     {
 393         $data = [
 394             [
 395                 Import::NONE,
 396                 null,
 397                 'NULL',
 398             ],
 399             [
 400                 Import::NONE,
 401                 Import::NONE,
 402                 'NULL',
 403             ],
 404             [
 405                 Import::INT,
 406                 Import::INT,
 407                 'NULL',
 408             ],
 409             [
 410                 Import::VARCHAR,
 411                 Import::VARCHAR,
 412                 'NULL',
 413             ],
 414             [
 415                 Import::VARCHAR,
 416                 null,
 417                 null,
 418             ],
 419             [
 420                 Import::VARCHAR,
 421                 Import::INT,
 422                 null,
 423             ],
 424             [
 425                 Import::INT,
 426                 Import::INT,
 427                 '10',
 428             ],
 429             [
 430                 Import::DECIMAL,
 431                 Import::DECIMAL,
 432                 '10.2',
 433             ],
 434             [
 435                 Import::DECIMAL,
 436                 Import::INT,
 437                 '10.2',
 438             ],
 439             [
 440                 Import::VARCHAR,
 441                 Import::VARCHAR,
 442                 'test',
 443             ],
 444             [
 445                 Import::VARCHAR,
 446                 Import::INT,
 447                 'test',
 448             ],
 449         ];
 450
 451         if (PHP_INT_MAX > 2147483647) {
 452             $data[] = [
 453                 Import::BIGINT,
 454                 Import::BIGINT,
 455                 '2147483648',
 456             ];
 457             $data[] = [
 458                 Import::BIGINT,
 459                 Import::INT,
 460                 '2147483648',
 461             ];
 462         } else {
 463             // To be fixed ?
 464             // Can not detect a BIGINT since the value is over PHP_INT_MAX
 465             $data[] = [
 466                 Import::VARCHAR,
 467                 Import::BIGINT,
 468                 '2147483648',
 469             ];
 470             $data[] = [
 471                 Import::VARCHAR,
 472                 Import::INT,
 473                 '2147483648',
 474             ];
 475         }
 476
 477         return $data;
 478     }
 479
 480     /**
 481      * Test for getMatchedRows.
 482      */
 483     public function testPMAGetMatchedRows(): void
 484     {
 485         $GLOBALS['db'] = 'PMA';
 486
 487         $updateQuery = 'UPDATE `table_1` '
 488             . 'SET `id` = 20 '
 489             . 'WHERE `id` > 10';
 490         $simulatedUpdateQuery = 'SELECT `id` FROM `table_1` WHERE `id` > 10 AND (`id` <> 20)';
 491
 492         $deleteQuery = 'DELETE FROM `table_1` '
 493             . 'WHERE `id` > 10';
 494         $simulatedDeleteQuery = 'SELECT * FROM `table_1` WHERE `id` > 10';
 495
 496         $this->simulatedQueryTest($updateQuery, $simulatedUpdateQuery);
 497         $this->simulatedQueryTest($deleteQuery, $simulatedDeleteQuery);
 498     }
 499
 500     /**
 501      * Tests simulated UPDATE/DELETE query.
 502      *
 503      * @param string $sqlQuery       SQL query
 504      * @param string $simulatedQuery Simulated query
 505      */
 506     public function simulatedQueryTest(string $sqlQuery, string $simulatedQuery): void
 507     {
 508         $parser = new Parser($sqlQuery);
 509         $analyzed_sql_results = [
 510             'query' => $sqlQuery,
 511             'parser' => $parser,
 512             'statement' => $parser->statements[0],
 513         ];
 514
 515         $simulated_data = $this->import->getMatchedRows($analyzed_sql_results);
 516
 517         // URL to matched rows.
 518         $_url_params = [
 519             'db'        => 'PMA',
 520             'sql_query' => $simulatedQuery,
 521         ];
 522         $matched_rows_url = Url::getFromRoute('/sql', $_url_params);
 523
 524         $this->assertEquals(
 525             [
 526                 'sql_query' => Generator::formatSql(
 527                     $analyzed_sql_results['query']
 528                 ),
 529                 'matched_rows' => 2,
 530                 'matched_rows_url' => $matched_rows_url,
 531             ],
 532             $simulated_data
 533         );
 534     }
 535
 536     /**
 537      * Test for checkIfRollbackPossible
 538      */
 539     public function testPMACheckIfRollbackPossible(): void
 540     {
 541         $GLOBALS['db'] = 'PMA';
 542
 543         $sqlQuery = 'UPDATE `table_1` AS t1, `table_2` t2 '
 544             . 'SET `table_1`.`id` = `table_2`.`id` '
 545             . 'WHERE 1';
 546
 547         $this->assertTrue($this->import->checkIfRollbackPossible($sqlQuery));
 548     }
 549
 550     /**
 551      * Data provider for testSkipByteOrderMarksFromContents
 552      *
 553      * @return array[]
 554      */
 555     public function providerContentWithByteOrderMarks(): array
 556     {
 557         return [
 558             [
 559                 "\xEF\xBB\xBF blabla上海",
 560                 ' blabla上海',
 561             ],
 562             [
 563                 "\xEF\xBB\xBF blabla",
 564                 ' blabla',
 565             ],
 566             [
 567                 "\xEF\xBB\xBF blabla\xEF\xBB\xBF",
 568                 " blabla\xEF\xBB\xBF",
 569             ],
 570             [
 571                 "\xFE\xFF blabla",
 572                 ' blabla',
 573             ],
 574             [
 575                 "\xFE\xFF blabla\xFE\xFF",
 576                 " blabla\xFE\xFF",
 577             ],
 578             [
 579                 "\xFF\xFE blabla",
 580                 ' blabla',
 581             ],
 582             [
 583                 "\xFF\xFE blabla\xFF\xFE",
 584                 " blabla\xFF\xFE",
 585             ],
 586             [
 587                 "\xEF\xBB\xBF\x44\x52\x4F\x50\x20\x54\x41\x42\x4C\x45\x20\x49\x46\x20\x45\x58\x49\x53\x54\x53",
 588                 'DROP TABLE IF EXISTS',
 589             ],
 590         ];
 591     }
 592
 593     /**
 594      * Test for skipByteOrderMarksFromContents
 595      *
 596      * @param string $input         The contents to strip BOM
 597      * @param string $cleanContents The contents cleaned
 598      *
 599      * @dataProvider providerContentWithByteOrderMarks
 600      */
 601     public function testSkipByteOrderMarksFromContents(string $input, string $cleanContents): void
 602     {
 603         $this->assertEquals($cleanContents, $this->import->skipByteOrderMarksFromContents($input));
 604     }
 605 }