1 | <?php |
||
2 | /* vim: set expandtab sw=4 ts=4 sts=4: */ |
||
3 | /** |
||
4 | * Functions for trigger management. |
||
5 | * |
||
6 | * @package PhpMyAdmin |
||
7 | */ |
||
8 | declare(strict_types=1); |
||
9 | |||
10 | namespace PhpMyAdmin\Rte; |
||
11 | |||
12 | use PhpMyAdmin\DatabaseInterface; |
||
13 | use PhpMyAdmin\Message; |
||
14 | use PhpMyAdmin\Response; |
||
15 | use PhpMyAdmin\Url; |
||
16 | use PhpMyAdmin\Util; |
||
17 | |||
18 | /** |
||
19 | * PhpMyAdmin\Rte\Triggers class |
||
20 | * |
||
21 | * @package PhpMyAdmin |
||
22 | */ |
||
23 | class Triggers |
||
24 | { |
||
25 | /** |
||
26 | * @var Export |
||
27 | */ |
||
28 | private $export; |
||
29 | |||
30 | /** |
||
31 | * @var Footer |
||
32 | */ |
||
33 | private $footer; |
||
34 | |||
35 | /** |
||
36 | * @var General |
||
37 | */ |
||
38 | private $general; |
||
39 | |||
40 | /** |
||
41 | * @var RteList |
||
42 | */ |
||
43 | private $rteList; |
||
44 | |||
45 | /** |
||
46 | * @var Words |
||
47 | */ |
||
48 | private $words; |
||
49 | |||
50 | /** |
||
51 | * @var DatabaseInterface |
||
52 | */ |
||
53 | private $dbi; |
||
54 | |||
55 | /** |
||
56 | * Triggers constructor. |
||
57 | * |
||
58 | * @param DatabaseInterface $dbi DatabaseInterface object |
||
59 | */ |
||
60 | public function __construct(DatabaseInterface $dbi) |
||
61 | { |
||
62 | $this->dbi = $dbi; |
||
63 | $this->export = new Export($this->dbi); |
||
64 | $this->footer = new Footer($this->dbi); |
||
65 | $this->general = new General($this->dbi); |
||
66 | $this->rteList = new RteList($this->dbi); |
||
67 | $this->words = new Words(); |
||
68 | } |
||
69 | |||
70 | /** |
||
71 | * Sets required globals |
||
72 | * |
||
73 | * @return void |
||
74 | */ |
||
75 | public function setGlobals() |
||
76 | { |
||
77 | global $action_timings, $event_manipulations; |
||
78 | |||
79 | // Some definitions for triggers |
||
80 | $action_timings = [ |
||
81 | 'BEFORE', |
||
82 | 'AFTER', |
||
83 | ]; |
||
84 | $event_manipulations = [ |
||
85 | 'INSERT', |
||
86 | 'UPDATE', |
||
87 | 'DELETE', |
||
88 | ]; |
||
89 | } |
||
90 | |||
91 | /** |
||
92 | * Main function for the triggers functionality |
||
93 | * |
||
94 | * @return void |
||
95 | */ |
||
96 | public function main() |
||
97 | { |
||
98 | global $db, $table; |
||
99 | |||
100 | $this->setGlobals(); |
||
101 | /** |
||
102 | * Process all requests |
||
103 | */ |
||
104 | $this->handleEditor(); |
||
105 | $this->export->triggers(); |
||
106 | /** |
||
107 | * Display a list of available triggers |
||
108 | */ |
||
109 | $items = $this->dbi->getTriggers($db, $table); |
||
110 | echo $this->rteList->get('trigger', $items); |
||
111 | /** |
||
112 | * Display a link for adding a new trigger, |
||
113 | * if the user has the necessary privileges |
||
114 | */ |
||
115 | echo $this->footer->triggers(); |
||
116 | } |
||
117 | |||
118 | /** |
||
119 | * Handles editor requests for adding or editing an item |
||
120 | * |
||
121 | * @return void |
||
122 | */ |
||
123 | public function handleEditor() |
||
124 | { |
||
125 | global $db, $errors, $message, $table; |
||
126 | |||
127 | if (! empty($_POST['editor_process_add']) |
||
128 | || ! empty($_POST['editor_process_edit']) |
||
129 | ) { |
||
130 | $sql_query = ''; |
||
131 | |||
132 | $item_query = $this->getQueryFromRequest(); |
||
133 | |||
134 | if (! count($errors)) { // set by PhpMyAdmin\Rte\Routines::getQueryFromRequest() |
||
135 | // Execute the created query |
||
136 | if (! empty($_POST['editor_process_edit'])) { |
||
137 | // Backup the old trigger, in case something goes wrong |
||
138 | $trigger = $this->getDataFromName($_POST['item_original_name']); |
||
139 | $create_item = $trigger['create']; |
||
140 | $drop_item = $trigger['drop'] . ';'; |
||
141 | $result = $this->dbi->tryQuery($drop_item); |
||
142 | if (! $result) { |
||
143 | $errors[] = sprintf( |
||
144 | __('The following query has failed: "%s"'), |
||
145 | htmlspecialchars($drop_item) |
||
146 | ) |
||
147 | . '<br>' |
||
148 | . __('MySQL said: ') . $this->dbi->getError(); |
||
149 | } else { |
||
150 | $result = $this->dbi->tryQuery($item_query); |
||
151 | if (! $result) { |
||
152 | $errors[] = sprintf( |
||
153 | __('The following query has failed: "%s"'), |
||
154 | htmlspecialchars($item_query) |
||
155 | ) |
||
156 | . '<br>' |
||
157 | . __('MySQL said: ') . $this->dbi->getError(); |
||
158 | // We dropped the old item, but were unable to create the |
||
159 | // new one. Try to restore the backup query. |
||
160 | $result = $this->dbi->tryQuery($create_item); |
||
161 | |||
162 | $errors = $this->general->checkResult( |
||
163 | $result, |
||
164 | __( |
||
165 | 'Sorry, we failed to restore the dropped trigger.' |
||
166 | ), |
||
167 | $create_item, |
||
168 | $errors |
||
169 | ); |
||
170 | } else { |
||
171 | $message = Message::success( |
||
172 | __('Trigger %1$s has been modified.') |
||
173 | ); |
||
174 | $message->addParam( |
||
175 | Util::backquote($_POST['item_name']) |
||
176 | ); |
||
177 | $sql_query = $drop_item . $item_query; |
||
178 | } |
||
179 | } |
||
180 | } else { |
||
181 | // 'Add a new item' mode |
||
182 | $result = $this->dbi->tryQuery($item_query); |
||
183 | if (! $result) { |
||
184 | $errors[] = sprintf( |
||
185 | __('The following query has failed: "%s"'), |
||
186 | htmlspecialchars($item_query) |
||
187 | ) |
||
188 | . '<br><br>' |
||
189 | . __('MySQL said: ') . $this->dbi->getError(); |
||
190 | } else { |
||
191 | $message = Message::success( |
||
192 | __('Trigger %1$s has been created.') |
||
193 | ); |
||
194 | $message->addParam( |
||
195 | Util::backquote($_POST['item_name']) |
||
196 | ); |
||
197 | $sql_query = $item_query; |
||
198 | } |
||
199 | } |
||
200 | } |
||
201 | |||
202 | if (count($errors)) { |
||
203 | $message = Message::error( |
||
204 | '<b>' |
||
205 | . __( |
||
206 | 'One or more errors have occurred while processing your request:' |
||
207 | ) |
||
208 | . '</b>' |
||
209 | ); |
||
210 | $message->addHtml('<ul>'); |
||
211 | foreach ($errors as $string) { |
||
212 | $message->addHtml('<li>' . $string . '</li>'); |
||
213 | } |
||
214 | $message->addHtml('</ul>'); |
||
215 | } |
||
216 | |||
217 | $output = Util::getMessage($message, $sql_query); |
||
218 | $response = Response::getInstance(); |
||
219 | if ($response->isAjax()) { |
||
220 | if ($message->isSuccess()) { |
||
221 | $items = $this->dbi->getTriggers($db, $table, ''); |
||
222 | $trigger = false; |
||
223 | foreach ($items as $value) { |
||
224 | if ($value['name'] == $_POST['item_name']) { |
||
225 | $trigger = $value; |
||
226 | } |
||
227 | } |
||
228 | $insert = false; |
||
229 | if (empty($table) |
||
230 | || ($trigger !== false && $table == $trigger['table']) |
||
231 | ) { |
||
232 | $insert = true; |
||
233 | $response->addJSON('new_row', $this->rteList->getTriggerRow($trigger)); |
||
234 | $response->addJSON( |
||
235 | 'name', |
||
236 | htmlspecialchars( |
||
237 | mb_strtoupper( |
||
238 | $_POST['item_name'] |
||
239 | ) |
||
240 | ) |
||
241 | ); |
||
242 | } |
||
243 | $response->addJSON('insert', $insert); |
||
244 | $response->addJSON('message', $output); |
||
245 | } else { |
||
246 | $response->addJSON('message', $message); |
||
247 | $response->setRequestStatus(false); |
||
248 | } |
||
249 | exit; |
||
250 | } |
||
251 | } |
||
252 | |||
253 | /** |
||
254 | * Display a form used to add/edit a trigger, if necessary |
||
255 | */ |
||
256 | if (count($errors) |
||
257 | || (empty($_POST['editor_process_add']) |
||
258 | && empty($_POST['editor_process_edit']) |
||
259 | && (! empty($_REQUEST['add_item']) |
||
260 | || ! empty($_REQUEST['edit_item']))) // FIXME: this must be simpler than that |
||
261 | ) { |
||
262 | $mode = null; |
||
263 | $item = null; |
||
264 | $title = null; |
||
265 | // Get the data for the form (if any) |
||
266 | if (! empty($_REQUEST['add_item'])) { |
||
267 | $title = $this->words->get('add'); |
||
268 | $item = $this->getDataFromRequest(); |
||
269 | $mode = 'add'; |
||
270 | } elseif (! empty($_REQUEST['edit_item'])) { |
||
271 | $title = __("Edit trigger"); |
||
272 | if (! empty($_REQUEST['item_name']) |
||
273 | && empty($_POST['editor_process_edit']) |
||
274 | ) { |
||
275 | $item = $this->getDataFromName($_REQUEST['item_name']); |
||
276 | if ($item !== false) { |
||
277 | $item['item_original_name'] = $item['item_name']; |
||
278 | } |
||
279 | } else { |
||
280 | $item = $this->getDataFromRequest(); |
||
281 | } |
||
282 | $mode = 'edit'; |
||
283 | } |
||
284 | $this->general->sendEditor('TRI', $mode, $item, $title, $db); |
||
0 ignored issues
–
show
Bug
introduced
by
Loading history...
|
|||
285 | } |
||
286 | } |
||
287 | |||
288 | /** |
||
289 | * This function will generate the values that are required to for the editor |
||
290 | * |
||
291 | * @return array Data necessary to create the editor. |
||
292 | */ |
||
293 | public function getDataFromRequest() |
||
294 | { |
||
295 | $retval = []; |
||
296 | $indices = [ |
||
297 | 'item_name', |
||
298 | 'item_table', |
||
299 | 'item_original_name', |
||
300 | 'item_action_timing', |
||
301 | 'item_event_manipulation', |
||
302 | 'item_definition', |
||
303 | 'item_definer', |
||
304 | ]; |
||
305 | foreach ($indices as $index) { |
||
306 | $retval[$index] = isset($_POST[$index]) ? $_POST[$index] : ''; |
||
307 | } |
||
308 | return $retval; |
||
309 | } |
||
310 | |||
311 | /** |
||
312 | * This function will generate the values that are required to complete |
||
313 | * the "Edit trigger" form given the name of a trigger. |
||
314 | * |
||
315 | * @param string $name The name of the trigger. |
||
316 | * |
||
317 | * @return array|bool Data necessary to create the editor. |
||
318 | */ |
||
319 | public function getDataFromName($name) |
||
320 | { |
||
321 | global $db, $table; |
||
322 | |||
323 | $temp = []; |
||
324 | $items = $this->dbi->getTriggers($db, $table, ''); |
||
325 | foreach ($items as $value) { |
||
326 | if ($value['name'] == $name) { |
||
327 | $temp = $value; |
||
328 | } |
||
329 | } |
||
330 | if (empty($temp)) { |
||
331 | return false; |
||
332 | } else { |
||
333 | $retval = []; |
||
334 | $retval['create'] = $temp['create']; |
||
335 | $retval['drop'] = $temp['drop']; |
||
336 | $retval['item_name'] = $temp['name']; |
||
337 | $retval['item_table'] = $temp['table']; |
||
338 | $retval['item_action_timing'] = $temp['action_timing']; |
||
339 | $retval['item_event_manipulation'] = $temp['event_manipulation']; |
||
340 | $retval['item_definition'] = $temp['definition']; |
||
341 | $retval['item_definer'] = $temp['definer']; |
||
342 | return $retval; |
||
343 | } |
||
344 | } |
||
345 | |||
346 | /** |
||
347 | * Displays a form used to add/edit a trigger |
||
348 | * |
||
349 | * @param string $mode If the editor will be used to edit a trigger |
||
350 | * or add a new one: 'edit' or 'add'. |
||
351 | * @param array $item Data for the trigger returned by getDataFromRequest() |
||
352 | * or getDataFromName() |
||
353 | * |
||
354 | * @return string HTML code for the editor. |
||
355 | */ |
||
356 | public function getEditorForm($mode, array $item) |
||
357 | { |
||
358 | global $db, $table, $event_manipulations, $action_timings; |
||
359 | |||
360 | $modeToUpper = mb_strtoupper($mode); |
||
361 | $response = Response::getInstance(); |
||
362 | |||
363 | // Escape special characters |
||
364 | $need_escape = [ |
||
365 | 'item_original_name', |
||
366 | 'item_name', |
||
367 | 'item_definition', |
||
368 | 'item_definer', |
||
369 | ]; |
||
370 | foreach ($need_escape as $key => $index) { |
||
371 | $item[$index] = htmlentities($item[$index], ENT_QUOTES, 'UTF-8'); |
||
372 | } |
||
373 | $original_data = ''; |
||
374 | if ($mode == 'edit') { |
||
375 | $original_data = "<input name='item_original_name' " |
||
376 | . "type='hidden' value='{$item['item_original_name']}'>\n"; |
||
377 | } |
||
378 | $query = "SELECT `TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` "; |
||
379 | $query .= "WHERE `TABLE_SCHEMA`='" . $this->dbi->escapeString($db) . "' "; |
||
380 | $query .= "AND `TABLE_TYPE` IN ('BASE TABLE', 'SYSTEM VERSIONED')"; |
||
381 | $tables = $this->dbi->fetchResult($query); |
||
382 | |||
383 | // Create the output |
||
384 | $retval = ""; |
||
385 | $retval .= "<!-- START " . $modeToUpper . " TRIGGER FORM -->\n\n"; |
||
386 | $retval .= "<form class='rte_form' action='db_triggers.php' method='post'>\n"; |
||
387 | $retval .= "<input name='{$mode}_item' type='hidden' value='1'>\n"; |
||
388 | $retval .= $original_data; |
||
389 | $retval .= Url::getHiddenInputs($db, $table) . "\n"; |
||
390 | $retval .= "<fieldset>\n"; |
||
391 | $retval .= "<legend>" . __('Details') . "</legend>\n"; |
||
392 | $retval .= "<table class='rte_table'>\n"; |
||
393 | $retval .= "<tr>\n"; |
||
394 | $retval .= " <td>" . __('Trigger name') . "</td>\n"; |
||
395 | $retval .= " <td><input type='text' name='item_name' maxlength='64'\n"; |
||
396 | $retval .= " value='{$item['item_name']}'></td>\n"; |
||
397 | $retval .= "</tr>\n"; |
||
398 | $retval .= "<tr>\n"; |
||
399 | $retval .= " <td>" . __('Table') . "</td>\n"; |
||
400 | $retval .= " <td>\n"; |
||
401 | $retval .= " <select name='item_table'>\n"; |
||
402 | foreach ($tables as $key => $value) { |
||
403 | $selected = ""; |
||
404 | if ($mode == 'add' && $value == $table) { |
||
405 | $selected = " selected='selected'"; |
||
406 | } elseif ($mode == 'edit' && $value == $item['item_table']) { |
||
407 | $selected = " selected='selected'"; |
||
408 | } |
||
409 | $retval .= "<option$selected>"; |
||
410 | $retval .= htmlspecialchars($value); |
||
411 | $retval .= "</option>\n"; |
||
412 | } |
||
413 | $retval .= " </select>\n"; |
||
414 | $retval .= " </td>\n"; |
||
415 | $retval .= "</tr>\n"; |
||
416 | $retval .= "<tr>\n"; |
||
417 | $retval .= " <td>" . _pgettext('Trigger action time', 'Time') . "</td>\n"; |
||
418 | $retval .= " <td><select name='item_timing'>\n"; |
||
419 | foreach ($action_timings as $key => $value) { |
||
420 | $selected = ""; |
||
421 | if (! empty($item['item_action_timing']) |
||
422 | && $item['item_action_timing'] == $value |
||
423 | ) { |
||
424 | $selected = " selected='selected'"; |
||
425 | } |
||
426 | $retval .= "<option$selected>$value</option>"; |
||
427 | } |
||
428 | $retval .= " </select></td>\n"; |
||
429 | $retval .= "</tr>\n"; |
||
430 | $retval .= "<tr>\n"; |
||
431 | $retval .= " <td>" . __('Event') . "</td>\n"; |
||
432 | $retval .= " <td><select name='item_event'>\n"; |
||
433 | foreach ($event_manipulations as $key => $value) { |
||
434 | $selected = ""; |
||
435 | if (! empty($item['item_event_manipulation']) |
||
436 | && $item['item_event_manipulation'] == $value |
||
437 | ) { |
||
438 | $selected = " selected='selected'"; |
||
439 | } |
||
440 | $retval .= "<option$selected>$value</option>"; |
||
441 | } |
||
442 | $retval .= " </select></td>\n"; |
||
443 | $retval .= "</tr>\n"; |
||
444 | $retval .= "<tr>\n"; |
||
445 | $retval .= " <td>" . __('Definition') . "</td>\n"; |
||
446 | $retval .= " <td><textarea name='item_definition' rows='15' cols='40'>"; |
||
447 | $retval .= $item['item_definition']; |
||
448 | $retval .= "</textarea></td>\n"; |
||
449 | $retval .= "</tr>\n"; |
||
450 | $retval .= "<tr>\n"; |
||
451 | $retval .= " <td>" . __('Definer') . "</td>\n"; |
||
452 | $retval .= " <td><input type='text' name='item_definer'\n"; |
||
453 | $retval .= " value='{$item['item_definer']}'></td>\n"; |
||
454 | $retval .= "</tr>\n"; |
||
455 | $retval .= "</table>\n"; |
||
456 | $retval .= "</fieldset>\n"; |
||
457 | if ($response->isAjax()) { |
||
458 | $retval .= "<input type='hidden' name='editor_process_{$mode}'\n"; |
||
459 | $retval .= " value='true'>\n"; |
||
460 | $retval .= "<input type='hidden' name='ajax_request' value='true'>\n"; |
||
461 | } else { |
||
462 | $retval .= "<fieldset class='tblFooters'>\n"; |
||
463 | $retval .= " <input type='submit' name='editor_process_{$mode}'\n"; |
||
464 | $retval .= " value='" . __('Go') . "'>\n"; |
||
465 | $retval .= "</fieldset>\n"; |
||
466 | } |
||
467 | $retval .= "</form>\n\n"; |
||
468 | $retval .= "<!-- END " . $modeToUpper . " TRIGGER FORM -->\n\n"; |
||
469 | |||
470 | return $retval; |
||
471 | } |
||
472 | |||
473 | /** |
||
474 | * Composes the query necessary to create a trigger from an HTTP request. |
||
475 | * |
||
476 | * @return string The CREATE TRIGGER query. |
||
477 | */ |
||
478 | public function getQueryFromRequest() |
||
479 | { |
||
480 | global $db, $errors, $action_timings, $event_manipulations; |
||
481 | |||
482 | $query = 'CREATE '; |
||
483 | if (! empty($_POST['item_definer'])) { |
||
484 | if (mb_strpos($_POST['item_definer'], '@') !== false |
||
485 | ) { |
||
486 | $arr = explode('@', $_POST['item_definer']); |
||
487 | $query .= 'DEFINER=' . Util::backquote($arr[0]); |
||
488 | $query .= '@' . Util::backquote($arr[1]) . ' '; |
||
489 | } else { |
||
490 | $errors[] = __('The definer must be in the "username@hostname" format!'); |
||
491 | } |
||
492 | } |
||
493 | $query .= 'TRIGGER '; |
||
494 | if (! empty($_POST['item_name'])) { |
||
495 | $query .= Util::backquote($_POST['item_name']) . ' '; |
||
496 | } else { |
||
497 | $errors[] = __('You must provide a trigger name!'); |
||
498 | } |
||
499 | if (! empty($_POST['item_timing']) |
||
500 | && in_array($_POST['item_timing'], $action_timings) |
||
501 | ) { |
||
502 | $query .= $_POST['item_timing'] . ' '; |
||
503 | } else { |
||
504 | $errors[] = __('You must provide a valid timing for the trigger!'); |
||
505 | } |
||
506 | if (! empty($_POST['item_event']) |
||
507 | && in_array($_POST['item_event'], $event_manipulations) |
||
508 | ) { |
||
509 | $query .= $_POST['item_event'] . ' '; |
||
510 | } else { |
||
511 | $errors[] = __('You must provide a valid event for the trigger!'); |
||
512 | } |
||
513 | $query .= 'ON '; |
||
514 | if (! empty($_POST['item_table']) |
||
515 | && in_array($_POST['item_table'], $this->dbi->getTables($db)) |
||
516 | ) { |
||
517 | $query .= Util::backquote($_POST['item_table']); |
||
518 | } else { |
||
519 | $errors[] = __('You must provide a valid table name!'); |
||
520 | } |
||
521 | $query .= ' FOR EACH ROW '; |
||
522 | if (! empty($_POST['item_definition'])) { |
||
523 | $query .= $_POST['item_definition']; |
||
524 | } else { |
||
525 | $errors[] = __('You must provide a trigger definition.'); |
||
526 | } |
||
527 | |||
528 | return $query; |
||
529 | } |
||
530 | } |
||
531 |