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\Table\Table
;
25 use PhpMyAdmin\Utils\ForeignKey
;
28 use function array_column
;
29 use function array_key_exists
;
30 use function array_keys
;
31 use function array_sum
;
37 use function htmlspecialchars
;
38 use function in_array
;
39 use function is_array
;
40 use function session_start
;
41 use function session_write_close
;
43 use function str_contains
;
44 use function str_replace
;
48 * Set of functions for the SQL executor
52 public function __construct(
53 private DatabaseInterface
$dbi,
54 private Relation
$relation,
55 private RelationCleanup
$relationCleanup,
56 private Operations
$operations,
57 private Transformations
$transformations,
58 private Template
$template,
59 private readonly BookmarkRepository
$bookmarkRepository,
64 * Handle remembered sorting order, only for single table query
66 * @param string $db database name
67 * @param string $table table name
68 * @param string $fullSqlQuery SQL query
70 private function handleSortOrder(
73 StatementInfo
$statementInfo,
74 string &$fullSqlQuery,
76 if ($statementInfo->statement
=== null ||
$statementInfo->parser
=== null) {
77 return $statementInfo;
80 $tableObject = new Table($table, $db, $this->dbi
);
82 if (! $statementInfo->order
) {
83 // Retrieving the name of the column we should sort after.
84 $sortCol = $tableObject->getUiProp(Table
::PROP_SORTED_COLUMN
);
85 if (empty($sortCol)) {
86 return $statementInfo;
89 // Remove the name of the table from the retrieved field name.
90 $sortCol = str_replace(
91 Util
::backquote($table) . '.',
96 // Create the new query.
97 $fullSqlQuery = Query
::replaceClause(
98 $statementInfo->statement
,
99 $statementInfo->parser
->list,
100 'ORDER BY ' . $sortCol,
103 // TODO: Avoid reparsing the query.
104 $statementInfo = StatementInfo
::fromArray(Query
::getAll($fullSqlQuery));
106 // Store the remembered table into session.
107 $tableObject->setUiProp(
108 Table
::PROP_SORTED_COLUMN
,
110 $statementInfo->statement
,
111 $statementInfo->parser
->list,
117 return $statementInfo;
121 * Append limit clause to SQL query
123 * @return string limit clause appended SQL query
125 private function getSqlWithLimitClause(StatementInfo
$statementInfo): string
127 if ($statementInfo->statement
=== null ||
$statementInfo->parser
=== null) {
131 return Query
::replaceClause(
132 $statementInfo->statement
,
133 $statementInfo->parser
->list,
134 'LIMIT ' . $_SESSION['tmpval']['pos'] . ', '
135 . $_SESSION['tmpval']['max_rows'],
140 * Verify whether the result set has columns from just one table
142 * @param mixed[] $fieldsMeta meta fields
144 private function resultSetHasJustOneTable(array $fieldsMeta): bool
146 $justOneTable = true;
148 foreach ($fieldsMeta as $oneFieldMeta) {
149 if ($oneFieldMeta->table
!= '' && $prevTable != '' && $oneFieldMeta->table
!= $prevTable) {
150 $justOneTable = false;
153 if ($oneFieldMeta->table
== '') {
157 $prevTable = $oneFieldMeta->table
;
160 return $justOneTable && $prevTable != '';
164 * Verify whether the result set contains all the columns
165 * of at least one unique key
167 * @param string $db database name
168 * @param string $table table name
169 * @param mixed[] $fieldsMeta meta fields
171 private function resultSetContainsUniqueKey(string $db, string $table, array $fieldsMeta): bool
173 $columns = $this->dbi
->getColumns($db, $table);
174 $resultSetColumnNames = [];
175 foreach ($fieldsMeta as $oneMeta) {
176 $resultSetColumnNames[] = $oneMeta->name
;
179 foreach (Index
::getFromTable($this->dbi
, $table, $db) as $index) {
180 if (! $index->isUnique()) {
184 $indexColumns = $index->getColumns();
186 foreach (array_keys($indexColumns) as $indexColumnName) {
188 ! in_array($indexColumnName, $resultSetColumnNames)
189 && array_key_exists($indexColumnName, $columns)
190 && ! str_contains($columns[$indexColumnName]->extra
, 'INVISIBLE')
198 if ($numberFound === count($indexColumns)) {
207 * Get the HTML for relational column dropdown
208 * During grid edit, if we have a relational field, returns the html for the
211 * @param string $db current database
212 * @param string $table current table
213 * @param string $column current column
214 * @param string $currentValue current selected value
216 * @return string html for the dropdown
218 public function getHtmlForRelationalColumnDropdown(
222 string $currentValue,
224 $foreigners = $this->relation
->getForeigners($db, $table, $column);
226 $foreignData = $this->relation
->getForeignData($foreigners, $column, false, '', '');
228 if ($foreignData['disp_row'] == null) {
229 //Handle the case when number of values
230 //is more than $cfg['ForeignKeyMaxLimit']
231 $urlParams = ['db' => $db, 'table' => $table, 'field' => $column];
233 return $this->template
->render('sql/relational_column_dropdown', [
234 'current_value' => $_POST['curr_value'],
235 'params' => $urlParams,
239 $dropdown = $this->relation
->foreignDropdown(
240 $foreignData['disp_row'],
241 $foreignData['foreign_field'],
242 $foreignData['foreign_display'],
244 Config
::getInstance()->settings
['ForeignKeyMaxLimit'],
247 return '<select>' . $dropdown . '</select>';
251 * @psalm-param non-empty-list<array{Status: non-empty-string, Duration: numeric-string}> $profilingResults
253 * @psalm-return array{
255 * states: array<string, array{total_time: float, calls: int<1, max>}>,
256 * chart: array{labels: list<string>, data: list<float>},
257 * profile: list<array{status: string, duration: string, duration_raw: numeric-string}>
260 private function getDetailedProfilingStats(array $profilingResults): array
262 $totalTime = (float) array_sum(array_column($profilingResults, 'Duration'));
263 if ($totalTime === 0.0) {
269 foreach ($profilingResults as $result) {
270 $status = ucwords($result['Status']);
273 'duration' => Util
::formatNumber($result['Duration'], 3, 1),
274 'duration_raw' => $result['Duration'],
277 if (! isset($states[$status])) {
278 $states[$status] = ['total_time' => (float) $result['Duration'], 'calls' => 1];
280 $states[$status]['calls']++
;
281 $states[$status]['total_time'] +
= $result['Duration'];
286 $chart = ['labels' => array_keys($states), 'data' => array_column($states, 'total_time')];
288 return ['total_time' => $totalTime, 'states' => $states, 'chart' => $chart, 'profile' => $profile];
292 * Get value of a column for a specific row (marked by $whereClause)
294 public function getFullValuesForSetColumn(
300 $row = $this->dbi
->fetchSingleRow(sprintf(
301 'SELECT `%s` FROM `%s`.`%s` WHERE %s',
312 return $row[$column];
316 * Get all the values for a enum column or set column in a table
318 * @param string $db current database
319 * @param string $table current table
320 * @param string $columnName current column
322 * @return mixed[]|null array containing the value list for the column, null on failure
324 public function getValuesForColumn(string $db, string $table, string $columnName): array|
null
326 $column = $this->dbi
->getColumn($db, $table, $columnName);
328 if ($column === null) {
332 return Util
::parseEnumSetValues($column->type
, false);
336 * Function to check whether to remember the sorting order or not.
338 private function isRememberSortingOrder(StatementInfo
$statementInfo): bool
340 return Config
::getInstance()->settings
['RememberSorting']
341 && ! ($statementInfo->isCount
342 ||
$statementInfo->isExport
343 ||
$statementInfo->isFunction
344 ||
$statementInfo->isAnalyse
)
345 && $statementInfo->selectFrom
346 && ($statementInfo->selectExpression
=== []
347 ||
((count($statementInfo->selectExpression
) === 1)
348 && ($statementInfo->selectExpression
[0] === '*')))
349 && count($statementInfo->selectTables
) === 1;
353 * Function to check whether the LIMIT clause should be appended or not.
355 private function isAppendLimitClause(StatementInfo
$statementInfo): bool
357 // Assigning LIMIT clause to an syntactically-wrong query
358 // is not needed. Also we would want to show the true query
359 // and the true error message to the query executor
361 return (isset($statementInfo->parser
)
362 && $statementInfo->parser
->errors
=== [])
363 && ($_SESSION['tmpval']['max_rows'] !== 'all')
364 && (! $statementInfo->isExport
&& ! $statementInfo->isAnalyse
)
365 && ($statementInfo->selectFrom
366 ||
$statementInfo->isSubquery
)
367 && ! $statementInfo->limit
;
371 * Function to check whether this query is for just browsing
373 * @param bool|null $findRealEnd whether the real end should be found
375 public static function isJustBrowsing(StatementInfo
$statementInfo, bool|
null $findRealEnd): bool
377 return ! $statementInfo->isGroup
378 && ! $statementInfo->isFunction
379 && ! $statementInfo->union
380 && ! $statementInfo->distinct
381 && $statementInfo->selectFrom
382 && (count($statementInfo->selectTables
) === 1)
383 && (empty($statementInfo->statement
->where
)
384 ||
(count($statementInfo->statement
->where
) === 1
385 && $statementInfo->statement
->where
[0]->expr
=== '1'))
386 && ! $statementInfo->group
387 && ! isset($findRealEnd)
388 && ! $statementInfo->isSubquery
389 && ! $statementInfo->join
390 && ! $statementInfo->having
;
394 * Function to check whether the related transformation information should be deleted.
396 private function isDeleteTransformationInfo(StatementInfo
$statementInfo): bool
398 return $statementInfo->queryType
=== 'ALTER' ||
$statementInfo->queryType
=== 'DROP';
402 * Function to check whether the user has rights to drop the database
404 * @param bool $allowUserDropDatabase whether the user is allowed to drop db
405 * @param bool $isSuperUser whether this user is a superuser
407 public function hasNoRightsToDropDatabase(
408 StatementInfo
$statementInfo,
409 bool $allowUserDropDatabase,
412 return ! $allowUserDropDatabase && $statementInfo->dropDatabase
&& ! $isSuperUser;
416 * Function to find the real end of rows
418 * @param string $db the current database
419 * @param string $table the current table
421 * @return int the number of rows
423 public function findRealEndOfRows(string $db, string $table): int
425 $unlimNumRows = $this->dbi
->getTable($db, $table)->countRecords(true);
426 $_SESSION['tmpval']['pos'] = $this->getStartPosToDisplayRow($unlimNumRows);
428 return $unlimNumRows;
432 * Function to get the default sql query for browsing page
434 * @param string $db the current database
435 * @param string $table the current table
437 * @return string the default $sql_query for browse page
439 public function getDefaultSqlQueryForBrowse(string $db, string $table): string
441 $config = Config
::getInstance();
442 $bookmark = $this->bookmarkRepository
->getByLabel(
443 $config->selectedServer
['user'],
444 DatabaseName
::from($db),
448 if ($bookmark !== null && $bookmark->getQuery() !== '') {
449 $GLOBALS['using_bookmark_message'] = Message
::notice(
450 __('Using bookmark "%s" as default browse query.'),
452 $GLOBALS['using_bookmark_message']->addParam($table);
453 $GLOBALS['using_bookmark_message']->addHtml(
454 MySQLDocumentation
::showDocumentation('faq', 'faq6-22'),
457 return $bookmark->getQuery();
460 $defaultOrderByClause = '';
463 isset($config->settings
['TablePrimaryKeyOrder'])
464 && ($config->settings
['TablePrimaryKeyOrder'] !== 'NONE')
467 $primary = Index
::getPrimary($this->dbi
, $table, $db);
469 if ($primary !== null) {
470 $primarycols = $primary->getColumns();
472 foreach ($primarycols as $col) {
473 $primaryKey = $col->getName();
477 if ($primaryKey !== null) {
478 $defaultOrderByClause = ' ORDER BY '
479 . Util
::backquote($table) . '.'
480 . Util
::backquote($primaryKey) . ' '
481 . $config->settings
['TablePrimaryKeyOrder'];
486 return 'SELECT * FROM ' . Util
::backquote($table) . $defaultOrderByClause;
490 * Responds an error when an error happens when executing the query
492 * @param bool $isGotoFile whether goto file or not
493 * @param string $error error after executing the query
494 * @param string $fullSqlQuery full sql query
496 private function handleQueryExecuteError(bool $isGotoFile, string $error, string $fullSqlQuery): never
498 $response = ResponseRenderer
::getInstance();
500 $message = Message
::rawError($error);
501 $response->setRequestStatus(false);
502 $response->addJSON('message', $message);
504 Generator
::mysqlDie($error, $fullSqlQuery, false);
507 $response->callExit();
511 * Function to store the query as a bookmark
513 * @param string $db the current database
514 * @param string $bookmarkUser the bookmarking user
515 * @param string $sqlQueryForBookmark the query to be stored in bookmark
516 * @param string $bookmarkLabel bookmark label
517 * @param bool $bookmarkReplace whether to replace existing bookmarks
519 public function storeTheQueryAsBookmark(
520 BookmarkFeature|
null $bookmarkFeature,
522 string $bookmarkUser,
523 string $sqlQueryForBookmark,
524 string $bookmarkLabel,
525 bool $bookmarkReplace,
527 // Should we replace bookmark?
528 if ($bookmarkReplace && $bookmarkFeature !== null) {
529 $config = Config
::getInstance();
530 $bookmarks = $this->bookmarkRepository
->getList($config->selectedServer
['user'], $db);
531 foreach ($bookmarks as $bookmark) {
532 if ($bookmark->getLabel() !== $bookmarkLabel) {
540 $bookmark = $this->bookmarkRepository
->createBookmark(
541 $sqlQueryForBookmark,
545 isset($_POST['bkm_all_users']),
548 if ($bookmark === false) {
556 * Function to get the affected or changed number of rows after executing a query
558 * @param bool $isAffected whether the query affected a table
559 * @param ResultInterface|false $result results of executing the query
561 * @return int|string number of rows affected or changed
562 * @psalm-return int|numeric-string
564 private function getNumberOfRowsAffectedOrChanged(bool $isAffected, ResultInterface|
false $result): int|
string
567 return $this->dbi
->affectedRows();
571 return $result->numRows();
578 * Checks if the current database has changed
579 * This could happen if the user sends a query like "USE `database`;"
581 * @param string $db the database in the query
583 * @return bool whether to reload the navigation(1) or not(0)
585 private function hasCurrentDbChanged(string $db): bool
591 $currentDb = $this->dbi
->fetchValue('SELECT DATABASE()');
593 // $current_db is false, except when a USE statement was sent
594 return ($currentDb != false) && ($db !== $currentDb);
598 * If a table, database or column gets dropped, clean comments.
600 * @param string $db current database
601 * @param string $table current table
602 * @param string|null $column current column
603 * @param bool $purge whether purge set or not
605 private function cleanupRelations(string $db, string $table, string|
null $column, bool $purge): void
607 if (! $purge ||
$db === '') {
612 if ($column !== null && $column !== '') {
613 $this->relationCleanup
->column($db, $table, $column);
615 $this->relationCleanup
->table($db, $table);
618 $this->relationCleanup
->database($db);
623 * Function to count the total number of rows for the same 'SELECT' query without
624 * the 'LIMIT' clause that may have been programmatically added
626 * @param int|string $numRows number of rows affected/changed by the query
627 * @param bool $justBrowsing whether just browsing or not
628 * @param string $db the current database
629 * @param string $table the current table
630 * @psalm-param int|numeric-string $numRows
632 * @return int|string unlimited number of rows
633 * @psalm-return int|numeric-string
635 private function countQueryResults(
640 StatementInfo
$statementInfo,
642 /* Shortcut for not analyzed/empty query */
643 if ($statementInfo->statement
=== null ||
$statementInfo->parser
=== null) {
647 if (! $this->isAppendLimitClause($statementInfo)) {
648 // if we did not append a limit, set this to get a correct
649 // "Showing rows..." message
650 // $_SESSION['tmpval']['max_rows'] = 'all';
651 $unlimNumRows = $numRows;
652 } elseif ($_SESSION['tmpval']['max_rows'] > $numRows) {
653 // When user has not defined a limit in query and total rows in
654 // result are less than max_rows to display, there is no need
655 // to count total rows for that query again
656 $unlimNumRows = $_SESSION['tmpval']['pos'] +
$numRows;
657 } elseif ($statementInfo->queryType
=== 'SELECT' ||
$statementInfo->isSubquery
) {
658 // c o u n t q u e r y
660 // If we are "just browsing", there is only one table (and no join),
661 // and no WHERE clause (or just 'WHERE 1 '),
662 // we do a quick count (which uses MaxExactCount) because
663 // SQL_CALC_FOUND_ROWS is not quick on large InnoDB tables
665 // However, do not count again if we did it previously
666 // due to $find_real_end == true
668 // Get row count (is approximate for InnoDB)
669 $unlimNumRows = $this->dbi
->getTable($db, $table)->countRecords();
671 * @todo Can we know at this point that this is InnoDB,
672 * (in this case there would be no need for getting
675 if ($unlimNumRows < Config
::getInstance()->settings
['MaxExactCount']) {
676 // Get the exact count if approximate count
677 // is less than MaxExactCount
679 * @todo In countRecords(), MaxExactCount is also verified,
680 * so can we avoid checking it twice?
682 $unlimNumRows = $this->dbi
->getTable($db, $table)->countRecords(true);
685 $statement = $statementInfo->statement
;
687 // Remove ORDER BY to decrease unnecessary sorting time
688 if ($statementInfo->order
) {
689 $statement->order
= null;
692 // Removes LIMIT clause that might have been added
693 if ($statementInfo->limit
) {
694 $statement->limit
= false;
697 if (! $statementInfo->isGroup
&& ! $statementInfo->distinct
&& count($statement->expr
) === 1) {
698 $statement->expr
[0] = new Expression();
699 $statement->expr
[0]->expr
= '1';
702 $countQuery = 'SELECT COUNT(*) FROM (' . $statement->build() . ' ) as cnt';
704 $unlimNumRows = (int) $this->dbi
->fetchValue($countQuery);
706 } else {// not $is_select
710 return $unlimNumRows;
714 * Function to handle all aspects relating to executing the query
716 * @param string $fullSqlQuery full sql query
717 * @param bool $isGotoFile whether to go to a file
718 * @param string $db current database
719 * @param string|null $table current table
720 * @param bool|null $findRealEnd whether to find the real end
721 * @param string|null $sqlQueryForBookmark sql query to be stored as bookmark
723 * @psalm-return array{
724 * ResultInterface|false,
725 * int|numeric-string,
726 * int|numeric-string,
727 * list<array{Status: non-empty-string, Duration: numeric-string}>,
731 private function executeTheQuery(
732 StatementInfo
$statementInfo,
733 string $fullSqlQuery,
737 bool|
null $findRealEnd,
738 string|
null $sqlQueryForBookmark,
740 $response = ResponseRenderer
::getInstance();
741 $response->getHeader()->getMenu()->setTable($table ??
'');
743 Profiling
::enable($this->dbi
);
745 if (! defined('TESTSUITE')) {
746 // close session in case the query takes too long
747 session_write_close();
750 $result = $this->dbi
->tryQuery($fullSqlQuery);
751 $GLOBALS['querytime'] = $this->dbi
->lastQueryExecutionTime
;
753 if (! defined('TESTSUITE')) {
760 // Displays an error message if required and stop parsing the script
761 $error = $this->dbi
->getError();
762 $config = Config
::getInstance();
763 if ($error && $config->settings
['IgnoreMultiSubmitErrors']) {
764 $errorMessage = $error;
765 } elseif ($error !== '') {
766 $this->handleQueryExecuteError($isGotoFile, $error, $fullSqlQuery);
769 // If there are no errors and bookmarklabel was given,
770 // store the query as a bookmark
771 if (! empty($_POST['bkm_label']) && $sqlQueryForBookmark) {
772 $bookmarkFeature = $this->relation
->getRelationParameters()->bookmarkFeature
;
773 $this->storeTheQueryAsBookmark(
776 $bookmarkFeature !== null ?
$config->selectedServer
['user'] : '',
777 $sqlQueryForBookmark,
779 isset($_POST['bkm_replace']),
783 // Gets the number of rows affected/returned
784 // (This must be done immediately after the query because
785 // mysql_affected_rows() reports about the last query done)
786 $numRows = $this->getNumberOfRowsAffectedOrChanged($statementInfo->isAffected
, $result);
788 $profilingResults = Profiling
::getInformation($this->dbi
);
790 $justBrowsing = self
::isJustBrowsing($statementInfo, $findRealEnd ??
null);
792 $unlimNumRows = $this->countQueryResults($numRows, $justBrowsing, $db, $table ??
'', $statementInfo);
794 $this->cleanupRelations($db, $table ??
'', $_POST['dropped_column'] ??
null, ! empty($_POST['purge']));
796 return [$result, $numRows, $unlimNumRows, $profilingResults, $errorMessage];
800 * Delete related transformation information
802 * @param string $db current database
803 * @param string $table current table
805 private function deleteTransformationInfo(string $db, string $table, StatementInfo
$statementInfo): void
807 if (! isset($statementInfo->statement
)) {
811 $statement = $statementInfo->statement
;
812 if ($statement instanceof AlterStatement
) {
814 ! empty($statement->altered
[0])
815 && $statement->altered
[0]->options
->has('DROP')
816 && ! empty($statement->altered
[0]->field
->column
)
818 $this->transformations
->clear($db, $table, $statement->altered
[0]->field
->column
);
820 } elseif ($statement instanceof DropStatement
) {
821 $this->transformations
->clear($db, $table);
826 * Function to get the message for the no rows returned case
828 * @param string|null $messageToShow message to show
829 * @param int|string $numRows number of rows
831 private function getMessageForNoRowsReturned(
832 string|
null $messageToShow,
833 StatementInfo
$statementInfo,
836 if ($statementInfo->queryType
=== 'DELETE') {
837 $message = Message
::getMessageForDeletedRows($numRows);
838 } elseif ($statementInfo->isInsert
) {
839 if ($statementInfo->queryType
=== 'REPLACE') {
840 // For REPLACE we get DELETED + INSERTED row count,
841 // so we have to call it affected
842 $message = Message
::getMessageForAffectedRows($numRows);
844 $message = Message
::getMessageForInsertedRows($numRows);
847 $insertId = $this->dbi
->insertId();
848 if ($insertId !== 0) {
849 // insert_id is id of FIRST record inserted in one insert,
850 // so if we inserted multiple rows, we had to increment this
851 $message->addText('[br]');
852 // need to use a temporary because the Message class
853 // currently supports adding parameters only to the first
855 $inserted = Message
::notice(__('Inserted row id: %1$d'));
856 $inserted->addParam($insertId +
$numRows - 1);
857 $message->addMessage($inserted);
859 } elseif ($statementInfo->isAffected
) {
860 $message = Message
::getMessageForAffectedRows($numRows);
862 // Ok, here is an explanation for the !$is_select.
863 // The form generated by PhpMyAdmin\SqlQueryForm
864 // and /database/sql has many submit buttons
865 // on the same form, and some confusion arises from the
866 // fact that $message_to_show is sent for every case.
867 // The $message_to_show containing a success message and sent with
868 // the form should not have priority over errors
869 } elseif ($messageToShow && $statementInfo->queryType
!== 'SELECT') {
870 $message = Message
::rawSuccess(htmlspecialchars($messageToShow));
871 } elseif (! empty($GLOBALS['show_as_php'])) {
872 $message = Message
::success(__('Showing as PHP code'));
873 } elseif (isset($GLOBALS['show_as_php'])) {
874 /* User disable showing as PHP, query is only displayed */
875 $message = Message
::notice(__('Showing SQL query'));
877 $message = Message
::success(
878 __('MySQL returned an empty result set (i.e. zero rows).'),
882 if (isset($GLOBALS['querytime'])) {
883 $queryTime = Message
::notice(
884 '(' . __('Query took %01.4f seconds.') . ')',
886 $queryTime->addParam($GLOBALS['querytime']);
887 $message->addMessage($queryTime);
890 // In case of ROLLBACK, notify the user.
891 if (isset($_POST['rollback_query'])) {
892 $message->addText(__('[ROLLBACK occurred.]'));
899 * Function to respond back when the query returns zero rows
900 * This method is called
901 * 1-> When browsing an empty table
902 * 2-> When executing a query on a non empty table which returns zero results
903 * 3-> When executing a query on an empty table
904 * 4-> When executing an INSERT, UPDATE, DELETE query from the SQL tab
905 * 5-> When deleting a row from BROWSE tab
906 * 6-> When searching using the SEARCH tab which returns zero results
907 * 7-> When changing the structure of the table except change operation
909 * @param string $db current database
910 * @param string|null $table current table
911 * @param string|null $messageToShow message to show
912 * @param int|string $numRows number of rows
913 * @param DisplayResults $displayResultsObject DisplayResult instance
914 * @param string $errorMessage error message from tryQuery
915 * @param ResultInterface|false $result executed query results
916 * @param string $sqlQuery sql query
917 * @param string|null $completeQuery complete sql query
918 * @psalm-param int|numeric-string $numRows
919 * @psalm-param list<array{Status: non-empty-string, Duration: numeric-string}> $profilingResults
921 * @return string html
923 private function getQueryResponseForNoResultsReturned(
924 StatementInfo
$statementInfo,
927 string|
null $messageToShow,
929 DisplayResults
$displayResultsObject,
930 string $errorMessage,
931 array $profilingResults,
932 ResultInterface|
false $result,
934 string|
null $completeQuery,
936 if ($this->isDeleteTransformationInfo($statementInfo)) {
937 $this->deleteTransformationInfo($db, $table ??
'', $statementInfo);
940 if ($errorMessage !== '') {
941 $message = Message
::rawError($errorMessage);
943 $message = $this->getMessageForNoRowsReturned($messageToShow, $statementInfo, $numRows);
946 $queryMessage = Generator
::getMessage($message, $GLOBALS['sql_query'], 'success');
948 if (isset($GLOBALS['show_as_php'])) {
949 return $queryMessage;
953 if (! empty($GLOBALS['reload'])) {
954 $extraData['reload'] = 1;
955 $extraData['db'] = $GLOBALS['db'];
958 // For ajax requests add message and sql_query as JSON
959 $config = Config
::getInstance();
960 if (empty($_REQUEST['ajax_page_request'])) {
961 $extraData['message'] = $message;
962 if ($config->settings
['ShowSQL']) {
963 $extraData['sql_query'] = $queryMessage;
968 isset($_POST['dropped_column'])
969 && $db !== '' && $table !== null && $table !== ''
971 // to refresh the list of indexes (Ajax mode)
972 $extraData['indexes_list'] = $this->getIndexList($table, $db);
975 $response = ResponseRenderer
::getInstance();
976 $response->addJSON($extraData);
977 $header = $response->getHeader();
978 $scripts = $header->getScripts();
979 $scripts->addFile('sql.js');
981 // We can only skip result fetching if the result contains no columns.
982 if (($result instanceof ResultInterface
&& $result->numFields() === 0) ||
$result === false) {
983 return $queryMessage;
986 $displayParts = DisplayParts
::fromArray([
987 'hasEditLink' => false,
988 'deleteLink' => DeleteLinkEnum
::NO_DELETE
,
989 'hasSortLink' => true,
990 'hasNavigationBar' => false,
991 'hasBookmarkForm' => true,
992 'hasTextButton' => true,
993 'hasPrintLink' => true,
996 $sqlQueryResultsTable = $this->getHtmlForSqlQueryResultsTable(
997 $displayResultsObject,
1008 $profilingChart = $this->getProfilingChart($profilingResults);
1011 $bookmarkFeature = $this->relation
->getRelationParameters()->bookmarkFeature
;
1013 $bookmarkFeature !== null
1014 && empty($_GET['id_bookmark'])
1017 $bookmark = $this->template
->render('sql/bookmark', [
1019 'goto' => Url
::getFromRoute('/sql', [
1022 'sql_query' => $sqlQuery,
1025 'user' => $config->selectedServer
['user'],
1026 'sql_query' => $completeQuery ??
$sqlQuery,
1027 'allow_shared_bookmarks' => $config->settings
['AllowSharedBookmarks'],
1031 return $this->template
->render('sql/no_results_returned', [
1032 'message' => $queryMessage,
1033 'sql_query_results_table' => $sqlQueryResultsTable,
1034 'profiling_chart' => $profilingChart,
1035 'bookmark' => $bookmark,
1038 'sql_query' => $sqlQuery,
1039 'is_procedure' => $statementInfo->isProcedure
,
1044 * Function to send response for ajax grid edit
1046 * @param ResultInterface $result result of the executed query
1048 private function getResponseForGridEdit(ResultInterface
$result): void
1050 $row = $result->fetchRow();
1051 $fieldsMeta = $this->dbi
->getFieldsMeta($result);
1053 if (isset($fieldsMeta[0]) && $fieldsMeta[0]->isBinary()) {
1054 $row[0] = bin2hex($row[0]);
1057 $response = ResponseRenderer
::getInstance();
1058 $response->addJSON('value', $row[0]);
1062 * Returns a message for successful creation of a bookmark or null if a bookmark
1065 private function getBookmarkCreatedMessage(): string
1068 if (isset($_GET['label'])) {
1069 $message = Message
::success(
1070 __('Bookmark %s has been created.'),
1072 $message->addParam($_GET['label']);
1073 $output = $message->getDisplay();
1080 * Function to get html for the sql query results table
1082 * @param DisplayResults $displayResultsObject instance of DisplayResult
1083 * @param bool $editable whether the result table is editable or not
1084 * @param int|string $unlimNumRows unlimited number of rows
1085 * @param int|string $numRows number of rows
1086 * @param mixed[]|null $showTable table definitions
1087 * @param ResultInterface $result result of the executed query
1088 * @param bool $isLimitedDisplay Show only limited operations or not
1089 * @psalm-param int|numeric-string $unlimNumRows
1090 * @psalm-param int|numeric-string $numRows
1092 private function getHtmlForSqlQueryResultsTable(
1093 DisplayResults
$displayResultsObject,
1094 DisplayParts
$displayParts,
1096 int|
string $unlimNumRows,
1097 int|
string $numRows,
1098 array|
null $showTable,
1099 ResultInterface
$result,
1100 StatementInfo
$statementInfo,
1101 bool $isLimitedDisplay = false,
1103 $printView = isset($_POST['printview']) && $_POST['printview'] == '1';
1104 $isBrowseDistinct = ! empty($_POST['is_browse_distinct']);
1106 if ($statementInfo->isProcedure
) {
1107 return $this->getHtmlForStoredProcedureResults(
1109 $displayResultsObject,
1119 $_SESSION['is_multi_query'] = false;
1120 $displayResultsObject->setProperties(
1122 $this->dbi
->getFieldsMeta($result),
1123 $statementInfo->isCount
,
1124 $statementInfo->isExport
,
1125 $statementInfo->isFunction
,
1126 $statementInfo->isAnalyse
,
1128 $GLOBALS['querytime'],
1129 $GLOBALS['text_dir'],
1130 $statementInfo->isMaint
,
1131 $statementInfo->isExplain
,
1132 $statementInfo->isShow
,
1139 return $displayResultsObject->getTable($result, $displayParts, $statementInfo, $isLimitedDisplay);
1142 /** @param mixed[]|null $showTable table definitions */
1143 private function getHtmlForStoredProcedureResults(
1144 ResultInterface
$result,
1145 DisplayResults
$displayResultsObject,
1146 StatementInfo
$statementInfo,
1147 array|
null $showTable,
1150 bool $isBrowseDistinct,
1151 bool $isLimitedDisplay,
1155 while ($result !== false) {
1156 $numRows = $result->numRows();
1159 $displayResultsObject->setProperties(
1161 $this->dbi
->getFieldsMeta($result),
1162 $statementInfo->isCount
,
1163 $statementInfo->isExport
,
1164 $statementInfo->isFunction
,
1165 $statementInfo->isAnalyse
,
1167 $GLOBALS['querytime'],
1168 $GLOBALS['text_dir'],
1169 $statementInfo->isMaint
,
1170 $statementInfo->isExplain
,
1171 $statementInfo->isShow
,
1178 $displayParts = DisplayParts
::fromArray([
1179 'hasEditLink' => false,
1180 'deleteLink' => DeleteLinkEnum
::NO_DELETE
,
1181 'hasSortLink' => true,
1182 'hasNavigationBar' => true,
1183 'hasBookmarkForm' => true,
1184 'hasTextButton' => true,
1185 'hasPrintLink' => true,
1188 $tableHtml .= $displayResultsObject->getTable(
1196 $result = $this->dbi
->nextResult();
1203 * Function to get html for the previous query if there is such.
1205 * @param string|null $displayQuery display query
1206 * @param bool $showSql whether to show sql
1207 * @param mixed[] $sqlData sql data
1208 * @param Message|string $displayMessage display message
1210 private function getHtmlForPreviousUpdateQuery(
1211 string|
null $displayQuery,
1214 Message|
string $displayMessage,
1216 if ($displayQuery !== null && $showSql && $sqlData === []) {
1217 return Generator
::getMessage($displayMessage, $displayQuery, 'success');
1224 * To get the message if a column index is missing. If not will return null
1226 * @param string|null $table current table
1227 * @param string $database current database
1228 * @param bool $editable whether the results table can be editable or not
1229 * @param bool $hasUniqueKey whether there is a unique key
1231 private function getMessageIfMissingColumnIndex(
1237 if ($table === null) {
1242 if (Utilities
::isSystemSchema($database) ||
! $editable) {
1243 $output = Message
::notice(
1246 'Current selection does not contain a unique column.'
1247 . ' Grid edit, checkbox, Edit, Copy and Delete features'
1248 . ' are not available. %s',
1250 MySQLDocumentation
::showDocumentation(
1252 'cfg_RowActionLinksWithoutUnique',
1256 } elseif (! $hasUniqueKey) {
1257 $output = Message
::notice(
1260 'Current selection does not contain a unique column.'
1261 . ' Grid edit, Edit, Copy and Delete features may result in'
1262 . ' undesired behavior. %s',
1264 MySQLDocumentation
::showDocumentation(
1266 'cfg_RowActionLinksWithoutUnique',
1276 * Function to display results when the executed query returns non empty results
1278 * @param ResultInterface $result executed query results
1279 * @param string $db current database
1280 * @param string|null $table current table
1281 * @param mixed[]|null $sqlData sql data
1282 * @param DisplayResults $displayResultsObject Instance of DisplayResults
1283 * @param int|string $unlimNumRows unlimited number of rows
1284 * @param int|string $numRows number of rows
1285 * @param string|null $dispQuery display query
1286 * @param Message|string|null $dispMessage display message
1287 * @param string $sqlQuery sql query
1288 * @param string|null $completeQuery complete sql query
1289 * @psalm-param int|numeric-string $unlimNumRows
1290 * @psalm-param int|numeric-string $numRows
1291 * @psalm-param list<array{Status: non-empty-string, Duration: numeric-string}> $profilingResults
1293 * @return string html
1295 private function getQueryResponseForResultsReturned(
1296 ResultInterface
$result,
1297 StatementInfo
$statementInfo,
1300 array|
null $sqlData,
1301 DisplayResults
$displayResultsObject,
1302 int|
string $unlimNumRows,
1303 int|
string $numRows,
1304 string|
null $dispQuery,
1305 Message|
string|
null $dispMessage,
1306 array $profilingResults,
1308 string|
null $completeQuery,
1310 $GLOBALS['showtable'] ??
= null;
1312 // If we are retrieving the full value of a truncated field or the original
1313 // value of a transformed field, show it here
1314 if (isset($_POST['grid_edit']) && $_POST['grid_edit'] == true) {
1315 $this->getResponseForGridEdit($result);
1316 ResponseRenderer
::getInstance()->callExit();
1319 // Gets the list of fields properties
1320 $fieldsMeta = $this->dbi
->getFieldsMeta($result);
1322 // Should be initialized these parameters before parsing
1323 if (! is_array($GLOBALS['showtable'])) {
1324 $GLOBALS['showtable'] = null;
1327 $response = ResponseRenderer
::getInstance();
1328 $header = $response->getHeader();
1329 $scripts = $header->getScripts();
1331 $justOneTable = $this->resultSetHasJustOneTable($fieldsMeta);
1333 // hide edit and delete links:
1334 // - for information_schema
1335 // - if the result set does not contain all the columns of a unique key
1336 // (unless this is an updatable view)
1337 // - if the SELECT query contains a join or a subquery
1339 $updatableView = false;
1341 $statement = $statementInfo->statement
;
1342 if ($statement instanceof SelectStatement
) {
1343 if ($statement->expr
&& $statement->expr
[0]->expr
=== '*' && $table) {
1344 $tableObj = new Table($table, $db, $this->dbi
);
1345 $updatableView = $tableObj->isUpdatableView();
1349 $statementInfo->join
1350 ||
$statementInfo->isSubquery
1351 ||
count($statementInfo->selectTables
) !== 1
1353 $justOneTable = false;
1357 $hasUnique = $table !== null && $this->resultSetContainsUniqueKey($db, $table, $fieldsMeta);
1359 $config = Config
::getInstance();
1360 $editable = ($hasUnique
1361 ||
$config->settings
['RowActionLinksWithoutUnique']
1364 && ! Utilities
::isSystemSchema($db);
1366 $_SESSION['tmpval']['possible_as_geometry'] = $editable;
1368 $displayParts = DisplayParts
::fromArray([
1369 'hasEditLink' => true,
1370 'deleteLink' => DeleteLinkEnum
::DELETE_ROW
,
1371 'hasSortLink' => true,
1372 'hasNavigationBar' => true,
1373 'hasBookmarkForm' => true,
1374 'hasTextButton' => false,
1375 'hasPrintLink' => true,
1379 $displayParts = DisplayParts
::fromArray([
1380 'hasEditLink' => false,
1381 'deleteLink' => DeleteLinkEnum
::NO_DELETE
,
1382 'hasSortLink' => true,
1383 'hasNavigationBar' => true,
1384 'hasBookmarkForm' => true,
1385 'hasTextButton' => true,
1386 'hasPrintLink' => true,
1390 if (isset($_POST['printview']) && $_POST['printview'] == '1') {
1391 $displayParts = DisplayParts
::fromArray([
1392 'hasEditLink' => false,
1393 'deleteLink' => DeleteLinkEnum
::NO_DELETE
,
1394 'hasSortLink' => false,
1395 'hasNavigationBar' => false,
1396 'hasBookmarkForm' => false,
1397 'hasTextButton' => false,
1398 'hasPrintLink' => false,
1402 if (! isset($_POST['printview']) ||
$_POST['printview'] != '1') {
1403 $scripts->addFile('makegrid.js');
1404 $scripts->addFile('sql.js');
1405 unset($GLOBALS['message']);
1406 //we don't need to buffer the output in getMessage here.
1407 //set a global variable and check against it in the function
1408 $GLOBALS['buffer_message'] = false;
1411 $previousUpdateQueryHtml = $this->getHtmlForPreviousUpdateQuery(
1413 $config->settings
['ShowSQL'],
1418 $profilingChartHtml = $this->getProfilingChart($profilingResults);
1420 $missingUniqueColumnMessage = $this->getMessageIfMissingColumnIndex($table, $db, $editable, $hasUnique);
1422 $bookmarkCreatedMessage = $this->getBookmarkCreatedMessage();
1424 $tableHtml = $this->getHtmlForSqlQueryResultsTable(
1425 $displayResultsObject,
1430 $GLOBALS['showtable'],
1435 $bookmarkSupportHtml = '';
1436 $bookmarkFeature = $this->relation
->getRelationParameters()->bookmarkFeature
;
1438 $bookmarkFeature !== null
1439 && $displayParts->hasBookmarkForm
1440 && empty($_GET['id_bookmark'])
1443 $bookmarkSupportHtml = $this->template
->render('sql/bookmark', [
1445 'goto' => Url
::getFromRoute('/sql', [
1448 'sql_query' => $sqlQuery,
1451 'user' => $config->selectedServer
['user'],
1452 'sql_query' => $completeQuery ??
$sqlQuery,
1456 return $this->template
->render('sql/sql_query_results', [
1457 'previous_update_query' => $previousUpdateQueryHtml,
1458 'profiling_chart' => $profilingChartHtml,
1459 'missing_unique_column_message' => $missingUniqueColumnMessage,
1460 'bookmark_created_message' => $bookmarkCreatedMessage,
1461 'table' => $tableHtml,
1462 'bookmark_support' => $bookmarkSupportHtml,
1467 * Function to execute the query and send the response
1469 * @param bool $isGotoFile whether goto file or not
1470 * @param string $db current database
1471 * @param string|null $table current table
1472 * @param bool|null $findRealEnd whether to find real end or not
1473 * @param string|null $sqlQueryForBookmark the sql query to be stored as bookmark
1474 * @param string|null $messageToShow message to show
1475 * @param mixed[]|null $sqlData sql data
1476 * @param string $goto goto page url
1477 * @param string|null $dispQuery display query
1478 * @param Message|string|null $dispMessage display message
1479 * @param string $sqlQuery sql query
1480 * @param string|null $completeQuery complete query
1482 public function executeQueryAndSendQueryResponse(
1483 StatementInfo|
null $statementInfo,
1487 bool|
null $findRealEnd,
1488 string|
null $sqlQueryForBookmark,
1489 string|
null $messageToShow,
1490 array|
null $sqlData,
1492 string|
null $dispQuery,
1493 Message|
string|
null $dispMessage,
1495 string|
null $completeQuery,
1497 if ($statementInfo === null) {
1498 // Parse and analyze the query
1499 [$statementInfo, $db, $tableFromSql] = ParseAnalyze
::sqlQuery($sqlQuery, $db);
1501 $table = $tableFromSql !== '' ?
$tableFromSql : $table;
1504 return $this->executeQueryAndGetQueryResponse(
1506 $isGotoFile, // is_gotofile
1509 $findRealEnd, // find_real_end
1510 $sqlQueryForBookmark, // sql_query_for_bookmark
1511 $messageToShow, // message_to_show
1512 $sqlData, // sql_data
1514 $dispQuery, // disp_query
1515 $dispMessage, // disp_message
1516 $sqlQuery, // sql_query
1517 $completeQuery, // complete_query
1522 * Function to execute the query and send the response
1524 * @param bool $isGotoFile whether goto file or not
1525 * @param string $db current database
1526 * @param string|null $table current table
1527 * @param bool|null $findRealEnd whether to find real end or not
1528 * @param string|null $sqlQueryForBookmark the sql query to be stored as bookmark
1529 * @param string|null $messageToShow message to show
1530 * @param mixed[]|null $sqlData sql data
1531 * @param string $goto goto page url
1532 * @param string|null $dispQuery display query
1533 * @param Message|string|null $dispMessage display message
1534 * @param string $sqlQuery sql query
1535 * @param string|null $completeQuery complete query
1537 * @return string html
1539 public function executeQueryAndGetQueryResponse(
1540 StatementInfo
$statementInfo,
1544 bool|
null $findRealEnd,
1545 string|
null $sqlQueryForBookmark,
1546 string|
null $messageToShow,
1547 array|
null $sqlData,
1549 string|
null $dispQuery,
1550 Message|
string|
null $dispMessage,
1552 string|
null $completeQuery,
1554 // Handle remembered sorting order, only for single table query.
1555 // Handling is not required when it's a union query
1556 // (the parser never sets the 'union' key to 0).
1557 // Handling is also not required if we came from the "Sort by key"
1560 $this->isRememberSortingOrder($statementInfo)
1561 && ! $statementInfo->union
1562 && ! isset($_POST['sort_by_key'])
1564 if (! isset($_SESSION['sql_from_query_box'])) {
1565 $statementInfo = $this->handleSortOrder($db, $table, $statementInfo, $sqlQuery);
1567 unset($_SESSION['sql_from_query_box']);
1571 $displayResultsObject = new DisplayResults(
1579 $displayResultsObject->setConfigParamsForDisplayTable($statementInfo);
1581 // assign default full_sql_query
1582 $fullSqlQuery = $sqlQuery;
1584 // Do append a "LIMIT" clause?
1585 if ($this->isAppendLimitClause($statementInfo)) {
1586 $fullSqlQuery = $this->getSqlWithLimitClause($statementInfo);
1589 $GLOBALS['reload'] = $this->hasCurrentDbChanged($db);
1590 $this->dbi
->selectDb($db);
1592 if (isset($GLOBALS['show_as_php'])) {
1593 // Only if we ask to see the php code
1594 // The following was copied from getQueryResponseForNoResultsReturned()
1595 // Delete if it's not needed in this context
1596 if ($this->isDeleteTransformationInfo($statementInfo)) {
1597 $this->deleteTransformationInfo($db, $table ??
'', $statementInfo);
1600 $message = $this->getMessageForNoRowsReturned($messageToShow, $statementInfo, 0);
1602 return Generator
::getMessage($message, $GLOBALS['sql_query'], 'success');
1605 // Handle disable/enable foreign key checks
1606 $defaultFkCheck = ForeignKey
::handleDisableCheckInit();
1608 [$result, $numRows, $unlimNumRows, $profilingResults, $errorMessage] = $this->executeTheQuery(
1615 $sqlQueryForBookmark,
1618 $warningMessages = $this->operations
->getWarningMessagesArray();
1620 // No rows returned -> move back to the calling page
1621 if (($numRows == 0 && $unlimNumRows == 0) ||
$statementInfo->isAffected ||
$result === false) {
1622 $htmlOutput = $this->getQueryResponseForNoResultsReturned(
1628 $displayResultsObject,
1636 // At least one row is returned -> displays a table with results
1637 $htmlOutput = $this->getQueryResponseForResultsReturned(
1643 $displayResultsObject,
1654 // Handle disable/enable foreign key checks
1655 ForeignKey
::handleDisableCheckCleanup($defaultFkCheck);
1657 foreach ($warningMessages as $warning) {
1658 $message = Message
::notice(htmlspecialchars($warning));
1659 $htmlOutput .= $message->getDisplay();
1666 * Function to define pos to display a row
1668 * @param int $numberOfLine Number of the line to display
1670 * @return int Start position to display the line
1672 private function getStartPosToDisplayRow(int $numberOfLine): int
1674 $maxRows = $_SESSION['tmpval']['max_rows'];
1676 return @((int) ceil($numberOfLine / $maxRows) - 1) * $maxRows;
1680 * Function to calculate new pos if pos is higher than number of rows
1681 * of displayed table
1683 * @param string $db Database name
1684 * @param string $table Table name
1685 * @param int|null $pos Initial position
1687 * @return int Number of pos to display last page
1689 public function calculatePosForLastPage(string $db, string $table, int|
null $pos): int
1691 if ($pos === null) {
1692 $pos = $_SESSION['tmpval']['pos'];
1695 $tableObject = new Table($table, $db, $this->dbi
);
1696 $unlimNumRows = $tableObject->countRecords(true);
1697 //If position is higher than number of rows
1698 if ($unlimNumRows <= $pos && $pos != 0) {
1699 return $this->getStartPosToDisplayRow($unlimNumRows);
1705 private function getIndexList(string $table, string $db): string
1707 $indexes = Index
::getFromTable($this->dbi
, $table, $db);
1708 $indexesDuplicates = Index
::findDuplicates($table, $db);
1709 $template = new Template();
1711 return $template->render('indexes', [
1712 'url_params' => $GLOBALS['urlParams'],
1713 'indexes' => $indexes,
1714 'indexes_duplicates' => $indexesDuplicates,
1718 /** @psalm-param list<array{Status: non-empty-string, Duration: numeric-string}> $profilingResults */
1719 private function getProfilingChart(array $profilingResults): string
1721 if ($profilingResults === []) {
1725 $profiling = $this->getDetailedProfilingStats($profilingResults);
1726 if ($profiling === []) {
1730 return $this->template
->render('sql/profiling_chart', ['profiling' => $profiling]);