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