Sql.php 65 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552155315541555155615571558155915601561156215631564156515661567156815691570157115721573157415751576157715781579158015811582158315841585158615871588158915901591159215931594159515961597159815991600160116021603160416051606160716081609161016111612161316141615161616171618161916201621162216231624162516261627162816291630163116321633163416351636163716381639164016411642164316441645164616471648164916501651165216531654165516561657165816591660166116621663166416651666166716681669167016711672167316741675167616771678167916801681168216831684168516861687168816891690169116921693169416951696169716981699170017011702170317041705170617071708170917101711171217131714171517161717171817191720172117221723172417251726172717281729173017311732173317341735173617371738173917401741174217431744174517461747174817491750175117521753175417551756175717581759176017611762176317641765176617671768176917701771177217731774177517761777177817791780178117821783178417851786178717881789179017911792179317941795179617971798179918001801180218031804180518061807180818091810181118121813181418151816181718181819182018211822
  1. <?php
  2. declare(strict_types=1);
  3. namespace PhpMyAdmin;
  4. use PhpMyAdmin\ConfigStorage\Features\BookmarkFeature;
  5. use PhpMyAdmin\ConfigStorage\Relation;
  6. use PhpMyAdmin\ConfigStorage\RelationCleanup;
  7. use PhpMyAdmin\Dbal\ResultInterface;
  8. use PhpMyAdmin\Display\Results as DisplayResults;
  9. use PhpMyAdmin\Html\Generator;
  10. use PhpMyAdmin\Html\MySQLDocumentation;
  11. use PhpMyAdmin\Query\Generator as QueryGenerator;
  12. use PhpMyAdmin\Query\Utilities;
  13. use PhpMyAdmin\SqlParser\Components\Expression;
  14. use PhpMyAdmin\SqlParser\Statements\AlterStatement;
  15. use PhpMyAdmin\SqlParser\Statements\DropStatement;
  16. use PhpMyAdmin\SqlParser\Statements\SelectStatement;
  17. use PhpMyAdmin\SqlParser\Utils\Query;
  18. use PhpMyAdmin\Utils\ForeignKey;
  19. use function __;
  20. use function array_key_exists;
  21. use function array_keys;
  22. use function array_map;
  23. use function bin2hex;
  24. use function ceil;
  25. use function count;
  26. use function defined;
  27. use function explode;
  28. use function htmlspecialchars;
  29. use function in_array;
  30. use function is_array;
  31. use function is_bool;
  32. use function is_object;
  33. use function session_start;
  34. use function session_write_close;
  35. use function sprintf;
  36. use function str_contains;
  37. use function str_replace;
  38. use function ucwords;
  39. /**
  40. * Set of functions for the SQL executor
  41. */
  42. class Sql
  43. {
  44. /** @var DatabaseInterface */
  45. private $dbi;
  46. /** @var Relation */
  47. private $relation;
  48. /** @var RelationCleanup */
  49. private $relationCleanup;
  50. /** @var Transformations */
  51. private $transformations;
  52. /** @var Operations */
  53. private $operations;
  54. /** @var Template */
  55. private $template;
  56. public function __construct(
  57. DatabaseInterface $dbi,
  58. Relation $relation,
  59. RelationCleanup $relationCleanup,
  60. Operations $operations,
  61. Transformations $transformations,
  62. Template $template
  63. ) {
  64. $this->dbi = $dbi;
  65. $this->relation = $relation;
  66. $this->relationCleanup = $relationCleanup;
  67. $this->operations = $operations;
  68. $this->transformations = $transformations;
  69. $this->template = $template;
  70. }
  71. /**
  72. * Handle remembered sorting order, only for single table query
  73. *
  74. * @param string $db database name
  75. * @param string $table table name
  76. * @param array $analyzedSqlResults the analyzed query results
  77. * @param string $fullSqlQuery SQL query
  78. */
  79. private function handleSortOrder(
  80. $db,
  81. $table,
  82. array &$analyzedSqlResults,
  83. &$fullSqlQuery
  84. ): void {
  85. $tableObject = new Table($table, $db);
  86. if (empty($analyzedSqlResults['order'])) {
  87. // Retrieving the name of the column we should sort after.
  88. $sortCol = $tableObject->getUiProp(Table::PROP_SORTED_COLUMN);
  89. if (empty($sortCol)) {
  90. return;
  91. }
  92. // Remove the name of the table from the retrieved field name.
  93. $sortCol = str_replace(
  94. Util::backquote($table) . '.',
  95. '',
  96. $sortCol
  97. );
  98. // Create the new query.
  99. $fullSqlQuery = Query::replaceClause(
  100. $analyzedSqlResults['statement'],
  101. $analyzedSqlResults['parser']->list,
  102. 'ORDER BY ' . $sortCol
  103. );
  104. // TODO: Avoid reparsing the query.
  105. $analyzedSqlResults = Query::getAll($fullSqlQuery);
  106. } else {
  107. // Store the remembered table into session.
  108. $tableObject->setUiProp(
  109. Table::PROP_SORTED_COLUMN,
  110. Query::getClause(
  111. $analyzedSqlResults['statement'],
  112. $analyzedSqlResults['parser']->list,
  113. 'ORDER BY'
  114. )
  115. );
  116. }
  117. }
  118. /**
  119. * Append limit clause to SQL query
  120. *
  121. * @param array $analyzedSqlResults the analyzed query results
  122. *
  123. * @return string limit clause appended SQL query
  124. */
  125. private function getSqlWithLimitClause(array $analyzedSqlResults)
  126. {
  127. return Query::replaceClause(
  128. $analyzedSqlResults['statement'],
  129. $analyzedSqlResults['parser']->list,
  130. 'LIMIT ' . $_SESSION['tmpval']['pos'] . ', '
  131. . $_SESSION['tmpval']['max_rows']
  132. );
  133. }
  134. /**
  135. * Verify whether the result set has columns from just one table
  136. *
  137. * @param array $fieldsMeta meta fields
  138. */
  139. private function resultSetHasJustOneTable(array $fieldsMeta): bool
  140. {
  141. $justOneTable = true;
  142. $prevTable = '';
  143. foreach ($fieldsMeta as $oneFieldMeta) {
  144. if ($oneFieldMeta->table != '' && $prevTable != '' && $oneFieldMeta->table != $prevTable) {
  145. $justOneTable = false;
  146. }
  147. if ($oneFieldMeta->table == '') {
  148. continue;
  149. }
  150. $prevTable = $oneFieldMeta->table;
  151. }
  152. return $justOneTable && $prevTable != '';
  153. }
  154. /**
  155. * Verify whether the result set contains all the columns
  156. * of at least one unique key
  157. *
  158. * @param string $db database name
  159. * @param string $table table name
  160. * @param array $fieldsMeta meta fields
  161. */
  162. private function resultSetContainsUniqueKey(string $db, string $table, array $fieldsMeta): bool
  163. {
  164. if ($table === '') {
  165. return false;
  166. }
  167. $columns = $this->dbi->getColumns($db, $table);
  168. $resultSetColumnNames = [];
  169. foreach ($fieldsMeta as $oneMeta) {
  170. $resultSetColumnNames[] = $oneMeta->name;
  171. }
  172. foreach (Index::getFromTable($table, $db) as $index) {
  173. if (! $index->isUnique()) {
  174. continue;
  175. }
  176. $indexColumns = $index->getColumns();
  177. $numberFound = 0;
  178. foreach (array_keys($indexColumns) as $indexColumnName) {
  179. if (
  180. ! in_array($indexColumnName, $resultSetColumnNames)
  181. && array_key_exists($indexColumnName, $columns)
  182. && ! str_contains($columns[$indexColumnName]['Extra'], 'INVISIBLE')
  183. ) {
  184. continue;
  185. }
  186. $numberFound++;
  187. }
  188. if ($numberFound == count($indexColumns)) {
  189. return true;
  190. }
  191. }
  192. return false;
  193. }
  194. /**
  195. * Get the HTML for relational column dropdown
  196. * During grid edit, if we have a relational field, returns the html for the
  197. * dropdown
  198. *
  199. * @param string $db current database
  200. * @param string $table current table
  201. * @param string $column current column
  202. * @param string $currentValue current selected value
  203. *
  204. * @return string html for the dropdown
  205. */
  206. public function getHtmlForRelationalColumnDropdown($db, $table, $column, $currentValue)
  207. {
  208. $foreigners = $this->relation->getForeigners($db, $table, $column);
  209. $foreignData = $this->relation->getForeignData($foreigners, $column, false, '', '');
  210. if ($foreignData['disp_row'] == null) {
  211. //Handle the case when number of values
  212. //is more than $cfg['ForeignKeyMaxLimit']
  213. $urlParams = [
  214. 'db' => $db,
  215. 'table' => $table,
  216. 'field' => $column,
  217. ];
  218. $dropdown = $this->template->render('sql/relational_column_dropdown', [
  219. 'current_value' => $_POST['curr_value'],
  220. 'params' => $urlParams,
  221. ]);
  222. } else {
  223. $dropdown = $this->relation->foreignDropdown(
  224. $foreignData['disp_row'],
  225. $foreignData['foreign_field'],
  226. $foreignData['foreign_display'],
  227. $currentValue,
  228. $GLOBALS['cfg']['ForeignKeyMaxLimit']
  229. );
  230. $dropdown = '<select>' . $dropdown . '</select>';
  231. }
  232. return $dropdown;
  233. }
  234. /** @return array<string, int|array> */
  235. private function getDetailedProfilingStats(array $profilingResults): array
  236. {
  237. $profiling = [
  238. 'total_time' => 0,
  239. 'states' => [],
  240. 'chart' => [],
  241. 'profile' => [],
  242. ];
  243. foreach ($profilingResults as $oneResult) {
  244. $status = ucwords($oneResult['Status']);
  245. $profiling['total_time'] += $oneResult['Duration'];
  246. $profiling['profile'][] = [
  247. 'status' => $status,
  248. 'duration' => Util::formatNumber($oneResult['Duration'], 3, 1),
  249. 'duration_raw' => $oneResult['Duration'],
  250. ];
  251. if (! isset($profiling['states'][$status])) {
  252. $profiling['states'][$status] = [
  253. 'total_time' => $oneResult['Duration'],
  254. 'calls' => 1,
  255. ];
  256. $profiling['chart'][$status] = $oneResult['Duration'];
  257. } else {
  258. $profiling['states'][$status]['calls']++;
  259. $profiling['states'][$status]['total_time'] += $oneResult['Duration'];
  260. $profiling['chart'][$status] += $oneResult['Duration'];
  261. }
  262. }
  263. return $profiling;
  264. }
  265. /**
  266. * Get value of a column for a specific row (marked by $whereClause)
  267. */
  268. public function getFullValuesForSetColumn(
  269. string $db,
  270. string $table,
  271. string $column,
  272. string $whereClause
  273. ): string {
  274. $row = $this->dbi->fetchSingleRow(sprintf(
  275. 'SELECT `%s` FROM `%s`.`%s` WHERE %s',
  276. $column,
  277. $db,
  278. $table,
  279. $whereClause
  280. ));
  281. if ($row === null) {
  282. return '';
  283. }
  284. return $row[$column];
  285. }
  286. /**
  287. * Get all the values for a enum column or set column in a table
  288. *
  289. * @param string $db current database
  290. * @param string $table current table
  291. * @param string $column current column
  292. *
  293. * @return array|null array containing the value list for the column, null on failure
  294. */
  295. public function getValuesForColumn(string $db, string $table, string $column): ?array
  296. {
  297. $fieldInfoQuery = QueryGenerator::getColumnsSql($db, $table, $this->dbi->escapeString($column));
  298. $fieldInfoResult = $this->dbi->fetchResult($fieldInfoQuery);
  299. if (! isset($fieldInfoResult[0])) {
  300. return null;
  301. }
  302. return Util::parseEnumSetValues($fieldInfoResult[0]['Type'], false);
  303. }
  304. /**
  305. * Function to check whether to remember the sorting order or not
  306. *
  307. * @param array $analyzedSqlResults the analyzed query and other variables set
  308. * after analyzing the query
  309. */
  310. private function isRememberSortingOrder(array $analyzedSqlResults): bool
  311. {
  312. return isset($analyzedSqlResults['select_expr'], $analyzedSqlResults['select_tables'])
  313. && $GLOBALS['cfg']['RememberSorting']
  314. && ! ($analyzedSqlResults['is_count']
  315. || $analyzedSqlResults['is_export']
  316. || $analyzedSqlResults['is_func']
  317. || $analyzedSqlResults['is_analyse'])
  318. && $analyzedSqlResults['select_from']
  319. && (empty($analyzedSqlResults['select_expr'])
  320. || ((count($analyzedSqlResults['select_expr']) === 1)
  321. && ($analyzedSqlResults['select_expr'][0] === '*')))
  322. && count($analyzedSqlResults['select_tables']) === 1;
  323. }
  324. /**
  325. * Function to check whether the LIMIT clause should be appended or not
  326. *
  327. * @param array $analyzedSqlResults the analyzed query and other variables set
  328. * after analyzing the query
  329. */
  330. private function isAppendLimitClause(array $analyzedSqlResults): bool
  331. {
  332. // Assigning LIMIT clause to an syntactically-wrong query
  333. // is not needed. Also we would want to show the true query
  334. // and the true error message to the query executor
  335. return (isset($analyzedSqlResults['parser'])
  336. && count($analyzedSqlResults['parser']->errors) === 0)
  337. && ($_SESSION['tmpval']['max_rows'] !== 'all')
  338. && ! ($analyzedSqlResults['is_export']
  339. || $analyzedSqlResults['is_analyse'])
  340. && ($analyzedSqlResults['select_from']
  341. || $analyzedSqlResults['is_subquery'])
  342. && empty($analyzedSqlResults['limit']);
  343. }
  344. /**
  345. * Function to check whether this query is for just browsing
  346. *
  347. * @param array<string, mixed> $analyzedSqlResults the analyzed query and other variables set
  348. * after analyzing the query
  349. * @param bool|null $findRealEnd whether the real end should be found
  350. */
  351. public static function isJustBrowsing(array $analyzedSqlResults, ?bool $findRealEnd): bool
  352. {
  353. return ! $analyzedSqlResults['is_group']
  354. && ! $analyzedSqlResults['is_func']
  355. && empty($analyzedSqlResults['union'])
  356. && empty($analyzedSqlResults['distinct'])
  357. && $analyzedSqlResults['select_from']
  358. && (count($analyzedSqlResults['select_tables']) === 1)
  359. && (empty($analyzedSqlResults['statement']->where)
  360. || (count($analyzedSqlResults['statement']->where) === 1
  361. && $analyzedSqlResults['statement']->where[0]->expr === '1'))
  362. && empty($analyzedSqlResults['group'])
  363. && ! isset($findRealEnd)
  364. && ! $analyzedSqlResults['is_subquery']
  365. && ! $analyzedSqlResults['join']
  366. && empty($analyzedSqlResults['having']);
  367. }
  368. /**
  369. * Function to check whether the related transformation information should be deleted
  370. *
  371. * @param array $analyzedSqlResults the analyzed query and other variables set
  372. * after analyzing the query
  373. */
  374. private function isDeleteTransformationInfo(array $analyzedSqlResults): bool
  375. {
  376. return ! empty($analyzedSqlResults['querytype'])
  377. && (($analyzedSqlResults['querytype'] === 'ALTER')
  378. || ($analyzedSqlResults['querytype'] === 'DROP'));
  379. }
  380. /**
  381. * Function to check whether the user has rights to drop the database
  382. *
  383. * @param array $analyzedSqlResults the analyzed query and other variables set
  384. * after analyzing the query
  385. * @param bool $allowUserDropDatabase whether the user is allowed to drop db
  386. * @param bool $isSuperUser whether this user is a superuser
  387. */
  388. public function hasNoRightsToDropDatabase(
  389. array $analyzedSqlResults,
  390. $allowUserDropDatabase,
  391. $isSuperUser
  392. ): bool {
  393. return ! $allowUserDropDatabase
  394. && isset($analyzedSqlResults['drop_database'])
  395. && $analyzedSqlResults['drop_database']
  396. && ! $isSuperUser;
  397. }
  398. /**
  399. * Function to set a column property
  400. *
  401. * @param Table $table Table instance
  402. * @param string $requestIndex col_order|col_visib
  403. *
  404. * @return bool|Message
  405. */
  406. public function setColumnProperty(Table $table, string $requestIndex)
  407. {
  408. $propertyValue = array_map('intval', explode(',', $_POST[$requestIndex]));
  409. switch ($requestIndex) {
  410. case 'col_order':
  411. $propertyToSet = Table::PROP_COLUMN_ORDER;
  412. break;
  413. case 'col_visib':
  414. $propertyToSet = Table::PROP_COLUMN_VISIB;
  415. break;
  416. default:
  417. $propertyToSet = '';
  418. }
  419. return $table->setUiProp($propertyToSet, $propertyValue, $_POST['table_create_time'] ?? null);
  420. }
  421. /**
  422. * Function to find the real end of rows
  423. *
  424. * @param string $db the current database
  425. * @param string $table the current table
  426. *
  427. * @return mixed the number of rows if "retain" param is true, otherwise true
  428. */
  429. public function findRealEndOfRows($db, $table)
  430. {
  431. $unlimNumRows = $this->dbi->getTable($db, $table)->countRecords(true);
  432. $_SESSION['tmpval']['pos'] = $this->getStartPosToDisplayRow($unlimNumRows);
  433. return $unlimNumRows;
  434. }
  435. /**
  436. * Function to get the default sql query for browsing page
  437. *
  438. * @param string $db the current database
  439. * @param string $table the current table
  440. *
  441. * @return string the default $sql_query for browse page
  442. */
  443. public function getDefaultSqlQueryForBrowse($db, $table): string
  444. {
  445. $bookmark = Bookmark::get($this->dbi, $GLOBALS['cfg']['Server']['user'], $db, $table, 'label', false, true);
  446. if ($bookmark !== null && $bookmark->getQuery() !== '') {
  447. $GLOBALS['using_bookmark_message'] = Message::notice(
  448. __('Using bookmark "%s" as default browse query.')
  449. );
  450. $GLOBALS['using_bookmark_message']->addParam($table);
  451. $GLOBALS['using_bookmark_message']->addHtml(
  452. MySQLDocumentation::showDocumentation('faq', 'faq6-22')
  453. );
  454. return $bookmark->getQuery();
  455. }
  456. $defaultOrderByClause = '';
  457. if (
  458. isset($GLOBALS['cfg']['TablePrimaryKeyOrder'])
  459. && ($GLOBALS['cfg']['TablePrimaryKeyOrder'] !== 'NONE')
  460. ) {
  461. $primaryKey = null;
  462. $primary = Index::getPrimary($table, $db);
  463. if ($primary !== false) {
  464. $primarycols = $primary->getColumns();
  465. foreach ($primarycols as $col) {
  466. $primaryKey = $col->getName();
  467. break;
  468. }
  469. if ($primaryKey !== null) {
  470. $defaultOrderByClause = ' ORDER BY '
  471. . Util::backquote($table) . '.'
  472. . Util::backquote($primaryKey) . ' '
  473. . $GLOBALS['cfg']['TablePrimaryKeyOrder'];
  474. }
  475. }
  476. }
  477. return 'SELECT * FROM ' . Util::backquote($table) . $defaultOrderByClause;
  478. }
  479. /**
  480. * Responds an error when an error happens when executing the query
  481. *
  482. * @param bool $isGotoFile whether goto file or not
  483. * @param string $error error after executing the query
  484. * @param string $fullSqlQuery full sql query
  485. */
  486. private function handleQueryExecuteError($isGotoFile, $error, $fullSqlQuery): void
  487. {
  488. if ($isGotoFile) {
  489. $message = Message::rawError($error);
  490. $response = ResponseRenderer::getInstance();
  491. $response->setRequestStatus(false);
  492. $response->addJSON('message', $message);
  493. } else {
  494. Generator::mysqlDie($error, $fullSqlQuery, false);
  495. }
  496. exit;
  497. }
  498. /**
  499. * Function to store the query as a bookmark
  500. *
  501. * @param string $db the current database
  502. * @param string $bookmarkUser the bookmarking user
  503. * @param string $sqlQueryForBookmark the query to be stored in bookmark
  504. * @param string $bookmarkLabel bookmark label
  505. * @param bool $bookmarkReplace whether to replace existing bookmarks
  506. */
  507. public function storeTheQueryAsBookmark(
  508. ?BookmarkFeature $bookmarkFeature,
  509. $db,
  510. $bookmarkUser,
  511. $sqlQueryForBookmark,
  512. $bookmarkLabel,
  513. bool $bookmarkReplace
  514. ): void {
  515. $bfields = [
  516. 'bkm_database' => $db,
  517. 'bkm_user' => $bookmarkUser,
  518. 'bkm_sql_query' => $sqlQueryForBookmark,
  519. 'bkm_label' => $bookmarkLabel,
  520. ];
  521. // Should we replace bookmark?
  522. if ($bookmarkReplace && $bookmarkFeature !== null) {
  523. $bookmarks = Bookmark::getList($bookmarkFeature, $this->dbi, $GLOBALS['cfg']['Server']['user'], $db);
  524. foreach ($bookmarks as $bookmark) {
  525. if ($bookmark->getLabel() != $bookmarkLabel) {
  526. continue;
  527. }
  528. $bookmark->delete();
  529. }
  530. }
  531. $bookmark = Bookmark::createBookmark(
  532. $this->dbi,
  533. $bfields,
  534. isset($_POST['bkm_all_users'])
  535. );
  536. if ($bookmark === false) {
  537. return;
  538. }
  539. $bookmark->save();
  540. }
  541. /**
  542. * Function to get the affected or changed number of rows after executing a query
  543. *
  544. * @param bool $isAffected whether the query affected a table
  545. * @param ResultInterface|false $result results of executing the query
  546. *
  547. * @return int|string number of rows affected or changed
  548. * @psalm-return int|numeric-string
  549. */
  550. private function getNumberOfRowsAffectedOrChanged($isAffected, $result)
  551. {
  552. if ($isAffected) {
  553. return $this->dbi->affectedRows();
  554. }
  555. if ($result) {
  556. return $result->numRows();
  557. }
  558. return 0;
  559. }
  560. /**
  561. * Checks if the current database has changed
  562. * This could happen if the user sends a query like "USE `database`;"
  563. *
  564. * @param string $db the database in the query
  565. *
  566. * @return bool whether to reload the navigation(1) or not(0)
  567. */
  568. private function hasCurrentDbChanged(string $db): bool
  569. {
  570. if ($db === '') {
  571. return false;
  572. }
  573. $currentDb = $this->dbi->fetchValue('SELECT DATABASE()');
  574. // $current_db is false, except when a USE statement was sent
  575. return ($currentDb != false) && ($db !== $currentDb);
  576. }
  577. /**
  578. * If a table, database or column gets dropped, clean comments.
  579. *
  580. * @param string $db current database
  581. * @param string $table current table
  582. * @param string|null $column current column
  583. * @param bool $purge whether purge set or not
  584. */
  585. private function cleanupRelations(string $db, string $table, ?string $column, bool $purge): void
  586. {
  587. if (! $purge || $db === '') {
  588. return;
  589. }
  590. if ($table !== '') {
  591. if ($column !== null && $column !== '') {
  592. $this->relationCleanup->column($db, $table, $column);
  593. } else {
  594. $this->relationCleanup->table($db, $table);
  595. }
  596. } else {
  597. $this->relationCleanup->database($db);
  598. }
  599. }
  600. /**
  601. * Function to count the total number of rows for the same 'SELECT' query without
  602. * the 'LIMIT' clause that may have been programmatically added
  603. *
  604. * @param int|string $numRows number of rows affected/changed by the query
  605. * @param bool $justBrowsing whether just browsing or not
  606. * @param string $db the current database
  607. * @param string $table the current table
  608. * @param array $analyzedSqlResults the analyzed query and other variables set after analyzing the query
  609. * @psalm-param int|numeric-string $numRows
  610. *
  611. * @return int|string unlimited number of rows
  612. * @psalm-return int|numeric-string
  613. */
  614. private function countQueryResults(
  615. $numRows,
  616. bool $justBrowsing,
  617. string $db,
  618. string $table,
  619. array $analyzedSqlResults
  620. ) {
  621. /* Shortcut for not analyzed/empty query */
  622. if ($analyzedSqlResults === []) {
  623. return 0;
  624. }
  625. if (! $this->isAppendLimitClause($analyzedSqlResults)) {
  626. // if we did not append a limit, set this to get a correct
  627. // "Showing rows..." message
  628. // $_SESSION['tmpval']['max_rows'] = 'all';
  629. $unlimNumRows = $numRows;
  630. } elseif ($_SESSION['tmpval']['max_rows'] > $numRows) {
  631. // When user has not defined a limit in query and total rows in
  632. // result are less than max_rows to display, there is no need
  633. // to count total rows for that query again
  634. $unlimNumRows = $_SESSION['tmpval']['pos'] + $numRows;
  635. } elseif ($analyzedSqlResults['querytype'] === 'SELECT' || $analyzedSqlResults['is_subquery']) {
  636. // c o u n t q u e r y
  637. // If we are "just browsing", there is only one table (and no join),
  638. // and no WHERE clause (or just 'WHERE 1 '),
  639. // we do a quick count (which uses MaxExactCount) because
  640. // SQL_CALC_FOUND_ROWS is not quick on large InnoDB tables
  641. // However, do not count again if we did it previously
  642. // due to $find_real_end == true
  643. if ($justBrowsing) {
  644. // Get row count (is approximate for InnoDB)
  645. $unlimNumRows = $this->dbi->getTable($db, $table)->countRecords();
  646. /**
  647. * @todo Can we know at this point that this is InnoDB,
  648. * (in this case there would be no need for getting
  649. * an exact count)?
  650. */
  651. if ($unlimNumRows < $GLOBALS['cfg']['MaxExactCount']) {
  652. // Get the exact count if approximate count
  653. // is less than MaxExactCount
  654. /**
  655. * @todo In countRecords(), MaxExactCount is also verified,
  656. * so can we avoid checking it twice?
  657. */
  658. $unlimNumRows = $this->dbi->getTable($db, $table)
  659. ->countRecords(true);
  660. }
  661. } else {
  662. /** @var SelectStatement $statement */
  663. $statement = $analyzedSqlResults['statement'];
  664. $changeOrder = $analyzedSqlResults['order'] !== false;
  665. $changeLimit = $analyzedSqlResults['limit'] !== false;
  666. $changeExpression = $analyzedSqlResults['is_group'] === false
  667. && $analyzedSqlResults['distinct'] === false
  668. && $analyzedSqlResults['union'] === false
  669. && count($statement->expr) === 1;
  670. if ($changeOrder || $changeLimit || $changeExpression) {
  671. $statement = clone $statement;
  672. }
  673. // Remove ORDER BY to decrease unnecessary sorting time
  674. $statement->order = null;
  675. // Removes LIMIT clause that might have been added
  676. $statement->limit = null;
  677. if ($changeExpression) {
  678. $statement->expr[0] = new Expression();
  679. $statement->expr[0]->expr = '1';
  680. }
  681. $countQuery = 'SELECT COUNT(*) FROM (' . $statement->build() . ' ) as cnt';
  682. $unlimNumRows = $this->dbi->fetchValue($countQuery);
  683. if ($unlimNumRows === false) {
  684. $unlimNumRows = 0;
  685. }
  686. }
  687. } else {// not $is_select
  688. $unlimNumRows = 0;
  689. }
  690. return $unlimNumRows;
  691. }
  692. /**
  693. * Function to handle all aspects relating to executing the query
  694. *
  695. * @param array $analyzedSqlResults analyzed sql results
  696. * @param string $fullSqlQuery full sql query
  697. * @param bool $isGotoFile whether to go to a file
  698. * @param string $db current database
  699. * @param string|null $table current table
  700. * @param bool|null $findRealEnd whether to find the real end
  701. * @param string|null $sqlQueryForBookmark sql query to be stored as bookmark
  702. * @param array|null $extraData extra data
  703. *
  704. * @psalm-return array{
  705. * ResultInterface|false|null,
  706. * int|numeric-string,
  707. * int|numeric-string,
  708. * array<string, string>|null,
  709. * array|null
  710. * }
  711. */
  712. private function executeTheQuery(
  713. array $analyzedSqlResults,
  714. $fullSqlQuery,
  715. $isGotoFile,
  716. string $db,
  717. ?string $table,
  718. ?bool $findRealEnd,
  719. ?string $sqlQueryForBookmark,
  720. $extraData
  721. ): array {
  722. $response = ResponseRenderer::getInstance();
  723. $response->getHeader()->getMenu()->setTable($table ?? '');
  724. // Only if we ask to see the php code
  725. if (isset($GLOBALS['show_as_php'])) {
  726. $result = null;
  727. $numRows = 0;
  728. $unlimNumRows = 0;
  729. $profilingResults = null;
  730. } else { // If we don't ask to see the php code
  731. Profiling::enable($this->dbi);
  732. if (! defined('TESTSUITE')) {
  733. // close session in case the query takes too long
  734. session_write_close();
  735. }
  736. $result = $this->dbi->tryQuery($fullSqlQuery);
  737. $GLOBALS['querytime'] = $this->dbi->lastQueryExecutionTime;
  738. if (! defined('TESTSUITE')) {
  739. // reopen session
  740. session_start();
  741. }
  742. // Displays an error message if required and stop parsing the script
  743. $error = $this->dbi->getError();
  744. if ($error && $GLOBALS['cfg']['IgnoreMultiSubmitErrors']) {
  745. $extraData['error'] = $error;
  746. } elseif ($error) {
  747. $this->handleQueryExecuteError($isGotoFile, $error, $fullSqlQuery);
  748. }
  749. // If there are no errors and bookmarklabel was given,
  750. // store the query as a bookmark
  751. if (! empty($_POST['bkm_label']) && $sqlQueryForBookmark) {
  752. $bookmarkFeature = $this->relation->getRelationParameters()->bookmarkFeature;
  753. $this->storeTheQueryAsBookmark(
  754. $bookmarkFeature,
  755. $db,
  756. $bookmarkFeature !== null ? $GLOBALS['cfg']['Server']['user'] : '',
  757. $sqlQueryForBookmark,
  758. $_POST['bkm_label'],
  759. isset($_POST['bkm_replace'])
  760. );
  761. }
  762. // Gets the number of rows affected/returned
  763. // (This must be done immediately after the query because
  764. // mysql_affected_rows() reports about the last query done)
  765. $numRows = $this->getNumberOfRowsAffectedOrChanged($analyzedSqlResults['is_affected'], $result);
  766. $profilingResults = Profiling::getInformation($this->dbi);
  767. $justBrowsing = self::isJustBrowsing($analyzedSqlResults, $findRealEnd ?? null);
  768. $unlimNumRows = $this->countQueryResults($numRows, $justBrowsing, $db, $table ?? '', $analyzedSqlResults);
  769. $this->cleanupRelations($db, $table ?? '', $_POST['dropped_column'] ?? null, ! empty($_POST['purge']));
  770. if (
  771. isset($_POST['dropped_column'])
  772. && $db !== '' && $table !== null && $table !== ''
  773. ) {
  774. // to refresh the list of indexes (Ajax mode)
  775. $indexes = Index::getFromTable($table, $db);
  776. $indexesDuplicates = Index::findDuplicates($table, $db);
  777. $template = new Template();
  778. $extraData['indexes_list'] = $template->render('indexes', [
  779. 'url_params' => $GLOBALS['urlParams'],
  780. 'indexes' => $indexes,
  781. 'indexes_duplicates' => $indexesDuplicates,
  782. ]);
  783. }
  784. }
  785. return [
  786. $result,
  787. $numRows,
  788. $unlimNumRows,
  789. $profilingResults,
  790. $extraData,
  791. ];
  792. }
  793. /**
  794. * Delete related transformation information
  795. *
  796. * @param string $db current database
  797. * @param string $table current table
  798. * @param array $analyzedSqlResults analyzed sql results
  799. */
  800. private function deleteTransformationInfo(string $db, string $table, array $analyzedSqlResults): void
  801. {
  802. if (! isset($analyzedSqlResults['statement'])) {
  803. return;
  804. }
  805. $statement = $analyzedSqlResults['statement'];
  806. if ($statement instanceof AlterStatement) {
  807. if (
  808. ! empty($statement->altered[0])
  809. && $statement->altered[0]->options->has('DROP')
  810. && ! empty($statement->altered[0]->field->column)
  811. ) {
  812. $this->transformations->clear($db, $table, $statement->altered[0]->field->column);
  813. }
  814. } elseif ($statement instanceof DropStatement) {
  815. $this->transformations->clear($db, $table);
  816. }
  817. }
  818. /**
  819. * Function to get the message for the no rows returned case
  820. *
  821. * @param string|null $messageToShow message to show
  822. * @param array $analyzedSqlResults analyzed sql results
  823. * @param int|string $numRows number of rows
  824. */
  825. private function getMessageForNoRowsReturned(
  826. ?string $messageToShow,
  827. array $analyzedSqlResults,
  828. $numRows
  829. ): Message {
  830. if ($analyzedSqlResults['querytype'] === 'DELETE') {
  831. $message = Message::getMessageForDeletedRows($numRows);
  832. } elseif ($analyzedSqlResults['is_insert']) {
  833. if ($analyzedSqlResults['querytype'] === 'REPLACE') {
  834. // For REPLACE we get DELETED + INSERTED row count,
  835. // so we have to call it affected
  836. $message = Message::getMessageForAffectedRows($numRows);
  837. } else {
  838. $message = Message::getMessageForInsertedRows($numRows);
  839. }
  840. $insertId = $this->dbi->insertId();
  841. if ($insertId !== 0) {
  842. // insert_id is id of FIRST record inserted in one insert,
  843. // so if we inserted multiple rows, we had to increment this
  844. $message->addText('[br]');
  845. // need to use a temporary because the Message class
  846. // currently supports adding parameters only to the first
  847. // message
  848. $inserted = Message::notice(__('Inserted row id: %1$d'));
  849. $inserted->addParam($insertId + $numRows - 1);
  850. $message->addMessage($inserted);
  851. }
  852. } elseif ($analyzedSqlResults['is_affected']) {
  853. $message = Message::getMessageForAffectedRows($numRows);
  854. // Ok, here is an explanation for the !$is_select.
  855. // The form generated by PhpMyAdmin\SqlQueryForm
  856. // and /database/sql has many submit buttons
  857. // on the same form, and some confusion arises from the
  858. // fact that $message_to_show is sent for every case.
  859. // The $message_to_show containing a success message and sent with
  860. // the form should not have priority over errors
  861. } elseif ($messageToShow && $analyzedSqlResults['querytype'] !== 'SELECT') {
  862. $message = Message::rawSuccess(htmlspecialchars($messageToShow));
  863. } elseif (! empty($GLOBALS['show_as_php'])) {
  864. $message = Message::success(__('Showing as PHP code'));
  865. } elseif (isset($GLOBALS['show_as_php'])) {
  866. /* User disable showing as PHP, query is only displayed */
  867. $message = Message::notice(__('Showing SQL query'));
  868. } else {
  869. $message = Message::success(
  870. __('MySQL returned an empty result set (i.e. zero rows).')
  871. );
  872. }
  873. if (isset($GLOBALS['querytime'])) {
  874. $queryTime = Message::notice(
  875. '(' . __('Query took %01.4f seconds.') . ')'
  876. );
  877. $queryTime->addParam($GLOBALS['querytime']);
  878. $message->addMessage($queryTime);
  879. }
  880. // In case of ROLLBACK, notify the user.
  881. if (isset($_POST['rollback_query'])) {
  882. $message->addText(__('[ROLLBACK occurred.]'));
  883. }
  884. return $message;
  885. }
  886. /**
  887. * Function to respond back when the query returns zero rows
  888. * This method is called
  889. * 1-> When browsing an empty table
  890. * 2-> When executing a query on a non empty table which returns zero results
  891. * 3-> When executing a query on an empty table
  892. * 4-> When executing an INSERT, UPDATE, DELETE query from the SQL tab
  893. * 5-> When deleting a row from BROWSE tab
  894. * 6-> When searching using the SEARCH tab which returns zero results
  895. * 7-> When changing the structure of the table except change operation
  896. *
  897. * @param array $analyzedSqlResults analyzed sql results
  898. * @param string $db current database
  899. * @param string|null $table current table
  900. * @param string|null $messageToShow message to show
  901. * @param int|string $numRows number of rows
  902. * @param DisplayResults $displayResultsObject DisplayResult instance
  903. * @param array|null $extraData extra data
  904. * @param array|null $profilingResults profiling results
  905. * @param ResultInterface|false|null $result executed query results
  906. * @param string $sqlQuery sql query
  907. * @param string|null $completeQuery complete sql query
  908. * @psalm-param int|numeric-string $numRows
  909. *
  910. * @return string html
  911. */
  912. private function getQueryResponseForNoResultsReturned(
  913. array $analyzedSqlResults,
  914. string $db,
  915. ?string $table,
  916. ?string $messageToShow,
  917. $numRows,
  918. $displayResultsObject,
  919. ?array $extraData,
  920. ?array $profilingResults,
  921. $result,
  922. $sqlQuery,
  923. ?string $completeQuery
  924. ): string {
  925. if ($this->isDeleteTransformationInfo($analyzedSqlResults)) {
  926. $this->deleteTransformationInfo($db, $table ?? '', $analyzedSqlResults);
  927. }
  928. if (isset($extraData['error'])) {
  929. $message = Message::rawError($extraData['error']);
  930. } else {
  931. $message = $this->getMessageForNoRowsReturned($messageToShow, $analyzedSqlResults, $numRows);
  932. }
  933. $queryMessage = Generator::getMessage($message, $sqlQuery, 'success');
  934. if (isset($GLOBALS['show_as_php'])) {
  935. return $queryMessage;
  936. }
  937. if (! empty($GLOBALS['reload'])) {
  938. $extraData['reload'] = 1;
  939. $extraData['db'] = $GLOBALS['db'];
  940. }
  941. // For ajax requests add message and sql_query as JSON
  942. if (empty($_REQUEST['ajax_page_request'])) {
  943. $extraData['message'] = $message;
  944. if ($GLOBALS['cfg']['ShowSQL']) {
  945. $extraData['sql_query'] = $queryMessage;
  946. }
  947. }
  948. $response = ResponseRenderer::getInstance();
  949. $response->addJSON($extraData ?? []);
  950. if (($result instanceof ResultInterface && $result->numFields() === 0) || isset($extraData['error'])) {
  951. return $queryMessage;
  952. }
  953. $displayParts = [
  954. 'edit_lnk' => null,
  955. 'del_lnk' => null,
  956. 'sort_lnk' => '1',
  957. 'nav_bar' => '0',
  958. 'bkm_form' => '1',
  959. 'text_btn' => '1',
  960. 'pview_lnk' => '1',
  961. ];
  962. $sqlQueryResultsTable = $this->getHtmlForSqlQueryResultsTable(
  963. $displayResultsObject,
  964. $displayParts,
  965. false,
  966. 0,
  967. $numRows,
  968. null,
  969. $result,
  970. $analyzedSqlResults,
  971. true
  972. );
  973. $profilingChart = '';
  974. if ($profilingResults !== null) {
  975. $header = $response->getHeader();
  976. $scripts = $header->getScripts();
  977. $scripts->addFile('sql.js');
  978. $profiling = $this->getDetailedProfilingStats($profilingResults);
  979. $profilingChart = $this->template->render('sql/profiling_chart', ['profiling' => $profiling]);
  980. }
  981. $bookmark = '';
  982. $bookmarkFeature = $this->relation->getRelationParameters()->bookmarkFeature;
  983. if (
  984. $bookmarkFeature !== null
  985. && empty($_GET['id_bookmark'])
  986. && $sqlQuery
  987. ) {
  988. $bookmark = $this->template->render('sql/bookmark', [
  989. 'db' => $db,
  990. 'goto' => Url::getFromRoute('/sql', [
  991. 'db' => $db,
  992. 'table' => $table,
  993. 'sql_query' => $sqlQuery,
  994. 'id_bookmark' => 1,
  995. ]),
  996. 'user' => $GLOBALS['cfg']['Server']['user'],
  997. 'sql_query' => $completeQuery ?? $sqlQuery,
  998. ]);
  999. }
  1000. return $this->template->render('sql/no_results_returned', [
  1001. 'message' => $queryMessage,
  1002. 'sql_query_results_table' => $sqlQueryResultsTable,
  1003. 'profiling_chart' => $profilingChart,
  1004. 'bookmark' => $bookmark,
  1005. 'db' => $db,
  1006. 'table' => $table,
  1007. 'sql_query' => $sqlQuery,
  1008. 'is_procedure' => ! empty($analyzedSqlResults['is_procedure']),
  1009. ]);
  1010. }
  1011. /**
  1012. * Function to send response for ajax grid edit
  1013. *
  1014. * @param ResultInterface $result result of the executed query
  1015. */
  1016. private function getResponseForGridEdit(ResultInterface $result): void
  1017. {
  1018. $row = $result->fetchRow();
  1019. $fieldsMeta = $this->dbi->getFieldsMeta($result);
  1020. if (isset($fieldsMeta[0]) && $fieldsMeta[0]->isBinary()) {
  1021. $row[0] = bin2hex($row[0]);
  1022. }
  1023. $response = ResponseRenderer::getInstance();
  1024. $response->addJSON('value', $row[0]);
  1025. }
  1026. /**
  1027. * Returns a message for successful creation of a bookmark or null if a bookmark
  1028. * was not created
  1029. */
  1030. private function getBookmarkCreatedMessage(): string
  1031. {
  1032. $output = '';
  1033. if (isset($_GET['label'])) {
  1034. $message = Message::success(
  1035. __('Bookmark %s has been created.')
  1036. );
  1037. $message->addParam($_GET['label']);
  1038. $output = $message->getDisplay();
  1039. }
  1040. return $output;
  1041. }
  1042. /**
  1043. * Function to get html for the sql query results table
  1044. *
  1045. * @param DisplayResults $displayResultsObject instance of DisplayResult
  1046. * @param array $displayParts the parts to display
  1047. * @param bool $editable whether the result table is
  1048. * editable or not
  1049. * @param int|string $unlimNumRows unlimited number of rows
  1050. * @param int|string $numRows number of rows
  1051. * @param array|null $showTable table definitions
  1052. * @param ResultInterface|false|null $result result of the executed query
  1053. * @param array $analyzedSqlResults analyzed sql results
  1054. * @param bool $isLimitedDisplay Show only limited operations or not
  1055. * @psalm-param int|numeric-string $unlimNumRows
  1056. * @psalm-param int|numeric-string $numRows
  1057. */
  1058. private function getHtmlForSqlQueryResultsTable(
  1059. $displayResultsObject,
  1060. array $displayParts,
  1061. $editable,
  1062. $unlimNumRows,
  1063. $numRows,
  1064. ?array $showTable,
  1065. $result,
  1066. array $analyzedSqlResults,
  1067. $isLimitedDisplay = false
  1068. ): string {
  1069. $printView = isset($_POST['printview']) && $_POST['printview'] == '1' ? '1' : null;
  1070. $tableHtml = '';
  1071. $isBrowseDistinct = ! empty($_POST['is_browse_distinct']);
  1072. if ($analyzedSqlResults['is_procedure']) {
  1073. do {
  1074. if ($result === null) {
  1075. $result = $this->dbi->storeResult();
  1076. }
  1077. if ($result === false) {
  1078. $result = null;
  1079. continue;
  1080. }
  1081. $numRows = $result->numRows();
  1082. if ($numRows > 0) {
  1083. $fieldsMeta = $this->dbi->getFieldsMeta($result);
  1084. $fieldsCount = count($fieldsMeta);
  1085. $displayResultsObject->setProperties(
  1086. $numRows,
  1087. $fieldsMeta,
  1088. $analyzedSqlResults['is_count'],
  1089. $analyzedSqlResults['is_export'],
  1090. $analyzedSqlResults['is_func'],
  1091. $analyzedSqlResults['is_analyse'],
  1092. $numRows,
  1093. $fieldsCount,
  1094. $GLOBALS['querytime'],
  1095. $GLOBALS['text_dir'],
  1096. $analyzedSqlResults['is_maint'],
  1097. $analyzedSqlResults['is_explain'],
  1098. $analyzedSqlResults['is_show'],
  1099. $showTable,
  1100. $printView,
  1101. $editable,
  1102. $isBrowseDistinct
  1103. );
  1104. $displayParts = [
  1105. 'edit_lnk' => $displayResultsObject::NO_EDIT_OR_DELETE,
  1106. 'del_lnk' => $displayResultsObject::NO_EDIT_OR_DELETE,
  1107. 'sort_lnk' => '1',
  1108. 'nav_bar' => '1',
  1109. 'bkm_form' => '1',
  1110. 'text_btn' => '1',
  1111. 'pview_lnk' => '1',
  1112. ];
  1113. $tableHtml .= $displayResultsObject->getTable(
  1114. $result,
  1115. $displayParts,
  1116. $analyzedSqlResults,
  1117. $isLimitedDisplay
  1118. );
  1119. }
  1120. $result = null;
  1121. } while ($this->dbi->moreResults() && $this->dbi->nextResult());
  1122. } else {
  1123. $fieldsMeta = [];
  1124. if (isset($result) && ! is_bool($result)) {
  1125. $fieldsMeta = $this->dbi->getFieldsMeta($result);
  1126. }
  1127. $fieldsCount = count($fieldsMeta);
  1128. $_SESSION['is_multi_query'] = false;
  1129. $displayResultsObject->setProperties(
  1130. $unlimNumRows,
  1131. $fieldsMeta,
  1132. $analyzedSqlResults['is_count'],
  1133. $analyzedSqlResults['is_export'],
  1134. $analyzedSqlResults['is_func'],
  1135. $analyzedSqlResults['is_analyse'],
  1136. $numRows,
  1137. $fieldsCount,
  1138. $GLOBALS['querytime'],
  1139. $GLOBALS['text_dir'],
  1140. $analyzedSqlResults['is_maint'],
  1141. $analyzedSqlResults['is_explain'],
  1142. $analyzedSqlResults['is_show'],
  1143. $showTable,
  1144. $printView,
  1145. $editable,
  1146. $isBrowseDistinct
  1147. );
  1148. if (! is_bool($result)) {
  1149. $tableHtml .= $displayResultsObject->getTable(
  1150. $result,
  1151. $displayParts,
  1152. $analyzedSqlResults,
  1153. $isLimitedDisplay
  1154. );
  1155. }
  1156. }
  1157. return $tableHtml;
  1158. }
  1159. /**
  1160. * Function to get html for the previous query if there is such.
  1161. *
  1162. * @param string|null $displayQuery display query
  1163. * @param bool $showSql whether to show sql
  1164. * @param array $sqlData sql data
  1165. * @param Message|string $displayMessage display message
  1166. */
  1167. private function getHtmlForPreviousUpdateQuery(
  1168. ?string $displayQuery,
  1169. bool $showSql,
  1170. array $sqlData,
  1171. $displayMessage
  1172. ): string {
  1173. $output = '';
  1174. if ($displayQuery !== null && $showSql && $sqlData === []) {
  1175. $output = Generator::getMessage($displayMessage, $displayQuery, 'success');
  1176. }
  1177. return $output;
  1178. }
  1179. /**
  1180. * To get the message if a column index is missing. If not will return null
  1181. *
  1182. * @param string|null $table current table
  1183. * @param string $database current database
  1184. * @param bool $editable whether the results table can be editable or not
  1185. * @param bool $hasUniqueKey whether there is a unique key
  1186. */
  1187. private function getMessageIfMissingColumnIndex(
  1188. ?string $table,
  1189. string $database,
  1190. bool $editable,
  1191. bool $hasUniqueKey
  1192. ): string {
  1193. if ($table === null) {
  1194. return '';
  1195. }
  1196. $output = '';
  1197. if (Utilities::isSystemSchema($database) || ! $editable) {
  1198. $output = Message::notice(
  1199. sprintf(
  1200. __(
  1201. 'Current selection does not contain a unique column.'
  1202. . ' Grid edit, checkbox, Edit, Copy and Delete features'
  1203. . ' are not available. %s'
  1204. ),
  1205. MySQLDocumentation::showDocumentation(
  1206. 'config',
  1207. 'cfg_RowActionLinksWithoutUnique'
  1208. )
  1209. )
  1210. )->getDisplay();
  1211. } elseif (! $hasUniqueKey) {
  1212. $output = Message::notice(
  1213. sprintf(
  1214. __(
  1215. 'Current selection does not contain a unique column.'
  1216. . ' Grid edit, Edit, Copy and Delete features may result in'
  1217. . ' undesired behavior. %s'
  1218. ),
  1219. MySQLDocumentation::showDocumentation(
  1220. 'config',
  1221. 'cfg_RowActionLinksWithoutUnique'
  1222. )
  1223. )
  1224. )->getDisplay();
  1225. }
  1226. return $output;
  1227. }
  1228. /**
  1229. * Function to display results when the executed query returns non empty results
  1230. *
  1231. * @param ResultInterface|false|null $result executed query results
  1232. * @param array $analyzedSqlResults analysed sql results
  1233. * @param string $db current database
  1234. * @param string|null $table current table
  1235. * @param array|null $sqlData sql data
  1236. * @param DisplayResults $displayResultsObject Instance of DisplayResults
  1237. * @param int|string $unlimNumRows unlimited number of rows
  1238. * @param int|string $numRows number of rows
  1239. * @param string|null $dispQuery display query
  1240. * @param Message|string|null $dispMessage display message
  1241. * @param array|null $profilingResults profiling results
  1242. * @param string $sqlQuery sql query
  1243. * @param string|null $completeQuery complete sql query
  1244. * @psalm-param int|numeric-string $unlimNumRows
  1245. * @psalm-param int|numeric-string $numRows
  1246. *
  1247. * @return string html
  1248. */
  1249. private function getQueryResponseForResultsReturned(
  1250. $result,
  1251. array $analyzedSqlResults,
  1252. string $db,
  1253. ?string $table,
  1254. ?array $sqlData,
  1255. $displayResultsObject,
  1256. $unlimNumRows,
  1257. $numRows,
  1258. ?string $dispQuery,
  1259. $dispMessage,
  1260. ?array $profilingResults,
  1261. $sqlQuery,
  1262. ?string $completeQuery
  1263. ): string {
  1264. global $showtable;
  1265. // If we are retrieving the full value of a truncated field or the original
  1266. // value of a transformed field, show it here
  1267. if (isset($_POST['grid_edit']) && $_POST['grid_edit'] == true && is_object($result)) {
  1268. $this->getResponseForGridEdit($result);
  1269. exit;
  1270. }
  1271. // Gets the list of fields properties
  1272. $fieldsMeta = [];
  1273. if ($result !== null && ! is_bool($result)) {
  1274. $fieldsMeta = $this->dbi->getFieldsMeta($result);
  1275. }
  1276. // Should be initialized these parameters before parsing
  1277. if (! is_array($showtable)) {
  1278. $showtable = null;
  1279. }
  1280. $response = ResponseRenderer::getInstance();
  1281. $header = $response->getHeader();
  1282. $scripts = $header->getScripts();
  1283. $justOneTable = $this->resultSetHasJustOneTable($fieldsMeta);
  1284. // hide edit and delete links:
  1285. // - for information_schema
  1286. // - if the result set does not contain all the columns of a unique key
  1287. // (unless this is an updatable view)
  1288. // - if the SELECT query contains a join or a subquery
  1289. $updatableView = false;
  1290. $statement = $analyzedSqlResults['statement'] ?? null;
  1291. if ($statement instanceof SelectStatement) {
  1292. if ($statement->expr && $statement->expr[0]->expr === '*' && $table) {
  1293. $_table = new Table($table, $db);
  1294. $updatableView = $_table->isUpdatableView();
  1295. }
  1296. if (
  1297. $analyzedSqlResults['join']
  1298. || $analyzedSqlResults['is_subquery']
  1299. || count($analyzedSqlResults['select_tables']) !== 1
  1300. ) {
  1301. $justOneTable = false;
  1302. }
  1303. }
  1304. $hasUnique = $table !== null && $this->resultSetContainsUniqueKey($db, $table, $fieldsMeta);
  1305. $editable = ($hasUnique
  1306. || $GLOBALS['cfg']['RowActionLinksWithoutUnique']
  1307. || $updatableView)
  1308. && $justOneTable
  1309. && ! Utilities::isSystemSchema($db);
  1310. $_SESSION['tmpval']['possible_as_geometry'] = $editable;
  1311. $displayParts = [
  1312. 'edit_lnk' => $displayResultsObject::UPDATE_ROW,
  1313. 'del_lnk' => $displayResultsObject::DELETE_ROW,
  1314. 'sort_lnk' => '1',
  1315. 'nav_bar' => '1',
  1316. 'bkm_form' => '1',
  1317. 'text_btn' => '0',
  1318. 'pview_lnk' => '1',
  1319. ];
  1320. if (! $editable) {
  1321. $displayParts = [
  1322. 'edit_lnk' => $displayResultsObject::NO_EDIT_OR_DELETE,
  1323. 'del_lnk' => $displayResultsObject::NO_EDIT_OR_DELETE,
  1324. 'sort_lnk' => '1',
  1325. 'nav_bar' => '1',
  1326. 'bkm_form' => '1',
  1327. 'text_btn' => '1',
  1328. 'pview_lnk' => '1',
  1329. ];
  1330. }
  1331. if (isset($_POST['printview']) && $_POST['printview'] == '1') {
  1332. $displayParts = [
  1333. 'edit_lnk' => $displayResultsObject::NO_EDIT_OR_DELETE,
  1334. 'del_lnk' => $displayResultsObject::NO_EDIT_OR_DELETE,
  1335. 'sort_lnk' => '0',
  1336. 'nav_bar' => '0',
  1337. 'bkm_form' => '0',
  1338. 'text_btn' => '0',
  1339. 'pview_lnk' => '0',
  1340. ];
  1341. }
  1342. if (! isset($_POST['printview']) || $_POST['printview'] != '1') {
  1343. $scripts->addFile('makegrid.js');
  1344. $scripts->addFile('sql.js');
  1345. unset($GLOBALS['message']);
  1346. //we don't need to buffer the output in getMessage here.
  1347. //set a global variable and check against it in the function
  1348. $GLOBALS['buffer_message'] = false;
  1349. }
  1350. $previousUpdateQueryHtml = $this->getHtmlForPreviousUpdateQuery(
  1351. $dispQuery,
  1352. (bool) $GLOBALS['cfg']['ShowSQL'],
  1353. $sqlData ?? [],
  1354. $dispMessage ?? ''
  1355. );
  1356. $profilingChartHtml = '';
  1357. if ($profilingResults) {
  1358. $profiling = $this->getDetailedProfilingStats($profilingResults);
  1359. $profilingChartHtml = $this->template->render('sql/profiling_chart', ['profiling' => $profiling]);
  1360. }
  1361. $missingUniqueColumnMessage = $this->getMessageIfMissingColumnIndex($table, $db, $editable, $hasUnique);
  1362. $bookmarkCreatedMessage = $this->getBookmarkCreatedMessage();
  1363. $tableHtml = $this->getHtmlForSqlQueryResultsTable(
  1364. $displayResultsObject,
  1365. $displayParts,
  1366. $editable,
  1367. $unlimNumRows,
  1368. $numRows,
  1369. $showtable,
  1370. $result,
  1371. $analyzedSqlResults
  1372. );
  1373. $bookmarkSupportHtml = '';
  1374. $bookmarkFeature = $this->relation->getRelationParameters()->bookmarkFeature;
  1375. if (
  1376. $bookmarkFeature !== null
  1377. && $displayParts['bkm_form'] == '1'
  1378. && empty($_GET['id_bookmark'])
  1379. && $sqlQuery
  1380. ) {
  1381. $bookmarkSupportHtml = $this->template->render('sql/bookmark', [
  1382. 'db' => $db,
  1383. 'goto' => Url::getFromRoute('/sql', [
  1384. 'db' => $db,
  1385. 'table' => $table,
  1386. 'sql_query' => $sqlQuery,
  1387. 'id_bookmark' => 1,
  1388. ]),
  1389. 'user' => $GLOBALS['cfg']['Server']['user'],
  1390. 'sql_query' => $completeQuery ?? $sqlQuery,
  1391. ]);
  1392. }
  1393. return $this->template->render('sql/sql_query_results', [
  1394. 'previous_update_query' => $previousUpdateQueryHtml,
  1395. 'profiling_chart' => $profilingChartHtml,
  1396. 'missing_unique_column_message' => $missingUniqueColumnMessage,
  1397. 'bookmark_created_message' => $bookmarkCreatedMessage,
  1398. 'table' => $tableHtml,
  1399. 'bookmark_support' => $bookmarkSupportHtml,
  1400. ]);
  1401. }
  1402. /**
  1403. * Function to execute the query and send the response
  1404. *
  1405. * @param array|null $analyzedSqlResults analysed sql results
  1406. * @param bool $isGotoFile whether goto file or not
  1407. * @param string $db current database
  1408. * @param string|null $table current table
  1409. * @param bool|null $findRealEnd whether to find real end or not
  1410. * @param string|null $sqlQueryForBookmark the sql query to be stored as bookmark
  1411. * @param array|null $extraData extra data
  1412. * @param string|null $messageToShow message to show
  1413. * @param array|null $sqlData sql data
  1414. * @param string $goto goto page url
  1415. * @param string|null $dispQuery display query
  1416. * @param Message|string|null $dispMessage display message
  1417. * @param string $sqlQuery sql query
  1418. * @param string|null $completeQuery complete query
  1419. */
  1420. public function executeQueryAndSendQueryResponse(
  1421. $analyzedSqlResults,
  1422. $isGotoFile,
  1423. string $db,
  1424. ?string $table,
  1425. $findRealEnd,
  1426. $sqlQueryForBookmark,
  1427. $extraData,
  1428. $messageToShow,
  1429. $sqlData,
  1430. $goto,
  1431. $dispQuery,
  1432. $dispMessage,
  1433. $sqlQuery,
  1434. $completeQuery
  1435. ): string {
  1436. if ($analyzedSqlResults == null) {
  1437. // Parse and analyze the query
  1438. [
  1439. $analyzedSqlResults,
  1440. $db,
  1441. $tableFromSql,
  1442. ] = ParseAnalyze::sqlQuery($sqlQuery, $db);
  1443. $table = $tableFromSql ?: $table;
  1444. }
  1445. return $this->executeQueryAndGetQueryResponse(
  1446. $analyzedSqlResults, // analyzed_sql_results
  1447. $isGotoFile, // is_gotofile
  1448. $db, // db
  1449. $table, // table
  1450. $findRealEnd, // find_real_end
  1451. $sqlQueryForBookmark, // sql_query_for_bookmark
  1452. $extraData, // extra_data
  1453. $messageToShow, // message_to_show
  1454. $sqlData, // sql_data
  1455. $goto, // goto
  1456. $dispQuery, // disp_query
  1457. $dispMessage, // disp_message
  1458. $sqlQuery, // sql_query
  1459. $completeQuery // complete_query
  1460. );
  1461. }
  1462. /**
  1463. * Function to execute the query and send the response
  1464. *
  1465. * @param array $analyzedSqlResults analysed sql results
  1466. * @param bool $isGotoFile whether goto file or not
  1467. * @param string $db current database
  1468. * @param string|null $table current table
  1469. * @param bool|null $findRealEnd whether to find real end or not
  1470. * @param string|null $sqlQueryForBookmark the sql query to be stored as bookmark
  1471. * @param array|null $extraData extra data
  1472. * @param string|null $messageToShow message to show
  1473. * @param array|null $sqlData sql data
  1474. * @param string $goto goto page url
  1475. * @param string|null $dispQuery display query
  1476. * @param Message|string|null $dispMessage display message
  1477. * @param string $sqlQuery sql query
  1478. * @param string|null $completeQuery complete query
  1479. *
  1480. * @return string html
  1481. */
  1482. public function executeQueryAndGetQueryResponse(
  1483. array $analyzedSqlResults,
  1484. $isGotoFile,
  1485. string $db,
  1486. ?string $table,
  1487. $findRealEnd,
  1488. ?string $sqlQueryForBookmark,
  1489. $extraData,
  1490. ?string $messageToShow,
  1491. $sqlData,
  1492. $goto,
  1493. ?string $dispQuery,
  1494. $dispMessage,
  1495. $sqlQuery,
  1496. ?string $completeQuery
  1497. ): string {
  1498. // Handle disable/enable foreign key checks
  1499. $defaultFkCheck = ForeignKey::handleDisableCheckInit();
  1500. // Handle remembered sorting order, only for single table query.
  1501. // Handling is not required when it's a union query
  1502. // (the parser never sets the 'union' key to 0).
  1503. // Handling is also not required if we came from the "Sort by key"
  1504. // drop-down.
  1505. if (
  1506. $analyzedSqlResults !== []
  1507. && $this->isRememberSortingOrder($analyzedSqlResults)
  1508. && empty($analyzedSqlResults['union'])
  1509. && ! isset($_POST['sort_by_key'])
  1510. ) {
  1511. if (! isset($_SESSION['sql_from_query_box'])) {
  1512. $this->handleSortOrder($db, $table, $analyzedSqlResults, $sqlQuery);
  1513. } else {
  1514. unset($_SESSION['sql_from_query_box']);
  1515. }
  1516. }
  1517. $displayResultsObject = new DisplayResults(
  1518. $GLOBALS['dbi'],
  1519. $GLOBALS['db'],
  1520. $GLOBALS['table'],
  1521. $GLOBALS['server'],
  1522. $goto,
  1523. $sqlQuery
  1524. );
  1525. $displayResultsObject->setConfigParamsForDisplayTable($analyzedSqlResults);
  1526. // assign default full_sql_query
  1527. $fullSqlQuery = $sqlQuery;
  1528. // Do append a "LIMIT" clause?
  1529. if ($this->isAppendLimitClause($analyzedSqlResults)) {
  1530. $fullSqlQuery = $this->getSqlWithLimitClause($analyzedSqlResults);
  1531. }
  1532. $GLOBALS['reload'] = $this->hasCurrentDbChanged($db);
  1533. $this->dbi->selectDb($db);
  1534. [
  1535. $result,
  1536. $numRows,
  1537. $unlimNumRows,
  1538. $profilingResults,
  1539. $extraData,
  1540. ] = $this->executeTheQuery(
  1541. $analyzedSqlResults,
  1542. $fullSqlQuery,
  1543. $isGotoFile,
  1544. $db,
  1545. $table,
  1546. $findRealEnd,
  1547. $sqlQueryForBookmark,
  1548. $extraData
  1549. );
  1550. $warningMessages = $this->operations->getWarningMessagesArray();
  1551. // No rows returned -> move back to the calling page
  1552. if (($numRows == 0 && $unlimNumRows == 0) || $analyzedSqlResults['is_affected']) {
  1553. $htmlOutput = $this->getQueryResponseForNoResultsReturned(
  1554. $analyzedSqlResults,
  1555. $db,
  1556. $table,
  1557. $messageToShow,
  1558. $numRows,
  1559. $displayResultsObject,
  1560. $extraData,
  1561. $profilingResults,
  1562. $result,
  1563. $sqlQuery,
  1564. $completeQuery
  1565. );
  1566. } else {
  1567. // At least one row is returned -> displays a table with results
  1568. $htmlOutput = $this->getQueryResponseForResultsReturned(
  1569. $result,
  1570. $analyzedSqlResults,
  1571. $db,
  1572. $table,
  1573. $sqlData,
  1574. $displayResultsObject,
  1575. $unlimNumRows,
  1576. $numRows,
  1577. $dispQuery,
  1578. $dispMessage,
  1579. $profilingResults,
  1580. $sqlQuery,
  1581. $completeQuery
  1582. );
  1583. }
  1584. // Handle disable/enable foreign key checks
  1585. ForeignKey::handleDisableCheckCleanup($defaultFkCheck);
  1586. foreach ($warningMessages as $warning) {
  1587. $message = Message::notice(Message::sanitize($warning));
  1588. $htmlOutput .= $message->getDisplay();
  1589. }
  1590. return $htmlOutput;
  1591. }
  1592. /**
  1593. * Function to define pos to display a row
  1594. *
  1595. * @param int $numberOfLine Number of the line to display
  1596. *
  1597. * @return int Start position to display the line
  1598. */
  1599. private function getStartPosToDisplayRow($numberOfLine)
  1600. {
  1601. $maxRows = $_SESSION['tmpval']['max_rows'];
  1602. return @((int) ceil($numberOfLine / $maxRows) - 1) * $maxRows;
  1603. }
  1604. /**
  1605. * Function to calculate new pos if pos is higher than number of rows
  1606. * of displayed table
  1607. *
  1608. * @param string $db Database name
  1609. * @param string $table Table name
  1610. * @param int|null $pos Initial position
  1611. *
  1612. * @return int Number of pos to display last page
  1613. */
  1614. public function calculatePosForLastPage($db, $table, $pos)
  1615. {
  1616. if ($pos === null) {
  1617. $pos = $_SESSION['tmpval']['pos'];
  1618. }
  1619. $tableObject = new Table($table, $db);
  1620. $unlimNumRows = $tableObject->countRecords(true);
  1621. //If position is higher than number of rows
  1622. if ($unlimNumRows <= $pos && $pos != 0) {
  1623. $pos = $this->getStartPosToDisplayRow($unlimNumRows);
  1624. }
  1625. return $pos;
  1626. }
  1627. }