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