db_search.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * searchs the entire database
   5  *
   6  * @todo    make use of UNION when searching multiple tables
   7  * @todo    display executed query, optional?
   8  * @uses    $cfg['UseDbSearch']
   9  * @uses    $GLOBALS['db']
  10  * @uses    PMA_DBI_get_tables()
  11  * @uses    PMA_sqlAddslashes()
  12  * @uses    PMA_getSearchSqls()
  13  * @uses    PMA_DBI_fetch_value()
  14  * @uses    PMA_linkOrButton()
  15  * @uses    PMA_generate_common_url()
  16  * @uses    PMA_generate_common_hidden_inputs()
  17  * @uses    PMA_showMySQLDocu()
  18  * @uses    $_REQUEST['search_str']
  19  * @uses    $_REQUEST['submit_search']
  20  * @uses    $_REQUEST['search_option']
  21  * @uses    $_REQUEST['table_select']
  22  * @uses    $_REQUEST['unselectall']
  23  * @uses    $_REQUEST['selectall']
  24  * @uses    $_REQUEST['field_str']
  25  * @uses    is_string()
  26  * @uses    htmlspecialchars()
  27  * @uses    array_key_exists()
  28  * @uses    is_array()
  29  * @uses    array_intersect()
  30  * @uses    sprintf()
  31  * @uses    in_array()
  32  * @package phpMyAdmin
  33  */
  34
  35 /**
  36  *
  37  */
  38 require_once './libraries/common.inc.php';
  39
  40 $GLOBALS['js_include'][] = 'jquery/jquery-1.4.2.js';
  41 $GLOBALS['js_include'][] = 'db_search.js';
  42
  43 /**
  44  * Gets some core libraries and send headers
  45  */
  46 require './libraries/db_common.inc.php';
  47
  48 /**
  49  * init
  50  */
  51 // If config variable $GLOBALS['cfg']['Usedbsearch'] is on false : exit.
  52 if (! $GLOBALS['cfg']['UseDbSearch']) {
  53     PMA_mysqlDie(__('Access denied'), '', false, $err_url);
  54 } // end if
  55 $url_query .= '&amp;goto=db_search.php';
  56 $url_params['goto'] = 'db_search.php';
  57
  58 /**
  59  * @global array list of tables from the current database
  60  * but do not clash with $tables coming from db_info.inc.php
  61  */
  62 $tables_names_only = PMA_DBI_get_tables($GLOBALS['db']);
  63
  64 $search_options = array(
  65     '1' => __('at least one of the words'),
  66     '2' => __('all words'),
  67     '3' => __('the exact phrase'),
  68     '4' => __('as regular expression'),
  69 );
  70
  71 if (empty($_REQUEST['search_option']) || ! is_string($_REQUEST['search_option'])
  72  || ! array_key_exists($_REQUEST['search_option'], $search_options)) {
  73     $search_option = 1;
  74     unset($_REQUEST['submit_search']);
  75 } else {
  76     $search_option = (int) $_REQUEST['search_option'];
  77     $option_str = $search_options[$_REQUEST['search_option']];
  78 }
  79
  80 if (empty($_REQUEST['search_str']) || ! is_string($_REQUEST['search_str'])) {
  81     unset($_REQUEST['submit_search']);
  82     $searched = '';
  83 } else {
  84     $searched = htmlspecialchars($_REQUEST['search_str']);
  85     // For "as regular expression" (search option 4), we should not treat
  86     // this as an expression that contains a LIKE (second parameter of
  87     // PMA_sqlAddslashes()).
  88     //
  89     // Usage example: If user is seaching for a literal $ in a regexp search,
  90     // he should enter \$ as the value.
  91     $search_str = PMA_sqlAddslashes($_REQUEST['search_str'], ($search_option == 4 ? false : true));
  92 }
  93
  94 $tables_selected = array();
  95 if (empty($_REQUEST['table_select']) || ! is_array($_REQUEST['table_select'])) {
  96     unset($_REQUEST['submit_search']);
  97 } elseif (! isset($_REQUEST['selectall']) && ! isset($_REQUEST['unselectall'])) {
  98     $tables_selected = array_intersect($_REQUEST['table_select'], $tables_names_only);
  99 }
 100
 101 if (isset($_REQUEST['selectall'])) {
 102     $tables_selected = $tables_names_only;
 103 } elseif (isset($_REQUEST['unselectall'])) {
 104     $tables_selected = array();
 105 }
 106
 107 if (empty($_REQUEST['field_str']) || ! is_string($_REQUEST['field_str'])) {
 108     unset($field_str);
 109 } else {
 110     $field_str = PMA_sqlAddslashes($_REQUEST['field_str'], true);
 111 }
 112
 113 /**
 114  * Displays top links if we are not in an Ajax request
 115  */
 116 $sub_part = '';
 117
 118 if( $GLOBALS['is_ajax_request'] != true) {
 119     require './libraries/db_info.inc.php';
 120     echo '<div id="searchresults">';
 121 }
 122
 123 /**
 124  * 1. Main search form has been submitted
 125  */
 126 if (isset($_REQUEST['submit_search'])) {
 127
 128     /**
 129      * Builds the SQL search query
 130      *
 131      * @todo    can we make use of fulltextsearch IN BOOLEAN MODE for this?
 132      * @uses    PMA_DBI_query
 133      * PMA_backquote
 134      * PMA_DBI_free_result
 135      * PMA_DBI_fetch_assoc
 136      * $GLOBALS['db']
 137      * explode
 138      * count
 139      * strlen
 140      * @param   string   the table name
 141      * @param   string   restrict the search to this field
 142      * @param   string   the string to search
 143      * @param   integer  type of search (1 -> 1 word at least, 2 -> all words,
 144      *                                   3 -> exact string, 4 -> regexp)
 145      *
 146      * @return  array    3 SQL querys (for count, display and delete results)
 147      *
 148      * @global  string   the url to return to in case of errors
 149      * @global  string   charset connection
 150      */
 151     function PMA_getSearchSqls($table, $field, $search_str, $search_option)
 152     {
 153         global $err_url;
 154
 155         // Statement types
 156         $sqlstr_select = 'SELECT';
 157         $sqlstr_delete = 'DELETE';
 158
 159         // Fields to select
 160         $tblfields = PMA_DBI_fetch_result('SHOW FIELDS FROM ' . PMA_backquote($table) . ' FROM ' . PMA_backquote($GLOBALS['db']),
 161             null, 'Field');
 162
 163         // Table to use
 164         $sqlstr_from = ' FROM ' . PMA_backquote($GLOBALS['db']) . '.' . PMA_backquote($table);
 165
 166         $search_words    = (($search_option > 2) ? array($search_str) : explode(' ', $search_str));
 167         $search_wds_cnt  = count($search_words);
 168
 169         $like_or_regex   = (($search_option == 4) ? 'REGEXP' : 'LIKE');
 170         $automatic_wildcard   = (($search_option < 3) ? '%' : '');
 171
 172         $fieldslikevalues = array();
 173         foreach ($search_words as $search_word) {
 174             // Eliminates empty values
 175             if (strlen($search_word) === 0) {
 176                 continue;
 177             }
 178
 179             $thefieldlikevalue = array();
 180             foreach ($tblfields as $tblfield) {
 181                 if (! isset($field) || strlen($field) == 0 || $tblfield == $field) {
 182                     $thefieldlikevalue[] = PMA_backquote($tblfield)
 183                                          . ' ' . $like_or_regex . ' '
 184                                          . "'" . $automatic_wildcard
 185                                          . $search_word
 186                                          . $automatic_wildcard . "'";
 187                 }
 188             } // end for
 189
 190             if (count($thefieldlikevalue) > 0) {
 191                 $fieldslikevalues[]      = implode(' OR ', $thefieldlikevalue);
 192             }
 193         } // end for
 194
 195         $implode_str  = ($search_option == 1 ? ' OR ' : ' AND ');
 196         if ( empty($fieldslikevalues)) {
 197             // this could happen when the "inside field" does not exist
 198             // in any selected tables
 199             $sqlstr_where = ' WHERE FALSE';
 200         } else {
 201             $sqlstr_where = ' WHERE (' . implode(') ' . $implode_str . ' (', $fieldslikevalues) . ')';
 202         }
 203         unset($fieldslikevalues);
 204
 205         // Builds complete queries
 206         $sql['select_fields'] = $sqlstr_select . ' * ' . $sqlstr_from . $sqlstr_where;
 207         // here, I think we need to still use the COUNT clause, even for
 208         // VIEWs, anyway we have a WHERE clause that should limit results
 209         $sql['select_count']  = $sqlstr_select . ' COUNT(*) AS `count`' . $sqlstr_from . $sqlstr_where;
 210         $sql['delete']        = $sqlstr_delete . $sqlstr_from . $sqlstr_where;
 211
 212         return $sql;
 213     } // end of the "PMA_getSearchSqls()" function
 214
 215
 216     /**
 217      * Displays the results
 218      */
 219     $this_url_params = array(
 220         'db'    => $GLOBALS['db'],
 221         'goto'  => 'db_sql.php',
 222         'pos'   => 0,
 223         'is_js_confirmed' => 0,
 224     );
 225
 226     // Displays search string
 227     echo '<br />' . "\n"
 228         .'<table class="data">' . "\n"
 229         .'<caption class="tblHeaders">' . "\n"
 230         .sprintf(__('Search results for "<i>%s</i>" %s:'),
 231             $searched, $option_str) . "\n"
 232         .'</caption>' . "\n";
 233
 234     $num_search_result_total = 0;
 235     $odd_row = true;
 236
 237     foreach ($tables_selected as $each_table) {
 238         // Gets the SQL statements
 239         $newsearchsqls = PMA_getSearchSqls($each_table, (! empty($field_str) ? $field_str : ''), $search_str, $search_option);
 240
 241         // Executes the "COUNT" statement
 242         $res_cnt = PMA_DBI_fetch_value($newsearchsqls['select_count']);
 243         $num_search_result_total += $res_cnt;
 244
 245         $sql_query .= $newsearchsqls['select_count'];
 246
 247         echo '<tr class="' . ($odd_row ? 'odd' : 'even') . '">'
 248             .'<td>' . sprintf(_ngettext('%s match inside table <i>%s</i>', '%s matches inside table <i>%s</i>', $res_cnt), $res_cnt,
 249                 htmlspecialchars($each_table)) . "</td>\n";
 250
 251         if ($res_cnt > 0) {
 252             $this_url_params['sql_query'] = $newsearchsqls['select_fields'];
 253             echo '<td>' . PMA_linkOrButton(
 254                     'sql.php' . PMA_generate_common_url($this_url_params),
 255                     __('Browse'), '') .  "</td>\n";
 256
 257             $this_url_params['sql_query'] = $newsearchsqls['delete'];
 258             echo '<td>' . PMA_linkOrButton(
 259                     'sql.php' . PMA_generate_common_url($this_url_params),
 260                     __('Delete'), $newsearchsqls['delete']) .  "</td>\n";
 261
 262         } else {
 263             echo '<td>&nbsp;</td>' . "\n"
 264                 .'<td>&nbsp;</td>' . "\n";
 265         }// end if else
 266         $odd_row = ! $odd_row;
 267         echo '</tr>' . "\n";
 268     } // end for
 269
 270     echo '</table>' . "\n";
 271
 272     if (count($tables_selected) > 1) {
 273         echo '<p>' . sprintf(_ngettext('<b>Total:</b> <i>%s</i> match', '<b>Total:</b> <i>%s</i> matches', $num_search_result_total),
 274             $num_search_result_total) . '</p>' . "\n";
 275     }
 276 } // end 1.
 277
 278 /**
 279  * If we are in an Ajax request, we need to exit after displaying all the HTML
 280  */
 281 if($GLOBALS['is_ajax_request'] == true) {
 282     exit;
 283 }
 284 else {
 285     echo '</div>';//end searchresults div
 286 }
 287
 288 /**
 289  * 2. Displays the main search form
 290  */
 291 ?>
 292 <a name="db_search"></a>
 293 <form id="db_search_form" method="post" action="db_search.php" name="db_search">
 294 <?php echo PMA_generate_common_hidden_inputs($GLOBALS['db']); ?>
 295 <fieldset>
 296     <legend><?php echo __('Search in database'); ?></legend>
 297
 298     <table class="formlayout">
 299     <tr><td><?php echo __('Word(s) or value(s) to search for (wildcard: "%"):'); ?></td>
 300         <td><input type="text" name="search_str" size="60"
 301                 value="<?php echo $searched; ?>" /></td>
 302     </tr>
 303     <tr><td align="right" valign="top">
 304             <?php echo __('Find:'); ?></td>
 305             <td><?php
 306
 307 $choices = array(
 308     '1' => __('at least one of the words') . PMA_showHint(__('Words are separated by a space character (" ").')),
 309     '2' => __('all words') . PMA_showHint(__('Words are separated by a space character (" ").')),
 310     '3' => __('the exact phrase'),
 311     '4' => __('as regular expression') . ' ' . PMA_showMySQLDocu('Regexp', 'Regexp')
 312 );
 313 // 4th parameter set to true to add line breaks
 314 // 5th parameter set to false to avoid htmlspecialchars() escaping in the label
 315 //  since we have some HTML in some labels
 316 PMA_display_html_radio('search_option', $choices, $search_option, true, false);
 317 unset($choices);
 318             ?>
 319             </td>
 320     </tr>
 321     <tr><td align="right" valign="top">
 322             <?php echo __('Inside table(s):'); ?></td>
 323         <td rowspan="2">
 324 <?php
 325 echo '            <select name="table_select[]" size="6" multiple="multiple">' . "\n";
 326 foreach ($tables_names_only as $each_table) {
 327     if (in_array($each_table, $tables_selected)) {
 328         $is_selected = ' selected="selected"';
 329     } else {
 330         $is_selected = '';
 331     }
 332
 333     echo '                <option value="' . htmlspecialchars($each_table) . '"'
 334         . $is_selected . '>'
 335         . str_replace(' ', '&nbsp;', htmlspecialchars($each_table)) . '</option>' . "\n";
 336 } // end while
 337
 338 echo '            </select>' . "\n";
 339 $alter_select =
 340     '<a href="db_search.php' . PMA_generate_common_url(array_merge($url_params, array('selectall' => 1))) . '#db_search"'
 341     . ' onclick="setSelectOptions(\'db_search\', \'table_select[]\', true); return false;">' . __('Select All') . '</a>'
 342     . '&nbsp;/&nbsp;'
 343     . '<a href="db_search.php' . PMA_generate_common_url(array_merge($url_params, array('unselectall' => 1))) . '#db_search"'
 344     . ' onclick="setSelectOptions(\'db_search\', \'table_select[]\', false); return false;">' . __('Unselect All') . '</a>';
 345 ?>
 346         </td>
 347     </tr>
 348     <tr><td align="right" valign="bottom">
 349             <?php echo $alter_select; ?></td>
 350     </tr>
 351     <tr><td align="right">
 352             <?php echo __('Inside column:'); ?></td>
 353         <td><input type="text" name="field_str" size="60"
 354                 value="<?php echo ! empty($field_str) ? htmlspecialchars($field_str) : ''; ?>" /></td>
 355     </tr>
 356     </table>
 357 </fieldset>
 358 <fieldset class="tblFooters">
 359     <input type="submit" name="submit_search" value="<?php echo __('Go'); ?>"
 360         id="buttonGo" />
 361 </fieldset>
 362 </form>
 363
 364 <?php
 365 /**
 366  * Displays the footer
 367  */
 368 require './libraries/footer.inc.php';
 369 ?>