3 declare(strict_types
=1);
7 use PhpMyAdmin\Bookmarks\BookmarkRepository
;
8 use PhpMyAdmin\ConfigStorage\Features\BookmarkFeature
;
9 use PhpMyAdmin\ConfigStorage\Relation
;
10 use PhpMyAdmin\ConfigStorage\RelationCleanup
;
11 use PhpMyAdmin\Dbal\ResultInterface
;
12 use PhpMyAdmin\Display\DeleteLinkEnum
;
13 use PhpMyAdmin\Display\DisplayParts
;
14 use PhpMyAdmin\Display\Results
as DisplayResults
;
15 use PhpMyAdmin\Html\Generator
;
16 use PhpMyAdmin\Html\MySQLDocumentation
;
17 use PhpMyAdmin\Identifiers\DatabaseName
;
18 use PhpMyAdmin\Query\Utilities
;
19 use PhpMyAdmin\SqlParser\Components\Expression
;
20 use PhpMyAdmin\SqlParser\Statements\AlterStatement
;
21 use PhpMyAdmin\SqlParser\Statements\DropStatement
;
22 use PhpMyAdmin\SqlParser\Statements\SelectStatement
;
23 use PhpMyAdmin\SqlParser\Utils\Query
;
24 use PhpMyAdmin\SqlParser\Utils\StatementInfo
;
25 use PhpMyAdmin\SqlParser\Utils\StatementType
;
26 use PhpMyAdmin\Table\Table
;
27 use PhpMyAdmin\Utils\ForeignKey
;
30 use function array_column
;
31 use function array_key_exists
;
32 use function array_keys
;
33 use function array_sum
;
39 use function htmlspecialchars
;
40 use function in_array
;
41 use function session_start
;
42 use function session_write_close
;
44 use function str_contains
;
45 use function str_replace
;
49 * Set of functions for the SQL executor
53 public function __construct(
54 private DatabaseInterface
$dbi,
55 private Relation
$relation,
56 private RelationCleanup
$relationCleanup,
57 private Transformations
$transformations,
58 private Template
$template,
59 private readonly BookmarkRepository
$bookmarkRepository,
60 private readonly Config
$config,
65 * Handle remembered sorting order, only for single table query
67 * @param string $db database name
68 * @param string $table table name
69 * @param string $fullSqlQuery SQL query
71 private function handleSortOrder(
74 StatementInfo
$statementInfo,
75 string &$fullSqlQuery,
77 if ($statementInfo->statement
=== null) {
78 return $statementInfo;
81 $tableObject = new Table($table, $db, $this->dbi
);
83 if (! $statementInfo->flags
->order
) {
84 // Retrieving the name of the column we should sort after.
85 $sortCol = $tableObject->getUiProp(Table
::PROP_SORTED_COLUMN
);
86 if (empty($sortCol)) {
87 return $statementInfo;
90 // Remove the name of the table from the retrieved field name.
91 $sortCol = str_replace(
92 Util
::backquote($table) . '.',
97 // Create the new query.
98 $fullSqlQuery = Query
::replaceClause(
99 $statementInfo->statement
,
100 $statementInfo->parser
->list,
101 'ORDER BY ' . $sortCol,
104 // TODO: Avoid reparsing the query.
105 $statementInfo = Query
::getAll($fullSqlQuery);
107 // Store the remembered table into session.
108 $tableObject->setUiProp(
109 Table
::PROP_SORTED_COLUMN
,
111 $statementInfo->statement
,
112 $statementInfo->parser
->list,
118 return $statementInfo;
122 * Append limit clause to SQL query
124 * @return string limit clause appended SQL query
126 private function getSqlWithLimitClause(StatementInfo
$statementInfo): string
128 if ($statementInfo->statement
=== null) {
132 return Query
::replaceClause(
133 $statementInfo->statement
,
134 $statementInfo->parser
->list,
135 'LIMIT ' . $_SESSION['tmpval']['pos'] . ', '
136 . $_SESSION['tmpval']['max_rows'],
141 * Verify whether the result set has columns from just one table
143 * @param mixed[] $fieldsMeta meta fields
145 private function resultSetHasJustOneTable(array $fieldsMeta): bool
147 $justOneTable = true;
149 foreach ($fieldsMeta as $oneFieldMeta) {
150 if ($oneFieldMeta->table
!= '' && $prevTable != '' && $oneFieldMeta->table
!= $prevTable) {
151 $justOneTable = false;
154 if ($oneFieldMeta->table
== '') {
158 $prevTable = $oneFieldMeta->table
;
161 return $justOneTable && $prevTable != '';
165 * Verify whether the result set contains all the columns
166 * of at least one unique key
168 * @param string $db database name
169 * @param string $table table name
170 * @param mixed[] $fieldsMeta meta fields
172 private function resultSetContainsUniqueKey(string $db, string $table, array $fieldsMeta): bool
174 $columns = $this->dbi
->getColumns($db, $table);
175 $resultSetColumnNames = [];
176 foreach ($fieldsMeta as $oneMeta) {
177 $resultSetColumnNames[] = $oneMeta->name
;
180 foreach (Index
::getFromTable($this->dbi
, $table, $db) as $index) {
181 if (! $index->isUnique()) {
185 $indexColumns = $index->getColumns();
187 foreach (array_keys($indexColumns) as $indexColumnName) {
189 ! in_array($indexColumnName, $resultSetColumnNames)
190 && array_key_exists($indexColumnName, $columns)
191 && ! str_contains($columns[$indexColumnName]->extra
, 'INVISIBLE')
199 if ($numberFound === count($indexColumns)) {
208 * Get the HTML for relational column dropdown
209 * During grid edit, if we have a relational field, returns the html for the
212 * @param string $db current database
213 * @param string $table current table
214 * @param string $column current column
215 * @param string $currentValue current selected value
217 * @return string html for the dropdown
219 public function getHtmlForRelationalColumnDropdown(
223 string $currentValue,
225 $foreigners = $this->relation
->getForeigners($db, $table, $column);
227 $foreignData = $this->relation
->getForeignData($foreigners, $column, false, '', '');
229 if ($foreignData->dispRow
=== null) {
230 //Handle the case when number of values
231 //is more than $cfg['ForeignKeyMaxLimit']
232 $urlParams = ['db' => $db, 'table' => $table, 'field' => $column];
234 return $this->template
->render('sql/relational_column_dropdown', [
235 'current_value' => $_POST['curr_value'],
236 'params' => $urlParams,
240 $dropdown = $this->relation
->foreignDropdown(
241 $foreignData->dispRow
,
242 $foreignData->foreignField
,
243 $foreignData->foreignDisplay
,
245 $this->config
->settings
['ForeignKeyMaxLimit'],
248 return '<select>' . $dropdown . '</select>';
252 * @psalm-param non-empty-list<array{Status: non-empty-string, Duration: numeric-string}> $profilingResults
254 * @psalm-return array{
256 * states: array<string, array{total_time: float, calls: int<1, max>}>,
257 * chart: array{labels: list<string>, data: list<float>},
258 * profile: list<array{status: string, duration: string, duration_raw: numeric-string}>
261 private function getDetailedProfilingStats(array $profilingResults): array
263 $totalTime = (float) array_sum(array_column($profilingResults, 'Duration'));
264 if ($totalTime === 0.0) {
270 foreach ($profilingResults as $result) {
271 $status = ucwords($result['Status']);
274 'duration' => Util
::formatNumber($result['Duration'], 3, 1),
275 'duration_raw' => $result['Duration'],
278 if (! isset($states[$status])) {
279 $states[$status] = ['total_time' => (float) $result['Duration'], 'calls' => 1];
281 $states[$status]['calls']++
;
282 $states[$status]['total_time'] +
= $result['Duration'];
287 $chart = ['labels' => array_keys($states), 'data' => array_column($states, 'total_time')];
289 return ['total_time' => $totalTime, 'states' => $states, 'chart' => $chart, 'profile' => $profile];
293 * Get value of a column for a specific row (marked by $whereClause)
295 public function getFullValuesForSetColumn(
301 $row = $this->dbi
->fetchSingleRow(sprintf(
302 'SELECT `%s` FROM `%s`.`%s` WHERE %s',
313 return $row[$column];
317 * Get all the values for a enum column or set column in a table
319 * @param string $db current database
320 * @param string $table current table
321 * @param string $columnName current column
323 * @return string[]|null array containing the value list for the column, null on failure
325 public function getValuesForColumn(string $db, string $table, string $columnName): array|
null
327 $column = $this->dbi
->getColumn($db, $table, $columnName);
329 if ($column === null) {
333 return Util
::parseEnumSetValues($column->type
, false);
337 * Function to check whether to remember the sorting order or not.
339 private function isRememberSortingOrder(StatementInfo
$statementInfo): bool
341 return $this->config
->settings
['RememberSorting']
342 && ! ($statementInfo->flags
->isCount
343 ||
$statementInfo->flags
->isExport
344 ||
$statementInfo->flags
->isFunc
345 ||
$statementInfo->flags
->isAnalyse
)
346 && $statementInfo->flags
->selectFrom
347 && ($statementInfo->selectExpressions
=== []
348 ||
(count($statementInfo->selectExpressions
) === 1
349 && $statementInfo->selectExpressions
[0] === '*'))
350 && count($statementInfo->selectTables
) === 1;
354 * Function to check whether the LIMIT clause should be appended or not.
356 private function isAppendLimitClause(StatementInfo
$statementInfo): bool
358 // Assigning LIMIT clause to an syntactically-wrong query
359 // is not needed. Also we would want to show the true query
360 // and the true error message to the query executor
362 return $statementInfo->parser
->errors
=== []
363 && $_SESSION['tmpval']['max_rows'] !== 'all'
364 && ! $statementInfo->flags
->isExport
&& ! $statementInfo->flags
->isAnalyse
365 && ($statementInfo->flags
->selectFrom ||
$statementInfo->flags
->isSubQuery
)
366 && ! $statementInfo->flags
->limit
;
370 * Function to check whether this query is for just browsing
372 public static function isJustBrowsing(StatementInfo
$statementInfo, bool $findRealEnd = false): bool
374 return ! $statementInfo->flags
->isGroup
375 && ! $statementInfo->flags
->isFunc
376 && ! $statementInfo->flags
->union
377 && ! $statementInfo->flags
->distinct
378 && $statementInfo->flags
->selectFrom
379 && (count($statementInfo->selectTables
) === 1)
380 && (empty($statementInfo->statement
->where
)
381 ||
(count($statementInfo->statement
->where
) === 1
382 && $statementInfo->statement
->where
[0]->expr
=== '1'))
383 && ! $statementInfo->flags
->group
385 && ! $statementInfo->flags
->isSubQuery
386 && ! $statementInfo->flags
->join
387 && ! $statementInfo->flags
->having
;
391 * Function to check whether the related transformation information should be deleted.
393 private function isDeleteTransformationInfo(StatementInfo
$statementInfo): bool
395 return $statementInfo->flags
->queryType
=== StatementType
::Alter
396 ||
$statementInfo->flags
->queryType
=== StatementType
::Drop
;
400 * Function to check whether the user has rights to drop the database
402 * @param bool $allowUserDropDatabase whether the user is allowed to drop db
403 * @param bool $isSuperUser whether this user is a superuser
405 public function hasNoRightsToDropDatabase(
406 StatementInfo
$statementInfo,
407 bool $allowUserDropDatabase,
410 return ! $allowUserDropDatabase && $statementInfo->flags
->dropDatabase
&& ! $isSuperUser;
414 * Function to get the default sql query for browsing page
416 * @param string $db the current database
417 * @param string $table the current table
419 * @return string the default $sql_query for browse page
421 public function getDefaultSqlQueryForBrowse(string $db, string $table): string
423 $bookmark = $this->bookmarkRepository
->getByLabel(
424 $this->config
->selectedServer
['user'],
425 DatabaseName
::from($db),
429 if ($bookmark !== null && $bookmark->getQuery() !== '') {
430 $GLOBALS['using_bookmark_message'] = Message
::notice(
431 __('Using bookmark "%s" as default browse query.'),
433 $GLOBALS['using_bookmark_message']->addParam($table);
434 $GLOBALS['using_bookmark_message']->addHtml(
435 MySQLDocumentation
::showDocumentation('faq', 'faq6-22'),
438 return $bookmark->getQuery();
441 $defaultOrderByClause = '';
444 isset($this->config
->settings
['TablePrimaryKeyOrder'])
445 && ($this->config
->settings
['TablePrimaryKeyOrder'] !== 'NONE')
448 $primary = Index
::getPrimary($this->dbi
, $table, $db);
450 if ($primary !== null) {
451 $primarycols = $primary->getColumns();
453 foreach ($primarycols as $col) {
454 $primaryKey = $col->getName();
458 if ($primaryKey !== null) {
459 $defaultOrderByClause = ' ORDER BY '
460 . Util
::backquote($table) . '.'
461 . Util
::backquote($primaryKey) . ' '
462 . $this->config
->settings
['TablePrimaryKeyOrder'];
467 return 'SELECT * FROM ' . Util
::backquote($table) . $defaultOrderByClause;
471 * Responds an error when an error happens when executing the query
473 * @param bool $isGotoFile whether goto file or not
474 * @param string $error error after executing the query
475 * @param string $fullSqlQuery full sql query
477 private function handleQueryExecuteError(bool $isGotoFile, string $error, string $fullSqlQuery): never
479 $response = ResponseRenderer
::getInstance();
481 $message = Message
::rawError($error);
482 $response->setRequestStatus(false);
483 $response->addJSON('message', $message);
485 Generator
::mysqlDie($error, $fullSqlQuery, false);
488 $response->callExit();
492 * Function to store the query as a bookmark
494 * @param string $db the current database
495 * @param string $bookmarkUser the bookmarking user
496 * @param string $sqlQueryForBookmark the query to be stored in bookmark
497 * @param string $bookmarkLabel bookmark label
498 * @param bool $bookmarkReplace whether to replace existing bookmarks
500 public function storeTheQueryAsBookmark(
501 BookmarkFeature|
null $bookmarkFeature,
503 string $bookmarkUser,
504 string $sqlQueryForBookmark,
505 string $bookmarkLabel,
506 bool $bookmarkReplace,
508 // Should we replace bookmark?
509 if ($bookmarkReplace && $bookmarkFeature !== null) {
510 $bookmarks = $this->bookmarkRepository
->getList($this->config
->selectedServer
['user'], $db);
511 foreach ($bookmarks as $bookmark) {
512 if ($bookmark->getLabel() !== $bookmarkLabel) {
520 $bookmark = $this->bookmarkRepository
->createBookmark(
521 $sqlQueryForBookmark,
525 isset($_POST['bkm_all_users']),
528 if ($bookmark === false) {
536 * Function to get the affected or changed number of rows after executing a query
538 * @param bool $isAffected whether the query affected a table
539 * @param ResultInterface|false $result results of executing the query
541 * @return int|string number of rows affected or changed
542 * @psalm-return int|numeric-string
544 private function getNumberOfRowsAffectedOrChanged(bool $isAffected, ResultInterface|
false $result): int|
string
547 return $this->dbi
->affectedRows();
551 return $result->numRows();
558 * Checks if the current database has changed
559 * This could happen if the user sends a query like "USE `database`;"
561 * @param string $db the database in the query
563 * @return bool whether to reload the navigation(1) or not(0)
565 private function hasCurrentDbChanged(string $db): bool
571 $currentDb = $this->dbi
->fetchValue('SELECT DATABASE()');
573 // $current_db is false, except when a USE statement was sent
574 return ($currentDb != false) && ($db !== $currentDb);
578 * If a table, database or column gets dropped, clean comments.
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
585 private function cleanupRelations(string $db, string $table, string|
null $column, bool $purge): void
587 if (! $purge ||
$db === '') {
592 if ($column !== null && $column !== '') {
593 $this->relationCleanup
->column($db, $table, $column);
595 $this->relationCleanup
->table($db, $table);
598 $this->relationCleanup
->database($db);
603 * Function to count the total number of rows for the same 'SELECT' query without
604 * the 'LIMIT' clause that may have been programmatically added
606 * @param int|string $numRows number of rows affected/changed by the query
607 * @param bool $justBrowsing whether just browsing or not
608 * @param string $db the current database
609 * @param string $table the current table
610 * @psalm-param int|numeric-string $numRows
612 * @return int|string unlimited number of rows
613 * @psalm-return int|numeric-string
615 private function countQueryResults(
620 StatementInfo
$statementInfo,
622 /* Shortcut for not analyzed/empty query */
623 if ($statementInfo->statement
=== null) {
627 if (! $this->isAppendLimitClause($statementInfo)) {
628 // if we did not append a limit, set this to get a correct
629 // "Showing rows..." message
630 // $_SESSION['tmpval']['max_rows'] = 'all';
631 $unlimNumRows = $numRows;
632 } elseif ($_SESSION['tmpval']['max_rows'] > $numRows) {
633 // When user has not defined a limit in query and total rows in
634 // result are less than max_rows to display, there is no need
635 // to count total rows for that query again
636 $unlimNumRows = $_SESSION['tmpval']['pos'] +
$numRows;
637 } elseif ($statementInfo->flags
->queryType
=== StatementType
::Select ||
$statementInfo->flags
->isSubQuery
) {
638 // c o u n t q u e r y
640 // If we are "just browsing", there is only one table (and no join),
641 // and no WHERE clause (or just 'WHERE 1 '),
642 // we do a quick count (which uses MaxExactCount) because
643 // SQL_CALC_FOUND_ROWS is not quick on large InnoDB tables
645 // Get row count (is approximate for InnoDB)
646 $unlimNumRows = $this->dbi
->getTable($db, $table)->countRecords();
648 * @todo Can we know at this point that this is InnoDB,
649 * (in this case there would be no need for getting
652 if ($unlimNumRows < $this->config
->settings
['MaxExactCount']) {
653 // Get the exact count if approximate count
654 // is less than MaxExactCount
656 * @todo In countRecords(), MaxExactCount is also verified,
657 * so can we avoid checking it twice?
659 $unlimNumRows = $this->dbi
->getTable($db, $table)->countRecords(true);
662 $statement = $statementInfo->statement
;
664 // Remove ORDER BY to decrease unnecessary sorting time
665 if ($statementInfo->flags
->order
) {
666 $statement->order
= null;
669 // Removes LIMIT clause that might have been added
670 if ($statementInfo->flags
->limit
) {
671 $statement->limit
= false;
675 ! $statementInfo->flags
->isGroup
676 && ! $statementInfo->flags
->distinct
677 && ! $statementInfo->flags
->union
678 && count($statement->expr
) === 1
680 $statement->expr
[0] = new Expression();
681 $statement->expr
[0]->expr
= '1';
684 $countQuery = 'SELECT COUNT(*) FROM (' . $statement->build() . ' ) as cnt';
686 $unlimNumRows = (int) $this->dbi
->fetchValue($countQuery);
688 } else {// not $is_select
692 return $unlimNumRows;
696 * Function to handle all aspects relating to executing the query
698 * @param string $fullSqlQuery full sql query
699 * @param bool $isGotoFile whether to go to a file
700 * @param string $db current database
701 * @param string|null $table current table
702 * @param string|null $sqlQueryForBookmark sql query to be stored as bookmark
704 * @psalm-return array{
705 * ResultInterface|false,
706 * int|numeric-string,
707 * int|numeric-string,
708 * list<array{Status: non-empty-string, Duration: numeric-string}>,
712 private function executeTheQuery(
713 StatementInfo
$statementInfo,
714 string $fullSqlQuery,
718 string|
null $sqlQueryForBookmark,
720 $response = ResponseRenderer
::getInstance();
721 $response->getHeader()->getMenu()->setTable($table ??
'');
723 Profiling
::enable($this->dbi
);
725 if (! defined('TESTSUITE')) {
726 // close session in case the query takes too long
727 session_write_close();
730 $result = $this->dbi
->tryQuery($fullSqlQuery);
731 $GLOBALS['querytime'] = $this->dbi
->lastQueryExecutionTime
;
733 if (! defined('TESTSUITE')) {
740 // Displays an error message if required and stop parsing the script
741 $error = $this->dbi
->getError();
742 if ($error && $this->config
->settings
['IgnoreMultiSubmitErrors']) {
743 $errorMessage = $error;
744 } elseif ($error !== '') {
745 $this->handleQueryExecuteError($isGotoFile, $error, $fullSqlQuery);
748 // If there are no errors and bookmarklabel was given,
749 // store the query as a bookmark
750 if (! empty($_POST['bkm_label']) && $sqlQueryForBookmark) {
751 $bookmarkFeature = $this->relation
->getRelationParameters()->bookmarkFeature
;
752 $this->storeTheQueryAsBookmark(
755 $bookmarkFeature !== null ?
$this->config
->selectedServer
['user'] : '',
756 $sqlQueryForBookmark,
758 isset($_POST['bkm_replace']),
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($statementInfo->flags
->isAffected
, $result);
767 $profilingResults = Profiling
::getInformation($this->dbi
);
769 $justBrowsing = self
::isJustBrowsing($statementInfo);
771 $unlimNumRows = $this->countQueryResults($numRows, $justBrowsing, $db, $table ??
'', $statementInfo);
773 $this->cleanupRelations($db, $table ??
'', $_POST['dropped_column'] ??
null, ! empty($_POST['purge']));
775 return [$result, $numRows, $unlimNumRows, $profilingResults, $errorMessage];
779 * Delete related transformation information
781 * @param string $db current database
782 * @param string $table current table
784 private function deleteTransformationInfo(string $db, string $table, StatementInfo
$statementInfo): void
786 if ($statementInfo->statement
=== null) {
790 $statement = $statementInfo->statement
;
791 if ($statement instanceof AlterStatement
) {
793 ! empty($statement->altered
[0])
794 && $statement->altered
[0]->options
->has('DROP')
795 && ! empty($statement->altered
[0]->field
->column
)
797 $this->transformations
->clear($db, $table, $statement->altered
[0]->field
->column
);
799 } elseif ($statement instanceof DropStatement
) {
800 $this->transformations
->clear($db, $table);
805 * Function to get the message for the no rows returned case
807 * @param string|null $messageToShow message to show
808 * @param int|string $numRows number of rows
810 private function getMessageForNoRowsReturned(
811 string|
null $messageToShow,
812 StatementInfo
$statementInfo,
815 if ($statementInfo->flags
->queryType
=== StatementType
::Delete
) {
816 $message = Message
::getMessageForDeletedRows($numRows);
817 } elseif ($statementInfo->flags
->isInsert
) {
818 if ($statementInfo->flags
->queryType
=== StatementType
::Replace
) {
819 // For REPLACE we get DELETED + INSERTED row count,
820 // so we have to call it affected
821 $message = Message
::getMessageForAffectedRows($numRows);
823 $message = Message
::getMessageForInsertedRows($numRows);
826 $insertId = $this->dbi
->insertId();
827 if ($insertId !== 0) {
828 // insert_id is id of FIRST record inserted in one insert,
829 // so if we inserted multiple rows, we had to increment this
830 $message->addText('[br]');
831 // need to use a temporary because the Message class
832 // currently supports adding parameters only to the first
834 $inserted = Message
::notice(__('Inserted row id: %1$d'));
835 $inserted->addParam($insertId +
$numRows - 1);
836 $message->addMessage($inserted);
838 } elseif ($statementInfo->flags
->isAffected
) {
839 $message = Message
::getMessageForAffectedRows($numRows);
841 // Ok, here is an explanation for the !$is_select.
842 // The form generated by PhpMyAdmin\SqlQueryForm
843 // and /database/sql has many submit buttons
844 // on the same form, and some confusion arises from the
845 // fact that $message_to_show is sent for every case.
846 // The $message_to_show containing a success message and sent with
847 // the form should not have priority over errors
848 } elseif ($messageToShow && $statementInfo->flags
->queryType
!== StatementType
::Select
) {
849 $message = Message
::rawSuccess(htmlspecialchars($messageToShow));
850 } elseif (! empty($GLOBALS['show_as_php'])) {
851 $message = Message
::success(__('Showing as PHP code'));
852 } elseif (isset($GLOBALS['show_as_php'])) {
853 /* User disable showing as PHP, query is only displayed */
854 $message = Message
::notice(__('Showing SQL query'));
856 $message = Message
::success(
857 __('MySQL returned an empty result set (i.e. zero rows).'),
861 if (isset($GLOBALS['querytime'])) {
862 $queryTime = Message
::notice(
863 '(' . __('Query took %01.4f seconds.') . ')',
865 $queryTime->addParam($GLOBALS['querytime']);
866 $message->addMessage($queryTime);
869 // In case of ROLLBACK, notify the user.
870 if (isset($_POST['rollback_query'])) {
871 $message->addText(__('[ROLLBACK occurred.]'));
878 * Function to respond back when the query returns zero rows
879 * This method is called
880 * 1-> When browsing an empty table
881 * 2-> When executing a query on a non empty table which returns zero results
882 * 3-> When executing a query on an empty table
883 * 4-> When executing an INSERT, UPDATE, DELETE query from the SQL tab
884 * 5-> When deleting a row from BROWSE tab
885 * 6-> When searching using the SEARCH tab which returns zero results
886 * 7-> When changing the structure of the table except change operation
888 * @param string $db current database
889 * @param string|null $table current table
890 * @param string|null $messageToShow message to show
891 * @param int|string $numRows number of rows
892 * @param DisplayResults $displayResultsObject DisplayResult instance
893 * @param string $errorMessage error message from tryQuery
894 * @param ResultInterface|false $result executed query results
895 * @param string $sqlQuery sql query
896 * @param string|null $completeQuery complete sql query
897 * @psalm-param int|numeric-string $numRows
898 * @psalm-param list<array{Status: non-empty-string, Duration: numeric-string}> $profilingResults
900 * @return string html
902 private function getQueryResponseForNoResultsReturned(
903 StatementInfo
$statementInfo,
906 string|
null $messageToShow,
908 DisplayResults
$displayResultsObject,
909 string $errorMessage,
910 array $profilingResults,
911 ResultInterface|
false $result,
913 string|
null $completeQuery,
915 if ($this->isDeleteTransformationInfo($statementInfo)) {
916 $this->deleteTransformationInfo($db, $table ??
'', $statementInfo);
919 if ($errorMessage !== '') {
920 $message = Message
::rawError($errorMessage);
922 $message = $this->getMessageForNoRowsReturned($messageToShow, $statementInfo, $numRows);
925 $queryMessage = Generator
::getMessage($message, $GLOBALS['sql_query'], 'success');
927 if (isset($GLOBALS['show_as_php'])) {
928 return $queryMessage;
932 if (! empty($GLOBALS['reload'])) {
933 $extraData['reload'] = 1;
934 $extraData['db'] = Current
::$database;
937 // For ajax requests add message and sql_query as JSON
938 if (empty($_REQUEST['ajax_page_request'])) {
939 $extraData['message'] = $message;
940 if ($this->config
->settings
['ShowSQL']) {
941 $extraData['sql_query'] = $queryMessage;
946 isset($_POST['dropped_column'])
947 && $db !== '' && $table !== null && $table !== ''
949 // to refresh the list of indexes (Ajax mode)
950 $extraData['indexes_list'] = $this->getIndexList($table, $db);
953 $response = ResponseRenderer
::getInstance();
954 $response->addJSON($extraData);
955 $header = $response->getHeader();
956 $scripts = $header->getScripts();
957 $scripts->addFile('sql.js');
959 // We can only skip result fetching if the result contains no columns.
960 if (($result instanceof ResultInterface
&& $result->numFields() === 0) ||
$result === false) {
961 return $queryMessage;
964 $displayParts = DisplayParts
::fromArray([
965 'hasEditLink' => false,
966 'deleteLink' => DeleteLinkEnum
::NO_DELETE
,
967 'hasSortLink' => true,
968 'hasNavigationBar' => false,
969 'hasBookmarkForm' => true,
970 'hasTextButton' => true,
971 'hasPrintLink' => true,
974 $sqlQueryResultsTable = $this->getHtmlForSqlQueryResultsTable(
975 $displayResultsObject,
985 $profilingChart = $this->getProfilingChart($profilingResults);
988 $bookmarkFeature = $this->relation
->getRelationParameters()->bookmarkFeature
;
990 $bookmarkFeature !== null
991 && empty($_GET['id_bookmark'])
994 $bookmark = $this->template
->render('sql/bookmark', [
996 'goto' => Url
::getFromRoute('/sql', [
999 'sql_query' => $sqlQuery,
1002 'user' => $this->config
->selectedServer
['user'],
1003 'sql_query' => $completeQuery ??
$sqlQuery,
1004 'allow_shared_bookmarks' => $this->config
->settings
['AllowSharedBookmarks'],
1008 return $this->template
->render('sql/no_results_returned', [
1009 'message' => $queryMessage,
1010 'sql_query_results_table' => $sqlQueryResultsTable,
1011 'profiling_chart' => $profilingChart,
1012 'bookmark' => $bookmark,
1015 'sql_query' => $sqlQuery,
1016 'is_procedure' => $statementInfo->flags
->isProcedure
,
1021 * Function to send response for ajax grid edit
1023 * @param ResultInterface $result result of the executed query
1025 private function getResponseForGridEdit(ResultInterface
$result): void
1027 $row = $result->fetchRow();
1028 $fieldsMeta = $this->dbi
->getFieldsMeta($result);
1030 if (isset($fieldsMeta[0]) && $fieldsMeta[0]->isBinary()) {
1031 $row[0] = bin2hex($row[0]);
1034 $response = ResponseRenderer
::getInstance();
1035 $response->addJSON('value', $row[0]);
1039 * Returns a message for successful creation of a bookmark or null if a bookmark
1042 private function getBookmarkCreatedMessage(): string
1045 if (isset($_GET['label'])) {
1046 $message = Message
::success(
1047 __('Bookmark %s has been created.'),
1049 $message->addParam($_GET['label']);
1050 $output = $message->getDisplay();
1057 * Function to get html for the sql query results table
1059 * @param DisplayResults $displayResultsObject instance of DisplayResult
1060 * @param bool $editable whether the result table is editable or not
1061 * @param int|string $unlimNumRows unlimited number of rows
1062 * @param int|string $numRows number of rows
1063 * @param ResultInterface $result result of the executed query
1064 * @param bool $isLimitedDisplay Show only limited operations or not
1065 * @psalm-param int|numeric-string $unlimNumRows
1066 * @psalm-param int|numeric-string $numRows
1068 private function getHtmlForSqlQueryResultsTable(
1069 DisplayResults
$displayResultsObject,
1070 DisplayParts
$displayParts,
1072 int|
string $unlimNumRows,
1073 int|
string $numRows,
1074 ResultInterface
$result,
1075 StatementInfo
$statementInfo,
1076 bool $isLimitedDisplay = false,
1078 $printView = isset($_POST['printview']) && $_POST['printview'] == '1';
1079 $isBrowseDistinct = ! empty($_POST['is_browse_distinct']);
1081 if ($statementInfo->flags
->isProcedure
) {
1082 return $this->getHtmlForStoredProcedureResults(
1084 $displayResultsObject,
1093 $displayResultsObject->setProperties(
1095 $this->dbi
->getFieldsMeta($result),
1096 $statementInfo->flags
->isCount
,
1097 $statementInfo->flags
->isExport
,
1098 $statementInfo->flags
->isFunc
,
1099 $statementInfo->flags
->isAnalyse
,
1101 $GLOBALS['querytime'],
1102 LanguageManager
::$textDir,
1103 $statementInfo->flags
->isMaint
,
1104 $statementInfo->flags
->queryType
=== StatementType
::Explain
,
1105 $statementInfo->flags
->queryType
=== StatementType
::Show
,
1111 return $displayResultsObject->getTable($result, $displayParts, $statementInfo, $isLimitedDisplay);
1114 private function getHtmlForStoredProcedureResults(
1115 ResultInterface
$result,
1116 DisplayResults
$displayResultsObject,
1117 StatementInfo
$statementInfo,
1120 bool $isBrowseDistinct,
1121 bool $isLimitedDisplay,
1125 while ($result !== false) {
1126 $numRows = $result->numRows();
1129 $displayResultsObject->setProperties(
1131 $this->dbi
->getFieldsMeta($result),
1132 $statementInfo->flags
->isCount
,
1133 $statementInfo->flags
->isExport
,
1134 $statementInfo->flags
->isFunc
,
1135 $statementInfo->flags
->isAnalyse
,
1137 $GLOBALS['querytime'],
1138 LanguageManager
::$textDir,
1139 $statementInfo->flags
->isMaint
,
1140 $statementInfo->flags
->queryType
=== StatementType
::Explain
,
1141 $statementInfo->flags
->queryType
=== StatementType
::Show
,
1147 $displayParts = DisplayParts
::fromArray([
1148 'hasEditLink' => false,
1149 'deleteLink' => DeleteLinkEnum
::NO_DELETE
,
1150 'hasSortLink' => true,
1151 'hasNavigationBar' => true,
1152 'hasBookmarkForm' => true,
1153 'hasTextButton' => true,
1154 'hasPrintLink' => true,
1157 $tableHtml .= $displayResultsObject->getTable(
1165 $result = $this->dbi
->nextResult();
1172 * Function to get html for the previous query if there is such.
1174 * @param string|null $displayQuery display query
1175 * @param bool $showSql whether to show sql
1176 * @param mixed[] $sqlData sql data
1177 * @param Message|string $displayMessage display message
1179 private function getHtmlForPreviousUpdateQuery(
1180 string|
null $displayQuery,
1183 Message|
string $displayMessage,
1185 if ($displayQuery !== null && $showSql && $sqlData === []) {
1186 return Generator
::getMessage($displayMessage, $displayQuery, 'success');
1193 * To get the message if a column index is missing. If not will return null
1195 * @param string|null $table current table
1196 * @param string $database current database
1197 * @param bool $editable whether the results table can be editable or not
1198 * @param bool $hasUniqueKey whether there is a unique key
1200 private function getMessageIfMissingColumnIndex(
1206 if ($table === null) {
1211 if (Utilities
::isSystemSchema($database) ||
! $editable) {
1212 $output = Message
::notice(
1215 'Current selection does not contain a unique column.'
1216 . ' Grid edit, checkbox, Edit, Copy and Delete features'
1217 . ' are not available. %s',
1219 MySQLDocumentation
::showDocumentation(
1221 'cfg_RowActionLinksWithoutUnique',
1225 } elseif (! $hasUniqueKey) {
1226 $output = Message
::notice(
1229 'Current selection does not contain a unique column.'
1230 . ' Grid edit, Edit, Copy and Delete features may result in'
1231 . ' undesired behavior. %s',
1233 MySQLDocumentation
::showDocumentation(
1235 'cfg_RowActionLinksWithoutUnique',
1245 * Function to display results when the executed query returns non empty results
1247 * @param ResultInterface $result executed query results
1248 * @param string $db current database
1249 * @param string|null $table current table
1250 * @param mixed[]|null $sqlData sql data
1251 * @param DisplayResults $displayResultsObject Instance of DisplayResults
1252 * @param int|string $unlimNumRows unlimited number of rows
1253 * @param int|string $numRows number of rows
1254 * @param string|null $dispQuery display query
1255 * @param Message|string|null $dispMessage display message
1256 * @param string $sqlQuery sql query
1257 * @param string|null $completeQuery complete sql query
1258 * @psalm-param int|numeric-string $unlimNumRows
1259 * @psalm-param int|numeric-string $numRows
1260 * @psalm-param list<array{Status: non-empty-string, Duration: numeric-string}> $profilingResults
1262 * @return string html
1264 private function getQueryResponseForResultsReturned(
1265 ResultInterface
$result,
1266 StatementInfo
$statementInfo,
1269 array|
null $sqlData,
1270 DisplayResults
$displayResultsObject,
1271 int|
string $unlimNumRows,
1272 int|
string $numRows,
1273 string|
null $dispQuery,
1274 Message|
string|
null $dispMessage,
1275 array $profilingResults,
1277 string|
null $completeQuery,
1279 // If we are retrieving the full value of a truncated field or the original
1280 // value of a transformed field, show it here
1281 if (isset($_POST['grid_edit']) && $_POST['grid_edit'] == true) {
1282 $this->getResponseForGridEdit($result);
1283 ResponseRenderer
::getInstance()->callExit();
1286 // Gets the list of fields properties
1287 $fieldsMeta = $this->dbi
->getFieldsMeta($result);
1289 $response = ResponseRenderer
::getInstance();
1290 $header = $response->getHeader();
1291 $scripts = $header->getScripts();
1293 $justOneTable = $this->resultSetHasJustOneTable($fieldsMeta);
1295 // hide edit and delete links:
1296 // - for information_schema
1297 // - if the result set does not contain all the columns of a unique key
1298 // (unless this is an updatable view)
1299 // - if the SELECT query contains a join or a subquery
1301 $updatableView = false;
1303 $statement = $statementInfo->statement
;
1304 if ($statement instanceof SelectStatement
) {
1305 if ($statement->expr
&& $statement->expr
[0]->expr
=== '*' && $table) {
1306 $tableObj = new Table($table, $db, $this->dbi
);
1307 $updatableView = $tableObj->isUpdatableView();
1311 $statementInfo->flags
->join
1312 ||
$statementInfo->flags
->isSubQuery
1313 ||
count($statementInfo->selectTables
) !== 1
1315 $justOneTable = false;
1319 $hasUnique = $table !== null && $this->resultSetContainsUniqueKey($db, $table, $fieldsMeta);
1321 $editable = ($hasUnique
1322 ||
$this->config
->settings
['RowActionLinksWithoutUnique']
1325 && ! Utilities
::isSystemSchema($db);
1327 $_SESSION['tmpval']['possible_as_geometry'] = $editable;
1329 $displayParts = DisplayParts
::fromArray([
1330 'hasEditLink' => true,
1331 'deleteLink' => DeleteLinkEnum
::DELETE_ROW
,
1332 'hasSortLink' => true,
1333 'hasNavigationBar' => true,
1334 'hasBookmarkForm' => true,
1335 'hasTextButton' => false,
1336 'hasPrintLink' => true,
1340 $displayParts = DisplayParts
::fromArray([
1341 'hasEditLink' => false,
1342 'deleteLink' => DeleteLinkEnum
::NO_DELETE
,
1343 'hasSortLink' => true,
1344 'hasNavigationBar' => true,
1345 'hasBookmarkForm' => true,
1346 'hasTextButton' => true,
1347 'hasPrintLink' => true,
1351 if (isset($_POST['printview']) && $_POST['printview'] == '1') {
1352 $displayParts = DisplayParts
::fromArray([
1353 'hasEditLink' => false,
1354 'deleteLink' => DeleteLinkEnum
::NO_DELETE
,
1355 'hasSortLink' => false,
1356 'hasNavigationBar' => false,
1357 'hasBookmarkForm' => false,
1358 'hasTextButton' => false,
1359 'hasPrintLink' => false,
1363 if (! isset($_POST['printview']) ||
$_POST['printview'] != '1') {
1364 $scripts->addFile('makegrid.js');
1365 $scripts->addFile('sql.js');
1366 unset($GLOBALS['message']);
1367 //we don't need to buffer the output in getMessage here.
1368 //set a global variable and check against it in the function
1369 $GLOBALS['buffer_message'] = false;
1372 $previousUpdateQueryHtml = $this->getHtmlForPreviousUpdateQuery(
1374 $this->config
->settings
['ShowSQL'],
1379 $profilingChartHtml = $this->getProfilingChart($profilingResults);
1381 $missingUniqueColumnMessage = $this->getMessageIfMissingColumnIndex($table, $db, $editable, $hasUnique);
1383 $bookmarkCreatedMessage = $this->getBookmarkCreatedMessage();
1385 $tableHtml = $this->getHtmlForSqlQueryResultsTable(
1386 $displayResultsObject,
1395 $bookmarkSupportHtml = '';
1396 $bookmarkFeature = $this->relation
->getRelationParameters()->bookmarkFeature
;
1398 $bookmarkFeature !== null
1399 && $displayParts->hasBookmarkForm
1400 && empty($_GET['id_bookmark'])
1403 $bookmarkSupportHtml = $this->template
->render('sql/bookmark', [
1405 'goto' => Url
::getFromRoute('/sql', [
1408 'sql_query' => $sqlQuery,
1411 'user' => $this->config
->selectedServer
['user'],
1412 'sql_query' => $completeQuery ??
$sqlQuery,
1416 return $this->template
->render('sql/sql_query_results', [
1417 'previous_update_query' => $previousUpdateQueryHtml,
1418 'profiling_chart' => $profilingChartHtml,
1419 'missing_unique_column_message' => $missingUniqueColumnMessage,
1420 'bookmark_created_message' => $bookmarkCreatedMessage,
1421 'table' => $tableHtml,
1422 'bookmark_support' => $bookmarkSupportHtml,
1427 * Function to execute the query and send the response
1429 * @param bool $isGotoFile whether goto file or not
1430 * @param string $db current database
1431 * @param string|null $table current table
1432 * @param string|null $sqlQueryForBookmark the sql query to be stored as bookmark
1433 * @param string|null $messageToShow message to show
1434 * @param mixed[]|null $sqlData sql data
1435 * @param string $goto goto page url
1436 * @param string|null $dispQuery display query
1437 * @param Message|string|null $dispMessage display message
1438 * @param string $sqlQuery sql query
1439 * @param string|null $completeQuery complete query
1441 public function executeQueryAndSendQueryResponse(
1442 StatementInfo|
null $statementInfo,
1446 string|
null $sqlQueryForBookmark,
1447 string|
null $messageToShow,
1448 array|
null $sqlData,
1450 string|
null $dispQuery,
1451 Message|
string|
null $dispMessage,
1453 string|
null $completeQuery,
1455 if ($statementInfo === null) {
1456 // Parse and analyze the query
1457 [$statementInfo, $db, $tableFromSql] = ParseAnalyze
::sqlQuery($sqlQuery, $db);
1459 $table = $tableFromSql !== '' ?
$tableFromSql : $table;
1462 return $this->executeQueryAndGetQueryResponse(
1464 $isGotoFile, // is_gotofile
1467 $sqlQueryForBookmark, // sql_query_for_bookmark
1468 $messageToShow, // message_to_show
1469 $sqlData, // sql_data
1471 $dispQuery, // disp_query
1472 $dispMessage, // disp_message
1473 $sqlQuery, // sql_query
1474 $completeQuery, // complete_query
1479 * Function to execute the query and send the response
1481 * @param bool $isGotoFile whether goto file or not
1482 * @param string $db current database
1483 * @param string|null $table current table
1484 * @param string|null $sqlQueryForBookmark the sql query to be stored as bookmark
1485 * @param string|null $messageToShow message to show
1486 * @param mixed[]|null $sqlData sql data
1487 * @param string $goto goto page url
1488 * @param string|null $dispQuery display query
1489 * @param Message|string|null $dispMessage display message
1490 * @param string $sqlQuery sql query
1491 * @param string|null $completeQuery complete query
1493 * @return string html
1495 public function executeQueryAndGetQueryResponse(
1496 StatementInfo
$statementInfo,
1500 string|
null $sqlQueryForBookmark,
1501 string|
null $messageToShow,
1502 array|
null $sqlData,
1504 string|
null $dispQuery,
1505 Message|
string|
null $dispMessage,
1507 string|
null $completeQuery,
1509 // Handle remembered sorting order, only for single table query.
1510 // Handling is not required when it's a union query
1511 // (the parser never sets the 'union' key to 0).
1512 // Handling is also not required if we came from the "Sort by key"
1515 $this->isRememberSortingOrder($statementInfo)
1516 && ! $statementInfo->flags
->union
1517 && ! isset($_POST['sort_by_key'])
1519 if (! isset($_SESSION['sql_from_query_box'])) {
1520 $statementInfo = $this->handleSortOrder($db, $table, $statementInfo, $sqlQuery);
1522 unset($_SESSION['sql_from_query_box']);
1526 $displayResultsObject = new DisplayResults(
1535 $displayResultsObject->setConfigParamsForDisplayTable($statementInfo);
1537 // assign default full_sql_query
1538 $fullSqlQuery = $sqlQuery;
1540 // Do append a "LIMIT" clause?
1541 if ($this->isAppendLimitClause($statementInfo)) {
1542 $fullSqlQuery = $this->getSqlWithLimitClause($statementInfo);
1545 $GLOBALS['reload'] = $this->hasCurrentDbChanged($db);
1546 $this->dbi
->selectDb($db);
1548 if (isset($GLOBALS['show_as_php'])) {
1549 // Only if we ask to see the php code
1550 // The following was copied from getQueryResponseForNoResultsReturned()
1551 // Delete if it's not needed in this context
1552 if ($this->isDeleteTransformationInfo($statementInfo)) {
1553 $this->deleteTransformationInfo($db, $table ??
'', $statementInfo);
1556 $message = $this->getMessageForNoRowsReturned($messageToShow, $statementInfo, 0);
1558 return Generator
::getMessage($message, $GLOBALS['sql_query'], 'success');
1561 // Handle disable/enable foreign key checks
1562 $defaultFkCheck = ForeignKey
::handleDisableCheckInit();
1564 [$result, $numRows, $unlimNumRows, $profilingResults, $errorMessage] = $this->executeTheQuery(
1570 $sqlQueryForBookmark,
1573 $warningMessages = $this->dbi
->getWarnings();
1575 // No rows returned -> move back to the calling page
1576 if (($numRows == 0 && $unlimNumRows == 0) ||
$statementInfo->flags
->isAffected ||
$result === false) {
1577 $htmlOutput = $this->getQueryResponseForNoResultsReturned(
1583 $displayResultsObject,
1591 // At least one row is returned -> displays a table with results
1592 $htmlOutput = $this->getQueryResponseForResultsReturned(
1598 $displayResultsObject,
1609 // Handle disable/enable foreign key checks
1610 ForeignKey
::handleDisableCheckCleanup($defaultFkCheck);
1612 foreach ($warningMessages as $warning) {
1613 $message = Message
::notice(htmlspecialchars((string) $warning));
1614 $htmlOutput .= $message->getDisplay();
1621 * Function to define pos to display a row
1623 * @param int $numberOfLine Number of the line to display
1625 * @return int Start position to display the line
1627 private function getStartPosToDisplayRow(int $numberOfLine): int
1629 $maxRows = $_SESSION['tmpval']['max_rows'];
1631 return @((int) ceil($numberOfLine / $maxRows) - 1) * $maxRows;
1635 * Function to calculate new pos if pos is higher than number of rows
1636 * of displayed table
1638 * @param string $db Database name
1639 * @param string $table Table name
1640 * @param int|null $pos Initial position
1642 * @return int Number of pos to display last page
1644 public function calculatePosForLastPage(string $db, string $table, int|
null $pos): int
1646 if ($pos === null) {
1647 $pos = $_SESSION['tmpval']['pos'];
1650 $tableObject = new Table($table, $db, $this->dbi
);
1651 $unlimNumRows = $tableObject->countRecords(true);
1652 //If position is higher than number of rows
1653 if ($unlimNumRows <= $pos && $pos != 0) {
1654 return $this->getStartPosToDisplayRow($unlimNumRows);
1660 private function getIndexList(string $table, string $db): string
1662 $indexes = Index
::getFromTable($this->dbi
, $table, $db);
1663 $indexesDuplicates = Index
::findDuplicates($table, $db);
1664 $template = new Template();
1666 return $template->render('indexes', [
1667 'url_params' => $GLOBALS['urlParams'],
1668 'indexes' => $indexes,
1669 'indexes_duplicates' => $indexesDuplicates,
1673 /** @psalm-param list<array{Status: non-empty-string, Duration: numeric-string}> $profilingResults */
1674 private function getProfilingChart(array $profilingResults): string
1676 if ($profilingResults === []) {
1680 $profiling = $this->getDetailedProfilingStats($profilingResults);
1681 if ($profiling === []) {
1685 return $this->template
->render('sql/profiling_chart', ['profiling' => $profiling]);