phpmyadmin/libraries/DbSearch.class.php

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