Passed
Branch main (26cc78)
by Thierry
02:40
created

Admin::columnForeignKeys()   A

Complexity

Conditions 3
Paths 3

Size

Total Lines 9
Code Lines 5

Duplication

Lines 0
Ratio 0 %

Importance

Changes 0
Metric Value
cc 3
eloc 5
nc 3
nop 1
dl 0
loc 9
rs 10
c 0
b 0
f 0
1
<?php
2
3
namespace Lagdo\DbAdmin\Db;
4
5
use Lagdo\DbAdmin\Driver\UtilInterface;
6
use Lagdo\DbAdmin\Driver\DriverInterface;
7
8
use Exception;
9
10
use function trim;
11
use function substr;
12
use function strlen;
13
use function implode;
14
use function is_numeric;
15
use function preg_match;
16
use function preg_replace;
17
use function microtime;
18
use function strtoupper;
19
use function uniqid;
20
21
class Admin
22
{
23
    /**
24
     * @var Util
25
     */
26
    public $util;
27
28
    /**
29
     * @var DriverInterface
30
     */
31
    public $driver;
32
33
    /**
34
     * @var Translator
35
     */
36
    protected $trans;
37
38
    /**
39
     * The constructor
40
     *
41
     * @param Util $util
42
     * @param DriverInterface $driver
43
     * @param Translator $trans
44
     */
45
    public function __construct(Util $util, DriverInterface $driver, Translator $trans)
46
    {
47
        $this->util = $util;
48
        $this->driver = $driver;
49
        $this->trans = $trans;
50
    }
51
52
    /**
53
     * Create SQL condition from parsed query string
54
     *
55
     * @param array $where Parsed query string
56
     * @param array $fields
57
     *
58
     * @return string
59
     */
60
    public function where(array $where, array $fields = [])
61
    {
62
        $clauses = [];
63
        $wheres = $where["where"] ?? [];
64
        foreach ((array) $wheres as $key => $value) {
65
            $key = $this->util->bracketEscape($key, 1); // 1 - back
66
            $column = $this->util->escapeKey($key);
67
            $clauses[] = $column .
68
                // LIKE because of floats but slow with ints
69
                ($this->driver->jush() == "sql" && is_numeric($value) && preg_match('~\.~', $value) ? " LIKE " .
70
                $this->driver->quote($value) : ($this->driver->jush() == "mssql" ? " LIKE " .
71
                $this->driver->quote(preg_replace('~[_%[]~', '[\0]', $value)) : " = " . // LIKE because of text
72
                $this->driver->unconvertField($fields[$key], $this->driver->quote($value)))); //! enum and set
73
            if ($this->driver->jush() == "sql" &&
74
                preg_match('~char|text~', $fields[$key]->type) && preg_match("~[^ -@]~", $value)) {
75
                // not just [a-z] to catch non-ASCII characters
76
                $clauses[] = "$column = " . $this->driver->quote($value) . " COLLATE " . $this->driver->charset() . "_bin";
77
            }
78
        }
79
        $nulls = $where["null"] ?? [];
80
        foreach ((array) $nulls as $key) {
81
            $clauses[] = $this->util->escapeKey($key) . " IS NULL";
82
        }
83
        return implode(" AND ", $clauses);
84
    }
85
86
    /**
87
     * Apply SQL function
88
     *
89
     * @param string $function
90
     * @param string $column escaped column identifier
91
     *
92
     * @return string
93
     */
94
    public function applySqlFunction(string $function, string $column)
95
    {
96
        return ($function ? ($function == 'unixepoch' ? "DATETIME($column, '$function')" :
97
            ($function == 'count distinct' ? 'COUNT(DISTINCT ' : strtoupper("$function(")) . "$column)") : $column);
98
    }
99
100
    /**
101
     * Remove current user definer from SQL command
102
     *
103
     * @param string $query
104
     *
105
     * @return string
106
     */
107
    public function removeDefiner(string $query)
108
    {
109
        return preg_replace('~^([A-Z =]+) DEFINER=`' .
110
            preg_replace('~@(.*)~', '`@`(%|\1)', $this->driver->user()) .
111
            '`~', '\1', $query); //! proper escaping of user
112
    }
113
114
    /**
115
     * Get the users and hosts
116
     *
117
     * @param string $database  The database name
118
     *
119
     * @return array
120
     */
121
    public function getUsers($database)
122
    {
123
        // From privileges.inc.php
124
        $statement = $this->driver->query("SELECT User, Host FROM mysql." .
125
            ($database == "" ? "user" : "db WHERE " . $this->driver->quote($database) . " LIKE Db") .
126
            " ORDER BY Host, User");
127
        // $grant = $statement;
128
        if (!$statement) {
129
            // list logged user, information_schema.USER_PRIVILEGES lists just the current user too
130
            $statement = $this->driver->query("SELECT SUBSTRING_INDEX(CURRENT_USER, '@', 1) " .
131
                "AS User, SUBSTRING_INDEX(CURRENT_USER, '@', -1) AS Host");
132
        }
133
        $users = [];
134
        while ($row = $statement->fetchAssoc()) {
135
            $users[] = $row;
136
        }
137
        return $users;
138
    }
139
140
    /**
141
     * Get the grants of a user on a given host
142
     *
143
     * @param string $user      The user name
144
     * @param string $host      The host name
145
     * @param string $password  The user password
146
     *
147
     * @return array
148
     */
149
    public function getUserGrants(string $user, string $host, string &$password)
150
    {
151
        // From user.inc.php
152
        $grants = [];
153
154
        //! use information_schema for MySQL 5 - column names in column privileges are not escaped
155
        if (($statement = $this->driver->query("SHOW GRANTS FOR " .
156
            $this->driver->quote($user) . "@" . $this->driver->quote($host)))) {
157
            while ($row = $statement->fetchRow()) {
158
                if (\preg_match('~GRANT (.*) ON (.*) TO ~', $row[0], $match) &&
159
                    \preg_match_all('~ *([^(,]*[^ ,(])( *\([^)]+\))?~', $match[1], $matches, PREG_SET_ORDER)) { //! escape the part between ON and TO
160
                    foreach ($matches as $val) {
161
                        $match2 = $match[2] ?? '';
162
                        $val2 = $val[2] ?? '';
163
                        if ($val[1] != "USAGE") {
164
                            $grants["$match2$val2"][$val[1]] = true;
165
                        }
166
                        if (\preg_match('~ WITH GRANT OPTION~', $row[0])) { //! don't check inside strings and identifiers
167
                            $grants["$match2$val2"]["GRANT OPTION"] = true;
168
                        }
169
                    }
170
                }
171
                if (\preg_match("~ IDENTIFIED BY PASSWORD '([^']+)~", $row[0], $match)) {
172
                    $password  = $match[1];
173
                }
174
            }
175
        }
176
177
        return $grants;
178
    }
179
180
    /**
181
     * @param array $features
182
     * @param array $contexts
183
     *
184
     * @return void
185
     */
186
    private function makeFeatures(array &$features, array $contexts)
187
    {
188
        foreach ($contexts as $context) {
189
            // Don't take 'Grant option' privileges.
190
            if ($row['Privilege'] === 'Grant option') {
0 ignored issues
show
Comprehensibility Best Practice introduced by
The variable $row seems to be never defined.
Loading history...
191
                continue;
192
            }
193
            // Privileges of 'Server Admin' and 'File access on server' are merged
194
            if ($context === 'File access on server') {
195
                $context = 'Server Admin';
196
            }
197
            $privilege = $row['Privilege'];
198
            // Comment for this is 'No privileges - allow connect only'
199
            if ($context === 'Server Admin' && $privilege === 'Usage') {
200
                continue;
201
            }
202
            // MySQL bug #30305
203
            if ($context === 'Procedures' && $privilege === 'Create routine') {
204
                $context = 'Databases';
205
            }
206
            if (!isset($features[$context])) {
207
                $features[$context] = [];
208
            }
209
            $features[$context][$privilege] = $row['Comment'];
210
            if ($context === 'Tables' &&
211
                in_array($privilege, ['Select', 'Insert', 'Update', 'References'])) {
212
                $features['Columns'][$privilege] = $row['Comment'];
213
            }
214
        }
215
    }
216
217
    /**
218
     * Get the user privileges
219
     *
220
     * @param array $grants     The user grants
221
     *
222
     * @return array
223
     */
224
    public function getUserPrivileges(array $grants)
225
    {
226
        // From user.inc.php
227
        $features = [
228
            '' => [
229
                'All privileges' => '',
230
            ],
231
            'Columns' => [],
232
        ];
233
        $rows = $this->driver->rows('SHOW PRIVILEGES');
234
        foreach ($rows as $row) {
235
            $this->makeFeatures($features, \explode(',', $row['Context']));
236
        }
237
238
        foreach (array_keys($features['Tables']) as $privilege) {
239
            unset($features['Databases'][$privilege]);
240
        }
241
242
        $privileges = [];
243
        $contexts = [
244
            "" => "",
245
            "Server Admin" => $this->trans->lang('Server'),
246
            "Databases" => $this->trans->lang('Database'),
247
            "Tables" => $this->trans->lang('Table'),
248
            "Columns" => $this->trans->lang('Column'),
249
            "Procedures" => $this->trans->lang('Routine'),
250
        ];
251
        foreach ($contexts as $context => $desc) {
252
            foreach ($features[$context] as $privilege => $comment) {
253
                $detail = [$desc, $this->util->html($privilege)];
254
                // echo "<tr><td" . ($desc ? ">$desc<td" : " colspan='2'") .
255
                //     ' lang="en" title="' . $this->util->html($comment) . '">' . $this->util->html($privilege);
256
                $i = 0;
257
                foreach ($grants as $object => $grant) {
258
                    $name = "'grants[$i][" . $this->util->html(\strtoupper($privilege)) . "]'";
259
                    $value = $grant[\strtoupper($privilege)] ?? false;
260
                    if ($context == "Server Admin" && $object != (isset($grants["*.*"]) ? "*.*" : ".*")) {
261
                        $detail[] = '';
262
                    }
263
                    // elseif(isset($values["grant"]))
264
                    // {
265
                    //     $detail[] = "<select name=$name><option><option value='1'" .
266
                    //         ($value ? " selected" : "") . ">" . $this->trans->lang('Grant') .
267
                    //         "<option value='0'" . ($value == "0" ? " selected" : "") . ">" .
268
                    //         $this->trans->lang('Revoke') . "</select>";
269
                    // }
270
                    else {
271
                        $detail[] = "<input type='checkbox' name=$name" . ($value ? " checked />" : " />");
272
                    }
273
                    $i++;
274
                }
275
                $privileges[] = $detail;
276
            }
277
        }
278
279
        return $privileges;
280
    }
281
282
    /**
283
     * Query printed after execution in the message
284
     *
285
     * @param string $query Executed query
286
     * @param string $time Elapsed time
287
     *
288
     * @return string
289
     */
290
    private function messageQuery(string $query, string $time)
0 ignored issues
show
Unused Code introduced by
The parameter $time is not used and could be removed. ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-unused  annotation

290
    private function messageQuery(string $query, /** @scrutinizer ignore-unused */ string $time)

This check looks for parameters that have been defined for a function or method, but which are not used in the method body.

Loading history...
291
    {
292
        if (strlen($query) > 1e6) {
293
            // [\x80-\xFF] - valid UTF-8, \n - can end by one-line comment
294
            $query = preg_replace('~[\x80-\xFF]+$~', '', substr($query, 0, 1e6)) . "\n…";
295
        }
296
        return $query;
297
    }
298
299
    /**
300
     * Execute query
301
     *
302
     * @param string $query
303
     * @param bool $execute
304
     * @param bool $failed
305
     * @param string $time
306
     *
307
     * @return bool
308
     */
309
    private function executeQuery(string $query, bool $execute = true, bool $failed = false, string $time = '')
310
    {
311
        if ($execute) {
312
            $start = microtime(true);
313
            $failed = !$this->driver->query($query);
314
            $time = $this->trans->formatTime($start);
315
        }
316
        if ($failed) {
317
            $sql = '';
318
            if ($query) {
319
                $sql = $this->messageQuery($query, $time);
320
            }
321
            throw new Exception($this->driver->error() . $sql);
322
        }
323
        return true;
324
    }
325
326
    /**
327
     * Execute remembered queries
328
     *
329
     * @param bool $failed
330
     *
331
     * @return bool
332
     */
333
    private function executeSavedQuery(bool $failed)
334
    {
335
        list($queries, $time) = $this->driver->queries();
336
        return $this->executeQuery($queries, false, $failed, $time);
337
    }
338
339
    /**
340
     * Find out foreign keys for each column
341
     *
342
     * @param string $table
343
     *
344
     * @return array
345
     */
346
    public function columnForeignKeys(string $table)
347
    {
348
        $keys = [];
349
        foreach ($this->driver->foreignKeys($table) as $foreignKey) {
350
            foreach ($foreignKey->source as $val) {
351
                $keys[$val][] = $foreignKey;
352
            }
353
        }
354
        return $keys;
355
    }
356
357
    /**
358
     * Drop old object and create a new one
359
     *
360
     * @param string $drop          Drop old object query
361
     * @param string $create        Create new object query
362
     * @param string $dropCreated   Drop new object query
363
     * @param string $test          Create test object query
364
     * @param string $dropTest      Drop test object query
365
     * @param string $oldName
366
     * @param string $newName
367
     *
368
     * @return string
369
     */
370
    protected function dropAndCreate(string $drop, string $create, string $dropCreated,
371
        string $test, string $dropTest, string $oldName, string $newName)
372
    {
373
        if ($oldName == '' && $newName == '') {
374
            $this->executeQuery($drop);
375
            return 'dropped';
376
        }
377
        if ($oldName == '') {
378
            $this->executeQuery($create);
379
            return 'created';
380
        }
381
        if ($oldName != $newName) {
382
            $created = $this->driver->execute($create);
383
            $dropped = $this->driver->execute($drop);
384
            // $this->executeSavedQuery(!($created && $this->driver->execute($drop)));
385
            if (!$dropped && $created) {
386
                $this->driver->execute($dropCreated);
387
            }
388
            return 'altered';
389
        }
390
        $this->executeSavedQuery(!($this->driver->execute($test) &&
391
            $this->driver->execute($dropTest) &&
392
            $this->driver->execute($drop) && $this->driver->execute($create)));
393
        return 'altered';
394
    }
395
396
    /**
397
     * Drop old object and redirect
398
     *
399
     * @param string $drop          Drop old object query
400
     *
401
     * @return void
402
     */
403
    public function drop(string $drop)
404
    {
405
        $this->executeQuery($drop);
406
    }
407
408
    /**
409
     * Create a view
410
     *
411
     * @param array  $values    The view values
412
     *
413
     * @return bool
414
     */
415
    public function createView(array $values)
416
    {
417
        // From view.inc.php
418
        $name = trim($values['name']);
419
        $type = $values['materialized'] ? ' MATERIALIZED VIEW ' : ' VIEW ';
420
421
        $sql = ($this->driver->jush() === 'mssql' ? 'ALTER' : 'CREATE OR REPLACE') .
422
            $type . $this->driver->table($name) . " AS\n" . $values['select'];
423
        return $this->executeQuery($sql);
424
    }
425
426
    /**
427
     * Update a view
428
     *
429
     * @param string $view      The view name
430
     * @param array  $values    The view values
431
     *
432
     * @return string
433
     */
434
    public function updateView(string $view, array $values)
435
    {
436
        // From view.inc.php
437
        $origType = 'VIEW';
438
        if ($this->driver->jush() === 'pgsql') {
439
            $status = $this->driver->tableStatus($view);
440
            $origType = strtoupper($status->engine);
441
        }
442
443
        $name = trim($values['name']);
444
        $type = $values['materialized'] ? 'MATERIALIZED VIEW' : 'VIEW';
445
        $tempName = $name . '_adminer_' . uniqid();
446
447
        return $this->dropAndCreate("DROP $origType " . $this->driver->table($view),
448
            "CREATE $type " . $this->driver->table($name) . " AS\n" . $values['select'],
449
            "DROP $type " . $this->driver->table($name),
450
            "CREATE $type " . $this->driver->table($tempName) . " AS\n" . $values['select'],
451
            "DROP $type " . $this->driver->table($tempName), $view, $name);
452
    }
453
454
    /**
455
     * Drop a view
456
     *
457
     * @param string $view      The view name
458
     *
459
     * @return bool
460
     */
461
    public function dropView(string $view)
462
    {
463
        // From view.inc.php
464
        $origType = 'VIEW';
465
        if ($this->driver->jush() == 'pgsql') {
466
            $status = $this->driver->tableStatus($view);
467
            $origType = strtoupper($status->engine);
468
        }
469
470
        $sql = "DROP $origType " . $this->driver->table($view);
471
        return $this->executeQuery($sql);
472
    }
473
}
474