|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
declare(strict_types=1); |
|
4
|
|
|
|
|
5
|
|
|
namespace PhpMyAdmin\Triggers; |
|
6
|
|
|
|
|
7
|
|
|
use PhpMyAdmin\Config; |
|
8
|
|
|
use PhpMyAdmin\Current; |
|
9
|
|
|
use PhpMyAdmin\DatabaseInterface; |
|
10
|
|
|
use PhpMyAdmin\Html\Generator; |
|
11
|
|
|
use PhpMyAdmin\Message; |
|
12
|
|
|
use PhpMyAdmin\Query\Generator as QueryGenerator; |
|
13
|
|
|
use PhpMyAdmin\Util; |
|
14
|
|
|
use Webmozart\Assert\Assert; |
|
15
|
|
|
|
|
16
|
|
|
use function __; |
|
17
|
|
|
use function array_column; |
|
18
|
|
|
use function array_multisort; |
|
19
|
|
|
use function explode; |
|
20
|
|
|
use function htmlspecialchars; |
|
21
|
|
|
use function in_array; |
|
22
|
|
|
use function sprintf; |
|
23
|
|
|
use function str_contains; |
|
24
|
|
|
|
|
25
|
|
|
use const SORT_ASC; |
|
26
|
|
|
|
|
27
|
|
|
/** |
|
28
|
|
|
* Functions for trigger management. |
|
29
|
|
|
*/ |
|
30
|
|
|
class Triggers |
|
31
|
|
|
{ |
|
32
|
|
|
/** @var array<int, string> */ |
|
33
|
|
|
private array $time = ['BEFORE', 'AFTER']; |
|
34
|
|
|
|
|
35
|
|
|
private const EVENTS = ['INSERT', 'UPDATE', 'DELETE']; |
|
36
|
|
|
|
|
37
|
44 |
|
public function __construct(private DatabaseInterface $dbi) |
|
38
|
|
|
{ |
|
39
|
44 |
|
} |
|
40
|
|
|
|
|
41
|
|
|
/** @return mixed[][] */ |
|
42
|
24 |
|
private static function fetchTriggerInfo(DatabaseInterface $dbi, string $db, string $table): array |
|
43
|
|
|
{ |
|
44
|
24 |
|
if (! Config::getInstance()->selectedServer['DisableIS']) { |
|
|
|
|
|
|
45
|
8 |
|
$query = QueryGenerator::getInformationSchemaTriggersRequest( |
|
46
|
8 |
|
$dbi->quoteString($db), |
|
47
|
8 |
|
$table === '' ? null : $dbi->quoteString($table), |
|
48
|
8 |
|
); |
|
49
|
|
|
} else { |
|
50
|
16 |
|
$query = 'SHOW TRIGGERS FROM ' . Util::backquote($db); |
|
51
|
16 |
|
if ($table !== '') { |
|
52
|
4 |
|
$query .= ' LIKE ' . $dbi->quoteString($table) . ';'; |
|
53
|
|
|
} |
|
54
|
|
|
} |
|
55
|
|
|
|
|
56
|
|
|
/** @var mixed[][] $triggers */ |
|
57
|
24 |
|
$triggers = $dbi->fetchResult($query); |
|
58
|
|
|
|
|
59
|
24 |
|
return $triggers; |
|
60
|
|
|
} |
|
61
|
|
|
|
|
62
|
|
|
/** |
|
63
|
|
|
* Handles editor requests for adding or editing an item |
|
64
|
|
|
*/ |
|
65
|
|
|
public function handleEditor(): string |
|
66
|
|
|
{ |
|
67
|
|
|
$sqlQuery = ''; |
|
68
|
|
|
|
|
69
|
|
|
$itemQuery = $this->getQueryFromRequest(); |
|
70
|
|
|
|
|
71
|
|
|
// set by getQueryFromRequest() |
|
72
|
|
|
if ($GLOBALS['errors'] === []) { |
|
73
|
|
|
// Execute the created query |
|
74
|
|
|
if (! empty($_POST['editor_process_edit'])) { |
|
75
|
|
|
// Backup the old trigger, in case something goes wrong |
|
76
|
|
|
$trigger = $this->getTriggerByName(Current::$database, Current::$table, $_POST['item_original_name']); |
|
77
|
|
|
$createItem = $trigger->getCreateSql(''); |
|
78
|
|
|
$dropItem = $trigger->getDropSql(); |
|
79
|
|
|
$result = $this->dbi->tryQuery($dropItem); |
|
80
|
|
|
if (! $result) { |
|
81
|
|
|
$GLOBALS['errors'][] = sprintf( |
|
82
|
|
|
__('The following query has failed: "%s"'), |
|
83
|
|
|
htmlspecialchars($dropItem), |
|
84
|
|
|
) |
|
85
|
|
|
. '<br>' |
|
86
|
|
|
. __('MySQL said: ') . $this->dbi->getError(); |
|
87
|
|
|
} else { |
|
88
|
|
|
$result = $this->dbi->tryQuery($itemQuery); |
|
89
|
|
|
if (! $result) { |
|
90
|
|
|
$GLOBALS['errors'][] = sprintf( |
|
91
|
|
|
__('The following query has failed: "%s"'), |
|
92
|
|
|
htmlspecialchars($itemQuery), |
|
93
|
|
|
) |
|
94
|
|
|
. '<br>' |
|
95
|
|
|
. __('MySQL said: ') . $this->dbi->getError(); |
|
96
|
|
|
// We dropped the old item, but were unable to create the |
|
97
|
|
|
// new one. Try to restore the backup query. |
|
98
|
|
|
$result = $this->dbi->tryQuery($createItem); |
|
99
|
|
|
|
|
100
|
|
|
if (! $result) { |
|
101
|
|
|
// OMG, this is really bad! We dropped the query, |
|
102
|
|
|
// failed to create a new one |
|
103
|
|
|
// and now even the backup query does not execute! |
|
104
|
|
|
// This should not happen, but we better handle |
|
105
|
|
|
// this just in case. |
|
106
|
|
|
$GLOBALS['errors'][] = __('Sorry, we failed to restore the dropped trigger.') . '<br>' |
|
107
|
|
|
. __('The backed up query was:') |
|
108
|
|
|
. '"' . htmlspecialchars($createItem) . '"<br>' |
|
109
|
|
|
. __('MySQL said: ') . $this->dbi->getError(); |
|
110
|
|
|
} |
|
111
|
|
|
} else { |
|
112
|
|
|
$GLOBALS['message'] = Message::success( |
|
113
|
|
|
__('Trigger %1$s has been modified.'), |
|
114
|
|
|
); |
|
115
|
|
|
$GLOBALS['message']->addParam( |
|
116
|
|
|
Util::backquote($_POST['item_name']), |
|
117
|
|
|
); |
|
118
|
|
|
$sqlQuery = $dropItem . $itemQuery; |
|
119
|
|
|
} |
|
120
|
|
|
} |
|
121
|
|
|
} else { |
|
122
|
|
|
// 'Add a new item' mode |
|
123
|
|
|
$result = $this->dbi->tryQuery($itemQuery); |
|
124
|
|
|
if (! $result) { |
|
125
|
|
|
$GLOBALS['errors'][] = sprintf( |
|
126
|
|
|
__('The following query has failed: "%s"'), |
|
127
|
|
|
htmlspecialchars($itemQuery), |
|
128
|
|
|
) |
|
129
|
|
|
. '<br><br>' |
|
130
|
|
|
. __('MySQL said: ') . $this->dbi->getError(); |
|
131
|
|
|
} else { |
|
132
|
|
|
$GLOBALS['message'] = Message::success( |
|
133
|
|
|
__('Trigger %1$s has been created.'), |
|
134
|
|
|
); |
|
135
|
|
|
$GLOBALS['message']->addParam( |
|
136
|
|
|
Util::backquote($_POST['item_name']), |
|
137
|
|
|
); |
|
138
|
|
|
$sqlQuery = $itemQuery; |
|
139
|
|
|
} |
|
140
|
|
|
} |
|
141
|
|
|
} |
|
142
|
|
|
|
|
143
|
|
|
if ($GLOBALS['errors'] !== []) { |
|
144
|
|
|
$GLOBALS['message'] = Message::error( |
|
145
|
|
|
'<b>' |
|
146
|
|
|
. __( |
|
147
|
|
|
'One or more errors have occurred while processing your request:', |
|
148
|
|
|
) |
|
149
|
|
|
. '</b>', |
|
150
|
|
|
); |
|
151
|
|
|
$GLOBALS['message']->addHtml('<ul>'); |
|
152
|
|
|
foreach ($GLOBALS['errors'] as $string) { |
|
153
|
|
|
$GLOBALS['message']->addHtml('<li>' . $string . '</li>'); |
|
154
|
|
|
} |
|
155
|
|
|
|
|
156
|
|
|
$GLOBALS['message']->addHtml('</ul>'); |
|
157
|
|
|
} |
|
158
|
|
|
|
|
159
|
|
|
return Generator::getMessage($GLOBALS['message'], $sqlQuery); |
|
160
|
|
|
} |
|
161
|
|
|
|
|
162
|
|
|
/** @return Trigger|null Data necessary to create the editor. */ |
|
163
|
|
|
public function getTriggerByName(string $db, string $table, string $name): Trigger|null |
|
164
|
|
|
{ |
|
165
|
|
|
$triggers = self::getDetails($this->dbi, $db, $table); |
|
166
|
|
|
foreach ($triggers as $trigger) { |
|
167
|
|
|
if ($trigger->name->getName() === $name) { |
|
168
|
|
|
return $trigger; |
|
169
|
|
|
} |
|
170
|
|
|
} |
|
171
|
|
|
|
|
172
|
|
|
return null; |
|
173
|
|
|
} |
|
174
|
|
|
|
|
175
|
|
|
/** |
|
176
|
|
|
* Composes the query necessary to create a trigger from an HTTP request. |
|
177
|
|
|
* |
|
178
|
|
|
* @return string The CREATE TRIGGER query. |
|
179
|
|
|
*/ |
|
180
|
16 |
|
public function getQueryFromRequest(): string |
|
181
|
|
|
{ |
|
182
|
16 |
|
$GLOBALS['errors'] ??= null; |
|
183
|
|
|
|
|
184
|
16 |
|
$query = 'CREATE '; |
|
185
|
16 |
|
if (! empty($_POST['item_definer'])) { |
|
186
|
12 |
|
if (str_contains($_POST['item_definer'], '@')) { |
|
187
|
8 |
|
$arr = explode('@', $_POST['item_definer']); |
|
188
|
8 |
|
$query .= 'DEFINER=' . Util::backquote($arr[0]); |
|
189
|
8 |
|
$query .= '@' . Util::backquote($arr[1]) . ' '; |
|
190
|
|
|
} else { |
|
191
|
4 |
|
$GLOBALS['errors'][] = __('The definer must be in the "username@hostname" format!'); |
|
192
|
|
|
} |
|
193
|
|
|
} |
|
194
|
|
|
|
|
195
|
16 |
|
$query .= 'TRIGGER '; |
|
196
|
16 |
|
if (! empty($_POST['item_name'])) { |
|
197
|
12 |
|
$query .= Util::backquote($_POST['item_name']) . ' '; |
|
198
|
|
|
} else { |
|
199
|
4 |
|
$GLOBALS['errors'][] = __('You must provide a trigger name!'); |
|
200
|
|
|
} |
|
201
|
|
|
|
|
202
|
16 |
|
if (! empty($_POST['item_timing']) && in_array($_POST['item_timing'], $this->time, true)) { |
|
203
|
12 |
|
$query .= $_POST['item_timing'] . ' '; |
|
204
|
|
|
} else { |
|
205
|
4 |
|
$GLOBALS['errors'][] = __('You must provide a valid timing for the trigger!'); |
|
206
|
|
|
} |
|
207
|
|
|
|
|
208
|
16 |
|
if (! empty($_POST['item_event']) && in_array($_POST['item_event'], self::EVENTS, true)) { |
|
209
|
8 |
|
$query .= $_POST['item_event'] . ' '; |
|
210
|
|
|
} else { |
|
211
|
8 |
|
$GLOBALS['errors'][] = __('You must provide a valid event for the trigger!'); |
|
212
|
|
|
} |
|
213
|
|
|
|
|
214
|
16 |
|
$query .= 'ON '; |
|
215
|
|
|
if ( |
|
216
|
16 |
|
! empty($_POST['item_table']) |
|
217
|
16 |
|
&& in_array($_POST['item_table'], $this->dbi->getTables(Current::$database), true) |
|
218
|
|
|
) { |
|
219
|
4 |
|
$query .= Util::backquote($_POST['item_table']); |
|
220
|
|
|
} else { |
|
221
|
12 |
|
$GLOBALS['errors'][] = __('You must provide a valid table name!'); |
|
222
|
|
|
} |
|
223
|
|
|
|
|
224
|
16 |
|
$query .= ' FOR EACH ROW '; |
|
225
|
16 |
|
if (! empty($_POST['item_definition'])) { |
|
226
|
12 |
|
$query .= $_POST['item_definition']; |
|
227
|
|
|
} else { |
|
228
|
4 |
|
$GLOBALS['errors'][] = __('You must provide a trigger definition.'); |
|
229
|
|
|
} |
|
230
|
|
|
|
|
231
|
16 |
|
return $query; |
|
232
|
|
|
} |
|
233
|
|
|
|
|
234
|
|
|
/** |
|
235
|
|
|
* Returns details about the TRIGGERs for a specific table or database. |
|
236
|
|
|
* |
|
237
|
|
|
* @return Trigger[] |
|
238
|
|
|
*/ |
|
239
|
24 |
|
public static function getDetails( |
|
240
|
|
|
DatabaseInterface $dbi, |
|
241
|
|
|
string $db, |
|
242
|
|
|
string $table = '', |
|
243
|
|
|
): array { |
|
244
|
24 |
|
$result = []; |
|
245
|
24 |
|
$triggers = self::fetchTriggerInfo($dbi, $db, $table); |
|
246
|
|
|
|
|
247
|
24 |
|
foreach ($triggers as $trigger) { |
|
248
|
20 |
|
$newTrigger = Trigger::tryFromArray($trigger); |
|
249
|
20 |
|
if ($newTrigger === null) { |
|
250
|
|
|
continue; |
|
251
|
|
|
} |
|
252
|
|
|
|
|
253
|
20 |
|
$result[] = $newTrigger; |
|
254
|
|
|
} |
|
255
|
|
|
|
|
256
|
|
|
// Sort results by name |
|
257
|
24 |
|
$name = array_column($result, 'name'); |
|
258
|
24 |
|
array_multisort($name, SORT_ASC, $result); |
|
|
|
|
|
|
259
|
|
|
|
|
260
|
24 |
|
return $result; |
|
261
|
|
|
} |
|
262
|
|
|
|
|
263
|
|
|
/** @return list<non-empty-string> */ |
|
|
|
|
|
|
264
|
|
|
public function getTables(string $db): array |
|
265
|
|
|
{ |
|
266
|
|
|
$query = sprintf( |
|
267
|
|
|
'SELECT `TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA`=%s' |
|
268
|
|
|
. " AND `TABLE_TYPE` IN ('BASE TABLE', 'SYSTEM VERSIONED')", |
|
269
|
|
|
$this->dbi->quoteString($db), |
|
270
|
|
|
); |
|
271
|
|
|
$tables = $this->dbi->fetchResult($query); |
|
272
|
|
|
Assert::allStringNotEmpty($tables); |
|
273
|
|
|
Assert::isList($tables); |
|
274
|
|
|
|
|
275
|
|
|
return $tables; |
|
276
|
|
|
} |
|
277
|
|
|
} |
|
278
|
|
|
|
phpmyadmin /
phpmyadmin
Loading history...
This function has been deprecated. The supplier of the function has supplied an explanatory message.
The explanatory message should give you some clue as to whether and when the function will be removed and what other function to use instead.