db_routines.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Routines management.
   5  *
   6  * @package phpMyAdmin
   7  */
   8
   9 /**
  10  * Include required files
  11  */
  12 require_once './libraries/common.inc.php';
  13 require_once './libraries/common.lib.php';
  14 require_once './libraries/db_routines.lib.php';
  15 require_once './libraries/mysql_charsets.lib.php';
  16 require_once './libraries/data_mysql.inc.php';
  17
  18 /**
  19  * Include JavaScript libraries
  20  */
  21 $GLOBALS['js_include'][] = 'jquery/jquery-ui-1.8.custom.js';
  22 $GLOBALS['js_include'][] = 'jquery/timepicker.js';
  23 $GLOBALS['js_include'][] = 'db_routines.js';
  24
  25 /**
  26  * Create labels for the list
  27  */
  28 $titles = PMA_buildActionTitles();
  29
  30 if ($GLOBALS['is_ajax_request'] != true) {
  31     /**
  32      * Displays the header
  33      */
  34     require_once './libraries/db_common.inc.php';
  35     /**
  36      * Displays the tabs
  37      */
  38     require_once './libraries/db_info.inc.php';
  39 } else {
  40     if (strlen($db)) {
  41         PMA_DBI_select_db($db);
  42         if (! isset($url_query)) {
  43             $url_query = PMA_generate_common_url($db);
  44         }
  45     }
  46 }
  47
  48 /**
  49  * Process all requests
  50  */
  51
  52 // Some definitions
  53 $param_directions    = array('IN',
  54                              'OUT',
  55                              'INOUT');
  56 $param_opts_num      = array('UNSIGNED',
  57                              'ZEROFILL',
  58                              'UNSIGNED ZEROFILL');
  59 $param_sqldataaccess = array('NO SQL',
  60                              'CONTAINS SQL',
  61                              'READS SQL DATA',
  62                              'MODIFIES SQL DATA');
  63
  64 /**
  65  * Generate the conditional classes that will be used to attach jQuery events to links.
  66  */
  67 $ajax_class = array(
  68                   'add'    => '',
  69                   'edit'   => '',
  70                   'exec'   => '',
  71                   'drop'   => '',
  72                   'export' => ''
  73               );
  74 if ($GLOBALS['cfg']['AjaxEnable']) {
  75     $ajax_class['add']    = 'class="add_routine_anchor"';
  76     $ajax_class['edit']   = 'class="edit_routine_anchor"';
  77     $ajax_class['exec']   = 'class="exec_routine_anchor"';
  78     $ajax_class['drop']   = 'class="drop_routine_anchor"';
  79     $ajax_class['export'] = 'class="export_routine_anchor"';
  80 }
  81
  82 /**
  83  * Keep a list of errors that occured while processing an 'Add' or 'Edit' operation.
  84  */
  85 $routine_errors = array();
  86
  87 /**
  88  * Handle all user requests other than the default of listing routines
  89  */
  90 if (! empty($_REQUEST['execute_routine']) && ! empty($_REQUEST['routine_name'])) {
  91     // Build the queries
  92     $routine   = PMA_RTN_getRoutineDataFromName($db, $_REQUEST['routine_name'], false);
  93     if ($routine !== false) {
  94         $queries   = array();
  95         $end_query = array();
  96         $args      = array();
  97         for ($i=0; $i<$routine['num_params']; $i++) {
  98             if (isset($_REQUEST['params'][$routine['param_name'][$i]])) {
  99                 $value = $_REQUEST['params'][$routine['param_name'][$i]];
 100                 if (is_array($value)) { // is SET type
 101                     $value = implode(',', $value);
 102                 }
 103                 $value = PMA_sqlAddSlashes($value);
 104                 if (! empty($_REQUEST['funcs'][$routine['param_name'][$i]])
 105                       && in_array($_REQUEST['funcs'][$routine['param_name'][$i]], $cfg['Functions'])) {
 106                     $queries[] = "SET @p$i={$_REQUEST['funcs'][$routine['param_name'][$i]]}('$value');\n";
 107                 } else {
 108                     $queries[] = "SET @p$i='$value';\n";
 109                 }
 110                 $args[] = "@p$i";
 111             } else {
 112                 $args[] = "@p$i";
 113             }
 114             if ($routine['type'] == 'PROCEDURE') {
 115                 if ($routine['param_dir'][$i] == 'OUT' || $routine['param_dir'][$i] == 'INOUT') {
 116                     $end_query[] = "@p$i AS " . PMA_backquote($routine['param_name'][$i]);
 117                 }
 118             }
 119         }
 120         if ($routine['type'] == 'PROCEDURE') {
 121             $queries[] = "CALL " . PMA_backquote($routine['name'])
 122                        . "(" . implode(', ', $args) . ");\n";
 123             if (count($end_query)) {
 124                 $queries[] = "SELECT " . implode(', ', $end_query) . ";\n";
 125             }
 126         } else {
 127             $queries[] = "SELECT " . PMA_backquote($routine['name'])
 128                        . "(" . implode(', ', $args) . ") "
 129                        . "AS " . PMA_backquote($routine['name']) . ";\n";
 130         }
 131         // Execute the queries
 132         $affected = 0;
 133         $result = null;
 134         $outcome = true;
 135         foreach ($queries as $num => $query) {
 136             $resource = PMA_DBI_try_query($query);
 137             if ($resource === false) {
 138                 $outcome = false;
 139                 break;
 140             }
 141             while (true) {
 142                 if(! PMA_DBI_more_results()) {
 143                     break;
 144                 }
 145                 PMA_DBI_next_result();
 146             }
 147             if (substr($query, 0, 6) == 'SELECT') {
 148                 $result = $resource;
 149             } else if (substr($query, 0, 4) == 'CALL') {
 150                 $affected = PMA_DBI_affected_rows() - PMA_DBI_num_rows($resource);
 151             }
 152         }
 153         // Generate output
 154         if ($outcome) {
 155             $message = __('Your SQL query has been executed successfully');
 156             if ($routine['type'] == 'PROCEDURE') {
 157                 $message .= '<br />';
 158                 $message .= sprintf(_ngettext('%d row affected by the last statement inside the procedure', '%d rows affected by the last statement inside the procedure', $affected), $affected);
 159             }
 160             $message = PMA_message::success($message);
 161             // Pass the SQL queries through the "pretty printer"
 162             $output  = '<code class="sql" style="margin-bottom: 1em;">';
 163             $output .= PMA_SQP_formatHtml(PMA_SQP_parse(implode($queries)));
 164             $output .= '</code>';
 165             // Display results
 166             if ($result) {
 167                 $output .= "<fieldset><legend>";
 168                 $output .= sprintf(__('Execution results of routine %s'),
 169                                    PMA_backquote(htmlspecialchars($routine['name'])));
 170                 $output .= "</legend>";
 171                 $output .= "<table><tr>";
 172                 foreach (PMA_DBI_get_fields_meta($result) as $key => $field) {
 173                     $output .= "<th>" . htmlspecialchars($field->name) . "</th>";
 174                 }
 175                 $output .= "</tr>";
 176                 // Stored routines can only ever return ONE ROW.
 177                 $data = PMA_DBI_fetch_single_row($result);
 178                 foreach ($data as $key => $value) {
 179                     if ($value === null) {
 180                         $value = '<i>NULL</i>';
 181                     } else {
 182                         $value = htmlspecialchars($value);
 183                     }
 184                     $output .= "<td class='odd'>" . $value . "</td>";
 185                 }
 186                 $output .= "</table></fieldset>";
 187             } else {
 188                 $notice = __('MySQL returned an empty result set (i.e. zero rows).');
 189                 $output .= PMA_message::notice($notice)->getDisplay();
 190             }
 191         } else {
 192             $output = '';
 193             $message = PMA_message::error(sprintf(__('The following query has failed: "%s"'), $query) . '<br /><br />'
 194                                                 . __('MySQL said: ') . PMA_DBI_getError(null));
 195         }
 196         // Print/send output
 197         if ($GLOBALS['is_ajax_request']) {
 198             $extra_data = array('dialog' => false);
 199             PMA_ajaxResponse($message->getDisplay() . $output, $message->isSuccess(), $extra_data);
 200         } else {
 201             echo $message->getDisplay() . $output;
 202             if ($message->isError()) {
 203                 // At least one query has failed, so shouldn't
 204                 // execute any more queries, so we quit.
 205                 exit;
 206             }
 207             unset($_POST);
 208             // Now deliberately fall through to displaying the routines list
 209         }
 210     } else {
 211         $message = __('Error in processing request') . ' : '
 212                  . sprintf(__('No routine with name %1$s found in database %2$s'),
 213                            htmlspecialchars(PMA_backquote($_REQUEST['routine_name'])),
 214                            htmlspecialchars(PMA_backquote($db)));
 215         $message = PMA_message::error($message);
 216         if ($GLOBALS['is_ajax_request']) {
 217             PMA_ajaxResponse($message, $message->isSuccess());
 218         } else {
 219             echo $message->getDisplay();
 220             unset($_POST);
 221         }
 222     }
 223 } else if (! empty($_GET['execute_dialog']) && ! empty($_GET['routine_name'])) {
 224     /**
 225      * Display the execute form for a routine.
 226      */
 227     $routine = PMA_RTN_getRoutineDataFromName($db, $_GET['routine_name'], false);
 228     if ($routine !== false) {
 229         $form = PMA_RTN_getExecuteForm($routine, $GLOBALS['is_ajax_request']);
 230         if ($GLOBALS['is_ajax_request'] == true) {
 231             $extra_data = array();
 232             $extra_data['dialog'] = true;
 233             $extra_data['title']  = __("Execute routine") . " ";
 234             $extra_data['title'] .= PMA_backquote(htmlentities($_GET['routine_name'], ENT_QUOTES));
 235             PMA_ajaxResponse($form, true, $extra_data);
 236         } else {
 237             echo "\n\n<h2>" . __("Execute routine") . "</h2>\n\n";
 238             echo $form;
 239             require './libraries/footer.inc.php';
 240             // exit;
 241         }
 242     } else if (($GLOBALS['is_ajax_request'] == true)) {
 243         $message = __('Error in processing request') . ' : '
 244                  . sprintf(__('No routine with name %1$s found in database %2$s'),
 245                            htmlspecialchars(PMA_backquote($_REQUEST['routine_name'])),
 246                            htmlspecialchars(PMA_backquote($db)));
 247         $message = PMA_message::error($message);
 248         PMA_ajaxResponse($message, false);
 249     }
 250 } else if (! empty($_GET['exportroutine']) && ! empty($_GET['routine_name'])) {
 251     /**
 252      * Display the export for a routine.
 253      */
 254     $routine_name = htmlspecialchars(PMA_backquote($_GET['routine_name']));
 255     $routine_type = PMA_DBI_fetch_value("SELECT ROUTINE_TYPE "
 256                                       . "FROM INFORMATION_SCHEMA.ROUTINES "
 257                                       . "WHERE ROUTINE_SCHEMA='" . PMA_sqlAddSlashes($db) . "' "
 258                                       . "AND SPECIFIC_NAME='" . PMA_sqlAddSlashes($_GET['routine_name']) . "';");
 259     if (! empty($routine_type) && $create_proc = PMA_DBI_get_definition($db, $routine_type, $_GET['routine_name'])) {
 260         $create_proc = '<textarea cols="40" rows="15" style="width: 100%;">' . htmlspecialchars($create_proc) . '</textarea>';
 261         if ($GLOBALS['is_ajax_request']) {
 262             $extra_data = array('title' => sprintf(__('Export of routine %s'), $routine_name));
 263             PMA_ajaxResponse($create_proc, true, $extra_data);
 264         } else {
 265             echo '<fieldset>' . "\n"
 266                . ' <legend>' . sprintf(__('Export of routine %s'), $routine_name) . '</legend>' . "\n"
 267                . $create_proc . "\n"
 268                . '</fieldset>';
 269         }
 270     } else {
 271         $response = __('Error in processing request') . ' : '
 272                   . sprintf(__('No routine with name %1$s found in database %2$s'),
 273                             $routine_name, htmlspecialchars(PMA_backquote($db)));
 274         $response = PMA_message::error($response);
 275         if ($GLOBALS['is_ajax_request']) {
 276             PMA_ajaxResponse($response, false);
 277         } else {
 278             $response->display();
 279         }
 280     }
 281 } else if (! empty($_REQUEST['routine_process_addroutine']) || ! empty($_REQUEST['routine_process_editroutine'])) {
 282     /**
 283      * Handle a request to create/edit a routine
 284      */
 285     $sql_query = '';
 286     $routine_query = PMA_RTN_getQueryFromRequest();
 287     if (! count($routine_errors)) { // set by PMA_RTN_getQueryFromRequest()
 288         // Execute the created query
 289         if (! empty($_REQUEST['routine_process_editroutine'])) {
 290             if (! in_array($_REQUEST['routine_original_type'], array('PROCEDURE', 'FUNCTION'))) {
 291                 $routine_errors[] = sprintf(__('Invalid routine type: "%s"'), htmlspecialchars($_REQUEST['routine_original_type']));
 292             } else {
 293                 // Backup the old routine, in case something goes wrong
 294                 $create_routine = PMA_DBI_get_definition($db, $_REQUEST['routine_original_type'], $_REQUEST['routine_original_name']);
 295                 $drop_routine = "DROP {$_REQUEST['routine_original_type']} " . PMA_backquote($_REQUEST['routine_original_name']) . ";\n";
 296                 $result = PMA_DBI_try_query($drop_routine);
 297                 if (! $result) {
 298                     $routine_errors[] = sprintf(__('The following query has failed: "%s"'), $drop_routine) . '<br />'
 299                                       . __('MySQL said: ') . PMA_DBI_getError(null);
 300                 } else {
 301                     $result = PMA_DBI_try_query($routine_query);
 302                     if (! $result) {
 303                         $routine_errors[] = sprintf(__('The following query has failed: "%s"'), $routine_query) . '<br />'
 304                                           . __('MySQL said: ') . PMA_DBI_getError(null);
 305                         // We dropped the old routine, but were unable to create the new one
 306                         // Try to restore the backup query
 307                         $result = PMA_DBI_try_query($create_routine);
 308                         if (! $result) {
 309                             // OMG, this is really bad! We dropped the query, failed to create a new one
 310                             // and now even the backup query does not execute!
 311                             // This should not happen, but we better handle this just in case.
 312                             $routine_errors[] = __('Sorry, we failed to restore the dropped routine.') . '<br />'
 313                                               . __('The backed up query was:') . "\"$create_routine\"" . '<br />'
 314                                               . __('MySQL said: ') . PMA_DBI_getError(null);
 315                         }
 316                     } else {
 317                         $message = PMA_Message::success(__('Routine %1$s has been modified.'));
 318                         $message->addParam(PMA_backquote($_REQUEST['routine_name']));
 319                         $sql_query = $drop_routine . $routine_query;
 320                     }
 321                 }
 322             }
 323         } else {
 324             // 'Add a new routine' mode
 325             $result = PMA_DBI_try_query($routine_query);
 326             if (! $result) {
 327                 $routine_errors[] = sprintf(__('The following query has failed: "%s"'), $routine_query) . '<br /><br />'
 328                                   . __('MySQL said: ') . PMA_DBI_getError(null);
 329             } else {
 330                 $message = PMA_Message::success(__('Routine %1$s has been created.'));
 331                 $message->addParam(PMA_backquote($_REQUEST['routine_name']));
 332                 $sql_query = $routine_query;
 333             }
 334         }
 335     }
 336
 337     if (count($routine_errors)) {
 338         $message = PMA_Message::error(__('<b>One or more errors have occured while processing your request:</b>'));
 339         $message->addString('<ul>');
 340         foreach ($routine_errors as $num => $string) {
 341             $message->addString('<li>' . $string . '</li>');
 342         }
 343         $message->addString('</ul>');
 344     }
 345
 346     $output = PMA_showMessage($message, $sql_query);
 347     if ($GLOBALS['is_ajax_request']) {
 348         $extra_data = array();
 349         if ($message->isSuccess()) {
 350             $columns  = "`SPECIFIC_NAME`, `ROUTINE_NAME`, `ROUTINE_TYPE`, `DTD_IDENTIFIER`, `ROUTINE_DEFINITION`";
 351             $where    = "ROUTINE_SCHEMA='" . PMA_sqlAddSlashes($db) . "' AND ROUTINE_NAME='" . PMA_sqlAddSlashes($_REQUEST['routine_name']) . "'";
 352             $routine  = PMA_DBI_fetch_single_row("SELECT $columns FROM `INFORMATION_SCHEMA`.`ROUTINES` WHERE $where;");
 353             $extra_data['name']      = htmlspecialchars(strtoupper($_REQUEST['routine_name']));
 354             $extra_data['new_row']   = PMA_RTN_getRowForRoutinesList($routine, 0, true);
 355             $response = $output;
 356         } else {
 357             $response = $message;
 358         }
 359         PMA_ajaxResponse($response, $message->isSuccess(), $extra_data);
 360     }
 361 }
 362
 363 /**
 364  * Display a form used to add/edit a routine, if necessary
 365  */
 366 if (count($routine_errors) || ( empty($_REQUEST['routine_process_addroutine']) && empty($_REQUEST['routine_process_editroutine']) &&
 367           (! empty($_REQUEST['addroutine']) || ! empty($_REQUEST['editroutine'])
 368         || ! empty($_REQUEST['routine_addparameter']) || ! empty($_REQUEST['routine_removeparameter'])
 369         || ! empty($_REQUEST['routine_changetype'])))) { // FIXME: this must be simpler than that
 370     // Handle requests to add/remove parameters and changing routine type
 371     // This is necessary when JS is disabled
 372     $operation = '';
 373     if (! empty($_REQUEST['routine_addparameter'])) {
 374         $operation = 'add';
 375     } else if (! empty($_REQUEST['routine_removeparameter'])) {
 376         $operation = 'remove';
 377     } else if (! empty($_REQUEST['routine_changetype'])) {
 378         $operation = 'change';
 379     }
 380     // Get the data for the form (if any)
 381     if (! empty($_REQUEST['addroutine'])) {
 382         $title = __("Create routine");
 383         $routine = PMA_RTN_getRoutineDataFromRequest();
 384         $mode = 'add';
 385     } else if (! empty($_REQUEST['editroutine'])) {
 386         $title = __("Edit routine");
 387         if (! $operation && ! empty($_REQUEST['routine_name']) && empty($_REQUEST['routine_process_editroutine'])) {
 388             $routine = PMA_RTN_getRoutineDataFromName($db, $_REQUEST['routine_name']);
 389             if ($routine !== false) {
 390                 $routine['original_name'] = $routine['name'];
 391                 $routine['original_type'] = $routine['type'];
 392             }
 393         } else {
 394             $routine = PMA_RTN_getRoutineDataFromRequest();
 395         }
 396         $mode = 'edit';
 397     }
 398     if ($routine !== false) {
 399         // Show form
 400         $editor = PMA_RTN_getEditorForm($mode, $operation, $routine, $routine_errors, $GLOBALS['is_ajax_request']);
 401         if ($GLOBALS['is_ajax_request']) {
 402             $template   = PMA_RTN_getParameterRow();
 403             $extra_data = array('title' => $title, 'param_template' => $template, 'type' => $routine['type']);
 404             PMA_ajaxResponse($editor, true, $extra_data);
 405         }
 406         echo "\n\n<h2>$title</h2>\n\n$editor";
 407         require './libraries/footer.inc.php';
 408         // exit;
 409     } else {
 410         $message = __('Error in processing request') . ' : '
 411                  . sprintf(__('No routine with name %1$s found in database %2$s'),
 412                            htmlspecialchars(PMA_backquote($_REQUEST['routine_name'])),
 413                            htmlspecialchars(PMA_backquote($db)));
 414         $message = PMA_message::error($message);
 415         if ($GLOBALS['is_ajax_request']) {
 416             PMA_ajaxResponse($message, false);
 417         } else {
 418             $message->display();
 419         }
 420     }
 421 }
 422
 423 /**
 424  * Display a list of available routines
 425  */
 426 echo PMA_RTN_getRoutinesList();
 427
 428 /**
 429  * Display the form for adding a new routine, if the user has the privileges.
 430  */
 431 echo PMA_RTN_getAddRoutineLink();
 432
 433 /**
 434  * Display a warning for users with PHP's old "mysql" extension.
 435  */
 436 if ($GLOBALS['cfg']['Server']['extension'] === 'mysql') {
 437     trigger_error(__('You are using PHP\'s deprecated \'mysql\' extension, '
 438                    . 'which is not capable of handling multi queries. '
 439                    . '<b>The execution of some stored routines may fail!</b> '
 440                    . 'Please use the improved \'mysqli\' extension to '
 441                    . 'avoid any problems.'), E_USER_WARNING);
 442 }
 443
 444 if ($GLOBALS['is_ajax_request'] != true) {
 445     /**
 446      * Displays the footer
 447      */
 448     require './libraries/footer.inc.php';
 449 }
 450
 451 ?>