1 | <?php |
||
2 | /* vim: set expandtab sw=4 ts=4 sts=4: */ |
||
3 | /** |
||
4 | * Tracking changes on databases, tables and views |
||
5 | * |
||
6 | * @package PhpMyAdmin |
||
7 | */ |
||
8 | declare(strict_types=1); |
||
9 | |||
10 | namespace PhpMyAdmin; |
||
11 | |||
12 | use PhpMyAdmin\DatabaseInterface; |
||
13 | use PhpMyAdmin\Plugins; |
||
14 | use PhpMyAdmin\Plugins\Export\ExportSql; |
||
15 | use PhpMyAdmin\Relation; |
||
16 | use PhpMyAdmin\SqlParser\Parser; |
||
17 | use PhpMyAdmin\SqlParser\Statements\AlterStatement; |
||
18 | use PhpMyAdmin\SqlParser\Statements\CreateStatement; |
||
19 | use PhpMyAdmin\SqlParser\Statements\DeleteStatement; |
||
20 | use PhpMyAdmin\SqlParser\Statements\DropStatement; |
||
21 | use PhpMyAdmin\SqlParser\Statements\InsertStatement; |
||
22 | use PhpMyAdmin\SqlParser\Statements\RenameStatement; |
||
23 | use PhpMyAdmin\SqlParser\Statements\TruncateStatement; |
||
24 | use PhpMyAdmin\SqlParser\Statements\UpdateStatement; |
||
25 | use PhpMyAdmin\Util; |
||
26 | |||
27 | /** |
||
28 | * This class tracks changes on databases, tables and views. |
||
29 | * |
||
30 | * @package PhpMyAdmin |
||
31 | * |
||
32 | * @todo use stristr instead of strstr |
||
33 | */ |
||
34 | class Tracker |
||
35 | { |
||
36 | /** |
||
37 | * Whether tracking is ready. |
||
38 | */ |
||
39 | protected static $enabled = false; |
||
40 | |||
41 | /** |
||
42 | * Cache to avoid quering tracking status multiple times. |
||
43 | */ |
||
44 | protected static $_tracking_cache = []; |
||
45 | |||
46 | /** |
||
47 | * Actually enables tracking. This needs to be done after all |
||
48 | * underlaying code is initialized. |
||
49 | * |
||
50 | * @static |
||
51 | * |
||
52 | * @return void |
||
53 | */ |
||
54 | public static function enable() |
||
55 | { |
||
56 | self::$enabled = true; |
||
57 | } |
||
58 | |||
59 | /** |
||
60 | * Gets the on/off value of the Tracker module, starts initialization. |
||
61 | * |
||
62 | * @static |
||
63 | * |
||
64 | * @return boolean (true=on|false=off) |
||
65 | */ |
||
66 | public static function isActive() |
||
67 | { |
||
68 | if (! self::$enabled) { |
||
69 | return false; |
||
70 | } |
||
71 | /* We need to avoid attempt to track any queries |
||
72 | * from Relation::getRelationsParam |
||
73 | */ |
||
74 | self::$enabled = false; |
||
75 | $relation = new Relation($GLOBALS['dbi']); |
||
76 | $cfgRelation = $relation->getRelationsParam(); |
||
77 | /* Restore original state */ |
||
78 | self::$enabled = true; |
||
79 | if (! $cfgRelation['trackingwork']) { |
||
80 | return false; |
||
81 | } |
||
82 | |||
83 | $pma_table = self::_getTrackingTable(); |
||
84 | |||
85 | return $pma_table !== null; |
||
86 | } |
||
87 | |||
88 | /** |
||
89 | * Parses the name of a table from a SQL statement substring. |
||
90 | * |
||
91 | * @param string $string part of SQL statement |
||
92 | * |
||
93 | * @static |
||
94 | * |
||
95 | * @return string the name of table |
||
96 | */ |
||
97 | protected static function getTableName($string) |
||
98 | { |
||
99 | if (mb_strstr($string, '.')) { |
||
100 | $temp = explode('.', $string); |
||
101 | $tablename = $temp[1]; |
||
102 | } else { |
||
103 | $tablename = $string; |
||
104 | } |
||
105 | |||
106 | $str = explode("\n", $tablename); |
||
107 | $tablename = $str[0]; |
||
108 | |||
109 | $tablename = str_replace([';', '`'], '', $tablename); |
||
110 | $tablename = trim($tablename); |
||
111 | |||
112 | return $tablename; |
||
113 | } |
||
114 | |||
115 | |||
116 | /** |
||
117 | * Gets the tracking status of a table, is it active or deactive ? |
||
118 | * |
||
119 | * @param string $dbname name of database |
||
120 | * @param string $tablename name of table |
||
121 | * |
||
122 | * @static |
||
123 | * |
||
124 | * @return boolean true or false |
||
125 | */ |
||
126 | public static function isTracked($dbname, $tablename) |
||
127 | { |
||
128 | if (! self::$enabled) { |
||
129 | return false; |
||
130 | } |
||
131 | |||
132 | if (isset(self::$_tracking_cache[$dbname][$tablename])) { |
||
133 | return self::$_tracking_cache[$dbname][$tablename]; |
||
134 | } |
||
135 | /* We need to avoid attempt to track any queries |
||
136 | * from Relation::getRelationsParam |
||
137 | */ |
||
138 | self::$enabled = false; |
||
139 | $relation = new Relation($GLOBALS['dbi']); |
||
140 | $cfgRelation = $relation->getRelationsParam(); |
||
141 | /* Restore original state */ |
||
142 | self::$enabled = true; |
||
143 | if (! $cfgRelation['trackingwork']) { |
||
144 | return false; |
||
145 | } |
||
146 | |||
147 | $sql_query = " SELECT tracking_active FROM " . self::_getTrackingTable() . |
||
148 | " WHERE db_name = '" . $GLOBALS['dbi']->escapeString($dbname) . "' " . |
||
149 | " AND table_name = '" . $GLOBALS['dbi']->escapeString($tablename) . "' " . |
||
150 | " ORDER BY version DESC LIMIT 1"; |
||
151 | |||
152 | $result = $GLOBALS['dbi']->fetchValue($sql_query, 0, 0, DatabaseInterface::CONNECT_CONTROL) == 1; |
||
153 | |||
154 | self::$_tracking_cache[$dbname][$tablename] = $result; |
||
155 | |||
156 | return $result; |
||
157 | } |
||
158 | |||
159 | /** |
||
160 | * Returns the comment line for the log. |
||
161 | * |
||
162 | * @return string Comment, contains date and username |
||
163 | */ |
||
164 | public static function getLogComment() |
||
165 | { |
||
166 | $date = Util::date('Y-m-d H:i:s'); |
||
167 | $user = preg_replace('/\s+/', ' ', $GLOBALS['cfg']['Server']['user']); |
||
168 | |||
169 | return "# log " . $date . " " . $user . "\n"; |
||
170 | } |
||
171 | |||
172 | /** |
||
173 | * Creates tracking version of a table / view |
||
174 | * (in other words: create a job to track future changes on the table). |
||
175 | * |
||
176 | * @param string $dbname name of database |
||
177 | * @param string $tablename name of table |
||
178 | * @param string $version version |
||
179 | * @param string $tracking_set set of tracking statements |
||
180 | * @param bool $is_view if table is a view |
||
181 | * |
||
182 | * @static |
||
183 | * |
||
184 | * @return int result of version insertion |
||
185 | */ |
||
186 | public static function createVersion( |
||
187 | $dbname, |
||
188 | $tablename, |
||
189 | $version, |
||
190 | $tracking_set = '', |
||
191 | bool $is_view = false |
||
192 | ) { |
||
193 | global $sql_backquotes, $export_type; |
||
194 | |||
195 | $relation = new Relation($GLOBALS['dbi']); |
||
196 | |||
197 | if ($tracking_set == '') { |
||
198 | $tracking_set |
||
199 | = $GLOBALS['cfg']['Server']['tracking_default_statements']; |
||
200 | } |
||
201 | |||
202 | /** |
||
203 | * get Export SQL instance |
||
204 | * @var ExportSql $export_sql_plugin |
||
205 | */ |
||
206 | $export_sql_plugin = Plugins::getPlugin( |
||
207 | "export", |
||
208 | "sql", |
||
209 | 'libraries/classes/Plugins/Export/', |
||
210 | [ |
||
211 | 'export_type' => $export_type, |
||
212 | 'single_table' => false, |
||
213 | ] |
||
214 | ); |
||
215 | |||
216 | $sql_backquotes = true; |
||
217 | |||
218 | $date = Util::date('Y-m-d H:i:s'); |
||
219 | |||
220 | // Get data definition snapshot of table |
||
221 | |||
222 | $columns = $GLOBALS['dbi']->getColumns($dbname, $tablename, null, true); |
||
223 | // int indices to reduce size |
||
224 | $columns = array_values($columns); |
||
225 | // remove Privileges to reduce size |
||
226 | for ($i = 0, $nb = count($columns); $i < $nb; $i++) { |
||
227 | unset($columns[$i]['Privileges']); |
||
228 | } |
||
229 | |||
230 | $indexes = $GLOBALS['dbi']->getTableIndexes($dbname, $tablename); |
||
231 | |||
232 | $snapshot = [ |
||
233 | 'COLUMNS' => $columns, |
||
234 | 'INDEXES' => $indexes, |
||
235 | ]; |
||
236 | $snapshot = serialize($snapshot); |
||
237 | |||
238 | // Get DROP TABLE / DROP VIEW and CREATE TABLE SQL statements |
||
239 | $sql_backquotes = true; |
||
240 | |||
241 | $create_sql = ""; |
||
242 | |||
243 | if ($GLOBALS['cfg']['Server']['tracking_add_drop_table'] == true |
||
244 | && $is_view === false |
||
245 | ) { |
||
246 | $create_sql .= self::getLogComment() |
||
247 | . 'DROP TABLE IF EXISTS ' . Util::backquote($tablename) . ";\n"; |
||
248 | } |
||
249 | |||
250 | if ($GLOBALS['cfg']['Server']['tracking_add_drop_view'] == true |
||
251 | && $is_view === true |
||
252 | ) { |
||
253 | $create_sql .= self::getLogComment() |
||
254 | . 'DROP VIEW IF EXISTS ' . Util::backquote($tablename) . ";\n"; |
||
255 | } |
||
256 | |||
257 | $create_sql .= self::getLogComment() . |
||
258 | $export_sql_plugin->getTableDef($dbname, $tablename, "\n", ""); |
||
259 | |||
260 | // Save version |
||
261 | |||
262 | $sql_query = "/*NOTRACK*/\n" . |
||
263 | "INSERT INTO " . self::_getTrackingTable() . " (" . |
||
264 | "db_name, " . |
||
265 | "table_name, " . |
||
266 | "version, " . |
||
267 | "date_created, " . |
||
268 | "date_updated, " . |
||
269 | "schema_snapshot, " . |
||
270 | "schema_sql, " . |
||
271 | "data_sql, " . |
||
272 | "tracking " . |
||
273 | ") " . |
||
274 | "values ( |
||
275 | '" . $GLOBALS['dbi']->escapeString($dbname) . "', |
||
276 | '" . $GLOBALS['dbi']->escapeString($tablename) . "', |
||
277 | '" . $GLOBALS['dbi']->escapeString($version) . "', |
||
278 | '" . $GLOBALS['dbi']->escapeString($date) . "', |
||
279 | '" . $GLOBALS['dbi']->escapeString($date) . "', |
||
280 | '" . $GLOBALS['dbi']->escapeString($snapshot) . "', |
||
281 | '" . $GLOBALS['dbi']->escapeString($create_sql) . "', |
||
282 | '" . $GLOBALS['dbi']->escapeString("\n") . "', |
||
283 | '" . $GLOBALS['dbi']->escapeString($tracking_set) |
||
284 | . "' )"; |
||
285 | |||
286 | $result = $relation->queryAsControlUser($sql_query); |
||
287 | |||
288 | if ($result) { |
||
289 | // Deactivate previous version |
||
290 | self::deactivateTracking($dbname, $tablename, (int) $version - 1); |
||
291 | } |
||
292 | |||
293 | return $result; |
||
294 | } |
||
295 | |||
296 | |||
297 | /** |
||
298 | * Removes all tracking data for a table or a version of a table |
||
299 | * |
||
300 | * @param string $dbname name of database |
||
301 | * @param string $tablename name of table |
||
302 | * @param string $version version |
||
303 | * |
||
304 | * @static |
||
305 | * |
||
306 | * @return int result of version insertion |
||
307 | */ |
||
308 | public static function deleteTracking($dbname, $tablename, $version = '') |
||
309 | { |
||
310 | $relation = new Relation($GLOBALS['dbi']); |
||
311 | |||
312 | $sql_query = "/*NOTRACK*/\n" |
||
313 | . "DELETE FROM " . self::_getTrackingTable() |
||
314 | . " WHERE `db_name` = '" |
||
315 | . $GLOBALS['dbi']->escapeString($dbname) . "'" |
||
316 | . " AND `table_name` = '" |
||
317 | . $GLOBALS['dbi']->escapeString($tablename) . "'"; |
||
318 | if ($version) { |
||
319 | $sql_query .= " AND `version` = '" |
||
320 | . $GLOBALS['dbi']->escapeString($version) . "'"; |
||
321 | } |
||
322 | return $relation->queryAsControlUser($sql_query); |
||
323 | } |
||
324 | |||
325 | /** |
||
326 | * Creates tracking version of a database |
||
327 | * (in other words: create a job to track future changes on the database). |
||
328 | * |
||
329 | * @param string $dbname name of database |
||
330 | * @param string $version version |
||
331 | * @param string $query query |
||
332 | * @param string $tracking_set set of tracking statements |
||
333 | * |
||
334 | * @static |
||
335 | * |
||
336 | * @return int result of version insertion |
||
337 | */ |
||
338 | public static function createDatabaseVersion( |
||
339 | $dbname, |
||
340 | $version, |
||
341 | $query, |
||
342 | $tracking_set = 'CREATE DATABASE,ALTER DATABASE,DROP DATABASE' |
||
343 | ) { |
||
344 | $relation = new Relation($GLOBALS['dbi']); |
||
345 | |||
346 | $date = Util::date('Y-m-d H:i:s'); |
||
347 | |||
348 | if ($tracking_set == '') { |
||
349 | $tracking_set |
||
350 | = $GLOBALS['cfg']['Server']['tracking_default_statements']; |
||
351 | } |
||
352 | |||
353 | $create_sql = ""; |
||
354 | |||
355 | if ($GLOBALS['cfg']['Server']['tracking_add_drop_database'] == true) { |
||
356 | $create_sql .= self::getLogComment() |
||
357 | . 'DROP DATABASE IF EXISTS ' . Util::backquote($dbname) . ";\n"; |
||
358 | } |
||
359 | |||
360 | $create_sql .= self::getLogComment() . $query; |
||
361 | |||
362 | // Save version |
||
363 | $sql_query = "/*NOTRACK*/\n" . |
||
364 | "INSERT INTO " . self::_getTrackingTable() . " (" . |
||
365 | "db_name, " . |
||
366 | "table_name, " . |
||
367 | "version, " . |
||
368 | "date_created, " . |
||
369 | "date_updated, " . |
||
370 | "schema_snapshot, " . |
||
371 | "schema_sql, " . |
||
372 | "data_sql, " . |
||
373 | "tracking " . |
||
374 | ") " . |
||
375 | "values ( |
||
376 | '" . $GLOBALS['dbi']->escapeString($dbname) . "', |
||
377 | '" . $GLOBALS['dbi']->escapeString('') . "', |
||
378 | '" . $GLOBALS['dbi']->escapeString($version) . "', |
||
379 | '" . $GLOBALS['dbi']->escapeString($date) . "', |
||
380 | '" . $GLOBALS['dbi']->escapeString($date) . "', |
||
381 | '" . $GLOBALS['dbi']->escapeString('') . "', |
||
382 | '" . $GLOBALS['dbi']->escapeString($create_sql) . "', |
||
383 | '" . $GLOBALS['dbi']->escapeString("\n") . "', |
||
384 | '" . $GLOBALS['dbi']->escapeString($tracking_set) |
||
385 | . "' )"; |
||
386 | |||
387 | return $relation->queryAsControlUser($sql_query); |
||
388 | } |
||
389 | |||
390 | |||
391 | |||
392 | /** |
||
393 | * Changes tracking of a table. |
||
394 | * |
||
395 | * @param string $dbname name of database |
||
396 | * @param string $tablename name of table |
||
397 | * @param string $version version |
||
398 | * @param integer $new_state the new state of tracking |
||
399 | * |
||
400 | * @static |
||
401 | * |
||
402 | * @return int result of SQL query |
||
403 | */ |
||
404 | private static function _changeTracking( |
||
405 | $dbname, |
||
406 | $tablename, |
||
407 | $version, |
||
408 | $new_state |
||
409 | ) { |
||
410 | $relation = new Relation($GLOBALS['dbi']); |
||
411 | |||
412 | $sql_query = " UPDATE " . self::_getTrackingTable() . |
||
413 | " SET `tracking_active` = '" . $new_state . "' " . |
||
414 | " WHERE `db_name` = '" . $GLOBALS['dbi']->escapeString($dbname) . "' " . |
||
415 | " AND `table_name` = '" . $GLOBALS['dbi']->escapeString($tablename) . "' " . |
||
416 | " AND `version` = '" . $GLOBALS['dbi']->escapeString((string) $version) . "' "; |
||
417 | |||
418 | return $relation->queryAsControlUser($sql_query); |
||
419 | } |
||
420 | |||
421 | /** |
||
422 | * Changes tracking data of a table. |
||
423 | * |
||
424 | * @param string $dbname name of database |
||
425 | * @param string $tablename name of table |
||
426 | * @param string $version version |
||
427 | * @param string $type type of data(DDL || DML) |
||
428 | * @param string|array $new_data the new tracking data |
||
429 | * |
||
430 | * @static |
||
431 | * |
||
432 | * @return bool result of change |
||
433 | */ |
||
434 | public static function changeTrackingData( |
||
435 | $dbname, |
||
436 | $tablename, |
||
437 | $version, |
||
438 | $type, |
||
439 | $new_data |
||
440 | ) { |
||
441 | $relation = new Relation($GLOBALS['dbi']); |
||
442 | |||
443 | if ($type == 'DDL') { |
||
444 | $save_to = 'schema_sql'; |
||
445 | } elseif ($type == 'DML') { |
||
446 | $save_to = 'data_sql'; |
||
447 | } else { |
||
448 | return false; |
||
449 | } |
||
450 | $date = Util::date('Y-m-d H:i:s'); |
||
451 | |||
452 | $new_data_processed = ''; |
||
453 | if (is_array($new_data)) { |
||
454 | foreach ($new_data as $data) { |
||
455 | $new_data_processed .= '# log ' . $date . ' ' . $data['username'] |
||
456 | . $GLOBALS['dbi']->escapeString($data['statement']) . "\n"; |
||
457 | } |
||
458 | } else { |
||
459 | $new_data_processed = $new_data; |
||
460 | } |
||
461 | |||
462 | $sql_query = " UPDATE " . self::_getTrackingTable() . |
||
463 | " SET `" . $save_to . "` = '" . $new_data_processed . "' " . |
||
464 | " WHERE `db_name` = '" . $GLOBALS['dbi']->escapeString($dbname) . "' " . |
||
465 | " AND `table_name` = '" . $GLOBALS['dbi']->escapeString($tablename) . "' " . |
||
466 | " AND `version` = '" . $GLOBALS['dbi']->escapeString($version) . "' "; |
||
467 | |||
468 | $result = $relation->queryAsControlUser($sql_query); |
||
469 | |||
470 | return (bool) $result; |
||
471 | } |
||
472 | |||
473 | /** |
||
474 | * Activates tracking of a table. |
||
475 | * |
||
476 | * @param string $dbname name of database |
||
477 | * @param string $tablename name of table |
||
478 | * @param string $version version |
||
479 | * |
||
480 | * @static |
||
481 | * |
||
482 | * @return int result of SQL query |
||
483 | */ |
||
484 | public static function activateTracking($dbname, $tablename, $version) |
||
485 | { |
||
486 | return self::_changeTracking($dbname, $tablename, $version, 1); |
||
487 | } |
||
488 | |||
489 | |||
490 | /** |
||
491 | * Deactivates tracking of a table. |
||
492 | * |
||
493 | * @param string $dbname name of database |
||
494 | * @param string $tablename name of table |
||
495 | * @param string $version version |
||
496 | * |
||
497 | * @static |
||
498 | * |
||
499 | * @return int result of SQL query |
||
500 | */ |
||
501 | public static function deactivateTracking($dbname, $tablename, $version) |
||
502 | { |
||
503 | return self::_changeTracking($dbname, $tablename, $version, 0); |
||
504 | } |
||
505 | |||
506 | |||
507 | /** |
||
508 | * Gets the newest version of a tracking job |
||
509 | * (in other words: gets the HEAD version). |
||
510 | * |
||
511 | * @param string $dbname name of database |
||
512 | * @param string $tablename name of table |
||
513 | * @param string $statement tracked statement |
||
514 | * |
||
515 | * @static |
||
516 | * |
||
517 | * @return int (-1 if no version exists | > 0 if a version exists) |
||
518 | */ |
||
519 | public static function getVersion($dbname, $tablename, $statement = null) |
||
520 | { |
||
521 | $relation = new Relation($GLOBALS['dbi']); |
||
522 | |||
523 | $sql_query = " SELECT MAX(version) FROM " . self::_getTrackingTable() . |
||
524 | " WHERE `db_name` = '" . $GLOBALS['dbi']->escapeString($dbname) . "' " . |
||
525 | " AND `table_name` = '" . $GLOBALS['dbi']->escapeString($tablename) . "' "; |
||
526 | |||
527 | if ($statement != "") { |
||
528 | $sql_query .= " AND FIND_IN_SET('" |
||
529 | . $statement . "',tracking) > 0" ; |
||
530 | } |
||
531 | $row = $GLOBALS['dbi']->fetchArray($relation->queryAsControlUser($sql_query)); |
||
532 | return isset($row[0]) |
||
533 | ? $row[0] |
||
534 | : -1; |
||
535 | } |
||
536 | |||
537 | |||
538 | /** |
||
539 | * Gets the record of a tracking job. |
||
540 | * |
||
541 | * @param string $dbname name of database |
||
542 | * @param string $tablename name of table |
||
543 | * @param string $version version number |
||
544 | * |
||
545 | * @static |
||
546 | * |
||
547 | * @return mixed record DDM log, DDL log, structure snapshot, tracked |
||
548 | * statements. |
||
549 | */ |
||
550 | public static function getTrackedData($dbname, $tablename, $version) |
||
551 | { |
||
552 | $relation = new Relation($GLOBALS['dbi']); |
||
553 | |||
554 | $sql_query = " SELECT * FROM " . self::_getTrackingTable() . |
||
555 | " WHERE `db_name` = '" . $GLOBALS['dbi']->escapeString($dbname) . "' "; |
||
556 | if (! empty($tablename)) { |
||
557 | $sql_query .= " AND `table_name` = '" |
||
558 | . $GLOBALS['dbi']->escapeString($tablename) . "' "; |
||
559 | } |
||
560 | $sql_query .= " AND `version` = '" . $GLOBALS['dbi']->escapeString($version) |
||
561 | . "' ORDER BY `version` DESC LIMIT 1"; |
||
562 | |||
563 | $mixed = $GLOBALS['dbi']->fetchAssoc($relation->queryAsControlUser($sql_query)); |
||
564 | |||
565 | // Parse log |
||
566 | $log_schema_entries = explode('# log ', (string) $mixed['schema_sql']); |
||
567 | $log_data_entries = explode('# log ', (string) $mixed['data_sql']); |
||
568 | |||
569 | $ddl_date_from = $date = Util::date('Y-m-d H:i:s'); |
||
570 | |||
571 | $ddlog = []; |
||
572 | $first_iteration = true; |
||
573 | |||
574 | // Iterate tracked data definition statements |
||
575 | // For each log entry we want to get date, username and statement |
||
576 | foreach ($log_schema_entries as $log_entry) { |
||
577 | if (trim($log_entry) != '') { |
||
578 | $date = mb_substr($log_entry, 0, 19); |
||
579 | $username = mb_substr( |
||
580 | $log_entry, |
||
581 | 20, |
||
582 | mb_strpos($log_entry, "\n") - 20 |
||
583 | ); |
||
584 | if ($first_iteration) { |
||
585 | $ddl_date_from = $date; |
||
586 | $first_iteration = false; |
||
587 | } |
||
588 | $statement = rtrim(mb_strstr($log_entry, "\n")); |
||
589 | |||
590 | $ddlog[] = [ |
||
591 | 'date' => $date, |
||
592 | 'username' => $username, |
||
593 | 'statement' => $statement, |
||
594 | ]; |
||
595 | } |
||
596 | } |
||
597 | |||
598 | $date_from = $ddl_date_from; |
||
599 | $ddl_date_to = $date; |
||
600 | |||
601 | $dml_date_from = $date_from; |
||
602 | |||
603 | $dmlog = []; |
||
604 | $first_iteration = true; |
||
605 | |||
606 | // Iterate tracked data manipulation statements |
||
607 | // For each log entry we want to get date, username and statement |
||
608 | foreach ($log_data_entries as $log_entry) { |
||
609 | if (trim($log_entry) != '') { |
||
610 | $date = mb_substr($log_entry, 0, 19); |
||
611 | $username = mb_substr( |
||
612 | $log_entry, |
||
613 | 20, |
||
614 | mb_strpos($log_entry, "\n") - 20 |
||
615 | ); |
||
616 | if ($first_iteration) { |
||
617 | $dml_date_from = $date; |
||
618 | $first_iteration = false; |
||
619 | } |
||
620 | $statement = rtrim(mb_strstr($log_entry, "\n")); |
||
621 | |||
622 | $dmlog[] = [ |
||
623 | 'date' => $date, |
||
624 | 'username' => $username, |
||
625 | 'statement' => $statement, |
||
626 | ]; |
||
627 | } |
||
628 | } |
||
629 | |||
630 | $dml_date_to = $date; |
||
631 | |||
632 | // Define begin and end of date range for both logs |
||
633 | $data = []; |
||
634 | if (strtotime($ddl_date_from) <= strtotime($dml_date_from)) { |
||
635 | $data['date_from'] = $ddl_date_from; |
||
636 | } else { |
||
637 | $data['date_from'] = $dml_date_from; |
||
638 | } |
||
639 | if (strtotime($ddl_date_to) >= strtotime($dml_date_to)) { |
||
640 | $data['date_to'] = $ddl_date_to; |
||
641 | } else { |
||
642 | $data['date_to'] = $dml_date_to; |
||
643 | } |
||
644 | $data['ddlog'] = $ddlog; |
||
645 | $data['dmlog'] = $dmlog; |
||
646 | $data['tracking'] = $mixed['tracking']; |
||
647 | $data['schema_snapshot'] = $mixed['schema_snapshot']; |
||
648 | |||
649 | return $data; |
||
650 | } |
||
651 | |||
652 | |||
653 | /** |
||
654 | * Parses a query. Gets |
||
655 | * - statement identifier (UPDATE, ALTER TABLE, ...) |
||
656 | * - type of statement, is it part of DDL or DML ? |
||
657 | * - tablename |
||
658 | * |
||
659 | * @param string $query query |
||
660 | * |
||
661 | * @static |
||
662 | * @todo: using PMA SQL Parser when possible |
||
663 | * @todo: support multi-table/view drops |
||
664 | * |
||
665 | * @return mixed Array containing identifier, type and tablename. |
||
666 | * |
||
667 | */ |
||
668 | public static function parseQuery($query) |
||
669 | { |
||
670 | // Usage of PMA_SQP does not work here |
||
671 | // |
||
672 | // require_once("libraries/sqlparser.lib.php"); |
||
673 | // $parsed_sql = PMA_SQP_parse($query); |
||
674 | // $sql_info = PMA_SQP_analyze($parsed_sql); |
||
675 | |||
676 | $parser = new Parser($query); |
||
677 | |||
678 | $tokens = $parser->list->tokens; |
||
679 | |||
680 | // Parse USE statement, need it for SQL dump imports |
||
681 | if ($tokens[0]->value == 'USE') { |
||
682 | $GLOBALS['db'] = $tokens[2]->value; |
||
683 | } |
||
684 | |||
685 | $result = []; |
||
686 | |||
687 | if (! empty($parser->statements)) { |
||
688 | $statement = $parser->statements[0]; |
||
689 | $options = isset($statement->options) ? $statement->options->options : null; |
||
690 | |||
691 | /* |
||
692 | * DDL statements |
||
693 | */ |
||
694 | $result['type'] = 'DDL'; |
||
695 | |||
696 | // Parse CREATE statement |
||
697 | if ($statement instanceof CreateStatement) { |
||
698 | if (empty($options) || ! isset($options[6])) { |
||
699 | return $result; |
||
700 | } |
||
701 | |||
702 | if ($options[6] == 'VIEW' || $options[6] == 'TABLE') { |
||
703 | $result['identifier'] = 'CREATE ' . $options[6]; |
||
704 | $result['tablename'] = $statement->name->table ; |
||
705 | } elseif ($options[6] == 'DATABASE') { |
||
706 | $result['identifier'] = 'CREATE DATABASE' ; |
||
707 | $result['tablename'] = '' ; |
||
708 | |||
709 | // In case of CREATE DATABASE, table field of the CreateStatement is actually name of the database |
||
710 | $GLOBALS['db'] = $statement->name->table; |
||
711 | } elseif ($options[6] == 'INDEX' |
||
712 | || $options[6] == 'UNIQUE INDEX' |
||
713 | || $options[6] == 'FULLTEXT INDEX' |
||
714 | || $options[6] == 'SPATIAL INDEX' |
||
715 | ) { |
||
716 | $result['identifier'] = 'CREATE INDEX'; |
||
717 | |||
718 | // In case of CREATE INDEX, we have to get the table name from body of the statement |
||
719 | $result['tablename'] = $statement->body[3]->value == '.' ? $statement->body[4]->value |
||
720 | : $statement->body[2]->value ; |
||
721 | } |
||
722 | } elseif ($statement instanceof AlterStatement) { // Parse ALTER statement |
||
723 | if (empty($options) || ! isset($options[3])) { |
||
724 | return $result; |
||
725 | } |
||
726 | |||
727 | if ($options[3] == 'VIEW' || $options[3] == 'TABLE') { |
||
728 | $result['identifier'] = 'ALTER ' . $options[3] ; |
||
729 | $result['tablename'] = $statement->table->table ; |
||
730 | } elseif ($options[3] == 'DATABASE') { |
||
731 | $result['identifier'] = 'ALTER DATABASE' ; |
||
732 | $result['tablename'] = '' ; |
||
733 | |||
734 | $GLOBALS['db'] = $statement->table->table ; |
||
735 | } |
||
736 | } elseif ($statement instanceof DropStatement) { // Parse DROP statement |
||
737 | if (empty($options) || ! isset($options[1])) { |
||
738 | return $result; |
||
739 | } |
||
740 | |||
741 | if ($options[1] == 'VIEW' || $options[1] == 'TABLE') { |
||
742 | $result['identifier'] = 'DROP ' . $options[1] ; |
||
743 | $result['tablename'] = $statement->fields[0]->table; |
||
744 | } elseif ($options[1] == 'DATABASE') { |
||
745 | $result['identifier'] = 'DROP DATABASE' ; |
||
746 | $result['tablename'] = ''; |
||
747 | |||
748 | $GLOBALS['db'] = $statement->fields[0]->table; |
||
749 | } elseif ($options[1] == 'INDEX') { |
||
750 | $result['identifier'] = 'DROP INDEX' ; |
||
751 | $result['tablename'] = $statement->table->table; |
||
752 | } |
||
753 | } elseif ($statement instanceof RenameStatement) { // Parse RENAME statement |
||
754 | $result['identifier'] = 'RENAME TABLE'; |
||
755 | $result['tablename'] = $statement->renames[0]->old->table; |
||
756 | $result['tablename_after_rename'] = $statement->renames[0]->new->table; |
||
757 | } |
||
758 | |||
759 | if (isset($result['identifier'])) { |
||
760 | return $result ; |
||
761 | } |
||
762 | |||
763 | /* |
||
764 | * DML statements |
||
765 | */ |
||
766 | $result['type'] = 'DML'; |
||
767 | |||
768 | // Parse UPDATE statement |
||
769 | if ($statement instanceof UpdateStatement) { |
||
770 | $result['identifier'] = 'UPDATE'; |
||
771 | $result['tablename'] = $statement->tables[0]->table; |
||
772 | } |
||
773 | |||
774 | // Parse INSERT INTO statement |
||
775 | if ($statement instanceof InsertStatement) { |
||
776 | $result['identifier'] = 'INSERT'; |
||
777 | $result['tablename'] = $statement->into->dest->table; |
||
778 | } |
||
779 | |||
780 | // Parse DELETE statement |
||
781 | if ($statement instanceof DeleteStatement) { |
||
782 | $result['identifier'] = 'DELETE'; |
||
783 | $result['tablename'] = $statement->from[0]->table; |
||
784 | } |
||
785 | |||
786 | // Parse TRUNCATE statement |
||
787 | if ($statement instanceof TruncateStatement) { |
||
788 | $result['identifier'] = 'TRUNCATE' ; |
||
789 | $result['tablename'] = $statement->table->table; |
||
790 | } |
||
791 | } |
||
792 | |||
793 | return $result; |
||
794 | } |
||
795 | |||
796 | |||
797 | /** |
||
798 | * Analyzes a given SQL statement and saves tracking data. |
||
799 | * |
||
800 | * @param string $query a SQL query |
||
801 | * |
||
802 | * @static |
||
803 | * |
||
804 | * @return void |
||
805 | */ |
||
806 | public static function handleQuery($query) |
||
807 | { |
||
808 | $relation = new Relation($GLOBALS['dbi']); |
||
809 | |||
810 | // If query is marked as untouchable, leave |
||
811 | if (mb_strstr($query, "/*NOTRACK*/")) { |
||
812 | return; |
||
813 | } |
||
814 | |||
815 | if (! (substr($query, -1) == ';')) { |
||
816 | $query .= ";\n"; |
||
817 | } |
||
818 | // Get some information about query |
||
819 | $result = self::parseQuery($query); |
||
820 | |||
821 | // Get database name |
||
822 | $dbname = trim(isset($GLOBALS['db']) ? $GLOBALS['db'] : '', '`'); |
||
823 | // $dbname can be empty, for example when coming from Synchronize |
||
824 | // and this is a query for the remote server |
||
825 | if (empty($dbname)) { |
||
826 | return; |
||
827 | } |
||
828 | |||
829 | // If we found a valid statement |
||
830 | if (isset($result['identifier'])) { |
||
831 | if (! self::isTracked($dbname, $result['tablename'])) { |
||
832 | return; |
||
833 | } |
||
834 | |||
835 | $version = self::getVersion( |
||
836 | $dbname, |
||
837 | $result['tablename'], |
||
838 | $result['identifier'] |
||
839 | ); |
||
840 | |||
841 | // If version not exists and auto-creation is enabled |
||
842 | if ($GLOBALS['cfg']['Server']['tracking_version_auto_create'] == true |
||
843 | && $version == -1 |
||
844 | ) { |
||
845 | // Create the version |
||
846 | |||
847 | switch ($result['identifier']) { |
||
848 | case 'CREATE TABLE': |
||
849 | self::createVersion($dbname, $result['tablename'], '1'); |
||
850 | break; |
||
851 | case 'CREATE VIEW': |
||
852 | self::createVersion( |
||
853 | $dbname, |
||
854 | $result['tablename'], |
||
855 | '1', |
||
856 | '', |
||
857 | true |
||
858 | ); |
||
859 | break; |
||
860 | case 'CREATE DATABASE': |
||
861 | self::createDatabaseVersion($dbname, '1', $query); |
||
862 | break; |
||
863 | } // end switch |
||
864 | } |
||
865 | |||
866 | // If version exists |
||
867 | if ($version != -1) { |
||
868 | if ($result['type'] == 'DDL') { |
||
869 | $save_to = 'schema_sql'; |
||
870 | } elseif ($result['type'] == 'DML') { |
||
871 | $save_to = 'data_sql'; |
||
872 | } else { |
||
873 | $save_to = ''; |
||
874 | } |
||
875 | $date = Util::date('Y-m-d H:i:s'); |
||
876 | |||
877 | // Cut off `dbname`. from query |
||
878 | $query = preg_replace( |
||
879 | '/`' . preg_quote($dbname, '/') . '`\s?\./', |
||
880 | '', |
||
881 | $query |
||
882 | ); |
||
883 | |||
884 | // Add log information |
||
885 | $query = self::getLogComment() . $query ; |
||
886 | |||
887 | // Mark it as untouchable |
||
888 | $sql_query = " /*NOTRACK*/\n" |
||
889 | . " UPDATE " . self::_getTrackingTable() |
||
890 | . " SET " . Util::backquote($save_to) |
||
891 | . " = CONCAT( " . Util::backquote($save_to) . ",'\n" |
||
892 | . $GLOBALS['dbi']->escapeString($query) . "') ," |
||
893 | . " `date_updated` = '" . $date . "' "; |
||
894 | |||
895 | // If table was renamed we have to change |
||
896 | // the tablename attribute in pma_tracking too |
||
897 | if ($result['identifier'] == 'RENAME TABLE') { |
||
898 | $sql_query .= ', `table_name` = \'' |
||
899 | . $GLOBALS['dbi']->escapeString($result['tablename_after_rename']) |
||
900 | . '\' '; |
||
901 | } |
||
902 | |||
903 | // Save the tracking information only for |
||
904 | // 1. the database |
||
905 | // 2. the table / view |
||
906 | // 3. the statements |
||
907 | // we want to track |
||
908 | $sql_query .= |
||
909 | " WHERE FIND_IN_SET('" . $result['identifier'] . "',tracking) > 0" . |
||
910 | " AND `db_name` = '" . $GLOBALS['dbi']->escapeString($dbname) . "' " . |
||
911 | " AND `table_name` = '" |
||
912 | . $GLOBALS['dbi']->escapeString($result['tablename']) . "' " . |
||
913 | " AND `version` = '" . $GLOBALS['dbi']->escapeString($version) . "' "; |
||
914 | |||
915 | $relation->queryAsControlUser($sql_query); |
||
916 | } |
||
917 | } |
||
918 | } |
||
919 | |||
920 | /** |
||
921 | * Returns the tracking table |
||
922 | * |
||
923 | * @return string tracking table |
||
924 | */ |
||
925 | private static function _getTrackingTable() |
||
926 | { |
||
927 | $relation = new Relation($GLOBALS['dbi']); |
||
928 | $cfgRelation = $relation->getRelationsParam(); |
||
929 | return Util::backquote($cfgRelation['db']) |
||
930 | . '.' . Util::backquote($cfgRelation['tracking']); |
||
0 ignored issues
–
show
Bug
introduced
by
Loading history...
|
|||
931 | } |
||
932 | } |
||
933 |