"Fossies" - the Fresh Open Source Software Archive

Member "phpMyAdmin-5.1.0-english/libraries/classes/Database/Triggers.php" (24 Feb 2021, 23487 Bytes) of package /linux/www/phpMyAdmin-5.1.0-english.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. For more information about "Triggers.php" see the Fossies "Dox" file reference documentation.

    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 }