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