src/Controllers/Table/SearchController.php

   1 <?php
   2
   3 declare(strict_types=1);
   4
   5 namespace PhpMyAdmin\Controllers\Table;
   6
   7 use PhpMyAdmin\Bookmarks\BookmarkRepository;
   8 use PhpMyAdmin\Config;
   9 use PhpMyAdmin\ConfigStorage\Relation;
  10 use PhpMyAdmin\ConfigStorage\RelationCleanup;
  11 use PhpMyAdmin\Controllers\InvocableController;
  12 use PhpMyAdmin\Current;
  13 use PhpMyAdmin\DatabaseInterface;
  14 use PhpMyAdmin\DbTableExists;
  15 use PhpMyAdmin\Http\Response;
  16 use PhpMyAdmin\Http\ServerRequest;
  17 use PhpMyAdmin\Identifiers\DatabaseName;
  18 use PhpMyAdmin\Identifiers\TableName;
  19 use PhpMyAdmin\Message;
  20 use PhpMyAdmin\ResponseRenderer;
  21 use PhpMyAdmin\Sql;
  22 use PhpMyAdmin\Table\Search;
  23 use PhpMyAdmin\Template;
  24 use PhpMyAdmin\Transformations;
  25 use PhpMyAdmin\Url;
  26 use PhpMyAdmin\Util;
  27 use PhpMyAdmin\Utils\Gis;
  28
  29 use function __;
  30 use function array_keys;
  31 use function in_array;
  32 use function mb_strtolower;
  33 use function md5;
  34 use function preg_match;
  35 use function preg_replace;
  36 use function str_ireplace;
  37 use function str_replace;
  38 use function strncasecmp;
  39 use function strtoupper;
  40
  41 /**
  42  * Handles table search tab.
  43  *
  44  * Display table search form, create SQL query from form data
  45  * and call Sql::executeQueryAndSendQueryResponse() to execute it.
  46  */
  47 final class SearchController implements InvocableController
  48 {
  49     /**
  50      * Names of columns
  51      *
  52      * @var list<string>
  53      */
  54     private array $columnNames = [];
  55     /**
  56      * Types of columns
  57      *
  58      * @var list<string>
  59      */
  60     private array $columnTypes = [];
  61     /**
  62      * Types of columns without any replacement
  63      *
  64      * @var list<string>
  65      */
  66     private array $originalColumnTypes = [];
  67     /**
  68      * Collations of columns
  69      *
  70      * @var list<string>
  71      */
  72     private array $columnCollations = [];
  73     /**
  74      * Null Flags of columns
  75      *
  76      * @var list<bool>
  77      */
  78     private array $columnNullFlags = [];
  79     /**
  80      * Whether a geometry column is present
  81      */
  82     private bool $geomColumnFlag = false;
  83     /**
  84      * Foreign Keys
  85      *
  86      * @var mixed[]
  87      */
  88     private array $foreigners = [];
  89
  90     public function __construct(
  91         private readonly ResponseRenderer $response,
  92         private readonly Template $template,
  93         private readonly Search $search,
  94         private readonly Relation $relation,
  95         private readonly DatabaseInterface $dbi,
  96         private readonly DbTableExists $dbTableExists,
  97     ) {
  98     }
  99
 100     /**
 101      * Gets all the columns of a table along with their types, collations
 102      * and whether null or not.
 103      */
 104     private function loadTableInfo(): void
 105     {
 106         // Gets the list and number of columns
 107         $columns = $this->dbi->getColumns(Current::$database, Current::$table, true);
 108         // Get details about the geometry functions
 109         $geomTypes = Gis::getDataTypes();
 110
 111         foreach ($columns as $row) {
 112             // set column name
 113             $this->columnNames[] = $row->field;
 114
 115             $type = $row->type;
 116             // before any replacement
 117             $this->originalColumnTypes[] = mb_strtolower($type);
 118             // check whether table contains geometric columns
 119             if (in_array($type, $geomTypes, true)) {
 120                 $this->geomColumnFlag = true;
 121             }
 122
 123             // reformat mysql query output
 124             if (strncasecmp($type, 'set', 3) == 0 || strncasecmp($type, 'enum', 4) == 0) {
 125                 $type = str_replace(',', ', ', $type);
 126             } else {
 127                 // strip the "BINARY" attribute, except if we find "BINARY(" because
 128                 // this would be a BINARY or VARBINARY column type
 129                 if (! preg_match('@BINARY[\(]@i', $type)) {
 130                     $type = str_ireplace('BINARY', '', $type);
 131                 }
 132
 133                 $type = str_ireplace('ZEROFILL', '', $type);
 134                 $type = str_ireplace('UNSIGNED', '', $type);
 135                 $type = mb_strtolower($type);
 136             }
 137
 138             if ($type === '') {
 139                 $type = '&nbsp;';
 140             }
 141
 142             $this->columnTypes[] = $type;
 143             $this->columnNullFlags[] = $row->isNull;
 144             $this->columnCollations[] = ! empty($row->collation) && $row->collation !== 'NULL'
 145                 ? $row->collation
 146                 : '';
 147         }
 148
 149         // Retrieve foreign keys
 150         $this->foreigners = $this->relation->getForeigners(Current::$database, Current::$table);
 151     }
 152
 153     /**
 154      * Index action
 155      */
 156     public function __invoke(ServerRequest $request): Response|null
 157     {
 158         if (! $this->response->checkParameters(['db', 'table'])) {
 159             return null;
 160         }
 161
 162         $GLOBALS['urlParams'] = ['db' => Current::$database, 'table' => Current::$table];
 163         $GLOBALS['errorUrl'] = Util::getScriptNameForOption(
 164             Config::getInstance()->settings['DefaultTabTable'],
 165             'table',
 166         );
 167         $GLOBALS['errorUrl'] .= Url::getCommon($GLOBALS['urlParams'], '&');
 168
 169         $databaseName = DatabaseName::tryFrom($request->getParam('db'));
 170         if ($databaseName === null || ! $this->dbTableExists->selectDatabase($databaseName)) {
 171             if ($request->isAjax()) {
 172                 $this->response->setRequestStatus(false);
 173                 $this->response->addJSON('message', Message::error(__('No databases selected.')));
 174
 175                 return null;
 176             }
 177
 178             $this->response->redirectToRoute('/', ['reload' => true, 'message' => __('No databases selected.')]);
 179
 180             return null;
 181         }
 182
 183         $tableName = TableName::tryFrom($request->getParam('table'));
 184         if ($tableName === null || ! $this->dbTableExists->hasTable($databaseName, $tableName)) {
 185             if ($request->isAjax()) {
 186                 $this->response->setRequestStatus(false);
 187                 $this->response->addJSON('message', Message::error(__('No table selected.')));
 188
 189                 return null;
 190             }
 191
 192             $this->response->redirectToRoute('/', ['reload' => true, 'message' => __('No table selected.')]);
 193
 194             return null;
 195         }
 196
 197         $this->loadTableInfo();
 198
 199         $this->response->addScriptFiles([
 200             'makegrid.js',
 201             'sql.js',
 202             'table/select.js',
 203             'table/change.js',
 204             'vendor/jquery/jquery.uitablefilter.js',
 205             'gis_data_editor.js',
 206         ]);
 207
 208         if (isset($_POST['range_search'])) {
 209             $this->rangeSearchAction();
 210
 211             return null;
 212         }
 213
 214         /**
 215          * No selection criteria received -> display the selection form
 216          */
 217         if (! isset($_POST['columnsToDisplay']) && ! isset($_POST['displayAllColumns'])) {
 218             $this->displaySelectionFormAction();
 219         } else {
 220             $this->doSelectionAction();
 221         }
 222
 223         return null;
 224     }
 225
 226     /**
 227      * Do selection action
 228      */
 229     private function doSelectionAction(): void
 230     {
 231         /**
 232          * Selection criteria have been submitted -> do the work
 233          */
 234         $sqlQuery = $this->search->buildSqlQuery();
 235
 236         /**
 237          * Add this to ensure following procedures included running correctly.
 238          */
 239         $sql = new Sql(
 240             $this->dbi,
 241             $this->relation,
 242             new RelationCleanup($this->dbi, $this->relation),
 243             new Transformations(),
 244             $this->template,
 245             new BookmarkRepository($this->dbi, $this->relation),
 246             Config::getInstance(),
 247         );
 248
 249         $this->response->addHTML($sql->executeQueryAndSendQueryResponse(
 250             null,
 251             false, // is_gotofile
 252             Current::$database, // db
 253             Current::$table, // table
 254             null, // sql_query_for_bookmark
 255             null, // message_to_show
 256             null, // sql_data
 257             $GLOBALS['goto'], // goto
 258             null, // disp_query
 259             null, // disp_message
 260             $sqlQuery, // sql_query
 261             null, // complete_query
 262         ));
 263     }
 264
 265     /**
 266      * Display selection form action
 267      */
 268     private function displaySelectionFormAction(): void
 269     {
 270         $config = Config::getInstance();
 271         if (! isset($GLOBALS['goto'])) {
 272             $GLOBALS['goto'] = Util::getScriptNameForOption($config->settings['DefaultTabTable'], 'table');
 273         }
 274
 275         $properties = [];
 276         foreach (array_keys($this->columnNames) as $columnIndex) {
 277             $properties[$columnIndex] = $this->getColumnProperties($columnIndex, $columnIndex);
 278         }
 279
 280         $this->response->render('table/search/index', [
 281             'db' => Current::$database,
 282             'table' => Current::$table,
 283             'goto' => $GLOBALS['goto'],
 284             'properties' => $properties,
 285             'geom_column_flag' => $this->geomColumnFlag,
 286             'column_names' => $this->columnNames,
 287             'column_types' => $this->columnTypes,
 288             'column_collations' => $this->columnCollations,
 289             'default_sliders_state' => $config->settings['InitialSlidersState'],
 290             'max_rows' => (int) $config->settings['MaxRows'],
 291         ]);
 292     }
 293
 294     /**
 295      * Range search action
 296      */
 297     private function rangeSearchAction(): void
 298     {
 299         $minMax = $this->getColumnMinMax($_POST['column']);
 300         $this->response->addJSON('column_data', $minMax);
 301     }
 302
 303     /**
 304      * Finds minimum and maximum value of a given column.
 305      *
 306      * @param string $column Column name
 307      *
 308      * @return mixed[]|null
 309      */
 310     private function getColumnMinMax(string $column): array|null
 311     {
 312         $sqlQuery = 'SELECT MIN(' . Util::backquote($column) . ') AS `min`, '
 313             . 'MAX(' . Util::backquote($column) . ') AS `max` '
 314             . 'FROM ' . Util::backquote(Current::$database) . '.'
 315             . Util::backquote(Current::$table);
 316
 317         return $this->dbi->fetchSingleRow($sqlQuery);
 318     }
 319
 320     /**
 321      * Provides a column's type, collation, operators list, and criteria value
 322      * to display in table search form
 323      *
 324      * @param int $searchIndex Row number in table search form
 325      * @param int $columnIndex Column index in ColumnNames array
 326      *
 327      * @return mixed[] Array containing column's properties
 328      */
 329     private function getColumnProperties(int $searchIndex, int $columnIndex): array
 330     {
 331         $selectedOperator = $_POST['criteriaColumnOperators'][$searchIndex] ?? '';
 332         $enteredValue = $_POST['criteriaValues'] ?? '';
 333         //Gets column's type and collation
 334         $type = $this->columnTypes[$columnIndex];
 335         $collation = $this->columnCollations[$columnIndex];
 336         $cleanType = preg_replace('@\(.*@s', '', $type);
 337         //Gets column's comparison operators depending on column type
 338         $typeOperators = $this->dbi->types->getTypeOperatorsHtml(
 339             $cleanType,
 340             $this->columnNullFlags[$columnIndex],
 341             $selectedOperator,
 342         );
 343         $func = $this->template->render('table/search/column_comparison_operators', [
 344             'search_index' => $searchIndex,
 345             'type_operators' => $typeOperators,
 346         ]);
 347         //Gets link to browse foreign data(if any) and criteria inputbox
 348         $foreignData = $this->relation->getForeignData(
 349             $this->foreigners,
 350             $this->columnNames[$columnIndex],
 351             false,
 352             '',
 353             '',
 354         );
 355         $htmlAttributes = '';
 356         $isInteger = in_array($cleanType, $this->dbi->types->getIntegerTypes(), true);
 357         $isFloat = in_array($cleanType, $this->dbi->types->getFloatTypes(), true);
 358         if ($isInteger) {
 359             $extractedColumnspec = Util::extractColumnSpec($this->originalColumnTypes[$columnIndex]);
 360             $isUnsigned = $extractedColumnspec['unsigned'];
 361             $minMaxValues = $this->dbi->types->getIntegerRange($cleanType, ! $isUnsigned);
 362             $htmlAttributes = 'data-min="' . $minMaxValues[0] . '" '
 363                             . 'data-max="' . $minMaxValues[1] . '"';
 364         }
 365
 366         $htmlAttributes .= ' onfocus="return '
 367                         . 'verifyAfterSearchFieldChange(' . $searchIndex . ', \'#tbl_search_form\')"';
 368
 369         $foreignDropdown = '';
 370
 371         $searchColumnInForeigners = $this->relation->searchColumnInForeigners(
 372             $this->foreigners,
 373             $this->columnNames[$columnIndex],
 374         );
 375
 376         if (
 377             $this->foreigners
 378             && $searchColumnInForeigners
 379             && $foreignData->dispRow !== null
 380         ) {
 381             $foreignDropdown = $this->relation->foreignDropdown(
 382                 $foreignData->dispRow,
 383                 $foreignData->foreignField,
 384                 $foreignData->foreignDisplay,
 385                 '',
 386                 Config::getInstance()->settings['ForeignKeyMaxLimit'],
 387             );
 388         }
 389
 390         $value = $this->template->render('table/search/input_box', [
 391             'str' => '',
 392             'column_type' => $type,
 393             'column_data_type' => $isInteger ? 'INT' : ($isFloat ? 'FLOAT' : strtoupper($cleanType)),
 394             'html_attributes' => $htmlAttributes,
 395             'column_id' => 'fieldID_',
 396             'in_zoom_search_edit' => false,
 397             'foreigners' => $this->foreigners,
 398             'column_name' => $this->columnNames[$columnIndex],
 399             'column_name_hash' => md5($this->columnNames[$columnIndex]),
 400             'foreign_data' => $foreignData,
 401             'table' => Current::$table,
 402             'column_index' => $searchIndex,
 403             'criteria_values' => $enteredValue,
 404             'db' => Current::$database,
 405             'in_fbs' => true,
 406             'foreign_dropdown' => $foreignDropdown,
 407             'search_column_in_foreigners' => $searchColumnInForeigners,
 408             'is_integer' => $isInteger,
 409             'is_float' => $isFloat,
 410         ]);
 411
 412         return ['type' => $type, 'collation' => $collation, 'func' => $func, 'value' => $value];
 413     }
 414 }