1 | <?php |
||||
2 | /* vim: set expandtab sw=4 ts=4 sts=4: */ |
||||
3 | /** |
||||
4 | * Holds the PhpMyAdmin\Controllers\Table\TableSearchController |
||||
5 | * |
||||
6 | * @package PhpMyAdmin\Controllers |
||||
7 | */ |
||||
8 | declare(strict_types=1); |
||||
9 | |||||
10 | namespace PhpMyAdmin\Controllers\Table; |
||||
11 | |||||
12 | use PhpMyAdmin\Controllers\TableController; |
||||
13 | use PhpMyAdmin\DatabaseInterface; |
||||
14 | use PhpMyAdmin\Relation; |
||||
15 | use PhpMyAdmin\Sql; |
||||
16 | use PhpMyAdmin\Template; |
||||
17 | use PhpMyAdmin\Util; |
||||
18 | |||||
19 | /** |
||||
20 | * Class TableSearchController |
||||
21 | * |
||||
22 | * @package PhpMyAdmin\Controllers |
||||
23 | */ |
||||
24 | class TableSearchController extends TableController |
||||
25 | { |
||||
26 | /** |
||||
27 | * Normal search or Zoom search |
||||
28 | * |
||||
29 | * @access private |
||||
30 | * @var string |
||||
31 | */ |
||||
32 | private $_searchType; |
||||
33 | /** |
||||
34 | * Names of columns |
||||
35 | * |
||||
36 | * @access private |
||||
37 | * @var array |
||||
38 | */ |
||||
39 | private $_columnNames; |
||||
40 | /** |
||||
41 | * Types of columns |
||||
42 | * |
||||
43 | * @access private |
||||
44 | * @var array |
||||
45 | */ |
||||
46 | private $_columnTypes; |
||||
47 | /** |
||||
48 | * Collations of columns |
||||
49 | * |
||||
50 | * @access private |
||||
51 | * @var array |
||||
52 | */ |
||||
53 | private $_columnCollations; |
||||
54 | /** |
||||
55 | * Null Flags of columns |
||||
56 | * |
||||
57 | * @access private |
||||
58 | * @var array |
||||
59 | */ |
||||
60 | private $_columnNullFlags; |
||||
61 | /** |
||||
62 | * Whether a geometry column is present |
||||
63 | * |
||||
64 | * @access private |
||||
65 | * @var boolean |
||||
66 | */ |
||||
67 | private $_geomColumnFlag; |
||||
68 | /** |
||||
69 | * Foreign Keys |
||||
70 | * |
||||
71 | * @access private |
||||
72 | * @var array |
||||
73 | */ |
||||
74 | private $_foreigners; |
||||
75 | /** |
||||
76 | * Connection charset |
||||
77 | * |
||||
78 | * @access private |
||||
79 | * @var string |
||||
80 | */ |
||||
81 | private $_connectionCharSet; |
||||
82 | |||||
83 | protected $url_query; |
||||
84 | |||||
85 | /** |
||||
86 | * @var Relation |
||||
87 | */ |
||||
88 | private $relation; |
||||
89 | |||||
90 | /** |
||||
91 | * Constructor |
||||
92 | * |
||||
93 | * @param \PhpMyAdmin\Response $response Response object |
||||
94 | * @param DatabaseInterface $dbi DatabaseInterface object |
||||
95 | * @param string $db Database name |
||||
96 | * @param string $table Table name |
||||
97 | * @param string $searchType Search type |
||||
98 | * @param string $url_query URL query |
||||
99 | */ |
||||
100 | public function __construct( |
||||
101 | $response, |
||||
102 | $dbi, |
||||
103 | $db, |
||||
104 | $table, |
||||
105 | $searchType, |
||||
106 | $url_query |
||||
107 | ) { |
||||
108 | parent::__construct($response, $dbi, $db, $table); |
||||
109 | |||||
110 | $this->url_query = $url_query; |
||||
111 | $this->_searchType = $searchType; |
||||
112 | $this->_columnNames = []; |
||||
113 | $this->_columnNullFlags = []; |
||||
114 | $this->_columnTypes = []; |
||||
115 | $this->_columnCollations = []; |
||||
116 | $this->_geomColumnFlag = false; |
||||
117 | $this->_foreigners = []; |
||||
118 | $this->relation = new Relation($dbi); |
||||
119 | // Loads table's information |
||||
120 | $this->_loadTableInfo(); |
||||
121 | $this->_connectionCharSet = $this->dbi->fetchValue( |
||||
122 | "SELECT @@character_set_connection" |
||||
123 | ); |
||||
124 | } |
||||
125 | |||||
126 | /** |
||||
127 | * Gets all the columns of a table along with their types, collations |
||||
128 | * and whether null or not. |
||||
129 | * |
||||
130 | * @return void |
||||
131 | */ |
||||
132 | private function _loadTableInfo() |
||||
133 | { |
||||
134 | // Gets the list and number of columns |
||||
135 | $columns = $this->dbi->getColumns( |
||||
136 | $this->db, |
||||
137 | $this->table, |
||||
138 | null, |
||||
139 | true |
||||
140 | ); |
||||
141 | // Get details about the geometry functions |
||||
142 | $geom_types = Util::getGISDatatypes(); |
||||
143 | |||||
144 | foreach ($columns as $row) { |
||||
145 | // set column name |
||||
146 | $this->_columnNames[] = $row['Field']; |
||||
147 | |||||
148 | $type = $row['Type']; |
||||
149 | // check whether table contains geometric columns |
||||
150 | if (in_array($type, $geom_types)) { |
||||
151 | $this->_geomColumnFlag = true; |
||||
152 | } |
||||
153 | // reformat mysql query output |
||||
154 | if (strncasecmp($type, 'set', 3) == 0 |
||||
155 | || strncasecmp($type, 'enum', 4) == 0 |
||||
156 | ) { |
||||
157 | $type = str_replace(',', ', ', $type); |
||||
158 | } else { |
||||
159 | // strip the "BINARY" attribute, except if we find "BINARY(" because |
||||
160 | // this would be a BINARY or VARBINARY column type |
||||
161 | if (! preg_match('@BINARY[\(]@i', $type)) { |
||||
162 | $type = preg_replace('@BINARY@i', '', $type); |
||||
163 | } |
||||
164 | $type = preg_replace('@ZEROFILL@i', '', $type); |
||||
165 | $type = preg_replace('@UNSIGNED@i', '', $type); |
||||
166 | $type = mb_strtolower($type); |
||||
167 | } |
||||
168 | if (empty($type)) { |
||||
169 | $type = ' '; |
||||
170 | } |
||||
171 | $this->_columnTypes[] = $type; |
||||
172 | $this->_columnNullFlags[] = $row['Null']; |
||||
173 | $this->_columnCollations[] |
||||
174 | = ! empty($row['Collation']) && $row['Collation'] != 'NULL' |
||||
175 | ? $row['Collation'] |
||||
176 | : ''; |
||||
177 | } // end for |
||||
178 | |||||
179 | // Retrieve foreign keys |
||||
180 | $this->_foreigners = $this->relation->getForeigners($this->db, $this->table); |
||||
181 | } |
||||
182 | |||||
183 | /** |
||||
184 | * Index action |
||||
185 | * |
||||
186 | * @return void |
||||
187 | */ |
||||
188 | public function indexAction() |
||||
189 | { |
||||
190 | global $goto; |
||||
191 | switch ($this->_searchType) { |
||||
192 | case 'replace': |
||||
193 | if (isset($_POST['find'])) { |
||||
194 | $this->findAction(); |
||||
195 | |||||
196 | return; |
||||
197 | } |
||||
198 | $this->response |
||||
199 | ->getHeader() |
||||
200 | ->getScripts() |
||||
201 | ->addFile('tbl_find_replace.js'); |
||||
202 | |||||
203 | if (isset($_POST['replace'])) { |
||||
204 | $this->replaceAction(); |
||||
205 | } |
||||
206 | |||||
207 | // Displays the find and replace form |
||||
208 | $this->displaySelectionFormAction(); |
||||
209 | break; |
||||
210 | |||||
211 | case 'normal': |
||||
212 | $this->response->getHeader() |
||||
213 | ->getScripts() |
||||
214 | ->addFiles( |
||||
215 | [ |
||||
216 | 'makegrid.js', |
||||
217 | 'sql.js', |
||||
218 | 'tbl_select.js', |
||||
219 | 'tbl_change.js', |
||||
220 | 'vendor/jquery/jquery.uitablefilter.js', |
||||
221 | 'gis_data_editor.js', |
||||
222 | ] |
||||
223 | ); |
||||
224 | |||||
225 | if (isset($_REQUEST['range_search'])) { |
||||
226 | $this->rangeSearchAction(); |
||||
227 | |||||
228 | return; |
||||
229 | } |
||||
230 | |||||
231 | /** |
||||
232 | * No selection criteria received -> display the selection form |
||||
233 | */ |
||||
234 | if (!isset($_POST['columnsToDisplay']) |
||||
235 | && !isset($_POST['displayAllColumns']) |
||||
236 | ) { |
||||
237 | $this->displaySelectionFormAction(); |
||||
238 | } else { |
||||
239 | $this->doSelectionAction(); |
||||
240 | } |
||||
241 | break; |
||||
242 | |||||
243 | case 'zoom': |
||||
244 | $this->response->getHeader() |
||||
245 | ->getScripts() |
||||
246 | ->addFiles( |
||||
247 | [ |
||||
248 | 'makegrid.js', |
||||
249 | 'sql.js', |
||||
250 | 'vendor/jqplot/jquery.jqplot.js', |
||||
251 | 'vendor/jqplot/plugins/jqplot.canvasTextRenderer.js', |
||||
252 | 'vendor/jqplot/plugins/jqplot.canvasAxisLabelRenderer.js', |
||||
253 | 'vendor/jqplot/plugins/jqplot.dateAxisRenderer.js', |
||||
254 | 'vendor/jqplot/plugins/jqplot.highlighter.js', |
||||
255 | 'vendor/jqplot/plugins/jqplot.cursor.js', |
||||
256 | 'tbl_zoom_plot_jqplot.js', |
||||
257 | 'tbl_change.js', |
||||
258 | ] |
||||
259 | ); |
||||
260 | |||||
261 | /** |
||||
262 | * Handle AJAX request for data row on point select |
||||
263 | * |
||||
264 | * @var boolean Object containing parameters for the POST request |
||||
265 | */ |
||||
266 | if (isset($_REQUEST['get_data_row']) |
||||
267 | && $_REQUEST['get_data_row'] == true |
||||
268 | ) { |
||||
269 | $this->getDataRowAction(); |
||||
270 | |||||
271 | return; |
||||
272 | } |
||||
273 | /** |
||||
274 | * Handle AJAX request for changing field information |
||||
275 | * (value,collation,operators,field values) in input form |
||||
276 | * |
||||
277 | * @var boolean Object containing parameters for the POST request |
||||
278 | */ |
||||
279 | if (isset($_REQUEST['change_tbl_info']) |
||||
280 | && $_REQUEST['change_tbl_info'] == true |
||||
281 | ) { |
||||
282 | $this->changeTableInfoAction(); |
||||
283 | |||||
284 | return; |
||||
285 | } |
||||
286 | |||||
287 | //Set default datalabel if not selected |
||||
288 | if (!isset($_POST['zoom_submit']) || $_POST['dataLabel'] == '') { |
||||
289 | $dataLabel = $this->relation->getDisplayField($this->db, $this->table); |
||||
290 | } else { |
||||
291 | $dataLabel = $_POST['dataLabel']; |
||||
292 | } |
||||
293 | |||||
294 | // Displays the zoom search form |
||||
295 | $this->displaySelectionFormAction($dataLabel); |
||||
296 | |||||
297 | /* |
||||
298 | * Handle the input criteria and generate the query result |
||||
299 | * Form for displaying query results |
||||
300 | */ |
||||
301 | if (isset($_POST['zoom_submit']) |
||||
302 | && $_POST['criteriaColumnNames'][0] != 'pma_null' |
||||
303 | && $_POST['criteriaColumnNames'][1] != 'pma_null' |
||||
304 | && $_POST['criteriaColumnNames'][0] != $_POST['criteriaColumnNames'][1] |
||||
305 | ) { |
||||
306 | if (! isset($goto)) { |
||||
307 | $goto = Util::getScriptNameForOption( |
||||
308 | $GLOBALS['cfg']['DefaultTabTable'], |
||||
309 | 'table' |
||||
310 | ); |
||||
311 | } |
||||
312 | $this->zoomSubmitAction($dataLabel, $goto); |
||||
313 | } |
||||
314 | break; |
||||
315 | } |
||||
316 | } |
||||
317 | |||||
318 | /** |
||||
319 | * Zoom submit action |
||||
320 | * |
||||
321 | * @param string $dataLabel Data label |
||||
322 | * @param string $goto Goto |
||||
323 | * |
||||
324 | * @return void |
||||
325 | */ |
||||
326 | public function zoomSubmitAction($dataLabel, $goto) |
||||
327 | { |
||||
328 | //Query generation part |
||||
329 | $sql_query = $this->_buildSqlQuery(); |
||||
330 | $sql_query .= ' LIMIT ' . $_POST['maxPlotLimit']; |
||||
331 | |||||
332 | //Query execution part |
||||
333 | $result = $this->dbi->query( |
||||
334 | $sql_query . ";", |
||||
335 | DatabaseInterface::CONNECT_USER, |
||||
336 | DatabaseInterface::QUERY_STORE |
||||
337 | ); |
||||
338 | $fields_meta = $this->dbi->getFieldsMeta($result); |
||||
339 | $data = []; |
||||
340 | while ($row = $this->dbi->fetchAssoc($result)) { |
||||
341 | //Need a row with indexes as 0,1,2 for the getUniqueCondition |
||||
342 | // hence using a temporary array |
||||
343 | $tmpRow = []; |
||||
344 | foreach ($row as $val) { |
||||
345 | $tmpRow[] = $val; |
||||
346 | } |
||||
347 | //Get unique condition on each row (will be needed for row update) |
||||
348 | $uniqueCondition = Util::getUniqueCondition( |
||||
349 | $result, // handle |
||||
350 | count($this->_columnNames), // fields_cnt |
||||
351 | $fields_meta, // fields_meta |
||||
352 | $tmpRow, // row |
||||
353 | true, // force_unique |
||||
354 | false, // restrict_to_table |
||||
355 | null // analyzed_sql_results |
||||
356 | ); |
||||
357 | //Append it to row array as where_clause |
||||
358 | $row['where_clause'] = $uniqueCondition[0]; |
||||
359 | |||||
360 | $tmpData = [ |
||||
361 | $_POST['criteriaColumnNames'][0] => |
||||
362 | $row[$_POST['criteriaColumnNames'][0]], |
||||
363 | $_POST['criteriaColumnNames'][1] => |
||||
364 | $row[$_POST['criteriaColumnNames'][1]], |
||||
365 | 'where_clause' => $uniqueCondition[0] |
||||
366 | ]; |
||||
367 | $tmpData[$dataLabel] = ($dataLabel) ? $row[$dataLabel] : ''; |
||||
368 | $data[] = $tmpData; |
||||
369 | } |
||||
370 | unset($tmpData); |
||||
371 | |||||
372 | //Displays form for point data and scatter plot |
||||
373 | $titles = [ |
||||
374 | 'Browse' => Util::getIcon( |
||||
375 | 'b_browse', |
||||
376 | __('Browse foreign values') |
||||
377 | ) |
||||
378 | ]; |
||||
379 | $column_names_hashes = []; |
||||
380 | |||||
381 | foreach ($this->_columnNames as $columnName) { |
||||
382 | $column_names_hashes[$columnName] = md5($columnName); |
||||
383 | } |
||||
384 | |||||
385 | $this->response->addHTML( |
||||
386 | $this->template->render('table/search/zoom_result_form', [ |
||||
387 | 'db' => $this->db, |
||||
388 | 'table' => $this->table, |
||||
389 | 'column_names' => $this->_columnNames, |
||||
390 | 'column_names_hashes' => $column_names_hashes, |
||||
391 | 'foreigners' => $this->_foreigners, |
||||
392 | 'column_null_flags' => $this->_columnNullFlags, |
||||
393 | 'column_types' => $this->_columnTypes, |
||||
394 | 'titles' => $titles, |
||||
395 | 'goto' => $goto, |
||||
396 | 'data' => $data, |
||||
397 | 'data_json' => json_encode($data), |
||||
398 | 'zoom_submit' => isset($_POST['zoom_submit']), |
||||
399 | 'foreign_max_limit' => $GLOBALS['cfg']['ForeignKeyMaxLimit'], |
||||
400 | ]) |
||||
401 | ); |
||||
402 | } |
||||
403 | |||||
404 | /** |
||||
405 | * Change table info action |
||||
406 | * |
||||
407 | * @return void |
||||
408 | */ |
||||
409 | public function changeTableInfoAction() |
||||
410 | { |
||||
411 | $field = $_REQUEST['field']; |
||||
412 | if ($field == 'pma_null') { |
||||
413 | $this->response->addJSON('field_type', ''); |
||||
414 | $this->response->addJSON('field_collation', ''); |
||||
415 | $this->response->addJSON('field_operators', ''); |
||||
416 | $this->response->addJSON('field_value', ''); |
||||
417 | return; |
||||
418 | } |
||||
419 | $key = array_search($field, $this->_columnNames); |
||||
420 | $search_index |
||||
421 | = ((isset($_REQUEST['it']) && is_numeric($_REQUEST['it'])) |
||||
422 | ? intval($_REQUEST['it']) : 0); |
||||
423 | |||||
424 | $properties = $this->getColumnProperties($search_index, $key); |
||||
425 | $this->response->addJSON( |
||||
426 | 'field_type', |
||||
427 | htmlspecialchars($properties['type']) |
||||
428 | ); |
||||
429 | $this->response->addJSON('field_collation', $properties['collation']); |
||||
430 | $this->response->addJSON('field_operators', $properties['func']); |
||||
431 | $this->response->addJSON('field_value', $properties['value']); |
||||
432 | } |
||||
433 | |||||
434 | /** |
||||
435 | * Get data row action |
||||
436 | * |
||||
437 | * @return void |
||||
438 | */ |
||||
439 | public function getDataRowAction() |
||||
440 | { |
||||
441 | $extra_data = []; |
||||
442 | $row_info_query = 'SELECT * FROM `' . $_REQUEST['db'] . '`.`' |
||||
443 | . $_REQUEST['table'] . '` WHERE ' . $_REQUEST['where_clause']; |
||||
444 | $result = $this->dbi->query( |
||||
445 | $row_info_query . ";", |
||||
446 | DatabaseInterface::CONNECT_USER, |
||||
447 | DatabaseInterface::QUERY_STORE |
||||
448 | ); |
||||
449 | $fields_meta = $this->dbi->getFieldsMeta($result); |
||||
450 | while ($row = $this->dbi->fetchAssoc($result)) { |
||||
451 | // for bit fields we need to convert them to printable form |
||||
452 | $i = 0; |
||||
453 | foreach ($row as $col => $val) { |
||||
454 | if ($fields_meta[$i]->type == 'bit') { |
||||
455 | $row[$col] = Util::printableBitValue( |
||||
456 | (int) $val, |
||||
457 | (int) $fields_meta[$i]->length |
||||
458 | ); |
||||
459 | } |
||||
460 | $i++; |
||||
461 | } |
||||
462 | $extra_data['row_info'] = $row; |
||||
463 | } |
||||
464 | $this->response->addJSON($extra_data); |
||||
465 | } |
||||
466 | |||||
467 | /** |
||||
468 | * Do selection action |
||||
469 | * |
||||
470 | * @return void |
||||
471 | */ |
||||
472 | public function doSelectionAction() |
||||
473 | { |
||||
474 | /** |
||||
475 | * Selection criteria have been submitted -> do the work |
||||
476 | */ |
||||
477 | $sql_query = $this->_buildSqlQuery(); |
||||
478 | |||||
479 | /** |
||||
480 | * Add this to ensure following procedures included running correctly. |
||||
481 | */ |
||||
482 | $db = $this->db; |
||||
483 | |||||
484 | $sql = new Sql(); |
||||
485 | $sql->executeQueryAndSendQueryResponse( |
||||
486 | null, // analyzed_sql_results |
||||
487 | false, // is_gotofile |
||||
488 | $this->db, // db |
||||
489 | $this->table, // table |
||||
490 | null, // find_real_end |
||||
491 | null, // sql_query_for_bookmark |
||||
492 | null, // extra_data |
||||
493 | null, // message_to_show |
||||
494 | null, // message |
||||
495 | null, // sql_data |
||||
496 | $GLOBALS['goto'], // goto |
||||
497 | $GLOBALS['pmaThemeImage'], // pmaThemeImage |
||||
498 | null, // disp_query |
||||
499 | null, // disp_message |
||||
500 | null, // query_type |
||||
501 | $sql_query, // sql_query |
||||
502 | null, // selectedTables |
||||
503 | null // complete_query |
||||
504 | ); |
||||
505 | } |
||||
506 | |||||
507 | /** |
||||
508 | * Display selection form action |
||||
509 | * |
||||
510 | * @param string $dataLabel Data label |
||||
511 | * |
||||
512 | * @return void |
||||
513 | */ |
||||
514 | public function displaySelectionFormAction($dataLabel = null) |
||||
515 | { |
||||
516 | global $goto; |
||||
517 | $this->url_query .= '&goto=tbl_select.php&back=tbl_select.php'; |
||||
518 | if (! isset($goto)) { |
||||
519 | $goto = Util::getScriptNameForOption( |
||||
520 | $GLOBALS['cfg']['DefaultTabTable'], |
||||
521 | 'table' |
||||
522 | ); |
||||
523 | } |
||||
524 | // Displays the table search form |
||||
525 | $this->response->addHTML( |
||||
526 | $this->template->render('secondary_tabs', [ |
||||
527 | 'url_params' => [ |
||||
528 | 'db' => $this->db, |
||||
529 | 'table' => $this->table, |
||||
530 | ], |
||||
531 | 'sub_tabs' => $this->_getSubTabs(), |
||||
532 | ]) |
||||
533 | ); |
||||
534 | |||||
535 | $column_names = $this->_columnNames; |
||||
536 | $column_types = $this->_columnTypes; |
||||
537 | $types = []; |
||||
538 | if ($this->_searchType == 'replace') { |
||||
539 | $num_cols = count($column_names); |
||||
540 | for ($i = 0; $i < $num_cols; $i++) { |
||||
541 | $types[$column_names[$i]] = preg_replace('@\\(.*@s', '', $column_types[$i]); |
||||
542 | } |
||||
543 | } |
||||
544 | |||||
545 | $criteria_column_names = isset($_POST['criteriaColumnNames']) ? $_POST['criteriaColumnNames'] : null; |
||||
546 | $keys = []; |
||||
547 | for ($i = 0; $i < 4; $i++) { |
||||
548 | if (isset($criteria_column_names[$i])) { |
||||
549 | if ($criteria_column_names[$i] != 'pma_null') { |
||||
550 | $keys[$criteria_column_names[$i]] = array_search($criteria_column_names[$i], $column_names); |
||||
551 | } |
||||
552 | } |
||||
553 | } |
||||
554 | |||||
555 | $this->response->addHTML( |
||||
556 | $this->template->render('table/search/selection_form', [ |
||||
557 | 'search_type' => $this->_searchType, |
||||
558 | 'db' => $this->db, |
||||
559 | 'table' => $this->table, |
||||
560 | 'goto' => $goto, |
||||
561 | 'self' => $this, |
||||
562 | 'geom_column_flag' => $this->_geomColumnFlag, |
||||
563 | 'column_names' => $column_names, |
||||
564 | 'column_types' => $column_types, |
||||
565 | 'types' => $types, |
||||
566 | 'column_collations' => $this->_columnCollations, |
||||
567 | 'data_label' => $dataLabel, |
||||
568 | 'keys' => $keys, |
||||
569 | 'criteria_column_names' => $criteria_column_names, |
||||
570 | 'default_sliders_state' => $GLOBALS['cfg']['InitialSlidersState'], |
||||
571 | 'criteria_column_types' => isset($_POST['criteriaColumnTypes']) ? $_POST['criteriaColumnTypes'] : null, |
||||
572 | 'sql_types' => $this->dbi->types, |
||||
573 | 'max_rows' => intval($GLOBALS['cfg']['MaxRows']), |
||||
574 | 'max_plot_limit' => ((! empty($_POST['maxPlotLimit'])) |
||||
575 | ? intval($_POST['maxPlotLimit']) |
||||
576 | : intval($GLOBALS['cfg']['maxRowPlotLimit'])), |
||||
577 | ]) |
||||
578 | ); |
||||
579 | } |
||||
580 | |||||
581 | /** |
||||
582 | * Range search action |
||||
583 | * |
||||
584 | * @return void |
||||
585 | */ |
||||
586 | public function rangeSearchAction() |
||||
587 | { |
||||
588 | $min_max = $this->getColumnMinMax($_REQUEST['column']); |
||||
589 | $this->response->addJSON('column_data', $min_max); |
||||
590 | } |
||||
591 | |||||
592 | /** |
||||
593 | * Find action |
||||
594 | * |
||||
595 | * @return void |
||||
596 | */ |
||||
597 | public function findAction() |
||||
598 | { |
||||
599 | $useRegex = array_key_exists('useRegex', $_POST) |
||||
600 | && $_POST['useRegex'] == 'on'; |
||||
601 | |||||
602 | $preview = $this->getReplacePreview( |
||||
603 | $_POST['columnIndex'], |
||||
604 | $_POST['find'], |
||||
605 | $_POST['replaceWith'], |
||||
606 | $useRegex, |
||||
607 | $this->_connectionCharSet |
||||
608 | ); |
||||
609 | $this->response->addJSON('preview', $preview); |
||||
610 | } |
||||
611 | |||||
612 | /** |
||||
613 | * Replace action |
||||
614 | * |
||||
615 | * @return void |
||||
616 | */ |
||||
617 | public function replaceAction() |
||||
618 | { |
||||
619 | $this->replace( |
||||
620 | $_POST['columnIndex'], |
||||
621 | $_POST['findString'], |
||||
622 | $_POST['replaceWith'], |
||||
623 | $_POST['useRegex'], |
||||
624 | $this->_connectionCharSet |
||||
625 | ); |
||||
626 | $this->response->addHTML( |
||||
627 | Util::getMessage( |
||||
628 | __('Your SQL query has been executed successfully.'), |
||||
629 | null, |
||||
630 | 'success' |
||||
631 | ) |
||||
632 | ); |
||||
633 | } |
||||
634 | |||||
635 | /** |
||||
636 | * Returns HTML for previewing strings found and their replacements |
||||
637 | * |
||||
638 | * @param int $columnIndex index of the column |
||||
639 | * @param string $find string to find in the column |
||||
640 | * @param string $replaceWith string to replace with |
||||
641 | * @param boolean $useRegex to use Regex replace or not |
||||
642 | * @param string $charSet character set of the connection |
||||
643 | * |
||||
644 | * @return string HTML for previewing strings found and their replacements |
||||
645 | */ |
||||
646 | public function getReplacePreview( |
||||
647 | $columnIndex, |
||||
648 | $find, |
||||
649 | $replaceWith, |
||||
650 | $useRegex, |
||||
651 | $charSet |
||||
652 | ) { |
||||
653 | $column = $this->_columnNames[$columnIndex]; |
||||
654 | if ($useRegex) { |
||||
655 | $result = $this->_getRegexReplaceRows( |
||||
656 | $columnIndex, |
||||
657 | $find, |
||||
658 | $replaceWith, |
||||
659 | $charSet |
||||
660 | ); |
||||
661 | } else { |
||||
662 | $sql_query = "SELECT " |
||||
663 | . Util::backquote($column) . "," |
||||
0 ignored issues
–
show
Bug
introduced
by
Loading history...
|
|||||
664 | . " REPLACE(" |
||||
665 | . Util::backquote($column) . ", '" . $find . "', '" |
||||
666 | . $replaceWith |
||||
667 | . "')," |
||||
668 | . " COUNT(*)" |
||||
669 | . " FROM " . Util::backquote($this->db) |
||||
670 | . "." . Util::backquote($this->table) |
||||
671 | . " WHERE " . Util::backquote($column) |
||||
672 | . " LIKE '%" . $find . "%' COLLATE " . $charSet . "_bin"; // here we |
||||
673 | // change the collation of the 2nd operand to a case sensitive |
||||
674 | // binary collation to make sure that the comparison |
||||
675 | // is case sensitive |
||||
676 | $sql_query .= " GROUP BY " . Util::backquote($column) |
||||
677 | . " ORDER BY " . Util::backquote($column) . " ASC"; |
||||
678 | |||||
679 | $result = $this->dbi->fetchResult($sql_query, 0); |
||||
680 | } |
||||
681 | |||||
682 | return $this->template->render('table/search/replace_preview', [ |
||||
683 | 'db' => $this->db, |
||||
684 | 'table' => $this->table, |
||||
685 | 'column_index' => $columnIndex, |
||||
686 | 'find' => $find, |
||||
687 | 'replace_with' => $replaceWith, |
||||
688 | 'use_regex' => $useRegex, |
||||
689 | 'result' => $result, |
||||
690 | ]); |
||||
691 | } |
||||
692 | |||||
693 | /** |
||||
694 | * Finds and returns Regex pattern and their replacements |
||||
695 | * |
||||
696 | * @param int $columnIndex index of the column |
||||
697 | * @param string $find string to find in the column |
||||
698 | * @param string $replaceWith string to replace with |
||||
699 | * @param string $charSet character set of the connection |
||||
700 | * |
||||
701 | * @return array Array containing original values, replaced values and count |
||||
702 | */ |
||||
703 | private function _getRegexReplaceRows( |
||||
704 | $columnIndex, |
||||
705 | $find, |
||||
706 | $replaceWith, |
||||
707 | $charSet |
||||
708 | ) { |
||||
709 | $column = $this->_columnNames[$columnIndex]; |
||||
710 | $sql_query = "SELECT " |
||||
711 | . Util::backquote($column) . "," |
||||
0 ignored issues
–
show
Are you sure
PhpMyAdmin\Util::backquote($column) of type array|mixed|string can be used in concatenation ?
(
Ignorable by Annotation
)
If this is a false-positive, you can also ignore this issue in your code via the
Loading history...
|
|||||
712 | . " 1," // to add an extra column that will have replaced value |
||||
713 | . " COUNT(*)" |
||||
714 | . " FROM " . Util::backquote($this->db) |
||||
715 | . "." . Util::backquote($this->table) |
||||
716 | . " WHERE " . Util::backquote($column) |
||||
717 | . " RLIKE '" . $this->dbi->escapeString($find) . "' COLLATE " |
||||
718 | . $charSet . "_bin"; // here we |
||||
719 | // change the collation of the 2nd operand to a case sensitive |
||||
720 | // binary collation to make sure that the comparison is case sensitive |
||||
721 | $sql_query .= " GROUP BY " . Util::backquote($column) |
||||
722 | . " ORDER BY " . Util::backquote($column) . " ASC"; |
||||
723 | |||||
724 | $result = $this->dbi->fetchResult($sql_query, 0); |
||||
725 | |||||
726 | if (is_array($result)) { |
||||
727 | /* Iterate over possible delimiters to get one */ |
||||
728 | $delimiters = ['/', '@', '#', '~', '!', '$', '%', '^', '&', '_']; |
||||
729 | $found = false; |
||||
730 | for ($i = 0, $l = count($delimiters); $i < $l; $i++) { |
||||
731 | if (strpos($find, $delimiters[$i]) === false) { |
||||
732 | $found = true; |
||||
733 | break; |
||||
734 | } |
||||
735 | } |
||||
736 | if (! $found) { |
||||
737 | return false; |
||||
738 | } |
||||
739 | $find = $delimiters[$i] . $find . $delimiters[$i]; |
||||
740 | foreach ($result as $index => $row) { |
||||
741 | $result[$index][1] = preg_replace( |
||||
742 | $find, |
||||
743 | $replaceWith, |
||||
744 | $row[0] |
||||
745 | ); |
||||
746 | } |
||||
747 | } |
||||
748 | return $result; |
||||
749 | } |
||||
750 | |||||
751 | /** |
||||
752 | * Replaces a given string in a column with a give replacement |
||||
753 | * |
||||
754 | * @param int $columnIndex index of the column |
||||
755 | * @param string $find string to find in the column |
||||
756 | * @param string $replaceWith string to replace with |
||||
757 | * @param boolean $useRegex to use Regex replace or not |
||||
758 | * @param string $charSet character set of the connection |
||||
759 | * |
||||
760 | * @return void |
||||
761 | */ |
||||
762 | public function replace( |
||||
763 | $columnIndex, |
||||
764 | $find, |
||||
765 | $replaceWith, |
||||
766 | $useRegex, |
||||
767 | $charSet |
||||
768 | ) { |
||||
769 | $column = $this->_columnNames[$columnIndex]; |
||||
770 | if ($useRegex) { |
||||
771 | $toReplace = $this->_getRegexReplaceRows( |
||||
772 | $columnIndex, |
||||
773 | $find, |
||||
774 | $replaceWith, |
||||
775 | $charSet |
||||
776 | ); |
||||
777 | $sql_query = "UPDATE " . Util::backquote($this->table) |
||||
778 | . " SET " . Util::backquote($column) . " = CASE"; |
||||
0 ignored issues
–
show
Are you sure
PhpMyAdmin\Util::backquote($column) of type array|mixed|string can be used in concatenation ?
(
Ignorable by Annotation
)
If this is a false-positive, you can also ignore this issue in your code via the
Loading history...
|
|||||
779 | if (is_array($toReplace)) { |
||||
780 | foreach ($toReplace as $row) { |
||||
781 | $sql_query .= "\n WHEN " . Util::backquote($column) |
||||
782 | . " = '" . $this->dbi->escapeString($row[0]) |
||||
783 | . "' THEN '" . $this->dbi->escapeString($row[1]) . "'"; |
||||
784 | } |
||||
785 | } |
||||
786 | $sql_query .= " END" |
||||
787 | . " WHERE " . Util::backquote($column) |
||||
788 | . " RLIKE '" . $this->dbi->escapeString($find) . "' COLLATE " |
||||
789 | . $charSet . "_bin"; // here we |
||||
790 | // change the collation of the 2nd operand to a case sensitive |
||||
791 | // binary collation to make sure that the comparison |
||||
792 | // is case sensitive |
||||
793 | } else { |
||||
794 | $sql_query = "UPDATE " . Util::backquote($this->table) |
||||
795 | . " SET " . Util::backquote($column) . " =" |
||||
796 | . " REPLACE(" |
||||
797 | . Util::backquote($column) . ", '" . $find . "', '" |
||||
798 | . $replaceWith |
||||
799 | . "')" |
||||
800 | . " WHERE " . Util::backquote($column) |
||||
801 | . " LIKE '%" . $find . "%' COLLATE " . $charSet . "_bin"; // here we |
||||
802 | // change the collation of the 2nd operand to a case sensitive |
||||
803 | // binary collation to make sure that the comparison |
||||
804 | // is case sensitive |
||||
805 | } |
||||
806 | $this->dbi->query( |
||||
807 | $sql_query, |
||||
808 | DatabaseInterface::CONNECT_USER, |
||||
809 | DatabaseInterface::QUERY_STORE |
||||
810 | ); |
||||
811 | $GLOBALS['sql_query'] = $sql_query; |
||||
812 | } |
||||
813 | |||||
814 | /** |
||||
815 | * Finds minimum and maximum value of a given column. |
||||
816 | * |
||||
817 | * @param string $column Column name |
||||
818 | * |
||||
819 | * @return array |
||||
820 | */ |
||||
821 | public function getColumnMinMax($column) |
||||
822 | { |
||||
823 | $sql_query = 'SELECT MIN(' . Util::backquote($column) . ') AS `min`, ' |
||||
824 | . 'MAX(' . Util::backquote($column) . ') AS `max` ' |
||||
825 | . 'FROM ' . Util::backquote($this->db) . '.' |
||||
826 | . Util::backquote($this->table); |
||||
827 | |||||
828 | $result = $this->dbi->fetchSingleRow($sql_query); |
||||
829 | |||||
830 | return $result; |
||||
831 | } |
||||
832 | |||||
833 | /** |
||||
834 | * Returns an array with necessary configurations to create |
||||
835 | * sub-tabs in the table_select page. |
||||
836 | * |
||||
837 | * @return array Array containing configuration (icon, text, link, id, args) |
||||
838 | * of sub-tabs |
||||
839 | */ |
||||
840 | private function _getSubTabs() |
||||
841 | { |
||||
842 | $subtabs = []; |
||||
843 | $subtabs['search']['icon'] = 'b_search'; |
||||
844 | $subtabs['search']['text'] = __('Table search'); |
||||
845 | $subtabs['search']['link'] = 'tbl_select.php'; |
||||
846 | $subtabs['search']['id'] = 'tbl_search_id'; |
||||
847 | $subtabs['search']['args']['pos'] = 0; |
||||
848 | |||||
849 | $subtabs['zoom']['icon'] = 'b_select'; |
||||
850 | $subtabs['zoom']['link'] = 'tbl_zoom_select.php'; |
||||
851 | $subtabs['zoom']['text'] = __('Zoom search'); |
||||
852 | $subtabs['zoom']['id'] = 'zoom_search_id'; |
||||
853 | |||||
854 | $subtabs['replace']['icon'] = 'b_find_replace'; |
||||
855 | $subtabs['replace']['link'] = 'tbl_find_replace.php'; |
||||
856 | $subtabs['replace']['text'] = __('Find and replace'); |
||||
857 | $subtabs['replace']['id'] = 'find_replace_id'; |
||||
858 | |||||
859 | return $subtabs; |
||||
860 | } |
||||
861 | |||||
862 | /** |
||||
863 | * Builds the sql search query from the post parameters |
||||
864 | * |
||||
865 | * @return string the generated SQL query |
||||
866 | */ |
||||
867 | private function _buildSqlQuery() |
||||
868 | { |
||||
869 | $sql_query = 'SELECT '; |
||||
870 | |||||
871 | // If only distinct values are needed |
||||
872 | $is_distinct = (isset($_POST['distinct'])) ? 'true' : 'false'; |
||||
873 | if ($is_distinct == 'true') { |
||||
874 | $sql_query .= 'DISTINCT '; |
||||
875 | } |
||||
876 | |||||
877 | // if all column names were selected to display, we do a 'SELECT *' |
||||
878 | // (more efficient and this helps prevent a problem in IE |
||||
879 | // if one of the rows is edited and we come back to the Select results) |
||||
880 | if (isset($_POST['zoom_submit']) || ! empty($_POST['displayAllColumns'])) { |
||||
881 | $sql_query .= '* '; |
||||
882 | } else { |
||||
883 | $sql_query .= implode( |
||||
884 | ', ', |
||||
885 | Util::backquote($_POST['columnsToDisplay']) |
||||
0 ignored issues
–
show
It seems like
PhpMyAdmin\Util::backquo...ST['columnsToDisplay']) can also be of type string ; however, parameter $pieces of implode() does only seem to accept array , maybe add an additional type check?
(
Ignorable by Annotation
)
If this is a false-positive, you can also ignore this issue in your code via the
Loading history...
|
|||||
886 | ); |
||||
887 | } // end if |
||||
888 | |||||
889 | $sql_query .= ' FROM ' |
||||
890 | . Util::backquote($_POST['table']); |
||||
0 ignored issues
–
show
Are you sure
PhpMyAdmin\Util::backquote($_POST['table']) of type array|mixed|string can be used in concatenation ?
(
Ignorable by Annotation
)
If this is a false-positive, you can also ignore this issue in your code via the
Loading history...
|
|||||
891 | $whereClause = $this->_generateWhereClause(); |
||||
892 | $sql_query .= $whereClause; |
||||
893 | |||||
894 | // if the search results are to be ordered |
||||
895 | if (isset($_POST['orderByColumn']) && $_POST['orderByColumn'] != '--nil--') { |
||||
896 | $sql_query .= ' ORDER BY ' |
||||
897 | . Util::backquote($_POST['orderByColumn']) |
||||
0 ignored issues
–
show
Are you sure
PhpMyAdmin\Util::backquo..._POST['orderByColumn']) of type array|mixed|string can be used in concatenation ?
(
Ignorable by Annotation
)
If this is a false-positive, you can also ignore this issue in your code via the
Loading history...
|
|||||
898 | . ' ' . $_POST['order']; |
||||
899 | } // end if |
||||
900 | return $sql_query; |
||||
901 | } |
||||
902 | |||||
903 | /** |
||||
904 | * Provides a column's type, collation, operators list, and criteria value |
||||
905 | * to display in table search form |
||||
906 | * |
||||
907 | * @param integer $search_index Row number in table search form |
||||
908 | * @param integer $column_index Column index in ColumnNames array |
||||
909 | * |
||||
910 | * @return array Array containing column's properties |
||||
911 | */ |
||||
912 | public function getColumnProperties($search_index, $column_index) |
||||
913 | { |
||||
914 | $selected_operator = (isset($_POST['criteriaColumnOperators'][$search_index]) |
||||
915 | ? $_POST['criteriaColumnOperators'][$search_index] : ''); |
||||
916 | $entered_value = (isset($_POST['criteriaValues']) |
||||
917 | ? $_POST['criteriaValues'] : ''); |
||||
918 | $titles = [ |
||||
919 | 'Browse' => Util::getIcon( |
||||
920 | 'b_browse', |
||||
921 | __('Browse foreign values') |
||||
922 | ) |
||||
923 | ]; |
||||
924 | //Gets column's type and collation |
||||
925 | $type = $this->_columnTypes[$column_index]; |
||||
926 | $collation = $this->_columnCollations[$column_index]; |
||||
927 | //Gets column's comparison operators depending on column type |
||||
928 | $typeOperators = $this->dbi->types->getTypeOperatorsHtml( |
||||
929 | preg_replace('@\(.*@s', '', $this->_columnTypes[$column_index]), |
||||
930 | $this->_columnNullFlags[$column_index], |
||||
931 | $selected_operator |
||||
932 | ); |
||||
933 | $func = $this->template->render('table/search/column_comparison_operators', [ |
||||
934 | 'search_index' => $search_index, |
||||
935 | 'type_operators' => $typeOperators, |
||||
936 | ]); |
||||
937 | //Gets link to browse foreign data(if any) and criteria inputbox |
||||
938 | $foreignData = $this->relation->getForeignData( |
||||
939 | $this->_foreigners, |
||||
940 | $this->_columnNames[$column_index], |
||||
941 | false, |
||||
942 | '', |
||||
943 | '' |
||||
944 | ); |
||||
945 | $value = $this->template->render('table/search/input_box', [ |
||||
946 | 'str' => '', |
||||
947 | 'column_type' => (string) $type, |
||||
948 | 'column_id' => 'fieldID_', |
||||
949 | 'in_zoom_search_edit' => false, |
||||
950 | 'foreigners' => $this->_foreigners, |
||||
951 | 'column_name' => $this->_columnNames[$column_index], |
||||
952 | 'column_name_hash' => md5($this->_columnNames[$column_index]), |
||||
953 | 'foreign_data' => $foreignData, |
||||
954 | 'table' => $this->table, |
||||
955 | 'column_index' => $search_index, |
||||
956 | 'foreign_max_limit' => $GLOBALS['cfg']['ForeignKeyMaxLimit'], |
||||
957 | 'criteria_values' => $entered_value, |
||||
958 | 'db' => $this->db, |
||||
959 | 'titles' => $titles, |
||||
960 | 'in_fbs' => true, |
||||
961 | ]); |
||||
962 | return [ |
||||
963 | 'type' => $type, |
||||
964 | 'collation' => $collation, |
||||
965 | 'func' => $func, |
||||
966 | 'value' => $value |
||||
967 | ]; |
||||
968 | } |
||||
969 | |||||
970 | /** |
||||
971 | * Generates the where clause for the SQL search query to be executed |
||||
972 | * |
||||
973 | * @return string the generated where clause |
||||
974 | */ |
||||
975 | private function _generateWhereClause() |
||||
976 | { |
||||
977 | if (isset($_POST['customWhereClause']) |
||||
978 | && trim($_POST['customWhereClause']) != '' |
||||
979 | ) { |
||||
980 | return ' WHERE ' . $_POST['customWhereClause']; |
||||
981 | } |
||||
982 | |||||
983 | // If there are no search criteria set or no unary criteria operators, |
||||
984 | // return |
||||
985 | if (! isset($_POST['criteriaValues']) |
||||
986 | && ! isset($_POST['criteriaColumnOperators']) |
||||
987 | && ! isset($_POST['geom_func']) |
||||
988 | ) { |
||||
989 | return ''; |
||||
990 | } |
||||
991 | |||||
992 | // else continue to form the where clause from column criteria values |
||||
993 | $fullWhereClause = []; |
||||
994 | foreach ($_POST['criteriaColumnOperators'] as $column_index => $operator) { |
||||
995 | $unaryFlag = $this->dbi->types->isUnaryOperator($operator); |
||||
996 | $tmp_geom_func = isset($_POST['geom_func'][$column_index]) |
||||
997 | ? $_POST['geom_func'][$column_index] : null; |
||||
998 | |||||
999 | $whereClause = $this->_getWhereClause( |
||||
1000 | $_POST['criteriaValues'][$column_index], |
||||
1001 | $_POST['criteriaColumnNames'][$column_index], |
||||
1002 | $_POST['criteriaColumnTypes'][$column_index], |
||||
1003 | $operator, |
||||
1004 | $unaryFlag, |
||||
1005 | $tmp_geom_func |
||||
1006 | ); |
||||
1007 | |||||
1008 | if ($whereClause) { |
||||
1009 | $fullWhereClause[] = $whereClause; |
||||
1010 | } |
||||
1011 | } // end foreach |
||||
1012 | |||||
1013 | if (!empty($fullWhereClause)) { |
||||
1014 | return ' WHERE ' . implode(' AND ', $fullWhereClause); |
||||
1015 | } |
||||
1016 | return ''; |
||||
1017 | } |
||||
1018 | |||||
1019 | /** |
||||
1020 | * Return the where clause in case column's type is ENUM. |
||||
1021 | * |
||||
1022 | * @param mixed $criteriaValues Search criteria input |
||||
1023 | * @param string $func_type Search function/operator |
||||
1024 | * |
||||
1025 | * @return string part of where clause. |
||||
1026 | */ |
||||
1027 | private function _getEnumWhereClause($criteriaValues, $func_type) |
||||
1028 | { |
||||
1029 | if (! is_array($criteriaValues)) { |
||||
1030 | $criteriaValues = explode(',', $criteriaValues); |
||||
1031 | } |
||||
1032 | $enum_selected_count = count($criteriaValues); |
||||
1033 | if ($func_type == '=' && $enum_selected_count > 1) { |
||||
1034 | $func_type = 'IN'; |
||||
1035 | $parens_open = '('; |
||||
1036 | $parens_close = ')'; |
||||
1037 | } elseif ($func_type == '!=' && $enum_selected_count > 1) { |
||||
1038 | $func_type = 'NOT IN'; |
||||
1039 | $parens_open = '('; |
||||
1040 | $parens_close = ')'; |
||||
1041 | } else { |
||||
1042 | $parens_open = ''; |
||||
1043 | $parens_close = ''; |
||||
1044 | } |
||||
1045 | $enum_where = '\'' |
||||
1046 | . $this->dbi->escapeString($criteriaValues[0]) . '\''; |
||||
1047 | for ($e = 1; $e < $enum_selected_count; $e++) { |
||||
1048 | $enum_where .= ', \'' |
||||
1049 | . $this->dbi->escapeString($criteriaValues[$e]) . '\''; |
||||
1050 | } |
||||
1051 | |||||
1052 | return ' ' . $func_type . ' ' . $parens_open |
||||
1053 | . $enum_where . $parens_close; |
||||
1054 | } |
||||
1055 | |||||
1056 | /** |
||||
1057 | * Return the where clause for a geometrical column. |
||||
1058 | * |
||||
1059 | * @param mixed $criteriaValues Search criteria input |
||||
1060 | * @param string $names Name of the column on which search is submitted |
||||
1061 | * @param string $func_type Search function/operator |
||||
1062 | * @param string $types Type of the field |
||||
1063 | * @param bool $geom_func Whether geometry functions should be applied |
||||
1064 | * |
||||
1065 | * @return string part of where clause. |
||||
1066 | */ |
||||
1067 | private function _getGeomWhereClause( |
||||
1068 | $criteriaValues, |
||||
1069 | $names, |
||||
1070 | $func_type, |
||||
1071 | $types, |
||||
1072 | $geom_func = null |
||||
1073 | ) { |
||||
1074 | $geom_unary_functions = [ |
||||
1075 | 'IsEmpty' => 1, |
||||
1076 | 'IsSimple' => 1, |
||||
1077 | 'IsRing' => 1, |
||||
1078 | 'IsClosed' => 1, |
||||
1079 | ]; |
||||
1080 | $where = ''; |
||||
1081 | |||||
1082 | // Get details about the geometry functions |
||||
1083 | $geom_funcs = Util::getGISFunctions($types, true, false); |
||||
1084 | |||||
1085 | // If the function takes multiple parameters |
||||
1086 | if (strpos($func_type, "IS NULL") !== false || strpos($func_type, "IS NOT NULL") !== false) { |
||||
1087 | $where = Util::backquote($names) . " " . $func_type; |
||||
1088 | return $where; |
||||
1089 | } elseif ($geom_funcs[$geom_func]['params'] > 1) { |
||||
1090 | // create gis data from the criteria input |
||||
1091 | $gis_data = Util::createGISData($criteriaValues); |
||||
1092 | $where = $geom_func . '(' . Util::backquote($names) |
||||
1093 | . ', ' . $gis_data . ')'; |
||||
1094 | return $where; |
||||
1095 | } |
||||
1096 | |||||
1097 | // New output type is the output type of the function being applied |
||||
1098 | $type = $geom_funcs[$geom_func]['type']; |
||||
1099 | $geom_function_applied = $geom_func |
||||
1100 | . '(' . Util::backquote($names) . ')'; |
||||
1101 | |||||
1102 | // If the where clause is something like 'IsEmpty(`spatial_col_name`)' |
||||
1103 | if (isset($geom_unary_functions[$geom_func]) |
||||
1104 | && trim($criteriaValues) == '' |
||||
1105 | ) { |
||||
1106 | $where = $geom_function_applied; |
||||
1107 | } elseif (in_array($type, Util::getGISDatatypes()) |
||||
1108 | && ! empty($criteriaValues) |
||||
1109 | ) { |
||||
1110 | // create gis data from the criteria input |
||||
1111 | $gis_data = Util::createGISData($criteriaValues); |
||||
1112 | $where = $geom_function_applied . " " . $func_type . " " . $gis_data; |
||||
1113 | } elseif (strlen($criteriaValues) > 0) { |
||||
1114 | $where = $geom_function_applied . " " |
||||
1115 | . $func_type . " '" . $criteriaValues . "'"; |
||||
1116 | } |
||||
1117 | return $where; |
||||
1118 | } |
||||
1119 | |||||
1120 | /** |
||||
1121 | * Return the where clause for query generation based on the inputs provided. |
||||
1122 | * |
||||
1123 | * @param mixed $criteriaValues Search criteria input |
||||
1124 | * @param string $names Name of the column on which search is submitted |
||||
1125 | * @param string $types Type of the field |
||||
1126 | * @param string $func_type Search function/operator |
||||
1127 | * @param bool $unaryFlag Whether operator unary or not |
||||
1128 | * @param bool $geom_func Whether geometry functions should be applied |
||||
1129 | * |
||||
1130 | * @return string generated where clause. |
||||
1131 | */ |
||||
1132 | private function _getWhereClause( |
||||
1133 | $criteriaValues, |
||||
1134 | $names, |
||||
1135 | $types, |
||||
1136 | $func_type, |
||||
1137 | $unaryFlag, |
||||
1138 | $geom_func = null |
||||
1139 | ) { |
||||
1140 | // If geometry function is set |
||||
1141 | if (! empty($geom_func)) { |
||||
1142 | return $this->_getGeomWhereClause( |
||||
1143 | $criteriaValues, |
||||
1144 | $names, |
||||
1145 | $func_type, |
||||
1146 | $types, |
||||
1147 | $geom_func |
||||
1148 | ); |
||||
1149 | } |
||||
1150 | |||||
1151 | $backquoted_name = Util::backquote($names); |
||||
1152 | $where = ''; |
||||
1153 | if ($unaryFlag) { |
||||
1154 | $where = $backquoted_name . ' ' . $func_type; |
||||
1155 | } elseif (strncasecmp($types, 'enum', 4) == 0 && (! empty($criteriaValues) || $criteriaValues[0] === '0')) { |
||||
1156 | $where = $backquoted_name; |
||||
1157 | $where .= $this->_getEnumWhereClause($criteriaValues, $func_type); |
||||
1158 | } elseif ($criteriaValues != '') { |
||||
1159 | // For these types we quote the value. Even if it's another type |
||||
1160 | // (like INT), for a LIKE we always quote the value. MySQL converts |
||||
1161 | // strings to numbers and numbers to strings as necessary |
||||
1162 | // during the comparison |
||||
1163 | if (preg_match('@char|binary|blob|text|set|date|time|year@i', $types) |
||||
1164 | || mb_strpos(' ' . $func_type, 'LIKE') |
||||
1165 | ) { |
||||
1166 | $quot = '\''; |
||||
1167 | } else { |
||||
1168 | $quot = ''; |
||||
1169 | } |
||||
1170 | |||||
1171 | // LIKE %...% |
||||
1172 | if ($func_type == 'LIKE %...%') { |
||||
1173 | $func_type = 'LIKE'; |
||||
1174 | $criteriaValues = '%' . $criteriaValues . '%'; |
||||
1175 | } |
||||
1176 | if ($func_type == 'REGEXP ^...$') { |
||||
1177 | $func_type = 'REGEXP'; |
||||
1178 | $criteriaValues = '^' . $criteriaValues . '$'; |
||||
1179 | } |
||||
1180 | |||||
1181 | if ('IN (...)' != $func_type |
||||
1182 | && 'NOT IN (...)' != $func_type |
||||
1183 | && 'BETWEEN' != $func_type |
||||
1184 | && 'NOT BETWEEN' != $func_type |
||||
1185 | ) { |
||||
1186 | return $backquoted_name . ' ' . $func_type . ' ' . $quot |
||||
1187 | . $this->dbi->escapeString($criteriaValues) . $quot; |
||||
1188 | } |
||||
1189 | $func_type = str_replace(' (...)', '', $func_type); |
||||
1190 | |||||
1191 | //Don't explode if this is already an array |
||||
1192 | //(Case for (NOT) IN/BETWEEN.) |
||||
1193 | if (is_array($criteriaValues)) { |
||||
1194 | $values = $criteriaValues; |
||||
1195 | } else { |
||||
1196 | $values = explode(',', $criteriaValues); |
||||
1197 | } |
||||
1198 | // quote values one by one |
||||
1199 | $emptyKey = false; |
||||
1200 | foreach ($values as $key => &$value) { |
||||
1201 | if ('' === $value) { |
||||
1202 | $emptyKey = $key; |
||||
1203 | $value = 'NULL'; |
||||
1204 | continue; |
||||
1205 | } |
||||
1206 | $value = $quot . $this->dbi->escapeString(trim($value)) |
||||
1207 | . $quot; |
||||
1208 | } |
||||
1209 | |||||
1210 | if ('BETWEEN' == $func_type || 'NOT BETWEEN' == $func_type) { |
||||
1211 | $where = $backquoted_name . ' ' . $func_type . ' ' |
||||
1212 | . (isset($values[0]) ? $values[0] : '') |
||||
1213 | . ' AND ' . (isset($values[1]) ? $values[1] : ''); |
||||
1214 | } else { //[NOT] IN |
||||
1215 | if (false !== $emptyKey) { |
||||
1216 | unset($values[$emptyKey]); |
||||
1217 | } |
||||
1218 | $wheres = []; |
||||
1219 | if (!empty($values)) { |
||||
1220 | $wheres[] = $backquoted_name . ' ' . $func_type |
||||
1221 | . ' (' . implode(',', $values) . ')'; |
||||
1222 | } |
||||
1223 | if (false !== $emptyKey) { |
||||
1224 | $wheres[] = $backquoted_name . ' IS NULL'; |
||||
1225 | } |
||||
1226 | $where = implode(' OR ', $wheres); |
||||
1227 | if (1 < count($wheres)) { |
||||
1228 | $where = '(' . $where . ')'; |
||||
1229 | } |
||||
1230 | } |
||||
1231 | } // end if |
||||
1232 | |||||
1233 | return $where; |
||||
1234 | } |
||||
1235 | } |
||||
1236 |