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 querys (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['select_columns'] = $sqlstr_select . ' * ' . $sqlstr_from
 179             . $where_clause;
 180         // here, I think we need to still use the COUNT clause, even for
 181         // VIEWs, anyway we have a WHERE clause that should limit results
 182         $sql['select_count']  = $sqlstr_select . ' COUNT(*) AS `count`'
 183             . $sqlstr_from . $where_clause;
 184         $sql['delete']        = $sqlstr_delete . $sqlstr_from . $where_clause;
 185
 186         return $sql;
 187     }
 188
 189     /**
 190      * Provides where clause for bulding SQL query
 191      *
 192      * @param string $table The table name
 193      *
 194      * @return string The generated where clause
 195      */
 196     private function _getWhereClause($table)
 197     {
 198         $where_clause = '';
 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 seaching for a literal $ in a regexp search,
 207         // he should enter \$ as the value.
 208         $this->_criteriaSearchString = 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($this->_criteriaSearchString)
 215             : explode(' ', $this->_criteriaSearchString));
 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                     // 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" href="'
 346                 . $browse_result_path . '" onclick="loadResult(\''
 347                 . $browse_result_path . '\',\'' . $each_table . '\',\''
 348                 . PMA_URL_getCommon($GLOBALS['db'], $each_table) . '\''
 349                 . ');return false;" >'
 350                 . __('Browse') . '</a></td>';
 351             $this_url_params['sql_query'] = $newsearchsqls['delete'];
 352             $delete_result_path = 'sql.php' . PMA_URL_getCommon($this_url_params);
 353             $html_output .= '<td><a name="delete_search" href="'
 354                 . $delete_result_path . '" onclick="deleteResult(\''
 355                 . $delete_result_path . '\' , \''
 356                 . sprintf(
 357                     __('Delete the matches for the %s table?'),
 358                     htmlspecialchars($each_table)
 359                 )
 360                 . '\');return false;">'
 361                 . __('Delete') . '</a></td>';
 362         } else {
 363             $html_output .= '<td>&nbsp;</td>'
 364                 .'<td>&nbsp;</td>';
 365         }// end if else
 366         $html_output .= '</tr>';
 367         return $html_output;
 368     }
 369
 370     /**
 371      * Provides the main search form's html
 372      *
 373      * @param array $url_params URL parameters
 374      *
 375      * @return string HTML for selection form
 376      */
 377     public function getSelectionForm($url_params)
 378     {
 379         $html_output = '<a id="db_search"></a>';
 380         $html_output .= '<form id="db_search_form"'
 381             . ' class="ajax"'
 382             . ' method="post" action="db_search.php" name="db_search">';
 383         $html_output .= PMA_URL_getHiddenInputs($GLOBALS['db']);
 384         $html_output .= '<fieldset>';
 385         // set legend caption
 386         $html_output .= '<legend>' . __('Search in database') . '</legend>';
 387         $html_output .= '<table class="formlayout">';
 388         // inputbox for search phrase
 389         $html_output .= '<tr>';
 390         $html_output .= '<td>' . __('Words or values to search for (wildcard: "%"):')
 391             . '</td>';
 392         $html_output .= '<td><input type="text"'
 393             . ' name="criteriaSearchString" size="60"'
 394             . ' value="' . htmlspecialchars($this->_criteriaSearchString) . '" />';
 395         $html_output .= '</td>';
 396         $html_output .= '</tr>';
 397         // choices for types of search
 398         $html_output .= '<tr>';
 399         $html_output .= '<td class="right vtop">' . __('Find:') . '</td>';
 400         $html_output .= '<td>';
 401         $choices = array(
 402             '1' => __('at least one of the words')
 403                 . PMA_Util::showHint(
 404                     __('Words are separated by a space character (" ").')
 405                 ),
 406             '2' => __('all words')
 407                 . PMA_Util::showHint(
 408                     __('Words are separated by a space character (" ").')
 409                 ),
 410             '3' => __('the exact phrase'),
 411             '4' => __('as regular expression') . ' '
 412                 . PMA_Util::showMySQLDocu('Regexp')
 413         );
 414         // 4th parameter set to true to add line breaks
 415         // 5th parameter set to false to avoid htmlspecialchars() escaping
 416         // in the label since we have some HTML in some labels
 417         $html_output .= PMA_Util::getRadioFields(
 418             'criteriaSearchType', $choices, $this->_criteriaSearchType, true, false
 419         );
 420         $html_output .= '</td></tr>';
 421         // displays table names as select options
 422         $html_output .= '<tr>';
 423         $html_output .= '<td class="right vtop">' . __('Inside tables:') . '</td>';
 424         $html_output .= '<td rowspan="2">';
 425         $html_output .= '<select name="criteriaTables[]" size="6"'
 426             . ' multiple="multiple">';
 427         foreach ($this->_tables_names_only as $each_table) {
 428             if (in_array($each_table, $this->_criteriaTables)) {
 429                 $is_selected = ' selected="selected"';
 430             } else {
 431                 $is_selected = '';
 432             }
 433             $html_output .= '<option value="' . htmlspecialchars($each_table) . '"'
 434                 . $is_selected . '>'
 435                 . str_replace(' ', '&nbsp;', htmlspecialchars($each_table))
 436                 . '</option>';
 437         } // end for
 438         $html_output .= '</select>';
 439         $html_output .= '</td></tr>';
 440         // Displays 'select all' and 'unselect all' links
 441         $alter_select = '<a href="#" '
 442             . 'onclick="setSelectOptions(\'db_search\','
 443             . ' \'criteriaTables[]\', true); return false;">'
 444             . __('Select All') . '</a> &nbsp;/&nbsp;';
 445         $alter_select .= '<a href="#" '
 446             . 'onclick="setSelectOptions(\'db_search\','
 447             . ' \'criteriaTables[]\', false); return false;">'
 448             . __('Unselect All') . '</a>';
 449         $html_output .= '<tr><td class="right vbottom">'
 450             . $alter_select . '</td></tr>';
 451         // Inputbox for column name entry
 452         $html_output .= '<tr>';
 453         $html_output .= '<td class="right">' . __('Inside column:') . '</td>';
 454         $html_output .= '<td><input type="text" name="criteriaColumnName" size="60"'
 455             . 'value="'
 456             . (! empty($this->_criteriaColumnName)
 457                 ? htmlspecialchars($this->_criteriaColumnName)
 458                 : '')
 459             . '" /></td>';
 460         $html_output .= '</tr>';
 461         $html_output .= '</table>';
 462         $html_output .= '</fieldset>';
 463         $html_output .= '<fieldset class="tblFooters">';
 464         $html_output .= '<input type="submit" name="submit_search" value="'
 465             . __('Go') . '" id="buttonGo" />';
 466         $html_output .= '</fieldset>';
 467         $html_output .= '</form>';
 468         $html_output .= $this->_getResultDivs();
 469
 470         return $html_output;
 471     }
 472
 473     /**
 474      * Provides div tags for browsing search results and sql query form.
 475      *
 476      * @return string div tags
 477      */
 478     private function _getResultDivs()
 479     {
 480         $html_output = '<!-- These two table-image and table-link elements display'
 481             . ' the table name in browse search results  -->';
 482         $html_output .= '<div id="table-info">';
 483         $html_output .= '<a class="item" id="table-link" ></a>';
 484         $html_output .= '</div>';
 485         // div for browsing results
 486         $html_output .= '<div id="browse-results">';
 487         $html_output .= '<!-- this browse-results div is used to load the browse'
 488             . ' and delete results in the db search -->';
 489         $html_output .= '</div>';
 490         $html_output .= '<br class="clearfloat" />';
 491         $html_output .= '<div id="sqlqueryform">';
 492         $html_output .= '<!-- this sqlqueryform div is used to load the delete'
 493             . ' form in the db search -->';
 494         $html_output .= '</div>';
 495         $html_output .= '<!--  toggle query box link-->';
 496         $html_output .= '<a id="togglequerybox"></a>';
 497         return $html_output;
 498     }
 499 }