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