libraries/classes/Controllers/Table/SearchController.php

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