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 = $GLOBALS['dbi']->getTables($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(
 107                 $_REQUEST['criteriaSearchType'],
 108                 $this->_searchTypes
 109             )
 110         ) {
 111             $this->_criteriaSearchType = 1;
 112             unset($_REQUEST['submit_search']);
 113         } else {
 114             $this->_criteriaSearchType = (int) $_REQUEST['criteriaSearchType'];
 115             $this->_searchTypeDescription
 116                 = $this->_searchTypes[$_REQUEST['criteriaSearchType']];
 117         }
 118
 119         if (empty($_REQUEST['criteriaSearchString'])
 120             || ! is_string($_REQUEST['criteriaSearchString'])
 121         ) {
 122             $this->_criteriaSearchString = '';
 123             unset($_REQUEST['submit_search']);
 124         } else {
 125             $this->_criteriaSearchString = $_REQUEST['criteriaSearchString'];
 126         }
 127
 128         $this->_criteriaTables = array();
 129         if (empty($_REQUEST['criteriaTables'])
 130             || ! is_array($_REQUEST['criteriaTables'])
 131         ) {
 132             unset($_REQUEST['submit_search']);
 133         } else {
 134             $this->_criteriaTables = array_intersect(
 135                 $_REQUEST['criteriaTables'], $this->_tables_names_only
 136             );
 137         }
 138
 139         if (empty($_REQUEST['criteriaColumnName'])
 140             || ! is_string($_REQUEST['criteriaColumnName'])
 141         ) {
 142             unset($this->_criteriaColumnName);
 143         } else {
 144             $this->_criteriaColumnName = PMA_Util::sqlAddSlashes(
 145                 $_REQUEST['criteriaColumnName'], true
 146             );
 147         }
 148     }
 149
 150     /**
 151      * Builds the SQL search query
 152      *
 153      * @param string $table The table name
 154      *
 155      * @return array 3 SQL queries (for count, display and delete results)
 156      *
 157      * @todo    can we make use of fulltextsearch IN BOOLEAN MODE for this?
 158      * PMA_backquote
 159      * DatabaseInterface::freeResult
 160      * DatabaseInterface::fetchAssoc
 161      * $GLOBALS['db']
 162      * explode
 163      * count
 164      * strlen
 165      */
 166     private function _getSearchSqls($table)
 167     {
 168         // Statement types
 169         $sqlstr_select = 'SELECT';
 170         $sqlstr_delete = 'DELETE';
 171         // Table to use
 172         $sqlstr_from = ' FROM '
 173             . PMA_Util::backquote($GLOBALS['db']) . '.'
 174             . PMA_Util::backquote($table);
 175         // Gets where clause for the query
 176         $where_clause = $this->_getWhereClause($table);
 177         // Builds complete queries
 178         $sql = array();
 179         $sql['select_columns'] = $sqlstr_select . ' * ' . $sqlstr_from
 180             . $where_clause;
 181         // here, I think we need to still use the COUNT clause, even for
 182         // VIEWs, anyway we have a WHERE clause that should limit results
 183         $sql['select_count']  = $sqlstr_select . ' COUNT(*) AS `count`'
 184             . $sqlstr_from . $where_clause;
 185         $sql['delete']        = $sqlstr_delete . $sqlstr_from . $where_clause;
 186
 187         return $sql;
 188     }
 189
 190     /**
 191      * Provides where clause for building SQL query
 192      *
 193      * @param string $table The table name
 194      *
 195      * @return string The generated where clause
 196      */
 197     private function _getWhereClause($table)
 198     {
 199         // Columns to select
 200         $allColumns = $GLOBALS['dbi']->getColumns($GLOBALS['db'], $table);
 201         $likeClauses = array();
 202         // Based on search type, decide like/regex & '%'/''
 203         $like_or_regex   = (($this->_criteriaSearchType == 4) ? 'REGEXP' : 'LIKE');
 204         $automatic_wildcard   = (($this->_criteriaSearchType < 3) ? '%' : '');
 205         // For "as regular expression" (search option 4), LIKE won't be used
 206         // Usage example: If user is searching for a literal $ in a regexp search,
 207         // he should enter \$ as the value.
 208         $criteriaSearchStringEscaped = PMA_Util::sqlAddSlashes(
 209             $this->_criteriaSearchString,
 210             ($this->_criteriaSearchType == 4 ? false : true)
 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 (/*overload*/mb_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                     || /*overload*/mb_strlen($this->_criteriaColumnName) == 0
 227                     || $column['Field'] == $this->_criteriaColumnName
 228                 ) {
 229                     // Drizzle has no CONVERT and all text columns are UTF-8
 230                     $column = ((PMA_DRIZZLE)
 231                         ? PMA_Util::backquote($column['Field'])
 232                         : 'CONVERT(' . PMA_Util::backquote($column['Field'])
 233                             . ' USING utf8)');
 234                     $likeClausesPerColumn[] = $column . ' ' . $like_or_regex . ' '
 235                         . "'"
 236                         . $automatic_wildcard . $search_word . $automatic_wildcard
 237                         . "'";
 238                 }
 239             } // end for
 240             if (count($likeClausesPerColumn) > 0) {
 241                 $likeClauses[] = implode(' OR ', $likeClausesPerColumn);
 242             }
 243         } // end for
 244         // Use 'OR' if 'at least one word' is to be searched, else use 'AND'
 245         $implode_str  = ($this->_criteriaSearchType == 1 ? ' OR ' : ' AND ');
 246         if (empty($likeClauses)) {
 247             // this could happen when the "inside column" does not exist
 248             // in any selected tables
 249             $where_clause = ' WHERE FALSE';
 250         } else {
 251             $where_clause = ' WHERE ('
 252                 . implode(') ' . $implode_str . ' (', $likeClauses)
 253                 . ')';
 254         }
 255         return $where_clause;
 256     }
 257
 258     /**
 259      * Displays database search results
 260      *
 261      * @return string HTML for search results
 262      */
 263     public function getSearchResults()
 264     {
 265         $html_output = '';
 266         // Displays search string
 267         $html_output .= '<br />'
 268             . '<table class="data">'
 269             . '<caption class="tblHeaders">'
 270             . sprintf(
 271                 __('Search results for "<i>%s</i>" %s:'),
 272                 htmlspecialchars($this->_criteriaSearchString),
 273                 $this->_searchTypeDescription
 274             )
 275             . '</caption>';
 276
 277         $num_search_result_total = 0;
 278         $odd_row = true;
 279         // For each table selected as search criteria
 280         foreach ($this->_criteriaTables as $each_table) {
 281             // Gets the SQL statements
 282             $newsearchsqls = $this->_getSearchSqls($each_table);
 283             // Executes the "COUNT" statement
 284             $res_cnt = $GLOBALS['dbi']->fetchValue($newsearchsqls['select_count']);
 285             $num_search_result_total += $res_cnt;
 286             // Gets the result row's HTML for a table
 287             $html_output .= $this->_getResultsRow(
 288                 $each_table, $newsearchsqls, $odd_row, $res_cnt
 289             );
 290             $odd_row = ! $odd_row;
 291         } // end for
 292         $html_output .= '</table>';
 293         // Displays total number of matches
 294         if (count($this->_criteriaTables) > 1) {
 295             $html_output .= '<p>';
 296             $html_output .= sprintf(
 297                 _ngettext(
 298                     '<b>Total:</b> <i>%s</i> match',
 299                     '<b>Total:</b> <i>%s</i> matches',
 300                     $num_search_result_total
 301                 ),
 302                 $num_search_result_total
 303             );
 304             $html_output .= '</p>';
 305         }
 306         return $html_output;
 307     }
 308
 309     /**
 310      * Provides search results row with browse/delete links.
 311      * (for a table)
 312      *
 313      * @param string  $each_table    One of the tables on which search was performed
 314      * @param array   $newsearchsqls Contains SQL queries
 315      * @param bool    $odd_row       For displaying contrasting table rows
 316      * @param integer $res_cnt       Number of results found
 317      *
 318      * @return string HTML row
 319      */
 320     private function _getResultsRow($each_table, $newsearchsqls, $odd_row, $res_cnt)
 321     {
 322         $this_url_params = array(
 323             'db'    => $GLOBALS['db'],
 324             'table' => $each_table,
 325             'goto'  => 'db_sql.php',
 326             'pos'   => 0,
 327             'is_js_confirmed' => 0,
 328         );
 329         // Start forming search results row
 330         $html_output = '<tr class="noclick ' . ($odd_row ? 'odd' : 'even') . '">';
 331         // Displays results count for a table
 332         $html_output .= '<td>';
 333         $html_output .= sprintf(
 334             _ngettext(
 335                 '%1$s match in <strong>%2$s</strong>',
 336                 '%1$s matches in <strong>%2$s</strong>', $res_cnt
 337             ),
 338             $res_cnt, htmlspecialchars($each_table)
 339         );
 340         $html_output .= '</td>';
 341         // Displays browse/delete link if result count > 0
 342         if ($res_cnt > 0) {
 343             $this_url_params['sql_query'] = $newsearchsqls['select_columns'];
 344             $browse_result_path = 'sql.php' . PMA_URL_getCommon($this_url_params);
 345             $html_output .= '<td><a name="browse_search" class="ajax" href="'
 346                 . $browse_result_path . '" onclick="loadResult(\''
 347                 . $browse_result_path . '\',\'' . $each_table . '\',\''
 348                 . PMA_URL_getCommon(
 349                     array(
 350                         'db' => $GLOBALS['db'], 'table' => $each_table
 351                     )
 352                 ) . '\''
 353                 . ');return false;" >'
 354                 . __('Browse') . '</a></td>';
 355             $this_url_params['sql_query'] = $newsearchsqls['delete'];
 356             $delete_result_path = 'sql.php' . PMA_URL_getCommon($this_url_params);
 357             $html_output .= '<td><a name="delete_search" class="ajax" href="'
 358                 . $delete_result_path . '" onclick="deleteResult(\''
 359                 . $delete_result_path . '\' , \''
 360                 . sprintf(
 361                     __('Delete the matches for the %s table?'),
 362                     htmlspecialchars($each_table)
 363                 )
 364                 . '\');return false;">'
 365                 . __('Delete') . '</a></td>';
 366         } else {
 367             $html_output .= '<td>&nbsp;</td>'
 368                 . '<td>&nbsp;</td>';
 369         }// end if else
 370         $html_output .= '</tr>';
 371         return $html_output;
 372     }
 373
 374     /**
 375      * Provides the main search form's html
 376      *
 377      * @return string HTML for selection form
 378      */
 379     public function getSelectionForm()
 380     {
 381         $html_output = '<a id="db_search"></a>';
 382         $html_output .= '<form id="db_search_form"'
 383             . ' class="ajax lock-page"'
 384             . ' method="post" action="db_search.php" name="db_search">';
 385         $html_output .= PMA_URL_getHiddenInputs($GLOBALS['db']);
 386         $html_output .= '<fieldset>';
 387         // set legend caption
 388         $html_output .= '<legend>' . __('Search in database') . '</legend>';
 389         $html_output .= '<table class="formlayout">';
 390         // inputbox for search phrase
 391         $html_output .= '<tr>';
 392         $html_output .= '<td>' . __('Words or values to search for (wildcard: "%"):')
 393             . '</td>';
 394         $html_output .= '<td><input type="text"'
 395             . ' name="criteriaSearchString" size="60"'
 396             . ' value="' . htmlspecialchars($this->_criteriaSearchString) . '" />';
 397         $html_output .= '</td>';
 398         $html_output .= '</tr>';
 399         // choices for types of search
 400         $html_output .= '<tr>';
 401         $html_output .= '<td class="right vtop">' . __('Find:') . '</td>';
 402         $html_output .= '<td>';
 403         $choices = array(
 404             '1' => __('at least one of the words')
 405                 . PMA_Util::showHint(
 406                     __('Words are separated by a space character (" ").')
 407                 ),
 408             '2' => __('all words')
 409                 . PMA_Util::showHint(
 410                     __('Words are separated by a space character (" ").')
 411                 ),
 412             '3' => __('the exact phrase'),
 413             '4' => __('as regular expression') . ' '
 414                 . PMA_Util::showMySQLDocu('Regexp')
 415         );
 416         // 4th parameter set to true to add line breaks
 417         // 5th parameter set to false to avoid htmlspecialchars() escaping
 418         // in the label since we have some HTML in some labels
 419         $html_output .= PMA_Util::getRadioFields(
 420             'criteriaSearchType', $choices, $this->_criteriaSearchType, true, false
 421         );
 422         $html_output .= '</td></tr>';
 423         // displays table names as select options
 424         $html_output .= '<tr>';
 425         $html_output .= '<td class="right vtop">' . __('Inside tables:') . '</td>';
 426         $html_output .= '<td rowspan="2">';
 427         $html_output .= '<select name="criteriaTables[]" size="6"'
 428             . ' multiple="multiple">';
 429         foreach ($this->_tables_names_only as $each_table) {
 430             if (in_array($each_table, $this->_criteriaTables)) {
 431                 $is_selected = ' selected="selected"';
 432             } else {
 433                 $is_selected = '';
 434             }
 435             $html_output .= '<option value="' . htmlspecialchars($each_table) . '"'
 436                 . $is_selected . '>'
 437                 . str_replace(' ', '&nbsp;', htmlspecialchars($each_table))
 438                 . '</option>';
 439         } // end for
 440         $html_output .= '</select>';
 441         $html_output .= '</td></tr>';
 442         // Displays 'select all' and 'unselect all' links
 443         $alter_select = '<a href="#" '
 444             . 'onclick="setSelectOptions(\'db_search\','
 445             . ' \'criteriaTables[]\', true); return false;">'
 446             . __('Select all') . '</a> &nbsp;/&nbsp;';
 447         $alter_select .= '<a href="#" '
 448             . 'onclick="setSelectOptions(\'db_search\','
 449             . ' \'criteriaTables[]\', false); return false;">'
 450             . __('Unselect all') . '</a>';
 451         $html_output .= '<tr><td class="right vbottom">'
 452             . $alter_select . '</td></tr>';
 453         // Inputbox for column name entry
 454         $html_output .= '<tr>';
 455         $html_output .= '<td class="right">' . __('Inside column:') . '</td>';
 456         $html_output .= '<td><input type="text" name="criteriaColumnName" size="60"'
 457             . 'value="'
 458             . (! empty($this->_criteriaColumnName)
 459                 ? htmlspecialchars($this->_criteriaColumnName)
 460                 : '')
 461             . '" /></td>';
 462         $html_output .= '</tr>';
 463         $html_output .= '</table>';
 464         $html_output .= '</fieldset>';
 465         $html_output .= '<fieldset class="tblFooters">';
 466         $html_output .= '<input type="submit" name="submit_search" value="'
 467             . __('Go') . '" id="buttonGo" />';
 468         $html_output .= '</fieldset>';
 469         $html_output .= '</form>';
 470         $html_output .= '<div id="togglesearchformdiv">'
 471             . '<a id="togglesearchformlink"></a></div>';
 472
 473         return $html_output;
 474     }
 475
 476     /**
 477      * Provides div tags for browsing search results and sql query form.
 478      *
 479      * @return string div tags
 480      */
 481     public function getResultDivs()
 482     {
 483         $html_output = '<!-- These two table-image and table-link elements display'
 484             . ' the table name in browse search results  -->';
 485         $html_output .= '<div id="table-info">';
 486         $html_output .= '<a class="item" id="table-link" ></a>';
 487         $html_output .= '</div>';
 488         // div for browsing results
 489         $html_output .= '<div id="browse-results">';
 490         $html_output .= '<!-- this browse-results div is used to load the browse'
 491             . ' and delete results in the db search -->';
 492         $html_output .= '</div>';
 493         $html_output .= '<br class="clearfloat" />';
 494         $html_output .= '<div id="sqlqueryform">';
 495         $html_output .= '<!-- this sqlqueryform div is used to load the delete'
 496             . ' form in the db search -->';
 497         $html_output .= '</div>';
 498         $html_output .= '<!--  toggle query box link-->';
 499         $html_output .= '<a id="togglequerybox"></a>';
 500         return $html_output;
 501     }
 502 }