libraries/rte/rte_routines.lib.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Functions for routine management.
   5  *
   6  * @package PhpMyAdmin
   7  */
   8 if (! defined('PHPMYADMIN')) {
   9     exit;
  10 }
  11
  12 /**
  13  * Sets required globals
  14  */
  15 function PMA_RTN_setGlobals()
  16 {
  17     global $param_directions, $param_opts_num, $param_sqldataaccess;
  18
  19     $param_directions    = array('IN',
  20                                  'OUT',
  21                                  'INOUT');
  22     $param_opts_num      = array('UNSIGNED',
  23                                  'ZEROFILL',
  24                                  'UNSIGNED ZEROFILL');
  25     $param_sqldataaccess = array('NO SQL',
  26                                  'CONTAINS SQL',
  27                                  'READS SQL DATA',
  28                                  'MODIFIES SQL DATA');
  29 }
  30
  31 /**
  32  * Main function for the routines functionality
  33  */
  34 function PMA_RTN_main()
  35 {
  36     global $db;
  37
  38     PMA_RTN_setGlobals();
  39     /**
  40      * Process all requests
  41      */
  42     PMA_RTN_handleEditor();
  43     PMA_RTN_handleExecute();
  44     PMA_RTN_handleExport();
  45     /**
  46      * Display a list of available routines
  47      */
  48     $columns  = "`SPECIFIC_NAME`, `ROUTINE_NAME`, `ROUTINE_TYPE`, ";
  49     $columns .= "`DTD_IDENTIFIER`, `ROUTINE_DEFINITION`";
  50     $where    = "ROUTINE_SCHEMA='" . PMA_sqlAddSlashes($db) . "'";
  51     $items    = PMA_DBI_fetch_result(
  52         "SELECT $columns FROM `INFORMATION_SCHEMA`.`ROUTINES` WHERE $where;"
  53     );
  54     echo PMA_RTE_getList('routine', $items);
  55     /**
  56      * Display the form for adding a new routine, if the user has the privileges.
  57      */
  58     echo PMA_RTN_getFooterLinks();
  59     /**
  60      * Display a warning for users with PHP's old "mysql" extension.
  61      */
  62     if ($GLOBALS['cfg']['Server']['extension'] === 'mysql') {
  63         trigger_error(
  64             __('You are using PHP\'s deprecated \'mysql\' extension, '
  65             . 'which is not capable of handling multi queries. '
  66             . '<b>The execution of some stored routines may fail!</b> '
  67             . 'Please use the improved \'mysqli\' extension to '
  68             . 'avoid any problems.'),
  69             E_USER_WARNING
  70         );
  71     }
  72 } // end PMA_RTN_main()
  73
  74 /**
  75  * This function parses a string containing one parameter of a routine,
  76  * as returned by PMA_RTN_parseAllParameters() and returns an array containing
  77  * the information about this parameter.
  78  *
  79  * @param   string  $value    A string containing one parameter of a routine
  80  *
  81  * @return  array             Parsed information about the input parameter
  82  */
  83 function PMA_RTN_parseOneParameter($value)
  84 {
  85     global $param_directions;
  86
  87     $retval = array(0 => '',
  88                     1 => '',
  89                     2 => '',
  90                     3 => '',
  91                     4 => '');
  92     $parsed_param = PMA_SQP_parse($value);
  93     $pos = 0;
  94     if (in_array(strtoupper($parsed_param[$pos]['data']), $param_directions)) {
  95         $retval[0] = strtoupper($parsed_param[0]['data']);
  96         $pos++;
  97     }
  98     if ($parsed_param[$pos]['type'] == 'alpha_identifier'
  99         || $parsed_param[$pos]['type'] == 'quote_backtick'
 100     ) {
 101         $retval[1] = PMA_unQuote($parsed_param[$pos]['data']);
 102         $pos++;
 103     }
 104     $depth = 0;
 105     $param_length = '';
 106     $param_opts = array();
 107     for ($i=$pos; $i<$parsed_param['len']; $i++) {
 108         if (($parsed_param[$i]['type'] == 'alpha_columnType'
 109             || $parsed_param[$i]['type'] == 'alpha_functionName') && $depth == 0 // "CHAR" seems to be mistaken for a function by the parser
 110         ) {
 111             $retval[2] = strtoupper($parsed_param[$i]['data']);
 112         } else if ($parsed_param[$i]['type'] == 'punct_bracket_open_round' && $depth == 0) {
 113             $depth = 1;
 114         } else if ($parsed_param[$i]['type'] == 'punct_bracket_close_round' && $depth == 1) {
 115             $depth = 0;
 116         } else if ($depth == 1) {
 117             $param_length .= $parsed_param[$i]['data'];
 118         } else if ($parsed_param[$i]['type'] == 'alpha_reservedWord' && strtoupper($parsed_param[$i]['data']) == 'CHARSET' && $depth == 0) {
 119             if ($parsed_param[$i+1]['type'] == 'alpha_charset' || $parsed_param[$i+1]['type'] == 'alpha_identifier') {
 120                 $param_opts[] = strtolower($parsed_param[$i+1]['data']);
 121             }
 122         } else if ($parsed_param[$i]['type'] == 'alpha_columnAttrib' && $depth == 0) {
 123             $param_opts[] = strtoupper($parsed_param[$i]['data']);
 124         }
 125     }
 126     $retval[3] = $param_length;
 127     sort($param_opts);
 128     $retval[4] = implode(' ', $param_opts);
 129
 130     return $retval;
 131 } // end PMA_RTN_parseOneParameter()
 132
 133 /**
 134  * This function looks through the contents of a parsed
 135  * SHOW CREATE [PROCEDURE | FUNCTION] query and extracts
 136  * information about the routine's parameters.
 137  *
 138  * @param   array   $parsed_query  Parsed query, returned by by PMA_SQP_parse()
 139  * @param   string  $routine_type  Routine type: 'PROCEDURE' or 'FUNCTION'
 140  *
 141  * @return  array   Information about the parameteres of a routine.
 142  */
 143 function PMA_RTN_parseAllParameters($parsed_query, $routine_type)
 144 {
 145     $retval = array();
 146     $retval['num'] = 0;
 147
 148     // First get the list of parameters from the query
 149     $buffer = '';
 150     $params = array();
 151     $fetching = false;
 152     $depth = 0;
 153     for ($i=0; $i<$parsed_query['len']; $i++) {
 154         if ($parsed_query[$i]['type'] == 'alpha_reservedWord' && $parsed_query[$i]['data'] == $routine_type) {
 155             $fetching = true;
 156         } else if ($fetching == true && $parsed_query[$i]['type'] == 'punct_bracket_open_round') {
 157             $depth++;
 158             if ($depth > 1) {
 159                 $buffer .= $parsed_query[$i]['data'] . ' ';
 160             }
 161         } else if ($fetching == true && $parsed_query[$i]['type'] == 'punct_bracket_close_round') {
 162             $depth--;
 163             if ($depth > 0) {
 164                 $buffer .= $parsed_query[$i]['data'] . ' ';
 165             } else {
 166                 break;
 167             }
 168         } else if ($parsed_query[$i]['type'] == 'punct_listsep' && $depth == 1) {
 169             $params[] = $buffer;
 170             $retval['num']++;
 171             $buffer = '';
 172         } else if ($fetching == true && $depth > 0) {
 173             $buffer .= $parsed_query[$i]['data'] . ' ';
 174         }
 175     }
 176     if (! empty($buffer)) {
 177         $params[] = $buffer;
 178         $retval['num']++;
 179     }
 180     // Now parse each parameter individually
 181     foreach ($params as $key => $value) {
 182         list($retval['dir'][],
 183              $retval['name'][],
 184              $retval['type'][],
 185              $retval['length'][],
 186              $retval['opts'][]) = PMA_RTN_parseOneParameter($value);
 187     }
 188     // Since some indices of $retval may be still undefined, we fill
 189     // them each with an empty array to avoid E_ALL errors in PHP.
 190     foreach (array('dir', 'name', 'type', 'length', 'opts') as $key => $index) {
 191         if (! isset($retval[$index])) {
 192             $retval[$index] = array();
 193         }
 194     }
 195
 196     return $retval;
 197 } // end PMA_RTN_parseAllParameters()
 198
 199 /**
 200  * This function looks through the contents of a parsed
 201  * SHOW CREATE [PROCEDURE | FUNCTION] query and extracts
 202  * information about the routine's definer.
 203  *
 204  * @param   array   $parsed_query   Parsed query, returned by PMA_SQP_parse()
 205  *
 206  * @return  string  The definer of a routine.
 207  */
 208 function PMA_RTN_parseRoutineDefiner($parsed_query)
 209 {
 210     $retval = '';
 211     $fetching = false;
 212     for ($i=0; $i<$parsed_query['len']; $i++) {
 213         if ($parsed_query[$i]['type'] == 'alpha_reservedWord' && $parsed_query[$i]['data'] == 'DEFINER') {
 214             $fetching = true;
 215         } else if ($fetching == true && ($parsed_query[$i]['type'] != 'quote_backtick' && substr($parsed_query[$i]['type'], 0, 5) != 'punct')) {
 216             break;
 217         } else if ($fetching == true && $parsed_query[$i]['type'] == 'quote_backtick') {
 218             $retval .= PMA_unQuote($parsed_query[$i]['data']);
 219         } else if ($fetching == true && $parsed_query[$i]['type'] == 'punct_user') {
 220             $retval .= $parsed_query[$i]['data'];
 221         }
 222     }
 223     return $retval;
 224 } // end PMA_RTN_parseRoutineDefiner()
 225
 226 /**
 227  * Handles editor requests for adding or editing an item
 228  */
 229 function PMA_RTN_handleEditor()
 230 {
 231     global $_GET, $_POST, $_REQUEST, $GLOBALS, $db, $errors;
 232
 233     if (! empty($_REQUEST['editor_process_add'])
 234         || ! empty($_REQUEST['editor_process_edit'])
 235     ) {
 236         /**
 237          * Handle a request to create/edit a routine
 238          */
 239         $sql_query = '';
 240         $routine_query = PMA_RTN_getQueryFromRequest();
 241         if (! count($errors)) { // set by PMA_RTN_getQueryFromRequest()
 242             // Execute the created query
 243             if (! empty($_REQUEST['editor_process_edit'])) {
 244                 if (! in_array($_REQUEST['item_original_type'], array('PROCEDURE', 'FUNCTION'))) {
 245                     $errors[] = sprintf(__('Invalid routine type: "%s"'), htmlspecialchars($_REQUEST['item_original_type']));
 246                 } else {
 247                     // Backup the old routine, in case something goes wrong
 248                     $create_routine = PMA_DBI_get_definition($db, $_REQUEST['item_original_type'], $_REQUEST['item_original_name']);
 249                     $drop_routine = "DROP {$_REQUEST['item_original_type']} " . PMA_backquote($_REQUEST['item_original_name']) . ";\n";
 250                     $result = PMA_DBI_try_query($drop_routine);
 251                     if (! $result) {
 252                         $errors[] = sprintf(__('The following query has failed: "%s"'), $drop_routine) . '<br />'
 253                                           . __('MySQL said: ') . PMA_DBI_getError(null);
 254                     } else {
 255                         $result = PMA_DBI_try_query($routine_query);
 256                         if (! $result) {
 257                             $errors[] = sprintf(__('The following query has failed: "%s"'), $routine_query) . '<br />'
 258                                               . __('MySQL said: ') . PMA_DBI_getError(null);
 259                             // We dropped the old routine, but were unable to create the new one
 260                             // Try to restore the backup query
 261                             $result = PMA_DBI_try_query($create_routine);
 262                             if (! $result) {
 263                                 // OMG, this is really bad! We dropped the query, failed to create a new one
 264                                 // and now even the backup query does not execute!
 265                                 // This should not happen, but we better handle this just in case.
 266                                 $errors[] = __('Sorry, we failed to restore the dropped routine.') . '<br />'
 267                                                   . __('The backed up query was:') . "\"$create_routine\"" . '<br />'
 268                                                   . __('MySQL said: ') . PMA_DBI_getError(null);
 269                             }
 270                         } else {
 271                             $message = PMA_Message::success(__('Routine %1$s has been modified.'));
 272                             $message->addParam(PMA_backquote($_REQUEST['item_name']));
 273                             $sql_query = $drop_routine . $routine_query;
 274                         }
 275                     }
 276                 }
 277             } else {
 278                 // 'Add a new routine' mode
 279                 $result = PMA_DBI_try_query($routine_query);
 280                 if (! $result) {
 281                     $errors[] = sprintf(__('The following query has failed: "%s"'), $routine_query) . '<br /><br />'
 282                                       . __('MySQL said: ') . PMA_DBI_getError(null);
 283                 } else {
 284                     $message = PMA_Message::success(__('Routine %1$s has been created.'));
 285                     $message->addParam(PMA_backquote($_REQUEST['item_name']));
 286                     $sql_query = $routine_query;
 287                 }
 288             }
 289         }
 290
 291         if (count($errors)) {
 292             $message = PMA_Message::error(__('<b>One or more errors have occured while processing your request:</b>'));
 293             $message->addString('<ul>');
 294             foreach ($errors as $string) {
 295                 $message->addString('<li>' . $string . '</li>');
 296             }
 297             $message->addString('</ul>');
 298         }
 299
 300         $output = PMA_showMessage($message, $sql_query);
 301         if ($GLOBALS['is_ajax_request']) {
 302             $extra_data = array();
 303             if ($message->isSuccess()) {
 304                 $columns  = "`SPECIFIC_NAME`, `ROUTINE_NAME`, `ROUTINE_TYPE`, `DTD_IDENTIFIER`, `ROUTINE_DEFINITION`";
 305                 $where    = "ROUTINE_SCHEMA='" . PMA_sqlAddSlashes($db) . "' "
 306                           . "AND ROUTINE_NAME='" . PMA_sqlAddSlashes($_REQUEST['item_name']) . "'"
 307                           . "AND ROUTINE_TYPE='" . PMA_sqlAddSlashes($_REQUEST['item_type']) . "'";
 308                 $routine  = PMA_DBI_fetch_single_row("SELECT $columns FROM `INFORMATION_SCHEMA`.`ROUTINES` WHERE $where;");
 309                 $extra_data['name']    = htmlspecialchars(strtoupper($_REQUEST['item_name']));
 310                 $extra_data['new_row'] = PMA_RTN_getRowForList($routine);
 311                 $extra_data['insert']  = ! empty($routine);
 312                 $response = $output;
 313             } else {
 314                 $response = $message;
 315             }
 316             PMA_ajaxResponse($response, $message->isSuccess(), $extra_data);
 317         }
 318     }
 319
 320     /**
 321      * Display a form used to add/edit a routine, if necessary
 322      */
 323     if (count($errors) || ( empty($_REQUEST['editor_process_add']) && empty($_REQUEST['editor_process_edit'])
 324         && (! empty($_REQUEST['add_item']) || ! empty($_REQUEST['edit_item'])
 325         || ! empty($_REQUEST['routine_addparameter']) || ! empty($_REQUEST['routine_removeparameter'])
 326         || ! empty($_REQUEST['routine_changetype']))) // FIXME: this must be simpler than that
 327     ) {
 328         // Handle requests to add/remove parameters and changing routine type
 329         // This is necessary when JS is disabled
 330         $operation = '';
 331         if (! empty($_REQUEST['routine_addparameter'])) {
 332             $operation = 'add';
 333         } else if (! empty($_REQUEST['routine_removeparameter'])) {
 334             $operation = 'remove';
 335         } else if (! empty($_REQUEST['routine_changetype'])) {
 336             $operation = 'change';
 337         }
 338         // Get the data for the form (if any)
 339         if (! empty($_REQUEST['add_item'])) {
 340             $title = PMA_RTE_getWord('add');
 341             $routine = PMA_RTN_getDataFromRequest();
 342             $mode = 'add';
 343         } else if (! empty($_REQUEST['edit_item'])) {
 344             $title = __("Edit routine");
 345             if (! $operation && ! empty($_REQUEST['item_name']) && empty($_REQUEST['editor_process_edit'])) {
 346                 $routine = PMA_RTN_getDataFromName($_REQUEST['item_name'], $_REQUEST['item_type']);
 347                 if ($routine !== false) {
 348                     $routine['item_original_name'] = $routine['item_name'];
 349                     $routine['item_original_type'] = $routine['item_type'];
 350                 }
 351             } else {
 352                 $routine = PMA_RTN_getDataFromRequest();
 353             }
 354             $mode = 'edit';
 355         }
 356         if ($routine !== false) {
 357             // Show form
 358             $editor = PMA_RTN_getEditorForm($mode, $operation, $routine);
 359             if ($GLOBALS['is_ajax_request']) {
 360                 $template   = PMA_RTN_getParameterRow();
 361                 $extra_data = array('title' => $title,
 362                                     'param_template' => $template,
 363                                     'type' => $routine['item_type']);
 364                 PMA_ajaxResponse($editor, true, $extra_data);
 365             }
 366             echo "\n\n<h2>$title</h2>\n\n$editor";
 367             include './libraries/footer.inc.php';
 368             // exit;
 369         } else {
 370             $message  = __('Error in processing request') . ' : ';
 371             $message .= sprintf(
 372                 PMA_RTE_getWord('not_found'),
 373                 htmlspecialchars(PMA_backquote($_REQUEST['item_name'])),
 374                 htmlspecialchars(PMA_backquote($db))
 375             );
 376             $message = PMA_message::error($message);
 377             if ($GLOBALS['is_ajax_request']) {
 378                 PMA_ajaxResponse($message, false);
 379             } else {
 380                 $message->display();
 381             }
 382         }
 383     }
 384 } // end PMA_RTN_handleEditor()
 385
 386 /**
 387  * This function will generate the values that are required to
 388  * complete the editor form. It is especially necessary to handle
 389  * the 'Add another parameter', 'Remove last parameter' and
 390  * 'Change routine type' functionalities when JS is disabled.
 391  *
 392  * @return  array    Data necessary to create the routine editor.
 393  */
 394 function PMA_RTN_getDataFromRequest()
 395 {
 396     global $_REQUEST, $param_directions, $param_sqldataaccess;
 397
 398     $retval = array();
 399     $indices = array('item_name',
 400                      'item_original_name',
 401                      'item_returnlength',
 402                      'item_returnopts_num',
 403                      'item_returnopts_text',
 404                      'item_definition',
 405                      'item_comment',
 406                      'item_definer');
 407     foreach ($indices as $key => $index) {
 408         $retval[$index] = isset($_REQUEST[$index]) ? $_REQUEST[$index] : '';
 409     }
 410
 411     $retval['item_type']         = 'PROCEDURE';
 412     $retval['item_type_toggle']  = 'FUNCTION';
 413     if (isset($_REQUEST['item_type']) && $_REQUEST['item_type'] == 'FUNCTION') {
 414         $retval['item_type']         = 'FUNCTION';
 415         $retval['item_type_toggle']  = 'PROCEDURE';
 416     }
 417     $retval['item_original_type'] = 'PROCEDURE';
 418     if (isset($_REQUEST['item_original_type'])
 419         && $_REQUEST['item_original_type'] == 'FUNCTION'
 420     ) {
 421         $retval['item_original_type'] = 'FUNCTION';
 422     }
 423     $retval['item_num_params']      = 0;
 424     $retval['item_param_dir']       = array();
 425     $retval['item_param_name']      = array();
 426     $retval['item_param_type']      = array();
 427     $retval['item_param_length']    = array();
 428     $retval['item_param_opts_num']  = array();
 429     $retval['item_param_opts_text'] = array();
 430     if (   isset($_REQUEST['item_param_name'])
 431         && isset($_REQUEST['item_param_type'])
 432         && isset($_REQUEST['item_param_length'])
 433         && isset($_REQUEST['item_param_opts_num'])
 434         && isset($_REQUEST['item_param_opts_text'])
 435         && is_array($_REQUEST['item_param_name'])
 436         && is_array($_REQUEST['item_param_type'])
 437         && is_array($_REQUEST['item_param_length'])
 438         && is_array($_REQUEST['item_param_opts_num'])
 439         && is_array($_REQUEST['item_param_opts_text'])
 440     ) {
 441         if ($_REQUEST['item_type'] == 'PROCEDURE') {
 442             $retval['item_param_dir'] = $_REQUEST['item_param_dir'];
 443             foreach ($retval['item_param_dir'] as $key => $value) {
 444                 if (! in_array($value, $param_directions, true)) {
 445                     $retval['item_param_dir'][$key] = '';
 446                 }
 447             }
 448         }
 449         $retval['item_param_name'] = $_REQUEST['item_param_name'];
 450         $retval['item_param_type'] = $_REQUEST['item_param_type'];
 451         foreach ($retval['item_param_type'] as $key => $value) {
 452             if (! in_array($value, PMA_getSupportedDatatypes(), true)) {
 453                 $retval['item_param_type'][$key] = '';
 454             }
 455         }
 456         $retval['item_param_length']    = $_REQUEST['item_param_length'];
 457         $retval['item_param_opts_num']  = $_REQUEST['item_param_opts_num'];
 458         $retval['item_param_opts_text'] = $_REQUEST['item_param_opts_text'];
 459         $retval['item_num_params'] = max(
 460             count($retval['item_param_name']),
 461             count($retval['item_param_type']),
 462             count($retval['item_param_length']),
 463             count($retval['item_param_opts_num']),
 464             count($retval['item_param_opts_text'])
 465         );
 466     }
 467     $retval['item_returntype'] = '';
 468     if (isset($_REQUEST['item_returntype'])
 469         && in_array($_REQUEST['item_returntype'], PMA_getSupportedDatatypes())
 470     ) {
 471         $retval['item_returntype'] = $_REQUEST['item_returntype'];
 472     }
 473
 474     $retval['item_isdeterministic'] = '';
 475     if (isset($_REQUEST['item_isdeterministic'])
 476         && strtolower($_REQUEST['item_isdeterministic']) == 'on'
 477     ) {
 478         $retval['item_isdeterministic'] = " checked='checked'";
 479     }
 480     $retval['item_securitytype_definer'] = '';
 481     $retval['item_securitytype_invoker'] = '';
 482     if (isset($_REQUEST['item_securitytype'])) {
 483         if ($_REQUEST['item_securitytype'] === 'DEFINER') {
 484             $retval['item_securitytype_definer'] = " selected='selected'";
 485         } else if ($_REQUEST['item_securitytype'] === 'INVOKER') {
 486             $retval['item_securitytype_invoker'] = " selected='selected'";
 487         }
 488     }
 489     $retval['item_sqldataaccess'] = '';
 490     if (isset($_REQUEST['item_sqldataaccess'])
 491         && in_array($_REQUEST['item_sqldataaccess'], $param_sqldataaccess, true)
 492     ) {
 493         $retval['item_sqldataaccess'] = $_REQUEST['item_sqldataaccess'];
 494     }
 495
 496     return $retval;
 497 } // end function PMA_RTN_getDataFromRequest()
 498
 499 /**
 500  * This function will generate the values that are required to complete
 501  * the "Edit routine" form given the name of a routine.
 502  *
 503  * @param   string   $name   The name of the routine.
 504  * @param   string   $type   Type of routine (ROUTINE|PROCEDURE)
 505  * @param   bool     $all    Whether to return all data or just
 506  *                           the info about parameters.
 507  *
 508  * @return  array    Data necessary to create the routine editor.
 509  */
 510 function PMA_RTN_getDataFromName($name, $type, $all = true)
 511 {
 512     global $db;
 513
 514     $retval  = array();
 515
 516     // Build and execute the query
 517     $fields  = "SPECIFIC_NAME, ROUTINE_TYPE, DTD_IDENTIFIER, "
 518              . "ROUTINE_DEFINITION, IS_DETERMINISTIC, SQL_DATA_ACCESS, "
 519              . "ROUTINE_COMMENT, SECURITY_TYPE";
 520     $where   = "ROUTINE_SCHEMA='" . PMA_sqlAddSlashes($db) . "' "
 521              . "AND SPECIFIC_NAME='" . PMA_sqlAddSlashes($name) . "'"
 522              . "AND ROUTINE_TYPE='" . PMA_sqlAddSlashes($type) . "'";
 523     $query   = "SELECT $fields FROM INFORMATION_SCHEMA.ROUTINES WHERE $where;";
 524
 525     $routine = PMA_DBI_fetch_single_row($query);
 526
 527     if (! $routine) {
 528         return false;
 529     }
 530
 531     // Get required data
 532     $retval['item_name'] = $routine['SPECIFIC_NAME'];
 533     $retval['item_type'] = $routine['ROUTINE_TYPE'];
 534     $parsed_query = PMA_SQP_parse(
 535         PMA_DBI_get_definition(
 536             $db,
 537             $routine['ROUTINE_TYPE'],
 538             $routine['SPECIFIC_NAME']
 539         )
 540     );
 541     $params = PMA_RTN_parseAllParameters($parsed_query, $routine['ROUTINE_TYPE']);
 542     $retval['item_num_params']      = $params['num'];
 543     $retval['item_param_dir']       = $params['dir'];
 544     $retval['item_param_name']      = $params['name'];
 545     $retval['item_param_type']      = $params['type'];
 546     $retval['item_param_length']    = $params['length'];
 547     $retval['item_param_opts_num']  = $params['opts'];
 548     $retval['item_param_opts_text'] = $params['opts'];
 549
 550     // Get extra data
 551     if ($all) {
 552         if ($retval['item_type'] == 'FUNCTION') {
 553             $retval['item_type_toggle'] = 'PROCEDURE';
 554         } else {
 555             $retval['item_type_toggle'] = 'FUNCTION';
 556         }
 557         $retval['item_returntype']   = '';
 558         $retval['item_returnlength'] = '';
 559         $retval['item_returnopts_num']  = '';
 560         $retval['item_returnopts_text'] = '';
 561         if (! empty($routine['DTD_IDENTIFIER'])) {
 562             if (strlen($routine['DTD_IDENTIFIER']) > 63) {
 563                 // If the DTD_IDENTIFIER string from INFORMATION_SCHEMA is
 564                 // at least 64 characters, then it may actually have been
 565                 // chopped because that column is a varchar(64), so we will
 566                 // parse the output of SHOW CREATE query to get accurate
 567                 // information about the return variable.
 568                 $dtd = '';
 569                 $fetching = false;
 570                 for ($i=0; $i<$parsed_query['len']; $i++) {
 571                     if ($parsed_query[$i]['type'] == 'alpha_reservedWord'
 572                         && strtoupper($parsed_query[$i]['data']) == 'RETURNS'
 573                     ) {
 574                         $fetching = true;
 575                     } else if ($fetching == true && $parsed_query[$i]['type'] == 'alpha_reservedWord') {
 576                         // We will not be looking for options such as UNSIGNED
 577                         // or ZEROFILL because there is no way that a numeric
 578                         // field's DTD_IDENTIFIER can be longer than 64
 579                         // characters. We can safely assume that the return
 580                         // datatype is either ENUM or SET, so we only look
 581                         // for CHARSET.
 582                         $word = strtoupper($parsed_query[$i]['data']);
 583                         if ($word == 'CHARSET'
 584                             && ($parsed_query[$i+1]['type'] == 'alpha_charset'
 585                             || $parsed_query[$i+1]['type'] == 'alpha_identifier')
 586                         ) {
 587                             $dtd .= $word . ' ' . $parsed_query[$i+1]['data'];
 588                         }
 589                         break;
 590                     } else if ($fetching == true) {
 591                         $dtd .= $parsed_query[$i]['data'] . ' ';
 592                     }
 593                 }
 594                 $routine['DTD_IDENTIFIER'] = $dtd;
 595             }
 596             $returnparam = PMA_RTN_parseOneParameter($routine['DTD_IDENTIFIER']);
 597             $retval['item_returntype']      = $returnparam[2];
 598             $retval['item_returnlength']    = $returnparam[3];
 599             $retval['item_returnopts_num']  = $returnparam[4];
 600             $retval['item_returnopts_text'] = $returnparam[4];
 601         }
 602         $retval['item_definer']         = PMA_RTN_parseRoutineDefiner($parsed_query);
 603         $retval['item_definition']      = $routine['ROUTINE_DEFINITION'];
 604         $retval['item_isdeterministic'] = '';
 605         if ($routine['IS_DETERMINISTIC'] == 'YES') {
 606             $retval['item_isdeterministic'] = " checked='checked'";
 607         }
 608         $retval['item_securitytype_definer'] = '';
 609         $retval['item_securitytype_invoker'] = '';
 610         if ($routine['SECURITY_TYPE'] == 'DEFINER') {
 611             $retval['item_securitytype_definer'] = " selected='selected'";
 612         } else if ($routine['SECURITY_TYPE'] == 'INVOKER') {
 613             $retval['item_securitytype_invoker'] = " selected='selected'";
 614         }
 615         $retval['item_sqldataaccess'] = $routine['SQL_DATA_ACCESS'];
 616         $retval['item_comment']       = $routine['ROUTINE_COMMENT'];
 617     }
 618
 619     return $retval;
 620 } // PMA_RTN_getDataFromName()
 621
 622 /**
 623  * Creates one row for the parameter table used in the routine editor.
 624  *
 625  * @param   array    $routine    Data for the routine returned by
 626  *                               PMA_RTN_getDataFromRequest() or
 627  *                               PMA_RTN_getDataFromName()
 628  * @param   mixed    $index      Either a numeric index of the row being processed
 629  *                               or NULL to create a template row for AJAX request
 630  * @param   string   $class      Class used to hide the direction column, if the
 631  *                               row is for a stored function.
 632  *
 633  * @return    string    HTML code of one row of parameter table for the editor.
 634  */
 635 function PMA_RTN_getParameterRow($routine = array(), $index = null, $class = '')
 636 {
 637     global $param_directions, $param_opts_num, $titles;
 638
 639     if ($index === null) {
 640         // template row for AJAX request
 641         $i = 0;
 642         $index = '%s';
 643         $drop_class = '';
 644         $routine = array(
 645             'item_param_dir'       => array(0 => ''),
 646             'item_param_name'      => array(0 => ''),
 647             'item_param_type'      => array(0 => ''),
 648             'item_param_length'    => array(0 => ''),
 649             'item_param_opts_num'  => array(0 => ''),
 650             'item_param_opts_text' => array(0 => '')
 651         );
 652     } else if (! empty($routine)) {
 653         // regular row for routine editor
 654         $drop_class = ' hide';
 655         $i = $index;
 656     } else {
 657         // No input data. This shouldn't happen,
 658         // but better be safe than sorry.
 659         return '';
 660     }
 661
 662     // Create the output
 663     $retval  = "";
 664     $retval .= "        <tr>\n";
 665     $retval .= "            <td class='routine_direction_cell$class'>\n";
 666     $retval .= "                <select name='item_param_dir[$index]'>\n";
 667     foreach ($param_directions as $key => $value) {
 668         $selected = "";
 669         if (! empty($routine['item_param_dir'][$i])
 670             && $routine['item_param_dir'][$i] == $value
 671         ) {
 672             $selected = " selected='selected'";
 673         }
 674         $retval .= "                    <option$selected>$value</option>\n";
 675     }
 676     $retval .= "                </select>\n";
 677     $retval .= "            </td>\n";
 678     $retval .= "            <td><input name='item_param_name[$index]' type='text'\n";
 679     $retval .= "                       value='{$routine['item_param_name'][$i]}' /></td>\n";
 680     $retval .= "            <td><select name='item_param_type[$index]'>";
 681     $retval .= PMA_getSupportedDatatypes(true, $routine['item_param_type'][$i]) . "\n";
 682     $retval .= "            </select></td>\n";
 683     $retval .= "            <td>\n";
 684     $retval .= "                <input id='item_param_length_$index'\n";
 685     $retval .= "                       name='item_param_length[$index]' type='text'\n";
 686     $retval .= "                       value='{$routine['item_param_length'][$i]}' />\n";
 687     $retval .= "                <div class='enum_hint'>\n";
 688     $retval .= "                    <a class='open_enum_editor' target='_blank'\n";
 689     $retval .= "                       href='enum_editor.php?" . PMA_generate_common_url() . "&amp;values=" . $routine['item_param_length'][$i] . "&amp;field=" . $routine['item_param_name'][$i] . "'>\n";
 690     $retval .= "                        " . PMA_getImage('b_edit', '', array('title'=>__('ENUM/SET editor'))) . "\n";
 691     $retval .= "                    </a>\n";
 692     $retval .= "                </div>\n";
 693     $retval .= "            </td>\n";
 694     $retval .= "            <td class='hide no_len'>---</td>\n";
 695     $retval .= "            <td class='routine_param_opts_text'>\n";
 696     $retval .= PMA_generateCharsetDropdownBox(
 697         PMA_CSDROPDOWN_CHARSET,
 698         "item_param_opts_text[$index]",
 699         null,
 700         $routine['item_param_opts_text'][$i]
 701     );
 702     $retval .= "            </td>\n";
 703     $retval .= "            <td class='hide no_opts'>---</td>\n";
 704     $retval .= "            <td class='routine_param_opts_num'>\n";
 705     $retval .= "                <select name='item_param_opts_num[$index]'>\n";
 706     $retval .= "                    <option value=''></option>";
 707     foreach ($param_opts_num as $key => $value) {
 708         $selected = "";
 709         if (! empty($routine['item_param_opts_num'][$i])
 710             && $routine['item_param_opts_num'][$i] == $value
 711         ) {
 712             $selected = " selected='selected'";
 713         }
 714         $retval .= "<option$selected>$value</option>";
 715     }
 716     $retval .= "\n                </select>\n";
 717     $retval .= "            </td>\n";
 718     $retval .= "            <td class='routine_param_remove$drop_class'>\n";
 719     $retval .= "                <a href='#' class='routine_param_remove_anchor'>\n";
 720     $retval .= "                    {$titles['Drop']}\n";
 721     $retval .= "                </a>\n";
 722     $retval .= "            </td>\n";
 723     $retval .= "        </tr>\n";
 724
 725     return $retval;
 726 } // end PMA_RTN_getParameterRow()
 727
 728 /**
 729  * Displays a form used to add/edit a routine
 730  *
 731  * @param   string   $mode         If the editor will be used edit a routine
 732  *                                 or add a new one: 'edit' or 'add'.
 733  * @param   string   $operation    If the editor was previously invoked with
 734  *                                 JS turned off, this will hold the name of
 735  *                                 the current operation
 736  * @param   array    $routine      Data for the routine returned by
 737  *                                 PMA_RTN_getDataFromRequest() or
 738  *                                 PMA_RTN_getDataFromName()
 739  *
 740  * @return  string   HTML code for the editor.
 741  */
 742 function PMA_RTN_getEditorForm($mode, $operation, $routine)
 743 {
 744     global $db, $errors, $param_sqldataaccess, $param_opts_num;
 745
 746     // Escape special characters
 747     $need_escape = array(
 748         'item_original_name',
 749         'item_name',
 750         'item_returnlength',
 751         'item_definition',
 752         'item_definer',
 753         'item_comment'
 754     );
 755     foreach ($need_escape as $key => $index) {
 756         $routine[$index] = htmlentities($routine[$index], ENT_QUOTES);
 757     }
 758     for ($i=0; $i<$routine['item_num_params']; $i++) {
 759         $routine['item_param_name'][$i]   = htmlentities(
 760             $routine['item_param_name'][$i],
 761             ENT_QUOTES
 762         );
 763         $routine['item_param_length'][$i] = htmlentities(
 764             $routine['item_param_length'][$i],
 765             ENT_QUOTES
 766         );
 767     }
 768
 769     // Handle some logic first
 770     if ($operation == 'change') {
 771         if ($routine['item_type'] == 'PROCEDURE') {
 772             $routine['item_type']        = 'FUNCTION';
 773             $routine['item_type_toggle'] = 'PROCEDURE';
 774         } else {
 775             $routine['item_type']        = 'PROCEDURE';
 776             $routine['item_type_toggle'] = 'FUNCTION';
 777         }
 778     } else if ($operation == 'add' || ($routine['item_num_params'] == 0 && $mode == 'add' && ! $errors)) {
 779         $routine['item_param_dir'][]       = '';
 780         $routine['item_param_name'][]      = '';
 781         $routine['item_param_type'][]      = '';
 782         $routine['item_param_length'][]    = '';
 783         $routine['item_param_opts_num'][]  = '';
 784         $routine['item_param_opts_text'][] = '';
 785         $routine['item_num_params']++;
 786     } else if ($operation == 'remove') {
 787         unset($routine['item_param_dir'][$routine['item_num_params']-1]);
 788         unset($routine['item_param_name'][$routine['item_num_params']-1]);
 789         unset($routine['item_param_type'][$routine['item_num_params']-1]);
 790         unset($routine['item_param_length'][$routine['item_num_params']-1]);
 791         unset($routine['item_param_opts_num'][$routine['item_num_params']-1]);
 792         unset($routine['item_param_opts_text'][$routine['item_num_params']-1]);
 793         $routine['item_num_params']--;
 794     }
 795     $disable_remove_parameter = '';
 796     if (! $routine['item_num_params']) {
 797         $disable_remove_parameter = " color: gray;' disabled='disabled";
 798     }
 799     $original_routine = '';
 800     if ($mode == 'edit') {
 801         $original_routine = "<input name='item_original_name' "
 802                           . "type='hidden' "
 803                           . "value='{$routine['item_original_name']}'/>\n"
 804                           . "<input name='item_original_type' "
 805                           . "type='hidden' "
 806                           . "value='{$routine['item_original_type']}'/>\n";
 807     }
 808     $isfunction_class   = '';
 809     $isprocedure_class  = '';
 810     $isfunction_select  = '';
 811     $isprocedure_select = '';
 812     if ($routine['item_type'] == 'PROCEDURE') {
 813         $isfunction_class   = ' hide';
 814         $isprocedure_select = " selected='selected'";
 815     } else {
 816         $isprocedure_class = ' hide';
 817         $isfunction_select = " selected='selected'";
 818     }
 819
 820     // Create the output
 821     $retval  = "";
 822     $retval .= "<!-- START " . strtoupper($mode) . " ROUTINE FORM -->\n\n";
 823     $retval .= "<form class='rte_form' action='db_routines.php' method='post'>\n";
 824     $retval .= "<input name='{$mode}_item' type='hidden' value='1' />\n";
 825     $retval .= $original_routine;
 826     $retval .= PMA_generate_common_hidden_inputs($db) . "\n";
 827     $retval .= "<fieldset>\n";
 828     $retval .= "<legend>" . __('Details') . "</legend>\n";
 829     $retval .= "<table class='rte_table' style='width: 100%'>\n";
 830     $retval .= "<tr>\n";
 831     $retval .= "    <td style='width: 20%;'>" . __('Routine name') . "</td>\n";
 832     $retval .= "    <td><input type='text' name='item_name' maxlength='64'\n";
 833     $retval .= "               value='{$routine['item_name']}' /></td>\n";
 834     $retval .= "</tr>\n";
 835     $retval .= "<tr>\n";
 836     $retval .= "    <td>" . __('Type') . "</td>\n";
 837     $retval .= "    <td>\n";
 838     if ($GLOBALS['is_ajax_request']) {
 839         $retval .= "        <select name='item_type'>\n";
 840         $retval .= "            <option value='PROCEDURE'$isprocedure_select>PROCEDURE</option>\n";
 841         $retval .= "            <option value='FUNCTION'$isfunction_select>FUNCTION</option>\n";
 842         $retval .= "        </select>\n";
 843     } else {
 844         $retval .= "        <input name='item_type' type='hidden' value='{$routine['item_type']}' />\n";
 845         $retval .= "        <div style='width: 49%; float: left; text-align: center; font-weight: bold;'>\n";
 846         $retval .= "            {$routine['item_type']}\n";
 847         $retval .= "        </div>\n";
 848         $retval .= "        <input style='width: 49%;' type='submit' name='routine_changetype'\n";
 849         $retval .= "               value='".sprintf(__('Change to %s'), $routine['item_type_toggle'])."' />\n";
 850     }
 851     $retval .= "    </td>\n";
 852     $retval .= "</tr>\n";
 853     $retval .= "<tr>\n";
 854     $retval .= "    <td>" . __('Parameters') . "</td>\n";
 855     $retval .= "    <td>\n";
 856     // parameter handling start
 857     $retval .= "        <table class='routine_params_table'>\n";
 858     $retval .= "        <tr>\n";
 859     $retval .= "            <th class='routine_direction_cell$isprocedure_class'>" . __('Direction') . "</th>\n";
 860     $retval .= "            <th>" . __('Name') . "</th>\n";
 861     $retval .= "            <th>" . __('Type') . "</th>\n";
 862     $retval .= "            <th>" . __('Length/Values') . "</th>\n";
 863     $retval .= "            <th colspan='2'>" . __('Options') . "</th>\n";
 864     $retval .= "            <th class='routine_param_remove hide'>&nbsp;</th>\n";
 865     $retval .= "        </tr>";
 866     for ($i=0; $i<$routine['item_num_params']; $i++) { // each parameter
 867         $retval .= PMA_RTN_getParameterRow($routine, $i, $isprocedure_class);
 868     }
 869     $retval .= "        </table>\n";
 870     $retval .= "    </td>\n";
 871     $retval .= "</tr>\n";
 872     $retval .= "<tr>\n";
 873     $retval .= "    <td>&nbsp;</td>\n";
 874     $retval .= "    <td>\n";
 875     $retval .= "        <input style='width: 49%;' type='submit' \n";
 876     $retval .= "               name='routine_addparameter'\n";
 877     $retval .= "               value='" . __('Add parameter') . "' />\n";
 878     $retval .= "        <input style='width: 49%;$disable_remove_parameter'\n";
 879     $retval .= "               type='submit' \n";
 880     $retval .= "               name='routine_removeparameter'\n";
 881     $retval .= "               value='" . __('Remove last parameter') . "' />\n";
 882     $retval .= "    </td>\n";
 883     $retval .= "</tr>\n";
 884     // parameter handling end
 885     $retval .= "<tr class='routine_return_row$isfunction_class'>\n";
 886     $retval .= "    <td>" . __('Return type') . "</td>\n";
 887     $retval .= "    <td><select name='item_returntype'>\n";
 888     $retval .= PMA_getSupportedDatatypes(true, $routine['item_returntype']) . "\n";
 889     $retval .= "    </select></td>\n";
 890     $retval .= "</tr>\n";
 891     $retval .= "<tr class='routine_return_row$isfunction_class'>\n";
 892     $retval .= "    <td>" . __('Return length/values') . "</td>\n";
 893     $retval .= "    <td><input type='text' name='item_returnlength'\n";
 894     $retval .= "               value='{$routine['item_returnlength']}' /></td>\n";
 895     $retval .= "    <td class='hide no_len'>---</td>\n";
 896     $retval .= "</tr>\n";
 897     $retval .= "<tr class='routine_return_row$isfunction_class'>\n";
 898     $retval .= "    <td>" . __('Return options') . "</td>\n";
 899     $retval .= "    <td><div>\n";
 900     $retval .= PMA_generateCharsetDropdownBox(
 901         PMA_CSDROPDOWN_CHARSET,
 902         "item_returnopts_text",
 903         null,
 904         $routine['item_returnopts_text']
 905     );
 906     $retval .= "\n    </div>\n";
 907     $retval .= "    <div><select name='item_returnopts_num'>\n";
 908     $retval .= "        <option value=''></option>";
 909     foreach ($param_opts_num as $key => $value) {
 910         $selected = "";
 911         if (! empty($routine['item_returnopts_num'])
 912             && $routine['item_returnopts_num'] == $value
 913         ) {
 914             $selected = " selected='selected'";
 915         }
 916         $retval .= "<option$selected>$value</option>";
 917     }
 918     $retval .= "\n    </select></div>\n";
 919     $retval .= "    <div class='hide no_opts'>---</div>\n";
 920     $retval .= "</td>\n";
 921     $retval .= "</tr>\n";
 922     $retval .= "<tr>\n";
 923     $retval .= "    <td>" . __('Definition') . "</td>\n";
 924     $retval .= "    <td><textarea name='item_definition' rows='15' cols='40'>";
 925     $retval .= $routine['item_definition'];
 926     $retval .= "</textarea></td>\n";
 927     $retval .= "</tr>\n";
 928     $retval .= "<tr>\n";
 929     $retval .= "    <td>" . __('Is deterministic') . "</td>\n";
 930     $retval .= "    <td><input type='checkbox' name='item_isdeterministic'{$routine['item_isdeterministic']} /></td>\n";
 931     $retval .= "</tr>\n";
 932     $retval .= "<tr>\n";
 933     $retval .= "    <td>" . __('Definer') . "</td>\n";
 934     $retval .= "    <td><input type='text' name='item_definer'\n";
 935     $retval .= "               value='{$routine['item_definer']}' /></td>\n";
 936     $retval .= "</tr>\n";
 937     $retval .= "<tr>\n";
 938     $retval .= "    <td>" . __('Security type') . "</td>\n";
 939     $retval .= "    <td><select name='item_securitytype'>\n";
 940     $retval .= "        <option value='DEFINER'{$routine['item_securitytype_definer']}>DEFINER</option>\n";
 941     $retval .= "        <option value='INVOKER'{$routine['item_securitytype_invoker']}>INVOKER</option>\n";
 942     $retval .= "    </select></td>\n";
 943     $retval .= "</tr>\n";
 944     $retval .= "<tr>\n";
 945     $retval .= "    <td>" . __('SQL data access') . "</td>\n";
 946     $retval .= "    <td><select name='item_sqldataaccess'>\n";
 947     foreach ($param_sqldataaccess as $key => $value) {
 948         $selected = "";
 949         if ($routine['item_sqldataaccess'] == $value) {
 950             $selected = " selected='selected'";
 951         }
 952         $retval .= "        <option$selected>$value</option>\n";
 953     }
 954     $retval .= "    </select></td>\n";
 955     $retval .= "</tr>\n";
 956     $retval .= "<tr>\n";
 957     $retval .= "    <td>" . __('Comment') . "</td>\n";
 958     $retval .= "    <td><input type='text' name='item_comment' maxlength='64'\n";
 959     $retval .= "               value='{$routine['item_comment']}' /></td>\n";
 960     $retval .= "</tr>\n";
 961     $retval .= "</table>\n";
 962     $retval .= "</fieldset>\n";
 963     if ($GLOBALS['is_ajax_request']) {
 964         $retval .= "<input type='hidden' name='editor_process_{$mode}'\n";
 965         $retval .= "       value='true' />\n";
 966         $retval .= "<input type='hidden' name='ajax_request' value='true' />\n";
 967     } else {
 968         $retval .= "<fieldset class='tblFooters'>\n";
 969         $retval .= "    <input type='submit' name='editor_process_{$mode}'\n";
 970         $retval .= "           value='" . __('Go') . "' />\n";
 971         $retval .= "</fieldset>\n";
 972     }
 973     $retval .= "</form>\n\n";
 974     $retval .= "<!-- END " . strtoupper($mode) . " ROUTINE FORM -->\n\n";
 975
 976     return $retval;
 977 } // end PMA_RTN_getEditorForm()
 978
 979 /**
 980  * Composes the query necessary to create a routine from an HTTP request.
 981  *
 982  * @return  string  The CREATE [ROUTINE | PROCEDURE] query.
 983  */
 984 function PMA_RTN_getQueryFromRequest()
 985 {
 986     global $_REQUEST, $cfg, $errors, $param_sqldataaccess, $param_directions;
 987
 988     $_REQUEST['item_type'] = isset($_REQUEST['item_type']) ? $_REQUEST['item_type'] : '';
 989
 990     $query = 'CREATE ';
 991     if (! empty($_REQUEST['item_definer'])) {
 992         if (strpos($_REQUEST['item_definer'], '@') !== false) {
 993             $arr = explode('@', $_REQUEST['item_definer']);
 994             $query .= 'DEFINER=' . PMA_backquote($arr[0]);
 995             $query .= '@' . PMA_backquote($arr[1]) . ' ';
 996         } else {
 997             $errors[] = __('The definer must be in the "username@hostname" format');
 998         }
 999     }
1000     if ($_REQUEST['item_type'] == 'FUNCTION'
1001         || $_REQUEST['item_type'] == 'PROCEDURE'
1002     ) {
1003         $query .= $_REQUEST['item_type'] . ' ';
1004     } else {
1005         $errors[] = sprintf(__('Invalid routine type: "%s"'), htmlspecialchars($_REQUEST['item_type']));
1006     }
1007     if (! empty($_REQUEST['item_name'])) {
1008         $query .= PMA_backquote($_REQUEST['item_name']);
1009     } else {
1010         $errors[] = __('You must provide a routine name');
1011     }
1012     $params = '';
1013     $warned_about_dir    = false;
1014     $warned_about_name   = false;
1015     $warned_about_length = false;
1016     if (   ! empty($_REQUEST['item_param_name'])
1017         && ! empty($_REQUEST['item_param_type'])
1018         && ! empty($_REQUEST['item_param_length'])
1019         && is_array($_REQUEST['item_param_name'])
1020         && is_array($_REQUEST['item_param_type'])
1021         && is_array($_REQUEST['item_param_length'])
1022     ) {
1023         for ($i=0; $i<count($_REQUEST['item_param_name']); $i++) {
1024             if (! empty($_REQUEST['item_param_name'][$i]) && ! empty($_REQUEST['item_param_type'][$i])) {
1025                 if ($_REQUEST['item_type'] == 'PROCEDURE'
1026                     && ! empty($_REQUEST['item_param_dir'][$i])
1027                     && in_array($_REQUEST['item_param_dir'][$i], $param_directions)
1028                 ) {
1029                     $params .= $_REQUEST['item_param_dir'][$i] . " " . PMA_backquote($_REQUEST['item_param_name'][$i]) . " "
1030                             . $_REQUEST['item_param_type'][$i];
1031                 } else if ($_REQUEST['item_type'] == 'FUNCTION') {
1032                     $params .= PMA_backquote($_REQUEST['item_param_name'][$i]) . " " . $_REQUEST['item_param_type'][$i];
1033                 } else if (! $warned_about_dir) {
1034                     $warned_about_dir = true;
1035                     $errors[] = sprintf(
1036                         __('Invalid direction "%s" given for parameter.'),
1037                         htmlspecialchars($_REQUEST['item_param_dir'][$i])
1038                     );
1039                 }
1040                 if ($_REQUEST['item_param_length'][$i] != ''
1041                     && !preg_match('@^(DATE|DATETIME|TIME|TINYBLOB|TINYTEXT|BLOB|TEXT|MEDIUMBLOB|MEDIUMTEXT|LONGBLOB|LONGTEXT|SERIAL|BOOLEAN)$@i',
1042                                    $_REQUEST['item_param_type'][$i])
1043                 ) {
1044                     $params .= "(" . $_REQUEST['item_param_length'][$i] . ")";
1045                 } else if ($_REQUEST['item_param_length'][$i] == '' && preg_match('@^(ENUM|SET|VARCHAR|VARBINARY)$@i', $_REQUEST['item_param_type'][$i])) {
1046                     if (! $warned_about_length) {
1047                         $warned_about_length = true;
1048                         $errors[] = __('You must provide length/values for routine parameters of type ENUM, SET, VARCHAR and VARBINARY.');
1049                     }
1050                 }
1051                 if (! empty($_REQUEST['item_param_opts_text'][$i])) {
1052                     if (in_array($_REQUEST['item_param_type'][$i], $cfg['ColumnTypes']['STRING'])) {
1053                         $params .= ' CHARSET ' . strtolower($_REQUEST['item_param_opts_text'][$i]);
1054                     }
1055                 }
1056                 if (! empty($_REQUEST['item_param_opts_num'][$i])) {
1057                     if (in_array($_REQUEST['item_param_type'][$i], $cfg['ColumnTypes']['NUMERIC'])) {
1058                         $params .= ' ' . strtoupper($_REQUEST['item_param_opts_num'][$i]);
1059                     }
1060                 }
1061                 if ($i != count($_REQUEST['item_param_name'])-1) {
1062                     $params .= ", ";
1063                 }
1064             } else if (! $warned_about_name) {
1065                 $warned_about_name = true;
1066                 $errors[] = __('You must provide a name and a type for each routine parameter.');
1067                 break;
1068             }
1069         }
1070     }
1071     $query .= "(" . $params . ") ";
1072     if ($_REQUEST['item_type'] == 'FUNCTION') {
1073         if (! empty($_REQUEST['item_returntype']) && in_array($_REQUEST['item_returntype'], PMA_getSupportedDatatypes())) {
1074             $query .= "RETURNS {$_REQUEST['item_returntype']}";
1075         } else {
1076             $errors[] = __('You must provide a valid return type for the routine.');
1077         }
1078         if (! empty($_REQUEST['item_returnlength'])
1079             && !preg_match('@^(DATE|DATETIME|TIME|TINYBLOB|TINYTEXT|BLOB|TEXT|MEDIUMBLOB|MEDIUMTEXT|LONGBLOB|LONGTEXT|SERIAL|BOOLEAN)$@i',
1080                             $_REQUEST['item_returntype'])
1081         ) {
1082             $query .= "(" . $_REQUEST['item_returnlength'] . ")";
1083         } else if (empty($_REQUEST['item_returnlength']) && preg_match('@^(ENUM|SET|VARCHAR|VARBINARY)$@i', $_REQUEST['item_returntype'])) {
1084             if (! $warned_about_length) {
1085                 $warned_about_length = true;
1086                 $errors[] = __('You must provide length/values for routine parameters of type ENUM, SET, VARCHAR and VARBINARY.');
1087             }
1088         }
1089         if (! empty($_REQUEST['item_returnopts_text'])) {
1090             if (in_array($_REQUEST['item_returntype'], $cfg['ColumnTypes']['STRING'])) {
1091                 $query .= ' CHARSET ' . strtolower($_REQUEST['item_returnopts_text']);
1092             }
1093         }
1094         if (! empty($_REQUEST['item_returnopts_num'])) {
1095             if (in_array($_REQUEST['item_returntype'], $cfg['ColumnTypes']['NUMERIC'])) {
1096                 $query .= ' ' . strtoupper($_REQUEST['item_returnopts_num']);
1097             }
1098         }
1099         $query .= ' ';
1100     }
1101     if (! empty($_REQUEST['item_comment'])) {
1102         $query .= "COMMENT '" . PMA_sqlAddslashes($_REQUEST['item_comment']) . "' ";
1103     }
1104     if (isset($_REQUEST['item_isdeterministic'])) {
1105         $query .= 'DETERMINISTIC ';
1106     } else {
1107         $query .= 'NOT DETERMINISTIC ';
1108     }
1109     if (! empty($_REQUEST['item_sqldataaccess']) && in_array($_REQUEST['item_sqldataaccess'], $param_sqldataaccess)) {
1110         $query .= $_REQUEST['item_sqldataaccess'] . ' ';
1111     }
1112     if (! empty($_REQUEST['item_securitytype'])) {
1113         if ($_REQUEST['item_securitytype'] == 'DEFINER' || $_REQUEST['item_securitytype'] == 'INVOKER') {
1114             $query .= 'SQL SECURITY ' . $_REQUEST['item_securitytype'] . ' ';
1115         }
1116     }
1117     if (! empty($_REQUEST['item_definition'])) {
1118         $query .= $_REQUEST['item_definition'];
1119     } else {
1120         $errors[] = __('You must provide a routine definition.');
1121     }
1122
1123     return $query;
1124 } // end PMA_RTN_getQueryFromRequest()
1125
1126 /**
1127  * Handles requests for executing a routine
1128  */
1129 function PMA_RTN_handleExecute()
1130 {
1131     global $_GET, $_POST, $_REQUEST, $GLOBALS, $db, $cfg;
1132
1133     /**
1134      * Handle all user requests other than the default of listing routines
1135      */
1136     if (! empty($_REQUEST['execute_routine']) && ! empty($_REQUEST['item_name'])) {
1137         // Build the queries
1138         $routine = PMA_RTN_getDataFromName($_REQUEST['item_name'], $_REQUEST['item_type'], false);
1139         if ($routine !== false) {
1140             $queries   = array();
1141             $end_query = array();
1142             $args      = array();
1143             for ($i=0; $i<$routine['item_num_params']; $i++) {
1144                 if (isset($_REQUEST['params'][$routine['item_param_name'][$i]])) {
1145                     $value = $_REQUEST['params'][$routine['item_param_name'][$i]];
1146                     if (is_array($value)) { // is SET type
1147                         $value = implode(',', $value);
1148                     }
1149                     $value = PMA_sqlAddSlashes($value);
1150                     if (! empty($_REQUEST['funcs'][$routine['item_param_name'][$i]])
1151                         && in_array($_REQUEST['funcs'][$routine['item_param_name'][$i]], $cfg['Functions'])
1152                     ) {
1153                         $queries[] = "SET @p$i={$_REQUEST['funcs'][$routine['item_param_name'][$i]]}('$value');\n";
1154                     } else {
1155                         $queries[] = "SET @p$i='$value';\n";
1156                     }
1157                     $args[] = "@p$i";
1158                 } else {
1159                     $args[] = "@p$i";
1160                 }
1161                 if ($routine['item_type'] == 'PROCEDURE') {
1162                     if ($routine['item_param_dir'][$i] == 'OUT'
1163                         || $routine['item_param_dir'][$i] == 'INOUT'
1164                     ) {
1165                         $end_query[] = "@p$i AS " . PMA_backquote($routine['item_param_name'][$i]);
1166                     }
1167                 }
1168             }
1169             if ($routine['item_type'] == 'PROCEDURE') {
1170                 $queries[] = "CALL " . PMA_backquote($routine['item_name'])
1171                            . "(" . implode(', ', $args) . ");\n";
1172                 if (count($end_query)) {
1173                     $queries[] = "SELECT " . implode(', ', $end_query) . ";\n";
1174                 }
1175             } else {
1176                 $queries[] = "SELECT " . PMA_backquote($routine['item_name'])
1177                            . "(" . implode(', ', $args) . ") "
1178                            . "AS " . PMA_backquote($routine['item_name']) . ";\n";
1179             }
1180             // Execute the queries
1181             $affected = 0;
1182             $result = null;
1183             $outcome = true;
1184             foreach ($queries as $query) {
1185                 $resource = PMA_DBI_try_query($query);
1186                 if ($resource === false) {
1187                     $outcome = false;
1188                     break;
1189                 }
1190                 while (true) {
1191                     if (! PMA_DBI_more_results()) {
1192                         break;
1193                     }
1194                     PMA_DBI_next_result();
1195                 }
1196                 if (substr($query, 0, 6) == 'SELECT') {
1197                     $result = $resource;
1198                 } else if (substr($query, 0, 4) == 'CALL') {
1199                     $result = $resource ? $resource : $result;
1200                     $affected = PMA_DBI_affected_rows() - PMA_DBI_num_rows($resource);
1201                 }
1202             }
1203             // Generate output
1204             if ($outcome) {
1205                 $message = __('Your SQL query has been executed successfully');
1206                 if ($routine['item_type'] == 'PROCEDURE') {
1207                     $message .= '<br />';
1208                     $message .= sprintf(
1209                         _ngettext(
1210                             '%d row affected by the last statement inside the procedure',
1211                             '%d rows affected by the last statement inside the procedure',
1212                             $affected
1213                         ),
1214                         $affected
1215                     );
1216                 }
1217                 $message = PMA_message::success($message);
1218                 // Pass the SQL queries through the "pretty printer"
1219                 $output  = '<code class="sql" style="margin-bottom: 1em;">';
1220                 $output .= PMA_SQP_formatHtml(PMA_SQP_parse(implode($queries)));
1221                 $output .= '</code>';
1222                 // Display results
1223                 if ($result) {
1224                     $output .= "<fieldset><legend>";
1225                     $output .= sprintf(
1226                         __('Execution results of routine %s'),
1227                         PMA_backquote(htmlspecialchars($routine['item_name']))
1228                     );
1229                     $output .= "</legend>";
1230                     $output .= "<table><tr>";
1231                     foreach (PMA_DBI_get_fields_meta($result) as $key => $field) {
1232                         $output .= "<th>";
1233                         $output .= htmlspecialchars($field->name);
1234                         $output .= "</th>";
1235                     }
1236                     $output .= "</tr>";
1237                     // Stored routines can only ever return ONE ROW.
1238                     $data = PMA_DBI_fetch_single_row($result);
1239                     foreach ($data as $key => $value) {
1240                         if ($value === null) {
1241                             $value = '<i>NULL</i>';
1242                         } else {
1243                             $value = htmlspecialchars($value);
1244                         }
1245                         $output .= "<td class='odd'>" . $value . "</td>";
1246                     }
1247                     $output .= "</table></fieldset>";
1248                 } else {
1249                     $notice = __('MySQL returned an empty result set (i.e. zero rows).');
1250                     $output .= PMA_message::notice($notice)->getDisplay();
1251                 }
1252             } else {
1253                 $output = '';
1254                 $message = PMA_message::error(sprintf(__('The following query has failed: "%s"'), $query) . '<br /><br />'
1255                                                     . __('MySQL said: ') . PMA_DBI_getError(null));
1256             }
1257             // Print/send output
1258             if ($GLOBALS['is_ajax_request']) {
1259                 $extra_data = array('dialog' => false);
1260                 PMA_ajaxResponse(
1261                     $message->getDisplay() . $output,
1262                     $message->isSuccess(),
1263                     $extra_data
1264                 );
1265             } else {
1266                 echo $message->getDisplay() . $output;
1267                 if ($message->isError()) {
1268                     // At least one query has failed, so shouldn't
1269                     // execute any more queries, so we quit.
1270                     exit;
1271                 }
1272                 unset($_POST);
1273                 // Now deliberately fall through to displaying the routines list
1274             }
1275         } else {
1276             $message  = __('Error in processing request') . ' : ';
1277             $message .= sprintf(
1278                 PMA_RTE_getWord('not_found'),
1279                 htmlspecialchars(PMA_backquote($_REQUEST['item_name'])),
1280                 htmlspecialchars(PMA_backquote($db))
1281             );
1282             $message = PMA_message::error($message);
1283             if ($GLOBALS['is_ajax_request']) {
1284                 PMA_ajaxResponse($message, $message->isSuccess());
1285             } else {
1286                 echo $message->getDisplay();
1287                 unset($_POST);
1288             }
1289         }
1290     } else if (! empty($_GET['execute_dialog']) && ! empty($_GET['item_name'])) {
1291         /**
1292          * Display the execute form for a routine.
1293          */
1294         $routine = PMA_RTN_getDataFromName($_GET['item_name'], $_GET['item_type'], true);
1295         if ($routine !== false) {
1296             $form = PMA_RTN_getExecuteForm($routine);
1297             if ($GLOBALS['is_ajax_request'] == true) {
1298                 $extra_data = array();
1299                 $extra_data['dialog'] = true;
1300                 $extra_data['title']  = __("Execute routine") . " ";
1301                 $extra_data['title'] .= PMA_backquote(
1302                     htmlentities($_GET['item_name'], ENT_QUOTES)
1303                 );
1304                 PMA_ajaxResponse($form, true, $extra_data);
1305             } else {
1306                 echo "\n\n<h2>" . __("Execute routine") . "</h2>\n\n";
1307                 echo $form;
1308                 include './libraries/footer.inc.php';
1309                 // exit;
1310             }
1311         } else if (($GLOBALS['is_ajax_request'] == true)) {
1312             $message  = __('Error in processing request') . ' : ';
1313             $message .= sprintf(
1314                 PMA_RTE_getWord('not_found'),
1315                 htmlspecialchars(PMA_backquote($_REQUEST['item_name'])),
1316                 htmlspecialchars(PMA_backquote($db))
1317             );
1318             $message = PMA_message::error($message);
1319             PMA_ajaxResponse($message, false);
1320         }
1321     }
1322 }
1323
1324 /**
1325  * Creates the HTML code that shows the routine execution dialog.
1326  *
1327  * @param   array    $routine      Data for the routine returned by
1328  *                                 PMA_RTN_getDataFromName()
1329  *
1330  * @return  string   HTML code for the routine execution dialog.
1331  */
1332 function PMA_RTN_getExecuteForm($routine)
1333 {
1334     global $db, $cfg;
1335
1336     // Escape special characters
1337     $routine['item_name'] = htmlentities($routine['item_name'], ENT_QUOTES);
1338     for ($i=0; $i<$routine['item_num_params']; $i++) {
1339         $routine['item_param_name'][$i] = htmlentities(
1340             $routine['item_param_name'][$i],
1341             ENT_QUOTES
1342         );
1343     }
1344
1345     // Create the output
1346     $retval  = "";
1347     $retval .= "<!-- START ROUTINE EXECUTE FORM -->\n\n";
1348     $retval .= "<form action='db_routines.php' method='post' class='rte_form'>\n";
1349     $retval .= "<input type='hidden' name='item_name'\n";
1350     $retval .= "       value='{$routine['item_name']}' />\n";
1351     $retval .= "<input type='hidden' name='item_type'\n";
1352     $retval .= "       value='{$routine['item_type']}' />\n";
1353     $retval .= PMA_generate_common_hidden_inputs($db) . "\n";
1354     $retval .= "<fieldset>\n";
1355     if ($GLOBALS['is_ajax_request'] != true) {
1356         $retval .= "<legend>{$routine['item_name']}</legend>\n";
1357         $retval .= "<table class='rte_table'>\n";
1358         $retval .= "<caption class='tblHeaders'>\n";
1359         $retval .= __('Routine parameters');
1360         $retval .= "</caption>\n";
1361     } else {
1362         $retval .= "<legend>" . __('Routine parameters') . "</legend>\n";
1363         $retval .= "<table class='rte_table' style='width: 100%;'>\n";
1364     }
1365     $retval .= "<tr>\n";
1366     $retval .= "<th>" . __('Name') . "</th>\n";
1367     $retval .= "<th>" . __('Type') . "</th>\n";
1368     if ($cfg['ShowFunctionFields']) {
1369         $retval .= "<th>" . __('Function') . "</th>\n";
1370     }
1371     $retval .= "<th>" . __('Value')    . "</th>\n";
1372     $retval .= "</tr>\n";
1373     // Get a list of data types that are not yet supported.
1374     $no_support_types = PMA_unsupportedDatatypes();
1375     for ($i=0; $i<$routine['item_num_params']; $i++) { // Each parameter
1376         if ($routine['item_type'] == 'PROCEDURE'
1377             && $routine['item_param_dir'][$i] == 'OUT'
1378         ) {
1379             continue;
1380         }
1381         $rowclass = ($i % 2 == 0) ? 'even' : 'odd';
1382         $retval .= "\n<tr class='$rowclass'>\n";
1383         $retval .= "<td>{$routine['item_param_name'][$i]}</td>\n";
1384         $retval .= "<td>{$routine['item_param_type'][$i]}</td>\n";
1385         if ($cfg['ShowFunctionFields']) {
1386             $retval .= "<td>\n";
1387             if (stristr($routine['item_param_type'][$i], 'enum')
1388                 || stristr($routine['item_param_type'][$i], 'set')
1389                 || in_array(strtolower($routine['item_param_type'][$i]), $no_support_types)
1390             ) {
1391                 $retval .= "--\n";
1392             } else {
1393                 $field = array(
1394                     'True_Type'       => strtolower($routine['item_param_type'][$i]),
1395                     'Type'            => '',
1396                     'Key'             => '',
1397                     'Field'           => '',
1398                     'Default'         => '',
1399                     'first_timestamp' => false
1400                 );
1401                 $retval .= "<select name='funcs[{$routine['item_param_name'][$i]}]'>";
1402                 $retval .= PMA_getFunctionsForField($field, false);
1403                 $retval .= "</select>";
1404             }
1405             $retval .= "</td>\n";
1406         }
1407         // Append a class to date/time fields so that
1408         // jQuery can attach a datepicker to them
1409         $class = '';
1410         if ($routine['item_param_type'][$i] == 'DATETIME'
1411             || $routine['item_param_type'][$i] == 'TIMESTAMP'
1412         ) {
1413             $class = 'datetimefield';
1414         } else if ($routine['item_param_type'][$i] == 'DATE') {
1415             $class = 'datefield';
1416         }
1417         $retval .= "<td class='nowrap'>\n";
1418         if (in_array($routine['item_param_type'][$i], array('ENUM', 'SET'))) {
1419             $tokens = PMA_SQP_parse($routine['item_param_length'][$i]);
1420             if ($routine['item_param_type'][$i] == 'ENUM') {
1421                 $input_type = 'radio';
1422             } else {
1423                 $input_type = 'checkbox';
1424             }
1425             for ($j=0; $j<$tokens['len']; $j++) {
1426                 if ($tokens[$j]['type'] != 'punct_listsep') {
1427                     $tokens[$j]['data'] = htmlentities(
1428                         PMA_unquote($tokens[$j]['data']),
1429                         ENT_QUOTES
1430                     );
1431                     $retval .= "<input name='params[{$routine['item_param_name'][$i]}][]' "
1432                              . "value='{$tokens[$j]['data']}' type='$input_type' />"
1433                              . "{$tokens[$j]['data']}<br />\n";
1434                 }
1435             }
1436         } else if (in_array(strtolower($routine['item_param_type'][$i]), $no_support_types)) {
1437             $retval .= "\n";
1438         } else {
1439             $retval .= "<input class='$class' type='text' name='params[{$routine['item_param_name'][$i]}]' />\n";
1440         }
1441         $retval .= "</td>\n";
1442         $retval .= "</tr>\n";
1443     }
1444     $retval .= "\n</table>\n";
1445     if ($GLOBALS['is_ajax_request'] != true) {
1446         $retval .= "</fieldset>\n\n";
1447         $retval .= "<fieldset class='tblFooters'>\n";
1448         $retval .= "    <input type='submit' name='execute_routine'\n";
1449         $retval .= "           value='" . __('Go') . "' />\n";
1450         $retval .= "</fieldset>\n";
1451     } else {
1452         $retval .= "<input type='hidden' name='execute_routine' value='true' />";
1453         $retval .= "<input type='hidden' name='ajax_request' value='true' />";
1454     }
1455     $retval .= "</form>\n\n";
1456     $retval .= "<!-- END ROUTINE EXECUTE FORM -->\n\n";
1457
1458     return $retval;
1459 } // end PMA_RTN_getExecuteForm()
1460
1461 ?>