libraries/DbSearch.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Handles Database Search
   5  *
   6  * @package PhpMyAdmin
   7  */
   8 namespace PMA\libraries;
   9
  10 use PMA\libraries\URL;
  11 use PMA\libraries\Sanitize;
  12
  13 /**
  14  * Class to handle database search
  15  *
  16  * @package PhpMyAdmin
  17  */
  18 class DbSearch
  19 {
  20     /**
  21      * Database name
  22      *
  23      * @access private
  24      * @var string
  25      */
  26     private $_db;
  27     /**
  28      * Table Names
  29      *
  30      * @access private
  31      * @var array
  32      */
  33     private $_tables_names_only;
  34     /**
  35      * Type of search
  36      *
  37      * @access private
  38      * @var array
  39      */
  40     private $_searchTypes;
  41     /**
  42      * Already set search type
  43      *
  44      * @access private
  45      * @var integer
  46      */
  47     private $_criteriaSearchType;
  48     /**
  49      * Already set search type's description
  50      *
  51      * @access private
  52      * @var string
  53      */
  54     private $_searchTypeDescription;
  55     /**
  56      * Search string/regexp
  57      *
  58      * @access private
  59      * @var string
  60      */
  61     private $_criteriaSearchString;
  62     /**
  63      * Criteria Tables to search in
  64      *
  65      * @access private
  66      * @var array
  67      */
  68     private $_criteriaTables;
  69     /**
  70      * Restrict the search to this column
  71      *
  72      * @access private
  73      * @var string
  74      */
  75     private $_criteriaColumnName;
  76
  77     /**
  78      * Public Constructor
  79      *
  80      * @param string $db Database name
  81      */
  82     public function __construct($db)
  83     {
  84         $this->_db = $db;
  85         // Sets criteria parameters
  86         $this->_setSearchParams();
  87     }
  88
  89     /**
  90      * Sets search parameters
  91      *
  92      * @return void
  93      */
  94     private function _setSearchParams()
  95     {
  96         $this->_tables_names_only = $GLOBALS['dbi']->getTables($this->_db);
  97
  98         $this->_searchTypes = array(
  99             '1' => __('at least one of the words'),
 100             '2' => __('all words'),
 101             '3' => __('the exact phrase'),
 102             '4' => __('as regular expression'),
 103         );
 104
 105         if (empty($_REQUEST['criteriaSearchType'])
 106             || ! is_string($_REQUEST['criteriaSearchType'])
 107             || ! array_key_exists(
 108                 $_REQUEST['criteriaSearchType'],
 109                 $this->_searchTypes
 110             )
 111         ) {
 112             $this->_criteriaSearchType = 1;
 113             unset($_REQUEST['submit_search']);
 114         } else {
 115             $this->_criteriaSearchType = (int) $_REQUEST['criteriaSearchType'];
 116             $this->_searchTypeDescription
 117                 = $this->_searchTypes[$_REQUEST['criteriaSearchType']];
 118         }
 119
 120         if (empty($_REQUEST['criteriaSearchString'])
 121             || ! is_string($_REQUEST['criteriaSearchString'])
 122         ) {
 123             $this->_criteriaSearchString = '';
 124             unset($_REQUEST['submit_search']);
 125         } else {
 126             $this->_criteriaSearchString = $_REQUEST['criteriaSearchString'];
 127         }
 128
 129         $this->_criteriaTables = array();
 130         if (empty($_REQUEST['criteriaTables'])
 131             || ! is_array($_REQUEST['criteriaTables'])
 132         ) {
 133             unset($_REQUEST['submit_search']);
 134         } else {
 135             $this->_criteriaTables = array_intersect(
 136                 $_REQUEST['criteriaTables'], $this->_tables_names_only
 137             );
 138         }
 139
 140         if (empty($_REQUEST['criteriaColumnName'])
 141             || ! is_string($_REQUEST['criteriaColumnName'])
 142         ) {
 143             unset($this->_criteriaColumnName);
 144         } else {
 145             $this->_criteriaColumnName = $GLOBALS['dbi']->escapeString(
 146                 $_REQUEST['criteriaColumnName']
 147             );
 148         }
 149     }
 150
 151     /**
 152      * Builds the SQL search query
 153      *
 154      * @param string $table The table name
 155      *
 156      * @return array 3 SQL queries (for count, display and delete results)
 157      *
 158      * @todo    can we make use of fulltextsearch IN BOOLEAN MODE for this?
 159      * PMA_backquote
 160      * DatabaseInterface::freeResult
 161      * DatabaseInterface::fetchAssoc
 162      * $GLOBALS['db']
 163      * explode
 164      * count
 165      * strlen
 166      */
 167     private function _getSearchSqls($table)
 168     {
 169         // Statement types
 170         $sqlstr_select = 'SELECT';
 171         $sqlstr_delete = 'DELETE';
 172         // Table to use
 173         $sqlstr_from = ' FROM '
 174             . Util::backquote($GLOBALS['db']) . '.'
 175             . Util::backquote($table);
 176         // Gets where clause for the query
 177         $where_clause = $this->_getWhereClause($table);
 178         // Builds complete queries
 179         $sql = array();
 180         $sql['select_columns'] = $sqlstr_select . ' * ' . $sqlstr_from
 181             . $where_clause;
 182         // here, I think we need to still use the COUNT clause, even for
 183         // VIEWs, anyway we have a WHERE clause that should limit results
 184         $sql['select_count']  = $sqlstr_select . ' COUNT(*) AS `count`'
 185             . $sqlstr_from . $where_clause;
 186         $sql['delete']        = $sqlstr_delete . $sqlstr_from . $where_clause;
 187
 188         return $sql;
 189     }
 190
 191     /**
 192      * Provides where clause for building SQL query
 193      *
 194      * @param string $table The table name
 195      *
 196      * @return string The generated where clause
 197      */
 198     private function _getWhereClause($table)
 199     {
 200         // Columns to select
 201         $allColumns = $GLOBALS['dbi']->getColumns($GLOBALS['db'], $table);
 202         $likeClauses = array();
 203         // Based on search type, decide like/regex & '%'/''
 204         $like_or_regex   = (($this->_criteriaSearchType == 4) ? 'REGEXP' : 'LIKE');
 205         $automatic_wildcard   = (($this->_criteriaSearchType < 3) ? '%' : '');
 206         // For "as regular expression" (search option 4), LIKE won't be used
 207         // Usage example: If user is searching for a literal $ in a regexp search,
 208         // he should enter \$ as the value.
 209         $criteriaSearchStringEscaped = $GLOBALS['dbi']->escapeString(
 210             $this->_criteriaSearchString
 211         );
 212         // Extract search words or pattern
 213         $search_words = (($this->_criteriaSearchType > 2)
 214             ? array($criteriaSearchStringEscaped)
 215             : explode(' ', $criteriaSearchStringEscaped));
 216
 217         foreach ($search_words as $search_word) {
 218             // Eliminates empty values
 219             if (strlen($search_word) === 0) {
 220                 continue;
 221             }
 222             $likeClausesPerColumn = array();
 223             // for each column in the table
 224             foreach ($allColumns as $column) {
 225                 if (! isset($this->_criteriaColumnName)
 226                     || strlen($this->_criteriaColumnName) === 0
 227                     || $column['Field'] == $this->_criteriaColumnName
 228                 ) {
 229                     $column = 'CONVERT(' . Util::backquote($column['Field'])
 230                             . ' USING utf8)';
 231                     $likeClausesPerColumn[] = $column . ' ' . $like_or_regex . ' '
 232                         . "'"
 233                         . $automatic_wildcard . $search_word . $automatic_wildcard
 234                         . "'";
 235                 }
 236             } // end for
 237             if (count($likeClausesPerColumn) > 0) {
 238                 $likeClauses[] = implode(' OR ', $likeClausesPerColumn);
 239             }
 240         } // end for
 241         // Use 'OR' if 'at least one word' is to be searched, else use 'AND'
 242         $implode_str  = ($this->_criteriaSearchType == 1 ? ' OR ' : ' AND ');
 243         if (empty($likeClauses)) {
 244             // this could happen when the "inside column" does not exist
 245             // in any selected tables
 246             $where_clause = ' WHERE FALSE';
 247         } else {
 248             $where_clause = ' WHERE ('
 249                 . implode(') ' . $implode_str . ' (', $likeClauses)
 250                 . ')';
 251         }
 252         return $where_clause;
 253     }
 254
 255     /**
 256      * Displays database search results
 257      *
 258      * @return string HTML for search results
 259      */
 260     public function getSearchResults()
 261     {
 262         $html_output = '';
 263         // Displays search string
 264         $html_output .= '<br />'
 265             . '<table class="data">'
 266             . '<caption class="tblHeaders">'
 267             . sprintf(
 268                 __('Search results for "<i>%s</i>" %s:'),
 269                 htmlspecialchars($this->_criteriaSearchString),
 270                 $this->_searchTypeDescription
 271             )
 272             . '</caption>';
 273
 274         $num_search_result_total = 0;
 275         // For each table selected as search criteria
 276         foreach ($this->_criteriaTables as $each_table) {
 277             // Gets the SQL statements
 278             $newsearchsqls = $this->_getSearchSqls($each_table);
 279             // Executes the "COUNT" statement
 280             $res_cnt = intval($GLOBALS['dbi']->fetchValue($newsearchsqls['select_count']));
 281             $num_search_result_total += $res_cnt;
 282             // Gets the result row's HTML for a table
 283             $html_output .= $this->_getResultsRow(
 284                 $each_table, $newsearchsqls, $res_cnt
 285             );
 286         } // end for
 287         $html_output .= '</table>';
 288         // Displays total number of matches
 289         if (count($this->_criteriaTables) > 1) {
 290             $html_output .= '<p>';
 291             $html_output .= sprintf(
 292                 _ngettext(
 293                     '<b>Total:</b> <i>%s</i> match',
 294                     '<b>Total:</b> <i>%s</i> matches',
 295                     $num_search_result_total
 296                 ),
 297                 $num_search_result_total
 298             );
 299             $html_output .= '</p>';
 300         }
 301         return $html_output;
 302     }
 303
 304     /**
 305      * Provides search results row with browse/delete links.
 306      * (for a table)
 307      *
 308      * @param string  $each_table    One of the tables on which search was performed
 309      * @param array   $newsearchsqls Contains SQL queries
 310      * @param integer $res_cnt       Number of results found
 311      *
 312      * @return string HTML row
 313      */
 314     private function _getResultsRow($each_table, $newsearchsqls, $res_cnt)
 315     {
 316         $this_url_params = array(
 317             'db'    => $GLOBALS['db'],
 318             'table' => $each_table,
 319             'goto'  => 'db_sql.php',
 320             'pos'   => 0,
 321             'is_js_confirmed' => 0,
 322         );
 323         // Start forming search results row
 324         $html_output = '<tr class="noclick">';
 325         // Displays results count for a table
 326         $html_output .= '<td>';
 327         $html_output .= sprintf(
 328             _ngettext(
 329                 '%1$s match in <strong>%2$s</strong>',
 330                 '%1$s matches in <strong>%2$s</strong>', $res_cnt
 331             ),
 332             $res_cnt, htmlspecialchars($each_table)
 333         );
 334         $html_output .= '</td>';
 335         // Displays browse/delete link if result count > 0
 336         if ($res_cnt > 0) {
 337             $this_url_params['db'] = htmlspecialchars($GLOBALS['db']);
 338             $this_url_params['table'] = htmlspecialchars($each_table);
 339             $browse_result_path = 'sql.php' . URL::getCommon($this_url_params);
 340             $html_output .= '<td><a name="browse_search" '
 341                 . ' class="ajax browse_results" href="'
 342                 . $browse_result_path . '" '
 343                 . 'data-browse-sql="'
 344                 . htmlspecialchars($newsearchsqls['select_columns']). '" '
 345                 . 'data-table-name="' . htmlspecialchars($each_table) . '" >'
 346                 . __('Browse') . '</a></td>';
 347
 348             $delete_result_path = $browse_result_path;
 349             $html_output .= '<td><a name="delete_search" class="ajax delete_results"'
 350                 . ' href="' . $delete_result_path . '"'
 351                 . ' data-delete-sql="' . htmlspecialchars($newsearchsqls['delete']) . '"'
 352                 . ' data-table-name="' . htmlspecialchars($each_table) . '" >'
 353                 . __('Delete') . '</a></td>';
 354         } else {
 355             $html_output .= '<td>&nbsp;</td>'
 356                 . '<td>&nbsp;</td>';
 357         }// end if else
 358         $html_output .= '</tr>';
 359         return $html_output;
 360     }
 361
 362     /**
 363      * Provides the main search form's html
 364      *
 365      * @return string HTML for selection form
 366      */
 367     public function getSelectionForm()
 368     {
 369         $html_output = '<a id="db_search"></a>';
 370         $html_output .= '<form id="db_search_form"'
 371             . ' class="ajax lock-page"'
 372             . ' method="post" action="db_search.php" name="db_search">';
 373         $html_output .= URL::getHiddenInputs($GLOBALS['db']);
 374         $html_output .= '<fieldset>';
 375         // set legend caption
 376         $html_output .= '<legend>' . __('Search in database') . '</legend>';
 377         $html_output .= '<table class="formlayout">';
 378         // inputbox for search phrase
 379         $html_output .= '<tr>';
 380         $html_output .= '<td>' . __('Words or values to search for (wildcard: "%"):')
 381             . '</td>';
 382         $html_output .= '<td><input type="text"'
 383             . ' name="criteriaSearchString" size="60"'
 384             . ' value="' . htmlspecialchars($this->_criteriaSearchString) . '" />';
 385         $html_output .= '</td>';
 386         $html_output .= '</tr>';
 387         // choices for types of search
 388         $html_output .= '<tr>';
 389         $html_output .= '<td class="right vtop">' . __('Find:') . '</td>';
 390         $html_output .= '<td>';
 391         $choices = array(
 392             '1' => __('at least one of the words')
 393                 . Util::showHint(
 394                     __('Words are separated by a space character (" ").')
 395                 ),
 396             '2' => __('all words')
 397                 . Util::showHint(
 398                     __('Words are separated by a space character (" ").')
 399                 ),
 400             '3' => __('the exact phrase'),
 401             '4' => __('as regular expression') . ' '
 402                 . Util::showMySQLDocu('Regexp')
 403         );
 404         // 4th parameter set to true to add line breaks
 405         // 5th parameter set to false to avoid htmlspecialchars() escaping
 406         // in the label since we have some HTML in some labels
 407         $html_output .= Util::getRadioFields(
 408             'criteriaSearchType', $choices, $this->_criteriaSearchType, true, false
 409         );
 410         $html_output .= '</td></tr>';
 411         // displays table names as select options
 412         $html_output .= '<tr>';
 413         $html_output .= '<td class="right vtop">' . __('Inside tables:') . '</td>';
 414         $html_output .= '<td rowspan="2">';
 415         $html_output .= '<select name="criteriaTables[]" size="6"'
 416             . ' multiple="multiple">';
 417         foreach ($this->_tables_names_only as $each_table) {
 418             if (in_array($each_table, $this->_criteriaTables)) {
 419                 $is_selected = ' selected="selected"';
 420             } else {
 421                 $is_selected = '';
 422             }
 423             $html_output .= '<option value="' . htmlspecialchars($each_table) . '"'
 424                 . $is_selected . '>'
 425                 . str_replace(' ', '&nbsp;', htmlspecialchars($each_table))
 426                 . '</option>';
 427         } // end for
 428         $html_output .= '</select>';
 429         $html_output .= '</td></tr>';
 430         // Displays 'select all' and 'unselect all' links
 431         $alter_select = '<a href="#" '
 432             . 'onclick="setSelectOptions(\'db_search\','
 433             . ' \'criteriaTables[]\', true); return false;">'
 434             . __('Select all') . '</a> &nbsp;/&nbsp;';
 435         $alter_select .= '<a href="#" '
 436             . 'onclick="setSelectOptions(\'db_search\','
 437             . ' \'criteriaTables[]\', false); return false;">'
 438             . __('Unselect all') . '</a>';
 439         $html_output .= '<tr><td class="right vbottom">'
 440             . $alter_select . '</td></tr>';
 441         // Inputbox for column name entry
 442         $html_output .= '<tr>';
 443         $html_output .= '<td class="right">' . __('Inside column:') . '</td>';
 444         $html_output .= '<td><input type="text" name="criteriaColumnName" size="60"'
 445             . 'value="'
 446             . (! empty($this->_criteriaColumnName)
 447                 ? htmlspecialchars($this->_criteriaColumnName)
 448                 : '')
 449             . '" /></td>';
 450         $html_output .= '</tr>';
 451         $html_output .= '</table>';
 452         $html_output .= '</fieldset>';
 453         $html_output .= '<fieldset class="tblFooters">';
 454         $html_output .= '<input type="submit" name="submit_search" value="'
 455             . __('Go') . '" id="buttonGo" />';
 456         $html_output .= '</fieldset>';
 457         $html_output .= '</form>';
 458         $html_output .= '<div id="togglesearchformdiv">'
 459             . '<a id="togglesearchformlink"></a></div>';
 460
 461         return $html_output;
 462     }
 463
 464     /**
 465      * Provides div tags for browsing search results and sql query form.
 466      *
 467      * @return string div tags
 468      */
 469     public function getResultDivs()
 470     {
 471         $html_output = '<!-- These two table-image and table-link elements display'
 472             . ' the table name in browse search results  -->';
 473         $html_output .= '<div id="table-info">';
 474         $html_output .= '<a class="item" id="table-link" ></a>';
 475         $html_output .= '</div>';
 476         // div for browsing results
 477         $html_output .= '<div id="browse-results">';
 478         $html_output .= '<!-- this browse-results div is used to load the browse'
 479             . ' and delete results in the db search -->';
 480         $html_output .= '</div>';
 481         $html_output .= '<br class="clearfloat" />';
 482         $html_output .= '<div id="sqlqueryform">';
 483         $html_output .= '<!-- this sqlqueryform div is used to load the delete'
 484             . ' form in the db search -->';
 485         $html_output .= '</div>';
 486         $html_output .= '<!--  toggle query box link-->';
 487         $html_output .= '<a id="togglequerybox"></a>';
 488         return $html_output;
 489     }
 490 }