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  * @package PhpMyAdmin
  12  */
  13 if (! defined('PHPMYADMIN')) {
  14     exit;
  15 }
  16
  17 /**
  18  *
  19  */
  20 require_once './libraries/file_listing.lib.php'; // used for file listing
  21 require_once './libraries/bookmark.lib.php'; // used for bookmarks
  22
  23 /**
  24  * return HTML for the sql query boxes
  25  *
  26  * @param boolean|string $query       query to display in the textarea
  27  *                                    or true to display last executed
  28  * @param boolean|string $display_tab sql|full|false
  29  *                                    what part to display
  30  *                                    false if not inside querywindow
  31  * @param string         $delimiter   delimiter
  32  *
  33  * @return string
  34  *
  35  * @usedby  server_sql.php
  36  * @usedby  db_sql.php
  37  * @usedby  tbl_sql.php
  38  * @usedby  tbl_structure.php
  39  * @usedby  tbl_tracking.php
  40  */
  41 function PMA_getHtmlForSqlQueryForm(
  42     $query = true, $display_tab = false, $delimiter = ';'
  43 ) {
  44     $html = '';
  45     if (! $display_tab) {
  46         $display_tab = 'full';
  47     }
  48     // query to show
  49     if (true === $query) {
  50         $query = $GLOBALS['sql_query'];
  51     }
  52
  53     // set enctype to multipart for file uploads
  54     if ($GLOBALS['is_upload']) {
  55         $enctype = ' enctype="multipart/form-data"';
  56     } else {
  57         $enctype = '';
  58     }
  59
  60     $table  = '';
  61     $db     = '';
  62     if (! mb_strlen($GLOBALS['db'])) {
  63         // prepare for server related
  64         $goto   = empty($GLOBALS['goto']) ?
  65                     'server_sql.php' : $GLOBALS['goto'];
  66     } elseif (! mb_strlen($GLOBALS['table'])) {
  67         // prepare for db related
  68         $db     = $GLOBALS['db'];
  69         $goto   = empty($GLOBALS['goto']) ?
  70                     'db_sql.php' : $GLOBALS['goto'];
  71     } else {
  72         $table  = $GLOBALS['table'];
  73         $db     = $GLOBALS['db'];
  74         $goto   = empty($GLOBALS['goto']) ?
  75                     'tbl_sql.php' : $GLOBALS['goto'];
  76     }
  77
  78     // start output
  79     $html .= '<form method="post" action="import.php" ' . $enctype;
  80     $html .= ' class="ajax lock-page"';
  81     $html .= ' id="sqlqueryform" name="sqlform">' . "\n";
  82
  83     $html .= '<input type="hidden" name="is_js_confirmed" value="0" />'
  84         . "\n" . PMA_URL_getHiddenInputs($db, $table) . "\n"
  85         . '<input type="hidden" name="pos" value="0" />' . "\n"
  86         . '<input type="hidden" name="goto" value="'
  87         . htmlspecialchars($goto) . '" />' . "\n"
  88         . '<input type="hidden" name="message_to_show" value="'
  89         . __('Your SQL query has been executed successfully.') . '" />'
  90         . "\n" . '<input type="hidden" name="prev_sql_query" value="'
  91         . htmlspecialchars($query) . '" />' . "\n";
  92
  93     // display querybox
  94     if ($display_tab === 'full' || $display_tab === 'sql') {
  95         $html .= PMA_getHtmlForSqlQueryFormInsert(
  96             $query, $delimiter
  97         );
  98     }
  99
 100     // Bookmark Support
 101     if ($display_tab === 'full') {
 102         $cfgBookmark = PMA_Bookmark_getParams();
 103         if ($cfgBookmark) {
 104             $html .= PMA_getHtmlForSqlQueryFormBookmark();
 105         }
 106     }
 107
 108     // Encoding setting form appended by Y.Kawada
 109     if (function_exists('PMA_Kanji_encodingForm')) {
 110         $html .= PMA_Kanji_encodingForm();
 111     }
 112
 113     $html .= '</form>' . "\n";
 114     // print an empty div, which will be later filled with
 115     // the sql query results by ajax
 116     $html .= '<div id="sqlqueryresultsouter"></div>';
 117
 118     return $html;
 119 }
 120
 121 /**
 122  * Get initial values for Sql Query Form Insert
 123  *
 124  * @param string $query query to display in the textarea
 125  *
 126  * @return array ($legend, $query, $columns_list)
 127  *
 128  * @usedby  PMA_getHtmlForSqlQueryFormInsert()
 129  */
 130 function PMA_initQueryForm($query)
 131 {
 132     $columns_list    = array();
 133     if (! mb_strlen($GLOBALS['db'])) {
 134         // prepare for server related
 135         $legend = sprintf(
 136             __('Run SQL query/queries on server %s'),
 137             '&quot;' . htmlspecialchars(
 138                 ! empty($GLOBALS['cfg']['Servers'][$GLOBALS['server']]['verbose'])
 139                 ? $GLOBALS['cfg']['Servers'][$GLOBALS['server']]['verbose']
 140                 : $GLOBALS['cfg']['Servers'][$GLOBALS['server']]['host']
 141             ) . '&quot;'
 142         );
 143     } elseif (! mb_strlen($GLOBALS['table'])) {
 144         // prepare for db related
 145         $db     = $GLOBALS['db'];
 146         // if you want navigation:
 147         $tmp_db_link = '<a href="' . PMA\libraries\Util::getScriptNameForOption(
 148             $GLOBALS['cfg']['DefaultTabDatabase'], 'database'
 149         )
 150             . PMA_URL_getCommon(array('db' => $db)) . '"';
 151         $tmp_db_link .= '>'
 152             . htmlspecialchars($db) . '</a>';
 153         $legend = sprintf(__('Run SQL query/queries on database %s'), $tmp_db_link);
 154         if (empty($query)) {
 155             $query = PMA\libraries\Util::expandUserString(
 156                 $GLOBALS['cfg']['DefaultQueryDatabase'], 'backquote'
 157             );
 158         }
 159     } else {
 160         $db     = $GLOBALS['db'];
 161         $table  = $GLOBALS['table'];
 162         // Get the list and number of fields
 163         // we do a try_query here, because we could be in the query window,
 164         // trying to synchronize and the table has not yet been created
 165         $columns_list = $GLOBALS['dbi']->getColumns(
 166             $db, $GLOBALS['table'], null, true
 167         );
 168
 169         $tmp_tbl_link = '<a href="' . PMA\libraries\Util::getScriptNameForOption(
 170             $GLOBALS['cfg']['DefaultTabTable'], 'table'
 171         ) . PMA_URL_getCommon(array('db' => $db, 'table' => $table)) . '" >';
 172         $tmp_tbl_link .= htmlspecialchars($db)
 173             . '.' . htmlspecialchars($table) . '</a>';
 174         $legend = sprintf(__('Run SQL query/queries on table %s'), $tmp_tbl_link);
 175         if (empty($query)) {
 176             $query = PMA\libraries\Util::expandUserString(
 177                 $GLOBALS['cfg']['DefaultQueryTable'], 'backquote'
 178             );
 179         }
 180     }
 181     $legend .= ': ' . PMA\libraries\Util::showMySQLDocu('SELECT');
 182
 183     return array($legend, $query, $columns_list);
 184 }
 185
 186 /**
 187  * return HTML for Sql Query Form Insert
 188  *
 189  * @param string $query     query to display in the textarea
 190  * @param string $delimiter default delimiter to use
 191  *
 192  * @return string
 193  *
 194  * @usedby  PMA_getHtmlForSqlQueryForm()
 195  */
 196 function PMA_getHtmlForSqlQueryFormInsert(
 197     $query = '', $delimiter = ';'
 198 ) {
 199     // enable auto select text in textarea
 200     if ($GLOBALS['cfg']['TextareaAutoSelect']) {
 201         $auto_sel = ' onclick="selectContent(this, sql_box_locked, true);"';
 202     } else {
 203         $auto_sel = '';
 204     }
 205
 206     $locking = '';
 207     $height = $GLOBALS['cfg']['TextareaRows'] * 2;
 208
 209     list($legend, $query, $columns_list) = PMA_initQueryForm($query);
 210
 211     if (! empty($columns_list)) {
 212         $sqlquerycontainer_id = 'sqlquerycontainer';
 213     } else {
 214         $sqlquerycontainer_id = 'sqlquerycontainerfull';
 215     }
 216
 217     $html = '<a id="querybox"></a>'
 218         . '<div id="queryboxcontainer">'
 219         . '<fieldset id="queryboxf">';
 220     $html .= '<legend>' . $legend . '</legend>';
 221     $html .= '<div id="queryfieldscontainer">';
 222     $html .= '<div id="' . $sqlquerycontainer_id . '">'
 223         . '<textarea tabindex="100" name="sql_query" id="sqlquery"'
 224         . '  cols="' . $GLOBALS['cfg']['TextareaCols'] . '"'
 225         . '  rows="' . $height . '"'
 226         . $auto_sel . $locking . '>'
 227         . htmlspecialchars($query)
 228         . '</textarea>';
 229     $html .= '<div id="querymessage"></div>';
 230     // Add buttons to generate query easily for
 231     // select all, single select, insert, update and delete
 232     if (! empty($columns_list)) {
 233         $html .= '<input type="button" value="SELECT *" id="selectall"'
 234             . ' class="button sqlbutton" />';
 235         $html .= '<input type="button" value="SELECT" id="select"'
 236             . ' class="button sqlbutton" />';
 237         $html .= '<input type="button" value="INSERT" id="insert"'
 238             . ' class="button sqlbutton" />';
 239         $html .= '<input type="button" value="UPDATE" id="update"'
 240             . ' class="button sqlbutton" />';
 241         $html .= '<input type="button" value="DELETE" id="delete"'
 242             . ' class="button sqlbutton" />';
 243     }
 244     $html .= '<input type="button" value="' . __('Clear') . '" id="clear"'
 245         . ' class="button sqlbutton" />';
 246     if ($GLOBALS['cfg']['CodemirrorEnable']) {
 247         $html .= '<input type="button" value="' . __('Format') . '" id="format"'
 248             . ' class="button sqlbutton" />';
 249     }
 250     $html .= '<input type="button" value="' . __('Get auto-saved query')
 251         . '" id="saved" class="button sqlbutton" />';
 252
 253     // parameter binding
 254     $html .= '<div>';
 255     $html .= '<input type="checkbox" name="parameterized" id="parameterized" />';
 256     $html .= '<label for="parameterized">' . __('Bind parameters') . '</label>';
 257     $html .= PMA\libraries\Util::showDocu('faq', 'faq6-40');
 258     $html .= '<div id="parametersDiv"></div>';
 259     $html .= '</div>';
 260
 261     $html .= '</div>' . "\n";
 262
 263     if (! empty($columns_list)) {
 264         $html .= '<div id="tablefieldscontainer">'
 265             . '<label>' . __('Columns') . '</label>'
 266             . '<select id="tablefields" name="dummy" '
 267             . 'size="' . ($GLOBALS['cfg']['TextareaRows'] - 2) . '" '
 268             . 'multiple="multiple" ondblclick="insertValueQuery()">';
 269         foreach ($columns_list as $field) {
 270             $html .= '<option value="'
 271                 . PMA\libraries\Util::backquote(htmlspecialchars($field['Field']))
 272                 . '"';
 273             if (isset($field['Field'])
 274                 && mb_strlen($field['Field'])
 275                 && isset($field['Comment'])
 276             ) {
 277                 $html .= ' title="' . htmlspecialchars($field['Comment']) . '"';
 278             }
 279             $html .= '>' . htmlspecialchars($field['Field']) . '</option>' . "\n";
 280         }
 281         $html .= '</select>'
 282             . '<div id="tablefieldinsertbuttoncontainer">';
 283         if (PMA\libraries\Util::showIcons('ActionLinksMode')) {
 284             $html .= '<input type="button" class="button" name="insert"'
 285                 . ' value="&lt;&lt;" onclick="insertValueQuery()"'
 286                 . ' title="' . __('Insert') . '" />';
 287         } else {
 288             $html .= '<input type="button" class="button" name="insert"'
 289                 . ' value="' . __('Insert') . '"'
 290                 . ' onclick="insertValueQuery()" />';
 291         }
 292         $html .= '</div>' . "\n"
 293             . '</div>' . "\n";
 294     }
 295
 296     $html .= '<div class="clearfloat"></div>' . "\n";
 297     $html .= '</div>' . "\n";
 298
 299     $cfgBookmark = PMA_Bookmark_getParams();
 300     if ($cfgBookmark) {
 301         $html .= '<div id="bookmarkoptions">';
 302         $html .= '<div class="formelement">';
 303         $html .= '<label for="bkm_label">'
 304             . __('Bookmark this SQL query:') . '</label>';
 305         $html .= '<input type="text" name="bkm_label" id="bkm_label"'
 306             . ' tabindex="110" value="" />';
 307         $html .= '</div>';
 308         $html .= '<div class="formelement">';
 309         $html .= '<input type="checkbox" name="bkm_all_users" tabindex="111"'
 310             . ' id="id_bkm_all_users" value="true" />';
 311         $html .= '<label for="id_bkm_all_users">'
 312             . __('Let every user access this bookmark') . '</label>';
 313         $html .= '</div>';
 314         $html .= '<div class="formelement">';
 315         $html .= '<input type="checkbox" name="bkm_replace" tabindex="112"'
 316             . ' id="id_bkm_replace" value="true" />';
 317         $html .= '<label for="id_bkm_replace">'
 318             . __('Replace existing bookmark of same name') . '</label>';
 319         $html .= '</div>';
 320         $html .= '</div>';
 321     }
 322
 323     $html .= '<div class="clearfloat"></div>' . "\n";
 324     $html .= '</fieldset>' . "\n"
 325         . '</div>' . "\n";
 326
 327     $html .= '<fieldset id="queryboxfooter" class="tblFooters">' . "\n";
 328     $html .= '<div class="formelement">' . "\n";
 329     $html .= '</div>' . "\n";
 330
 331     $html .= '<div class="formelement">';
 332     $html .= '<label for="id_sql_delimiter">[ ' . __('Delimiter')
 333         . '</label>' . "\n";
 334     $html .= '<input type="text" name="sql_delimiter" tabindex="131" size="3" '
 335         . 'value="' . $delimiter . '" '
 336         . 'id="id_sql_delimiter" /> ]';
 337     $html .= '</div>';
 338
 339     $html .= '<div class="formelement">';
 340     $html .= '<input type="checkbox" name="show_query" value="1" '
 341         . 'id="checkbox_show_query" tabindex="132" checked="checked" />'
 342         . '<label for="checkbox_show_query">' . __('Show this query here again')
 343         . '</label>';
 344     $html .= '</div>';
 345
 346     $html .= '<div class="formelement">';
 347     $html .= '<input type="checkbox" name="retain_query_box" value="1" '
 348         . 'id="retain_query_box" tabindex="133" '
 349         . ($GLOBALS['cfg']['RetainQueryBox'] === false
 350             ? '' : ' checked="checked"')
 351         . ' />'
 352         . '<label for="retain_query_box">' . __('Retain query box')
 353         . '</label>';
 354     $html .= '</div>';
 355
 356     $html .= '<div class="formelement">';
 357     $html .= '<input type="checkbox" name="rollback_query" value="1" '
 358         . 'id="rollback_query" tabindex="134" />'
 359         . '<label for="rollback_query">' . __('Rollback when finished')
 360         . '</label>';
 361     $html .= '</div>';
 362
 363     // Disable/Enable foreign key checks
 364     $html .= '<div class="formelement">';
 365     $html .= PMA\libraries\Util::getFKCheckbox();
 366     $html .= '</div>';
 367
 368     $html .= '<input type="submit" id="button_submit_query" name="SQL"';
 369
 370     $html .= ' tabindex="200" value="' . __('Go') . '" />' . "\n";
 371     $html .= '<div class="clearfloat"></div>' . "\n";
 372     $html .= '</fieldset>' . "\n";
 373
 374     return $html;
 375 }
 376
 377 /**
 378  * return HTML for sql Query Form Bookmark
 379  *
 380  * @return string|null
 381  *
 382  * @usedby  PMA_getHtmlForSqlQueryForm()
 383  */
 384 function PMA_getHtmlForSqlQueryFormBookmark()
 385 {
 386     $bookmark_list = PMA_Bookmark_getList($GLOBALS['db']);
 387     if (empty($bookmark_list) || count($bookmark_list) < 1) {
 388         return null;
 389     }
 390
 391     $html  = '<fieldset id="fieldsetBookmarkOptions">';
 392     $html .= '<legend>';
 393     $html .= __('Bookmarked SQL query') . '</legend>' . "\n";
 394     $html .= '<div class="formelement">';
 395     $html .= '<select name="id_bookmark" id="id_bookmark">' . "\n";
 396     $html .= '<option value="">&nbsp;</option>' . "\n";
 397     foreach ($bookmark_list as $key => $value) {
 398         $html .= '<option value="' . htmlspecialchars($key) . '"'
 399             . ' data-varcount="' . PMA_Bookmark_getVariableCount($value['query'])
 400             . '">'
 401             . htmlspecialchars($value['label']) . '</option>' . "\n";
 402     }
 403     // &nbsp; is required for correct display with styles/line height
 404     $html .= '</select>&nbsp;' . "\n";
 405     $html .= '</div>' . "\n";
 406     $html .= '<div class="formelement">' . "\n";
 407     $html .= '<input type="radio" name="action_bookmark" value="0"'
 408         . ' id="radio_bookmark_exe" checked="checked" />'
 409         . '<label for="radio_bookmark_exe">' . __('Submit')
 410         . '</label>' . "\n";
 411     $html .= '<input type="radio" name="action_bookmark" value="1"'
 412         . ' id="radio_bookmark_view" />'
 413         . '<label for="radio_bookmark_view">' . __('View only')
 414         . '</label>' . "\n";
 415     $html .= '<input type="radio" name="action_bookmark" value="2"'
 416         . ' id="radio_bookmark_del" />'
 417         . '<label for="radio_bookmark_del">' . __('Delete')
 418         . '</label>' . "\n";
 419     $html .= '</div>' . "\n";
 420     $html .= '<div class="clearfloat"></div>' . "\n";
 421     $html .= '<div class="formelement hide">' . "\n";
 422     $html .= __('Variables');
 423     $html .= PMA\libraries\Util::showDocu('faq', 'faqbookmark');
 424     $html .= '<div id="bookmark_variables"></div>';
 425     $html .= '</div>' . "\n";
 426     $html .= '</fieldset>' . "\n";
 427
 428     $html .= '<fieldset id="fieldsetBookmarkOptionsFooter" class="tblFooters">';
 429     $html .= '<input type="submit" name="SQL" id="button_submit_bookmark" value="'
 430         . __('Go') . '" />';
 431     $html .= '<div class="clearfloat"></div>' . "\n";
 432     $html .= '</fieldset>' . "\n";
 433
 434     return $html;
 435 }