Relation.php 67 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746174717481749175017511752175317541755175617571758175917601761176217631764176517661767176817691770177117721773177417751776177717781779178017811782178317841785178617871788178917901791179217931794179517961797179817991800180118021803180418051806180718081809181018111812181318141815181618171818181918201821182218231824182518261827182818291830183118321833
  1. <?php
  2. declare(strict_types=1);
  3. namespace PhpMyAdmin\ConfigStorage;
  4. use PhpMyAdmin\ConfigStorage\Features\PdfFeature;
  5. use PhpMyAdmin\DatabaseInterface;
  6. use PhpMyAdmin\Dbal\DatabaseName;
  7. use PhpMyAdmin\Dbal\TableName;
  8. use PhpMyAdmin\InternalRelations;
  9. use PhpMyAdmin\SqlParser\Parser;
  10. use PhpMyAdmin\SqlParser\Statements\CreateStatement;
  11. use PhpMyAdmin\SqlParser\Utils\Table as TableUtils;
  12. use PhpMyAdmin\Table;
  13. use PhpMyAdmin\Util;
  14. use PhpMyAdmin\Version;
  15. use function __;
  16. use function array_keys;
  17. use function array_reverse;
  18. use function array_search;
  19. use function array_shift;
  20. use function asort;
  21. use function bin2hex;
  22. use function count;
  23. use function explode;
  24. use function file_get_contents;
  25. use function htmlspecialchars;
  26. use function implode;
  27. use function in_array;
  28. use function is_array;
  29. use function is_scalar;
  30. use function is_string;
  31. use function ksort;
  32. use function mb_check_encoding;
  33. use function mb_strlen;
  34. use function mb_strtolower;
  35. use function mb_strtoupper;
  36. use function mb_substr;
  37. use function natcasesort;
  38. use function preg_match;
  39. use function sprintf;
  40. use function str_contains;
  41. use function str_replace;
  42. use function strlen;
  43. use function trim;
  44. use function uksort;
  45. use function usort;
  46. use const SQL_DIR;
  47. /**
  48. * Set of functions used with the relation and PDF feature
  49. */
  50. class Relation
  51. {
  52. /** @var DatabaseInterface */
  53. public $dbi;
  54. /** @param DatabaseInterface $dbi */
  55. public function __construct($dbi)
  56. {
  57. $this->dbi = $dbi;
  58. }
  59. public function getRelationParameters(): RelationParameters
  60. {
  61. $server = $GLOBALS['server'];
  62. if (! isset($_SESSION['relation']) || ! is_array($_SESSION['relation'])) {
  63. $_SESSION['relation'] = [];
  64. }
  65. if (
  66. isset($_SESSION['relation'][$server]) && is_array($_SESSION['relation'][$server])
  67. && isset($_SESSION['relation'][$server]['version'])
  68. && $_SESSION['relation'][$server]['version'] === Version::VERSION
  69. ) {
  70. return RelationParameters::fromArray($_SESSION['relation'][$server]);
  71. }
  72. $relationParameters = RelationParameters::fromArray($this->checkRelationsParam());
  73. $_SESSION['relation'][$server] = $relationParameters->toArray();
  74. return $relationParameters;
  75. }
  76. /**
  77. * @param array<string, bool|string|null> $relationParams
  78. *
  79. * @return array<string, bool|string|null>
  80. */
  81. private function checkTableAccess(array $relationParams): array
  82. {
  83. if (isset($relationParams['relation'], $relationParams['table_info'])) {
  84. if ($this->canAccessStorageTable((string) $relationParams['table_info'])) {
  85. $relationParams['displaywork'] = true;
  86. }
  87. }
  88. if (isset($relationParams['table_coords'], $relationParams['pdf_pages'])) {
  89. if ($this->canAccessStorageTable((string) $relationParams['table_coords'])) {
  90. if ($this->canAccessStorageTable((string) $relationParams['pdf_pages'])) {
  91. $relationParams['pdfwork'] = true;
  92. }
  93. }
  94. }
  95. if (isset($relationParams['column_info'])) {
  96. if ($this->canAccessStorageTable((string) $relationParams['column_info'])) {
  97. $relationParams['commwork'] = true;
  98. // phpMyAdmin 4.3+
  99. // Check for input transformations upgrade.
  100. $relationParams['mimework'] = $this->tryUpgradeTransformations();
  101. }
  102. }
  103. if (isset($relationParams['users'], $relationParams['usergroups'])) {
  104. if ($this->canAccessStorageTable((string) $relationParams['users'])) {
  105. if ($this->canAccessStorageTable((string) $relationParams['usergroups'])) {
  106. $relationParams['menuswork'] = true;
  107. }
  108. }
  109. }
  110. $settings = [
  111. 'export_templates' => 'exporttemplateswork',
  112. 'designer_settings' => 'designersettingswork',
  113. 'central_columns' => 'centralcolumnswork',
  114. 'savedsearches' => 'savedsearcheswork',
  115. 'navigationhiding' => 'navwork',
  116. 'bookmark' => 'bookmarkwork',
  117. 'userconfig' => 'userconfigwork',
  118. 'tracking' => 'trackingwork',
  119. 'table_uiprefs' => 'uiprefswork',
  120. 'favorite' => 'favoritework',
  121. 'recent' => 'recentwork',
  122. 'history' => 'historywork',
  123. 'relation' => 'relwork',
  124. ];
  125. foreach ($settings as $setingName => $worksKey) {
  126. if (! isset($relationParams[$setingName])) {
  127. continue;
  128. }
  129. if (! $this->canAccessStorageTable((string) $relationParams[$setingName])) {
  130. continue;
  131. }
  132. $relationParams[$worksKey] = true;
  133. }
  134. return $relationParams;
  135. }
  136. /**
  137. * @param array<string, bool|string|null> $relationParams
  138. *
  139. * @return array<string, bool|string|null>|null
  140. */
  141. private function fillRelationParamsWithTableNames(array $relationParams): ?array
  142. {
  143. if ($this->arePmadbTablesAllDisabled()) {
  144. return null;
  145. }
  146. $tabQuery = 'SHOW TABLES FROM '
  147. . Util::backquote($GLOBALS['cfg']['Server']['pmadb']);
  148. $tableRes = $this->dbi->tryQueryAsControlUser($tabQuery);
  149. if ($tableRes === false) {
  150. return null;
  151. }
  152. while ($currTable = $tableRes->fetchRow()) {
  153. if ($currTable[0] == $GLOBALS['cfg']['Server']['bookmarktable']) {
  154. $relationParams['bookmark'] = (string) $currTable[0];
  155. } elseif ($currTable[0] == $GLOBALS['cfg']['Server']['relation']) {
  156. $relationParams['relation'] = (string) $currTable[0];
  157. } elseif ($currTable[0] == $GLOBALS['cfg']['Server']['table_info']) {
  158. $relationParams['table_info'] = (string) $currTable[0];
  159. } elseif ($currTable[0] == $GLOBALS['cfg']['Server']['table_coords']) {
  160. $relationParams['table_coords'] = (string) $currTable[0];
  161. } elseif ($currTable[0] == $GLOBALS['cfg']['Server']['column_info']) {
  162. $relationParams['column_info'] = (string) $currTable[0];
  163. } elseif ($currTable[0] == $GLOBALS['cfg']['Server']['pdf_pages']) {
  164. $relationParams['pdf_pages'] = (string) $currTable[0];
  165. } elseif ($currTable[0] == $GLOBALS['cfg']['Server']['history']) {
  166. $relationParams['history'] = (string) $currTable[0];
  167. } elseif ($currTable[0] == $GLOBALS['cfg']['Server']['recent']) {
  168. $relationParams['recent'] = (string) $currTable[0];
  169. } elseif ($currTable[0] == $GLOBALS['cfg']['Server']['favorite']) {
  170. $relationParams['favorite'] = (string) $currTable[0];
  171. } elseif ($currTable[0] == $GLOBALS['cfg']['Server']['table_uiprefs']) {
  172. $relationParams['table_uiprefs'] = (string) $currTable[0];
  173. } elseif ($currTable[0] == $GLOBALS['cfg']['Server']['tracking']) {
  174. $relationParams['tracking'] = (string) $currTable[0];
  175. } elseif ($currTable[0] == $GLOBALS['cfg']['Server']['userconfig']) {
  176. $relationParams['userconfig'] = (string) $currTable[0];
  177. } elseif ($currTable[0] == $GLOBALS['cfg']['Server']['users']) {
  178. $relationParams['users'] = (string) $currTable[0];
  179. } elseif ($currTable[0] == $GLOBALS['cfg']['Server']['usergroups']) {
  180. $relationParams['usergroups'] = (string) $currTable[0];
  181. } elseif ($currTable[0] == $GLOBALS['cfg']['Server']['navigationhiding']) {
  182. $relationParams['navigationhiding'] = (string) $currTable[0];
  183. } elseif ($currTable[0] == $GLOBALS['cfg']['Server']['savedsearches']) {
  184. $relationParams['savedsearches'] = (string) $currTable[0];
  185. } elseif ($currTable[0] == $GLOBALS['cfg']['Server']['central_columns']) {
  186. $relationParams['central_columns'] = (string) $currTable[0];
  187. } elseif ($currTable[0] == $GLOBALS['cfg']['Server']['designer_settings']) {
  188. $relationParams['designer_settings'] = (string) $currTable[0];
  189. } elseif ($currTable[0] == $GLOBALS['cfg']['Server']['export_templates']) {
  190. $relationParams['export_templates'] = (string) $currTable[0];
  191. }
  192. }
  193. return $relationParams;
  194. }
  195. /**
  196. * Defines the relation parameters for the current user
  197. * just a copy of the functions used for relations ;-)
  198. * but added some stuff to check what will work
  199. *
  200. * @return array<string, bool|string|null> the relation parameters for the current user
  201. */
  202. private function checkRelationsParam(): array
  203. {
  204. $relationParams = [];
  205. $relationParams['version'] = Version::VERSION;
  206. $workToTable = [
  207. 'relwork' => 'relation',
  208. 'displaywork' => [
  209. 'relation',
  210. 'table_info',
  211. ],
  212. 'bookmarkwork' => 'bookmarktable',
  213. 'pdfwork' => [
  214. 'table_coords',
  215. 'pdf_pages',
  216. ],
  217. 'commwork' => 'column_info',
  218. 'mimework' => 'column_info',
  219. 'historywork' => 'history',
  220. 'recentwork' => 'recent',
  221. 'favoritework' => 'favorite',
  222. 'uiprefswork' => 'table_uiprefs',
  223. 'trackingwork' => 'tracking',
  224. 'userconfigwork' => 'userconfig',
  225. 'menuswork' => [
  226. 'users',
  227. 'usergroups',
  228. ],
  229. 'navwork' => 'navigationhiding',
  230. 'savedsearcheswork' => 'savedsearches',
  231. 'centralcolumnswork' => 'central_columns',
  232. 'designersettingswork' => 'designer_settings',
  233. 'exporttemplateswork' => 'export_templates',
  234. ];
  235. foreach (array_keys($workToTable) as $work) {
  236. $relationParams[$work] = false;
  237. }
  238. $relationParams['allworks'] = false;
  239. $relationParams['user'] = null;
  240. $relationParams['db'] = null;
  241. if (
  242. $GLOBALS['server'] == 0
  243. || empty($GLOBALS['cfg']['Server']['pmadb'])
  244. || ! $this->dbi->selectDb($GLOBALS['cfg']['Server']['pmadb'], DatabaseInterface::CONNECT_CONTROL)
  245. ) {
  246. // No server selected -> no bookmark table
  247. // we return the array with the falses in it,
  248. // to avoid some 'Uninitialized string offset' errors later
  249. $GLOBALS['cfg']['Server']['pmadb'] = false;
  250. return $relationParams;
  251. }
  252. $relationParams['user'] = $GLOBALS['cfg']['Server']['user'];
  253. $relationParams['db'] = $GLOBALS['cfg']['Server']['pmadb'];
  254. // Now I just check if all tables that i need are present so I can for
  255. // example enable relations but not pdf...
  256. // I was thinking of checking if they have all required columns but I
  257. // fear it might be too slow
  258. $relationParamsFilled = $this->fillRelationParamsWithTableNames($relationParams);
  259. if ($relationParamsFilled === null) {
  260. // query failed ... ?
  261. //$GLOBALS['cfg']['Server']['pmadb'] = false;
  262. return $relationParams;
  263. }
  264. // Filling did success
  265. $relationParams = $relationParamsFilled;
  266. $relationParams = $this->checkTableAccess($relationParams);
  267. $allWorks = true;
  268. foreach ($workToTable as $work => $table) {
  269. if ($relationParams[$work]) {
  270. continue;
  271. }
  272. if (is_string($table)) {
  273. if (isset($GLOBALS['cfg']['Server'][$table]) && $GLOBALS['cfg']['Server'][$table] !== false) {
  274. $allWorks = false;
  275. break;
  276. }
  277. } else {
  278. $oneNull = false;
  279. foreach ($table as $t) {
  280. if (isset($GLOBALS['cfg']['Server'][$t]) && $GLOBALS['cfg']['Server'][$t] === false) {
  281. $oneNull = true;
  282. break;
  283. }
  284. }
  285. if (! $oneNull) {
  286. $allWorks = false;
  287. break;
  288. }
  289. }
  290. }
  291. $relationParams['allworks'] = $allWorks;
  292. return $relationParams;
  293. }
  294. /**
  295. * Check if the table is accessible
  296. *
  297. * @param string $tableDbName The table or table.db
  298. */
  299. public function canAccessStorageTable(string $tableDbName): bool
  300. {
  301. $result = $this->dbi->tryQueryAsControlUser('SELECT NULL FROM ' . Util::backquote($tableDbName) . ' LIMIT 0');
  302. return $result !== false;
  303. }
  304. /**
  305. * Check whether column_info table input transformation
  306. * upgrade is required and try to upgrade silently
  307. */
  308. public function tryUpgradeTransformations(): bool
  309. {
  310. // From 4.3, new input oriented transformation feature was introduced.
  311. // Check whether column_info table has input transformation columns
  312. $new_cols = [
  313. 'input_transformation',
  314. 'input_transformation_options',
  315. ];
  316. $query = 'SHOW COLUMNS FROM '
  317. . Util::backquote($GLOBALS['cfg']['Server']['pmadb'])
  318. . '.' . Util::backquote($GLOBALS['cfg']['Server']['column_info'])
  319. . ' WHERE Field IN (\'' . implode('\', \'', $new_cols) . '\')';
  320. $result = $this->dbi->tryQueryAsControlUser($query);
  321. if ($result) {
  322. $rows = $result->numRows();
  323. unset($result);
  324. // input transformations are present
  325. // no need to upgrade
  326. if ($rows === 2) {
  327. return true;
  328. // try silent upgrade without disturbing the user
  329. }
  330. // read upgrade query file
  331. $query = @file_get_contents(SQL_DIR . 'upgrade_column_info_4_3_0+.sql');
  332. // replace database name from query to with set in config.inc.php
  333. // replace pma__column_info table name from query
  334. // to with set in config.inc.php
  335. $query = str_replace(
  336. [
  337. '`phpmyadmin`',
  338. '`pma__column_info`',
  339. ],
  340. [
  341. Util::backquote($GLOBALS['cfg']['Server']['pmadb']),
  342. Util::backquote($GLOBALS['cfg']['Server']['column_info']),
  343. ],
  344. (string) $query
  345. );
  346. $this->dbi->tryMultiQuery($query, DatabaseInterface::CONNECT_CONTROL);
  347. // skips result sets of query as we are not interested in it
  348. do {
  349. $hasResult = (
  350. $this->dbi->moreResults(DatabaseInterface::CONNECT_CONTROL)
  351. && $this->dbi->nextResult(DatabaseInterface::CONNECT_CONTROL)
  352. );
  353. } while ($hasResult);
  354. $error = $this->dbi->getError(DatabaseInterface::CONNECT_CONTROL);
  355. // return true if no error exists otherwise false
  356. return empty($error);
  357. }
  358. // some failure, either in upgrading or something else
  359. // make some noise, time to wake up user.
  360. return false;
  361. }
  362. /**
  363. * Gets all Relations to foreign tables for a given table or
  364. * optionally a given column in a table
  365. *
  366. * @param string $db the name of the db to check for
  367. * @param string $table the name of the table to check for
  368. * @param string $column the name of the column to check for
  369. * @param string $source the source for foreign key information
  370. *
  371. * @return array db,table,column
  372. */
  373. public function getForeigners($db, $table, $column = '', $source = 'both')
  374. {
  375. $relationFeature = $this->getRelationParameters()->relationFeature;
  376. $foreign = [];
  377. if ($relationFeature !== null && ($source === 'both' || $source === 'internal')) {
  378. $rel_query = 'SELECT `master_field`, `foreign_db`, '
  379. . '`foreign_table`, `foreign_field`'
  380. . ' FROM ' . Util::backquote($relationFeature->database)
  381. . '.' . Util::backquote($relationFeature->relation)
  382. . ' WHERE `master_db` = \'' . $this->dbi->escapeString($db) . '\''
  383. . ' AND `master_table` = \'' . $this->dbi->escapeString($table) . '\'';
  384. if (strlen($column) > 0) {
  385. $rel_query .= ' AND `master_field` = '
  386. . '\'' . $this->dbi->escapeString($column) . '\'';
  387. }
  388. $foreign = $this->dbi->fetchResult($rel_query, 'master_field', null, DatabaseInterface::CONNECT_CONTROL);
  389. }
  390. if (($source === 'both' || $source === 'foreign') && strlen($table) > 0) {
  391. $tableObj = new Table($table, $db);
  392. $show_create_table = $tableObj->showCreate();
  393. if ($show_create_table !== '') {
  394. $parser = new Parser($show_create_table);
  395. $stmt = $parser->statements[0];
  396. $foreign['foreign_keys_data'] = [];
  397. if ($stmt instanceof CreateStatement) {
  398. $foreign['foreign_keys_data'] = TableUtils::getForeignKeys($stmt);
  399. }
  400. }
  401. }
  402. /**
  403. * Emulating relations for some information_schema tables
  404. */
  405. $isInformationSchema = mb_strtolower($db) === 'information_schema';
  406. $isMysql = mb_strtolower($db) === 'mysql';
  407. if (($isInformationSchema || $isMysql) && ($source === 'internal' || $source === 'both')) {
  408. if ($isInformationSchema) {
  409. $internalRelations = InternalRelations::getInformationSchema();
  410. } else {
  411. $internalRelations = InternalRelations::getMySql();
  412. }
  413. if (isset($internalRelations[$table])) {
  414. foreach ($internalRelations[$table] as $field => $relations) {
  415. if (
  416. (strlen($column) !== 0 && $column != $field)
  417. || (isset($foreign[$field])
  418. && strlen($foreign[$field]) !== 0)
  419. ) {
  420. continue;
  421. }
  422. $foreign[$field] = $relations;
  423. }
  424. }
  425. }
  426. return $foreign;
  427. }
  428. /**
  429. * Gets the display field of a table
  430. *
  431. * @param string $db the name of the db to check for
  432. * @param string $table the name of the table to check for
  433. *
  434. * @return string|false field name or false
  435. */
  436. public function getDisplayField($db, $table)
  437. {
  438. $displayFeature = $this->getRelationParameters()->displayFeature;
  439. /**
  440. * Try to fetch the display field from DB.
  441. */
  442. if ($displayFeature !== null) {
  443. $disp_query = 'SELECT `display_field`'
  444. . ' FROM ' . Util::backquote($displayFeature->database)
  445. . '.' . Util::backquote($displayFeature->tableInfo)
  446. . ' WHERE `db_name` = \'' . $this->dbi->escapeString((string) $db) . '\''
  447. . ' AND `table_name` = \'' . $this->dbi->escapeString((string) $table) . '\'';
  448. $row = $this->dbi->fetchSingleRow(
  449. $disp_query,
  450. DatabaseInterface::FETCH_ASSOC,
  451. DatabaseInterface::CONNECT_CONTROL
  452. );
  453. if (isset($row['display_field'])) {
  454. return $row['display_field'];
  455. }
  456. }
  457. /**
  458. * Emulating the display field for some information_schema tables.
  459. */
  460. if ($db === 'information_schema') {
  461. switch ($table) {
  462. case 'CHARACTER_SETS':
  463. return 'DESCRIPTION';
  464. case 'TABLES':
  465. return 'TABLE_COMMENT';
  466. }
  467. }
  468. /**
  469. * Pick first char field
  470. */
  471. $columns = $this->dbi->getColumnsFull($db, $table);
  472. foreach ($columns as $column) {
  473. if ($this->dbi->types->getTypeClass($column['DATA_TYPE']) === 'CHAR') {
  474. return $column['COLUMN_NAME'];
  475. }
  476. }
  477. return false;
  478. }
  479. /**
  480. * Gets the comments for all columns of a table or the db itself
  481. *
  482. * @param string $db the name of the db to check for
  483. * @param string $table the name of the table to check for
  484. *
  485. * @return array [column_name] = comment
  486. */
  487. public function getComments($db, $table = ''): array
  488. {
  489. if ($table === '') {
  490. return [$this->getDbComment($db)];
  491. }
  492. $comments = [];
  493. // MySQL native column comments
  494. $columns = $this->dbi->getColumns($db, $table, true);
  495. foreach ($columns as $column) {
  496. if (empty($column['Comment'])) {
  497. continue;
  498. }
  499. $comments[$column['Field']] = $column['Comment'];
  500. }
  501. return $comments;
  502. }
  503. /**
  504. * Gets the comment for a db
  505. *
  506. * @param string $db the name of the db to check for
  507. */
  508. public function getDbComment(string $db): string
  509. {
  510. $columnCommentsFeature = $this->getRelationParameters()->columnCommentsFeature;
  511. if ($columnCommentsFeature !== null) {
  512. // pmadb internal db comment
  513. $com_qry = 'SELECT `comment`'
  514. . ' FROM ' . Util::backquote($columnCommentsFeature->database)
  515. . '.' . Util::backquote($columnCommentsFeature->columnInfo)
  516. . ' WHERE db_name = \'' . $this->dbi->escapeString($db) . '\''
  517. . ' AND table_name = \'\''
  518. . ' AND column_name = \'(db_comment)\'';
  519. $com_rs = $this->dbi->tryQueryAsControlUser($com_qry);
  520. if ($com_rs && $com_rs->numRows() > 0) {
  521. $row = $com_rs->fetchAssoc();
  522. return (string) $row['comment'];
  523. }
  524. }
  525. return '';
  526. }
  527. /**
  528. * Gets the comment for a db
  529. *
  530. * @return array comments
  531. */
  532. public function getDbComments()
  533. {
  534. $columnCommentsFeature = $this->getRelationParameters()->columnCommentsFeature;
  535. if ($columnCommentsFeature !== null) {
  536. // pmadb internal db comment
  537. $com_qry = 'SELECT `db_name`, `comment`'
  538. . ' FROM ' . Util::backquote($columnCommentsFeature->database)
  539. . '.' . Util::backquote($columnCommentsFeature->columnInfo)
  540. . ' WHERE `column_name` = \'(db_comment)\'';
  541. $com_rs = $this->dbi->tryQueryAsControlUser($com_qry);
  542. if ($com_rs && $com_rs->numRows() > 0) {
  543. return $com_rs->fetchAllKeyPair();
  544. }
  545. }
  546. return [];
  547. }
  548. /**
  549. * Set a database comment to a certain value.
  550. *
  551. * @param string $db the name of the db
  552. * @param string $comment the value of the column
  553. */
  554. public function setDbComment($db, $comment = ''): bool
  555. {
  556. $columnCommentsFeature = $this->getRelationParameters()->columnCommentsFeature;
  557. if ($columnCommentsFeature === null) {
  558. return false;
  559. }
  560. if (strlen($comment) > 0) {
  561. $upd_query = 'INSERT INTO '
  562. . Util::backquote($columnCommentsFeature->database) . '.'
  563. . Util::backquote($columnCommentsFeature->columnInfo)
  564. . ' (`db_name`, `table_name`, `column_name`, `comment`)'
  565. . ' VALUES (\''
  566. . $this->dbi->escapeString($db)
  567. . "', '', '(db_comment)', '"
  568. . $this->dbi->escapeString($comment)
  569. . "') "
  570. . ' ON DUPLICATE KEY UPDATE '
  571. . "`comment` = '" . $this->dbi->escapeString($comment) . "'";
  572. } else {
  573. $upd_query = 'DELETE FROM '
  574. . Util::backquote($columnCommentsFeature->database) . '.'
  575. . Util::backquote($columnCommentsFeature->columnInfo)
  576. . ' WHERE `db_name` = \'' . $this->dbi->escapeString($db)
  577. . '\'
  578. AND `table_name` = \'\'
  579. AND `column_name` = \'(db_comment)\'';
  580. }
  581. return (bool) $this->dbi->queryAsControlUser($upd_query);
  582. }
  583. /**
  584. * Set a SQL history entry
  585. *
  586. * @param string $db the name of the db
  587. * @param string $table the name of the table
  588. * @param string $username the username
  589. * @param string $sqlquery the sql query
  590. */
  591. public function setHistory($db, $table, $username, $sqlquery): void
  592. {
  593. $maxCharactersInDisplayedSQL = $GLOBALS['cfg']['MaxCharactersInDisplayedSQL'];
  594. // Prevent to run this automatically on Footer class destroying in testsuite
  595. if (mb_strlen($sqlquery) > $maxCharactersInDisplayedSQL) {
  596. return;
  597. }
  598. $sqlHistoryFeature = $this->getRelationParameters()->sqlHistoryFeature;
  599. if (! isset($_SESSION['sql_history'])) {
  600. $_SESSION['sql_history'] = [];
  601. }
  602. $_SESSION['sql_history'][] = [
  603. 'db' => $db,
  604. 'table' => $table,
  605. 'sqlquery' => $sqlquery,
  606. ];
  607. if (count($_SESSION['sql_history']) > $GLOBALS['cfg']['QueryHistoryMax']) {
  608. // history should not exceed a maximum count
  609. array_shift($_SESSION['sql_history']);
  610. }
  611. if ($sqlHistoryFeature === null || ! $GLOBALS['cfg']['QueryHistoryDB']) {
  612. return;
  613. }
  614. $this->dbi->queryAsControlUser(
  615. 'INSERT INTO '
  616. . Util::backquote($sqlHistoryFeature->database) . '.'
  617. . Util::backquote($sqlHistoryFeature->history) . '
  618. (`username`,
  619. `db`,
  620. `table`,
  621. `timevalue`,
  622. `sqlquery`)
  623. VALUES
  624. (\'' . $this->dbi->escapeString($username) . '\',
  625. \'' . $this->dbi->escapeString($db) . '\',
  626. \'' . $this->dbi->escapeString($table) . '\',
  627. NOW(),
  628. \'' . $this->dbi->escapeString($sqlquery) . '\')'
  629. );
  630. $this->purgeHistory($username);
  631. }
  632. /**
  633. * Gets a SQL history entry
  634. *
  635. * @param string $username the username
  636. *
  637. * @return array|bool list of history items
  638. */
  639. public function getHistory($username)
  640. {
  641. $sqlHistoryFeature = $this->getRelationParameters()->sqlHistoryFeature;
  642. if ($sqlHistoryFeature === null) {
  643. return false;
  644. }
  645. /**
  646. * if db-based history is disabled but there exists a session-based
  647. * history, use it
  648. */
  649. if (! $GLOBALS['cfg']['QueryHistoryDB']) {
  650. if (isset($_SESSION['sql_history'])) {
  651. return array_reverse($_SESSION['sql_history']);
  652. }
  653. return false;
  654. }
  655. $hist_query = '
  656. SELECT `db`,
  657. `table`,
  658. `sqlquery`,
  659. `timevalue`
  660. FROM ' . Util::backquote($sqlHistoryFeature->database)
  661. . '.' . Util::backquote($sqlHistoryFeature->history) . '
  662. WHERE `username` = \'' . $this->dbi->escapeString($username) . '\'
  663. ORDER BY `id` DESC';
  664. return $this->dbi->fetchResult($hist_query, null, null, DatabaseInterface::CONNECT_CONTROL);
  665. }
  666. /**
  667. * purges SQL history
  668. *
  669. * deletes entries that exceeds $cfg['QueryHistoryMax'], oldest first, for the
  670. * given user
  671. *
  672. * @param string $username the username
  673. */
  674. public function purgeHistory($username): void
  675. {
  676. $sqlHistoryFeature = $this->getRelationParameters()->sqlHistoryFeature;
  677. if (! $GLOBALS['cfg']['QueryHistoryDB'] || $sqlHistoryFeature === null) {
  678. return;
  679. }
  680. $search_query = '
  681. SELECT `timevalue`
  682. FROM ' . Util::backquote($sqlHistoryFeature->database)
  683. . '.' . Util::backquote($sqlHistoryFeature->history) . '
  684. WHERE `username` = \'' . $this->dbi->escapeString($username) . '\'
  685. ORDER BY `timevalue` DESC
  686. LIMIT ' . $GLOBALS['cfg']['QueryHistoryMax'] . ', 1';
  687. $max_time = $this->dbi->fetchValue($search_query, 0, DatabaseInterface::CONNECT_CONTROL);
  688. if (! $max_time) {
  689. return;
  690. }
  691. $this->dbi->queryAsControlUser(
  692. 'DELETE FROM '
  693. . Util::backquote($sqlHistoryFeature->database) . '.'
  694. . Util::backquote($sqlHistoryFeature->history) . '
  695. WHERE `username` = \'' . $this->dbi->escapeString($username)
  696. . '\'
  697. AND `timevalue` <= \'' . $max_time . '\''
  698. );
  699. }
  700. /**
  701. * Prepares the dropdown for one mode
  702. *
  703. * @param array $foreign the keys and values for foreigns
  704. * @param string $data the current data of the dropdown
  705. * @param string $mode the needed mode
  706. *
  707. * @return string[] the <option value=""><option>s
  708. */
  709. public function buildForeignDropdown(array $foreign, $data, $mode): array
  710. {
  711. $reloptions = [];
  712. // id-only is a special mode used when no foreign display column
  713. // is available
  714. if ($mode === 'id-content' || $mode === 'id-only') {
  715. // sort for id-content
  716. if ($GLOBALS['cfg']['NaturalOrder']) {
  717. uksort($foreign, 'strnatcasecmp');
  718. } else {
  719. ksort($foreign);
  720. }
  721. } elseif ($mode === 'content-id') {
  722. // sort for content-id
  723. if ($GLOBALS['cfg']['NaturalOrder']) {
  724. natcasesort($foreign);
  725. } else {
  726. asort($foreign);
  727. }
  728. }
  729. foreach ($foreign as $key => $value) {
  730. $key = (string) $key;
  731. $value = (string) $value;
  732. $data = (string) $data;
  733. if (mb_check_encoding($key, 'utf-8') && ! preg_match('/[\x00-\x08\x0B\x0C\x0E-\x1F\x80-\x9F]/u', $key)) {
  734. $selected = ($key == $data);
  735. // show as text if it's valid utf-8
  736. $key = htmlspecialchars($key);
  737. } else {
  738. $key = '0x' . bin2hex($key);
  739. if (str_contains($data, '0x')) {
  740. $selected = ($key == trim($data));
  741. } else {
  742. $selected = ($key == '0x' . $data);
  743. }
  744. }
  745. if (
  746. mb_check_encoding($value, 'utf-8')
  747. && ! preg_match('/[\x00-\x08\x0B\x0C\x0E-\x1F\x80-\x9F]/u', $value)
  748. ) {
  749. if (mb_strlen($value) <= $GLOBALS['cfg']['LimitChars']) {
  750. // show as text if it's valid utf-8
  751. $value = htmlspecialchars($value);
  752. } else {
  753. // show as truncated text if it's valid utf-8
  754. $value = htmlspecialchars(
  755. mb_substr(
  756. $value,
  757. 0,
  758. (int) $GLOBALS['cfg']['LimitChars']
  759. ) . '...'
  760. );
  761. }
  762. } else {
  763. $value = '0x' . bin2hex($value);
  764. }
  765. $reloption = '<option value="' . $key . '"';
  766. if ($selected) {
  767. $reloption .= ' selected="selected"';
  768. }
  769. if ($mode === 'content-id') {
  770. $reloptions[] = $reloption . '>'
  771. . $value . '&nbsp;-&nbsp;' . $key . '</option>';
  772. } elseif ($mode === 'id-content') {
  773. $reloptions[] = $reloption . '>'
  774. . $key . '&nbsp;-&nbsp;' . $value . '</option>';
  775. } elseif ($mode === 'id-only') {
  776. $reloptions[] = $reloption . '>'
  777. . $key . '</option>';
  778. }
  779. }
  780. return $reloptions;
  781. }
  782. /**
  783. * Outputs dropdown with values of foreign fields
  784. *
  785. * @param array[] $disp_row array of the displayed row
  786. * @param string $foreign_field the foreign field
  787. * @param string $foreign_display the foreign field to display
  788. * @param string $data the current data of the dropdown (field in row)
  789. * @param int|null $max maximum number of items in the dropdown
  790. *
  791. * @return string the <option value=""><option>s
  792. */
  793. public function foreignDropdown(
  794. array $disp_row,
  795. $foreign_field,
  796. string $foreign_display,
  797. $data,
  798. $max = null
  799. ): string {
  800. if ($max === null) {
  801. $max = $GLOBALS['cfg']['ForeignKeyMaxLimit'];
  802. }
  803. $foreign = [];
  804. // collect the data
  805. foreach ($disp_row as $relrow) {
  806. $key = $relrow[$foreign_field];
  807. // if the display field has been defined for this foreign table
  808. if ($foreign_display) {
  809. $value = $relrow[$foreign_display];
  810. } else {
  811. $value = '';
  812. }
  813. $foreign[$key] = $value;
  814. }
  815. // put the dropdown sections in correct order
  816. $top = [];
  817. $bottom = [];
  818. if ($foreign_display) {
  819. if (
  820. isset($GLOBALS['cfg']['ForeignKeyDropdownOrder'])
  821. && is_array($GLOBALS['cfg']['ForeignKeyDropdownOrder'])
  822. ) {
  823. if (
  824. isset($GLOBALS['cfg']['ForeignKeyDropdownOrder'][0])
  825. && is_scalar($GLOBALS['cfg']['ForeignKeyDropdownOrder'][0])
  826. && strlen((string) $GLOBALS['cfg']['ForeignKeyDropdownOrder'][0]) > 0
  827. ) {
  828. $top = $this->buildForeignDropdown(
  829. $foreign,
  830. $data,
  831. (string) $GLOBALS['cfg']['ForeignKeyDropdownOrder'][0]
  832. );
  833. }
  834. if (
  835. isset($GLOBALS['cfg']['ForeignKeyDropdownOrder'][1])
  836. && is_scalar($GLOBALS['cfg']['ForeignKeyDropdownOrder'][1])
  837. && strlen((string) $GLOBALS['cfg']['ForeignKeyDropdownOrder'][1]) > 0
  838. ) {
  839. $bottom = $this->buildForeignDropdown(
  840. $foreign,
  841. $data,
  842. (string) $GLOBALS['cfg']['ForeignKeyDropdownOrder'][1]
  843. );
  844. }
  845. } else {
  846. $top = $this->buildForeignDropdown($foreign, $data, 'id-content');
  847. $bottom = $this->buildForeignDropdown($foreign, $data, 'content-id');
  848. }
  849. } else {
  850. $top = $this->buildForeignDropdown($foreign, $data, 'id-only');
  851. }
  852. // beginning of dropdown
  853. $ret = '<option value="">&nbsp;</option>';
  854. $top_count = count($top);
  855. if ($max == -1 || $top_count < $max) {
  856. $ret .= implode('', $top);
  857. if ($foreign_display && $top_count > 0) {
  858. // this empty option is to visually mark the beginning of the
  859. // second series of values (bottom)
  860. $ret .= '<option value="">&nbsp;</option>';
  861. }
  862. }
  863. if ($foreign_display) {
  864. $ret .= implode('', $bottom);
  865. }
  866. return $ret;
  867. }
  868. /**
  869. * Gets foreign keys in preparation for a drop-down selector
  870. *
  871. * @param array|bool $foreigners array of the foreign keys
  872. * @param string $field the foreign field name
  873. * @param bool $override_total whether to override the total
  874. * @param string $foreign_filter a possible filter
  875. * @param string $foreign_limit a possible LIMIT clause
  876. * @param bool $get_total optional, whether to get total num of rows
  877. * in $foreignData['the_total;]
  878. * (has an effect of performance)
  879. *
  880. * @return array<string, mixed> data about the foreign keys
  881. * @psalm-return array{
  882. * foreign_link: bool,
  883. * the_total: mixed,
  884. * foreign_display: string,
  885. * disp_row: list<non-empty-array>|null,
  886. * foreign_field: mixed
  887. * }
  888. */
  889. public function getForeignData(
  890. $foreigners,
  891. $field,
  892. $override_total,
  893. string $foreign_filter,
  894. $foreign_limit,
  895. $get_total = false
  896. ): array {
  897. // we always show the foreign field in the drop-down; if a display
  898. // field is defined, we show it besides the foreign field
  899. $foreign_link = false;
  900. $disp_row = $foreign_display = $the_total = $foreign_field = null;
  901. do {
  902. if (! $foreigners) {
  903. break;
  904. }
  905. $foreigner = $this->searchColumnInForeigners($foreigners, $field);
  906. if ($foreigner == false) {
  907. break;
  908. }
  909. $foreign_db = $foreigner['foreign_db'];
  910. $foreign_table = $foreigner['foreign_table'];
  911. $foreign_field = $foreigner['foreign_field'];
  912. // Count number of rows in the foreign table. Currently we do
  913. // not use a drop-down if more than ForeignKeyMaxLimit rows in the
  914. // foreign table,
  915. // for speed reasons and because we need a better interface for this.
  916. //
  917. // We could also do the SELECT anyway, with a LIMIT, and ensure that
  918. // the current value of the field is one of the choices.
  919. // Check if table has more rows than specified by
  920. // $GLOBALS['cfg']['ForeignKeyMaxLimit']
  921. $moreThanLimit = $this->dbi->getTable($foreign_db, $foreign_table)
  922. ->checkIfMinRecordsExist($GLOBALS['cfg']['ForeignKeyMaxLimit']);
  923. if ($override_total === true || ! $moreThanLimit) {
  924. // foreign_display can be false if no display field defined:
  925. $foreign_display = $this->getDisplayField($foreign_db, $foreign_table);
  926. $f_query_main = 'SELECT ' . Util::backquote($foreign_field)
  927. . (
  928. $foreign_display === false
  929. ? ''
  930. : ', ' . Util::backquote($foreign_display)
  931. );
  932. $f_query_from = ' FROM ' . Util::backquote($foreign_db)
  933. . '.' . Util::backquote($foreign_table);
  934. $f_query_filter = $foreign_filter === '' ? '' : ' WHERE '
  935. . Util::backquote($foreign_field)
  936. . ' LIKE "%' . $this->dbi->escapeString($foreign_filter) . '%"'
  937. . (
  938. $foreign_display === false
  939. ? ''
  940. : ' OR ' . Util::backquote($foreign_display)
  941. . ' LIKE "%' . $this->dbi->escapeString($foreign_filter)
  942. . '%"'
  943. );
  944. $f_query_order = $foreign_display === false ? '' : ' ORDER BY '
  945. . Util::backquote($foreign_table) . '.'
  946. . Util::backquote($foreign_display);
  947. $f_query_limit = $foreign_limit ?: '';
  948. if ($foreign_filter !== '') {
  949. $the_total = $this->dbi->fetchValue('SELECT COUNT(*)' . $f_query_from . $f_query_filter);
  950. if ($the_total === false) {
  951. $the_total = 0;
  952. }
  953. }
  954. $disp = $this->dbi->tryQuery(
  955. $f_query_main . $f_query_from . $f_query_filter
  956. . $f_query_order . $f_query_limit
  957. );
  958. if ($disp && $disp->numRows() > 0) {
  959. // If a resultset has been created, pre-cache it in the $disp_row
  960. // array. This helps us from not needing to use mysql_data_seek by
  961. // accessing a pre-cached PHP array. Usually those resultsets are
  962. // not that big, so a performance hit should not be expected.
  963. $disp_row = $disp->fetchAllAssoc();
  964. } else {
  965. // Either no data in the foreign table or
  966. // user does not have select permission to foreign table/field
  967. // Show an input field with a 'Browse foreign values' link
  968. $disp_row = null;
  969. $foreign_link = true;
  970. }
  971. } else {
  972. $disp_row = null;
  973. $foreign_link = true;
  974. }
  975. } while (false);
  976. if ($get_total && isset($foreign_db, $foreign_table)) {
  977. $the_total = $this->dbi->getTable($foreign_db, $foreign_table)
  978. ->countRecords(true);
  979. }
  980. return [
  981. 'foreign_link' => $foreign_link,
  982. 'the_total' => $the_total,
  983. 'foreign_display' => $foreign_display ?: '',
  984. 'disp_row' => $disp_row,
  985. 'foreign_field' => $foreign_field,
  986. ];
  987. }
  988. /**
  989. * Rename a field in relation tables
  990. *
  991. * usually called after a column in a table was renamed
  992. *
  993. * @param string $db database name
  994. * @param string $table table name
  995. * @param string $field old field name
  996. * @param string $new_name new field name
  997. */
  998. public function renameField($db, $table, $field, $new_name): void
  999. {
  1000. $relationParameters = $this->getRelationParameters();
  1001. if ($relationParameters->displayFeature !== null) {
  1002. $table_query = 'UPDATE '
  1003. . Util::backquote($relationParameters->displayFeature->database) . '.'
  1004. . Util::backquote($relationParameters->displayFeature->tableInfo)
  1005. . ' SET display_field = \'' . $this->dbi->escapeString($new_name) . '\''
  1006. . ' WHERE db_name = \'' . $this->dbi->escapeString($db)
  1007. . '\''
  1008. . ' AND table_name = \'' . $this->dbi->escapeString($table)
  1009. . '\''
  1010. . ' AND display_field = \'' . $this->dbi->escapeString($field)
  1011. . '\'';
  1012. $this->dbi->queryAsControlUser($table_query);
  1013. }
  1014. if ($relationParameters->relationFeature === null) {
  1015. return;
  1016. }
  1017. $table_query = 'UPDATE '
  1018. . Util::backquote($relationParameters->relationFeature->database) . '.'
  1019. . Util::backquote($relationParameters->relationFeature->relation)
  1020. . ' SET master_field = \'' . $this->dbi->escapeString($new_name) . '\''
  1021. . ' WHERE master_db = \'' . $this->dbi->escapeString($db)
  1022. . '\''
  1023. . ' AND master_table = \'' . $this->dbi->escapeString($table)
  1024. . '\''
  1025. . ' AND master_field = \'' . $this->dbi->escapeString($field)
  1026. . '\'';
  1027. $this->dbi->queryAsControlUser($table_query);
  1028. $table_query = 'UPDATE '
  1029. . Util::backquote($relationParameters->relationFeature->database) . '.'
  1030. . Util::backquote($relationParameters->relationFeature->relation)
  1031. . ' SET foreign_field = \'' . $this->dbi->escapeString($new_name) . '\''
  1032. . ' WHERE foreign_db = \'' . $this->dbi->escapeString($db)
  1033. . '\''
  1034. . ' AND foreign_table = \'' . $this->dbi->escapeString($table)
  1035. . '\''
  1036. . ' AND foreign_field = \'' . $this->dbi->escapeString($field)
  1037. . '\'';
  1038. $this->dbi->queryAsControlUser($table_query);
  1039. }
  1040. /**
  1041. * Performs SQL query used for renaming table.
  1042. *
  1043. * @param string $source_db Source database name
  1044. * @param string $target_db Target database name
  1045. * @param string $source_table Source table name
  1046. * @param string $target_table Target table name
  1047. * @param string $db_field Name of database field
  1048. * @param string $table_field Name of table field
  1049. */
  1050. public function renameSingleTable(
  1051. DatabaseName $configStorageDatabase,
  1052. TableName $configStorageTable,
  1053. string $source_db,
  1054. string $target_db,
  1055. string $source_table,
  1056. string $target_table,
  1057. string $db_field,
  1058. string $table_field
  1059. ): void {
  1060. $query = 'UPDATE '
  1061. . Util::backquote($configStorageDatabase) . '.'
  1062. . Util::backquote($configStorageTable)
  1063. . ' SET '
  1064. . $db_field . ' = \'' . $this->dbi->escapeString($target_db)
  1065. . '\', '
  1066. . $table_field . ' = \'' . $this->dbi->escapeString($target_table)
  1067. . '\''
  1068. . ' WHERE '
  1069. . $db_field . ' = \'' . $this->dbi->escapeString($source_db) . '\''
  1070. . ' AND '
  1071. . $table_field . ' = \'' . $this->dbi->escapeString($source_table)
  1072. . '\'';
  1073. $this->dbi->queryAsControlUser($query);
  1074. }
  1075. /**
  1076. * Rename a table in relation tables
  1077. *
  1078. * usually called after table has been moved
  1079. *
  1080. * @param string $source_db Source database name
  1081. * @param string $target_db Target database name
  1082. * @param string $source_table Source table name
  1083. * @param string $target_table Target table name
  1084. */
  1085. public function renameTable($source_db, $target_db, $source_table, $target_table): void
  1086. {
  1087. $relationParameters = $this->getRelationParameters();
  1088. // Move old entries from PMA-DBs to new table
  1089. if ($relationParameters->columnCommentsFeature !== null) {
  1090. $this->renameSingleTable(
  1091. $relationParameters->columnCommentsFeature->database,
  1092. $relationParameters->columnCommentsFeature->columnInfo,
  1093. $source_db,
  1094. $target_db,
  1095. $source_table,
  1096. $target_table,
  1097. 'db_name',
  1098. 'table_name'
  1099. );
  1100. }
  1101. // updating bookmarks is not possible since only a single table is
  1102. // moved, and not the whole DB.
  1103. if ($relationParameters->displayFeature !== null) {
  1104. $this->renameSingleTable(
  1105. $relationParameters->displayFeature->database,
  1106. $relationParameters->displayFeature->tableInfo,
  1107. $source_db,
  1108. $target_db,
  1109. $source_table,
  1110. $target_table,
  1111. 'db_name',
  1112. 'table_name'
  1113. );
  1114. }
  1115. if ($relationParameters->relationFeature !== null) {
  1116. $this->renameSingleTable(
  1117. $relationParameters->relationFeature->database,
  1118. $relationParameters->relationFeature->relation,
  1119. $source_db,
  1120. $target_db,
  1121. $source_table,
  1122. $target_table,
  1123. 'foreign_db',
  1124. 'foreign_table'
  1125. );
  1126. $this->renameSingleTable(
  1127. $relationParameters->relationFeature->database,
  1128. $relationParameters->relationFeature->relation,
  1129. $source_db,
  1130. $target_db,
  1131. $source_table,
  1132. $target_table,
  1133. 'master_db',
  1134. 'master_table'
  1135. );
  1136. }
  1137. if ($relationParameters->pdfFeature !== null) {
  1138. if ($source_db == $target_db) {
  1139. // rename within the database can be handled
  1140. $this->renameSingleTable(
  1141. $relationParameters->pdfFeature->database,
  1142. $relationParameters->pdfFeature->tableCoords,
  1143. $source_db,
  1144. $target_db,
  1145. $source_table,
  1146. $target_table,
  1147. 'db_name',
  1148. 'table_name'
  1149. );
  1150. } else {
  1151. // if the table is moved out of the database we can no longer keep the
  1152. // record for table coordinate
  1153. $remove_query = 'DELETE FROM '
  1154. . Util::backquote($relationParameters->pdfFeature->database) . '.'
  1155. . Util::backquote($relationParameters->pdfFeature->tableCoords)
  1156. . " WHERE db_name = '" . $this->dbi->escapeString($source_db) . "'"
  1157. . " AND table_name = '" . $this->dbi->escapeString($source_table)
  1158. . "'";
  1159. $this->dbi->queryAsControlUser($remove_query);
  1160. }
  1161. }
  1162. if ($relationParameters->uiPreferencesFeature !== null) {
  1163. $this->renameSingleTable(
  1164. $relationParameters->uiPreferencesFeature->database,
  1165. $relationParameters->uiPreferencesFeature->tableUiPrefs,
  1166. $source_db,
  1167. $target_db,
  1168. $source_table,
  1169. $target_table,
  1170. 'db_name',
  1171. 'table_name'
  1172. );
  1173. }
  1174. if ($relationParameters->navigationItemsHidingFeature === null) {
  1175. return;
  1176. }
  1177. // update hidden items inside table
  1178. $this->renameSingleTable(
  1179. $relationParameters->navigationItemsHidingFeature->database,
  1180. $relationParameters->navigationItemsHidingFeature->navigationHiding,
  1181. $source_db,
  1182. $target_db,
  1183. $source_table,
  1184. $target_table,
  1185. 'db_name',
  1186. 'table_name'
  1187. );
  1188. // update data for hidden table
  1189. $query = 'UPDATE '
  1190. . Util::backquote($relationParameters->navigationItemsHidingFeature->database) . '.'
  1191. . Util::backquote($relationParameters->navigationItemsHidingFeature->navigationHiding)
  1192. . " SET db_name = '" . $this->dbi->escapeString($target_db)
  1193. . "',"
  1194. . " item_name = '" . $this->dbi->escapeString($target_table)
  1195. . "'"
  1196. . " WHERE db_name = '" . $this->dbi->escapeString($source_db)
  1197. . "'"
  1198. . " AND item_name = '" . $this->dbi->escapeString($source_table)
  1199. . "'"
  1200. . " AND item_type = 'table'";
  1201. $this->dbi->queryAsControlUser($query);
  1202. }
  1203. /**
  1204. * Create a PDF page
  1205. *
  1206. * @param string|null $newpage name of the new PDF page
  1207. * @param string $db database name
  1208. */
  1209. public function createPage(?string $newpage, PdfFeature $pdfFeature, $db): int
  1210. {
  1211. $ins_query = 'INSERT INTO '
  1212. . Util::backquote($pdfFeature->database) . '.'
  1213. . Util::backquote($pdfFeature->pdfPages)
  1214. . ' (db_name, page_descr)'
  1215. . ' VALUES (\''
  1216. . $this->dbi->escapeString($db) . '\', \''
  1217. . $this->dbi->escapeString($newpage ?: __('no description')) . '\')';
  1218. $this->dbi->tryQueryAsControlUser($ins_query);
  1219. return $this->dbi->insertId(DatabaseInterface::CONNECT_CONTROL);
  1220. }
  1221. /**
  1222. * Get child table references for a table column.
  1223. * This works only if 'DisableIS' is false. An empty array is returned otherwise.
  1224. *
  1225. * @param string $db name of master table db.
  1226. * @param string $table name of master table.
  1227. * @param string $column name of master table column.
  1228. */
  1229. public function getChildReferences($db, $table, $column = ''): array
  1230. {
  1231. if (! $GLOBALS['cfg']['Server']['DisableIS']) {
  1232. $rel_query = 'SELECT `column_name`, `table_name`,'
  1233. . ' `table_schema`, `referenced_column_name`'
  1234. . ' FROM `information_schema`.`key_column_usage`'
  1235. . " WHERE `referenced_table_name` = '"
  1236. . $this->dbi->escapeString($table) . "'"
  1237. . " AND `referenced_table_schema` = '"
  1238. . $this->dbi->escapeString($db) . "'";
  1239. if ($column) {
  1240. $rel_query .= " AND `referenced_column_name` = '"
  1241. . $this->dbi->escapeString($column) . "'";
  1242. }
  1243. return $this->dbi->fetchResult(
  1244. $rel_query,
  1245. [
  1246. 'referenced_column_name',
  1247. null,
  1248. ]
  1249. );
  1250. }
  1251. return [];
  1252. }
  1253. /**
  1254. * Check child table references and foreign key for a table column.
  1255. *
  1256. * @param string $db name of master table db.
  1257. * @param string $table name of master table.
  1258. * @param string $column name of master table column.
  1259. * @param array|null $foreigners_full foreigners array for the whole table.
  1260. * @param array|null $child_references_full child references for the whole table.
  1261. *
  1262. * @return array<string, mixed> telling about references if foreign key.
  1263. * @psalm-return array{isEditable: bool, isForeignKey: bool, isReferenced: bool, references: string[]}
  1264. */
  1265. public function checkChildForeignReferences(
  1266. $db,
  1267. $table,
  1268. $column,
  1269. $foreigners_full = null,
  1270. $child_references_full = null
  1271. ): array {
  1272. $column_status = [
  1273. 'isEditable' => true,
  1274. 'isReferenced' => false,
  1275. 'isForeignKey' => false,
  1276. 'references' => [],
  1277. ];
  1278. $foreigners = [];
  1279. if ($foreigners_full !== null) {
  1280. if (isset($foreigners_full[$column])) {
  1281. $foreigners[$column] = $foreigners_full[$column];
  1282. }
  1283. if (isset($foreigners_full['foreign_keys_data'])) {
  1284. $foreigners['foreign_keys_data'] = $foreigners_full['foreign_keys_data'];
  1285. }
  1286. } else {
  1287. $foreigners = $this->getForeigners($db, $table, $column, 'foreign');
  1288. }
  1289. $foreigner = $this->searchColumnInForeigners($foreigners, $column);
  1290. $child_references = [];
  1291. if ($child_references_full !== null) {
  1292. if (isset($child_references_full[$column])) {
  1293. $child_references = $child_references_full[$column];
  1294. }
  1295. } else {
  1296. $child_references = $this->getChildReferences($db, $table, $column);
  1297. }
  1298. if (count($child_references) > 0 || $foreigner) {
  1299. $column_status['isEditable'] = false;
  1300. if (count($child_references) > 0) {
  1301. $column_status['isReferenced'] = true;
  1302. foreach ($child_references as $columns) {
  1303. $column_status['references'][] = Util::backquote($columns['table_schema'])
  1304. . '.' . Util::backquote($columns['table_name']);
  1305. }
  1306. }
  1307. if ($foreigner) {
  1308. $column_status['isForeignKey'] = true;
  1309. }
  1310. }
  1311. return $column_status;
  1312. }
  1313. /**
  1314. * Search a table column in foreign data.
  1315. *
  1316. * @param array $foreigners Table Foreign data
  1317. * @param string $column Column name
  1318. *
  1319. * @return array|false
  1320. */
  1321. public function searchColumnInForeigners(array $foreigners, $column)
  1322. {
  1323. if (isset($foreigners[$column])) {
  1324. return $foreigners[$column];
  1325. }
  1326. if (! isset($foreigners['foreign_keys_data'])) {
  1327. return false;
  1328. }
  1329. $foreigner = [];
  1330. foreach ($foreigners['foreign_keys_data'] as $one_key) {
  1331. $column_index = array_search($column, $one_key['index_list']);
  1332. if ($column_index !== false) {
  1333. $foreigner['foreign_field'] = $one_key['ref_index_list'][$column_index];
  1334. $foreigner['foreign_db'] = $one_key['ref_db_name'] ?? $GLOBALS['db'];
  1335. $foreigner['foreign_table'] = $one_key['ref_table_name'];
  1336. $foreigner['constraint'] = $one_key['constraint'];
  1337. $foreigner['on_update'] = $one_key['on_update'] ?? 'RESTRICT';
  1338. $foreigner['on_delete'] = $one_key['on_delete'] ?? 'RESTRICT';
  1339. return $foreigner;
  1340. }
  1341. }
  1342. return false;
  1343. }
  1344. /**
  1345. * Returns default PMA table names and their create queries.
  1346. *
  1347. * @return array<string, string> table name, create query
  1348. */
  1349. public function getDefaultPmaTableNames(array $tableNameReplacements): array
  1350. {
  1351. $pma_tables = [];
  1352. $create_tables_file = (string) file_get_contents(SQL_DIR . 'create_tables.sql');
  1353. $queries = explode(';', $create_tables_file);
  1354. foreach ($queries as $query) {
  1355. if (! preg_match('/CREATE TABLE IF NOT EXISTS `(.*)` \(/', $query, $table)) {
  1356. continue;
  1357. }
  1358. // The following redundant cast is needed for PHPStan
  1359. $tableName = (string) $table[1];
  1360. // Replace the table name with another one
  1361. if (isset($tableNameReplacements[$tableName])) {
  1362. $query = str_replace($tableName, $tableNameReplacements[$tableName], $query);
  1363. }
  1364. $pma_tables[$tableName] = $query . ';';
  1365. }
  1366. return $pma_tables;
  1367. }
  1368. /**
  1369. * Create a database to be used as configuration storage
  1370. */
  1371. public function createPmaDatabase(string $configurationStorageDbName): bool
  1372. {
  1373. $this->dbi->tryQuery(
  1374. 'CREATE DATABASE IF NOT EXISTS ' . Util::backquote($configurationStorageDbName),
  1375. DatabaseInterface::CONNECT_CONTROL
  1376. );
  1377. $error = $this->dbi->getError(DatabaseInterface::CONNECT_CONTROL);
  1378. if (! $error) {
  1379. // Re-build the cache to show the list of tables created or not
  1380. // This is the case when the DB could be created but no tables just after
  1381. // So just purge the cache and show the new configuration storage state
  1382. unset($_SESSION['relation'][$GLOBALS['server']]);
  1383. $this->getRelationParameters();
  1384. return true;
  1385. }
  1386. $GLOBALS['message'] = $error;
  1387. if ($GLOBALS['errno'] === 1044) {
  1388. $GLOBALS['message'] = sprintf(
  1389. __(
  1390. 'You do not have necessary privileges to create a database named'
  1391. . ' \'%s\'. You may go to \'Operations\' tab of any'
  1392. . ' database to set up the phpMyAdmin configuration storage there.'
  1393. ),
  1394. $configurationStorageDbName
  1395. );
  1396. }
  1397. return false;
  1398. }
  1399. /**
  1400. * Creates PMA tables in the given db, updates if already exists.
  1401. *
  1402. * @param string $db database
  1403. * @param bool $create whether to create tables if they don't exist.
  1404. */
  1405. public function fixPmaTables($db, $create = true): void
  1406. {
  1407. if ($this->arePmadbTablesAllDisabled()) {
  1408. return;
  1409. }
  1410. $tablesToFeatures = [
  1411. 'pma__bookmark' => 'bookmarktable',
  1412. 'pma__relation' => 'relation',
  1413. 'pma__table_info' => 'table_info',
  1414. 'pma__table_coords' => 'table_coords',
  1415. 'pma__pdf_pages' => 'pdf_pages',
  1416. 'pma__column_info' => 'column_info',
  1417. 'pma__history' => 'history',
  1418. 'pma__recent' => 'recent',
  1419. 'pma__favorite' => 'favorite',
  1420. 'pma__table_uiprefs' => 'table_uiprefs',
  1421. 'pma__tracking' => 'tracking',
  1422. 'pma__userconfig' => 'userconfig',
  1423. 'pma__users' => 'users',
  1424. 'pma__usergroups' => 'usergroups',
  1425. 'pma__navigationhiding' => 'navigationhiding',
  1426. 'pma__savedsearches' => 'savedsearches',
  1427. 'pma__central_columns' => 'central_columns',
  1428. 'pma__designer_settings' => 'designer_settings',
  1429. 'pma__export_templates' => 'export_templates',
  1430. ];
  1431. $existingTables = $this->dbi->getTables($db, DatabaseInterface::CONNECT_CONTROL);
  1432. /** @var array<string, string> $tableNameReplacements */
  1433. $tableNameReplacements = [];
  1434. // Build a map of replacements between default table names and name built by the user
  1435. foreach ($tablesToFeatures as $table => $feature) {
  1436. // Empty, we can not do anything about it
  1437. if (empty($GLOBALS['cfg']['Server'][$feature])) {
  1438. continue;
  1439. }
  1440. // Default table name, nothing to do
  1441. if ($GLOBALS['cfg']['Server'][$feature] === $table) {
  1442. continue;
  1443. }
  1444. // Set the replacement to transform the default table name into a custom name
  1445. $tableNameReplacements[$table] = $GLOBALS['cfg']['Server'][$feature];
  1446. }
  1447. $createQueries = null;
  1448. $foundOne = false;
  1449. foreach ($tablesToFeatures as $table => $feature) {
  1450. if (($GLOBALS['cfg']['Server'][$feature] ?? null) === false) {
  1451. // The feature is disabled by the user in config
  1452. continue;
  1453. }
  1454. // Check if the table already exists
  1455. // use the possible replaced name first and fallback on the table name
  1456. // if no replacement exists
  1457. if (! in_array($tableNameReplacements[$table] ?? $table, $existingTables)) {
  1458. if ($create) {
  1459. if ($createQueries == null) { // first create
  1460. $createQueries = $this->getDefaultPmaTableNames($tableNameReplacements);
  1461. if (! $this->dbi->selectDb($db, DatabaseInterface::CONNECT_CONTROL)) {
  1462. $GLOBALS['message'] = $this->dbi->getError(DatabaseInterface::CONNECT_CONTROL);
  1463. return;
  1464. }
  1465. }
  1466. $this->dbi->tryQuery($createQueries[$table], DatabaseInterface::CONNECT_CONTROL);
  1467. $error = $this->dbi->getError(DatabaseInterface::CONNECT_CONTROL);
  1468. if ($error) {
  1469. $GLOBALS['message'] = $error;
  1470. return;
  1471. }
  1472. $foundOne = true;
  1473. if (empty($GLOBALS['cfg']['Server'][$feature])) {
  1474. // Do not override a user defined value, only fill if empty
  1475. $GLOBALS['cfg']['Server'][$feature] = $table;
  1476. }
  1477. }
  1478. } else {
  1479. $foundOne = true;
  1480. if (empty($GLOBALS['cfg']['Server'][$feature])) {
  1481. // Do not override a user defined value, only fill if empty
  1482. $GLOBALS['cfg']['Server'][$feature] = $table;
  1483. }
  1484. }
  1485. }
  1486. if (! $foundOne) {
  1487. return;
  1488. }
  1489. $GLOBALS['cfg']['Server']['pmadb'] = $db;
  1490. //NOTE: I am unsure why we do that, as it defeats the purpose of the session cache
  1491. // Unset the cache
  1492. unset($_SESSION['relation'][$GLOBALS['server']]);
  1493. // Fill back the cache
  1494. $this->getRelationParameters();
  1495. }
  1496. /**
  1497. * Gets the relations info and status, depending on the condition
  1498. *
  1499. * @param bool $condition whether to look for foreigners or not
  1500. * @param string $db database name
  1501. * @param string $table table name
  1502. *
  1503. * @return array ($res_rel, $have_rel)
  1504. * @psalm-return array{array, bool}
  1505. */
  1506. public function getRelationsAndStatus(bool $condition, $db, $table)
  1507. {
  1508. $have_rel = false;
  1509. $res_rel = [];
  1510. if ($condition) {
  1511. // Find which tables are related with the current one and write it in
  1512. // an array
  1513. $res_rel = $this->getForeigners($db, $table);
  1514. $have_rel = count($res_rel) > 0;
  1515. }
  1516. return [
  1517. $res_rel,
  1518. $have_rel,
  1519. ];
  1520. }
  1521. /**
  1522. * Verifies that all pmadb features are disabled
  1523. */
  1524. public function arePmadbTablesAllDisabled(): bool
  1525. {
  1526. return ($GLOBALS['cfg']['Server']['bookmarktable'] ?? null) === false
  1527. && ($GLOBALS['cfg']['Server']['relation'] ?? null) === false
  1528. && ($GLOBALS['cfg']['Server']['table_info'] ?? null) === false
  1529. && ($GLOBALS['cfg']['Server']['table_coords'] ?? null) === false
  1530. && ($GLOBALS['cfg']['Server']['column_info'] ?? null) === false
  1531. && ($GLOBALS['cfg']['Server']['pdf_pages'] ?? null) === false
  1532. && ($GLOBALS['cfg']['Server']['history'] ?? null) === false
  1533. && ($GLOBALS['cfg']['Server']['recent'] ?? null) === false
  1534. && ($GLOBALS['cfg']['Server']['favorite'] ?? null) === false
  1535. && ($GLOBALS['cfg']['Server']['table_uiprefs'] ?? null) === false
  1536. && ($GLOBALS['cfg']['Server']['tracking'] ?? null) === false
  1537. && ($GLOBALS['cfg']['Server']['userconfig'] ?? null) === false
  1538. && ($GLOBALS['cfg']['Server']['users'] ?? null) === false
  1539. && ($GLOBALS['cfg']['Server']['usergroups'] ?? null) === false
  1540. && ($GLOBALS['cfg']['Server']['navigationhiding'] ?? null) === false
  1541. && ($GLOBALS['cfg']['Server']['savedsearches'] ?? null) === false
  1542. && ($GLOBALS['cfg']['Server']['central_columns'] ?? null) === false
  1543. && ($GLOBALS['cfg']['Server']['designer_settings'] ?? null) === false
  1544. && ($GLOBALS['cfg']['Server']['export_templates'] ?? null) === false;
  1545. }
  1546. /**
  1547. * Verifies if all the pmadb tables are defined
  1548. */
  1549. public function arePmadbTablesDefined(): bool
  1550. {
  1551. return ! (empty($GLOBALS['cfg']['Server']['bookmarktable'])
  1552. || empty($GLOBALS['cfg']['Server']['relation'])
  1553. || empty($GLOBALS['cfg']['Server']['table_info'])
  1554. || empty($GLOBALS['cfg']['Server']['table_coords'])
  1555. || empty($GLOBALS['cfg']['Server']['column_info'])
  1556. || empty($GLOBALS['cfg']['Server']['pdf_pages'])
  1557. || empty($GLOBALS['cfg']['Server']['history'])
  1558. || empty($GLOBALS['cfg']['Server']['recent'])
  1559. || empty($GLOBALS['cfg']['Server']['favorite'])
  1560. || empty($GLOBALS['cfg']['Server']['table_uiprefs'])
  1561. || empty($GLOBALS['cfg']['Server']['tracking'])
  1562. || empty($GLOBALS['cfg']['Server']['userconfig'])
  1563. || empty($GLOBALS['cfg']['Server']['users'])
  1564. || empty($GLOBALS['cfg']['Server']['usergroups'])
  1565. || empty($GLOBALS['cfg']['Server']['navigationhiding'])
  1566. || empty($GLOBALS['cfg']['Server']['savedsearches'])
  1567. || empty($GLOBALS['cfg']['Server']['central_columns'])
  1568. || empty($GLOBALS['cfg']['Server']['designer_settings'])
  1569. || empty($GLOBALS['cfg']['Server']['export_templates']));
  1570. }
  1571. /**
  1572. * Get tables for foreign key constraint
  1573. *
  1574. * @param string $foreignDb Database name
  1575. * @param string $tblStorageEngine Table storage engine
  1576. *
  1577. * @return array Table names
  1578. */
  1579. public function getTables($foreignDb, $tblStorageEngine)
  1580. {
  1581. $tables = [];
  1582. $tablesRows = $this->dbi->query('SHOW TABLE STATUS FROM ' . Util::backquote($foreignDb));
  1583. while ($row = $tablesRows->fetchRow()) {
  1584. if (! isset($row[1]) || mb_strtoupper($row[1]) != $tblStorageEngine) {
  1585. continue;
  1586. }
  1587. $tables[] = $row[0];
  1588. }
  1589. if ($GLOBALS['cfg']['NaturalOrder']) {
  1590. usort($tables, 'strnatcasecmp');
  1591. }
  1592. return $tables;
  1593. }
  1594. public function getConfigurationStorageDbName(): string
  1595. {
  1596. global $cfg;
  1597. $cfgStorageDbName = $cfg['Server']['pmadb'] ?? '';
  1598. // Use "phpmyadmin" as a default database name to check to keep the behavior consistent
  1599. return empty($cfgStorageDbName) ? 'phpmyadmin' : $cfgStorageDbName;
  1600. }
  1601. /**
  1602. * This function checks and initializes the phpMyAdmin configuration
  1603. * storage state before it is used into session cache.
  1604. */
  1605. public function initRelationParamsCache(): void
  1606. {
  1607. $storageDbName = $GLOBALS['cfg']['Server']['pmadb'] ?? '';
  1608. // Use "phpmyadmin" as a default database name to check to keep the behavior consistent
  1609. $storageDbName = is_string($storageDbName) && $storageDbName !== '' ? $storageDbName : 'phpmyadmin';
  1610. // This will make users not having explicitly listed databases
  1611. // have config values filled by the default phpMyAdmin storage table name values
  1612. $this->fixPmaTables($storageDbName, false);
  1613. // This global will be changed if fixPmaTables did find one valid table
  1614. $storageDbName = $GLOBALS['cfg']['Server']['pmadb'] ?? '';
  1615. // Empty means that until now no pmadb was found eligible
  1616. if (! empty($storageDbName)) {
  1617. return;
  1618. }
  1619. $this->fixPmaTables($GLOBALS['db'], false);
  1620. }
  1621. }