libraries/sql_query_form.lib.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * functions for displaying the sql query form
   5  *
   6  * @usedby  server_sql.php
   7  * @usedby  db_sql.php
   8  * @usedby  tbl_sql.php
   9  * @usedby  tbl_structure.php
  10  * @usedby  tbl_tracking.php
  11  * @usedby  querywindow.php
  12  * @package phpMyAdmin
  13  */
  14 if (! defined('PHPMYADMIN')) {
  15     exit;
  16 }
  17
  18 /**
  19  *
  20  */
  21 require_once './libraries/file_listing.php'; // used for file listing
  22 require_once './libraries/bookmark.lib.php'; // used for file listing
  23
  24 /**
  25  * prints the sql query boxes
  26  *
  27  * @usedby  server_sql.php
  28  * @usedby  db_sql.php
  29  * @usedby  tbl_sql.php
  30  * @usedby  tbl_structure.php
  31  * @usedby  tbl_tracking.php
  32  * @usedby  querywindow.php
  33  * @uses    $GLOBALS['table']
  34  * @uses    $GLOBALS['db']
  35  * @uses    $GLOBALS['server']
  36  * @uses    $GLOBALS['goto']
  37  * @uses    $GLOBALS['is_upload']           from common.inc.php
  38  * @uses    $GLOBALS['sql_query']           from grab_globals.lib.php
  39  * @uses    $GLOBALS['cfg']['DefaultQueryTable']
  40  * @uses    $GLOBALS['cfg']['DefaultQueryDatabase']
  41  * @uses    $GLOBALS['cfg']['Servers']
  42  * @uses    $GLOBALS['cfg']['DefaultTabDatabase']
  43  * @uses    $GLOBALS['cfg']['Bookmark']
  44  * @uses    PMA_generate_common_url()
  45  * @uses    PMA_backquote()
  46  * @uses    PMA_DBI_fetch_result()
  47  * @uses    PMA_showMySQLDocu()
  48  * @uses    PMA_generate_common_hidden_inputs()
  49  * @uses    PMA_sqlQueryFormBookmark()
  50  * @uses    PMA_sqlQueryFormInsert()
  51  * @uses    PMA_sqlQueryFormUpload()
  52  * @uses    PMA_DBI_QUERY_STORE
  53  * @uses    PMA_set_enc_form()
  54  * @uses    sprintf()
  55  * @uses    htmlspecialchars()
  56  * @uses    str_replace()
  57  * @uses    md5()
  58  * @uses    function_exists()
  59  * @param   boolean|string  $query          query to display in the textarea
  60  *                                          or true to display last executed
  61  * @param   boolean|string  $display_tab    sql|files|history|full|FALSE
  62  *                                          what part to display
  63  *                                          false if not inside querywindow
  64  * @param   string          $delimiter
  65  */
  66 function PMA_sqlQueryForm($query = true, $display_tab = false, $delimiter = ';')
  67 {
  68     // check tab to display if inside querywindow
  69     if (! $display_tab) {
  70         $display_tab = 'full';
  71         $is_querywindow = false;
  72     } else {
  73         $is_querywindow = true;
  74     }
  75
  76     // query to show
  77     if (true === $query) {
  78         $query = $GLOBALS['sql_query'];
  79     }
  80
  81     // set enctype to multipart for file uploads
  82     if ($GLOBALS['is_upload']) {
  83         $enctype = ' enctype="multipart/form-data"';
  84     } else {
  85         $enctype = '';
  86     }
  87
  88     $table  = '';
  89     $db     = '';
  90     if (! strlen($GLOBALS['db'])) {
  91         // prepare for server related
  92         $goto   = empty($GLOBALS['goto']) ?
  93                     'server_sql.php' : $GLOBALS['goto'];
  94     } elseif (! strlen($GLOBALS['table'])) {
  95         // prepare for db related
  96         $db     = $GLOBALS['db'];
  97         $goto   = empty($GLOBALS['goto']) ?
  98                     'db_sql.php' : $GLOBALS['goto'];
  99     } else {
 100         $table  = $GLOBALS['table'];
 101         $db     = $GLOBALS['db'];
 102         $goto   = empty($GLOBALS['goto']) ?
 103                     'tbl_sql.php' : $GLOBALS['goto'];
 104     }
 105
 106
 107     // start output
 108     if ($is_querywindow) {
 109         ?>
 110         <form method="post" id="sqlqueryform" target="frame_content"
 111               action="import.php"<?php echo $enctype; ?> name="sqlform"
 112               onsubmit="var save_name = window.opener.parent.frame_content.name;
 113               window.opener.parent.frame_content.name = save_name + '<?php echo time(); ?>';
 114               this.target = window.opener.parent.frame_content.name;
 115               return checkSqlQuery(this)">
 116         <?php
 117     } else {
 118         echo '<form method="post" action="import.php" ' . $enctype . ' id="sqlqueryform"'
 119             .' onsubmit="return checkSqlQuery(this)" name="sqlform">' . "\n";
 120     }
 121
 122     if ($is_querywindow) {
 123         echo '<input type="hidden" name="focus_querywindow" value="true" />'
 124             ."\n";
 125         if ($display_tab != 'sql' && $display_tab != 'full') {
 126             echo '<input type="hidden" name="sql_query" value="" />' . "\n";
 127             echo '<input type="hidden" name="show_query" value="1" />' . "\n";
 128         }
 129     }
 130     echo '<input type="hidden" name="is_js_confirmed" value="0" />' . "\n"
 131         .PMA_generate_common_hidden_inputs($db, $table) . "\n"
 132         .'<input type="hidden" name="pos" value="0" />' . "\n"
 133         .'<input type="hidden" name="goto" value="'
 134         .htmlspecialchars($goto) . '" />' . "\n"
 135         .'<input type="hidden" name="message_to_show" value="'
 136         . htmlspecialchars(__('Your SQL query has been executed successfully')) . '" />' . "\n"
 137         .'<input type="hidden" name="prev_sql_query" value="'
 138         . htmlspecialchars($query) . '" />' . "\n";
 139
 140     // display querybox
 141     if ($display_tab === 'full' || $display_tab === 'sql') {
 142         PMA_sqlQueryFormInsert($query, $is_querywindow, $delimiter);
 143     }
 144
 145     // display uploads
 146     if ($display_tab === 'files' && $GLOBALS['is_upload']) {
 147         PMA_sqlQueryFormUpload();
 148     }
 149
 150     // Bookmark Support
 151     if ($display_tab === 'full' || $display_tab === 'history') {
 152         if (! empty($GLOBALS['cfg']['Bookmark'])) {
 153             PMA_sqlQueryFormBookmark();
 154         }
 155     }
 156
 157     // Encoding setting form appended by Y.Kawada
 158     if (function_exists('PMA_set_enc_form')) {
 159         echo PMA_set_enc_form('    ');
 160     }
 161
 162     echo '</form>' . "\n";
 163     if ($is_querywindow) {
 164         ?>
 165         <script type="text/javascript">
 166         //<![CDATA[
 167             if (window.opener) {
 168                 window.opener.parent.insertQuery();
 169             }
 170         //]]>
 171         </script>
 172         <?php
 173     }
 174
 175     // print an empty div, which will be later filled with the sql query results by ajax
 176     echo '<div id="sqlqueryresults"></div>';
 177 }
 178
 179 /**
 180  * prints querybox fieldset
 181  *
 182  * @usedby  PMA_sqlQueryForm()
 183  * @uses    $GLOBALS['text_dir']
 184  * @uses    $GLOBALS['cfg']['TextareaAutoSelect']
 185  * @uses    $GLOBALS['cfg']['TextareaCols']
 186  * @uses    $GLOBALS['cfg']['TextareaRows']
 187  * @uses    PMA_USR_OS
 188  * @uses    PMA_USR_BROWSER_AGENT
 189  * @uses    PMA_USR_BROWSER_VER
 190  * @uses    htmlspecialchars()
 191  * @param   string      $query          query to display in the textarea
 192  * @param   boolean     $is_querywindow if inside querywindow or not
 193  * @param   string      $delimiter      default delimiter to use
 194  */
 195 function PMA_sqlQueryFormInsert($query = '', $is_querywindow = false, $delimiter = ';')
 196 {
 197
 198     // enable auto select text in textarea
 199     if ($GLOBALS['cfg']['TextareaAutoSelect']) {
 200         $auto_sel = ' onfocus="selectContent(this, sql_box_locked, true)"';
 201     } else {
 202         $auto_sel = '';
 203     }
 204
 205     // enable locking if inside query window
 206     if ($is_querywindow) {
 207         $locking = ' onkeypress="document.sqlform.elements[\'LockFromUpdate\'].'
 208             .'checked = true;"';
 209         $height = $GLOBALS['cfg']['TextareaRows'] * 1.25;
 210     } else {
 211         $locking = '';
 212         $height = $GLOBALS['cfg']['TextareaRows'] * 2;
 213     }
 214
 215     $table          = '';
 216     $db             = '';
 217     $fields_list    = array();
 218     if (! strlen($GLOBALS['db'])) {
 219         // prepare for server related
 220         $legend = sprintf(__('Run SQL query/queries on server %s'),
 221             '&quot;' . htmlspecialchars(
 222                 ! empty($GLOBALS['cfg']['Servers'][$GLOBALS['server']]['verbose']) ? $GLOBALS['cfg']['Servers'][$GLOBALS['server']]['verbose'] : $GLOBALS['cfg']['Servers'][$GLOBALS['server']]['host']) . '&quot;');
 223     } elseif (! strlen($GLOBALS['table'])) {
 224         // prepare for db related
 225         $db     = $GLOBALS['db'];
 226         // if you want navigation:
 227         $tmp_db_link = '<a href="' . $GLOBALS['cfg']['DefaultTabDatabase']
 228             . '?' . PMA_generate_common_url($db) . '"';
 229         if ($is_querywindow) {
 230             $tmp_db_link .= ' target="_self"'
 231                 . ' onclick="this.target=window.opener.frame_content.name"';
 232         }
 233         $tmp_db_link .= '>'
 234             . htmlspecialchars($db) . '</a>';
 235         // else use
 236         // $tmp_db_link = htmlspecialchars($db);
 237         $legend = sprintf(__('Run SQL query/queries on database %s'), $tmp_db_link);
 238         if (empty($query)) {
 239             $query = PMA_expandUserString($GLOBALS['cfg']['DefaultQueryDatabase'], 'PMA_backquote');
 240         }
 241     } else {
 242         $table  = $GLOBALS['table'];
 243         $db     = $GLOBALS['db'];
 244         // Get the list and number of fields
 245         // we do a try_query here, because we could be in the query window,
 246         // trying to synchonize and the table has not yet been created
 247         $fields_list = PMA_DBI_fetch_result(
 248             'SHOW FULL COLUMNS FROM ' . PMA_backquote($db)
 249             . '.' . PMA_backquote($GLOBALS['table']));
 250
 251         $tmp_db_link = '<a href="' . $GLOBALS['cfg']['DefaultTabDatabase']
 252             . '?' . PMA_generate_common_url($db) . '"';
 253         if ($is_querywindow) {
 254             $tmp_db_link .= ' target="_self"'
 255                 . ' onclick="this.target=window.opener.frame_content.name"';
 256         }
 257         $tmp_db_link .= '>'
 258             . htmlspecialchars($db) . '</a>';
 259         // else use
 260         // $tmp_db_link = htmlspecialchars($db);
 261         $legend = sprintf(__('Run SQL query/queries on database %s'), $tmp_db_link);
 262         if (empty($query)) {
 263             $query = PMA_expandUserString($GLOBALS['cfg']['DefaultQueryTable'], 'PMA_backquote');
 264         }
 265     }
 266     $legend .= ': ' . PMA_showMySQLDocu('SQL-Syntax', 'SELECT');
 267
 268     if (count($fields_list)) {
 269         $sqlquerycontainer_id = 'sqlquerycontainer';
 270     } else {
 271         $sqlquerycontainer_id = 'sqlquerycontainerfull';
 272     }
 273
 274     echo '<a name="querybox"></a>' . "\n"
 275         .'<div id="queryboxcontainer">' . "\n"
 276         .'<fieldset id="querybox">' . "\n";
 277     echo '<legend>' . $legend . '</legend>' . "\n";
 278     echo '<div id="queryfieldscontainer">' . "\n";
 279     echo '<div id="' . $sqlquerycontainer_id . '">' . "\n"
 280         .'<textarea tabindex="100" name="sql_query" id="sqlquery"'
 281         .'  cols="' . $GLOBALS['cfg']['TextareaCols'] . '"'
 282         .'  rows="' . $height . '"'
 283         .'  dir="' . $GLOBALS['text_dir'] . '"'
 284         .$auto_sel . $locking . '>' . htmlspecialchars($query) . '</textarea>' . "\n";
 285     // Add buttons to generate query easily for select all,single select,insert,update and delete
 286     if(count($fields_list)) {
 287         echo '<input type="button" value="SELECT *" id="selectall" class="sqlbutton" />';
 288         echo '<input type="button" value="SELECT" id="select" class="sqlbutton" />';
 289         echo '<input type="button" value="INSERT" id="insert" class="sqlbutton" />';
 290         echo '<input type="button" value="UPDATE" id="update" class="sqlbutton" />';
 291         echo '<input type="button" value="DELETE" id="delete" class="sqlbutton" />';
 292     }
 293     echo '</div>' . "\n";
 294
 295     if (count($fields_list)) {
 296         echo '<div id="tablefieldscontainer">' . "\n"
 297             .'<label>' . __('Columns') . '</label>' . "\n"
 298             .'<select id="tablefields" name="dummy" '
 299             .'size="' . ($GLOBALS['cfg']['TextareaRows'] - 2) . '" '
 300             .'multiple="multiple" ondblclick="insertValueQuery()">' . "\n";
 301         foreach ($fields_list as $field) {
 302             echo '<option value="'
 303                 .PMA_backquote(htmlspecialchars($field['Field'])) . '"';
 304             if (isset($field['Field']) && strlen($field['Field']) && isset($field['Comment'])) {
 305                 echo ' title="' . htmlspecialchars($field['Comment']) . '"';
 306             }
 307             echo '>' . htmlspecialchars($field['Field']) . '</option>' . "\n";
 308         }
 309         echo '</select>' . "\n"
 310             .'<div id="tablefieldinsertbuttoncontainer">' . "\n";
 311         if ($GLOBALS['cfg']['PropertiesIconic']) {
 312             echo '<input type="button" name="insert" value="&lt;&lt;"'
 313                 .' onclick="insertValueQuery()"'
 314                 .' title="' . __('Insert') . '" />' . "\n";
 315         } else {
 316             echo '<input type="button" name="insert"'
 317                 .' value="' . __('Insert') . '"'
 318                 .' onclick="insertValueQuery()" />' . "\n";
 319         }
 320         echo '</div>' . "\n"
 321             .'</div>' . "\n";
 322     }
 323
 324     echo '<div class="clearfloat"></div>' . "\n";
 325     echo '</div>' . "\n";
 326
 327     if (! empty($GLOBALS['cfg']['Bookmark'])) {
 328         ?>
 329         <div id="bookmarkoptions">
 330         <div class="formelement">
 331         <label for="bkm_label">
 332             <?php echo __('Bookmark this SQL query'); ?>:</label>
 333         <input type="text" name="bkm_label" id="bkm_label" tabindex="110" value="" />
 334         </div>
 335         <div class="formelement">
 336         <input type="checkbox" name="bkm_all_users" tabindex="111" id="id_bkm_all_users"
 337             value="true" />
 338         <label for="id_bkm_all_users">
 339             <?php echo __('Let every user access this bookmark'); ?></label>
 340         </div>
 341         <div class="formelement">
 342         <input type="checkbox" name="bkm_replace" tabindex="112" id="id_bkm_replace"
 343             value="true" />
 344         <label for="id_bkm_replace">
 345             <?php echo __('Replace existing bookmark of same name'); ?></label>
 346         </div>
 347         </div>
 348         <?php
 349     }
 350
 351     echo '<div class="clearfloat"></div>' . "\n";
 352     echo '</fieldset>' . "\n"
 353         .'</div>' . "\n";
 354
 355     echo '<fieldset id="queryboxfooter" class="tblFooters">' . "\n";
 356     echo '<div class="formelement">' . "\n";
 357     if ($is_querywindow) {
 358         ?>
 359         <script type="text/javascript">
 360         //<![CDATA[
 361             document.writeln(' <input type="checkbox" name="LockFromUpdate" checked="checked" tabindex="120" id="checkbox_lock" /> <label for="checkbox_lock"><?php echo __('Do not overwrite this query from outside the window'); ?></label> ');
 362         //]]>
 363         </script>
 364         <?php
 365     }
 366     echo '</div>' . "\n";
 367     echo '<div class="formelement">' . "\n";
 368     echo '<label for="id_sql_delimiter">[ ' . __('Delimiter')
 369         .'</label>' . "\n";
 370     echo '<input type="text" name="sql_delimiter" tabindex="131" size="3" '
 371         .'value="' . $delimiter . '" '
 372         .'id="id_sql_delimiter" /> ]' . "\n";
 373
 374     echo '<input type="checkbox" name="show_query" value="1" '
 375         .'id="checkbox_show_query" tabindex="132" checked="checked" />' . "\n"
 376         .'<label for="checkbox_show_query">' . __(' Show this query here again ')
 377         .'</label>' . "\n";
 378
 379     echo '</div>' . "\n";
 380     echo '<input type="submit" name="SQL" tabindex="200" value="' . __('Go') . '" />'
 381         ."\n";
 382     echo '<div class="clearfloat"></div>' . "\n";
 383     echo '</fieldset>' . "\n";
 384 }
 385
 386 /**
 387  * prints bookmark fieldset
 388  *
 389  * @usedby  PMA_sqlQueryForm()
 390  * @uses    PMA_Bookmark_getList()
 391  * @uses    $GLOBALS['db']
 392  * @uses    $GLOBALS['pmaThemeImage']
 393  * @uses    $GLOBALS['cfg']['ReplaceHelpImg']
 394  * @uses    count()
 395  * @uses    htmlspecialchars()
 396  */
 397 function PMA_sqlQueryFormBookmark()
 398 {
 399     $bookmark_list = PMA_Bookmark_getList($GLOBALS['db']);
 400     if (! $bookmark_list || count($bookmark_list) < 1) {
 401         return;
 402     }
 403
 404     echo '<fieldset id="bookmarkoptions">';
 405     echo '<legend>';
 406     echo __('Bookmarked SQL query') . '</legend>' . "\n";
 407     echo '<div class="formelement">';
 408     echo '<select name="id_bookmark">' . "\n";
 409     echo '<option value="">&nbsp;</option>' . "\n";
 410     foreach ($bookmark_list as $key => $value) {
 411         echo '<option value="' . htmlspecialchars($key) . '">'
 412             .htmlspecialchars($value) . '</option>' . "\n";
 413     }
 414     // &nbsp; is required for correct display with styles/line height
 415     echo '</select>&nbsp;' . "\n";
 416     echo '</div>' . "\n";
 417     echo '<div class="formelement">' . "\n";
 418     echo __('Variable');
 419     if ($GLOBALS['cfg']['ReplaceHelpImg']) {
 420         echo ' <a href="./Documentation.html#faqbookmark"'
 421             .' target="documentation">'
 422             .'<img class="icon" src="' . $GLOBALS['pmaThemeImage'] . 'b_help.png"'
 423             .' border="0" width="11" height="11" align="middle"'
 424             .' alt="' . __('Documentation') . '" /></a> ';
 425     } else {
 426         echo ' (<a href="./Documentation.html#faqbookmark"'
 427             .' target="documentation">' . __('Documentation') . '</a>): ';
 428     }
 429     echo '<input type="text" name="bookmark_variable" class="textfield"'
 430         .' size="10" />' . "\n";
 431     echo '</div>' . "\n";
 432     echo '<div class="formelement">' . "\n";
 433     echo '<input type="radio" name="action_bookmark" value="0"'
 434         .' id="radio_bookmark_exe" checked="checked" />'
 435         .'<label for="radio_bookmark_exe">' . __('Submit')
 436         .'</label>' . "\n";
 437     echo '<input type="radio" name="action_bookmark" value="1"'
 438         .' id="radio_bookmark_view" />'
 439         .'<label for="radio_bookmark_view">' . __('View only')
 440         .'</label>' . "\n";
 441     echo '<input type="radio" name="action_bookmark" value="2"'
 442         .' id="radio_bookmark_del" />'
 443         .'<label for="radio_bookmark_del">' . __('Delete')
 444         .'</label>' . "\n";
 445     echo '</div>' . "\n";
 446     echo '<div class="clearfloat"></div>' . "\n";
 447     echo '</fieldset>' . "\n";
 448
 449     echo '<fieldset id="bookmarkoptionsfooter" class="tblFooters">' . "\n";
 450     echo '<input type="submit" name="SQL" value="' . __('Go') . '" />';
 451     echo '<div class="clearfloat"></div>' . "\n";
 452     echo '</fieldset>' . "\n";
 453 }
 454
 455 /**
 456  * prints bookmark fieldset
 457  *
 458  * @usedby  PMA_sqlQueryForm()
 459  * @uses    $GLOBALS['cfg']['GZipDump']
 460  * @uses    $GLOBALS['cfg']['BZipDump']
 461  * @uses    $GLOBALS['cfg']['UploadDir']
 462  * @uses    $GLOBALS['cfg']['AvailableCharsets']
 463  * @uses    $GLOBALS['charset']
 464  * @uses    $GLOBALS['max_upload_size']
 465  * @uses    PMA_supportedDecompressions()
 466  * @uses    PMA_getFileSelectOptions()
 467  * @uses    PMA_displayMaximumUploadSize()
 468  * @uses    PMA_generateCharsetDropdownBox()
 469  * @uses    PMA_generateHiddenMaxFileSize()
 470  * @uses    PMA_CSDROPDOWN_CHARSET
 471  * @uses    empty()
 472  */
 473 function PMA_sqlQueryFormUpload(){
 474     $errors = array ();
 475
 476     $matcher = '@\.sql(\.(' . PMA_supportedDecompressions() . '))?$@'; // we allow only SQL here
 477
 478     if (!empty($GLOBALS['cfg']['UploadDir'])) {
 479         $files = PMA_getFileSelectOptions(PMA_userDir($GLOBALS['cfg']['UploadDir']), $matcher, (isset($timeout_passed) && $timeout_passed && isset($local_import_file)) ? $local_import_file : '');
 480     } else {
 481         $files = '';
 482     }
 483
 484     // start output
 485     echo '<fieldset id="">';
 486     echo '<legend>';
 487     echo __('Location of the text file') . '</legend>';
 488     echo '<div class="formelement">';
 489     echo '<input type="file" name="sql_file" class="textfield" /> ';
 490     echo PMA_displayMaximumUploadSize($GLOBALS['max_upload_size']);
 491     // some browsers should respect this :)
 492     echo PMA_generateHiddenMaxFileSize($GLOBALS['max_upload_size']) . "\n";
 493     echo '</div>';
 494
 495     if ($files === FALSE) {
 496         $errors[] = PMA_Message::error( __('The directory you set for upload work cannot be reached'));
 497     } elseif (!empty($files)) {
 498         echo '<div class="formelement">';
 499         echo '<strong>' . __('web server upload directory') .':</strong>' . "\n";
 500         echo '<select size="1" name="sql_localfile">' . "\n";
 501         echo '<option value="" selected="selected"></option>' . "\n";
 502         echo $files;
 503         echo '</select>' . "\n";
 504         echo '</div>';
 505     }
 506
 507     echo '<div class="clearfloat"></div>' . "\n";
 508     echo '</fieldset>';
 509
 510
 511     echo '<fieldset id="" class="tblFooters">';
 512     echo __('Character set of the file:') . "\n";
 513     echo PMA_generateCharsetDropdownBox(PMA_CSDROPDOWN_CHARSET,
 514             'charset_of_file', null, 'utf8', FALSE);
 515     echo '<input type="submit" name="SQL" value="' . __('Go')
 516         .'" />' . "\n";
 517     echo '<div class="clearfloat"></div>' . "\n";
 518     echo '</fieldset>';
 519
 520     foreach ($errors as $error) {
 521         $error->display();
 522     }
 523 }
 524 ?>