tests/classes/Database/RoutinesTest.php

   1 <?php
   2
   3 declare(strict_types=1);
   4
   5 namespace PhpMyAdmin\Tests\Database;
   6
   7 use PhpMyAdmin\Config;
   8 use PhpMyAdmin\Current;
   9 use PhpMyAdmin\Database\Routines;
  10 use PhpMyAdmin\DatabaseInterface;
  11 use PhpMyAdmin\Dbal\Connection;
  12 use PhpMyAdmin\Tests\AbstractTestCase;
  13 use PhpMyAdmin\Types;
  14 use PhpMyAdmin\UserPrivileges;
  15 use PHPUnit\Framework\Attributes\CoversClass;
  16 use PHPUnit\Framework\Attributes\DataProvider;
  17
  18 #[CoversClass(Routines::class)]
  19 class RoutinesTest extends AbstractTestCase
  20 {
  21     private Routines $routines;
  22
  23     /**
  24      * Set up
  25      */
  26     protected function setUp(): void
  27     {
  28         parent::setUp();
  29
  30         parent::setGlobalConfig();
  31
  32         parent::setLanguage();
  33
  34         DatabaseInterface::$instance = $this->createDatabaseInterface();
  35         $config = Config::getInstance();
  36         $config->selectedServer['DisableIS'] = false;
  37         $config->settings['ActionLinksMode'] = 'icons';
  38         Current::$database = 'db';
  39         Current::$table = 'table';
  40         UserPrivileges::$routines = false;
  41         UserPrivileges::$isReload = false;
  42         $GLOBALS['errors'] = [];
  43
  44         $this->routines = new Routines(DatabaseInterface::getInstance());
  45     }
  46
  47     /**
  48      * Test for getDataFromRequest
  49      *
  50      * @param array<string, mixed> $in  Input
  51      * @param array<string, mixed> $out Expected output
  52      */
  53     #[DataProvider('providerGetDataFromRequest')]
  54     public function testGetDataFromRequest(array $in, array $out): void
  55     {
  56         unset($_POST);
  57         unset($_REQUEST);
  58         foreach ($in as $key => $value) {
  59             if ($value === '') {
  60                 continue;
  61             }
  62
  63             $_POST[$key] = $value;
  64             $_REQUEST[$key] = $value;
  65         }
  66
  67         $this->assertEquals($out, $this->routines->getDataFromRequest());
  68     }
  69
  70     /**
  71      * Data provider for testGetDataFromRequest
  72      *
  73      * @return array<array{array<string, mixed>, array<string, mixed>}>
  74      */
  75     public static function providerGetDataFromRequest(): array
  76     {
  77         return [
  78             [
  79                 [
  80                     'item_name' => '',
  81                     'item_original_name' => '',
  82                     'item_returnlength' => '',
  83                     'item_returnopts_num' => '',
  84                     'item_returnopts_text' => '',
  85                     'item_definition' => '',
  86                     'item_comment' => '',
  87                     'item_definer' => '',
  88                     'item_type' => '',
  89                     'item_type_toggle' => '',
  90                     'item_original_type' => '',
  91                     'item_param_dir' => '',
  92                     'item_param_name' => '',
  93                     'item_param_type' => '',
  94                     'item_param_length' => '',
  95                     'item_param_opts_num' => '',
  96                     'item_param_opts_text' => '',
  97                     'item_returntype' => '',
  98                     'item_isdeterministic' => '',
  99                     'item_securitytype' => '',
 100                     'item_sqldataaccess' => '',
 101                 ],
 102                 [
 103                     'item_name' => '',
 104                     'item_original_name' => '',
 105                     'item_returnlength' => '',
 106                     'item_returnopts_num' => '',
 107                     'item_returnopts_text' => '',
 108                     'item_definition' => '',
 109                     'item_comment' => '',
 110                     'item_definer' => '',
 111                     'item_type' => 'PROCEDURE',
 112                     'item_type_toggle' => 'FUNCTION',
 113                     'item_original_type' => 'PROCEDURE',
 114                     'item_num_params' => 0,
 115                     'item_param_dir' => [],
 116                     'item_param_name' => [],
 117                     'item_param_type' => [],
 118                     'item_param_length' => [],
 119                     'item_param_opts_num' => [],
 120                     'item_param_opts_text' => [],
 121                     'item_returntype' => '',
 122                     'item_isdeterministic' => '',
 123                     'item_securitytype_definer' => '',
 124                     'item_securitytype_invoker' => '',
 125                     'item_sqldataaccess' => '',
 126                 ],
 127             ],
 128             [
 129                 [
 130                     'item_name' => 'proc2',
 131                     'item_original_name' => 'proc',
 132                     'item_returnlength' => '',
 133                     'item_returnopts_num' => '',
 134                     'item_returnopts_text' => '',
 135                     'item_definition' => 'SELECT NULL',
 136                     'item_comment' => 'some text',
 137                     'item_definer' => 'root@localhost',
 138                     'item_type' => 'PROCEDURE',
 139                     'item_type_toggle' => 'FUNCTION',
 140                     'item_original_type' => 'PROCEDURE',
 141                     'item_param_dir' => [0 => 'IN', 1 => 'FAIL'],
 142                     'item_param_name' => [0 => 'bar', 1 => 'baz'],
 143                     'item_param_type' => [0 => 'INT', 1 => 'FAIL'],
 144                     'item_param_length' => [0 => '20', 1 => ''],
 145                     'item_param_opts_num' => [0 => 'UNSIGNED', 1 => ''],
 146                     'item_param_opts_text' => [0 => '', 1 => 'latin1'],
 147                     'item_returntype' => '',
 148                     'item_isdeterministic' => 'ON',
 149                     'item_securitytype' => 'INVOKER',
 150                     'item_sqldataaccess' => 'NO SQL',
 151                 ],
 152                 [
 153                     'item_name' => 'proc2',
 154                     'item_original_name' => 'proc',
 155                     'item_returnlength' => '',
 156                     'item_returnopts_num' => '',
 157                     'item_returnopts_text' => '',
 158                     'item_definition' => 'SELECT NULL',
 159                     'item_comment' => 'some text',
 160                     'item_definer' => 'root@localhost',
 161                     'item_type' => 'PROCEDURE',
 162                     'item_type_toggle' => 'FUNCTION',
 163                     'item_original_type' => 'PROCEDURE',
 164                     'item_num_params' => 2,
 165                     'item_param_dir' => [0 => 'IN', 1 => ''],
 166                     'item_param_name' => [0 => 'bar', 1 => 'baz'],
 167                     'item_param_type' => [0 => 'INT', 1 => ''],
 168                     'item_param_length' => [0 => '20', 1 => ''],
 169                     'item_param_opts_num' => [0 => 'UNSIGNED', 1 => ''],
 170                     'item_param_opts_text' => [0 => '', 1 => 'latin1'],
 171                     'item_returntype' => '',
 172                     'item_isdeterministic' => ' checked=\'checked\'',
 173                     'item_securitytype_definer' => '',
 174                     'item_securitytype_invoker' => ' selected=\'selected\'',
 175                     'item_sqldataaccess' => 'NO SQL',
 176                 ],
 177             ],
 178             [
 179                 [
 180                     'item_name' => 'func2',
 181                     'item_original_name' => 'func',
 182                     'item_returnlength' => '20',
 183                     'item_returnopts_num' => '',
 184                     'item_returnopts_text' => 'CHARSET utf8',
 185                     'item_definition' => 'SELECT NULL',
 186                     'item_comment' => 'some text',
 187                     'item_definer' => 'root@localhost',
 188                     'item_type' => 'FUNCTION',
 189                     'item_type_toggle' => 'PROCEDURE',
 190                     'item_original_type' => 'FUNCTION',
 191                     'item_param_dir' => [0 => '', 1 => ''],
 192                     'item_param_name' => [0 => 'bar', 1 => 'baz'],
 193                     'item_param_type' => [0 => '<s>XSS</s>', 1 => 'TEXT'],
 194                     'item_param_length' => [0 => '10,10', 1 => ''],
 195                     'item_param_opts_num' => [0 => 'UNSIGNED', 1 => ''],
 196                     'item_param_opts_text' => [0 => '', 1 => 'utf8'],
 197                     'item_returntype' => 'VARCHAR',
 198                     'item_isdeterministic' => '',
 199                     'item_securitytype' => 'DEFINER',
 200                     'item_sqldataaccess' => '',
 201                 ],
 202                 [
 203                     'item_name' => 'func2',
 204                     'item_original_name' => 'func',
 205                     'item_returnlength' => '20',
 206                     'item_returnopts_num' => '',
 207                     'item_returnopts_text' => 'CHARSET utf8',
 208                     'item_definition' => 'SELECT NULL',
 209                     'item_comment' => 'some text',
 210                     'item_definer' => 'root@localhost',
 211                     'item_type' => 'FUNCTION',
 212                     'item_type_toggle' => 'PROCEDURE',
 213                     'item_original_type' => 'FUNCTION',
 214                     'item_num_params' => '2',
 215                     'item_param_dir' => [],
 216                     'item_param_name' => [0 => 'bar', 1 => 'baz'],
 217                     'item_param_type' => [0 => '', 1 => 'TEXT'],
 218                     'item_param_length' => [0 => '10,10', 1 => ''],
 219                     'item_param_opts_num' => [0 => 'UNSIGNED', 1 => ''],
 220                     'item_param_opts_text' => [0 => '', 1 => 'utf8'],
 221                     'item_returntype' => 'VARCHAR',
 222                     'item_isdeterministic' => '',
 223                     'item_securitytype_definer' => ' selected=\'selected\'',
 224                     'item_securitytype_invoker' => '',
 225                     'item_sqldataaccess' => '',
 226                 ],
 227             ],
 228         ];
 229     }
 230
 231     /**
 232      * Test for getQueryFromRequest
 233      *
 234      * @param array<string, string|array<string>> $request Request
 235      * @param string                              $query   Query
 236      * @param int                                 $numErr  Error number
 237      */
 238     #[DataProvider('providerGetQueryFromRequest')]
 239     public function testGetQueryFromRequest(array $request, string $query, int $numErr): void
 240     {
 241         Config::getInstance()->settings['ShowFunctionFields'] = false;
 242
 243         $GLOBALS['errors'] = [];
 244
 245         $oldDbi = DatabaseInterface::getInstance();
 246         $dbi = $this->getMockBuilder(DatabaseInterface::class)
 247             ->disableOriginalConstructor()
 248             ->getMock();
 249         $dbi->types = new Types($dbi);
 250         $dbi->expects($this->any())
 251             ->method('quoteString')
 252             ->willReturnMap([
 253                 ['foo', Connection::TYPE_USER, "'foo'"],
 254                 ["foo's bar", Connection::TYPE_USER, "'foo\'s bar'"],
 255             ]);
 256         DatabaseInterface::$instance = $dbi;
 257
 258         $routines = new Routines($dbi);
 259
 260         unset($_POST);
 261         $_POST = $request;
 262         $this->assertEquals($query, $routines->getQueryFromRequest());
 263         $this->assertCount($numErr, $GLOBALS['errors']);
 264
 265         // reset
 266         DatabaseInterface::$instance = $oldDbi;
 267     }
 268
 269     /**
 270      * Data provider for testGetQueryFromRequest
 271      *
 272      * @return array<array{array<string, string|array<string>>, string, int}>
 273      */
 274     public static function providerGetQueryFromRequest(): array
 275     {
 276         return [
 277             // Testing success
 278             [
 279                 [
 280                     'item_name' => 'p r o c',
 281                     'item_returnlength' => '',
 282                     'item_returnopts_num' => '',
 283                     'item_returnopts_text' => '',
 284                     'item_definition' => 'SELECT 0;',
 285                     'item_comment' => 'foo',
 286                     'item_definer' => 'me@home',
 287                     'item_type' => 'PROCEDURE',
 288                     'item_num_params' => '0',
 289                     'item_param_dir' => [],
 290                     'item_param_name' => '',
 291                     'item_param_type' => '',
 292                     'item_param_length' => '',
 293                     'item_param_opts_num' => '',
 294                     'item_param_opts_text' => '',
 295                     'item_returntype' => '',
 296                     'item_isdeterministic' => '',
 297                     'item_securitytype' => 'INVOKER',
 298                     'item_sqldataaccess' => 'NO SQL',
 299                 ],
 300                 'CREATE DEFINER=`me`@`home` PROCEDURE `p r o c`() COMMENT \'foo\' '
 301                 . 'DETERMINISTIC NO SQL SQL SECURITY INVOKER SELECT 0;',
 302                 0,
 303             ],
 304             [
 305                 [
 306                     'item_name' => 'pr``oc',
 307                     'item_returnlength' => '',
 308                     'item_returnopts_num' => '',
 309                     'item_returnopts_text' => '',
 310                     'item_definition' => 'SELECT \'foobar\';',
 311                     'item_comment' => '',
 312                     'item_definer' => 'someuser@somehost',
 313                     'item_type' => 'PROCEDURE',
 314                     'item_num_params' => '2',
 315                     'item_param_dir' => ['IN', 'INOUT'],
 316                     'item_param_name' => ['pa`ram', 'par 2'],
 317                     'item_param_type' => ['INT', 'ENUM'],
 318                     'item_param_length' => ['10', '\'a\', \'b\''],
 319                     'item_param_opts_num' => ['ZEROFILL', ''],
 320                     'item_param_opts_text' => ['utf8', 'latin1'],
 321                     'item_returntype' => '',
 322                     'item_securitytype' => 'DEFINER',
 323                     'item_sqldataaccess' => 'foobar',
 324                 ],
 325                 'CREATE DEFINER=`someuser`@`somehost` PROCEDURE `pr````oc`'
 326                 . '(IN `pa``ram` INT(10) ZEROFILL, INOUT `par 2` ENUM(\'a\', \'b\')'
 327                 . ' CHARSET latin1) NOT DETERMINISTIC SQL SECURITY DEFINER SELECT '
 328                 . '\'foobar\';',
 329                 0,
 330             ],
 331             [
 332                 [
 333                     'item_name' => 'func\\',
 334                     'item_returnlength' => '5,5',
 335                     'item_returnopts_num' => 'UNSIGNED ZEROFILL',
 336                     'item_returnopts_text' => '',
 337                     'item_definition' => 'SELECT \'foobar\';',
 338                     'item_comment' => 'foo\'s bar',
 339                     'item_definer' => '',
 340                     'item_type' => 'FUNCTION',
 341                     'item_num_params' => '1',
 342                     'item_param_dir' => [],
 343                     'item_param_name' => ['pa`ram'],
 344                     'item_param_type' => ['VARCHAR'],
 345                     'item_param_length' => ['45'],
 346                     'item_param_opts_num' => [''],
 347                     'item_param_opts_text' => ['latin1'],
 348                     'item_returntype' => 'DECIMAL',
 349                     'item_isdeterministic' => 'ON',
 350                     'item_securitytype' => 'DEFINER',
 351                     'item_sqldataaccess' => 'READ SQL DATA',
 352                 ],
 353                 'CREATE FUNCTION `func\\`(`pa``ram` VARCHAR(45) CHARSET latin1) '
 354                 . 'RETURNS DECIMAL(5,5) UNSIGNED ZEROFILL COMMENT \'foo\\\'s bar\' '
 355                 . 'DETERMINISTIC SQL SECURITY DEFINER SELECT \'foobar\';',
 356                 0,
 357             ],
 358             [
 359                 [
 360                     'item_name' => 'func',
 361                     'item_returnlength' => '20',
 362                     'item_returnopts_num' => '',
 363                     'item_returnopts_text' => 'utf8',
 364                     'item_definition' => 'SELECT 0;',
 365                     'item_comment' => '',
 366                     'item_definer' => '',
 367                     'item_type' => 'FUNCTION',
 368                     'item_num_params' => '1',
 369                     'item_returntype' => 'VARCHAR',
 370                     'item_securitytype' => 'DEFINER',
 371                     'item_sqldataaccess' => 'READ SQL DATA',
 372                 ],
 373                 'CREATE FUNCTION `func`() RETURNS VARCHAR(20) CHARSET utf8 NOT '
 374                 . 'DETERMINISTIC SQL SECURITY DEFINER SELECT 0;',
 375                 0,
 376             ],
 377             // Testing failures
 378             [
 379                 [],
 380                 'CREATE () NOT DETERMINISTIC ', // invalid query
 381                 3,
 382             ],
 383             [
 384                 [
 385                     'item_name' => 'proc',
 386                     'item_returnlength' => '',
 387                     'item_returnopts_num' => '',
 388                     'item_returnopts_text' => '',
 389                     'item_definition' => 'SELECT 0;',
 390                     'item_comment' => 'foo',
 391                     'item_definer' => 'mehome', // invalid definer format
 392                     'item_type' => 'PROCEDURE',
 393                     'item_num_params' => '0',
 394                     'item_param_dir' => '',
 395                     'item_param_name' => '',
 396                     'item_param_type' => '',
 397                     'item_param_length' => '',
 398                     'item_param_opts_num' => '',
 399                     'item_param_opts_text' => '',
 400                     'item_returntype' => '',
 401                     'item_isdeterministic' => '',
 402                     'item_securitytype' => 'INVOKER',
 403                     'item_sqldataaccess' => 'NO SQL',
 404                 ],
 405                 // valid query
 406                 'CREATE PROCEDURE `proc`() COMMENT \'foo\' DETERMINISTIC NO SQL SQL SECURITY INVOKER SELECT 0;',
 407                 1,
 408             ],
 409             [
 410                 [
 411                     'item_name' => 'proc',
 412                     'item_returnlength' => '',
 413                     'item_returnopts_num' => '',
 414                     'item_returnopts_text' => '',
 415                     'item_definition' => 'SELECT 0;',
 416                     'item_comment' => '',
 417                     'item_definer' => '',
 418                     'item_type' => 'PROCEDURE',
 419                     'item_num_params' => '2',
 420                     'item_param_dir' => ['FAIL', 'INOUT'], // invalid direction
 421                     'item_param_name' => ['pa`ram', 'goo'],
 422                     'item_param_type' => ['INT', 'ENUM'],
 423                     'item_param_length' => ['10', ''], // missing ENUM values
 424                     'item_param_opts_num' => ['ZEROFILL', ''],
 425                     'item_param_opts_text' => ['utf8', 'latin1'],
 426                     'item_returntype' => '',
 427                     'item_securitytype' => 'DEFINER',
 428                     'item_sqldataaccess' => 'foobar', // invalid, will just be ignored without throwing errors
 429                 ],
 430                 'CREATE PROCEDURE `proc`((10) ZEROFILL, '
 431                 . 'INOUT `goo` ENUM CHARSET latin1) NOT DETERMINISTIC '
 432                 . 'SQL SECURITY DEFINER SELECT 0;', // invalid query
 433                 2,
 434             ],
 435             [
 436                 [
 437                     'item_name' => 'func',
 438                     'item_returnlength' => '', // missing length for VARCHAR
 439                     'item_returnopts_num' => '',
 440                     'item_returnopts_text' => 'utf8',
 441                     'item_definition' => 'SELECT 0;',
 442                     'item_comment' => '',
 443                     'item_definer' => '',
 444                     'item_type' => 'FUNCTION',
 445                     'item_num_params' => '2',
 446                     'item_param_dir' => ['IN'],
 447                     'item_param_name' => [''], // missing name
 448                     'item_param_type' => ['INT'],
 449                     'item_param_length' => ['10'],
 450                     'item_param_opts_num' => ['ZEROFILL'],
 451                     'item_param_opts_text' => ['latin1'],
 452                     'item_returntype' => 'VARCHAR',
 453                     'item_securitytype' => 'DEFINER',
 454                     'item_sqldataaccess' => '',
 455                 ],
 456                 'CREATE FUNCTION `func`() RETURNS VARCHAR CHARSET utf8 NOT '
 457                 . 'DETERMINISTIC SQL SECURITY DEFINER SELECT 0;', // invalid query
 458                 2,
 459             ],
 460             [
 461                 [
 462                     'item_name' => 'func',
 463                     'item_returnlength' => '',
 464                     'item_returnopts_num' => '',
 465                     'item_returnopts_text' => '',
 466                     'item_definition' => 'SELECT 0;',
 467                     'item_comment' => '',
 468                     'item_definer' => '',
 469                     'item_type' => 'FUNCTION',
 470                     'item_num_params' => '0',
 471                     'item_returntype' => 'FAIL', // invalid return type
 472                     'item_securitytype' => 'DEFINER',
 473                     'item_sqldataaccess' => '',
 474                 ],
 475                 'CREATE FUNCTION `func`()  NOT DETERMINISTIC SQL SECURITY DEFINER SELECT 0;', // invalid query
 476                 1,
 477             ],
 478         ];
 479     }
 480
 481     public function testGetFunctionNames(): void
 482     {
 483         $dbiDummy = $this->createDbiDummy();
 484         $dbiDummy->addResult(
 485             'SHOW FUNCTION STATUS;',
 486             [
 487                 ['db_test', 'test_func', 'FUNCTION'],
 488                 ['test_db', 'test_func1', 'FUNCTION'],
 489                 ['test_db', '', 'FUNCTION'],
 490                 ['test_db', 'test_func2', 'FUNCTION'],
 491                 ['test_db', 'test_func', 'PROCEDURE'],
 492             ],
 493             ['Db', 'Name', 'Type'],
 494         );
 495
 496         $names = Routines::getFunctionNames($this->createDatabaseInterface($dbiDummy), 'test_db');
 497         $this->assertSame(['test_func1', 'test_func2'], $names);
 498
 499         $dbiDummy->assertAllQueriesConsumed();
 500     }
 501
 502     public function testGetFunctionNamesWithEmptyReturn(): void
 503     {
 504         $dbiDummy = $this->createDbiDummy();
 505         $dbiDummy->addResult(
 506             'SHOW FUNCTION STATUS;',
 507             [['db_test', 'test_func', 'FUNCTION'], ['test_db', '', 'FUNCTION'], ['test_db', 'test_func', 'PROCEDURE']],
 508             ['Db', 'Name', 'Type'],
 509         );
 510
 511         $names = Routines::getFunctionNames($this->createDatabaseInterface($dbiDummy), 'test_db');
 512         $this->assertSame([], $names);
 513
 514         $dbiDummy->assertAllQueriesConsumed();
 515     }
 516
 517     public function testGetProcedureNames(): void
 518     {
 519         $dbiDummy = $this->createDbiDummy();
 520         $dbiDummy->addResult(
 521             'SHOW PROCEDURE STATUS;',
 522             [
 523                 ['db_test', 'test_proc', 'PROCEDURE'],
 524                 ['test_db', 'test_proc1', 'PROCEDURE'],
 525                 ['test_db', '', 'PROCEDURE'],
 526                 ['test_db', 'test_proc2', 'PROCEDURE'],
 527                 ['test_db', 'test_proc', 'FUNCTION'],
 528             ],
 529             ['Db', 'Name', 'Type'],
 530         );
 531
 532         $names = Routines::getProcedureNames($this->createDatabaseInterface($dbiDummy), 'test_db');
 533         $this->assertSame(['test_proc1', 'test_proc2'], $names);
 534
 535         $dbiDummy->assertAllQueriesConsumed();
 536     }
 537
 538     public function testGetProcedureNamesWithEmptyReturn(): void
 539     {
 540         $dbiDummy = $this->createDbiDummy();
 541         $dbiDummy->addResult(
 542             'SHOW PROCEDURE STATUS;',
 543             [
 544                 ['db_test', 'test_proc', 'PROCEDURE'],
 545                 ['test_db', '', 'PROCEDURE'],
 546                 ['test_db', 'test_proc', 'FUNCTION'],
 547             ],
 548             ['Db', 'Name', 'Type'],
 549         );
 550
 551         $names = Routines::getProcedureNames($this->createDatabaseInterface($dbiDummy), 'test_db');
 552         $this->assertSame([], $names);
 553
 554         $dbiDummy->assertAllQueriesConsumed();
 555     }
 556 }