1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
declare(strict_types=1); |
4
|
|
|
|
5
|
|
|
namespace PhpMyAdmin\Database; |
6
|
|
|
|
7
|
|
|
use PhpMyAdmin\Charsets; |
8
|
|
|
use PhpMyAdmin\Config; |
9
|
|
|
use PhpMyAdmin\Current; |
10
|
|
|
use PhpMyAdmin\DatabaseInterface; |
11
|
|
|
use PhpMyAdmin\Html\Generator; |
12
|
|
|
use PhpMyAdmin\Message; |
13
|
|
|
use PhpMyAdmin\Query\Generator as QueryGenerator; |
14
|
|
|
use PhpMyAdmin\SqlParser\Parser; |
15
|
|
|
use PhpMyAdmin\SqlParser\Statements\CreateStatement; |
16
|
|
|
use PhpMyAdmin\SqlParser\TokensList; |
17
|
|
|
use PhpMyAdmin\SqlParser\Utils\Routine as RoutineUtils; |
18
|
|
|
use PhpMyAdmin\UserPrivileges; |
19
|
|
|
use PhpMyAdmin\Util; |
20
|
|
|
|
21
|
|
|
use function __; |
22
|
|
|
use function _ngettext; |
23
|
|
|
use function array_column; |
24
|
|
|
use function array_merge; |
25
|
|
|
use function array_multisort; |
26
|
|
|
use function count; |
27
|
|
|
use function explode; |
28
|
|
|
use function htmlentities; |
29
|
|
|
use function htmlspecialchars; |
30
|
|
|
use function implode; |
31
|
|
|
use function in_array; |
32
|
|
|
use function is_array; |
33
|
|
|
use function is_string; |
34
|
|
|
use function max; |
35
|
|
|
use function mb_strtolower; |
36
|
|
|
use function mb_strtoupper; |
37
|
|
|
use function preg_match; |
38
|
|
|
use function sprintf; |
39
|
|
|
use function str_contains; |
40
|
|
|
use function str_ends_with; |
41
|
|
|
use function str_starts_with; |
42
|
|
|
use function stripos; |
43
|
|
|
|
44
|
|
|
use const ENT_QUOTES; |
45
|
|
|
use const SORT_ASC; |
46
|
|
|
|
47
|
|
|
/** |
48
|
|
|
* Functions for routine management. |
49
|
|
|
*/ |
50
|
|
|
class Routines |
51
|
|
|
{ |
52
|
|
|
/** @var array<int, string> */ |
53
|
|
|
public readonly array $directions; |
54
|
|
|
|
55
|
|
|
/** @var array<int, string> */ |
56
|
|
|
public readonly array $sqlDataAccess; |
57
|
|
|
|
58
|
|
|
/** @var array<int, string> */ |
59
|
|
|
public readonly array $numericOptions; |
60
|
|
|
|
61
|
72 |
|
public function __construct(private DatabaseInterface $dbi) |
62
|
|
|
{ |
63
|
72 |
|
$this->directions = ['IN', 'OUT', 'INOUT']; |
|
|
|
|
64
|
72 |
|
$this->sqlDataAccess = ['CONTAINS SQL', 'NO SQL', 'READS SQL DATA', 'MODIFIES SQL DATA']; |
|
|
|
|
65
|
72 |
|
$this->numericOptions = ['UNSIGNED', 'ZEROFILL', 'UNSIGNED ZEROFILL']; |
|
|
|
|
66
|
|
|
} |
67
|
|
|
|
68
|
|
|
/** |
69
|
|
|
* Handle request to create or edit a routine |
70
|
|
|
*/ |
71
|
|
|
public function handleRequestCreateOrEdit(UserPrivileges $userPrivileges, string $db): string |
72
|
|
|
{ |
73
|
|
|
$sqlQuery = ''; |
74
|
|
|
$routineQuery = $this->getQueryFromRequest(); |
75
|
|
|
|
76
|
|
|
// set by getQueryFromRequest() |
77
|
|
|
if ($GLOBALS['errors'] === []) { |
78
|
|
|
// Execute the created query |
79
|
|
|
if (! empty($_POST['editor_process_edit'])) { |
80
|
|
|
if (! in_array($_POST['item_original_type'], ['PROCEDURE', 'FUNCTION'], true)) { |
81
|
|
|
$GLOBALS['errors'][] = sprintf( |
82
|
|
|
__('Invalid routine type: "%s"'), |
83
|
|
|
htmlspecialchars($_POST['item_original_type']), |
84
|
|
|
); |
85
|
|
|
} else { |
86
|
|
|
// Backup the old routine, in case something goes wrong |
87
|
|
|
if ($_POST['item_original_type'] === 'FUNCTION') { |
88
|
|
|
$createRoutine = self::getFunctionDefinition($this->dbi, $db, $_POST['item_original_name']); |
89
|
|
|
} else { |
90
|
|
|
$createRoutine = self::getProcedureDefinition($this->dbi, $db, $_POST['item_original_name']); |
91
|
|
|
} |
92
|
|
|
|
93
|
|
|
$privilegesBackup = $this->backupPrivileges($userPrivileges); |
94
|
|
|
|
95
|
|
|
$dropRoutine = 'DROP ' . $_POST['item_original_type'] . ' ' |
96
|
|
|
. Util::backquote($_POST['item_original_name']) |
97
|
|
|
. ";\n"; |
98
|
|
|
$result = $this->dbi->tryQuery($dropRoutine); |
99
|
|
|
if (! $result) { |
100
|
|
|
$GLOBALS['errors'][] = sprintf( |
101
|
|
|
__('The following query has failed: "%s"'), |
102
|
|
|
htmlspecialchars($dropRoutine), |
103
|
|
|
) |
104
|
|
|
. '<br>' |
105
|
|
|
. __('MySQL said: ') . $this->dbi->getError(); |
106
|
|
|
} else { |
107
|
|
|
[$newErrors, $GLOBALS['message']] = $this->create( |
108
|
|
|
$userPrivileges, |
109
|
|
|
$routineQuery, |
110
|
|
|
$createRoutine, |
111
|
|
|
$privilegesBackup, |
112
|
|
|
); |
113
|
|
|
if (empty($newErrors)) { |
114
|
|
|
$sqlQuery = $dropRoutine . $routineQuery; |
115
|
|
|
} else { |
116
|
|
|
$GLOBALS['errors'] = array_merge($GLOBALS['errors'], $newErrors); |
117
|
|
|
} |
118
|
|
|
|
119
|
|
|
unset($newErrors); |
120
|
|
|
} |
121
|
|
|
} |
122
|
|
|
} else { |
123
|
|
|
// 'Add a new routine' mode |
124
|
|
|
$result = $this->dbi->tryQuery($routineQuery); |
125
|
|
|
if (! $result) { |
126
|
|
|
$GLOBALS['errors'][] = sprintf( |
127
|
|
|
__('The following query has failed: "%s"'), |
128
|
|
|
htmlspecialchars($routineQuery), |
129
|
|
|
) |
130
|
|
|
. '<br><br>' |
131
|
|
|
. __('MySQL said: ') . $this->dbi->getError(); |
132
|
|
|
} else { |
133
|
|
|
$GLOBALS['message'] = Message::success( |
134
|
|
|
__('Routine %1$s has been created.'), |
135
|
|
|
); |
136
|
|
|
$GLOBALS['message']->addParam( |
137
|
|
|
Util::backquote($_POST['item_name']), |
138
|
|
|
); |
139
|
|
|
$sqlQuery = $routineQuery; |
140
|
|
|
} |
141
|
|
|
} |
142
|
|
|
} |
143
|
|
|
|
144
|
|
|
if ($GLOBALS['errors'] !== []) { |
145
|
|
|
$GLOBALS['message'] = Message::error( |
146
|
|
|
__( |
147
|
|
|
'One or more errors have occurred while processing your request:', |
148
|
|
|
), |
149
|
|
|
); |
150
|
|
|
$GLOBALS['message']->addHtml('<ul>'); |
151
|
|
|
foreach ($GLOBALS['errors'] as $string) { |
152
|
|
|
$GLOBALS['message']->addHtml('<li>' . $string . '</li>'); |
153
|
|
|
} |
154
|
|
|
|
155
|
|
|
$GLOBALS['message']->addHtml('</ul>'); |
156
|
|
|
} |
157
|
|
|
|
158
|
|
|
return Generator::getMessage($GLOBALS['message'], $sqlQuery); |
159
|
|
|
} |
160
|
|
|
|
161
|
|
|
/** |
162
|
|
|
* Backup the privileges |
163
|
|
|
* |
164
|
|
|
* @return string[][] |
165
|
|
|
*/ |
166
|
|
|
public function backupPrivileges(UserPrivileges $userPrivileges): array |
167
|
|
|
{ |
168
|
|
|
if (! $userPrivileges->routines || ! $userPrivileges->isReload) { |
169
|
|
|
return []; |
170
|
|
|
} |
171
|
|
|
|
172
|
|
|
// Backup the Old Privileges before dropping |
173
|
|
|
// if $_POST['item_adjust_privileges'] set |
174
|
|
|
if (empty($_POST['item_adjust_privileges'])) { |
175
|
|
|
return []; |
176
|
|
|
} |
177
|
|
|
|
178
|
|
|
$privilegesBackupQuery = 'SELECT * FROM ' . Util::backquote('mysql') |
179
|
|
|
. '.' . Util::backquote('procs_priv') |
180
|
|
|
. ' WHERE Routine_name = ' . $this->dbi->quoteString($_POST['item_original_name']) |
181
|
|
|
. ' AND Routine_type = ' . $this->dbi->quoteString($_POST['item_original_type']); |
182
|
|
|
|
183
|
|
|
return $this->dbi->fetchResult($privilegesBackupQuery, 0); |
|
|
|
|
184
|
|
|
} |
185
|
|
|
|
186
|
|
|
/** |
187
|
|
|
* Create the routine |
188
|
|
|
* |
189
|
|
|
* @param string $routineQuery Query to create routine |
190
|
|
|
* @param string $createRoutine Query to restore routine |
191
|
|
|
* @param string[][] $privilegesBackup Privileges backup |
192
|
|
|
* |
193
|
|
|
* @return array{string[], Message|null} |
|
|
|
|
194
|
|
|
*/ |
195
|
|
|
public function create( |
196
|
|
|
UserPrivileges $userPrivileges, |
197
|
|
|
string $routineQuery, |
198
|
|
|
string $createRoutine, |
199
|
|
|
array $privilegesBackup, |
200
|
|
|
): array { |
201
|
|
|
$result = $this->dbi->tryQuery($routineQuery); |
202
|
|
|
if (! $result) { |
203
|
|
|
$errors = []; |
204
|
|
|
$errors[] = sprintf( |
205
|
|
|
__('The following query has failed: "%s"'), |
206
|
|
|
htmlspecialchars($routineQuery), |
207
|
|
|
) |
208
|
|
|
. '<br>' |
209
|
|
|
. __('MySQL said: ') . $this->dbi->getError(); |
210
|
|
|
// We dropped the old routine, |
211
|
|
|
// but were unable to create the new one |
212
|
|
|
// Try to restore the backup query |
213
|
|
|
$result = $this->dbi->tryQuery($createRoutine); |
214
|
|
|
if (! $result) { |
215
|
|
|
// OMG, this is really bad! We dropped the query, |
216
|
|
|
// failed to create a new one |
217
|
|
|
// and now even the backup query does not execute! |
218
|
|
|
// This should not happen, but we better handle |
219
|
|
|
// this just in case. |
220
|
|
|
$errors[] = __('Sorry, we failed to restore the dropped routine.') . '<br>' |
221
|
|
|
. __('The backed up query was:') |
222
|
|
|
. '"' . htmlspecialchars($createRoutine) . '"<br>' |
223
|
|
|
. __('MySQL said: ') . $this->dbi->getError(); |
224
|
|
|
} |
225
|
|
|
|
226
|
|
|
return [$errors, null]; |
227
|
|
|
} |
228
|
|
|
|
229
|
|
|
// Default value |
230
|
|
|
$resultAdjust = false; |
231
|
|
|
|
232
|
|
|
if ($userPrivileges->routines && $userPrivileges->isReload) { |
233
|
|
|
// Insert all the previous privileges |
234
|
|
|
// but with the new name and the new type |
235
|
|
|
foreach ($privilegesBackup as $priv) { |
236
|
|
|
$adjustProcPrivilege = 'INSERT INTO ' |
237
|
|
|
. Util::backquote('mysql') . '.' |
238
|
|
|
. Util::backquote('procs_priv') |
239
|
|
|
. ' VALUES(' . $this->dbi->quoteString($priv[0]) . ', ' |
240
|
|
|
. $this->dbi->quoteString($priv[1]) . ', ' . $this->dbi->quoteString($priv[2]) . ', ' |
241
|
|
|
. $this->dbi->quoteString($_POST['item_name']) . ', ' |
242
|
|
|
. $this->dbi->quoteString($_POST['item_type']) . ', ' |
243
|
|
|
. $this->dbi->quoteString($priv[5]) . ', ' |
244
|
|
|
. $this->dbi->quoteString($priv[6]) . ', ' |
245
|
|
|
. $this->dbi->quoteString($priv[7]) . ');'; |
246
|
|
|
$this->dbi->query($adjustProcPrivilege); |
247
|
|
|
$resultAdjust = true; |
248
|
|
|
} |
249
|
|
|
} |
250
|
|
|
|
251
|
|
|
$message = $this->flushPrivileges($resultAdjust); |
252
|
|
|
|
253
|
|
|
return [[], $message]; |
254
|
|
|
} |
255
|
|
|
|
256
|
|
|
/** |
257
|
|
|
* Flush privileges and get message |
258
|
|
|
* |
259
|
|
|
* @param bool $flushPrivileges Flush privileges |
260
|
|
|
*/ |
261
|
|
|
public function flushPrivileges(bool $flushPrivileges): Message |
262
|
|
|
{ |
263
|
|
|
if ($flushPrivileges) { |
264
|
|
|
// Flush the Privileges |
265
|
|
|
$flushPrivQuery = 'FLUSH PRIVILEGES;'; |
266
|
|
|
$this->dbi->query($flushPrivQuery); |
267
|
|
|
|
268
|
|
|
$message = Message::success( |
269
|
|
|
__( |
270
|
|
|
'Routine %1$s has been modified. Privileges have been adjusted.', |
271
|
|
|
), |
272
|
|
|
); |
273
|
|
|
} else { |
274
|
|
|
$message = Message::success( |
275
|
|
|
__('Routine %1$s has been modified.'), |
276
|
|
|
); |
277
|
|
|
} |
278
|
|
|
|
279
|
|
|
$message->addParam( |
280
|
|
|
Util::backquote($_POST['item_name']), |
281
|
|
|
); |
282
|
|
|
|
283
|
|
|
return $message; |
284
|
|
|
} |
285
|
|
|
|
286
|
|
|
/** |
287
|
|
|
* This function will generate the values that are required to |
288
|
|
|
* complete the editor form. It is especially necessary to handle |
289
|
|
|
* the 'Add another parameter', 'Remove last parameter' and |
290
|
|
|
* 'Change routine type' functionalities when JS is disabled. |
291
|
|
|
* |
292
|
|
|
* @return mixed[] Data necessary to create the routine editor. |
293
|
|
|
*/ |
294
|
12 |
|
public function getDataFromRequest(): array |
295
|
|
|
{ |
296
|
12 |
|
$retval = []; |
297
|
12 |
|
$indices = [ |
298
|
12 |
|
'item_name', |
299
|
12 |
|
'item_original_name', |
300
|
12 |
|
'item_returnlength', |
301
|
12 |
|
'item_returnopts_num', |
302
|
12 |
|
'item_returnopts_text', |
303
|
12 |
|
'item_definition', |
304
|
12 |
|
'item_comment', |
305
|
12 |
|
'item_definer', |
306
|
12 |
|
]; |
307
|
12 |
|
foreach ($indices as $index) { |
308
|
12 |
|
$retval[$index] = $_POST[$index] ?? ''; |
309
|
|
|
} |
310
|
|
|
|
311
|
12 |
|
$retval['item_type'] = 'PROCEDURE'; |
312
|
12 |
|
$retval['item_type_toggle'] = 'FUNCTION'; |
313
|
12 |
|
if (isset($_POST['item_type']) && $_POST['item_type'] === 'FUNCTION') { |
314
|
4 |
|
$retval['item_type'] = 'FUNCTION'; |
315
|
4 |
|
$retval['item_type_toggle'] = 'PROCEDURE'; |
316
|
|
|
} |
317
|
|
|
|
318
|
12 |
|
$retval['item_original_type'] = 'PROCEDURE'; |
319
|
12 |
|
if (isset($_POST['item_original_type']) && $_POST['item_original_type'] === 'FUNCTION') { |
320
|
4 |
|
$retval['item_original_type'] = 'FUNCTION'; |
321
|
|
|
} |
322
|
|
|
|
323
|
12 |
|
$retval['item_num_params'] = 0; |
324
|
12 |
|
$retval['item_param_dir'] = []; |
325
|
12 |
|
$retval['item_param_name'] = []; |
326
|
12 |
|
$retval['item_param_type'] = []; |
327
|
12 |
|
$retval['item_param_length'] = []; |
328
|
12 |
|
$retval['item_param_opts_num'] = []; |
329
|
12 |
|
$retval['item_param_opts_text'] = []; |
330
|
|
|
if ( |
331
|
|
|
isset( |
332
|
12 |
|
$_POST['item_param_name'], |
333
|
12 |
|
$_POST['item_param_type'], |
334
|
12 |
|
$_POST['item_param_length'], |
335
|
12 |
|
$_POST['item_param_opts_num'], |
336
|
12 |
|
$_POST['item_param_opts_text'], |
337
|
|
|
) |
338
|
12 |
|
&& is_array($_POST['item_param_name']) |
339
|
12 |
|
&& is_array($_POST['item_param_type']) |
340
|
12 |
|
&& is_array($_POST['item_param_length']) |
341
|
12 |
|
&& is_array($_POST['item_param_opts_num']) |
342
|
12 |
|
&& is_array($_POST['item_param_opts_text']) |
343
|
|
|
) { |
344
|
8 |
|
if ($_POST['item_type'] === 'PROCEDURE') { |
345
|
4 |
|
$retval['item_param_dir'] = $_POST['item_param_dir']; |
346
|
4 |
|
foreach ($retval['item_param_dir'] as $key => $value) { |
347
|
4 |
|
if (in_array($value, $this->directions, true)) { |
348
|
4 |
|
continue; |
349
|
|
|
} |
350
|
|
|
|
351
|
4 |
|
$retval['item_param_dir'][$key] = ''; |
352
|
|
|
} |
353
|
|
|
} |
354
|
|
|
|
355
|
8 |
|
$retval['item_param_name'] = $_POST['item_param_name']; |
356
|
8 |
|
$retval['item_param_type'] = $_POST['item_param_type']; |
357
|
8 |
|
foreach ($retval['item_param_type'] as $key => $value) { |
358
|
8 |
|
if (in_array($value, Util::getSupportedDatatypes(), true)) { |
359
|
8 |
|
continue; |
360
|
|
|
} |
361
|
|
|
|
362
|
8 |
|
$retval['item_param_type'][$key] = ''; |
363
|
|
|
} |
364
|
|
|
|
365
|
8 |
|
$retval['item_param_length'] = $_POST['item_param_length']; |
366
|
8 |
|
$retval['item_param_opts_num'] = $_POST['item_param_opts_num']; |
367
|
8 |
|
$retval['item_param_opts_text'] = $_POST['item_param_opts_text']; |
368
|
8 |
|
$retval['item_num_params'] = max( |
369
|
8 |
|
count($retval['item_param_name']), |
370
|
8 |
|
count($retval['item_param_type']), |
371
|
8 |
|
count($retval['item_param_length']), |
372
|
8 |
|
count($retval['item_param_opts_num']), |
373
|
8 |
|
count($retval['item_param_opts_text']), |
374
|
8 |
|
); |
375
|
|
|
} |
376
|
|
|
|
377
|
12 |
|
$retval['item_returntype'] = ''; |
378
|
|
|
if ( |
379
|
12 |
|
isset($_POST['item_returntype']) |
380
|
12 |
|
&& in_array($_POST['item_returntype'], Util::getSupportedDatatypes(), true) |
381
|
|
|
) { |
382
|
4 |
|
$retval['item_returntype'] = $_POST['item_returntype']; |
383
|
|
|
} |
384
|
|
|
|
385
|
12 |
|
$retval['item_isdeterministic'] = ''; |
386
|
12 |
|
if (isset($_POST['item_isdeterministic']) && mb_strtolower($_POST['item_isdeterministic']) === 'on') { |
387
|
4 |
|
$retval['item_isdeterministic'] = " checked='checked'"; |
388
|
|
|
} |
389
|
|
|
|
390
|
12 |
|
$retval['item_securitytype_definer'] = ''; |
391
|
12 |
|
$retval['item_securitytype_invoker'] = ''; |
392
|
12 |
|
if (isset($_POST['item_securitytype'])) { |
393
|
8 |
|
if ($_POST['item_securitytype'] === 'DEFINER') { |
394
|
4 |
|
$retval['item_securitytype_definer'] = " selected='selected'"; |
395
|
4 |
|
} elseif ($_POST['item_securitytype'] === 'INVOKER') { |
396
|
4 |
|
$retval['item_securitytype_invoker'] = " selected='selected'"; |
397
|
|
|
} |
398
|
|
|
} |
399
|
|
|
|
400
|
12 |
|
$retval['item_sqldataaccess'] = ''; |
401
|
12 |
|
if (isset($_POST['item_sqldataaccess']) && in_array($_POST['item_sqldataaccess'], $this->sqlDataAccess, true)) { |
402
|
4 |
|
$retval['item_sqldataaccess'] = $_POST['item_sqldataaccess']; |
403
|
|
|
} |
404
|
|
|
|
405
|
12 |
|
return $retval; |
406
|
|
|
} |
407
|
|
|
|
408
|
|
|
/** |
409
|
|
|
* This function will generate the values that are required to complete |
410
|
|
|
* the "Edit routine" form given the name of a routine. |
411
|
|
|
* |
412
|
|
|
* @param string $name The name of the routine. |
413
|
|
|
* @param string $type Type of routine (ROUTINE|PROCEDURE) |
414
|
|
|
* @param bool $all Whether to return all data or just the info about parameters. |
415
|
|
|
* |
416
|
|
|
* @return mixed[]|null Data necessary to create the routine editor. |
417
|
|
|
*/ |
418
|
|
|
public function getDataFromName(string $name, string $type, bool $all = true): array|null |
419
|
|
|
{ |
420
|
|
|
$retval = []; |
421
|
|
|
|
422
|
|
|
// Build and execute the query |
423
|
|
|
$fields = 'SPECIFIC_NAME, ROUTINE_TYPE, DTD_IDENTIFIER, ' |
424
|
|
|
. 'ROUTINE_DEFINITION, IS_DETERMINISTIC, SQL_DATA_ACCESS, ' |
425
|
|
|
. 'ROUTINE_COMMENT, SECURITY_TYPE'; |
426
|
|
|
$where = 'ROUTINE_SCHEMA ' . Util::getCollateForIS() . '=' . $this->dbi->quoteString(Current::$database) |
427
|
|
|
. ' AND SPECIFIC_NAME=' . $this->dbi->quoteString($name) |
428
|
|
|
. ' AND ROUTINE_TYPE=' . $this->dbi->quoteString($type); |
429
|
|
|
$query = 'SELECT ' . $fields . ' FROM INFORMATION_SCHEMA.ROUTINES WHERE ' . $where . ';'; |
430
|
|
|
|
431
|
|
|
$routine = $this->dbi->fetchSingleRow($query); |
432
|
|
|
|
433
|
|
|
if ($routine === null || $routine === []) { |
434
|
|
|
return null; |
435
|
|
|
} |
436
|
|
|
|
437
|
|
|
// Get required data |
438
|
|
|
$retval['item_name'] = $routine['SPECIFIC_NAME']; |
439
|
|
|
$retval['item_type'] = $routine['ROUTINE_TYPE']; |
440
|
|
|
|
441
|
|
|
if ($routine['ROUTINE_TYPE'] === 'FUNCTION') { |
442
|
|
|
$definition = self::getFunctionDefinition($this->dbi, Current::$database, $routine['SPECIFIC_NAME']); |
443
|
|
|
} else { |
444
|
|
|
$definition = self::getProcedureDefinition($this->dbi, Current::$database, $routine['SPECIFIC_NAME']); |
445
|
|
|
} |
446
|
|
|
|
447
|
|
|
if ($definition === null) { |
448
|
|
|
return null; |
449
|
|
|
} |
450
|
|
|
|
451
|
|
|
$parser = new Parser('DELIMITER $$' . "\n" . $definition); |
452
|
|
|
|
453
|
|
|
/** @var CreateStatement $stmt */ |
454
|
|
|
$stmt = $parser->statements[0]; |
455
|
|
|
|
456
|
|
|
// Do not use $routine['ROUTINE_DEFINITION'] because of a MySQL escaping issue: #15370 |
457
|
|
|
$body = TokensList::buildFromArray($stmt->body); |
458
|
|
|
if ($body === '') { |
459
|
|
|
// Fallback just in case the parser fails |
460
|
|
|
$body = (string) $routine['ROUTINE_DEFINITION']; |
461
|
|
|
} |
462
|
|
|
|
463
|
|
|
$params = RoutineUtils::getParameters($stmt); |
464
|
|
|
$retval['item_num_params'] = $params['num']; |
465
|
|
|
$retval['item_param_dir'] = $params['dir']; |
466
|
|
|
$retval['item_param_name'] = $params['name']; |
467
|
|
|
$retval['item_param_type'] = $params['type']; |
468
|
|
|
$retval['item_param_length'] = $params['length']; |
469
|
|
|
$retval['item_param_length_arr'] = $params['length_arr']; |
470
|
|
|
$retval['item_param_opts_num'] = $params['opts']; |
471
|
|
|
$retval['item_param_opts_text'] = $params['opts']; |
472
|
|
|
|
473
|
|
|
// Get extra data |
474
|
|
|
if (! $all) { |
475
|
|
|
return $retval; |
476
|
|
|
} |
477
|
|
|
|
478
|
|
|
if ($retval['item_type'] === 'FUNCTION') { |
479
|
|
|
$retval['item_type_toggle'] = 'PROCEDURE'; |
480
|
|
|
} else { |
481
|
|
|
$retval['item_type_toggle'] = 'FUNCTION'; |
482
|
|
|
} |
483
|
|
|
|
484
|
|
|
$retval['item_returntype'] = ''; |
485
|
|
|
$retval['item_returnlength'] = ''; |
486
|
|
|
$retval['item_returnopts_num'] = ''; |
487
|
|
|
$retval['item_returnopts_text'] = ''; |
488
|
|
|
|
489
|
|
|
if (! empty($routine['DTD_IDENTIFIER'])) { |
490
|
|
|
$options = []; |
491
|
|
|
foreach ($stmt->return->options->options as $opt) { |
492
|
|
|
$options[] = is_string($opt) ? $opt : $opt['value']; |
493
|
|
|
} |
494
|
|
|
|
495
|
|
|
$retval['item_returntype'] = $stmt->return->name; |
496
|
|
|
$retval['item_returnlength'] = implode(',', $stmt->return->parameters); |
497
|
|
|
$retval['item_returnopts_num'] = implode(' ', $options); |
498
|
|
|
$retval['item_returnopts_text'] = implode(' ', $options); |
499
|
|
|
} |
500
|
|
|
|
501
|
|
|
$retval['item_definer'] = $stmt->options->has('DEFINER'); |
|
|
|
|
502
|
|
|
$retval['item_definition'] = $body; |
503
|
|
|
$retval['item_isdeterministic'] = ''; |
504
|
|
|
if ($routine['IS_DETERMINISTIC'] === 'YES') { |
505
|
|
|
$retval['item_isdeterministic'] = " checked='checked'"; |
506
|
|
|
} |
507
|
|
|
|
508
|
|
|
$retval['item_securitytype_definer'] = ''; |
509
|
|
|
$retval['item_securitytype_invoker'] = ''; |
510
|
|
|
if ($routine['SECURITY_TYPE'] === 'DEFINER') { |
511
|
|
|
$retval['item_securitytype_definer'] = " selected='selected'"; |
512
|
|
|
} elseif ($routine['SECURITY_TYPE'] === 'INVOKER') { |
513
|
|
|
$retval['item_securitytype_invoker'] = " selected='selected'"; |
514
|
|
|
} |
515
|
|
|
|
516
|
|
|
$retval['item_sqldataaccess'] = $routine['SQL_DATA_ACCESS']; |
517
|
|
|
$retval['item_comment'] = $routine['ROUTINE_COMMENT']; |
518
|
|
|
|
519
|
|
|
return $retval; |
520
|
|
|
} |
521
|
|
|
|
522
|
|
|
/** |
523
|
|
|
* Creates one row for the parameter table used in the routine editor. |
524
|
|
|
* |
525
|
|
|
* @param mixed[] $routine Data for the routine returned by |
526
|
|
|
* getDataFromRequest() or getDataFromName() |
527
|
|
|
* @param mixed $index Either a numeric index of the row being processed |
528
|
|
|
* or NULL to create a template row for AJAX request |
529
|
|
|
* @param string $class Class used to hide the direction column, if the |
530
|
|
|
* row is for a stored function. |
531
|
|
|
* |
532
|
|
|
* @return mixed[] |
533
|
|
|
*/ |
534
|
|
|
public function getParameterRow(array $routine = [], mixed $index = null, string $class = ''): array |
535
|
|
|
{ |
536
|
|
|
if ($index === null) { |
537
|
|
|
// template row for AJAX request |
538
|
|
|
$i = 0; |
539
|
|
|
$index = '%s'; |
540
|
|
|
$dropClass = ''; |
541
|
|
|
$routine = [ |
542
|
|
|
'item_param_dir' => [''], |
543
|
|
|
'item_param_name' => [''], |
544
|
|
|
'item_param_type' => [''], |
545
|
|
|
'item_param_length' => [''], |
546
|
|
|
'item_param_opts_num' => [''], |
547
|
|
|
'item_param_opts_text' => [''], |
548
|
|
|
]; |
549
|
|
|
} elseif ($routine !== []) { |
550
|
|
|
// regular row for routine editor |
551
|
|
|
$dropClass = ' hide'; |
552
|
|
|
$i = $index; |
553
|
|
|
} else { |
554
|
|
|
// No input data. This shouldn't happen, |
555
|
|
|
// but better be safe than sorry. |
556
|
|
|
return []; |
557
|
|
|
} |
558
|
|
|
|
559
|
|
|
$allCharsets = Charsets::getCharsets($this->dbi, Config::getInstance()->selectedServer['DisableIS']); |
|
|
|
|
560
|
|
|
$charsets = []; |
561
|
|
|
foreach ($allCharsets as $charset) { |
562
|
|
|
$charsets[] = [ |
563
|
|
|
'name' => $charset->getName(), |
564
|
|
|
'description' => $charset->getDescription(), |
565
|
|
|
'is_selected' => $charset->getName() === $routine['item_param_opts_text'][$i], |
566
|
|
|
]; |
567
|
|
|
} |
568
|
|
|
|
569
|
|
|
return [ |
570
|
|
|
'class' => $class, |
571
|
|
|
'index' => $index, |
572
|
|
|
'param_directions' => $this->directions, |
573
|
|
|
'param_opts_num' => $this->numericOptions, |
574
|
|
|
'item_param_dir' => $routine['item_param_dir'][$i] ?? '', |
575
|
|
|
'item_param_name' => $routine['item_param_name'][$i] ?? '', |
576
|
|
|
'item_param_length' => $routine['item_param_length'][$i] ?? '', |
577
|
|
|
'item_param_opts_num' => $routine['item_param_opts_num'][$i] ?? '', |
578
|
|
|
'supported_datatypes' => Generator::getSupportedDatatypes( |
579
|
|
|
$this->dbi->types->mapAliasToMysqlType($routine['item_param_type'][$i]), |
580
|
|
|
), |
581
|
|
|
'charsets' => $charsets, |
582
|
|
|
'drop_class' => $dropClass, |
583
|
|
|
]; |
584
|
|
|
} |
585
|
|
|
|
586
|
|
|
/** |
587
|
|
|
* Set the found errors and build the params |
588
|
|
|
* |
589
|
|
|
* @param string[] $itemParamName The parameter names |
590
|
|
|
* @param string[] $itemParamDir The direction parameter (see $this->directions) |
591
|
|
|
* @param mixed[] $itemParamType The parameter type |
592
|
|
|
* @param mixed[] $itemParamLength A length or not for the parameter |
593
|
|
|
* @param mixed[] $itemParamOpsText An optional charset for the parameter |
594
|
|
|
* @param mixed[] $itemParamOpsNum An optional parameter for a $itemParamType NUMBER |
595
|
|
|
* @param string $itemType The item type (PROCEDURE/FUNCTION) |
596
|
|
|
* @param bool $warnedAboutLength A boolean that will be switched if a the length warning is given |
597
|
|
|
*/ |
598
|
16 |
|
private function processParamsAndBuild( |
599
|
|
|
array $itemParamName, |
600
|
|
|
array $itemParamDir, |
601
|
|
|
array $itemParamType, |
602
|
|
|
array $itemParamLength, |
603
|
|
|
array $itemParamOpsText, |
604
|
|
|
array $itemParamOpsNum, |
605
|
|
|
string $itemType, |
606
|
|
|
bool &$warnedAboutLength, |
607
|
|
|
): string { |
608
|
16 |
|
$GLOBALS['errors'] ??= null; |
609
|
|
|
|
610
|
16 |
|
$params = ''; |
611
|
16 |
|
$warnedAboutDir = false; |
612
|
|
|
|
613
|
16 |
|
for ($i = 0, $nb = count($itemParamName); $i < $nb; $i++) { |
614
|
16 |
|
if (empty($itemParamName[$i]) || empty($itemParamType[$i])) { |
615
|
4 |
|
$GLOBALS['errors'][] = __('You must provide a name and a type for each routine parameter.'); |
616
|
4 |
|
break; |
617
|
|
|
} |
618
|
|
|
|
619
|
|
|
if ( |
620
|
12 |
|
$itemType === 'PROCEDURE' |
621
|
12 |
|
&& ! empty($itemParamDir[$i]) |
622
|
12 |
|
&& in_array($itemParamDir[$i], $this->directions, true) |
623
|
|
|
) { |
624
|
8 |
|
$params .= $itemParamDir[$i] . ' ' |
625
|
8 |
|
. Util::backquote($itemParamName[$i]) |
626
|
8 |
|
. ' ' . $itemParamType[$i]; |
627
|
8 |
|
} elseif ($itemType === 'FUNCTION') { |
628
|
4 |
|
$params .= Util::backquote($itemParamName[$i]) |
629
|
4 |
|
. ' ' . $itemParamType[$i]; |
630
|
4 |
|
} elseif (! $warnedAboutDir) { |
631
|
4 |
|
$warnedAboutDir = true; |
632
|
4 |
|
$GLOBALS['errors'][] = sprintf( |
633
|
4 |
|
__('Invalid direction "%s" given for parameter.'), |
634
|
4 |
|
htmlspecialchars($itemParamDir[$i]), |
635
|
4 |
|
); |
636
|
|
|
} |
637
|
|
|
|
638
|
|
|
if ( |
639
|
12 |
|
$itemParamLength[$i] != '' |
640
|
12 |
|
&& ! preg_match( |
641
|
12 |
|
'@^(DATE|TINYBLOB|TINYTEXT|BLOB|TEXT|MEDIUMBLOB|MEDIUMTEXT|LONGBLOB|LONGTEXT|SERIAL|BOOLEAN)$@i', |
642
|
12 |
|
$itemParamType[$i], |
643
|
12 |
|
) |
644
|
|
|
) { |
645
|
12 |
|
$params .= '(' . $itemParamLength[$i] . ')'; |
646
|
|
|
} elseif ( |
647
|
4 |
|
$itemParamLength[$i] == '' |
648
|
4 |
|
&& preg_match('@^(ENUM|SET|VARCHAR|VARBINARY)$@i', $itemParamType[$i]) |
649
|
|
|
) { |
650
|
4 |
|
if (! $warnedAboutLength) { |
651
|
4 |
|
$warnedAboutLength = true; |
652
|
4 |
|
$GLOBALS['errors'][] = __( |
653
|
4 |
|
'You must provide length/values for routine parameters' |
654
|
4 |
|
. ' of type ENUM, SET, VARCHAR and VARBINARY.', |
655
|
4 |
|
); |
656
|
|
|
} |
657
|
|
|
} |
658
|
|
|
|
659
|
12 |
|
if (! empty($itemParamOpsText[$i])) { |
660
|
12 |
|
if ($this->dbi->types->getTypeClass($itemParamType[$i]) === 'CHAR') { |
661
|
12 |
|
if (! in_array($itemParamType[$i], ['VARBINARY', 'BINARY'], true)) { |
662
|
12 |
|
$params .= ' CHARSET ' |
663
|
12 |
|
. mb_strtolower($itemParamOpsText[$i]); |
664
|
|
|
} |
665
|
|
|
} |
666
|
|
|
} |
667
|
|
|
|
668
|
12 |
|
if (! empty($itemParamOpsNum[$i])) { |
669
|
8 |
|
if ($this->dbi->types->getTypeClass($itemParamType[$i]) === 'NUMBER') { |
670
|
8 |
|
$params .= ' ' |
671
|
8 |
|
. mb_strtoupper($itemParamOpsNum[$i]); |
672
|
|
|
} |
673
|
|
|
} |
674
|
|
|
|
675
|
12 |
|
if ($i === count($itemParamName) - 1) { |
676
|
12 |
|
continue; |
677
|
|
|
} |
678
|
|
|
|
679
|
8 |
|
$params .= ', '; |
680
|
|
|
} |
681
|
|
|
|
682
|
16 |
|
return $params; |
683
|
|
|
} |
684
|
|
|
|
685
|
|
|
/** |
686
|
|
|
* Set the found errors and build the query |
687
|
|
|
* |
688
|
|
|
* @param string $query The existing query |
689
|
|
|
* @param bool $warnedAboutLength If the length warning was given |
690
|
|
|
*/ |
691
|
16 |
|
private function processFunctionSpecificParameters( |
692
|
|
|
string $query, |
693
|
|
|
bool $warnedAboutLength, |
694
|
|
|
): string { |
695
|
16 |
|
$GLOBALS['errors'] ??= null; |
696
|
|
|
|
697
|
16 |
|
$itemReturnType = $_POST['item_returntype'] ?? null; |
698
|
|
|
|
699
|
16 |
|
if ($itemReturnType !== '' && in_array($itemReturnType, Util::getSupportedDatatypes(), true)) { |
700
|
12 |
|
$query .= 'RETURNS ' . $itemReturnType; |
701
|
|
|
} else { |
702
|
4 |
|
$GLOBALS['errors'][] = __('You must provide a valid return type for the routine.'); |
703
|
|
|
} |
704
|
|
|
|
705
|
|
|
if ( |
706
|
16 |
|
! empty($_POST['item_returnlength']) |
707
|
16 |
|
&& ! preg_match( |
708
|
16 |
|
'@^(DATE|DATETIME|TIME|TINYBLOB|TINYTEXT|BLOB|TEXT|' |
709
|
16 |
|
. 'MEDIUMBLOB|MEDIUMTEXT|LONGBLOB|LONGTEXT|SERIAL|BOOLEAN)$@i', |
710
|
16 |
|
$itemReturnType, |
711
|
16 |
|
) |
712
|
|
|
) { |
713
|
8 |
|
$query .= '(' . $_POST['item_returnlength'] . ')'; |
714
|
|
|
} elseif ( |
715
|
8 |
|
empty($_POST['item_returnlength']) |
716
|
8 |
|
&& preg_match('@^(ENUM|SET|VARCHAR|VARBINARY)$@i', $itemReturnType) |
717
|
|
|
) { |
718
|
4 |
|
if (! $warnedAboutLength) { |
719
|
4 |
|
$GLOBALS['errors'][] = __( |
720
|
4 |
|
'You must provide length/values for routine parameters of type ENUM, SET, VARCHAR and VARBINARY.', |
721
|
4 |
|
); |
722
|
|
|
} |
723
|
|
|
} |
724
|
|
|
|
725
|
16 |
|
if (! empty($_POST['item_returnopts_text'])) { |
726
|
8 |
|
if ($this->dbi->types->getTypeClass($itemReturnType) === 'CHAR') { |
727
|
8 |
|
$query .= ' CHARSET ' |
728
|
8 |
|
. mb_strtolower($_POST['item_returnopts_text']); |
729
|
|
|
} |
730
|
|
|
} |
731
|
|
|
|
732
|
16 |
|
if (! empty($_POST['item_returnopts_num'])) { |
733
|
4 |
|
if ($this->dbi->types->getTypeClass($itemReturnType) === 'NUMBER') { |
734
|
4 |
|
$query .= ' ' |
735
|
4 |
|
. mb_strtoupper($_POST['item_returnopts_num']); |
736
|
|
|
} |
737
|
|
|
} |
738
|
|
|
|
739
|
16 |
|
return $query . ' '; |
740
|
|
|
} |
741
|
|
|
|
742
|
|
|
/** |
743
|
|
|
* Composes the query necessary to create a routine from an HTTP request. |
744
|
|
|
* |
745
|
|
|
* @return string The CREATE [ROUTINE | PROCEDURE] query. |
746
|
|
|
*/ |
747
|
36 |
|
public function getQueryFromRequest(): string |
748
|
|
|
{ |
749
|
36 |
|
$GLOBALS['errors'] ??= null; |
750
|
|
|
|
751
|
36 |
|
$itemType = $_POST['item_type'] ?? ''; |
752
|
36 |
|
$itemDefiner = $_POST['item_definer'] ?? ''; |
753
|
36 |
|
$itemName = $_POST['item_name'] ?? ''; |
754
|
|
|
|
755
|
36 |
|
$query = 'CREATE '; |
756
|
36 |
|
if (! empty($itemDefiner)) { |
757
|
12 |
|
if (str_contains($itemDefiner, '@')) { |
758
|
8 |
|
$arr = explode('@', $itemDefiner); |
759
|
|
|
|
760
|
8 |
|
$doBackquote = true; |
761
|
8 |
|
if (str_starts_with($arr[0], '`') && str_ends_with($arr[0], '`')) { |
762
|
|
|
$doBackquote = false; |
763
|
|
|
} |
764
|
|
|
|
765
|
8 |
|
$query .= 'DEFINER=' . Util::backquoteCompat($arr[0], 'NONE', $doBackquote); |
766
|
|
|
|
767
|
8 |
|
$doBackquote = true; |
768
|
8 |
|
if (str_starts_with($arr[1], '`') && str_ends_with($arr[1], '`')) { |
769
|
|
|
$doBackquote = false; |
770
|
|
|
} |
771
|
|
|
|
772
|
8 |
|
$query .= '@' . Util::backquoteCompat($arr[1], 'NONE', $doBackquote) . ' '; |
773
|
|
|
} else { |
774
|
4 |
|
$GLOBALS['errors'][] = __('The definer must be in the "username@hostname" format!'); |
775
|
|
|
} |
776
|
|
|
} |
777
|
|
|
|
778
|
36 |
|
if ($itemType === 'FUNCTION' || $itemType === 'PROCEDURE') { |
779
|
32 |
|
$query .= $itemType . ' '; |
780
|
|
|
} else { |
781
|
4 |
|
$GLOBALS['errors'][] = sprintf( |
782
|
4 |
|
__('Invalid routine type: "%s"'), |
783
|
4 |
|
htmlspecialchars($itemType), |
784
|
4 |
|
); |
785
|
|
|
} |
786
|
|
|
|
787
|
36 |
|
if (! empty($itemName)) { |
788
|
32 |
|
$query .= Util::backquote($itemName); |
789
|
|
|
} else { |
790
|
4 |
|
$GLOBALS['errors'][] = __('You must provide a routine name!'); |
791
|
|
|
} |
792
|
|
|
|
793
|
36 |
|
$warnedAboutLength = false; |
794
|
|
|
|
795
|
36 |
|
$itemParamName = $_POST['item_param_name'] ?? ''; |
796
|
36 |
|
$itemParamType = $_POST['item_param_type'] ?? ''; |
797
|
36 |
|
$itemParamLength = $_POST['item_param_length'] ?? ''; |
798
|
36 |
|
$itemParamDir = (array) ($_POST['item_param_dir'] ?? []); |
799
|
36 |
|
$itemParamOpsText = (array) ($_POST['item_param_opts_text'] ?? []); |
800
|
36 |
|
$itemParamOpsNum = (array) ($_POST['item_param_opts_num'] ?? []); |
801
|
|
|
|
802
|
36 |
|
$params = ''; |
803
|
|
|
if ( |
804
|
36 |
|
! empty($itemParamName) |
805
|
36 |
|
&& ! empty($itemParamType) |
806
|
36 |
|
&& ! empty($itemParamLength) |
807
|
36 |
|
&& is_array($itemParamName) |
808
|
36 |
|
&& is_array($itemParamType) |
809
|
36 |
|
&& is_array($itemParamLength) |
810
|
|
|
) { |
811
|
16 |
|
$params = $this->processParamsAndBuild( |
812
|
16 |
|
$itemParamName, |
813
|
16 |
|
$itemParamDir, |
814
|
16 |
|
$itemParamType, |
815
|
16 |
|
$itemParamLength, |
816
|
16 |
|
$itemParamOpsText, |
817
|
16 |
|
$itemParamOpsNum, |
818
|
16 |
|
$itemType, |
819
|
16 |
|
$warnedAboutLength, // Will possibly be modified by the function |
820
|
16 |
|
); |
821
|
|
|
} |
822
|
|
|
|
823
|
36 |
|
$query .= '(' . $params . ') '; |
824
|
36 |
|
if ($itemType === 'FUNCTION') { |
825
|
16 |
|
$query = $this->processFunctionSpecificParameters($query, $warnedAboutLength); |
826
|
|
|
} |
827
|
|
|
|
828
|
36 |
|
if (! empty($_POST['item_comment'])) { |
829
|
12 |
|
$query .= 'COMMENT ' . $this->dbi->quoteString($_POST['item_comment']) . ' '; |
830
|
|
|
} |
831
|
|
|
|
832
|
36 |
|
if (isset($_POST['item_isdeterministic'])) { |
833
|
12 |
|
$query .= 'DETERMINISTIC '; |
834
|
|
|
} else { |
835
|
24 |
|
$query .= 'NOT DETERMINISTIC '; |
836
|
|
|
} |
837
|
|
|
|
838
|
36 |
|
$itemSqlDataAccess = $_POST['item_sqldataaccess'] ?? ''; |
839
|
36 |
|
if (in_array($itemSqlDataAccess, $this->sqlDataAccess, true)) { |
840
|
8 |
|
$query .= $itemSqlDataAccess . ' '; |
841
|
|
|
} |
842
|
|
|
|
843
|
36 |
|
$itemSecurityType = $_POST['item_securitytype'] ?? ''; |
844
|
36 |
|
if (! empty($itemSecurityType)) { |
845
|
32 |
|
if ($itemSecurityType === 'DEFINER' || $itemSecurityType === 'INVOKER') { |
846
|
32 |
|
$query .= 'SQL SECURITY ' . $itemSecurityType . ' '; |
847
|
|
|
} |
848
|
|
|
} |
849
|
|
|
|
850
|
36 |
|
$itemDefinition = $_POST['item_definition'] ?? ''; |
851
|
36 |
|
if (! empty($itemDefinition)) { |
852
|
32 |
|
$query .= $itemDefinition; |
853
|
|
|
} else { |
854
|
4 |
|
$GLOBALS['errors'][] = __('You must provide a routine definition.'); |
855
|
|
|
} |
856
|
|
|
|
857
|
36 |
|
return $query; |
858
|
|
|
} |
859
|
|
|
|
860
|
|
|
/** |
861
|
|
|
* @param mixed[] $routine The routine params |
862
|
|
|
* |
863
|
|
|
* @return string[] The SQL queries / SQL query parts |
864
|
|
|
*/ |
865
|
|
|
private function getQueriesFromRoutineForm(array $routine): array |
866
|
|
|
{ |
867
|
|
|
$queries = []; |
868
|
|
|
$outParams = []; |
869
|
|
|
$args = []; |
870
|
|
|
$allFunctions = $this->dbi->types->getAllFunctions(); |
871
|
|
|
for ($i = 0; $i < $routine['item_num_params']; $i++) { |
872
|
|
|
if (isset($_POST['params'][$routine['item_param_name'][$i]])) { |
873
|
|
|
$value = $_POST['params'][$routine['item_param_name'][$i]]; |
874
|
|
|
if (is_array($value)) { // is SET type |
875
|
|
|
$value = implode(',', $value); |
876
|
|
|
} |
877
|
|
|
|
878
|
|
|
if ( |
879
|
|
|
! empty($_POST['funcs'][$routine['item_param_name'][$i]]) |
880
|
|
|
&& in_array($_POST['funcs'][$routine['item_param_name'][$i]], $allFunctions, true) |
881
|
|
|
) { |
882
|
|
|
$queries[] = sprintf( |
883
|
|
|
'SET @p%d=%s(%s);', |
884
|
|
|
$i, |
885
|
|
|
$_POST['funcs'][$routine['item_param_name'][$i]], |
886
|
|
|
$this->dbi->quoteString($value), |
887
|
|
|
); |
888
|
|
|
} else { |
889
|
|
|
$queries[] = 'SET @p' . $i . '=' . $this->dbi->quoteString($value) . ';'; |
890
|
|
|
} |
891
|
|
|
} |
892
|
|
|
|
893
|
|
|
$args[] = '@p' . $i; |
894
|
|
|
|
895
|
|
|
if ($routine['item_type'] !== 'PROCEDURE') { |
896
|
|
|
continue; |
897
|
|
|
} |
898
|
|
|
|
899
|
|
|
if ($routine['item_param_dir'][$i] !== 'OUT' && $routine['item_param_dir'][$i] !== 'INOUT') { |
900
|
|
|
continue; |
901
|
|
|
} |
902
|
|
|
|
903
|
|
|
$outParams[] = '@p' . $i . ' AS ' . Util::backquote($routine['item_param_name'][$i]); |
904
|
|
|
} |
905
|
|
|
|
906
|
|
|
if ($routine['item_type'] === 'PROCEDURE') { |
907
|
|
|
$queries[] = sprintf( |
908
|
|
|
'CALL %s(%s);', |
909
|
|
|
Util::backquote($routine['item_name']), |
910
|
|
|
implode(', ', $args), |
911
|
|
|
); |
912
|
|
|
if ($outParams !== []) { |
913
|
|
|
$queries[] = 'SELECT ' . implode(', ', $outParams) . ';'; |
914
|
|
|
} |
915
|
|
|
} else { |
916
|
|
|
$queries[] = sprintf( |
917
|
|
|
'SELECT %s(%s) AS %s;', |
918
|
|
|
Util::backquote($routine['item_name']), |
919
|
|
|
implode(', ', $args), |
920
|
|
|
Util::backquote($routine['item_name']), |
921
|
|
|
); |
922
|
|
|
} |
923
|
|
|
|
924
|
|
|
return $queries; |
925
|
|
|
} |
926
|
|
|
|
927
|
|
|
/** |
928
|
|
|
* @param mixed[] $routine |
929
|
|
|
* |
930
|
|
|
* @psalm-return array{string, Message} |
931
|
|
|
*/ |
932
|
|
|
public function handleExecuteRoutine(array $routine): array |
933
|
|
|
{ |
934
|
|
|
$queries = $this->getQueriesFromRoutineForm($routine); |
935
|
|
|
|
936
|
|
|
$affected = 0; |
937
|
|
|
$resultHtmlTables = ''; |
938
|
|
|
$nbResultsetToDisplay = 0; |
939
|
|
|
|
940
|
|
|
foreach ($queries as $query) { |
941
|
|
|
$result = $this->dbi->tryQuery($query); |
942
|
|
|
|
943
|
|
|
// Generate output |
944
|
|
|
while ($result !== false) { |
945
|
|
|
if ($result->numRows() > 0) { |
946
|
|
|
$resultHtmlTables .= '<table class="table table-striped w-auto"><tr>'; |
947
|
|
|
foreach ($result->getFieldNames() as $field) { |
948
|
|
|
$resultHtmlTables .= '<th>'; |
949
|
|
|
$resultHtmlTables .= htmlspecialchars($field); |
950
|
|
|
$resultHtmlTables .= '</th>'; |
951
|
|
|
} |
952
|
|
|
|
953
|
|
|
$resultHtmlTables .= '</tr>'; |
954
|
|
|
|
955
|
|
|
foreach ($result as $row) { |
956
|
|
|
$resultHtmlTables .= '<tr>' . $this->browseRow($row) . '</tr>'; |
957
|
|
|
} |
958
|
|
|
|
959
|
|
|
$resultHtmlTables .= '</table>'; |
960
|
|
|
$nbResultsetToDisplay++; |
961
|
|
|
$affected = $result->numRows(); |
962
|
|
|
} |
963
|
|
|
|
964
|
|
|
$result = $this->dbi->nextResult(); |
965
|
|
|
} |
966
|
|
|
|
967
|
|
|
// We must check for an error after fetching the results because |
968
|
|
|
// either tryQuery might have produced an error or any of nextResult calls. |
969
|
|
|
if ($this->dbi->getError() !== '') { |
970
|
|
|
$message = Message::error( |
971
|
|
|
sprintf( |
972
|
|
|
__('The following query has failed: "%s"'), |
973
|
|
|
htmlspecialchars($query), |
974
|
|
|
) |
975
|
|
|
. '<br><br>' |
976
|
|
|
. __('MySQL said: ') . $this->dbi->getError(), |
977
|
|
|
); |
978
|
|
|
|
979
|
|
|
return ['', $message]; |
980
|
|
|
} |
981
|
|
|
} |
982
|
|
|
|
983
|
|
|
// Pass the SQL queries through the "pretty printer" |
984
|
|
|
$output = Generator::formatSql(implode("\n", $queries)); |
985
|
|
|
// Display results |
986
|
|
|
$output .= '<div class="card my-3"><div class="card-header">'; |
987
|
|
|
$output .= sprintf( |
988
|
|
|
__('Execution results of routine %s'), |
989
|
|
|
htmlspecialchars(Util::backquote($routine['item_name'])), |
990
|
|
|
); |
991
|
|
|
$output .= '</div><div class="card-body">'; |
992
|
|
|
$output .= $resultHtmlTables; |
993
|
|
|
$output .= '</div></div>'; |
994
|
|
|
|
995
|
|
|
$message = __('Your SQL query has been executed successfully.'); |
996
|
|
|
if ($routine['item_type'] === 'PROCEDURE') { |
997
|
|
|
$message .= '<br>'; |
998
|
|
|
|
999
|
|
|
// TODO : message need to be modified according to the |
1000
|
|
|
// output from the routine |
1001
|
|
|
$message .= sprintf( |
1002
|
|
|
_ngettext( |
1003
|
|
|
'%d row affected by the last statement inside the procedure.', |
1004
|
|
|
'%d rows affected by the last statement inside the procedure.', |
1005
|
|
|
(int) $affected, |
1006
|
|
|
), |
1007
|
|
|
$affected, |
1008
|
|
|
); |
1009
|
|
|
} |
1010
|
|
|
|
1011
|
|
|
if ($nbResultsetToDisplay === 0) { |
|
|
|
|
1012
|
|
|
$notice = __('MySQL returned an empty result set (i.e. zero rows).'); |
1013
|
|
|
$output .= Message::notice($notice)->getDisplay(); |
1014
|
|
|
} |
1015
|
|
|
|
1016
|
|
|
return [$output, Message::success($message)]; |
1017
|
|
|
} |
1018
|
|
|
|
1019
|
|
|
/** |
1020
|
|
|
* Browse row array |
1021
|
|
|
* |
1022
|
|
|
* @param (string|null)[] $row Columns |
1023
|
|
|
*/ |
1024
|
|
|
private function browseRow(array $row): string |
1025
|
|
|
{ |
1026
|
|
|
$output = ''; |
1027
|
|
|
foreach ($row as $value) { |
1028
|
|
|
if ($value === null) { |
1029
|
|
|
$value = '<i>NULL</i>'; |
1030
|
|
|
} else { |
1031
|
|
|
$value = htmlspecialchars($value); |
1032
|
|
|
} |
1033
|
|
|
|
1034
|
|
|
$output .= '<td>' . $value . '</td>'; |
1035
|
|
|
} |
1036
|
|
|
|
1037
|
|
|
return $output; |
1038
|
|
|
} |
1039
|
|
|
|
1040
|
|
|
/** |
1041
|
|
|
* Creates the HTML code that shows the routine execution dialog. |
1042
|
|
|
* |
1043
|
|
|
* @param mixed[] $routine Data for the routine returned by getDataFromName() |
1044
|
|
|
* |
1045
|
|
|
* @psalm-return array{mixed[], mixed[]} |
1046
|
|
|
*/ |
1047
|
|
|
public function getExecuteForm(array $routine): array |
1048
|
|
|
{ |
1049
|
|
|
// Escape special characters |
1050
|
|
|
$routine['item_name'] = htmlentities($routine['item_name'], ENT_QUOTES); |
1051
|
|
|
for ($i = 0; $i < $routine['item_num_params']; $i++) { |
1052
|
|
|
$routine['item_param_name'][$i] = htmlentities($routine['item_param_name'][$i], ENT_QUOTES); |
1053
|
|
|
} |
1054
|
|
|
|
1055
|
|
|
$noSupportTypes = Util::unsupportedDatatypes(); |
1056
|
|
|
|
1057
|
|
|
$params = []; |
1058
|
|
|
$params['no_support_types'] = $noSupportTypes; |
1059
|
|
|
|
1060
|
|
|
for ($i = 0; $i < $routine['item_num_params']; $i++) { |
1061
|
|
|
if ($routine['item_type'] === 'PROCEDURE' && $routine['item_param_dir'][$i] === 'OUT') { |
1062
|
|
|
continue; |
1063
|
|
|
} |
1064
|
|
|
|
1065
|
|
|
if (Config::getInstance()->settings['ShowFunctionFields']) { |
|
|
|
|
1066
|
|
|
if ( |
1067
|
|
|
stripos($routine['item_param_type'][$i], 'enum') !== false |
1068
|
|
|
|| stripos($routine['item_param_type'][$i], 'set') !== false |
1069
|
|
|
|| in_array( |
1070
|
|
|
mb_strtolower($routine['item_param_type'][$i]), |
1071
|
|
|
$noSupportTypes, |
1072
|
|
|
true, |
1073
|
|
|
) |
1074
|
|
|
) { |
1075
|
|
|
$params[$i]['generator'] = null; |
1076
|
|
|
} else { |
1077
|
|
|
$defaultFunction = Generator::getDefaultFunctionForField( |
1078
|
|
|
mb_strtolower($routine['item_param_type'][$i]), |
1079
|
|
|
false, |
1080
|
|
|
'', |
1081
|
|
|
'', |
1082
|
|
|
false, |
1083
|
|
|
'', |
1084
|
|
|
'', |
1085
|
|
|
false, |
1086
|
|
|
); |
1087
|
|
|
$params[$i]['generator'] = Generator::getFunctionsForField($defaultFunction, []); |
1088
|
|
|
} |
1089
|
|
|
} |
1090
|
|
|
|
1091
|
|
|
if ($routine['item_param_type'][$i] === 'DATETIME' || $routine['item_param_type'][$i] === 'TIMESTAMP') { |
1092
|
|
|
$params[$i]['class'] = 'datetimefield'; |
1093
|
|
|
} elseif ($routine['item_param_type'][$i] === 'DATE') { |
1094
|
|
|
$params[$i]['class'] = 'datefield'; |
1095
|
|
|
} |
1096
|
|
|
|
1097
|
|
|
if (in_array($routine['item_param_type'][$i], ['ENUM', 'SET'], true)) { |
1098
|
|
|
if ($routine['item_param_type'][$i] === 'ENUM') { |
1099
|
|
|
$params[$i]['input_type'] = 'radio'; |
1100
|
|
|
} else { |
1101
|
|
|
$params[$i]['input_type'] = 'checkbox'; |
1102
|
|
|
} |
1103
|
|
|
|
1104
|
|
|
foreach ($routine['item_param_length_arr'][$i] as $value) { |
1105
|
|
|
$value = htmlentities(Util::unQuote($value), ENT_QUOTES); |
1106
|
|
|
$params[$i]['htmlentities'][] = $value; |
1107
|
|
|
} |
1108
|
|
|
} elseif (in_array(mb_strtolower($routine['item_param_type'][$i]), $noSupportTypes, true)) { |
1109
|
|
|
$params[$i]['input_type'] = null; |
1110
|
|
|
} else { |
1111
|
|
|
$params[$i]['input_type'] = 'text'; |
1112
|
|
|
} |
1113
|
|
|
} |
1114
|
|
|
|
1115
|
|
|
return [$routine, $params]; |
1116
|
|
|
} |
1117
|
|
|
|
1118
|
|
|
/** |
1119
|
|
|
* Creates the contents for a row in the list of routines |
1120
|
|
|
* |
1121
|
|
|
* @param string $rowClass Additional class |
1122
|
|
|
* |
1123
|
|
|
* @return mixed[] |
1124
|
|
|
*/ |
1125
|
4 |
|
public function getRow(Routine $routine, string $rowClass = ''): array |
1126
|
|
|
{ |
1127
|
4 |
|
$sqlDrop = sprintf( |
1128
|
4 |
|
'DROP %s IF EXISTS %s', |
1129
|
4 |
|
$routine->type, |
1130
|
4 |
|
Util::backquote($routine->name), |
1131
|
4 |
|
); |
1132
|
|
|
|
1133
|
|
|
// this is for our purpose to decide whether to |
1134
|
|
|
// show the edit link or not, so we need the DEFINER for the routine |
1135
|
4 |
|
$where = 'ROUTINE_SCHEMA ' . Util::getCollateForIS() . '=' . $this->dbi->quoteString(Current::$database) |
1136
|
4 |
|
. ' AND SPECIFIC_NAME=' . $this->dbi->quoteString($routine->name) |
1137
|
4 |
|
. ' AND ROUTINE_TYPE=' . $this->dbi->quoteString($routine->type); |
1138
|
4 |
|
$query = 'SELECT `DEFINER` FROM INFORMATION_SCHEMA.ROUTINES WHERE ' . $where . ';'; |
1139
|
4 |
|
$routineDefiner = $this->dbi->fetchValue($query); |
1140
|
|
|
|
1141
|
4 |
|
$currentUser = $this->dbi->getCurrentUser(); |
1142
|
4 |
|
$currentUserIsRoutineDefiner = $currentUser === $routineDefiner; |
1143
|
|
|
|
1144
|
|
|
// Since editing a procedure involved dropping and recreating, check also for |
1145
|
|
|
// CREATE ROUTINE privilege to avoid lost procedures. |
1146
|
4 |
|
$hasCreateRoutine = Util::currentUserHasPrivilege('CREATE ROUTINE', Current::$database); |
1147
|
4 |
|
$hasEditPrivilege = ($hasCreateRoutine && $currentUserIsRoutineDefiner) |
1148
|
4 |
|
|| $this->dbi->isSuperUser(); |
1149
|
4 |
|
$hasExportPrivilege = ($hasCreateRoutine && $currentUserIsRoutineDefiner) |
1150
|
4 |
|
|| $this->dbi->isSuperUser(); |
1151
|
4 |
|
$hasExecutePrivilege = Util::currentUserHasPrivilege('EXECUTE', Current::$database) |
1152
|
4 |
|
|| $currentUserIsRoutineDefiner; |
1153
|
|
|
|
1154
|
|
|
// There is a problem with Util::currentUserHasPrivilege(): |
1155
|
|
|
// it does not detect all kinds of privileges, for example |
1156
|
|
|
// a direct privilege on a specific routine. So, at this point, |
1157
|
|
|
// we show the Execute link, hoping that the user has the correct rights. |
1158
|
|
|
// Also, information_schema might be hiding the ROUTINE_DEFINITION |
1159
|
|
|
// but a routine with no input parameters can be nonetheless executed. |
1160
|
|
|
|
1161
|
|
|
// Check if the routine has any input parameters. If it does, |
1162
|
|
|
// we will show a dialog to get values for these parameters, |
1163
|
|
|
// otherwise we can execute it directly. |
1164
|
|
|
|
1165
|
4 |
|
if ($routine->type === 'FUNCTION') { |
1166
|
4 |
|
$definition = self::getFunctionDefinition($this->dbi, Current::$database, $routine->name); |
1167
|
|
|
} else { |
1168
|
4 |
|
$definition = self::getProcedureDefinition($this->dbi, Current::$database, $routine->name); |
1169
|
|
|
} |
1170
|
|
|
|
1171
|
4 |
|
$executeAction = ''; |
1172
|
|
|
|
1173
|
4 |
|
if ($definition !== null) { |
1174
|
4 |
|
$parser = new Parser('DELIMITER $$' . "\n" . $definition); |
1175
|
|
|
|
1176
|
|
|
/** @var CreateStatement $stmt */ |
1177
|
4 |
|
$stmt = $parser->statements[0]; |
1178
|
|
|
|
1179
|
4 |
|
$params = RoutineUtils::getParameters($stmt); |
1180
|
|
|
|
1181
|
4 |
|
if ($hasExecutePrivilege) { |
1182
|
4 |
|
$executeAction = 'execute_routine'; |
1183
|
4 |
|
for ($i = 0; $i < $params['num']; $i++) { |
1184
|
4 |
|
if ($routine->type === 'PROCEDURE' && $params['dir'][$i] === 'OUT') { |
1185
|
|
|
continue; |
1186
|
|
|
} |
1187
|
|
|
|
1188
|
4 |
|
$executeAction = 'execute_dialog'; |
1189
|
4 |
|
break; |
1190
|
|
|
} |
1191
|
|
|
} |
1192
|
|
|
} |
1193
|
|
|
|
1194
|
4 |
|
return [ |
1195
|
4 |
|
'db' => Current::$database, |
1196
|
4 |
|
'table' => Current::$table, |
1197
|
4 |
|
'sql_drop' => $sqlDrop, |
1198
|
4 |
|
'routine' => $routine, |
1199
|
4 |
|
'row_class' => $rowClass, |
1200
|
4 |
|
'has_edit_privilege' => $hasEditPrivilege, |
1201
|
4 |
|
'has_export_privilege' => $hasExportPrivilege, |
1202
|
4 |
|
'has_execute_privilege' => $hasExecutePrivilege, |
1203
|
4 |
|
'execute_action' => $executeAction, |
1204
|
4 |
|
]; |
1205
|
|
|
} |
1206
|
|
|
|
1207
|
|
|
/** |
1208
|
|
|
* returns details about the PROCEDUREs or FUNCTIONs for a specific database |
1209
|
|
|
* or details about a specific routine |
1210
|
|
|
* |
1211
|
|
|
* @param string $db db name |
1212
|
|
|
* @param string|null $which PROCEDURE | FUNCTION or null for both |
1213
|
|
|
* @param string $name name of the routine (to fetch a specific routine) |
1214
|
|
|
* |
1215
|
|
|
* @return Routine[] |
1216
|
|
|
*/ |
1217
|
8 |
|
public static function getDetails( |
1218
|
|
|
DatabaseInterface $dbi, |
1219
|
|
|
string $db, |
1220
|
|
|
string|null $which = null, |
1221
|
|
|
string $name = '', |
1222
|
|
|
): array { |
1223
|
8 |
|
if (! Config::getInstance()->selectedServer['DisableIS']) { |
|
|
|
|
1224
|
|
|
$query = QueryGenerator::getInformationSchemaRoutinesRequest( |
1225
|
|
|
$dbi->quoteString($db), |
1226
|
|
|
in_array($which, ['FUNCTION', 'PROCEDURE'], true) ? $which : null, |
1227
|
|
|
$name === '' ? null : $dbi->quoteString($name), |
1228
|
|
|
); |
1229
|
|
|
$routines = $dbi->fetchResult($query); |
1230
|
|
|
} else { |
1231
|
8 |
|
$routines = []; |
1232
|
|
|
|
1233
|
8 |
|
if ($which === 'FUNCTION' || $which == null) { |
|
|
|
|
1234
|
8 |
|
$query = 'SHOW FUNCTION STATUS WHERE `Db` = ' . $dbi->quoteString($db); |
1235
|
8 |
|
if ($name !== '') { |
1236
|
|
|
$query .= ' AND `Name` = ' . $dbi->quoteString($name); |
1237
|
|
|
} |
1238
|
|
|
|
1239
|
8 |
|
$routines = $dbi->fetchResult($query); |
1240
|
|
|
} |
1241
|
|
|
|
1242
|
8 |
|
if ($which === 'PROCEDURE' || $which == null) { |
|
|
|
|
1243
|
8 |
|
$query = 'SHOW PROCEDURE STATUS WHERE `Db` = ' . $dbi->quoteString($db); |
1244
|
8 |
|
if ($name !== '') { |
1245
|
|
|
$query .= ' AND `Name` = ' . $dbi->quoteString($name); |
1246
|
|
|
} |
1247
|
|
|
|
1248
|
8 |
|
$routines = array_merge($routines, $dbi->fetchResult($query)); |
1249
|
|
|
} |
1250
|
|
|
} |
1251
|
|
|
|
1252
|
8 |
|
$ret = []; |
1253
|
|
|
/** @var array{Name:string, Type:string, DTD_IDENTIFIER:string|null} $routine */ |
1254
|
8 |
|
foreach ($routines as $routine) { |
1255
|
4 |
|
$ret[] = new Routine($routine['Name'], $routine['Type'], $routine['DTD_IDENTIFIER'] ?? ''); |
1256
|
|
|
} |
1257
|
|
|
|
1258
|
|
|
// Sort results by name |
1259
|
8 |
|
$name = array_column($ret, 'name'); |
1260
|
8 |
|
array_multisort($name, SORT_ASC, $ret); |
|
|
|
|
1261
|
|
|
|
1262
|
8 |
|
return $ret; |
1263
|
|
|
} |
1264
|
|
|
|
1265
|
4 |
|
public static function getFunctionDefinition(DatabaseInterface $dbi, string $db, string $name): string|null |
1266
|
|
|
{ |
1267
|
4 |
|
$result = $dbi->fetchValue( |
1268
|
4 |
|
'SHOW CREATE FUNCTION ' . Util::backquote($db) . '.' . Util::backquote($name), |
1269
|
4 |
|
'Create Function', |
1270
|
4 |
|
); |
1271
|
|
|
|
1272
|
4 |
|
return is_string($result) ? $result : null; |
1273
|
|
|
} |
1274
|
|
|
|
1275
|
4 |
|
public static function getProcedureDefinition(DatabaseInterface $dbi, string $db, string $name): string|null |
1276
|
|
|
{ |
1277
|
4 |
|
$result = $dbi->fetchValue( |
1278
|
4 |
|
'SHOW CREATE PROCEDURE ' . Util::backquote($db) . '.' . Util::backquote($name), |
1279
|
4 |
|
'Create Procedure', |
1280
|
4 |
|
); |
1281
|
|
|
|
1282
|
4 |
|
return is_string($result) ? $result : null; |
1283
|
|
|
} |
1284
|
|
|
|
1285
|
|
|
/** |
1286
|
|
|
* @return array<int, string> |
1287
|
|
|
* @psalm-return list<non-empty-string> |
1288
|
|
|
*/ |
1289
|
8 |
|
public static function getFunctionNames(DatabaseInterface $dbi, string $db): array |
1290
|
|
|
{ |
1291
|
|
|
/** @psalm-var list<array{Db: string, Name: string, Type: string}> $functions */ |
1292
|
8 |
|
$functions = $dbi->fetchResult('SHOW FUNCTION STATUS;'); |
1293
|
8 |
|
$names = []; |
1294
|
8 |
|
foreach ($functions as $function) { |
1295
|
8 |
|
if ($function['Db'] !== $db || $function['Type'] !== 'FUNCTION' || $function['Name'] === '') { |
1296
|
8 |
|
continue; |
1297
|
|
|
} |
1298
|
|
|
|
1299
|
4 |
|
$names[] = $function['Name']; |
1300
|
|
|
} |
1301
|
|
|
|
1302
|
8 |
|
return $names; |
1303
|
|
|
} |
1304
|
|
|
|
1305
|
|
|
/** |
1306
|
|
|
* @return array<int, string> |
1307
|
|
|
* @psalm-return list<non-empty-string> |
1308
|
|
|
*/ |
1309
|
8 |
|
public static function getProcedureNames(DatabaseInterface $dbi, string $db): array |
1310
|
|
|
{ |
1311
|
|
|
/** @psalm-var list<array{Db: string, Name: string, Type: string}> $procedures */ |
1312
|
8 |
|
$procedures = $dbi->fetchResult('SHOW PROCEDURE STATUS;'); |
1313
|
8 |
|
$names = []; |
1314
|
8 |
|
foreach ($procedures as $procedure) { |
1315
|
8 |
|
if ($procedure['Db'] !== $db || $procedure['Type'] !== 'PROCEDURE' || $procedure['Name'] === '') { |
1316
|
8 |
|
continue; |
1317
|
|
|
} |
1318
|
|
|
|
1319
|
4 |
|
$names[] = $procedure['Name']; |
1320
|
|
|
} |
1321
|
|
|
|
1322
|
8 |
|
return $names; |
1323
|
|
|
} |
1324
|
|
|
} |
1325
|
|
|
|