"Fossies" - the Fresh Open Source Software Archive

Member "phpMyAdmin-5.1.0-all-languages/libraries/classes/Database/Triggers.php" (24 Feb 2021, 23487 Bytes) of package /linux/www/phpMyAdmin-5.1.0-all-languages.zip:


The requested HTML page contains a <FORM> tag that is unusable on "Fossies" in "automatic" (rendered) mode so that page is shown as HTML source code syntax highlighting (style: standard) with prefixed line numbers and code folding option. Alternatively you can here view or download the uninterpreted source code file.

    1 <?php
    2 
    3 declare(strict_types=1);
    4 
    5 namespace PhpMyAdmin\Database;
    6 
    7 use PhpMyAdmin\DatabaseInterface;
    8 use PhpMyAdmin\Html\Generator;
    9 use PhpMyAdmin\Message;
   10 use PhpMyAdmin\Response;
   11 use PhpMyAdmin\Template;
   12 use PhpMyAdmin\Url;
   13 use PhpMyAdmin\Util;
   14 use const ENT_QUOTES;
   15 use function count;
   16 use function explode;
   17 use function htmlentities;
   18 use function htmlspecialchars;
   19 use function in_array;
   20 use function mb_strpos;
   21 use function mb_strtoupper;
   22 use function sprintf;
   23 use function trim;
   24 
   25 /**
   26  * Functions for trigger management.
   27  */
   28 class Triggers
   29 {
   30     /** @var array<int, string> */
   31     private $time = ['BEFORE', 'AFTER'];
   32 
   33     /** @var array<int, string> */
   34     private $event = ['INSERT', 'UPDATE', 'DELETE'];
   35 
   36     /** @var DatabaseInterface */
   37     private $dbi;
   38 
   39     /** @var Template */
   40     private $template;
   41 
   42     /** @var Response */
   43     private $response;
   44 
   45     /**
   46      * @param DatabaseInterface $dbi      DatabaseInterface instance.
   47      * @param Template          $template Template instance.
   48      * @param Response          $response Response instance.
   49      */
   50     public function __construct(DatabaseInterface $dbi, Template $template, $response)
   51     {
   52         $this->dbi = $dbi;
   53         $this->template = $template;
   54         $this->response = $response;
   55     }
   56 
   57     /**
   58      * Main function for the triggers functionality
   59      *
   60      * @return void
   61      */
   62     public function main()
   63     {
   64         global $db, $table, $text_dir, $PMA_Theme;
   65 
   66         /**
   67          * Process all requests
   68          */
   69         $this->handleEditor();
   70         $this->export();
   71 
   72         $items = $this->dbi->getTriggers($db, $table);
   73         $hasDropPrivilege = Util::currentUserHasPrivilege('TRIGGER', $db);
   74         $hasEditPrivilege = Util::currentUserHasPrivilege('TRIGGER', $db, $table);
   75         $isAjax = $this->response->isAjax() && empty($_REQUEST['ajax_page_request']);
   76 
   77         $rows = '';
   78         foreach ($items as $item) {
   79             $rows .= $this->template->render('database/triggers/row', [
   80                 'db' => $db,
   81                 'table' => $table,
   82                 'trigger' => $item,
   83                 'has_drop_privilege' => $hasDropPrivilege,
   84                 'has_edit_privilege' => $hasEditPrivilege,
   85                 'row_class' => $isAjax ? 'ajaxInsert hide' : '',
   86             ]);
   87         }
   88 
   89         echo $this->template->render('database/triggers/list', [
   90             'db' => $db,
   91             'table' => $table,
   92             'items' => $items,
   93             'rows' => $rows,
   94             'select_all_arrow_src' => $PMA_Theme->getImgPath() . 'arrow_' . $text_dir . '.png',
   95         ]);
   96 
   97         echo $this->template->render('database/triggers/footer', [
   98             'db' => $db,
   99             'table' => $table,
  100             'has_privilege' => Util::currentUserHasPrivilege('TRIGGER', $db, $table),
  101         ]);
  102     }
  103 
  104     /**
  105      * Handles editor requests for adding or editing an item
  106      *
  107      * @return void
  108      */
  109     public function handleEditor()
  110     {
  111         global $db, $errors, $message, $table;
  112 
  113         if (! empty($_POST['editor_process_add'])
  114             || ! empty($_POST['editor_process_edit'])
  115         ) {
  116             $sql_query = '';
  117 
  118             $item_query = $this->getQueryFromRequest();
  119 
  120             // set by getQueryFromRequest()
  121             if (! count($errors)) {
  122                 // Execute the created query
  123                 if (! empty($_POST['editor_process_edit'])) {
  124                     // Backup the old trigger, in case something goes wrong
  125                     $trigger = $this->getDataFromName($_POST['item_original_name']);
  126                     $create_item = $trigger['create'];
  127                     $drop_item = $trigger['drop'] . ';';
  128                     $result = $this->dbi->tryQuery($drop_item);
  129                     if (! $result) {
  130                         $errors[] = sprintf(
  131                             __('The following query has failed: "%s"'),
  132                             htmlspecialchars($drop_item)
  133                         )
  134                         . '<br>'
  135                         . __('MySQL said: ') . $this->dbi->getError();
  136                     } else {
  137                         $result = $this->dbi->tryQuery($item_query);
  138                         if (! $result) {
  139                             $errors[] = sprintf(
  140                                 __('The following query has failed: "%s"'),
  141                                 htmlspecialchars($item_query)
  142                             )
  143                             . '<br>'
  144                             . __('MySQL said: ') . $this->dbi->getError();
  145                             // We dropped the old item, but were unable to create the
  146                             // new one. Try to restore the backup query.
  147                             $result = $this->dbi->tryQuery($create_item);
  148 
  149                             $errors = $this->checkResult($result, $create_item, $errors);
  150                         } else {
  151                             $message = Message::success(
  152                                 __('Trigger %1$s has been modified.')
  153                             );
  154                             $message->addParam(
  155                                 Util::backquote($_POST['item_name'])
  156                             );
  157                             $sql_query = $drop_item . $item_query;
  158                         }
  159                     }
  160                 } else {
  161                     // 'Add a new item' mode
  162                     $result = $this->dbi->tryQuery($item_query);
  163                     if (! $result) {
  164                         $errors[] = sprintf(
  165                             __('The following query has failed: "%s"'),
  166                             htmlspecialchars($item_query)
  167                         )
  168                         . '<br><br>'
  169                         . __('MySQL said: ') . $this->dbi->getError();
  170                     } else {
  171                         $message = Message::success(
  172                             __('Trigger %1$s has been created.')
  173                         );
  174                         $message->addParam(
  175                             Util::backquote($_POST['item_name'])
  176                         );
  177                         $sql_query = $item_query;
  178                     }
  179                 }
  180             }
  181 
  182             if (count($errors)) {
  183                 $message = Message::error(
  184                     '<b>'
  185                     . __(
  186                         'One or more errors have occurred while processing your request:'
  187                     )
  188                     . '</b>'
  189                 );
  190                 $message->addHtml('<ul>');
  191                 foreach ($errors as $string) {
  192                     $message->addHtml('<li>' . $string . '</li>');
  193                 }
  194                 $message->addHtml('</ul>');
  195             }
  196 
  197             $output = Generator::getMessage($message, $sql_query);
  198 
  199             if ($this->response->isAjax()) {
  200                 if ($message->isSuccess()) {
  201                     $items = $this->dbi->getTriggers($db, $table, '');
  202                     $trigger = false;
  203                     foreach ($items as $value) {
  204                         if ($value['name'] != $_POST['item_name']) {
  205                             continue;
  206                         }
  207 
  208                         $trigger = $value;
  209                     }
  210                     $insert = false;
  211                     if (empty($table)
  212                         || ($trigger !== false && $table == $trigger['table'])
  213                     ) {
  214                         $insert = true;
  215                         $this->response->addJSON(
  216                             'new_row',
  217                             $this->template->render('database/triggers/row', [
  218                                 'db' => $db,
  219                                 'table' => $table,
  220                                 'trigger' => $trigger,
  221                                 'has_drop_privilege' => Util::currentUserHasPrivilege('TRIGGER', $db),
  222                                 'has_edit_privilege' => Util::currentUserHasPrivilege('TRIGGER', $db, $table),
  223                                 'row_class' => '',
  224                             ])
  225                         );
  226                         $this->response->addJSON(
  227                             'name',
  228                             htmlspecialchars(
  229                                 mb_strtoupper(
  230                                     $_POST['item_name']
  231                                 )
  232                             )
  233                         );
  234                     }
  235                     $this->response->addJSON('insert', $insert);
  236                     $this->response->addJSON('message', $output);
  237                 } else {
  238                     $this->response->addJSON('message', $message);
  239                     $this->response->setRequestStatus(false);
  240                 }
  241                 exit;
  242             }
  243         }
  244 
  245         /**
  246          * Display a form used to add/edit a trigger, if necessary
  247          */
  248         if (! count($errors)
  249             && (! empty($_POST['editor_process_add'])
  250             || ! empty($_POST['editor_process_edit'])
  251             || (empty($_REQUEST['add_item'])
  252             && empty($_REQUEST['edit_item']))) // FIXME: this must be simpler than that
  253         ) {
  254             return;
  255         }
  256 
  257         $mode = '';
  258         $item = null;
  259         $title = '';
  260         // Get the data for the form (if any)
  261         if (! empty($_REQUEST['add_item'])) {
  262             $title = __('Add trigger');
  263             $item = $this->getDataFromRequest();
  264             $mode = 'add';
  265         } elseif (! empty($_REQUEST['edit_item'])) {
  266             $title = __('Edit trigger');
  267             if (! empty($_REQUEST['item_name'])
  268                 && empty($_POST['editor_process_edit'])
  269             ) {
  270                 $item = $this->getDataFromName($_REQUEST['item_name']);
  271                 if ($item !== null) {
  272                     $item['item_original_name'] = $item['item_name'];
  273                 }
  274             } else {
  275                 $item = $this->getDataFromRequest();
  276             }
  277             $mode = 'edit';
  278         }
  279         $this->sendEditor($mode, $item, $title, $db);
  280     }
  281 
  282     /**
  283      * This function will generate the values that are required to for the editor
  284      *
  285      * @return array    Data necessary to create the editor.
  286      */
  287     public function getDataFromRequest()
  288     {
  289         $retval = [];
  290         $indices = [
  291             'item_name',
  292             'item_table',
  293             'item_original_name',
  294             'item_action_timing',
  295             'item_event_manipulation',
  296             'item_definition',
  297             'item_definer',
  298         ];
  299         foreach ($indices as $index) {
  300             $retval[$index] = $_POST[$index] ?? '';
  301         }
  302 
  303         return $retval;
  304     }
  305 
  306     /**
  307      * This function will generate the values that are required to complete
  308      * the "Edit trigger" form given the name of a trigger.
  309      *
  310      * @param string $name The name of the trigger.
  311      *
  312      * @return array|null Data necessary to create the editor.
  313      */
  314     public function getDataFromName($name): ?array
  315     {
  316         global $db, $table;
  317 
  318         $temp = [];
  319         $items = $this->dbi->getTriggers($db, $table, '');
  320         foreach ($items as $value) {
  321             if ($value['name'] != $name) {
  322                 continue;
  323             }
  324 
  325             $temp = $value;
  326         }
  327         if (empty($temp)) {
  328             return null;
  329         }
  330 
  331         $retval = [];
  332         $retval['create']                  = $temp['create'];
  333         $retval['drop']                    = $temp['drop'];
  334         $retval['item_name']               = $temp['name'];
  335         $retval['item_table']              = $temp['table'];
  336         $retval['item_action_timing']      = $temp['action_timing'];
  337         $retval['item_event_manipulation'] = $temp['event_manipulation'];
  338         $retval['item_definition']         = $temp['definition'];
  339         $retval['item_definer']            = $temp['definer'];
  340 
  341         return $retval;
  342     }
  343 
  344     /**
  345      * Displays a form used to add/edit a trigger
  346      *
  347      * @param string $mode If the editor will be used to edit a trigger
  348      *                     or add a new one: 'edit' or 'add'.
  349      * @param array  $item Data for the trigger returned by getDataFromRequest()
  350      *                     or getDataFromName()
  351      *
  352      * @return string HTML code for the editor.
  353      */
  354     public function getEditorForm($mode, array $item)
  355     {
  356         global $db, $table;
  357 
  358         $modeToUpper = mb_strtoupper($mode);
  359 
  360         // Escape special characters
  361         $need_escape = [
  362             'item_original_name',
  363             'item_name',
  364             'item_definition',
  365             'item_definer',
  366         ];
  367         foreach ($need_escape as $key => $index) {
  368             $item[$index] = htmlentities($item[$index], ENT_QUOTES, 'UTF-8');
  369         }
  370         $original_data = '';
  371         if ($mode === 'edit') {
  372             $original_data = "<input name='item_original_name' "
  373                            . "type='hidden' value='" . $item['item_original_name'] . "'>\n";
  374         }
  375         $query  = 'SELECT `TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` ';
  376         $query .= "WHERE `TABLE_SCHEMA`='" . $this->dbi->escapeString($db) . "' ";
  377         $query .= "AND `TABLE_TYPE` IN ('BASE TABLE', 'SYSTEM VERSIONED')";
  378         $tables = $this->dbi->fetchResult($query);
  379 
  380         // Create the output
  381         $retval  = '';
  382         $retval .= '<!-- START ' . $modeToUpper . " TRIGGER FORM -->\n\n";
  383         $retval .= '<form class="rte_form" action="' . Url::getFromRoute('/database/triggers')
  384             . '" method="post">' . "\n";
  385         $retval .= "<input name='" . $mode . "_item' type='hidden' value='1'>\n";
  386         $retval .= $original_data;
  387         $retval .= Url::getHiddenInputs($db, $table) . "\n";
  388         $retval .= "<fieldset>\n";
  389         $retval .= '<legend>' . __('Details') . "</legend>\n";
  390         $retval .= "<table class='rte_table table table-borderless table-sm'>\n";
  391         $retval .= "<tr>\n";
  392         $retval .= '    <td>' . __('Trigger name') . "</td>\n";
  393         $retval .= "    <td><input type='text' name='item_name' maxlength='64'\n";
  394         $retval .= "               value='" . $item['item_name'] . "'></td>\n";
  395         $retval .= "</tr>\n";
  396         $retval .= "<tr>\n";
  397         $retval .= '    <td>' . __('Table') . "</td>\n";
  398         $retval .= "    <td>\n";
  399         $retval .= "        <select name='item_table'>\n";
  400         foreach ($tables as $key => $value) {
  401             $selected = '';
  402             if ($mode === 'add' && $value == $table) {
  403                 $selected = " selected='selected'";
  404             } elseif ($mode === 'edit' && $value == $item['item_table']) {
  405                 $selected = " selected='selected'";
  406             }
  407             $retval .= '<option' . $selected . '>';
  408             $retval .= htmlspecialchars($value);
  409             $retval .= "</option>\n";
  410         }
  411         $retval .= "        </select>\n";
  412         $retval .= "    </td>\n";
  413         $retval .= "</tr>\n";
  414         $retval .= "<tr>\n";
  415         $retval .= '    <td>' . _pgettext('Trigger action time', 'Time') . "</td>\n";
  416         $retval .= "    <td><select name='item_timing'>\n";
  417         foreach ($this->time as $key => $value) {
  418             $selected = '';
  419             if (! empty($item['item_action_timing'])
  420                 && $item['item_action_timing'] == $value
  421             ) {
  422                 $selected = " selected='selected'";
  423             }
  424             $retval .= '<option' . $selected . '>' . $value . '</option>';
  425         }
  426         $retval .= "    </select></td>\n";
  427         $retval .= "</tr>\n";
  428         $retval .= "<tr>\n";
  429         $retval .= '    <td>' . __('Event') . "</td>\n";
  430         $retval .= "    <td><select name='item_event'>\n";
  431         foreach ($this->event as $key => $value) {
  432             $selected = '';
  433             if (! empty($item['item_event_manipulation'])
  434                 && $item['item_event_manipulation'] == $value
  435             ) {
  436                 $selected = " selected='selected'";
  437             }
  438             $retval .= '<option' . $selected . '>' . $value . '</option>';
  439         }
  440         $retval .= "    </select></td>\n";
  441         $retval .= "</tr>\n";
  442         $retval .= "<tr>\n";
  443         $retval .= '    <td>' . __('Definition') . "</td>\n";
  444         $retval .= "    <td><textarea name='item_definition' rows='15' cols='40'>";
  445         $retval .= $item['item_definition'];
  446         $retval .= "</textarea></td>\n";
  447         $retval .= "</tr>\n";
  448         $retval .= "<tr>\n";
  449         $retval .= '    <td>' . __('Definer') . "</td>\n";
  450         $retval .= "    <td><input type='text' name='item_definer'\n";
  451         $retval .= "               value='" . $item['item_definer'] . "'></td>\n";
  452         $retval .= "</tr>\n";
  453         $retval .= "</table>\n";
  454         $retval .= "</fieldset>\n";
  455         if ($this->response->isAjax()) {
  456             $retval .= "<input type='hidden' name='editor_process_" . $mode . "'\n";
  457             $retval .= "       value='true'>\n";
  458             $retval .= "<input type='hidden' name='ajax_request' value='true'>\n";
  459         } else {
  460             $retval .= "<fieldset class='tblFooters'>\n";
  461             $retval .= "    <input type='submit' name='editor_process_" . $mode . "'\n";
  462             $retval .= "           value='" . __('Go') . "'>\n";
  463             $retval .= "</fieldset>\n";
  464         }
  465         $retval .= "</form>\n\n";
  466         $retval .= '<!-- END ' . $modeToUpper . " TRIGGER FORM -->\n\n";
  467 
  468         return $retval;
  469     }
  470 
  471     /**
  472      * Composes the query necessary to create a trigger from an HTTP request.
  473      *
  474      * @return string  The CREATE TRIGGER query.
  475      */
  476     public function getQueryFromRequest()
  477     {
  478         global $db, $errors;
  479 
  480         $query = 'CREATE ';
  481         if (! empty($_POST['item_definer'])) {
  482             if (mb_strpos($_POST['item_definer'], '@') !== false
  483             ) {
  484                 $arr = explode('@', $_POST['item_definer']);
  485                 $query .= 'DEFINER=' . Util::backquote($arr[0]);
  486                 $query .= '@' . Util::backquote($arr[1]) . ' ';
  487             } else {
  488                 $errors[] = __('The definer must be in the "username@hostname" format!');
  489             }
  490         }
  491         $query .= 'TRIGGER ';
  492         if (! empty($_POST['item_name'])) {
  493             $query .= Util::backquote($_POST['item_name']) . ' ';
  494         } else {
  495             $errors[] = __('You must provide a trigger name!');
  496         }
  497         if (! empty($_POST['item_timing'])
  498             && in_array($_POST['item_timing'], $this->time)
  499         ) {
  500             $query .= $_POST['item_timing'] . ' ';
  501         } else {
  502             $errors[] = __('You must provide a valid timing for the trigger!');
  503         }
  504         if (! empty($_POST['item_event'])
  505             && in_array($_POST['item_event'], $this->event)
  506         ) {
  507             $query .= $_POST['item_event'] . ' ';
  508         } else {
  509             $errors[] = __('You must provide a valid event for the trigger!');
  510         }
  511         $query .= 'ON ';
  512         if (! empty($_POST['item_table'])
  513             && in_array($_POST['item_table'], $this->dbi->getTables($db))
  514         ) {
  515             $query .= Util::backquote($_POST['item_table']);
  516         } else {
  517             $errors[] = __('You must provide a valid table name!');
  518         }
  519         $query .= ' FOR EACH ROW ';
  520         if (! empty($_POST['item_definition'])) {
  521             $query .= $_POST['item_definition'];
  522         } else {
  523             $errors[] = __('You must provide a trigger definition.');
  524         }
  525 
  526         return $query;
  527     }
  528 
  529     /**
  530      * @param resource|bool $result          Query result
  531      * @param string        $createStatement Query
  532      * @param array         $errors          Errors
  533      *
  534      * @return array
  535      */
  536     private function checkResult($result, $createStatement, array $errors)
  537     {
  538         if ($result) {
  539             return $errors;
  540         }
  541 
  542         // OMG, this is really bad! We dropped the query,
  543         // failed to create a new one
  544         // and now even the backup query does not execute!
  545         // This should not happen, but we better handle
  546         // this just in case.
  547         $errors[] = __('Sorry, we failed to restore the dropped trigger.') . '<br>'
  548             . __('The backed up query was:')
  549             . '"' . htmlspecialchars($createStatement) . '"<br>'
  550             . __('MySQL said: ') . $this->dbi->getError();
  551 
  552         return $errors;
  553     }
  554 
  555     /**
  556      * Send editor via ajax or by echoing.
  557      *
  558      * @param string     $mode  Editor mode 'add' or 'edit'
  559      * @param array|null $item  Data necessary to create the editor
  560      * @param string     $title Title of the editor
  561      * @param string     $db    Database
  562      *
  563      * @return void
  564      */
  565     private function sendEditor($mode, ?array $item, $title, $db)
  566     {
  567         if ($item !== null) {
  568             $editor = $this->getEditorForm($mode, $item);
  569             if ($this->response->isAjax()) {
  570                 $this->response->addJSON('message', $editor);
  571                 $this->response->addJSON('title', $title);
  572             } else {
  573                 echo "\n\n<h2>" . $title . "</h2>\n\n" . $editor;
  574                 unset($_POST);
  575             }
  576             exit;
  577         }
  578 
  579         $message  = __('Error in processing request:') . ' ';
  580         $message .= sprintf(
  581             __('No trigger with name %1$s found in database %2$s.'),
  582             htmlspecialchars(Util::backquote($_REQUEST['item_name'])),
  583             htmlspecialchars(Util::backquote($db))
  584         );
  585         $message = Message::error($message);
  586         if ($this->response->isAjax()) {
  587             $this->response->setRequestStatus(false);
  588             $this->response->addJSON('message', $message);
  589             exit;
  590         }
  591 
  592         echo $message->getDisplay();
  593     }
  594 
  595     private function export(): void
  596     {
  597         global $db, $table;
  598 
  599         if (empty($_GET['export_item']) || empty($_GET['item_name'])) {
  600             return;
  601         }
  602 
  603         $itemName = $_GET['item_name'];
  604         $triggers = $this->dbi->getTriggers($db, $table, '');
  605         $exportData = false;
  606 
  607         foreach ($triggers as $trigger) {
  608             if ($trigger['name'] === $itemName) {
  609                 $exportData = $trigger['create'];
  610                 break;
  611             }
  612         }
  613 
  614         $itemName = htmlspecialchars(Util::backquote($_GET['item_name']));
  615         if ($exportData !== false) {
  616             $exportData = htmlspecialchars(trim($exportData));
  617             $title = sprintf(__('Export of trigger %s'), $itemName);
  618 
  619             if ($this->response->isAjax()) {
  620                 $this->response->addJSON('message', $exportData);
  621                 $this->response->addJSON('title', $title);
  622 
  623                 exit;
  624             }
  625 
  626             $exportData = '<textarea cols="40" rows="15" style="width: 100%;">'
  627                 . $exportData . '</textarea>';
  628             echo "<fieldset>\n" . '<legend>' . $title . "</legend>\n"
  629                 . $exportData . "</fieldset>\n";
  630 
  631             return;
  632         }
  633 
  634         $message = sprintf(
  635             __('Error in processing request: No trigger with name %1$s found in database %2$s.'),
  636             $itemName,
  637             htmlspecialchars(Util::backquote($db))
  638         );
  639         $message = Message::error($message);
  640 
  641         if ($this->response->isAjax()) {
  642             $this->response->setRequestStatus(false);
  643             $this->response->addJSON('message', $message);
  644 
  645             exit;
  646         }
  647 
  648         echo $message->getDisplay();
  649     }
  650 }