ZoomSearchController.php 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475
  1. <?php
  2. declare(strict_types=1);
  3. namespace PhpMyAdmin\Controllers\Table;
  4. use PhpMyAdmin\ConfigStorage\Relation;
  5. use PhpMyAdmin\Core;
  6. use PhpMyAdmin\DatabaseInterface;
  7. use PhpMyAdmin\DbTableExists;
  8. use PhpMyAdmin\ResponseRenderer;
  9. use PhpMyAdmin\Table\Search;
  10. use PhpMyAdmin\Template;
  11. use PhpMyAdmin\Url;
  12. use PhpMyAdmin\Util;
  13. use PhpMyAdmin\Utils\Gis;
  14. use function array_map;
  15. use function array_search;
  16. use function array_values;
  17. use function count;
  18. use function htmlspecialchars;
  19. use function in_array;
  20. use function intval;
  21. use function is_numeric;
  22. use function json_encode;
  23. use function mb_strtolower;
  24. use function md5;
  25. use function preg_match;
  26. use function preg_replace;
  27. use function str_ireplace;
  28. use function str_replace;
  29. use function strncasecmp;
  30. use function strtoupper;
  31. /**
  32. * Handles table zoom search tab.
  33. *
  34. * Display table zoom search form, create SQL queries from form data.
  35. */
  36. class ZoomSearchController extends AbstractController
  37. {
  38. /** @var Search */
  39. private $search;
  40. /** @var Relation */
  41. private $relation;
  42. /** @var array */
  43. private $columnNames;
  44. /** @var array */
  45. private $columnTypes;
  46. /** @var array */
  47. private $originalColumnTypes;
  48. /** @var array */
  49. private $columnCollations;
  50. /** @var array */
  51. private $columnNullFlags;
  52. /** @var bool Whether a geometry column is present */
  53. private $geomColumnFlag;
  54. /** @var array Foreign keys */
  55. private $foreigners;
  56. /** @var DatabaseInterface */
  57. private $dbi;
  58. public function __construct(
  59. ResponseRenderer $response,
  60. Template $template,
  61. string $db,
  62. string $table,
  63. Search $search,
  64. Relation $relation,
  65. DatabaseInterface $dbi
  66. ) {
  67. parent::__construct($response, $template, $db, $table);
  68. $this->search = $search;
  69. $this->relation = $relation;
  70. $this->dbi = $dbi;
  71. $this->columnNames = [];
  72. $this->columnTypes = [];
  73. $this->originalColumnTypes = [];
  74. $this->columnCollations = [];
  75. $this->columnNullFlags = [];
  76. $this->geomColumnFlag = false;
  77. $this->foreigners = [];
  78. $this->loadTableInfo();
  79. }
  80. public function __invoke(): void
  81. {
  82. global $goto, $db, $table, $urlParams, $cfg, $errorUrl;
  83. Util::checkParameters(['db', 'table']);
  84. $urlParams = ['db' => $db, 'table' => $table];
  85. $errorUrl = Util::getScriptNameForOption($cfg['DefaultTabTable'], 'table');
  86. $errorUrl .= Url::getCommon($urlParams, '&');
  87. DbTableExists::check();
  88. $this->addScriptFiles([
  89. 'makegrid.js',
  90. 'sql.js',
  91. 'vendor/jqplot/jquery.jqplot.js',
  92. 'vendor/jqplot/plugins/jqplot.canvasTextRenderer.js',
  93. 'vendor/jqplot/plugins/jqplot.canvasAxisLabelRenderer.js',
  94. 'vendor/jqplot/plugins/jqplot.dateAxisRenderer.js',
  95. 'vendor/jqplot/plugins/jqplot.highlighter.js',
  96. 'vendor/jqplot/plugins/jqplot.cursor.js',
  97. 'table/zoom_plot_jqplot.js',
  98. 'table/select.js',
  99. 'table/change.js',
  100. 'gis_data_editor.js',
  101. ]);
  102. /**
  103. * Handle AJAX request for data row on point select
  104. */
  105. if (isset($_POST['get_data_row']) && $_POST['get_data_row'] == true) {
  106. $this->getDataRowAction();
  107. return;
  108. }
  109. /**
  110. * Handle AJAX request for changing field information
  111. * (value,collation,operators,field values) in input form
  112. */
  113. if (isset($_POST['change_tbl_info']) && $_POST['change_tbl_info'] == true) {
  114. $this->changeTableInfoAction();
  115. return;
  116. }
  117. //Set default datalabel if not selected
  118. if (! isset($_POST['zoom_submit']) || $_POST['dataLabel'] == '') {
  119. $dataLabel = $this->relation->getDisplayField($this->db, $this->table);
  120. } else {
  121. $dataLabel = $_POST['dataLabel'];
  122. }
  123. // Displays the zoom search form
  124. $this->displaySelectionFormAction($dataLabel);
  125. /**
  126. * Handle the input criteria and generate the query result
  127. * Form for displaying query results
  128. */
  129. if (
  130. ! isset($_POST['zoom_submit'])
  131. || $_POST['criteriaColumnNames'][0] === 'pma_null'
  132. || $_POST['criteriaColumnNames'][1] === 'pma_null'
  133. || $_POST['criteriaColumnNames'][0] == $_POST['criteriaColumnNames'][1]
  134. ) {
  135. return;
  136. }
  137. if (! isset($goto)) {
  138. $goto = Util::getScriptNameForOption($GLOBALS['cfg']['DefaultTabTable'], 'table');
  139. }
  140. $this->zoomSubmitAction($dataLabel, $goto);
  141. }
  142. /**
  143. * Gets all the columns of a table along with their types, collations
  144. * and whether null or not.
  145. */
  146. private function loadTableInfo(): void
  147. {
  148. // Gets the list and number of columns
  149. $columns = $this->dbi->getColumns($this->db, $this->table, true);
  150. // Get details about the geometry functions
  151. $geom_types = Gis::getDataTypes();
  152. foreach ($columns as $row) {
  153. // set column name
  154. $this->columnNames[] = $row['Field'];
  155. $type = (string) $row['Type'];
  156. // before any replacement
  157. $this->originalColumnTypes[] = mb_strtolower($type);
  158. // check whether table contains geometric columns
  159. if (in_array($type, $geom_types)) {
  160. $this->geomColumnFlag = true;
  161. }
  162. // reformat mysql query output
  163. if (strncasecmp($type, 'set', 3) == 0 || strncasecmp($type, 'enum', 4) == 0) {
  164. $type = str_replace(',', ', ', $type);
  165. } else {
  166. // strip the "BINARY" attribute, except if we find "BINARY(" because
  167. // this would be a BINARY or VARBINARY column type
  168. if (! preg_match('@BINARY[\(]@i', $type)) {
  169. $type = str_ireplace('BINARY', '', $type);
  170. }
  171. $type = str_ireplace('ZEROFILL', '', $type);
  172. $type = str_ireplace('UNSIGNED', '', $type);
  173. $type = mb_strtolower($type);
  174. }
  175. if (empty($type)) {
  176. $type = '&nbsp;';
  177. }
  178. $this->columnTypes[] = $type;
  179. $this->columnNullFlags[] = $row['Null'];
  180. $this->columnCollations[] = ! empty($row['Collation']) && $row['Collation'] !== 'NULL'
  181. ? $row['Collation']
  182. : '';
  183. }
  184. // Retrieve foreign keys
  185. $this->foreigners = $this->relation->getForeigners($this->db, $this->table);
  186. }
  187. /**
  188. * Display selection form action
  189. *
  190. * @param string $dataLabel Data label
  191. */
  192. public function displaySelectionFormAction($dataLabel = null): void
  193. {
  194. global $goto;
  195. if (! isset($goto)) {
  196. $goto = Util::getScriptNameForOption($GLOBALS['cfg']['DefaultTabTable'], 'table');
  197. }
  198. $column_names = $this->columnNames;
  199. $criteria_column_names = $_POST['criteriaColumnNames'] ?? null;
  200. $keys = [];
  201. for ($i = 0; $i < 4; $i++) {
  202. if (! isset($criteria_column_names[$i])) {
  203. continue;
  204. }
  205. if ($criteria_column_names[$i] === 'pma_null') {
  206. continue;
  207. }
  208. $keys[$criteria_column_names[$i]] = array_search($criteria_column_names[$i], $column_names);
  209. }
  210. $this->render('table/zoom_search/index', [
  211. 'db' => $this->db,
  212. 'table' => $this->table,
  213. 'goto' => $goto,
  214. 'self' => $this,
  215. 'geom_column_flag' => $this->geomColumnFlag,
  216. 'column_names' => $column_names,
  217. 'data_label' => $dataLabel,
  218. 'keys' => $keys,
  219. 'criteria_column_names' => $criteria_column_names,
  220. 'criteria_column_types' => $_POST['criteriaColumnTypes'] ?? null,
  221. 'max_plot_limit' => ! empty($_POST['maxPlotLimit'])
  222. ? intval($_POST['maxPlotLimit'])
  223. : intval($GLOBALS['cfg']['maxRowPlotLimit']),
  224. ]);
  225. }
  226. /**
  227. * Get data row action
  228. */
  229. public function getDataRowAction(): void
  230. {
  231. if (! Core::checkSqlQuerySignature($_POST['where_clause'], $_POST['where_clause_sign'])) {
  232. return;
  233. }
  234. $extra_data = [];
  235. $row_info_query = 'SELECT * FROM ' . Util::backquote($_POST['db']) . '.'
  236. . Util::backquote($_POST['table']) . ' WHERE ' . $_POST['where_clause'];
  237. $result = $this->dbi->query($row_info_query . ';');
  238. $fields_meta = $this->dbi->getFieldsMeta($result);
  239. while ($row = $result->fetchAssoc()) {
  240. // for bit fields we need to convert them to printable form
  241. $i = 0;
  242. foreach ($row as $col => $val) {
  243. if ($fields_meta[$i]->isMappedTypeBit) {
  244. $row[$col] = Util::printableBitValue((int) $val, (int) $fields_meta[$i]->length);
  245. }
  246. $i++;
  247. }
  248. $extra_data['row_info'] = $row;
  249. }
  250. $this->response->addJSON($extra_data);
  251. }
  252. /**
  253. * Change table info action
  254. */
  255. public function changeTableInfoAction(): void
  256. {
  257. $field = $_POST['field'];
  258. if ($field === 'pma_null') {
  259. $this->response->addJSON('field_type', '');
  260. $this->response->addJSON('field_collation', '');
  261. $this->response->addJSON('field_operators', '');
  262. $this->response->addJSON('field_value', '');
  263. return;
  264. }
  265. $key = array_search($field, $this->columnNames);
  266. $search_index = (isset($_POST['it']) && is_numeric($_POST['it'])
  267. ? intval($_POST['it']) : 0);
  268. $properties = $this->getColumnProperties($search_index, $key);
  269. $this->response->addJSON(
  270. 'field_type',
  271. htmlspecialchars($properties['type'])
  272. );
  273. $this->response->addJSON('field_collation', $properties['collation']);
  274. $this->response->addJSON('field_operators', $properties['func']);
  275. $this->response->addJSON('field_value', $properties['value']);
  276. }
  277. /**
  278. * Zoom submit action
  279. *
  280. * @param string $dataLabel Data label
  281. * @param string $goto Goto
  282. */
  283. public function zoomSubmitAction($dataLabel, $goto): void
  284. {
  285. //Query generation part
  286. $sql_query = $this->search->buildSqlQuery();
  287. $sql_query .= ' LIMIT ' . $_POST['maxPlotLimit'];
  288. //Query execution part
  289. $result = $this->dbi->query($sql_query . ';');
  290. $fields_meta = $this->dbi->getFieldsMeta($result);
  291. $data = [];
  292. while ($row = $result->fetchAssoc()) {
  293. //Need a row with indexes as 0,1,2 for the getUniqueCondition
  294. // hence using a temporary array
  295. $tmpRow = array_values($row);
  296. //Get unique condition on each row (will be needed for row update)
  297. $uniqueCondition = Util::getUniqueCondition(
  298. count($this->columnNames),
  299. $fields_meta,
  300. $tmpRow,
  301. true
  302. );
  303. //Append it to row array as where_clause
  304. $row['where_clause'] = $uniqueCondition[0];
  305. $row['where_clause_sign'] = Core::signSqlQuery($uniqueCondition[0]);
  306. $tmpData = [
  307. $_POST['criteriaColumnNames'][0] => $row[$_POST['criteriaColumnNames'][0]],
  308. $_POST['criteriaColumnNames'][1] => $row[$_POST['criteriaColumnNames'][1]],
  309. 'where_clause' => $uniqueCondition[0],
  310. 'where_clause_sign' => Core::signSqlQuery($uniqueCondition[0]),
  311. ];
  312. $tmpData[$dataLabel] = $dataLabel ? $row[$dataLabel] : '';
  313. $data[] = $tmpData;
  314. }
  315. unset($tmpData);
  316. $column_names_hashes = [];
  317. foreach ($this->columnNames as $columnName) {
  318. $column_names_hashes[$columnName] = md5($columnName);
  319. }
  320. $columnDataTypes = array_map(static function (string $type): string {
  321. return strtoupper((string) preg_replace('@\(.*@s', '', $type));
  322. }, $this->columnTypes);
  323. $this->render('table/zoom_search/result_form', [
  324. 'db' => $this->db,
  325. 'table' => $this->table,
  326. 'column_names' => $this->columnNames,
  327. 'column_names_hashes' => $column_names_hashes,
  328. 'foreigners' => $this->foreigners,
  329. 'column_null_flags' => $this->columnNullFlags,
  330. 'column_types' => $this->columnTypes,
  331. 'column_data_types' => $columnDataTypes,
  332. 'goto' => $goto,
  333. 'data' => $data,
  334. 'data_json' => json_encode($data),
  335. 'zoom_submit' => isset($_POST['zoom_submit']),
  336. 'foreign_max_limit' => $GLOBALS['cfg']['ForeignKeyMaxLimit'],
  337. ]);
  338. }
  339. /**
  340. * Provides a column's type, collation, operators list, and criteria value
  341. * to display in table search form
  342. *
  343. * @param int $search_index Row number in table search form
  344. * @param int $column_index Column index in ColumnNames array
  345. *
  346. * @return array Array containing column's properties
  347. */
  348. public function getColumnProperties($search_index, $column_index)
  349. {
  350. $selected_operator = ($_POST['criteriaColumnOperators'][$search_index] ?? '');
  351. $entered_value = ($_POST['criteriaValues'] ?? '');
  352. //Gets column's type and collation
  353. $type = $this->columnTypes[$column_index];
  354. $collation = $this->columnCollations[$column_index];
  355. $cleanType = preg_replace('@\(.*@s', '', $type);
  356. //Gets column's comparison operators depending on column type
  357. $typeOperators = $this->dbi->types->getTypeOperatorsHtml(
  358. $cleanType,
  359. $this->columnNullFlags[$column_index],
  360. $selected_operator
  361. );
  362. $func = $this->template->render('table/search/column_comparison_operators', [
  363. 'search_index' => $search_index,
  364. 'type_operators' => $typeOperators,
  365. ]);
  366. //Gets link to browse foreign data(if any) and criteria inputbox
  367. $foreignData = $this->relation->getForeignData(
  368. $this->foreigners,
  369. $this->columnNames[$column_index],
  370. false,
  371. '',
  372. ''
  373. );
  374. $htmlAttributes = '';
  375. if (in_array($cleanType, $this->dbi->types->getIntegerTypes())) {
  376. $extractedColumnspec = Util::extractColumnSpec($this->originalColumnTypes[$column_index]);
  377. $is_unsigned = $extractedColumnspec['unsigned'];
  378. $minMaxValues = $this->dbi->types->getIntegerRange($cleanType, ! $is_unsigned);
  379. $htmlAttributes = 'data-min="' . $minMaxValues[0] . '" '
  380. . 'data-max="' . $minMaxValues[1] . '"';
  381. }
  382. $htmlAttributes .= ' onfocus="return '
  383. . 'verifyAfterSearchFieldChange(' . $search_index . ', \'#zoom_search_form\')"';
  384. $value = $this->template->render('table/search/input_box', [
  385. 'str' => '',
  386. 'column_type' => (string) $type,
  387. 'column_data_type' => strtoupper($cleanType),
  388. 'html_attributes' => $htmlAttributes,
  389. 'column_id' => 'fieldID_',
  390. 'in_zoom_search_edit' => false,
  391. 'foreigners' => $this->foreigners,
  392. 'column_name' => $this->columnNames[$column_index],
  393. 'column_name_hash' => md5($this->columnNames[$column_index]),
  394. 'foreign_data' => $foreignData,
  395. 'table' => $this->table,
  396. 'column_index' => $search_index,
  397. 'foreign_max_limit' => $GLOBALS['cfg']['ForeignKeyMaxLimit'],
  398. 'criteria_values' => $entered_value,
  399. 'db' => $this->db,
  400. 'in_fbs' => true,
  401. ]);
  402. return [
  403. 'type' => $type,
  404. 'collation' => $collation,
  405. 'func' => $func,
  406. 'value' => $value,
  407. ];
  408. }
  409. }