src/Database/Routines.php

   1 <?php
   2
   3 declare(strict_types=1);
   4
   5 namespace PhpMyAdmin\Database;
   6
   7 use PhpMyAdmin\Charsets;
   8 use PhpMyAdmin\Config;
   9 use PhpMyAdmin\Current;
  10 use PhpMyAdmin\DatabaseInterface;
  11 use PhpMyAdmin\Html\Generator;
  12 use PhpMyAdmin\Message;
  13 use PhpMyAdmin\Query\Generator as QueryGenerator;
  14 use PhpMyAdmin\SqlParser\Parser;
  15 use PhpMyAdmin\SqlParser\Statements\CreateStatement;
  16 use PhpMyAdmin\SqlParser\TokensList;
  17 use PhpMyAdmin\SqlParser\Utils\Routine as RoutineUtils;
  18 use PhpMyAdmin\UserPrivileges;
  19 use PhpMyAdmin\Util;
  20
  21 use function __;
  22 use function _ngettext;
  23 use function array_column;
  24 use function array_merge;
  25 use function array_multisort;
  26 use function count;
  27 use function explode;
  28 use function htmlentities;
  29 use function htmlspecialchars;
  30 use function implode;
  31 use function in_array;
  32 use function is_array;
  33 use function is_string;
  34 use function max;
  35 use function mb_strtolower;
  36 use function mb_strtoupper;
  37 use function preg_match;
  38 use function sprintf;
  39 use function str_contains;
  40 use function str_ends_with;
  41 use function str_starts_with;
  42 use function stripos;
  43
  44 use const ENT_QUOTES;
  45 use const SORT_ASC;
  46
  47 /**
  48  * Functions for routine management.
  49  */
  50 class Routines
  51 {
  52     /** @var array<int, string> */
  53     public readonly array $directions;
  54
  55     /** @var array<int, string> */
  56     public readonly array $sqlDataAccess;
  57
  58     /** @var array<int, string> */
  59     public readonly array $numericOptions;
  60
  61     public function __construct(private DatabaseInterface $dbi)
  62     {
  63         $this->directions = ['IN', 'OUT', 'INOUT'];
  64         $this->sqlDataAccess = ['CONTAINS SQL', 'NO SQL', 'READS SQL DATA', 'MODIFIES SQL DATA'];
  65         $this->numericOptions = ['UNSIGNED', 'ZEROFILL', 'UNSIGNED ZEROFILL'];
  66     }
  67
  68     /**
  69      * Handle request to create or edit a routine
  70      */
  71     public function handleRequestCreateOrEdit(UserPrivileges $userPrivileges, string $db): string
  72     {
  73         $sqlQuery = '';
  74         $routineQuery = $this->getQueryFromRequest();
  75
  76         // set by getQueryFromRequest()
  77         if ($GLOBALS['errors'] === []) {
  78             // Execute the created query
  79             if (! empty($_POST['editor_process_edit'])) {
  80                 if (! in_array($_POST['item_original_type'], ['PROCEDURE', 'FUNCTION'], true)) {
  81                     $GLOBALS['errors'][] = sprintf(
  82                         __('Invalid routine type: "%s"'),
  83                         htmlspecialchars($_POST['item_original_type']),
  84                     );
  85                 } else {
  86                     // Backup the old routine, in case something goes wrong
  87                     if ($_POST['item_original_type'] === 'FUNCTION') {
  88                         $createRoutine = self::getFunctionDefinition($this->dbi, $db, $_POST['item_original_name']);
  89                     } else {
  90                         $createRoutine = self::getProcedureDefinition($this->dbi, $db, $_POST['item_original_name']);
  91                     }
  92
  93                     $privilegesBackup = $this->backupPrivileges($userPrivileges);
  94
  95                     $dropRoutine = 'DROP ' . $_POST['item_original_type'] . ' '
  96                         . Util::backquote($_POST['item_original_name'])
  97                         . ";\n";
  98                     $result = $this->dbi->tryQuery($dropRoutine);
  99                     if (! $result) {
 100                         $GLOBALS['errors'][] = sprintf(
 101                             __('The following query has failed: "%s"'),
 102                             htmlspecialchars($dropRoutine),
 103                         )
 104                         . '<br>'
 105                         . __('MySQL said: ') . $this->dbi->getError();
 106                     } else {
 107                         [$newErrors, $GLOBALS['message']] = $this->create(
 108                             $userPrivileges,
 109                             $routineQuery,
 110                             $createRoutine,
 111                             $privilegesBackup,
 112                         );
 113                         if (empty($newErrors)) {
 114                             $sqlQuery = $dropRoutine . $routineQuery;
 115                         } else {
 116                             $GLOBALS['errors'] = array_merge($GLOBALS['errors'], $newErrors);
 117                         }
 118
 119                         unset($newErrors);
 120                     }
 121                 }
 122             } else {
 123                 // 'Add a new routine' mode
 124                 $result = $this->dbi->tryQuery($routineQuery);
 125                 if (! $result) {
 126                     $GLOBALS['errors'][] = sprintf(
 127                         __('The following query has failed: "%s"'),
 128                         htmlspecialchars($routineQuery),
 129                     )
 130                     . '<br><br>'
 131                     . __('MySQL said: ') . $this->dbi->getError();
 132                 } else {
 133                     $GLOBALS['message'] = Message::success(
 134                         __('Routine %1$s has been created.'),
 135                     );
 136                     $GLOBALS['message']->addParam(
 137                         Util::backquote($_POST['item_name']),
 138                     );
 139                     $sqlQuery = $routineQuery;
 140                 }
 141             }
 142         }
 143
 144         if ($GLOBALS['errors'] !== []) {
 145             $GLOBALS['message'] = Message::error(
 146                 __(
 147                     'One or more errors have occurred while processing your request:',
 148                 ),
 149             );
 150             $GLOBALS['message']->addHtml('<ul>');
 151             foreach ($GLOBALS['errors'] as $string) {
 152                 $GLOBALS['message']->addHtml('<li>' . $string . '</li>');
 153             }
 154
 155             $GLOBALS['message']->addHtml('</ul>');
 156         }
 157
 158         return Generator::getMessage($GLOBALS['message'], $sqlQuery);
 159     }
 160
 161     /**
 162      * Backup the privileges
 163      *
 164      * @return string[][]
 165      */
 166     public function backupPrivileges(UserPrivileges $userPrivileges): array
 167     {
 168         if (! $userPrivileges->routines || ! $userPrivileges->isReload) {
 169             return [];
 170         }
 171
 172         // Backup the Old Privileges before dropping
 173         // if $_POST['item_adjust_privileges'] set
 174         if (empty($_POST['item_adjust_privileges'])) {
 175             return [];
 176         }
 177
 178         $privilegesBackupQuery = 'SELECT * FROM ' . Util::backquote('mysql')
 179         . '.' . Util::backquote('procs_priv')
 180         . ' WHERE Routine_name = ' . $this->dbi->quoteString($_POST['item_original_name'])
 181         . ' AND Routine_type = ' . $this->dbi->quoteString($_POST['item_original_type']);
 182
 183         return $this->dbi->fetchResult($privilegesBackupQuery, 0);
 184     }
 185
 186     /**
 187      * Create the routine
 188      *
 189      * @param string     $routineQuery     Query to create routine
 190      * @param string     $createRoutine    Query to restore routine
 191      * @param string[][] $privilegesBackup Privileges backup
 192      *
 193      * @return array{string[], Message|null}
 194      */
 195     public function create(
 196         UserPrivileges $userPrivileges,
 197         string $routineQuery,
 198         string $createRoutine,
 199         array $privilegesBackup,
 200     ): array {
 201         $result = $this->dbi->tryQuery($routineQuery);
 202         if (! $result) {
 203             $errors = [];
 204             $errors[] = sprintf(
 205                 __('The following query has failed: "%s"'),
 206                 htmlspecialchars($routineQuery),
 207             )
 208             . '<br>'
 209             . __('MySQL said: ') . $this->dbi->getError();
 210             // We dropped the old routine,
 211             // but were unable to create the new one
 212             // Try to restore the backup query
 213             $result = $this->dbi->tryQuery($createRoutine);
 214             if (! $result) {
 215                 // OMG, this is really bad! We dropped the query,
 216                 // failed to create a new one
 217                 // and now even the backup query does not execute!
 218                 // This should not happen, but we better handle
 219                 // this just in case.
 220                 $errors[] = __('Sorry, we failed to restore the dropped routine.') . '<br>'
 221                 . __('The backed up query was:')
 222                 . '"' . htmlspecialchars($createRoutine) . '"<br>'
 223                 . __('MySQL said: ') . $this->dbi->getError();
 224             }
 225
 226             return [$errors, null];
 227         }
 228
 229         // Default value
 230         $resultAdjust = false;
 231
 232         if ($userPrivileges->routines && $userPrivileges->isReload) {
 233             // Insert all the previous privileges
 234             // but with the new name and the new type
 235             foreach ($privilegesBackup as $priv) {
 236                 $adjustProcPrivilege = 'INSERT INTO '
 237                     . Util::backquote('mysql') . '.'
 238                     . Util::backquote('procs_priv')
 239                     . ' VALUES(' . $this->dbi->quoteString($priv[0]) . ', '
 240                     . $this->dbi->quoteString($priv[1]) . ', ' . $this->dbi->quoteString($priv[2]) . ', '
 241                     . $this->dbi->quoteString($_POST['item_name']) . ', '
 242                     . $this->dbi->quoteString($_POST['item_type']) . ', '
 243                     . $this->dbi->quoteString($priv[5]) . ', '
 244                     . $this->dbi->quoteString($priv[6]) . ', '
 245                     . $this->dbi->quoteString($priv[7]) . ');';
 246                 $this->dbi->query($adjustProcPrivilege);
 247                 $resultAdjust = true;
 248             }
 249         }
 250
 251         $message = $this->flushPrivileges($resultAdjust);
 252
 253         return [[], $message];
 254     }
 255
 256     /**
 257      * Flush privileges and get message
 258      *
 259      * @param bool $flushPrivileges Flush privileges
 260      */
 261     public function flushPrivileges(bool $flushPrivileges): Message
 262     {
 263         if ($flushPrivileges) {
 264             // Flush the Privileges
 265             $flushPrivQuery = 'FLUSH PRIVILEGES;';
 266             $this->dbi->query($flushPrivQuery);
 267
 268             $message = Message::success(
 269                 __(
 270                     'Routine %1$s has been modified. Privileges have been adjusted.',
 271                 ),
 272             );
 273         } else {
 274             $message = Message::success(
 275                 __('Routine %1$s has been modified.'),
 276             );
 277         }
 278
 279         $message->addParam(
 280             Util::backquote($_POST['item_name']),
 281         );
 282
 283         return $message;
 284     }
 285
 286     /**
 287      * This function will generate the values that are required to
 288      * complete the editor form. It is especially necessary to handle
 289      * the 'Add another parameter', 'Remove last parameter' and
 290      * 'Change routine type' functionalities when JS is disabled.
 291      *
 292      * @return mixed[]    Data necessary to create the routine editor.
 293      */
 294     public function getDataFromRequest(): array
 295     {
 296         $retval = [];
 297         $indices = [
 298             'item_name',
 299             'item_original_name',
 300             'item_returnlength',
 301             'item_returnopts_num',
 302             'item_returnopts_text',
 303             'item_definition',
 304             'item_comment',
 305             'item_definer',
 306         ];
 307         foreach ($indices as $index) {
 308             $retval[$index] = $_POST[$index] ?? '';
 309         }
 310
 311         $retval['item_type'] = 'PROCEDURE';
 312         $retval['item_type_toggle'] = 'FUNCTION';
 313         if (isset($_POST['item_type']) && $_POST['item_type'] === 'FUNCTION') {
 314             $retval['item_type'] = 'FUNCTION';
 315             $retval['item_type_toggle'] = 'PROCEDURE';
 316         }
 317
 318         $retval['item_original_type'] = 'PROCEDURE';
 319         if (isset($_POST['item_original_type']) && $_POST['item_original_type'] === 'FUNCTION') {
 320             $retval['item_original_type'] = 'FUNCTION';
 321         }
 322
 323         $retval['item_num_params'] = 0;
 324         $retval['item_param_dir'] = [];
 325         $retval['item_param_name'] = [];
 326         $retval['item_param_type'] = [];
 327         $retval['item_param_length'] = [];
 328         $retval['item_param_opts_num'] = [];
 329         $retval['item_param_opts_text'] = [];
 330         if (
 331             isset(
 332                 $_POST['item_param_name'],
 333                 $_POST['item_param_type'],
 334                 $_POST['item_param_length'],
 335                 $_POST['item_param_opts_num'],
 336                 $_POST['item_param_opts_text'],
 337             )
 338             && is_array($_POST['item_param_name'])
 339             && is_array($_POST['item_param_type'])
 340             && is_array($_POST['item_param_length'])
 341             && is_array($_POST['item_param_opts_num'])
 342             && is_array($_POST['item_param_opts_text'])
 343         ) {
 344             if ($_POST['item_type'] === 'PROCEDURE') {
 345                 $retval['item_param_dir'] = $_POST['item_param_dir'];
 346                 foreach ($retval['item_param_dir'] as $key => $value) {
 347                     if (in_array($value, $this->directions, true)) {
 348                         continue;
 349                     }
 350
 351                     $retval['item_param_dir'][$key] = '';
 352                 }
 353             }
 354
 355             $retval['item_param_name'] = $_POST['item_param_name'];
 356             $retval['item_param_type'] = $_POST['item_param_type'];
 357             foreach ($retval['item_param_type'] as $key => $value) {
 358                 if (in_array($value, Util::getSupportedDatatypes(), true)) {
 359                     continue;
 360                 }
 361
 362                 $retval['item_param_type'][$key] = '';
 363             }
 364
 365             $retval['item_param_length'] = $_POST['item_param_length'];
 366             $retval['item_param_opts_num'] = $_POST['item_param_opts_num'];
 367             $retval['item_param_opts_text'] = $_POST['item_param_opts_text'];
 368             $retval['item_num_params'] = max(
 369                 count($retval['item_param_name']),
 370                 count($retval['item_param_type']),
 371                 count($retval['item_param_length']),
 372                 count($retval['item_param_opts_num']),
 373                 count($retval['item_param_opts_text']),
 374             );
 375         }
 376
 377         $retval['item_returntype'] = '';
 378         if (
 379             isset($_POST['item_returntype'])
 380             && in_array($_POST['item_returntype'], Util::getSupportedDatatypes(), true)
 381         ) {
 382             $retval['item_returntype'] = $_POST['item_returntype'];
 383         }
 384
 385         $retval['item_isdeterministic'] = '';
 386         if (isset($_POST['item_isdeterministic']) && mb_strtolower($_POST['item_isdeterministic']) === 'on') {
 387             $retval['item_isdeterministic'] = " checked='checked'";
 388         }
 389
 390         $retval['item_securitytype_definer'] = '';
 391         $retval['item_securitytype_invoker'] = '';
 392         if (isset($_POST['item_securitytype'])) {
 393             if ($_POST['item_securitytype'] === 'DEFINER') {
 394                 $retval['item_securitytype_definer'] = " selected='selected'";
 395             } elseif ($_POST['item_securitytype'] === 'INVOKER') {
 396                 $retval['item_securitytype_invoker'] = " selected='selected'";
 397             }
 398         }
 399
 400         $retval['item_sqldataaccess'] = '';
 401         if (isset($_POST['item_sqldataaccess']) && in_array($_POST['item_sqldataaccess'], $this->sqlDataAccess, true)) {
 402             $retval['item_sqldataaccess'] = $_POST['item_sqldataaccess'];
 403         }
 404
 405         return $retval;
 406     }
 407
 408     /**
 409      * This function will generate the values that are required to complete
 410      * the "Edit routine" form given the name of a routine.
 411      *
 412      * @param string $name The name of the routine.
 413      * @param string $type Type of routine (ROUTINE|PROCEDURE)
 414      * @param bool   $all  Whether to return all data or just the info about parameters.
 415      *
 416      * @return mixed[]|null    Data necessary to create the routine editor.
 417      */
 418     public function getDataFromName(string $name, string $type, bool $all = true): array|null
 419     {
 420         $retval = [];
 421
 422         // Build and execute the query
 423         $fields = 'SPECIFIC_NAME, ROUTINE_TYPE, DTD_IDENTIFIER, '
 424                  . 'ROUTINE_DEFINITION, IS_DETERMINISTIC, SQL_DATA_ACCESS, '
 425                  . 'ROUTINE_COMMENT, SECURITY_TYPE';
 426         $where = 'ROUTINE_SCHEMA ' . Util::getCollateForIS() . '=' . $this->dbi->quoteString(Current::$database)
 427                  . ' AND SPECIFIC_NAME=' . $this->dbi->quoteString($name)
 428                  . ' AND ROUTINE_TYPE=' . $this->dbi->quoteString($type);
 429         $query = 'SELECT ' . $fields . ' FROM INFORMATION_SCHEMA.ROUTINES WHERE ' . $where . ';';
 430
 431         $routine = $this->dbi->fetchSingleRow($query);
 432
 433         if ($routine === null || $routine === []) {
 434             return null;
 435         }
 436
 437         // Get required data
 438         $retval['item_name'] = $routine['SPECIFIC_NAME'];
 439         $retval['item_type'] = $routine['ROUTINE_TYPE'];
 440
 441         if ($routine['ROUTINE_TYPE'] === 'FUNCTION') {
 442             $definition = self::getFunctionDefinition($this->dbi, Current::$database, $routine['SPECIFIC_NAME']);
 443         } else {
 444             $definition = self::getProcedureDefinition($this->dbi, Current::$database, $routine['SPECIFIC_NAME']);
 445         }
 446
 447         if ($definition === null) {
 448             return null;
 449         }
 450
 451         $parser = new Parser('DELIMITER $$' . "\n" . $definition);
 452
 453         /** @var CreateStatement $stmt */
 454         $stmt = $parser->statements[0];
 455
 456         // Do not use $routine['ROUTINE_DEFINITION'] because of a MySQL escaping issue: #15370
 457         $body = TokensList::buildFromArray($stmt->body);
 458         if ($body === '') {
 459             // Fallback just in case the parser fails
 460             $body = (string) $routine['ROUTINE_DEFINITION'];
 461         }
 462
 463         $params = RoutineUtils::getParameters($stmt);
 464         $retval['item_num_params'] = $params['num'];
 465         $retval['item_param_dir'] = $params['dir'];
 466         $retval['item_param_name'] = $params['name'];
 467         $retval['item_param_type'] = $params['type'];
 468         $retval['item_param_length'] = $params['length'];
 469         $retval['item_param_length_arr'] = $params['length_arr'];
 470         $retval['item_param_opts_num'] = $params['opts'];
 471         $retval['item_param_opts_text'] = $params['opts'];
 472
 473         // Get extra data
 474         if (! $all) {
 475             return $retval;
 476         }
 477
 478         if ($retval['item_type'] === 'FUNCTION') {
 479             $retval['item_type_toggle'] = 'PROCEDURE';
 480         } else {
 481             $retval['item_type_toggle'] = 'FUNCTION';
 482         }
 483
 484         $retval['item_returntype'] = '';
 485         $retval['item_returnlength'] = '';
 486         $retval['item_returnopts_num'] = '';
 487         $retval['item_returnopts_text'] = '';
 488
 489         if (! empty($routine['DTD_IDENTIFIER'])) {
 490             $options = [];
 491             foreach ($stmt->return->options->options as $opt) {
 492                 $options[] = is_string($opt) ? $opt : $opt['value'];
 493             }
 494
 495             $retval['item_returntype'] = $stmt->return->name;
 496             $retval['item_returnlength'] = implode(',', $stmt->return->parameters);
 497             $retval['item_returnopts_num'] = implode(' ', $options);
 498             $retval['item_returnopts_text'] = implode(' ', $options);
 499         }
 500
 501         $retval['item_definer'] = $stmt->options->has('DEFINER');
 502         $retval['item_definition'] = $body;
 503         $retval['item_isdeterministic'] = '';
 504         if ($routine['IS_DETERMINISTIC'] === 'YES') {
 505             $retval['item_isdeterministic'] = " checked='checked'";
 506         }
 507
 508         $retval['item_securitytype_definer'] = '';
 509         $retval['item_securitytype_invoker'] = '';
 510         if ($routine['SECURITY_TYPE'] === 'DEFINER') {
 511             $retval['item_securitytype_definer'] = " selected='selected'";
 512         } elseif ($routine['SECURITY_TYPE'] === 'INVOKER') {
 513             $retval['item_securitytype_invoker'] = " selected='selected'";
 514         }
 515
 516         $retval['item_sqldataaccess'] = $routine['SQL_DATA_ACCESS'];
 517         $retval['item_comment'] = $routine['ROUTINE_COMMENT'];
 518
 519         return $retval;
 520     }
 521
 522     /**
 523      * Creates one row for the parameter table used in the routine editor.
 524      *
 525      * @param mixed[] $routine Data for the routine returned by
 526      *                       getDataFromRequest() or getDataFromName()
 527      * @param mixed   $index   Either a numeric index of the row being processed
 528      *                         or NULL to create a template row for AJAX request
 529      * @param string  $class   Class used to hide the direction column, if the
 530      *                         row is for a stored function.
 531      *
 532      * @return mixed[]
 533      */
 534     public function getParameterRow(array $routine = [], mixed $index = null, string $class = ''): array
 535     {
 536         if ($index === null) {
 537             // template row for AJAX request
 538             $i = 0;
 539             $index = '%s';
 540             $dropClass = '';
 541             $routine = [
 542                 'item_param_dir' => [''],
 543                 'item_param_name' => [''],
 544                 'item_param_type' => [''],
 545                 'item_param_length' => [''],
 546                 'item_param_opts_num' => [''],
 547                 'item_param_opts_text' => [''],
 548             ];
 549         } elseif ($routine !== []) {
 550             // regular row for routine editor
 551             $dropClass = ' hide';
 552             $i = $index;
 553         } else {
 554             // No input data. This shouldn't happen,
 555             // but better be safe than sorry.
 556             return [];
 557         }
 558
 559         $allCharsets = Charsets::getCharsets($this->dbi, Config::getInstance()->selectedServer['DisableIS']);
 560         $charsets = [];
 561         foreach ($allCharsets as $charset) {
 562             $charsets[] = [
 563                 'name' => $charset->getName(),
 564                 'description' => $charset->getDescription(),
 565                 'is_selected' => $charset->getName() === $routine['item_param_opts_text'][$i],
 566             ];
 567         }
 568
 569         return [
 570             'class' => $class,
 571             'index' => $index,
 572             'param_directions' => $this->directions,
 573             'param_opts_num' => $this->numericOptions,
 574             'item_param_dir' => $routine['item_param_dir'][$i] ?? '',
 575             'item_param_name' => $routine['item_param_name'][$i] ?? '',
 576             'item_param_length' => $routine['item_param_length'][$i] ?? '',
 577             'item_param_opts_num' => $routine['item_param_opts_num'][$i] ?? '',
 578             'supported_datatypes' => Generator::getSupportedDatatypes(
 579                 $this->dbi->types->mapAliasToMysqlType($routine['item_param_type'][$i]),
 580             ),
 581             'charsets' => $charsets,
 582             'drop_class' => $dropClass,
 583         ];
 584     }
 585
 586     /**
 587      * Set the found errors and build the params
 588      *
 589      * @param string[] $itemParamName     The parameter names
 590      * @param string[] $itemParamDir      The direction parameter (see $this->directions)
 591      * @param mixed[]  $itemParamType     The parameter type
 592      * @param mixed[]  $itemParamLength   A length or not for the parameter
 593      * @param mixed[]  $itemParamOpsText  An optional charset for the parameter
 594      * @param mixed[]  $itemParamOpsNum   An optional parameter for a $itemParamType NUMBER
 595      * @param string   $itemType          The item type (PROCEDURE/FUNCTION)
 596      * @param bool     $warnedAboutLength A boolean that will be switched if a the length warning is given
 597      */
 598     private function processParamsAndBuild(
 599         array $itemParamName,
 600         array $itemParamDir,
 601         array $itemParamType,
 602         array $itemParamLength,
 603         array $itemParamOpsText,
 604         array $itemParamOpsNum,
 605         string $itemType,
 606         bool &$warnedAboutLength,
 607     ): string {
 608         $GLOBALS['errors'] ??= null;
 609
 610         $params = '';
 611         $warnedAboutDir = false;
 612
 613         for ($i = 0, $nb = count($itemParamName); $i < $nb; $i++) {
 614             if (empty($itemParamName[$i]) || empty($itemParamType[$i])) {
 615                 $GLOBALS['errors'][] = __('You must provide a name and a type for each routine parameter.');
 616                 break;
 617             }
 618
 619             if (
 620                 $itemType === 'PROCEDURE'
 621                 && ! empty($itemParamDir[$i])
 622                 && in_array($itemParamDir[$i], $this->directions, true)
 623             ) {
 624                 $params .= $itemParamDir[$i] . ' '
 625                     . Util::backquote($itemParamName[$i])
 626                     . ' ' . $itemParamType[$i];
 627             } elseif ($itemType === 'FUNCTION') {
 628                 $params .= Util::backquote($itemParamName[$i])
 629                     . ' ' . $itemParamType[$i];
 630             } elseif (! $warnedAboutDir) {
 631                 $warnedAboutDir = true;
 632                 $GLOBALS['errors'][] = sprintf(
 633                     __('Invalid direction "%s" given for parameter.'),
 634                     htmlspecialchars($itemParamDir[$i]),
 635                 );
 636             }
 637
 638             if (
 639                 $itemParamLength[$i] != ''
 640                 && ! preg_match(
 641                     '@^(DATE|TINYBLOB|TINYTEXT|BLOB|TEXT|MEDIUMBLOB|MEDIUMTEXT|LONGBLOB|LONGTEXT|SERIAL|BOOLEAN)$@i',
 642                     $itemParamType[$i],
 643                 )
 644             ) {
 645                 $params .= '(' . $itemParamLength[$i] . ')';
 646             } elseif (
 647                 $itemParamLength[$i] == ''
 648                 && preg_match('@^(ENUM|SET|VARCHAR|VARBINARY)$@i', $itemParamType[$i])
 649             ) {
 650                 if (! $warnedAboutLength) {
 651                     $warnedAboutLength = true;
 652                     $GLOBALS['errors'][] = __(
 653                         'You must provide length/values for routine parameters'
 654                         . ' of type ENUM, SET, VARCHAR and VARBINARY.',
 655                     );
 656                 }
 657             }
 658
 659             if (! empty($itemParamOpsText[$i])) {
 660                 if ($this->dbi->types->getTypeClass($itemParamType[$i]) === 'CHAR') {
 661                     if (! in_array($itemParamType[$i], ['VARBINARY', 'BINARY'], true)) {
 662                         $params .= ' CHARSET '
 663                             . mb_strtolower($itemParamOpsText[$i]);
 664                     }
 665                 }
 666             }
 667
 668             if (! empty($itemParamOpsNum[$i])) {
 669                 if ($this->dbi->types->getTypeClass($itemParamType[$i]) === 'NUMBER') {
 670                     $params .= ' '
 671                         . mb_strtoupper($itemParamOpsNum[$i]);
 672                 }
 673             }
 674
 675             if ($i === count($itemParamName) - 1) {
 676                 continue;
 677             }
 678
 679             $params .= ', ';
 680         }
 681
 682         return $params;
 683     }
 684
 685     /**
 686      * Set the found errors and build the query
 687      *
 688      * @param string $query             The existing query
 689      * @param bool   $warnedAboutLength If the length warning was given
 690      */
 691     private function processFunctionSpecificParameters(
 692         string $query,
 693         bool $warnedAboutLength,
 694     ): string {
 695         $GLOBALS['errors'] ??= null;
 696
 697         $itemReturnType = $_POST['item_returntype'] ?? null;
 698
 699         if ($itemReturnType !== '' && in_array($itemReturnType, Util::getSupportedDatatypes(), true)) {
 700             $query .= 'RETURNS ' . $itemReturnType;
 701         } else {
 702             $GLOBALS['errors'][] = __('You must provide a valid return type for the routine.');
 703         }
 704
 705         if (
 706             ! empty($_POST['item_returnlength'])
 707             && ! preg_match(
 708                 '@^(DATE|DATETIME|TIME|TINYBLOB|TINYTEXT|BLOB|TEXT|'
 709                 . 'MEDIUMBLOB|MEDIUMTEXT|LONGBLOB|LONGTEXT|SERIAL|BOOLEAN)$@i',
 710                 $itemReturnType,
 711             )
 712         ) {
 713             $query .= '(' . $_POST['item_returnlength'] . ')';
 714         } elseif (
 715             empty($_POST['item_returnlength'])
 716             && preg_match('@^(ENUM|SET|VARCHAR|VARBINARY)$@i', $itemReturnType)
 717         ) {
 718             if (! $warnedAboutLength) {
 719                 $GLOBALS['errors'][] = __(
 720                     'You must provide length/values for routine parameters of type ENUM, SET, VARCHAR and VARBINARY.',
 721                 );
 722             }
 723         }
 724
 725         if (! empty($_POST['item_returnopts_text'])) {
 726             if ($this->dbi->types->getTypeClass($itemReturnType) === 'CHAR') {
 727                 $query .= ' CHARSET '
 728                     . mb_strtolower($_POST['item_returnopts_text']);
 729             }
 730         }
 731
 732         if (! empty($_POST['item_returnopts_num'])) {
 733             if ($this->dbi->types->getTypeClass($itemReturnType) === 'NUMBER') {
 734                 $query .= ' '
 735                     . mb_strtoupper($_POST['item_returnopts_num']);
 736             }
 737         }
 738
 739         return $query . ' ';
 740     }
 741
 742     /**
 743      * Composes the query necessary to create a routine from an HTTP request.
 744      *
 745      * @return string  The CREATE [ROUTINE | PROCEDURE] query.
 746      */
 747     public function getQueryFromRequest(): string
 748     {
 749         $GLOBALS['errors'] ??= null;
 750
 751         $itemType = $_POST['item_type'] ?? '';
 752         $itemDefiner = $_POST['item_definer'] ?? '';
 753         $itemName = $_POST['item_name'] ?? '';
 754
 755         $query = 'CREATE ';
 756         if (! empty($itemDefiner)) {
 757             if (str_contains($itemDefiner, '@')) {
 758                 $arr = explode('@', $itemDefiner);
 759
 760                 $doBackquote = true;
 761                 if (str_starts_with($arr[0], '`') && str_ends_with($arr[0], '`')) {
 762                     $doBackquote = false;
 763                 }
 764
 765                 $query .= 'DEFINER=' . Util::backquoteCompat($arr[0], 'NONE', $doBackquote);
 766
 767                 $doBackquote = true;
 768                 if (str_starts_with($arr[1], '`') && str_ends_with($arr[1], '`')) {
 769                     $doBackquote = false;
 770                 }
 771
 772                 $query .= '@' . Util::backquoteCompat($arr[1], 'NONE', $doBackquote) . ' ';
 773             } else {
 774                 $GLOBALS['errors'][] = __('The definer must be in the "username@hostname" format!');
 775             }
 776         }
 777
 778         if ($itemType === 'FUNCTION' || $itemType === 'PROCEDURE') {
 779             $query .= $itemType . ' ';
 780         } else {
 781             $GLOBALS['errors'][] = sprintf(
 782                 __('Invalid routine type: "%s"'),
 783                 htmlspecialchars($itemType),
 784             );
 785         }
 786
 787         if (! empty($itemName)) {
 788             $query .= Util::backquote($itemName);
 789         } else {
 790             $GLOBALS['errors'][] = __('You must provide a routine name!');
 791         }
 792
 793         $warnedAboutLength = false;
 794
 795         $itemParamName = $_POST['item_param_name'] ?? '';
 796         $itemParamType = $_POST['item_param_type'] ?? '';
 797         $itemParamLength = $_POST['item_param_length'] ?? '';
 798         $itemParamDir = (array) ($_POST['item_param_dir'] ?? []);
 799         $itemParamOpsText = (array) ($_POST['item_param_opts_text'] ?? []);
 800         $itemParamOpsNum = (array) ($_POST['item_param_opts_num'] ?? []);
 801
 802         $params = '';
 803         if (
 804             ! empty($itemParamName)
 805             && ! empty($itemParamType)
 806             && ! empty($itemParamLength)
 807             && is_array($itemParamName)
 808             && is_array($itemParamType)
 809             && is_array($itemParamLength)
 810         ) {
 811             $params = $this->processParamsAndBuild(
 812                 $itemParamName,
 813                 $itemParamDir,
 814                 $itemParamType,
 815                 $itemParamLength,
 816                 $itemParamOpsText,
 817                 $itemParamOpsNum,
 818                 $itemType,
 819                 $warnedAboutLength, // Will possibly be modified by the function
 820             );
 821         }
 822
 823         $query .= '(' . $params . ') ';
 824         if ($itemType === 'FUNCTION') {
 825             $query = $this->processFunctionSpecificParameters($query, $warnedAboutLength);
 826         }
 827
 828         if (! empty($_POST['item_comment'])) {
 829             $query .= 'COMMENT ' . $this->dbi->quoteString($_POST['item_comment']) . ' ';
 830         }
 831
 832         if (isset($_POST['item_isdeterministic'])) {
 833             $query .= 'DETERMINISTIC ';
 834         } else {
 835             $query .= 'NOT DETERMINISTIC ';
 836         }
 837
 838         $itemSqlDataAccess = $_POST['item_sqldataaccess'] ?? '';
 839         if (in_array($itemSqlDataAccess, $this->sqlDataAccess, true)) {
 840             $query .= $itemSqlDataAccess . ' ';
 841         }
 842
 843         $itemSecurityType = $_POST['item_securitytype'] ?? '';
 844         if (! empty($itemSecurityType)) {
 845             if ($itemSecurityType === 'DEFINER' || $itemSecurityType === 'INVOKER') {
 846                 $query .= 'SQL SECURITY ' . $itemSecurityType . ' ';
 847             }
 848         }
 849
 850         $itemDefinition = $_POST['item_definition'] ?? '';
 851         if (! empty($itemDefinition)) {
 852             $query .= $itemDefinition;
 853         } else {
 854             $GLOBALS['errors'][] = __('You must provide a routine definition.');
 855         }
 856
 857         return $query;
 858     }
 859
 860     /**
 861      * @param mixed[] $routine The routine params
 862      *
 863      * @return string[] The SQL queries / SQL query parts
 864      */
 865     private function getQueriesFromRoutineForm(array $routine): array
 866     {
 867         $queries = [];
 868         $outParams = [];
 869         $args = [];
 870         $allFunctions = $this->dbi->types->getAllFunctions();
 871         for ($i = 0; $i < $routine['item_num_params']; $i++) {
 872             if (isset($_POST['params'][$routine['item_param_name'][$i]])) {
 873                 $value = $_POST['params'][$routine['item_param_name'][$i]];
 874                 if (is_array($value)) { // is SET type
 875                     $value = implode(',', $value);
 876                 }
 877
 878                 if (
 879                     ! empty($_POST['funcs'][$routine['item_param_name'][$i]])
 880                     && in_array($_POST['funcs'][$routine['item_param_name'][$i]], $allFunctions, true)
 881                 ) {
 882                     $queries[] = sprintf(
 883                         'SET @p%d=%s(%s);',
 884                         $i,
 885                         $_POST['funcs'][$routine['item_param_name'][$i]],
 886                         $this->dbi->quoteString($value),
 887                     );
 888                 } else {
 889                     $queries[] = 'SET @p' . $i . '=' . $this->dbi->quoteString($value) . ';';
 890                 }
 891             }
 892
 893             $args[] = '@p' . $i;
 894
 895             if ($routine['item_type'] !== 'PROCEDURE') {
 896                 continue;
 897             }
 898
 899             if ($routine['item_param_dir'][$i] !== 'OUT' && $routine['item_param_dir'][$i] !== 'INOUT') {
 900                 continue;
 901             }
 902
 903             $outParams[] = '@p' . $i . ' AS ' . Util::backquote($routine['item_param_name'][$i]);
 904         }
 905
 906         if ($routine['item_type'] === 'PROCEDURE') {
 907             $queries[] = sprintf(
 908                 'CALL %s(%s);',
 909                 Util::backquote($routine['item_name']),
 910                 implode(', ', $args),
 911             );
 912             if ($outParams !== []) {
 913                 $queries[] = 'SELECT ' . implode(', ', $outParams) . ';';
 914             }
 915         } else {
 916             $queries[] = sprintf(
 917                 'SELECT %s(%s) AS %s;',
 918                 Util::backquote($routine['item_name']),
 919                 implode(', ', $args),
 920                 Util::backquote($routine['item_name']),
 921             );
 922         }
 923
 924         return $queries;
 925     }
 926
 927     /**
 928      * @param mixed[] $routine
 929      *
 930      * @psalm-return array{string, Message}
 931      */
 932     public function handleExecuteRoutine(array $routine): array
 933     {
 934         $queries = $this->getQueriesFromRoutineForm($routine);
 935
 936         $affected = 0;
 937         $resultHtmlTables = '';
 938         $nbResultsetToDisplay = 0;
 939
 940         foreach ($queries as $query) {
 941             $result = $this->dbi->tryQuery($query);
 942
 943             // Generate output
 944             while ($result !== false) {
 945                 if ($result->numRows() > 0) {
 946                     $resultHtmlTables .= '<table class="table table-striped w-auto"><tr>';
 947                     foreach ($result->getFieldNames() as $field) {
 948                         $resultHtmlTables .= '<th>';
 949                         $resultHtmlTables .= htmlspecialchars($field);
 950                         $resultHtmlTables .= '</th>';
 951                     }
 952
 953                     $resultHtmlTables .= '</tr>';
 954
 955                     foreach ($result as $row) {
 956                         $resultHtmlTables .= '<tr>' . $this->browseRow($row) . '</tr>';
 957                     }
 958
 959                     $resultHtmlTables .= '</table>';
 960                     $nbResultsetToDisplay++;
 961                     $affected = $result->numRows();
 962                 }
 963
 964                 $result = $this->dbi->nextResult();
 965             }
 966
 967             // We must check for an error after fetching the results because
 968             // either tryQuery might have produced an error or any of nextResult calls.
 969             if ($this->dbi->getError() !== '') {
 970                 $message = Message::error(
 971                     sprintf(
 972                         __('The following query has failed: "%s"'),
 973                         htmlspecialchars($query),
 974                     )
 975                     . '<br><br>'
 976                     . __('MySQL said: ') . $this->dbi->getError(),
 977                 );
 978
 979                 return ['', $message];
 980             }
 981         }
 982
 983         // Pass the SQL queries through the "pretty printer"
 984         $output = Generator::formatSql(implode("\n", $queries));
 985         // Display results
 986         $output .= '<div class="card my-3"><div class="card-header">';
 987         $output .= sprintf(
 988             __('Execution results of routine %s'),
 989             htmlspecialchars(Util::backquote($routine['item_name'])),
 990         );
 991         $output .= '</div><div class="card-body">';
 992         $output .= $resultHtmlTables;
 993         $output .= '</div></div>';
 994
 995         $message = __('Your SQL query has been executed successfully.');
 996         if ($routine['item_type'] === 'PROCEDURE') {
 997             $message .= '<br>';
 998
 999             // TODO : message need to be modified according to the
1000             // output from the routine
1001             $message .= sprintf(
1002                 _ngettext(
1003                     '%d row affected by the last statement inside the procedure.',
1004                     '%d rows affected by the last statement inside the procedure.',
1005                     (int) $affected,
1006                 ),
1007                 $affected,
1008             );
1009         }
1010
1011         if ($nbResultsetToDisplay === 0) {
1012             $notice = __('MySQL returned an empty result set (i.e. zero rows).');
1013             $output .= Message::notice($notice)->getDisplay();
1014         }
1015
1016         return [$output, Message::success($message)];
1017     }
1018
1019     /**
1020      * Browse row array
1021      *
1022      * @param (string|null)[] $row Columns
1023      */
1024     private function browseRow(array $row): string
1025     {
1026         $output = '';
1027         foreach ($row as $value) {
1028             if ($value === null) {
1029                 $value = '<i>NULL</i>';
1030             } else {
1031                 $value = htmlspecialchars($value);
1032             }
1033
1034             $output .= '<td>' . $value . '</td>';
1035         }
1036
1037         return $output;
1038     }
1039
1040     /**
1041      * Creates the HTML code that shows the routine execution dialog.
1042      *
1043      * @param mixed[] $routine Data for the routine returned by getDataFromName()
1044      *
1045      * @psalm-return array{mixed[], mixed[]}
1046      */
1047     public function getExecuteForm(array $routine): array
1048     {
1049         // Escape special characters
1050         $routine['item_name'] = htmlentities($routine['item_name'], ENT_QUOTES);
1051         for ($i = 0; $i < $routine['item_num_params']; $i++) {
1052             $routine['item_param_name'][$i] = htmlentities($routine['item_param_name'][$i], ENT_QUOTES);
1053         }
1054
1055         $noSupportTypes = Util::unsupportedDatatypes();
1056
1057         $params = [];
1058         $params['no_support_types'] = $noSupportTypes;
1059
1060         for ($i = 0; $i < $routine['item_num_params']; $i++) {
1061             if ($routine['item_type'] === 'PROCEDURE' && $routine['item_param_dir'][$i] === 'OUT') {
1062                 continue;
1063             }
1064
1065             if (Config::getInstance()->settings['ShowFunctionFields']) {
1066                 if (
1067                     stripos($routine['item_param_type'][$i], 'enum') !== false
1068                     || stripos($routine['item_param_type'][$i], 'set') !== false
1069                     || in_array(
1070                         mb_strtolower($routine['item_param_type'][$i]),
1071                         $noSupportTypes,
1072                         true,
1073                     )
1074                 ) {
1075                     $params[$i]['generator'] = null;
1076                 } else {
1077                     $defaultFunction = Generator::getDefaultFunctionForField(
1078                         mb_strtolower($routine['item_param_type'][$i]),
1079                         false,
1080                         '',
1081                         '',
1082                         false,
1083                         '',
1084                         '',
1085                         false,
1086                     );
1087                     $params[$i]['generator'] = Generator::getFunctionsForField($defaultFunction);
1088                 }
1089             }
1090
1091             if ($routine['item_param_type'][$i] === 'DATETIME' || $routine['item_param_type'][$i] === 'TIMESTAMP') {
1092                 $params[$i]['class'] = 'datetimefield';
1093             } elseif ($routine['item_param_type'][$i] === 'DATE') {
1094                 $params[$i]['class'] = 'datefield';
1095             }
1096
1097             if (in_array($routine['item_param_type'][$i], ['ENUM', 'SET'], true)) {
1098                 if ($routine['item_param_type'][$i] === 'ENUM') {
1099                     $params[$i]['input_type'] = 'radio';
1100                 } else {
1101                     $params[$i]['input_type'] = 'checkbox';
1102                 }
1103
1104                 foreach ($routine['item_param_length_arr'][$i] as $value) {
1105                     $value = htmlentities(Util::unQuote($value), ENT_QUOTES);
1106                     $params[$i]['htmlentities'][] = $value;
1107                 }
1108             } elseif (in_array(mb_strtolower($routine['item_param_type'][$i]), $noSupportTypes, true)) {
1109                 $params[$i]['input_type'] = null;
1110             } else {
1111                 $params[$i]['input_type'] = 'text';
1112             }
1113         }
1114
1115         return [$routine, $params];
1116     }
1117
1118     /**
1119      * Creates the contents for a row in the list of routines
1120      *
1121      * @param string $rowClass Additional class
1122      *
1123      * @return mixed[]
1124      */
1125     public function getRow(Routine $routine, string $rowClass = ''): array
1126     {
1127         $sqlDrop = sprintf(
1128             'DROP %s IF EXISTS %s',
1129             $routine->type,
1130             Util::backquote($routine->name),
1131         );
1132
1133         // this is for our purpose to decide whether to
1134         // show the edit link or not, so we need the DEFINER for the routine
1135         $where = 'ROUTINE_SCHEMA ' . Util::getCollateForIS() . '=' . $this->dbi->quoteString(Current::$database)
1136             . ' AND SPECIFIC_NAME=' . $this->dbi->quoteString($routine->name)
1137             . ' AND ROUTINE_TYPE=' . $this->dbi->quoteString($routine->type);
1138         $query = 'SELECT `DEFINER` FROM INFORMATION_SCHEMA.ROUTINES WHERE ' . $where . ';';
1139         $routineDefiner = $this->dbi->fetchValue($query);
1140
1141         $currentUser = $this->dbi->getCurrentUser();
1142         $currentUserIsRoutineDefiner = $currentUser === $routineDefiner;
1143
1144         // Since editing a procedure involved dropping and recreating, check also for
1145         // CREATE ROUTINE privilege to avoid lost procedures.
1146         $hasCreateRoutine = Util::currentUserHasPrivilege('CREATE ROUTINE', Current::$database);
1147         $hasEditPrivilege = ($hasCreateRoutine && $currentUserIsRoutineDefiner)
1148                             || $this->dbi->isSuperUser();
1149         $hasExportPrivilege = ($hasCreateRoutine && $currentUserIsRoutineDefiner)
1150                             || $this->dbi->isSuperUser();
1151         $hasExecutePrivilege = Util::currentUserHasPrivilege('EXECUTE', Current::$database)
1152                             || $currentUserIsRoutineDefiner;
1153
1154         // There is a problem with Util::currentUserHasPrivilege():
1155         // it does not detect all kinds of privileges, for example
1156         // a direct privilege on a specific routine. So, at this point,
1157         // we show the Execute link, hoping that the user has the correct rights.
1158         // Also, information_schema might be hiding the ROUTINE_DEFINITION
1159         // but a routine with no input parameters can be nonetheless executed.
1160
1161         // Check if the routine has any input parameters. If it does,
1162         // we will show a dialog to get values for these parameters,
1163         // otherwise we can execute it directly.
1164
1165         if ($routine->type === 'FUNCTION') {
1166             $definition = self::getFunctionDefinition($this->dbi, Current::$database, $routine->name);
1167         } else {
1168             $definition = self::getProcedureDefinition($this->dbi, Current::$database, $routine->name);
1169         }
1170
1171         $executeAction = '';
1172
1173         if ($definition !== null) {
1174             $parser = new Parser('DELIMITER $$' . "\n" . $definition);
1175
1176             /** @var CreateStatement $stmt */
1177             $stmt = $parser->statements[0];
1178
1179             $params = RoutineUtils::getParameters($stmt);
1180
1181             if ($hasExecutePrivilege) {
1182                 $executeAction = 'execute_routine';
1183                 for ($i = 0; $i < $params['num']; $i++) {
1184                     if ($routine->type === 'PROCEDURE' && $params['dir'][$i] === 'OUT') {
1185                         continue;
1186                     }
1187
1188                     $executeAction = 'execute_dialog';
1189                     break;
1190                 }
1191             }
1192         }
1193
1194         return [
1195             'db' => Current::$database,
1196             'table' => Current::$table,
1197             'sql_drop' => $sqlDrop,
1198             'routine' => $routine,
1199             'row_class' => $rowClass,
1200             'has_edit_privilege' => $hasEditPrivilege,
1201             'has_export_privilege' => $hasExportPrivilege,
1202             'has_execute_privilege' => $hasExecutePrivilege,
1203             'execute_action' => $executeAction,
1204         ];
1205     }
1206
1207     /**
1208      * returns details about the PROCEDUREs or FUNCTIONs for a specific database
1209      * or details about a specific routine
1210      *
1211      * @param string      $db    db name
1212      * @param string|null $which PROCEDURE | FUNCTION or null for both
1213      * @param string      $name  name of the routine (to fetch a specific routine)
1214      *
1215      * @return Routine[]
1216      */
1217     public static function getDetails(
1218         DatabaseInterface $dbi,
1219         string $db,
1220         string|null $which = null,
1221         string $name = '',
1222     ): array {
1223         if (! Config::getInstance()->selectedServer['DisableIS']) {
1224             $query = QueryGenerator::getInformationSchemaRoutinesRequest(
1225                 $dbi->quoteString($db),
1226                 in_array($which, ['FUNCTION', 'PROCEDURE'], true) ? $which : null,
1227                 $name === '' ? null : $dbi->quoteString($name),
1228             );
1229             $routines = $dbi->fetchResult($query);
1230         } else {
1231             $routines = [];
1232
1233             if ($which === 'FUNCTION' || $which == null) {
1234                 $query = 'SHOW FUNCTION STATUS WHERE `Db` = ' . $dbi->quoteString($db);
1235                 if ($name !== '') {
1236                     $query .= ' AND `Name` = ' . $dbi->quoteString($name);
1237                 }
1238
1239                 $routines = $dbi->fetchResult($query);
1240             }
1241
1242             if ($which === 'PROCEDURE' || $which == null) {
1243                 $query = 'SHOW PROCEDURE STATUS WHERE `Db` = ' . $dbi->quoteString($db);
1244                 if ($name !== '') {
1245                     $query .= ' AND `Name` = ' . $dbi->quoteString($name);
1246                 }
1247
1248                 $routines = array_merge($routines, $dbi->fetchResult($query));
1249             }
1250         }
1251
1252         $ret = [];
1253         /** @var array{Name:string, Type:string, DTD_IDENTIFIER:string|null} $routine */
1254         foreach ($routines as $routine) {
1255             $ret[] = new Routine($routine['Name'], $routine['Type'], $routine['DTD_IDENTIFIER'] ?? '');
1256         }
1257
1258         // Sort results by name
1259         $name = array_column($ret, 'name');
1260         array_multisort($name, SORT_ASC, $ret);
1261
1262         return $ret;
1263     }
1264
1265     public static function getFunctionDefinition(DatabaseInterface $dbi, string $db, string $name): string|null
1266     {
1267         $result = $dbi->fetchValue(
1268             'SHOW CREATE FUNCTION ' . Util::backquote($db) . '.' . Util::backquote($name),
1269             'Create Function',
1270         );
1271
1272         return is_string($result) ? $result : null;
1273     }
1274
1275     public static function getProcedureDefinition(DatabaseInterface $dbi, string $db, string $name): string|null
1276     {
1277         $result = $dbi->fetchValue(
1278             'SHOW CREATE PROCEDURE ' . Util::backquote($db) . '.' . Util::backquote($name),
1279             'Create Procedure',
1280         );
1281
1282         return is_string($result) ? $result : null;
1283     }
1284
1285     /**
1286      * @return array<int, string>
1287      * @psalm-return list<non-empty-string>
1288      */
1289     public static function getFunctionNames(DatabaseInterface $dbi, string $db): array
1290     {
1291         /** @psalm-var list<array{Db: string, Name: string, Type: string}> $functions */
1292         $functions = $dbi->fetchResult('SHOW FUNCTION STATUS;');
1293         $names = [];
1294         foreach ($functions as $function) {
1295             if ($function['Db'] !== $db || $function['Type'] !== 'FUNCTION' || $function['Name'] === '') {
1296                 continue;
1297             }
1298
1299             $names[] = $function['Name'];
1300         }
1301
1302         return $names;
1303     }
1304
1305     /**
1306      * @return array<int, string>
1307      * @psalm-return list<non-empty-string>
1308      */
1309     public static function getProcedureNames(DatabaseInterface $dbi, string $db): array
1310     {
1311         /** @psalm-var list<array{Db: string, Name: string, Type: string}> $procedures */
1312         $procedures = $dbi->fetchResult('SHOW PROCEDURE STATUS;');
1313         $names = [];
1314         foreach ($procedures as $procedure) {
1315             if ($procedure['Db'] !== $db || $procedure['Type'] !== 'PROCEDURE' || $procedure['Name'] === '') {
1316                 continue;
1317             }
1318
1319             $names[] = $procedure['Name'];
1320         }
1321
1322         return $names;
1323     }
1324 }