"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 }