Tracker.php 33 KB

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