libraries/classes/Controllers/Table/FindReplaceController.php

   1 <?php
   2 declare(strict_types=1);
   3
   4 namespace PhpMyAdmin\Controllers\Table;
   5
   6 use PhpMyAdmin\Common;
   7 use PhpMyAdmin\DatabaseInterface;
   8 use PhpMyAdmin\Html\Generator;
   9 use PhpMyAdmin\Response;
  10 use PhpMyAdmin\Template;
  11 use PhpMyAdmin\Util;
  12 use function array_key_exists;
  13 use function count;
  14 use function is_array;
  15 use function mb_strtolower;
  16 use function preg_match;
  17 use function preg_replace;
  18 use function str_ireplace;
  19 use function str_replace;
  20 use function strncasecmp;
  21 use function strpos;
  22
  23 /**
  24  * Handles find and replace tab.
  25  *
  26  * Displays find and replace form, allows previewing and do the replacing.
  27  */
  28 class FindReplaceController extends AbstractController
  29 {
  30     /** @var array */
  31     private $_columnNames;
  32
  33     /** @var array */
  34     private $_columnTypes;
  35
  36     /** @var string */
  37     private $_connectionCharSet;
  38
  39     /**
  40      * @param Response          $response Response object
  41      * @param DatabaseInterface $dbi      DatabaseInterface object
  42      * @param Template          $template Template object
  43      * @param string            $db       Database name
  44      * @param string            $table    Table name
  45      */
  46     public function __construct($response, $dbi, Template $template, $db, $table)
  47     {
  48         parent::__construct($response, $dbi, $template, $db, $table);
  49         $this->_columnNames = [];
  50         $this->_columnTypes = [];
  51         $this->_loadTableInfo();
  52         $this->_connectionCharSet = $this->dbi->fetchValue(
  53             'SELECT @@character_set_connection'
  54         );
  55     }
  56
  57     public function index(): void
  58     {
  59         Common::table();
  60
  61         if (isset($_POST['find'])) {
  62             $this->findAction();
  63             return;
  64         }
  65
  66         $header = $this->response->getHeader();
  67         $scripts = $header->getScripts();
  68         $scripts->addFile('table/find_replace.js');
  69
  70         if (isset($_POST['replace'])) {
  71             $this->replaceAction();
  72         }
  73
  74         // Displays the find and replace form
  75         $this->displaySelectionFormAction();
  76     }
  77
  78     /**
  79      * Gets all the columns of a table along with their types.
  80      */
  81     private function _loadTableInfo(): void
  82     {
  83         // Gets the list and number of columns
  84         $columns = $this->dbi->getColumns(
  85             $this->db,
  86             $this->table,
  87             null,
  88             true
  89         );
  90
  91         foreach ($columns as $row) {
  92             // set column name
  93             $this->_columnNames[] = $row['Field'];
  94
  95             $type = $row['Type'];
  96             // reformat mysql query output
  97             if (strncasecmp($type, 'set', 3) == 0
  98                 || strncasecmp($type, 'enum', 4) == 0
  99             ) {
 100                 $type = str_replace(',', ', ', $type);
 101             } else {
 102                 // strip the "BINARY" attribute, except if we find "BINARY(" because
 103                 // this would be a BINARY or VARBINARY column type
 104                 if (! preg_match('@BINARY[\(]@i', $type)) {
 105                     $type = str_ireplace('BINARY', '', $type);
 106                 }
 107                 $type = str_ireplace('ZEROFILL', '', $type);
 108                 $type = str_ireplace('UNSIGNED', '', $type);
 109                 $type = mb_strtolower($type);
 110             }
 111             if (empty($type)) {
 112                 $type = '&nbsp;';
 113             }
 114             $this->_columnTypes[] = $type;
 115         }
 116     }
 117
 118     /**
 119      * Display selection form action
 120      */
 121     public function displaySelectionFormAction(): void
 122     {
 123         global $goto;
 124
 125         if (! isset($goto)) {
 126             $goto = Util::getScriptNameForOption(
 127                 $GLOBALS['cfg']['DefaultTabTable'],
 128                 'table'
 129             );
 130         }
 131
 132         $column_names = $this->_columnNames;
 133         $column_types = $this->_columnTypes;
 134         $types = [];
 135         $num_cols = count($column_names);
 136         for ($i = 0; $i < $num_cols; $i++) {
 137             $types[$column_names[$i]] = preg_replace(
 138                 '@\\(.*@s',
 139                 '',
 140                 $column_types[$i]
 141             );
 142         }
 143
 144         $this->render('table/find_replace/index', [
 145             'db' => $this->db,
 146             'table' => $this->table,
 147             'goto' => $goto,
 148             'column_names' => $column_names,
 149             'types' => $types,
 150             'sql_types' => $this->dbi->types,
 151         ]);
 152     }
 153
 154     public function findAction(): void
 155     {
 156         $useRegex = array_key_exists('useRegex', $_POST)
 157             && $_POST['useRegex'] == 'on';
 158
 159         $preview = $this->getReplacePreview(
 160             $_POST['columnIndex'],
 161             $_POST['find'],
 162             $_POST['replaceWith'],
 163             $useRegex,
 164             $this->_connectionCharSet
 165         );
 166         $this->response->addJSON('preview', $preview);
 167     }
 168
 169     public function replaceAction(): void
 170     {
 171         $this->replace(
 172             $_POST['columnIndex'],
 173             $_POST['findString'],
 174             $_POST['replaceWith'],
 175             $_POST['useRegex'],
 176             $this->_connectionCharSet
 177         );
 178         $this->response->addHTML(
 179             Generator::getMessage(
 180                 __('Your SQL query has been executed successfully.'),
 181                 null,
 182                 'success'
 183             )
 184         );
 185     }
 186
 187     /**
 188      * Returns HTML for previewing strings found and their replacements
 189      *
 190      * @param int    $columnIndex index of the column
 191      * @param string $find        string to find in the column
 192      * @param string $replaceWith string to replace with
 193      * @param bool   $useRegex    to use Regex replace or not
 194      * @param string $charSet     character set of the connection
 195      *
 196      * @return string HTML for previewing strings found and their replacements
 197      */
 198     public function getReplacePreview(
 199         $columnIndex,
 200         $find,
 201         $replaceWith,
 202         $useRegex,
 203         $charSet
 204     ) {
 205         $column = $this->_columnNames[$columnIndex];
 206         if ($useRegex) {
 207             $result = $this->_getRegexReplaceRows(
 208                 $columnIndex,
 209                 $find,
 210                 $replaceWith,
 211                 $charSet
 212             );
 213         } else {
 214             $sql_query = 'SELECT '
 215                 . Util::backquote($column) . ','
 216                 . ' REPLACE('
 217                 . Util::backquote($column) . ", '" . $find . "', '"
 218                 . $replaceWith
 219                 . "'),"
 220                 . ' COUNT(*)'
 221                 . ' FROM ' . Util::backquote($this->db)
 222                 . '.' . Util::backquote($this->table)
 223                 . ' WHERE ' . Util::backquote($column)
 224                 . " LIKE '%" . $find . "%' COLLATE " . $charSet . '_bin'; // here we
 225             // change the collation of the 2nd operand to a case sensitive
 226             // binary collation to make sure that the comparison
 227             // is case sensitive
 228             $sql_query .= ' GROUP BY ' . Util::backquote($column)
 229                 . ' ORDER BY ' . Util::backquote($column) . ' ASC';
 230
 231             $result = $this->dbi->fetchResult($sql_query, 0);
 232         }
 233
 234         return $this->template->render('table/find_replace/replace_preview', [
 235             'db' => $this->db,
 236             'table' => $this->table,
 237             'column_index' => $columnIndex,
 238             'find' => $find,
 239             'replace_with' => $replaceWith,
 240             'use_regex' => $useRegex,
 241             'result' => $result,
 242         ]);
 243     }
 244
 245     /**
 246      * Finds and returns Regex pattern and their replacements
 247      *
 248      * @param int    $columnIndex index of the column
 249      * @param string $find        string to find in the column
 250      * @param string $replaceWith string to replace with
 251      * @param string $charSet     character set of the connection
 252      *
 253      * @return array|bool Array containing original values, replaced values and count
 254      */
 255     private function _getRegexReplaceRows(
 256         $columnIndex,
 257         $find,
 258         $replaceWith,
 259         $charSet
 260     ) {
 261         $column = $this->_columnNames[$columnIndex];
 262         $sql_query = 'SELECT '
 263             . Util::backquote($column) . ','
 264             . ' 1,' // to add an extra column that will have replaced value
 265             . ' COUNT(*)'
 266             . ' FROM ' . Util::backquote($this->db)
 267             . '.' . Util::backquote($this->table)
 268             . ' WHERE ' . Util::backquote($column)
 269             . " RLIKE '" . $this->dbi->escapeString($find) . "' COLLATE "
 270             . $charSet . '_bin'; // here we
 271         // change the collation of the 2nd operand to a case sensitive
 272         // binary collation to make sure that the comparison is case sensitive
 273         $sql_query .= ' GROUP BY ' . Util::backquote($column)
 274             . ' ORDER BY ' . Util::backquote($column) . ' ASC';
 275
 276         $result = $this->dbi->fetchResult($sql_query, 0);
 277
 278         if (is_array($result)) {
 279             /* Iterate over possible delimiters to get one */
 280             $delimiters = [
 281                 '/',
 282                 '@',
 283                 '#',
 284                 '~',
 285                 '!',
 286                 '$',
 287                 '%',
 288                 '^',
 289                 '&',
 290                 '_',
 291             ];
 292             $found = false;
 293             for ($i = 0, $l = count($delimiters); $i < $l; $i++) {
 294                 if (strpos($find, $delimiters[$i]) === false) {
 295                     $found = true;
 296                     break;
 297                 }
 298             }
 299             if (! $found) {
 300                 return false;
 301             }
 302             $find = $delimiters[$i] . $find . $delimiters[$i];
 303             foreach ($result as $index => $row) {
 304                 $result[$index][1] = preg_replace(
 305                     $find,
 306                     $replaceWith,
 307                     $row[0]
 308                 );
 309             }
 310         }
 311         return $result;
 312     }
 313
 314     /**
 315      * Replaces a given string in a column with a give replacement
 316      *
 317      * @param int    $columnIndex index of the column
 318      * @param string $find        string to find in the column
 319      * @param string $replaceWith string to replace with
 320      * @param bool   $useRegex    to use Regex replace or not
 321      * @param string $charSet     character set of the connection
 322      *
 323      * @return void
 324      */
 325     public function replace(
 326         $columnIndex,
 327         $find,
 328         $replaceWith,
 329         $useRegex,
 330         $charSet
 331     ) {
 332         $column = $this->_columnNames[$columnIndex];
 333         if ($useRegex) {
 334             $toReplace = $this->_getRegexReplaceRows(
 335                 $columnIndex,
 336                 $find,
 337                 $replaceWith,
 338                 $charSet
 339             );
 340             $sql_query = 'UPDATE ' . Util::backquote($this->table)
 341                 . ' SET ' . Util::backquote($column) . ' = CASE';
 342             if (is_array($toReplace)) {
 343                 foreach ($toReplace as $row) {
 344                     $sql_query .= "\n WHEN " . Util::backquote($column)
 345                         . " = '" . $this->dbi->escapeString($row[0])
 346                         . "' THEN '" . $this->dbi->escapeString($row[1]) . "'";
 347                 }
 348             }
 349             $sql_query .= ' END'
 350                 . ' WHERE ' . Util::backquote($column)
 351                 . " RLIKE '" . $this->dbi->escapeString($find) . "' COLLATE "
 352                 . $charSet . '_bin'; // here we
 353             // change the collation of the 2nd operand to a case sensitive
 354             // binary collation to make sure that the comparison
 355             // is case sensitive
 356         } else {
 357             $sql_query = 'UPDATE ' . Util::backquote($this->table)
 358                 . ' SET ' . Util::backquote($column) . ' ='
 359                 . ' REPLACE('
 360                 . Util::backquote($column) . ", '" . $find . "', '"
 361                 . $replaceWith
 362                 . "')"
 363                 . ' WHERE ' . Util::backquote($column)
 364                 . " LIKE '%" . $find . "%' COLLATE " . $charSet . '_bin'; // here we
 365             // change the collation of the 2nd operand to a case sensitive
 366             // binary collation to make sure that the comparison
 367             // is case sensitive
 368         }
 369         $this->dbi->query(
 370             $sql_query,
 371             DatabaseInterface::CONNECT_USER,
 372             DatabaseInterface::QUERY_STORE
 373         );
 374         $GLOBALS['sql_query'] = $sql_query;
 375     }
 376 }