2 declare(strict_types
=1);
4 namespace PhpMyAdmin\Controllers\Table
;
7 use PhpMyAdmin\DatabaseInterface
;
8 use PhpMyAdmin\Html\Generator
;
9 use PhpMyAdmin\Relation
;
10 use PhpMyAdmin\Response
;
12 use PhpMyAdmin\Table\Search
;
13 use PhpMyAdmin\Template
;
15 use function in_array
;
17 use function mb_strtolower
;
19 use function preg_match
;
20 use function preg_replace
;
21 use function str_ireplace
;
22 use function str_replace
;
23 use function strncasecmp
;
26 * Handles table search tab.
28 * Display table search form, create SQL query from form data
29 * and call Sql::executeQueryAndSendQueryResponse() to execute it.
31 class SearchController
extends AbstractController
39 private $_columnNames;
46 private $_columnTypes;
48 * Types of columns without any replacement
53 private $_originalColumnTypes;
55 * Collations of columns
60 private $_columnCollations;
62 * Null Flags of columns
67 private $_columnNullFlags;
69 * Whether a geometry column is present
74 private $_geomColumnFlag;
90 * @param Response $response Response object
91 * @param DatabaseInterface $dbi DatabaseInterface object
92 * @param Template $template Template object
93 * @param string $db Database name
94 * @param string $table Table name
95 * @param Search $search A Search instance.
96 * @param Relation $relation Relation instance
98 public function __construct(
107 parent
::__construct($response, $dbi, $template, $db, $table);
108 $this->search
= $search;
109 $this->relation
= $relation;
110 $this->_columnNames
= [];
111 $this->_columnNullFlags
= [];
112 $this->_columnTypes
= [];
113 $this->_originalColumnTypes
= [];
114 $this->_columnCollations
= [];
115 $this->_geomColumnFlag
= false;
116 $this->_foreigners
= [];
117 $this->_loadTableInfo();
121 * Gets all the columns of a table along with their types, collations
122 * and whether null or not.
126 private function _loadTableInfo()
128 // Gets the list and number of columns
129 $columns = $this->dbi
->getColumns(
135 // Get details about the geometry functions
136 $geom_types = Util
::getGISDatatypes();
138 foreach ($columns as $row) {
140 $this->_columnNames
[] = $row['Field'];
142 $type = $row['Type'];
143 // before any replacement
144 $this->_originalColumnTypes
[] = mb_strtolower($type);
145 // check whether table contains geometric columns
146 if (in_array($type, $geom_types)) {
147 $this->_geomColumnFlag
= true;
149 // reformat mysql query output
150 if (strncasecmp($type, 'set', 3) == 0
151 ||
strncasecmp($type, 'enum', 4) == 0
153 $type = str_replace(',', ', ', $type);
155 // strip the "BINARY" attribute, except if we find "BINARY(" because
156 // this would be a BINARY or VARBINARY column type
157 if (! preg_match('@BINARY[\(]@i', $type)) {
158 $type = str_ireplace('BINARY', '', $type);
160 $type = str_ireplace('ZEROFILL', '', $type);
161 $type = str_ireplace('UNSIGNED', '', $type);
162 $type = mb_strtolower($type);
167 $this->_columnTypes
[] = $type;
168 $this->_columnNullFlags
[] = $row['Null'];
169 $this->_columnCollations
[]
170 = ! empty($row['Collation']) && $row['Collation'] != 'NULL'
175 // Retrieve foreign keys
176 $this->_foreigners
= $this->relation
->getForeigners($this->db
, $this->table
);
182 public function index(): void
186 $header = $this->response
->getHeader();
187 $scripts = $header->getScripts();
193 'vendor/jquery/jquery.uitablefilter.js',
194 'gis_data_editor.js',
197 if (isset($_POST['range_search'])) {
198 $this->rangeSearchAction();
203 * No selection criteria received -> display the selection form
205 if (! isset($_POST['columnsToDisplay'])
206 && ! isset($_POST['displayAllColumns'])
208 $this->displaySelectionFormAction();
210 $this->doSelectionAction();
215 * Get data row action
219 public function getDataRowAction()
222 $row_info_query = 'SELECT * FROM ' . Util
::backquote($_POST['db']) . '.'
223 . Util
::backquote($_POST['table']) . ' WHERE ' . $_POST['where_clause'];
224 $result = $this->dbi
->query(
225 $row_info_query . ';',
226 DatabaseInterface
::CONNECT_USER
,
227 DatabaseInterface
::QUERY_STORE
229 $fields_meta = $this->dbi
->getFieldsMeta($result);
230 while ($row = $this->dbi
->fetchAssoc($result)) {
231 // for bit fields we need to convert them to printable form
233 foreach ($row as $col => $val) {
234 if ($fields_meta[$i]->type
== 'bit') {
235 $row[$col] = Util
::printableBitValue(
237 (int) $fields_meta[$i]->length
242 $extra_data['row_info'] = $row;
244 $this->response
->addJSON($extra_data);
248 * Do selection action
252 public function doSelectionAction()
255 * Selection criteria have been submitted -> do the work
257 $sql_query = $this->search
->buildSqlQuery();
260 * Add this to ensure following procedures included running correctly.
263 $sql->executeQueryAndSendQueryResponse(
264 null, // analyzed_sql_results
265 false, // is_gotofile
267 $this->table
, // table
268 null, // find_real_end
269 null, // sql_query_for_bookmark
271 null, // message_to_show
274 $GLOBALS['goto'], // goto
275 $GLOBALS['pmaThemeImage'], // pmaThemeImage
277 null, // disp_message
279 $sql_query, // sql_query
280 null, // selectedTables
281 null // complete_query
286 * Display selection form action
288 public function displaySelectionFormAction(): void
292 if (! isset($goto)) {
293 $goto = Util
::getScriptNameForOption(
294 $cfg['DefaultTabTable'],
299 $this->render('table/search/index', [
301 'table' => $this->table
,
304 'geom_column_flag' => $this->_geomColumnFlag
,
305 'column_names' => $this->_columnNames
,
306 'column_types' => $this->_columnTypes
,
307 'column_collations' => $this->_columnCollations
,
308 'default_sliders_state' => $cfg['InitialSlidersState'],
309 'max_rows' => intval($cfg['MaxRows']),
314 * Range search action
318 public function rangeSearchAction()
320 $min_max = $this->getColumnMinMax($_POST['column']);
321 $this->response
->addJSON('column_data', $min_max);
325 * Finds minimum and maximum value of a given column.
327 * @param string $column Column name
331 public function getColumnMinMax($column)
333 $sql_query = 'SELECT MIN(' . Util
::backquote($column) . ') AS `min`, '
334 . 'MAX(' . Util
::backquote($column) . ') AS `max` '
335 . 'FROM ' . Util
::backquote($this->db
) . '.'
336 . Util
::backquote($this->table
);
338 return $this->dbi
->fetchSingleRow($sql_query);
342 * Provides a column's type, collation, operators list, and criteria value
343 * to display in table search form
345 * @param int $search_index Row number in table search form
346 * @param int $column_index Column index in ColumnNames array
348 * @return array Array containing column's properties
350 public function getColumnProperties($search_index, $column_index)
352 $selected_operator = ($_POST['criteriaColumnOperators'][$search_index] ??
'');
353 $entered_value = ($_POST['criteriaValues'] ??
'');
355 'Browse' => Generator
::getIcon(
357 __('Browse foreign values')
360 //Gets column's type and collation
361 $type = $this->_columnTypes
[$column_index];
362 $collation = $this->_columnCollations
[$column_index];
363 $cleanType = preg_replace('@\(.*@s', '', $type);
364 //Gets column's comparison operators depending on column type
365 $typeOperators = $this->dbi
->types
->getTypeOperatorsHtml(
367 $this->_columnNullFlags
[$column_index],
370 $func = $this->template
->render('table/search/column_comparison_operators', [
371 'search_index' => $search_index,
372 'type_operators' => $typeOperators,
374 //Gets link to browse foreign data(if any) and criteria inputbox
375 $foreignData = $this->relation
->getForeignData(
377 $this->_columnNames
[$column_index],
382 $htmlAttributes = '';
383 if (in_array($cleanType, $this->dbi
->types
->getIntegerTypes())) {
384 $extractedColumnspec = Util
::extractColumnSpec(
385 $this->_originalColumnTypes
[$column_index]
387 $is_unsigned = $extractedColumnspec['unsigned'];
388 $minMaxValues = $this->dbi
->types
->getIntegerRange(
392 $htmlAttributes = 'min="' . $minMaxValues[0] . '" '
393 . 'max="' . $minMaxValues[1] . '"';
397 $htmlAttributes .= " onchange= 'return verifyAfterSearchFieldChange(" . $column_index . ")'";
399 $value = $this->template
->render('table/search/input_box', [
401 'column_type' => (string) $type,
402 'html_attributes' => $htmlAttributes,
403 'column_id' => 'fieldID_',
404 'in_zoom_search_edit' => false,
405 'foreigners' => $this->_foreigners
,
406 'column_name' => $this->_columnNames
[$column_index],
407 'column_name_hash' => md5($this->_columnNames
[$column_index]),
408 'foreign_data' => $foreignData,
409 'table' => $this->table
,
410 'column_index' => $search_index,
411 'foreign_max_limit' => $GLOBALS['cfg']['ForeignKeyMaxLimit'],
412 'criteria_values' => $entered_value,
419 'collation' => $collation,