src/ConfigStorage/Relation.php

   1 <?php
   2
   3 declare(strict_types=1);
   4
   5 namespace PhpMyAdmin\ConfigStorage;
   6
   7 use PhpMyAdmin\Config;
   8 use PhpMyAdmin\ConfigStorage\Features\PdfFeature;
   9 use PhpMyAdmin\Current;
  10 use PhpMyAdmin\DatabaseInterface;
  11 use PhpMyAdmin\Dbal\ConnectionType;
  12 use PhpMyAdmin\Identifiers\DatabaseName;
  13 use PhpMyAdmin\Identifiers\TableName;
  14 use PhpMyAdmin\InternalRelations;
  15 use PhpMyAdmin\SqlParser\Parser;
  16 use PhpMyAdmin\SqlParser\Statements\CreateStatement;
  17 use PhpMyAdmin\SqlParser\Utils\Table as TableUtils;
  18 use PhpMyAdmin\Table\Table;
  19 use PhpMyAdmin\Util;
  20 use PhpMyAdmin\Version;
  21
  22 use function __;
  23 use function array_fill_keys;
  24 use function array_keys;
  25 use function array_reverse;
  26 use function array_search;
  27 use function array_shift;
  28 use function asort;
  29 use function bin2hex;
  30 use function count;
  31 use function explode;
  32 use function file_get_contents;
  33 use function htmlspecialchars;
  34 use function implode;
  35 use function in_array;
  36 use function is_string;
  37 use function ksort;
  38 use function mb_check_encoding;
  39 use function mb_strlen;
  40 use function mb_strtolower;
  41 use function mb_strtoupper;
  42 use function mb_substr;
  43 use function natcasesort;
  44 use function preg_match;
  45 use function sprintf;
  46 use function str_contains;
  47 use function str_replace;
  48 use function strnatcasecmp;
  49 use function trim;
  50 use function uksort;
  51 use function usort;
  52
  53 use const SQL_DIR;
  54
  55 /**
  56  * Set of functions used with the relation and PDF feature
  57  */
  58 class Relation
  59 {
  60     private static RelationParameters|null $cache = null;
  61     private readonly Config $config;
  62
  63     public function __construct(public DatabaseInterface $dbi, Config|null $config = null)
  64     {
  65         $this->config = $config ?? Config::getInstance();
  66     }
  67
  68     public function getRelationParameters(): RelationParameters
  69     {
  70         if (self::$cache === null) {
  71             self::$cache = RelationParameters::fromArray($this->checkRelationsParam());
  72         }
  73
  74         return self::$cache;
  75     }
  76
  77     /**
  78      * @param array<string, bool|string|null> $relationParams
  79      *
  80      * @return array<string, bool|string|null>
  81      */
  82     private function checkTableAccess(array $relationParams): array
  83     {
  84         if (isset($relationParams['relation'], $relationParams['table_info'])) {
  85             if ($this->canAccessStorageTable((string) $relationParams['table_info'])) {
  86                 $relationParams['displaywork'] = true;
  87             }
  88         }
  89
  90         if (isset($relationParams['table_coords'], $relationParams['pdf_pages'])) {
  91             if ($this->canAccessStorageTable((string) $relationParams['table_coords'])) {
  92                 if ($this->canAccessStorageTable((string) $relationParams['pdf_pages'])) {
  93                     $relationParams['pdfwork'] = true;
  94                 }
  95             }
  96         }
  97
  98         if (isset($relationParams['column_info'])) {
  99             if ($this->canAccessStorageTable((string) $relationParams['column_info'])) {
 100                 $relationParams['commwork'] = true;
 101                 // phpMyAdmin 4.3+
 102                 // Check for input transformations upgrade.
 103                 $relationParams['mimework'] = $this->tryUpgradeTransformations();
 104             }
 105         }
 106
 107         if (isset($relationParams['users'], $relationParams['usergroups'])) {
 108             if ($this->canAccessStorageTable((string) $relationParams['users'])) {
 109                 if ($this->canAccessStorageTable((string) $relationParams['usergroups'])) {
 110                     $relationParams['menuswork'] = true;
 111                 }
 112             }
 113         }
 114
 115         $settings = [
 116             'export_templates' => 'exporttemplateswork',
 117             'designer_settings' => 'designersettingswork',
 118             'central_columns' => 'centralcolumnswork',
 119             'savedsearches' => 'savedsearcheswork',
 120             'navigationhiding' => 'navwork',
 121             'bookmark' => 'bookmarkwork',
 122             'userconfig' => 'userconfigwork',
 123             'tracking' => 'trackingwork',
 124             'table_uiprefs' => 'uiprefswork',
 125             'favorite' => 'favoritework',
 126             'recent' => 'recentwork',
 127             'history' => 'historywork',
 128             'relation' => 'relwork',
 129         ];
 130
 131         foreach ($settings as $setingName => $worksKey) {
 132             if (! isset($relationParams[$setingName])) {
 133                 continue;
 134             }
 135
 136             if (! $this->canAccessStorageTable((string) $relationParams[$setingName])) {
 137                 continue;
 138             }
 139
 140             $relationParams[$worksKey] = true;
 141         }
 142
 143         return $relationParams;
 144     }
 145
 146     /**
 147      * @param array<string, bool|string|null> $relationParams
 148      *
 149      * @return array<string, bool|string|null>|null
 150      */
 151     private function fillRelationParamsWithTableNames(array $relationParams): array|null
 152     {
 153         if ($this->arePmadbTablesAllDisabled()) {
 154             return null;
 155         }
 156
 157         $tables = $this->dbi->getTables($this->config->selectedServer['pmadb'], ConnectionType::ControlUser);
 158         if ($tables === []) {
 159             return null;
 160         }
 161
 162         foreach ($tables as $table) {
 163             if ($table == $this->config->selectedServer['bookmarktable']) {
 164                 $relationParams['bookmark'] = $table;
 165             } elseif ($table == $this->config->selectedServer['relation']) {
 166                 $relationParams['relation'] = $table;
 167             } elseif ($table == $this->config->selectedServer['table_info']) {
 168                 $relationParams['table_info'] = $table;
 169             } elseif ($table == $this->config->selectedServer['table_coords']) {
 170                 $relationParams['table_coords'] = $table;
 171             } elseif ($table == $this->config->selectedServer['column_info']) {
 172                 $relationParams['column_info'] = $table;
 173             } elseif ($table == $this->config->selectedServer['pdf_pages']) {
 174                 $relationParams['pdf_pages'] = $table;
 175             } elseif ($table == $this->config->selectedServer['history']) {
 176                 $relationParams['history'] = $table;
 177             } elseif ($table == $this->config->selectedServer['recent']) {
 178                 $relationParams['recent'] = $table;
 179             } elseif ($table == $this->config->selectedServer['favorite']) {
 180                 $relationParams['favorite'] = $table;
 181             } elseif ($table == $this->config->selectedServer['table_uiprefs']) {
 182                 $relationParams['table_uiprefs'] = $table;
 183             } elseif ($table == $this->config->selectedServer['tracking']) {
 184                 $relationParams['tracking'] = $table;
 185             } elseif ($table == $this->config->selectedServer['userconfig']) {
 186                 $relationParams['userconfig'] = $table;
 187             } elseif ($table == $this->config->selectedServer['users']) {
 188                 $relationParams['users'] = $table;
 189             } elseif ($table == $this->config->selectedServer['usergroups']) {
 190                 $relationParams['usergroups'] = $table;
 191             } elseif ($table == $this->config->selectedServer['navigationhiding']) {
 192                 $relationParams['navigationhiding'] = $table;
 193             } elseif ($table == $this->config->selectedServer['savedsearches']) {
 194                 $relationParams['savedsearches'] = $table;
 195             } elseif ($table == $this->config->selectedServer['central_columns']) {
 196                 $relationParams['central_columns'] = $table;
 197             } elseif ($table == $this->config->selectedServer['designer_settings']) {
 198                 $relationParams['designer_settings'] = $table;
 199             } elseif ($table == $this->config->selectedServer['export_templates']) {
 200                 $relationParams['export_templates'] = $table;
 201             }
 202         }
 203
 204         return $relationParams;
 205     }
 206
 207     /**
 208      * Defines the relation parameters for the current user
 209      * just a copy of the functions used for relations ;-)
 210      * but added some stuff to check what will work
 211      *
 212      * @return array<string, bool|string|null> the relation parameters for the current user
 213      */
 214     private function checkRelationsParam(): array
 215     {
 216         $workToTable = [
 217             'relwork' => 'relation',
 218             'displaywork' => ['relation', 'table_info'],
 219             'bookmarkwork' => 'bookmarktable',
 220             'pdfwork' => ['table_coords', 'pdf_pages'],
 221             'commwork' => 'column_info',
 222             'mimework' => 'column_info',
 223             'historywork' => 'history',
 224             'recentwork' => 'recent',
 225             'favoritework' => 'favorite',
 226             'uiprefswork' => 'table_uiprefs',
 227             'trackingwork' => 'tracking',
 228             'userconfigwork' => 'userconfig',
 229             'menuswork' => ['users', 'usergroups'],
 230             'navwork' => 'navigationhiding',
 231             'savedsearcheswork' => 'savedsearches',
 232             'centralcolumnswork' => 'central_columns',
 233             'designersettingswork' => 'designer_settings',
 234             'exporttemplateswork' => 'export_templates',
 235         ];
 236
 237         $relationParams = array_fill_keys(array_keys($workToTable), false);
 238
 239         $relationParams['version'] = Version::VERSION;
 240         $relationParams['allworks'] = false;
 241         $relationParams['user'] = null;
 242         $relationParams['db'] = null;
 243
 244         if (
 245             Current::$server === 0
 246             || $this->config->selectedServer['pmadb'] === ''
 247             || ! $this->dbi->selectDb($this->config->selectedServer['pmadb'], ConnectionType::ControlUser)
 248         ) {
 249             $this->config->selectedServer['pmadb'] = '';
 250
 251             return $relationParams;
 252         }
 253
 254         $relationParams['user'] = $this->config->selectedServer['user'];
 255         $relationParams['db'] = $this->config->selectedServer['pmadb'];
 256
 257         $relationParamsFilled = $this->fillRelationParamsWithTableNames($relationParams);
 258
 259         if ($relationParamsFilled === null) {
 260             return $relationParams;
 261         }
 262
 263         $relationParams = $this->checkTableAccess($relationParamsFilled);
 264
 265         $allWorks = true;
 266         foreach ($workToTable as $work => $table) {
 267             if ($relationParams[$work]) {
 268                 continue;
 269             }
 270
 271             if (is_string($table)) {
 272                 if (isset($this->config->selectedServer[$table]) && $this->config->selectedServer[$table] !== false) {
 273                     $allWorks = false;
 274                     break;
 275                 }
 276             } else {
 277                 $oneNull = false;
 278                 foreach ($table as $t) {
 279                     if (isset($this->config->selectedServer[$t]) && $this->config->selectedServer[$t] === false) {
 280                         $oneNull = true;
 281                         break;
 282                     }
 283                 }
 284
 285                 if (! $oneNull) {
 286                     $allWorks = false;
 287                     break;
 288                 }
 289             }
 290         }
 291
 292         $relationParams['allworks'] = $allWorks;
 293
 294         return $relationParams;
 295     }
 296
 297     /**
 298      * Check if the table is accessible
 299      *
 300      * @param string $tableDbName The table or table.db
 301      */
 302     public function canAccessStorageTable(string $tableDbName): bool
 303     {
 304         $result = $this->dbi->tryQueryAsControlUser('SELECT NULL FROM ' . Util::backquote($tableDbName) . ' LIMIT 0');
 305
 306         return $result !== false;
 307     }
 308
 309     /**
 310      * Check whether column_info table input transformation
 311      * upgrade is required and try to upgrade silently
 312      */
 313     public function tryUpgradeTransformations(): bool
 314     {
 315         // From 4.3, new input oriented transformation feature was introduced.
 316         // Check whether column_info table has input transformation columns
 317         $newCols = ['input_transformation', 'input_transformation_options'];
 318         $query = 'SHOW COLUMNS FROM '
 319             . Util::backquote($this->config->selectedServer['pmadb'])
 320             . '.' . Util::backquote($this->config->selectedServer['column_info'])
 321             . ' WHERE Field IN (\'' . implode('\', \'', $newCols) . '\')';
 322         $result = $this->dbi->tryQueryAsControlUser($query);
 323         if ($result) {
 324             $rows = $result->numRows();
 325             unset($result);
 326             // input transformations are present
 327             // no need to upgrade
 328             if ($rows === 2) {
 329                 return true;
 330
 331                 // try silent upgrade without disturbing the user
 332             }
 333
 334             // read upgrade query file
 335             $query = @file_get_contents(SQL_DIR . 'upgrade_column_info_4_3_0+.sql');
 336             // replace database name from query to with set in config.inc.php
 337             // replace pma__column_info table name from query
 338             // to with set in config.inc.php
 339             $query = str_replace(
 340                 ['`phpmyadmin`', '`pma__column_info`'],
 341                 [
 342                     Util::backquote($this->config->selectedServer['pmadb']),
 343                     Util::backquote($this->config->selectedServer['column_info']),
 344                 ],
 345                 (string) $query,
 346             );
 347             $this->dbi->tryMultiQuery($query, ConnectionType::ControlUser);
 348             // skips result sets of query as we are not interested in it
 349             /** @infection-ignore-all */
 350             do {
 351                 $hasResult = $this->dbi->nextResult(ConnectionType::ControlUser);
 352             } while ($hasResult !== false);
 353
 354             $error = $this->dbi->getError(ConnectionType::ControlUser);
 355
 356             // return true if no error exists otherwise false
 357             return $error === '';
 358         }
 359
 360         // some failure, either in upgrading or something else
 361         // make some noise, time to wake up user.
 362         return false;
 363     }
 364
 365     /**
 366      * Gets all Relations to foreign tables for a given table or
 367      * optionally a given column in a table
 368      *
 369      * @param string $db     the name of the db to check for
 370      * @param string $table  the name of the table to check for
 371      * @param string $column the name of the column to check for
 372      * @param string $source the source for foreign key information
 373      *
 374      * @return mixed[]    db,table,column
 375      */
 376     public function getForeigners(string $db, string $table, string $column = '', string $source = 'both'): array
 377     {
 378         $relationFeature = $this->getRelationParameters()->relationFeature;
 379         $foreign = [];
 380
 381         if ($relationFeature !== null && ($source === 'both' || $source === 'internal')) {
 382             $relQuery = 'SELECT `master_field`, `foreign_db`, '
 383                 . '`foreign_table`, `foreign_field`'
 384                 . ' FROM ' . Util::backquote($relationFeature->database)
 385                 . '.' . Util::backquote($relationFeature->relation)
 386                 . ' WHERE `master_db` = ' . $this->dbi->quoteString($db)
 387                 . ' AND `master_table` = ' . $this->dbi->quoteString($table);
 388             if ($column !== '') {
 389                 $relQuery .= ' AND `master_field` = ' . $this->dbi->quoteString($column);
 390             }
 391
 392             $foreign = $this->dbi->fetchResult($relQuery, 'master_field', null, ConnectionType::ControlUser);
 393         }
 394
 395         if (($source === 'both' || $source === 'foreign') && $table !== '') {
 396             $tableObj = new Table($table, $db, $this->dbi);
 397             $showCreateTable = $tableObj->showCreate();
 398             if ($showCreateTable !== '') {
 399                 $parser = new Parser($showCreateTable);
 400                 $stmt = $parser->statements[0];
 401                 $foreign['foreign_keys_data'] = [];
 402                 if ($stmt instanceof CreateStatement) {
 403                     $foreign['foreign_keys_data'] = TableUtils::getForeignKeys($stmt);
 404                 }
 405             }
 406         }
 407
 408         /**
 409          * Emulating relations for some information_schema tables
 410          */
 411         $isInformationSchema = mb_strtolower($db) === 'information_schema';
 412         $isMysql = mb_strtolower($db) === 'mysql';
 413         if (($isInformationSchema || $isMysql) && ($source === 'internal' || $source === 'both')) {
 414             if ($isInformationSchema) {
 415                 $internalRelations = InternalRelations::INFORMATION_SCHEMA;
 416             } else {
 417                 $internalRelations = InternalRelations::MYSQL;
 418             }
 419
 420             if (isset($internalRelations[$table])) {
 421                 foreach ($internalRelations[$table] as $field => $relations) {
 422                     if (
 423                         ($column !== '' && $column != $field)
 424                         || (isset($foreign[$field])
 425                         && $foreign[$field] != '')
 426                     ) {
 427                         continue;
 428                     }
 429
 430                     $foreign[$field] = $relations;
 431                 }
 432             }
 433         }
 434
 435         return $foreign;
 436     }
 437
 438     /**
 439      * Gets the display field of a table
 440      *
 441      * @param string $db    the name of the db to check for
 442      * @param string $table the name of the table to check for
 443      *
 444      * @return string field name
 445      */
 446     public function getDisplayField(string $db, string $table): string
 447     {
 448         $displayFeature = $this->getRelationParameters()->displayFeature;
 449
 450         /**
 451          * Try to fetch the display field from DB.
 452          */
 453         if ($displayFeature !== null) {
 454             $dispQuery = 'SELECT `display_field`'
 455                     . ' FROM ' . Util::backquote($displayFeature->database)
 456                     . '.' . Util::backquote($displayFeature->tableInfo)
 457                     . ' WHERE `db_name` = ' . $this->dbi->quoteString($db)
 458                     . ' AND `table_name` = ' . $this->dbi->quoteString($table);
 459
 460             $row = $this->dbi->fetchSingleRow($dispQuery, DatabaseInterface::FETCH_ASSOC, ConnectionType::ControlUser);
 461             if (isset($row['display_field'])) {
 462                 return $row['display_field'];
 463             }
 464         }
 465
 466         /**
 467          * Emulating the display field for some information_schema tables.
 468          */
 469         if ($db === 'information_schema') {
 470             switch ($table) {
 471                 case 'CHARACTER_SETS':
 472                     return 'DESCRIPTION';
 473
 474                 case 'TABLES':
 475                     return 'TABLE_COMMENT';
 476             }
 477         }
 478
 479         /**
 480          * Pick first char field
 481          */
 482         $columns = $this->dbi->getColumnsFull($db, $table);
 483         foreach ($columns as $column) {
 484             if ($this->dbi->types->getTypeClass($column['DATA_TYPE']) === 'CHAR') {
 485                 return $column['COLUMN_NAME'];
 486             }
 487         }
 488
 489         return '';
 490     }
 491
 492     /**
 493      * Gets the comments for all columns of a table or the db itself
 494      *
 495      * @param string $db    the name of the db to check for
 496      * @param string $table the name of the table to check for
 497      *
 498      * @return string[]    [column_name] = comment
 499      */
 500     public function getComments(string $db, string $table = ''): array
 501     {
 502         if ($table === '') {
 503             return [$this->getDbComment($db)];
 504         }
 505
 506         $comments = [];
 507
 508         // MySQL native column comments
 509         $columns = $this->dbi->getColumns($db, $table, true);
 510         foreach ($columns as $column) {
 511             if ($column->comment === '') {
 512                 continue;
 513             }
 514
 515             $comments[$column->field] = $column->comment;
 516         }
 517
 518         return $comments;
 519     }
 520
 521     /**
 522      * Gets the comment for a db
 523      *
 524      * @param string $db the name of the db to check for
 525      */
 526     public function getDbComment(string $db): string
 527     {
 528         $columnCommentsFeature = $this->getRelationParameters()->columnCommentsFeature;
 529         if ($columnCommentsFeature !== null) {
 530             // pmadb internal db comment
 531             $comQry = 'SELECT `comment`'
 532                     . ' FROM ' . Util::backquote($columnCommentsFeature->database)
 533                     . '.' . Util::backquote($columnCommentsFeature->columnInfo)
 534                     . ' WHERE db_name = ' . $this->dbi->quoteString($db, ConnectionType::ControlUser)
 535                     . ' AND table_name  = \'\''
 536                     . ' AND column_name = \'(db_comment)\'';
 537             $comRs = $this->dbi->tryQueryAsControlUser($comQry);
 538
 539             if ($comRs && $comRs->numRows() > 0) {
 540                 $row = $comRs->fetchAssoc();
 541
 542                 return (string) $row['comment'];
 543             }
 544         }
 545
 546         return '';
 547     }
 548
 549     /**
 550      * Set a database comment to a certain value.
 551      *
 552      * @param string $db      the name of the db
 553      * @param string $comment the value of the column
 554      */
 555     public function setDbComment(string $db, string $comment = ''): bool
 556     {
 557         $columnCommentsFeature = $this->getRelationParameters()->columnCommentsFeature;
 558         if ($columnCommentsFeature === null) {
 559             return false;
 560         }
 561
 562         if ($comment !== '') {
 563             $updQuery = 'INSERT INTO '
 564                 . Util::backquote($columnCommentsFeature->database) . '.'
 565                 . Util::backquote($columnCommentsFeature->columnInfo)
 566                 . ' (`db_name`, `table_name`, `column_name`, `comment`)'
 567                 . ' VALUES ('
 568                 . $this->dbi->quoteString($db, ConnectionType::ControlUser)
 569                 . ", '', '(db_comment)', "
 570                 . $this->dbi->quoteString($comment, ConnectionType::ControlUser)
 571                 . ') '
 572                 . ' ON DUPLICATE KEY UPDATE '
 573                 . '`comment` = ' . $this->dbi->quoteString($comment, ConnectionType::ControlUser);
 574         } else {
 575             $updQuery = 'DELETE FROM '
 576                 . Util::backquote($columnCommentsFeature->database) . '.'
 577                 . Util::backquote($columnCommentsFeature->columnInfo)
 578                 . ' WHERE `db_name`     = ' . $this->dbi->quoteString($db, ConnectionType::ControlUser)
 579                 . '
 580                     AND `table_name`  = \'\'
 581                     AND `column_name` = \'(db_comment)\'';
 582         }
 583
 584         return (bool) $this->dbi->queryAsControlUser($updQuery);
 585     }
 586
 587     /**
 588      * Set a SQL history entry
 589      *
 590      * @param string $db       the name of the db
 591      * @param string $table    the name of the table
 592      * @param string $username the username
 593      * @param string $sqlquery the sql query
 594      */
 595     public function setHistory(string $db, string $table, string $username, string $sqlquery): void
 596     {
 597         $maxCharactersInDisplayedSQL = $this->config->settings['MaxCharactersInDisplayedSQL'];
 598         // Prevent to run this automatically on Footer class destroying in testsuite
 599         if (mb_strlen($sqlquery) > $maxCharactersInDisplayedSQL) {
 600             return;
 601         }
 602
 603         $sqlHistoryFeature = $this->getRelationParameters()->sqlHistoryFeature;
 604
 605         if (! isset($_SESSION['sql_history'])) {
 606             $_SESSION['sql_history'] = [];
 607         }
 608
 609         $_SESSION['sql_history'][] = ['db' => $db, 'table' => $table, 'sqlquery' => $sqlquery];
 610
 611         if (count($_SESSION['sql_history']) > $this->config->settings['QueryHistoryMax']) {
 612             // history should not exceed a maximum count
 613             array_shift($_SESSION['sql_history']);
 614         }
 615
 616         if ($sqlHistoryFeature === null || ! $this->config->settings['QueryHistoryDB']) {
 617             return;
 618         }
 619
 620         $this->dbi->queryAsControlUser(
 621             'INSERT INTO '
 622             . Util::backquote($sqlHistoryFeature->database) . '.'
 623             . Util::backquote($sqlHistoryFeature->history) . '
 624                   (`username`,
 625                     `db`,
 626                     `table`,
 627                     `timevalue`,
 628                     `sqlquery`)
 629             VALUES
 630                   (' . $this->dbi->quoteString($username, ConnectionType::ControlUser) . ',
 631                    ' . $this->dbi->quoteString($db, ConnectionType::ControlUser) . ',
 632                    ' . $this->dbi->quoteString($table, ConnectionType::ControlUser) . ',
 633                    NOW(),
 634                    ' . $this->dbi->quoteString($sqlquery, ConnectionType::ControlUser) . ')',
 635         );
 636
 637         $this->purgeHistory($username);
 638     }
 639
 640     /**
 641      * Gets a SQL history entry
 642      *
 643      * @param string $username the username
 644      *
 645      * @return mixed[]|bool list of history items
 646      */
 647     public function getHistory(string $username): array|bool
 648     {
 649         $sqlHistoryFeature = $this->getRelationParameters()->sqlHistoryFeature;
 650         if ($sqlHistoryFeature === null) {
 651             return false;
 652         }
 653
 654         /**
 655          * if db-based history is disabled but there exists a session-based
 656          * history, use it
 657          */
 658         if (! $this->config->settings['QueryHistoryDB']) {
 659             if (isset($_SESSION['sql_history'])) {
 660                 return array_reverse($_SESSION['sql_history']);
 661             }
 662
 663             return false;
 664         }
 665
 666         $histQuery = '
 667              SELECT `db`,
 668                     `table`,
 669                     `sqlquery`,
 670                     `timevalue`
 671                FROM ' . Util::backquote($sqlHistoryFeature->database)
 672                 . '.' . Util::backquote($sqlHistoryFeature->history) . '
 673               WHERE `username` = ' . $this->dbi->quoteString($username) . '
 674            ORDER BY `id` DESC';
 675
 676         return $this->dbi->fetchResult($histQuery, null, null, ConnectionType::ControlUser);
 677     }
 678
 679     /**
 680      * purges SQL history
 681      *
 682      * deletes entries that exceeds $cfg['QueryHistoryMax'], oldest first, for the
 683      * given user
 684      *
 685      * @param string $username the username
 686      */
 687     public function purgeHistory(string $username): void
 688     {
 689         $sqlHistoryFeature = $this->getRelationParameters()->sqlHistoryFeature;
 690         if (! $this->config->settings['QueryHistoryDB'] || $sqlHistoryFeature === null) {
 691             return;
 692         }
 693
 694         $searchQuery = '
 695             SELECT `timevalue`
 696             FROM ' . Util::backquote($sqlHistoryFeature->database)
 697                 . '.' . Util::backquote($sqlHistoryFeature->history) . '
 698             WHERE `username` = ' . $this->dbi->quoteString($username) . '
 699             ORDER BY `timevalue` DESC
 700             LIMIT ' . $this->config->settings['QueryHistoryMax'] . ', 1';
 701
 702         $maxTime = $this->dbi->fetchValue($searchQuery, 0, ConnectionType::ControlUser);
 703
 704         if (! $maxTime) {
 705             return;
 706         }
 707
 708         $this->dbi->queryAsControlUser(
 709             'DELETE FROM '
 710             . Util::backquote($sqlHistoryFeature->database) . '.'
 711             . Util::backquote($sqlHistoryFeature->history) . '
 712               WHERE `username` = ' . $this->dbi->quoteString($username, ConnectionType::ControlUser)
 713             . '
 714                 AND `timevalue` <= \'' . $maxTime . '\'',
 715         );
 716     }
 717
 718     /**
 719      * Prepares the dropdown for one mode
 720      *
 721      * @param mixed[] $foreign the keys and values for foreigns
 722      * @param string  $data    the current data of the dropdown
 723      * @param string  $mode    the needed mode
 724      *
 725      * @return string[] the <option value=""><option>s
 726      */
 727     public function buildForeignDropdown(array $foreign, string $data, string $mode): array
 728     {
 729         $reloptions = [];
 730
 731         // id-only is a special mode used when no foreign display column
 732         // is available
 733         if ($mode === 'id-content' || $mode === 'id-only') {
 734             // sort for id-content
 735             if ($this->config->settings['NaturalOrder']) {
 736                 uksort($foreign, strnatcasecmp(...));
 737             } else {
 738                 ksort($foreign);
 739             }
 740         } elseif ($mode === 'content-id') {
 741             // sort for content-id
 742             if ($this->config->settings['NaturalOrder']) {
 743                 natcasesort($foreign);
 744             } else {
 745                 asort($foreign);
 746             }
 747         }
 748
 749         foreach ($foreign as $key => $value) {
 750             $key = (string) $key;
 751             $value = (string) $value;
 752
 753             if (mb_check_encoding($key, 'utf-8') && ! preg_match('/[\x00-\x08\x0B\x0C\x0E-\x1F\x80-\x9F]/u', $key)) {
 754                 $selected = $key === $data;
 755                 // show as text if it's valid utf-8
 756                 $key = htmlspecialchars($key);
 757             } else {
 758                 $key = '0x' . bin2hex($key);
 759                 if (str_contains($data, '0x')) {
 760                     $selected = $key === trim($data);
 761                 } else {
 762                     $selected = $key === '0x' . $data;
 763                 }
 764             }
 765
 766             if (
 767                 mb_check_encoding($value, 'utf-8')
 768                 && ! preg_match('/[\x00-\x08\x0B\x0C\x0E-\x1F\x80-\x9F]/u', $value)
 769             ) {
 770                 if (mb_strlen($value) <= $this->config->settings['LimitChars']) {
 771                     // show as text if it's valid utf-8
 772                     $value = htmlspecialchars($value);
 773                 } else {
 774                     // show as truncated text if it's valid utf-8
 775                     $value = htmlspecialchars(
 776                         mb_substr(
 777                             $value,
 778                             0,
 779                             $this->config->settings['LimitChars'],
 780                         ) . '...',
 781                     );
 782                 }
 783             } else {
 784                 $value = '0x' . bin2hex($value);
 785             }
 786
 787             $reloption = '<option value="' . $key . '"';
 788
 789             if ($selected) {
 790                 $reloption .= ' selected="selected"';
 791             }
 792
 793             if ($mode === 'content-id') {
 794                 $reloptions[] = $reloption . '>'
 795                     . $value . '&nbsp;-&nbsp;' . $key . '</option>';
 796             } elseif ($mode === 'id-content') {
 797                 $reloptions[] = $reloption . '>'
 798                     . $key . '&nbsp;-&nbsp;' . $value . '</option>';
 799             } elseif ($mode === 'id-only') {
 800                 $reloptions[] = $reloption . '>'
 801                     . $key . '</option>';
 802             }
 803         }
 804
 805         return $reloptions;
 806     }
 807
 808     /**
 809      * Outputs dropdown with values of foreign fields
 810      *
 811      * @param mixed[][] $dispRow        array of the displayed row
 812      * @param string    $foreignField   the foreign field
 813      * @param string    $foreignDisplay the foreign field to display
 814      * @param string    $data           the current data of the dropdown (field in row)
 815      * @param int|null  $max            maximum number of items in the dropdown
 816      *
 817      * @return string   the <option value=""><option>s
 818      */
 819     public function foreignDropdown(
 820         array $dispRow,
 821         string $foreignField,
 822         string $foreignDisplay,
 823         string $data,
 824         int|null $max = null,
 825     ): string {
 826         if ($max === null) {
 827             $max = $this->config->settings['ForeignKeyMaxLimit'];
 828         }
 829
 830         $foreign = [];
 831
 832         // collect the data
 833         foreach ($dispRow as $relrow) {
 834             $key = $relrow[$foreignField];
 835
 836             // if the display field has been defined for this foreign table
 837             $value = $foreignDisplay !== '' ? $relrow[$foreignDisplay] : '';
 838
 839             $foreign[$key] = $value;
 840         }
 841
 842         // put the dropdown sections in correct order
 843         $bottom = [];
 844         if ($foreignDisplay !== '') {
 845             $top = $this->buildForeignDropdown($foreign, $data, $this->config->settings['ForeignKeyDropdownOrder'][0]);
 846
 847             if (isset($this->config->settings['ForeignKeyDropdownOrder'][1])) {
 848                 $bottom = $this->buildForeignDropdown(
 849                     $foreign,
 850                     $data,
 851                     $this->config->settings['ForeignKeyDropdownOrder'][1],
 852                 );
 853             }
 854         } else {
 855             $top = $this->buildForeignDropdown($foreign, $data, 'id-only');
 856         }
 857
 858         // beginning of dropdown
 859         $ret = '<option value="">&nbsp;</option>';
 860         $topCount = count($top);
 861         if ($max == -1 || $topCount < $max) {
 862             $ret .= implode('', $top);
 863             if ($foreignDisplay && $topCount > 0) {
 864                 // this empty option is to visually mark the beginning of the
 865                 // second series of values (bottom)
 866                 $ret .= '<option value="">&nbsp;</option>';
 867             }
 868         }
 869
 870         if ($foreignDisplay !== '') {
 871             $ret .= implode('', $bottom);
 872         }
 873
 874         return $ret;
 875     }
 876
 877     /**
 878      * Gets foreign keys in preparation for a drop-down selector
 879      *
 880      * @param mixed[]|bool $foreigners    array of the foreign keys
 881      * @param string       $field         the foreign field name
 882      * @param bool         $overrideTotal whether to override the total
 883      * @param string       $foreignFilter a possible filter
 884      * @param string       $foreignLimit  a possible LIMIT clause
 885      * @param bool         $getTotal      optional, whether to get total num of rows
 886      *                                    in $foreignData['the_total;]
 887      *                                    (has an effect of performance)
 888      */
 889     public function getForeignData(
 890         array|bool $foreigners,
 891         string $field,
 892         bool $overrideTotal,
 893         string $foreignFilter,
 894         string $foreignLimit,
 895         bool $getTotal = false,
 896     ): ForeignData {
 897         // we always show the foreign field in the drop-down; if a display
 898         // field is defined, we show it besides the foreign field
 899         $foreignLink = false;
 900         $dispRow = $foreignDisplay = $theTotal = $foreignField = null;
 901         do {
 902             if ($foreigners === false || $foreigners === []) {
 903                 break;
 904             }
 905
 906             $foreigner = $this->searchColumnInForeigners($foreigners, $field);
 907             if ($foreigner == false) {
 908                 break;
 909             }
 910
 911             $foreignDb = $foreigner['foreign_db'];
 912             $foreignTable = $foreigner['foreign_table'];
 913             $foreignField = $foreigner['foreign_field'];
 914
 915             // Count number of rows in the foreign table. Currently we do
 916             // not use a drop-down if more than ForeignKeyMaxLimit rows in the
 917             // foreign table,
 918             // for speed reasons and because we need a better interface for this.
 919             //
 920             // We could also do the SELECT anyway, with a LIMIT, and ensure that
 921             // the current value of the field is one of the choices.
 922
 923             // Check if table has more rows than specified by ForeignKeyMaxLimit
 924             $moreThanLimit = $this->dbi->getTable($foreignDb, $foreignTable)
 925                 ->checkIfMinRecordsExist($this->config->settings['ForeignKeyMaxLimit']);
 926
 927             if ($overrideTotal || ! $moreThanLimit) {
 928                 // foreign_display can be false if no display field defined:
 929                 $foreignDisplay = $this->getDisplayField($foreignDb, $foreignTable);
 930
 931                 $fQueryMain = 'SELECT ' . Util::backquote($foreignField)
 932                     . (
 933                         $foreignDisplay === ''
 934                             ? ''
 935                             : ', ' . Util::backquote($foreignDisplay)
 936                     );
 937                 $fQueryFrom = ' FROM ' . Util::backquote($foreignDb)
 938                     . '.' . Util::backquote($foreignTable);
 939                 $fQueryFilter = $foreignFilter === '' ? '' : ' WHERE '
 940                     . Util::backquote($foreignField)
 941                     . ' LIKE ' . $this->dbi->quoteString(
 942                         '%' . $this->dbi->escapeMysqlWildcards($foreignFilter) . '%',
 943                     )
 944                     . (
 945                         $foreignDisplay === ''
 946                         ? ''
 947                         : ' OR ' . Util::backquote($foreignDisplay)
 948                         . ' LIKE ' . $this->dbi->quoteString(
 949                             '%' . $this->dbi->escapeMysqlWildcards($foreignFilter) . '%',
 950                         )
 951                     );
 952                 $fQueryOrder = $foreignDisplay === '' ? '' : ' ORDER BY '
 953                     . Util::backquote($foreignTable) . '.'
 954                     . Util::backquote($foreignDisplay);
 955
 956                 $fQueryLimit = $foreignLimit;
 957
 958                 if ($foreignFilter !== '') {
 959                     $theTotal = $this->dbi->fetchValue('SELECT COUNT(*)' . $fQueryFrom . $fQueryFilter);
 960                 }
 961
 962                 $disp = $this->dbi->tryQuery($fQueryMain . $fQueryFrom . $fQueryFilter . $fQueryOrder . $fQueryLimit);
 963                 if ($disp && $disp->numRows() > 0) {
 964                     $dispRow = $disp->fetchAllAssoc();
 965                 } else {
 966                     // Either no data in the foreign table or
 967                     // user does not have select permission to foreign table/field
 968                     // Show an input field with a 'Browse foreign values' link
 969                     $dispRow = null;
 970                     $foreignLink = true;
 971                 }
 972             } else {
 973                 $dispRow = null;
 974                 $foreignLink = true;
 975             }
 976         } while (false);
 977
 978         if ($getTotal && isset($foreignDb, $foreignTable)) {
 979             $theTotal = $this->dbi->getTable($foreignDb, $foreignTable)
 980                 ->countRecords(true);
 981         }
 982
 983         return new ForeignData(
 984             $foreignLink,
 985             (int) $theTotal,
 986             is_string($foreignDisplay) ? $foreignDisplay : '',
 987             $dispRow,
 988             $foreignField,
 989         );
 990     }
 991
 992     /**
 993      * Rename a field in relation tables
 994      *
 995      * usually called after a column in a table was renamed
 996      *
 997      * @param string $db      database name
 998      * @param string $table   table name
 999      * @param string $field   old field name
1000      * @param string $newName new field name
1001      */
1002     public function renameField(string $db, string $table, string $field, string $newName): void
1003     {
1004         $relationParameters = $this->getRelationParameters();
1005
1006         if ($relationParameters->displayFeature !== null) {
1007             $tableQuery = 'UPDATE '
1008                 . Util::backquote($relationParameters->displayFeature->database) . '.'
1009                 . Util::backquote($relationParameters->displayFeature->tableInfo)
1010                 . '   SET display_field = ' . $this->dbi->quoteString($newName, ConnectionType::ControlUser)
1011                 . ' WHERE db_name       = ' . $this->dbi->quoteString($db, ConnectionType::ControlUser)
1012                 . '   AND table_name    = ' . $this->dbi->quoteString($table, ConnectionType::ControlUser)
1013                 . '   AND display_field = ' . $this->dbi->quoteString($field, ConnectionType::ControlUser);
1014             $this->dbi->queryAsControlUser($tableQuery);
1015         }
1016
1017         if ($relationParameters->relationFeature === null) {
1018             return;
1019         }
1020
1021         $tableQuery = 'UPDATE '
1022             . Util::backquote($relationParameters->relationFeature->database) . '.'
1023             . Util::backquote($relationParameters->relationFeature->relation)
1024             . '   SET master_field = ' . $this->dbi->quoteString($newName, ConnectionType::ControlUser)
1025             . ' WHERE master_db    = ' . $this->dbi->quoteString($db, ConnectionType::ControlUser)
1026             . '   AND master_table = ' . $this->dbi->quoteString($table, ConnectionType::ControlUser)
1027             . '   AND master_field = ' . $this->dbi->quoteString($field, ConnectionType::ControlUser);
1028         $this->dbi->queryAsControlUser($tableQuery);
1029
1030         $tableQuery = 'UPDATE '
1031             . Util::backquote($relationParameters->relationFeature->database) . '.'
1032             . Util::backquote($relationParameters->relationFeature->relation)
1033             . '   SET foreign_field = ' . $this->dbi->quoteString($newName, ConnectionType::ControlUser)
1034             . ' WHERE foreign_db    = ' . $this->dbi->quoteString($db, ConnectionType::ControlUser)
1035             . '   AND foreign_table = ' . $this->dbi->quoteString($table, ConnectionType::ControlUser)
1036             . '   AND foreign_field = ' . $this->dbi->quoteString($field, ConnectionType::ControlUser);
1037         $this->dbi->queryAsControlUser($tableQuery);
1038     }
1039
1040     /**
1041      * Performs SQL query used for renaming table.
1042      *
1043      * @param string $sourceDb    Source database name
1044      * @param string $targetDb    Target database name
1045      * @param string $sourceTable Source table name
1046      * @param string $targetTable Target table name
1047      * @param string $dbField     Name of database field
1048      * @param string $tableField  Name of table field
1049      */
1050     public function renameSingleTable(
1051         DatabaseName $configStorageDatabase,
1052         TableName $configStorageTable,
1053         string $sourceDb,
1054         string $targetDb,
1055         string $sourceTable,
1056         string $targetTable,
1057         string $dbField,
1058         string $tableField,
1059     ): void {
1060         $query = 'UPDATE '
1061             . Util::backquote($configStorageDatabase) . '.'
1062             . Util::backquote($configStorageTable)
1063             . ' SET '
1064             . $dbField . ' = ' . $this->dbi->quoteString($targetDb, ConnectionType::ControlUser)
1065             . ', '
1066             . $tableField . ' = ' . $this->dbi->quoteString($targetTable, ConnectionType::ControlUser)
1067             . ' WHERE '
1068             . $dbField . '  = ' . $this->dbi->quoteString($sourceDb, ConnectionType::ControlUser)
1069             . ' AND '
1070             . $tableField . ' = ' . $this->dbi->quoteString($sourceTable, ConnectionType::ControlUser);
1071         $this->dbi->queryAsControlUser($query);
1072     }
1073
1074     /**
1075      * Rename a table in relation tables
1076      *
1077      * usually called after table has been moved
1078      *
1079      * @param string $sourceDb    Source database name
1080      * @param string $targetDb    Target database name
1081      * @param string $sourceTable Source table name
1082      * @param string $targetTable Target table name
1083      */
1084     public function renameTable(string $sourceDb, string $targetDb, string $sourceTable, string $targetTable): void
1085     {
1086         $relationParameters = $this->getRelationParameters();
1087
1088         // Move old entries from PMA-DBs to new table
1089         if ($relationParameters->columnCommentsFeature !== null) {
1090             $this->renameSingleTable(
1091                 $relationParameters->columnCommentsFeature->database,
1092                 $relationParameters->columnCommentsFeature->columnInfo,
1093                 $sourceDb,
1094                 $targetDb,
1095                 $sourceTable,
1096                 $targetTable,
1097                 'db_name',
1098                 'table_name',
1099             );
1100         }
1101
1102         // updating bookmarks is not possible since only a single table is
1103         // moved, and not the whole DB.
1104
1105         if ($relationParameters->displayFeature !== null) {
1106             $this->renameSingleTable(
1107                 $relationParameters->displayFeature->database,
1108                 $relationParameters->displayFeature->tableInfo,
1109                 $sourceDb,
1110                 $targetDb,
1111                 $sourceTable,
1112                 $targetTable,
1113                 'db_name',
1114                 'table_name',
1115             );
1116         }
1117
1118         if ($relationParameters->relationFeature !== null) {
1119             $this->renameSingleTable(
1120                 $relationParameters->relationFeature->database,
1121                 $relationParameters->relationFeature->relation,
1122                 $sourceDb,
1123                 $targetDb,
1124                 $sourceTable,
1125                 $targetTable,
1126                 'foreign_db',
1127                 'foreign_table',
1128             );
1129
1130             $this->renameSingleTable(
1131                 $relationParameters->relationFeature->database,
1132                 $relationParameters->relationFeature->relation,
1133                 $sourceDb,
1134                 $targetDb,
1135                 $sourceTable,
1136                 $targetTable,
1137                 'master_db',
1138                 'master_table',
1139             );
1140         }
1141
1142         if ($relationParameters->pdfFeature !== null) {
1143             if ($sourceDb === $targetDb) {
1144                 // rename within the database can be handled
1145                 $this->renameSingleTable(
1146                     $relationParameters->pdfFeature->database,
1147                     $relationParameters->pdfFeature->tableCoords,
1148                     $sourceDb,
1149                     $targetDb,
1150                     $sourceTable,
1151                     $targetTable,
1152                     'db_name',
1153                     'table_name',
1154                 );
1155             } else {
1156                 // if the table is moved out of the database we can no longer keep the
1157                 // record for table coordinate
1158                 $removeQuery = 'DELETE FROM '
1159                     . Util::backquote($relationParameters->pdfFeature->database) . '.'
1160                     . Util::backquote($relationParameters->pdfFeature->tableCoords)
1161                     . ' WHERE db_name  = ' . $this->dbi->quoteString($sourceDb, ConnectionType::ControlUser)
1162                     . ' AND table_name = ' . $this->dbi->quoteString($sourceTable, ConnectionType::ControlUser);
1163                 $this->dbi->queryAsControlUser($removeQuery);
1164             }
1165         }
1166
1167         if ($relationParameters->uiPreferencesFeature !== null) {
1168             $this->renameSingleTable(
1169                 $relationParameters->uiPreferencesFeature->database,
1170                 $relationParameters->uiPreferencesFeature->tableUiPrefs,
1171                 $sourceDb,
1172                 $targetDb,
1173                 $sourceTable,
1174                 $targetTable,
1175                 'db_name',
1176                 'table_name',
1177             );
1178         }
1179
1180         if ($relationParameters->navigationItemsHidingFeature === null) {
1181             return;
1182         }
1183
1184         // update hidden items inside table
1185         $this->renameSingleTable(
1186             $relationParameters->navigationItemsHidingFeature->database,
1187             $relationParameters->navigationItemsHidingFeature->navigationHiding,
1188             $sourceDb,
1189             $targetDb,
1190             $sourceTable,
1191             $targetTable,
1192             'db_name',
1193             'table_name',
1194         );
1195
1196         // update data for hidden table
1197         $query = 'UPDATE '
1198             . Util::backquote($relationParameters->navigationItemsHidingFeature->database) . '.'
1199             . Util::backquote($relationParameters->navigationItemsHidingFeature->navigationHiding)
1200             . ' SET db_name = ' . $this->dbi->quoteString($targetDb, ConnectionType::ControlUser)
1201             . ','
1202             . ' item_name = ' . $this->dbi->quoteString($targetTable, ConnectionType::ControlUser)
1203             . ' WHERE db_name  = ' . $this->dbi->quoteString($sourceDb, ConnectionType::ControlUser)
1204             . ' AND item_name = ' . $this->dbi->quoteString($sourceTable, ConnectionType::ControlUser)
1205             . " AND item_type = 'table'";
1206         $this->dbi->queryAsControlUser($query);
1207     }
1208
1209     /**
1210      * Create a PDF page
1211      *
1212      * @param string|null $newpage name of the new PDF page
1213      * @param string      $db      database name
1214      */
1215     public function createPage(string|null $newpage, PdfFeature $pdfFeature, string $db): int
1216     {
1217         $insQuery = 'INSERT INTO '
1218             . Util::backquote($pdfFeature->database) . '.'
1219             . Util::backquote($pdfFeature->pdfPages)
1220             . ' (db_name, page_descr)'
1221             . ' VALUES ('
1222             . $this->dbi->quoteString($db, ConnectionType::ControlUser) . ', '
1223             . $this->dbi->quoteString(
1224                 $newpage !== null && $newpage !== '' ? $newpage : __('no description'),
1225                 ConnectionType::ControlUser,
1226             ) . ')';
1227         $this->dbi->tryQueryAsControlUser($insQuery);
1228
1229         return $this->dbi->insertId(ConnectionType::ControlUser);
1230     }
1231
1232     /**
1233      * Get child table references for a table column.
1234      * This works only if 'DisableIS' is false. An empty array is returned otherwise.
1235      *
1236      * @param string $db     name of master table db.
1237      * @param string $table  name of master table.
1238      * @param string $column name of master table column.
1239      *
1240      * @return mixed[]
1241      */
1242     public function getChildReferences(string $db, string $table, string $column = ''): array
1243     {
1244         if (! $this->config->selectedServer['DisableIS']) {
1245             $relQuery = 'SELECT `column_name`, `table_name`,'
1246                 . ' `table_schema`, `referenced_column_name`'
1247                 . ' FROM `information_schema`.`key_column_usage`'
1248                 . ' WHERE `referenced_table_name` = '
1249                 . $this->dbi->quoteString($table)
1250                 . ' AND `referenced_table_schema` = '
1251                 . $this->dbi->quoteString($db);
1252             if ($column !== '') {
1253                 $relQuery .= ' AND `referenced_column_name` = '
1254                     . $this->dbi->quoteString($column);
1255             }
1256
1257             return $this->dbi->fetchResult(
1258                 $relQuery,
1259                 ['referenced_column_name', null],
1260             );
1261         }
1262
1263         return [];
1264     }
1265
1266     /**
1267      * Check child table references and foreign key for a table column.
1268      *
1269      * @param string       $db                  name of master table db.
1270      * @param string       $table               name of master table.
1271      * @param string       $column              name of master table column.
1272      * @param mixed[]|null $foreignersFull      foreigners array for the whole table.
1273      * @param mixed[]|null $childReferencesFull child references for the whole table.
1274      *
1275      * @return array<string, mixed> telling about references if foreign key.
1276      * @psalm-return array{isEditable: bool, isForeignKey: bool, isReferenced: bool, references: string[]}
1277      */
1278     public function checkChildForeignReferences(
1279         string $db,
1280         string $table,
1281         string $column,
1282         array|null $foreignersFull = null,
1283         array|null $childReferencesFull = null,
1284     ): array {
1285         $columnStatus = ['isEditable' => true, 'isReferenced' => false, 'isForeignKey' => false, 'references' => []];
1286
1287         $foreigners = [];
1288         if ($foreignersFull !== null) {
1289             if (isset($foreignersFull[$column])) {
1290                 $foreigners[$column] = $foreignersFull[$column];
1291             }
1292
1293             if (isset($foreignersFull['foreign_keys_data'])) {
1294                 $foreigners['foreign_keys_data'] = $foreignersFull['foreign_keys_data'];
1295             }
1296         } else {
1297             $foreigners = $this->getForeigners($db, $table, $column, 'foreign');
1298         }
1299
1300         $foreigner = $this->searchColumnInForeigners($foreigners, $column);
1301
1302         $childReferences = [];
1303         if ($childReferencesFull !== null) {
1304             if (isset($childReferencesFull[$column])) {
1305                 $childReferences = $childReferencesFull[$column];
1306             }
1307         } else {
1308             $childReferences = $this->getChildReferences($db, $table, $column);
1309         }
1310
1311         if (count($childReferences) > 0 || $foreigner) {
1312             $columnStatus['isEditable'] = false;
1313             if (count($childReferences) > 0) {
1314                 $columnStatus['isReferenced'] = true;
1315                 foreach ($childReferences as $columns) {
1316                     $columnStatus['references'][] = Util::backquote($columns['table_schema'])
1317                         . '.' . Util::backquote($columns['table_name']);
1318                 }
1319             }
1320
1321             if ($foreigner) {
1322                 $columnStatus['isForeignKey'] = true;
1323             }
1324         }
1325
1326         return $columnStatus;
1327     }
1328
1329     /**
1330      * Search a table column in foreign data.
1331      *
1332      * @param mixed[] $foreigners Table Foreign data
1333      * @param string  $column     Column name
1334      */
1335     public function searchColumnInForeigners(array $foreigners, string $column): array|false
1336     {
1337         if (isset($foreigners[$column])) {
1338             return $foreigners[$column];
1339         }
1340
1341         if (! isset($foreigners['foreign_keys_data'])) {
1342             return false;
1343         }
1344
1345         $foreigner = [];
1346         foreach ($foreigners['foreign_keys_data'] as $oneKey) {
1347             $columnIndex = array_search($column, $oneKey['index_list']);
1348             if ($columnIndex !== false) {
1349                 $foreigner['foreign_field'] = $oneKey['ref_index_list'][$columnIndex];
1350                 $foreigner['foreign_db'] = $oneKey['ref_db_name'] ?? Current::$database;
1351                 $foreigner['foreign_table'] = $oneKey['ref_table_name'];
1352                 $foreigner['constraint'] = $oneKey['constraint'];
1353                 $foreigner['on_update'] = $oneKey['on_update'] ?? 'RESTRICT';
1354                 $foreigner['on_delete'] = $oneKey['on_delete'] ?? 'RESTRICT';
1355
1356                 return $foreigner;
1357             }
1358         }
1359
1360         return false;
1361     }
1362
1363     /**
1364      * Returns default PMA table names and their create queries.
1365      *
1366      * @param array<string, string> $tableNameReplacements
1367      *
1368      * @return array<string, string> table name, create query
1369      */
1370     public function getCreateTableSqlQueries(array $tableNameReplacements): array
1371     {
1372         $pmaTables = [];
1373         $createTablesFile = (string) file_get_contents(SQL_DIR . 'create_tables.sql');
1374
1375         $queries = explode(';', $createTablesFile);
1376
1377         foreach ($queries as $query) {
1378             if (! preg_match('/CREATE TABLE IF NOT EXISTS `(.*)` \(/', $query, $table)) {
1379                 continue;
1380             }
1381
1382             $tableName = $table[1];
1383
1384             // Replace the table name with another one
1385             if (isset($tableNameReplacements[$tableName])) {
1386                 $query = str_replace($tableName, $tableNameReplacements[$tableName], $query);
1387             }
1388
1389             $pmaTables[$tableName] = $query . ';';
1390         }
1391
1392         return $pmaTables;
1393     }
1394
1395     /**
1396      * Create a database to be used as configuration storage
1397      */
1398     public function createPmaDatabase(string $configurationStorageDbName): bool
1399     {
1400         $this->dbi->tryQuery(
1401             'CREATE DATABASE IF NOT EXISTS ' . Util::backquote($configurationStorageDbName),
1402             ConnectionType::ControlUser,
1403         );
1404
1405         $error = $this->dbi->getError(ConnectionType::ControlUser);
1406         if ($error === '') {
1407             // Re-build the cache to show the list of tables created or not
1408             // This is the case when the DB could be created but no tables just after
1409             // So just purge the cache and show the new configuration storage state
1410             self::$cache = null;
1411             $this->getRelationParameters();
1412
1413             return true;
1414         }
1415
1416         $GLOBALS['message'] = $error;
1417
1418         if ($GLOBALS['errno'] === 1044) {
1419             $GLOBALS['message'] = sprintf(
1420                 __(
1421                     'You do not have necessary privileges to create a database named'
1422                     . ' \'%s\'. You may go to \'Operations\' tab of any'
1423                     . ' database to set up the phpMyAdmin configuration storage there.',
1424                 ),
1425                 $configurationStorageDbName,
1426             );
1427         }
1428
1429         return false;
1430     }
1431
1432     /**
1433      * Creates PMA tables in the given db, updates if already exists.
1434      *
1435      * @param string $db     database
1436      * @param bool   $create whether to create tables if they don't exist.
1437      */
1438     public function fixPmaTables(string $db, bool $create = true): void
1439     {
1440         if ($this->arePmadbTablesAllDisabled()) {
1441             return;
1442         }
1443
1444         $tablesToFeatures = [
1445             'pma__bookmark' => 'bookmarktable',
1446             'pma__relation' => 'relation',
1447             'pma__table_info' => 'table_info',
1448             'pma__table_coords' => 'table_coords',
1449             'pma__pdf_pages' => 'pdf_pages',
1450             'pma__column_info' => 'column_info',
1451             'pma__history' => 'history',
1452             'pma__recent' => 'recent',
1453             'pma__favorite' => 'favorite',
1454             'pma__table_uiprefs' => 'table_uiprefs',
1455             'pma__tracking' => 'tracking',
1456             'pma__userconfig' => 'userconfig',
1457             'pma__users' => 'users',
1458             'pma__usergroups' => 'usergroups',
1459             'pma__navigationhiding' => 'navigationhiding',
1460             'pma__savedsearches' => 'savedsearches',
1461             'pma__central_columns' => 'central_columns',
1462             'pma__designer_settings' => 'designer_settings',
1463             'pma__export_templates' => 'export_templates',
1464         ];
1465
1466         $existingTables = $this->dbi->getTables($db, ConnectionType::ControlUser);
1467
1468         $tableNameReplacements = $this->getTableReplacementNames($tablesToFeatures);
1469
1470         $createQueries = [];
1471         if ($create) {
1472             $createQueries = $this->getCreateTableSqlQueries($tableNameReplacements);
1473             if (! $this->dbi->selectDb($db, ConnectionType::ControlUser)) {
1474                 $GLOBALS['message'] = $this->dbi->getError(ConnectionType::ControlUser);
1475
1476                 return;
1477             }
1478         }
1479
1480         $foundOne = false;
1481         foreach ($tablesToFeatures as $table => $feature) {
1482             if (($this->config->selectedServer[$feature] ?? null) === false) {
1483                 // The feature is disabled by the user in config
1484                 continue;
1485             }
1486
1487             // Check if the table already exists
1488             // use the possible replaced name first and fallback on the table name
1489             // if no replacement exists
1490             if (! in_array($tableNameReplacements[$table] ?? $table, $existingTables, true)) {
1491                 if (! $create) {
1492                     continue;
1493                 }
1494
1495                 $this->dbi->tryQuery($createQueries[$table], ConnectionType::ControlUser);
1496
1497                 $error = $this->dbi->getError(ConnectionType::ControlUser);
1498                 if ($error !== '') {
1499                     $GLOBALS['message'] = $error;
1500
1501                     return;
1502                 }
1503             }
1504
1505             $foundOne = true;
1506
1507             // Do not override a user defined value, only fill if empty
1508             if (isset($this->config->selectedServer[$feature]) && $this->config->selectedServer[$feature] !== '') {
1509                 continue;
1510             }
1511
1512             // Fill it with the default table name
1513             $this->config->selectedServer[$feature] = $table;
1514         }
1515
1516         if (! $foundOne) {
1517             return;
1518         }
1519
1520         $this->config->selectedServer['pmadb'] = $db;
1521
1522         // Unset the cache as new tables might have been added
1523         self::$cache = null;
1524         // Fill back the cache
1525         $this->getRelationParameters();
1526     }
1527
1528     /**
1529      * Gets the relations info and status, depending on the condition
1530      *
1531      * @param bool   $condition whether to look for foreigners or not
1532      * @param string $db        database name
1533      * @param string $table     table name
1534      *
1535      * @return mixed[]
1536      */
1537     public function getRelationsAndStatus(bool $condition, string $db, string $table): array
1538     {
1539         if ($condition) {
1540             // Find which tables are related with the current one and write it in an array
1541             return $this->getForeigners($db, $table);
1542         }
1543
1544         return [];
1545     }
1546
1547     /**
1548      * Verifies that all pmadb features are disabled
1549      */
1550     public function arePmadbTablesAllDisabled(): bool
1551     {
1552         return ($this->config->selectedServer['bookmarktable'] ?? null) === false
1553             && ($this->config->selectedServer['relation'] ?? null) === false
1554             && ($this->config->selectedServer['table_info'] ?? null) === false
1555             && ($this->config->selectedServer['table_coords'] ?? null) === false
1556             && ($this->config->selectedServer['column_info'] ?? null) === false
1557             && ($this->config->selectedServer['pdf_pages'] ?? null) === false
1558             && ($this->config->selectedServer['history'] ?? null) === false
1559             && ($this->config->selectedServer['recent'] ?? null) === false
1560             && ($this->config->selectedServer['favorite'] ?? null) === false
1561             && ($this->config->selectedServer['table_uiprefs'] ?? null) === false
1562             && ($this->config->selectedServer['tracking'] ?? null) === false
1563             && ($this->config->selectedServer['userconfig'] ?? null) === false
1564             && ($this->config->selectedServer['users'] ?? null) === false
1565             && ($this->config->selectedServer['usergroups'] ?? null) === false
1566             && ($this->config->selectedServer['navigationhiding'] ?? null) === false
1567             && ($this->config->selectedServer['savedsearches'] ?? null) === false
1568             && ($this->config->selectedServer['central_columns'] ?? null) === false
1569             && ($this->config->selectedServer['designer_settings'] ?? null) === false
1570             && ($this->config->selectedServer['export_templates'] ?? null) === false;
1571     }
1572
1573     /**
1574      * Verifies if all the pmadb tables are defined
1575      */
1576     public function arePmadbTablesDefined(): bool
1577     {
1578         return ! (empty($this->config->selectedServer['bookmarktable'])
1579             || empty($this->config->selectedServer['relation'])
1580             || empty($this->config->selectedServer['table_info'])
1581             || empty($this->config->selectedServer['table_coords'])
1582             || empty($this->config->selectedServer['column_info'])
1583             || empty($this->config->selectedServer['pdf_pages'])
1584             || empty($this->config->selectedServer['history'])
1585             || empty($this->config->selectedServer['recent'])
1586             || empty($this->config->selectedServer['favorite'])
1587             || empty($this->config->selectedServer['table_uiprefs'])
1588             || empty($this->config->selectedServer['tracking'])
1589             || empty($this->config->selectedServer['userconfig'])
1590             || empty($this->config->selectedServer['users'])
1591             || empty($this->config->selectedServer['usergroups'])
1592             || empty($this->config->selectedServer['navigationhiding'])
1593             || empty($this->config->selectedServer['savedsearches'])
1594             || empty($this->config->selectedServer['central_columns'])
1595             || empty($this->config->selectedServer['designer_settings'])
1596             || empty($this->config->selectedServer['export_templates']));
1597     }
1598
1599     /**
1600      * Get tables for foreign key constraint
1601      *
1602      * @param string $foreignDb        Database name
1603      * @param string $tblStorageEngine Table storage engine
1604      *
1605      * @return mixed[] Table names
1606      */
1607     public function getTables(string $foreignDb, string $tblStorageEngine): array
1608     {
1609         $tables = [];
1610         $tablesRows = $this->dbi->query('SHOW TABLE STATUS FROM ' . Util::backquote($foreignDb));
1611         while ($row = $tablesRows->fetchRow()) {
1612             if (! isset($row[1]) || mb_strtoupper($row[1]) !== $tblStorageEngine) {
1613                 continue;
1614             }
1615
1616             $tables[] = $row[0];
1617         }
1618
1619         if ($this->config->settings['NaturalOrder']) {
1620             usort($tables, strnatcasecmp(...));
1621         }
1622
1623         return $tables;
1624     }
1625
1626     public function getConfigurationStorageDbName(): string
1627     {
1628         $cfgStorageDbName = $this->config->selectedServer['pmadb'] ?? '';
1629
1630         // Use "phpmyadmin" as a default database name to check to keep the behavior consistent
1631         return empty($cfgStorageDbName) ? 'phpmyadmin' : $cfgStorageDbName;
1632     }
1633
1634     /**
1635      * This function checks and initializes the phpMyAdmin configuration
1636      * storage state before it is used into session cache.
1637      */
1638     public function initRelationParamsCache(): void
1639     {
1640         $storageDbName = $this->config->selectedServer['pmadb'] ?? '';
1641         // Use "phpmyadmin" as a default database name to check to keep the behavior consistent
1642         $storageDbName = $storageDbName !== '' ? $storageDbName : 'phpmyadmin';
1643
1644         // This will make users not having explicitly listed databases
1645         // have config values filled by the default phpMyAdmin storage table name values
1646         $this->fixPmaTables($storageDbName, false);
1647
1648         // This global will be changed if fixPmaTables did find one valid table
1649         // Empty means that until now no pmadb was found eligible
1650         if ($this->config->selectedServer['pmadb'] !== '') {
1651             return;
1652         }
1653
1654         $this->fixPmaTables(Current::$database, false);
1655     }
1656
1657     /**
1658      * @param non-empty-array<string, string> $tablesToFeatures
1659      *
1660      * @return array<string, string>
1661      */
1662     private function getTableReplacementNames(array $tablesToFeatures): array
1663     {
1664         $tableNameReplacements = [];
1665
1666         foreach ($tablesToFeatures as $table => $feature) {
1667             if (empty($this->config->selectedServer[$feature]) || $this->config->selectedServer[$feature] === $table) {
1668                 continue;
1669             }
1670
1671             // Set the replacement to transform the default table name into a custom name
1672             $tableNameReplacements[$table] = $this->config->selectedServer[$feature];
1673         }
1674
1675         return $tableNameReplacements;
1676     }
1677 }