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