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 session_start
;
40 use function session_write_close
;
42 use function str_contains
;
43 use function str_replace
;
47 * Set of functions for the SQL executor
51 public function __construct(
52 private DatabaseInterface
$dbi,
53 private Relation
$relation,
54 private RelationCleanup
$relationCleanup,
55 private Operations
$operations,
56 private Transformations
$transformations,
57 private Template
$template,
58 private readonly BookmarkRepository
$bookmarkRepository,
63 * Handle remembered sorting order, only for single table query
65 * @param string $db database name
66 * @param string $table table name
67 * @param string $fullSqlQuery SQL query
69 private function handleSortOrder(
72 StatementInfo
$statementInfo,
73 string &$fullSqlQuery,
75 if ($statementInfo->statement
=== null ||
$statementInfo->parser
=== null) {
76 return $statementInfo;
79 $tableObject = new Table($table, $db, $this->dbi
);
81 if (! $statementInfo->order
) {
82 // Retrieving the name of the column we should sort after.
83 $sortCol = $tableObject->getUiProp(Table
::PROP_SORTED_COLUMN
);
84 if (empty($sortCol)) {
85 return $statementInfo;
88 // Remove the name of the table from the retrieved field name.
89 $sortCol = str_replace(
90 Util
::backquote($table) . '.',
95 // Create the new query.
96 $fullSqlQuery = Query
::replaceClause(
97 $statementInfo->statement
,
98 $statementInfo->parser
->list,
99 'ORDER BY ' . $sortCol,
102 // TODO: Avoid reparsing the query.
103 $statementInfo = StatementInfo
::fromArray(Query
::getAll($fullSqlQuery));
105 // Store the remembered table into session.
106 $tableObject->setUiProp(
107 Table
::PROP_SORTED_COLUMN
,
109 $statementInfo->statement
,
110 $statementInfo->parser
->list,
116 return $statementInfo;
120 * Append limit clause to SQL query
122 * @return string limit clause appended SQL query
124 private function getSqlWithLimitClause(StatementInfo
$statementInfo): string
126 if ($statementInfo->statement
=== null ||
$statementInfo->parser
=== null) {
130 return Query
::replaceClause(
131 $statementInfo->statement
,
132 $statementInfo->parser
->list,
133 'LIMIT ' . $_SESSION['tmpval']['pos'] . ', '
134 . $_SESSION['tmpval']['max_rows'],
139 * Verify whether the result set has columns from just one table
141 * @param mixed[] $fieldsMeta meta fields
143 private function resultSetHasJustOneTable(array $fieldsMeta): bool
145 $justOneTable = true;
147 foreach ($fieldsMeta as $oneFieldMeta) {
148 if ($oneFieldMeta->table
!= '' && $prevTable != '' && $oneFieldMeta->table
!= $prevTable) {
149 $justOneTable = false;
152 if ($oneFieldMeta->table
== '') {
156 $prevTable = $oneFieldMeta->table
;
159 return $justOneTable && $prevTable != '';
163 * Verify whether the result set contains all the columns
164 * of at least one unique key
166 * @param string $db database name
167 * @param string $table table name
168 * @param mixed[] $fieldsMeta meta fields
170 private function resultSetContainsUniqueKey(string $db, string $table, array $fieldsMeta): bool
172 $columns = $this->dbi
->getColumns($db, $table);
173 $resultSetColumnNames = [];
174 foreach ($fieldsMeta as $oneMeta) {
175 $resultSetColumnNames[] = $oneMeta->name
;
178 foreach (Index
::getFromTable($this->dbi
, $table, $db) as $index) {
179 if (! $index->isUnique()) {
183 $indexColumns = $index->getColumns();
185 foreach (array_keys($indexColumns) as $indexColumnName) {
187 ! in_array($indexColumnName, $resultSetColumnNames)
188 && array_key_exists($indexColumnName, $columns)
189 && ! str_contains($columns[$indexColumnName]->extra
, 'INVISIBLE')
197 if ($numberFound === count($indexColumns)) {
206 * Get the HTML for relational column dropdown
207 * During grid edit, if we have a relational field, returns the html for the
210 * @param string $db current database
211 * @param string $table current table
212 * @param string $column current column
213 * @param string $currentValue current selected value
215 * @return string html for the dropdown
217 public function getHtmlForRelationalColumnDropdown(
221 string $currentValue,
223 $foreigners = $this->relation
->getForeigners($db, $table, $column);
225 $foreignData = $this->relation
->getForeignData($foreigners, $column, false, '', '');
227 if ($foreignData['disp_row'] == null) {
228 //Handle the case when number of values
229 //is more than $cfg['ForeignKeyMaxLimit']
230 $urlParams = ['db' => $db, 'table' => $table, 'field' => $column];
232 return $this->template
->render('sql/relational_column_dropdown', [
233 'current_value' => $_POST['curr_value'],
234 'params' => $urlParams,
238 $dropdown = $this->relation
->foreignDropdown(
239 $foreignData['disp_row'],
240 $foreignData['foreign_field'],
241 $foreignData['foreign_display'],
243 Config
::getInstance()->settings
['ForeignKeyMaxLimit'],
246 return '<select>' . $dropdown . '</select>';
250 * @psalm-param non-empty-list<array{Status: non-empty-string, Duration: numeric-string}> $profilingResults
252 * @psalm-return array{
254 * states: array<string, array{total_time: float, calls: int<1, max>}>,
255 * chart: array{labels: list<string>, data: list<float>},
256 * profile: list<array{status: string, duration: string, duration_raw: numeric-string}>
259 private function getDetailedProfilingStats(array $profilingResults): array
261 $totalTime = (float) array_sum(array_column($profilingResults, 'Duration'));
262 if ($totalTime === 0.0) {
268 foreach ($profilingResults as $result) {
269 $status = ucwords($result['Status']);
272 'duration' => Util
::formatNumber($result['Duration'], 3, 1),
273 'duration_raw' => $result['Duration'],
276 if (! isset($states[$status])) {
277 $states[$status] = ['total_time' => (float) $result['Duration'], 'calls' => 1];
279 $states[$status]['calls']++
;
280 $states[$status]['total_time'] +
= $result['Duration'];
285 $chart = ['labels' => array_keys($states), 'data' => array_column($states, 'total_time')];
287 return ['total_time' => $totalTime, 'states' => $states, 'chart' => $chart, 'profile' => $profile];
291 * Get value of a column for a specific row (marked by $whereClause)
293 public function getFullValuesForSetColumn(
299 $row = $this->dbi
->fetchSingleRow(sprintf(
300 'SELECT `%s` FROM `%s`.`%s` WHERE %s',
311 return $row[$column];
315 * Get all the values for a enum column or set column in a table
317 * @param string $db current database
318 * @param string $table current table
319 * @param string $columnName current column
321 * @return mixed[]|null array containing the value list for the column, null on failure
323 public function getValuesForColumn(string $db, string $table, string $columnName): array|
null
325 $column = $this->dbi
->getColumn($db, $table, $columnName);
327 if ($column === null) {
331 return Util
::parseEnumSetValues($column->type
, false);
335 * Function to check whether to remember the sorting order or not.
337 private function isRememberSortingOrder(StatementInfo
$statementInfo): bool
339 return Config
::getInstance()->settings
['RememberSorting']
340 && ! ($statementInfo->isCount
341 ||
$statementInfo->isExport
342 ||
$statementInfo->isFunction
343 ||
$statementInfo->isAnalyse
)
344 && $statementInfo->selectFrom
345 && ($statementInfo->selectExpression
=== []
346 ||
((count($statementInfo->selectExpression
) === 1)
347 && ($statementInfo->selectExpression
[0] === '*')))
348 && count($statementInfo->selectTables
) === 1;
352 * Function to check whether the LIMIT clause should be appended or not.
354 private function isAppendLimitClause(StatementInfo
$statementInfo): bool
356 // Assigning LIMIT clause to an syntactically-wrong query
357 // is not needed. Also we would want to show the true query
358 // and the true error message to the query executor
360 return (isset($statementInfo->parser
)
361 && $statementInfo->parser
->errors
=== [])
362 && ($_SESSION['tmpval']['max_rows'] !== 'all')
363 && (! $statementInfo->isExport
&& ! $statementInfo->isAnalyse
)
364 && ($statementInfo->selectFrom
365 ||
$statementInfo->isSubquery
)
366 && ! $statementInfo->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->isGroup
375 && ! $statementInfo->isFunction
376 && ! $statementInfo->union
377 && ! $statementInfo->distinct
378 && $statementInfo->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->group
385 && ! $statementInfo->isSubquery
386 && ! $statementInfo->join
387 && ! $statementInfo->having
;
391 * Function to check whether the related transformation information should be deleted.
393 private function isDeleteTransformationInfo(StatementInfo
$statementInfo): bool
395 return $statementInfo->queryType
=== 'ALTER' ||
$statementInfo->queryType
=== 'DROP';
399 * Function to check whether the user has rights to drop the database
401 * @param bool $allowUserDropDatabase whether the user is allowed to drop db
402 * @param bool $isSuperUser whether this user is a superuser
404 public function hasNoRightsToDropDatabase(
405 StatementInfo
$statementInfo,
406 bool $allowUserDropDatabase,
409 return ! $allowUserDropDatabase && $statementInfo->dropDatabase
&& ! $isSuperUser;
413 * Function to get the default sql query for browsing page
415 * @param string $db the current database
416 * @param string $table the current table
418 * @return string the default $sql_query for browse page
420 public function getDefaultSqlQueryForBrowse(string $db, string $table): string
422 $config = Config
::getInstance();
423 $bookmark = $this->bookmarkRepository
->getByLabel(
424 $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($config->settings
['TablePrimaryKeyOrder'])
445 && ($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 . $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 $config = Config
::getInstance();
511 $bookmarks = $this->bookmarkRepository
->getList($config->selectedServer
['user'], $db);
512 foreach ($bookmarks as $bookmark) {
513 if ($bookmark->getLabel() !== $bookmarkLabel) {
521 $bookmark = $this->bookmarkRepository
->createBookmark(
522 $sqlQueryForBookmark,
526 isset($_POST['bkm_all_users']),
529 if ($bookmark === false) {
537 * Function to get the affected or changed number of rows after executing a query
539 * @param bool $isAffected whether the query affected a table
540 * @param ResultInterface|false $result results of executing the query
542 * @return int|string number of rows affected or changed
543 * @psalm-return int|numeric-string
545 private function getNumberOfRowsAffectedOrChanged(bool $isAffected, ResultInterface|
false $result): int|
string
548 return $this->dbi
->affectedRows();
552 return $result->numRows();
559 * Checks if the current database has changed
560 * This could happen if the user sends a query like "USE `database`;"
562 * @param string $db the database in the query
564 * @return bool whether to reload the navigation(1) or not(0)
566 private function hasCurrentDbChanged(string $db): bool
572 $currentDb = $this->dbi
->fetchValue('SELECT DATABASE()');
574 // $current_db is false, except when a USE statement was sent
575 return ($currentDb != false) && ($db !== $currentDb);
579 * If a table, database or column gets dropped, clean comments.
581 * @param string $db current database
582 * @param string $table current table
583 * @param string|null $column current column
584 * @param bool $purge whether purge set or not
586 private function cleanupRelations(string $db, string $table, string|
null $column, bool $purge): void
588 if (! $purge ||
$db === '') {
593 if ($column !== null && $column !== '') {
594 $this->relationCleanup
->column($db, $table, $column);
596 $this->relationCleanup
->table($db, $table);
599 $this->relationCleanup
->database($db);
604 * Function to count the total number of rows for the same 'SELECT' query without
605 * the 'LIMIT' clause that may have been programmatically added
607 * @param int|string $numRows number of rows affected/changed by the query
608 * @param bool $justBrowsing whether just browsing or not
609 * @param string $db the current database
610 * @param string $table the current table
611 * @psalm-param int|numeric-string $numRows
613 * @return int|string unlimited number of rows
614 * @psalm-return int|numeric-string
616 private function countQueryResults(
621 StatementInfo
$statementInfo,
623 /* Shortcut for not analyzed/empty query */
624 if ($statementInfo->statement
=== null ||
$statementInfo->parser
=== null) {
628 if (! $this->isAppendLimitClause($statementInfo)) {
629 // if we did not append a limit, set this to get a correct
630 // "Showing rows..." message
631 // $_SESSION['tmpval']['max_rows'] = 'all';
632 $unlimNumRows = $numRows;
633 } elseif ($_SESSION['tmpval']['max_rows'] > $numRows) {
634 // When user has not defined a limit in query and total rows in
635 // result are less than max_rows to display, there is no need
636 // to count total rows for that query again
637 $unlimNumRows = $_SESSION['tmpval']['pos'] +
$numRows;
638 } elseif ($statementInfo->queryType
=== 'SELECT' ||
$statementInfo->isSubquery
) {
639 // c o u n t q u e r y
641 // If we are "just browsing", there is only one table (and no join),
642 // and no WHERE clause (or just 'WHERE 1 '),
643 // we do a quick count (which uses MaxExactCount) because
644 // SQL_CALC_FOUND_ROWS is not quick on large InnoDB tables
646 // Get row count (is approximate for InnoDB)
647 $unlimNumRows = $this->dbi
->getTable($db, $table)->countRecords();
649 * @todo Can we know at this point that this is InnoDB,
650 * (in this case there would be no need for getting
653 if ($unlimNumRows < Config
::getInstance()->settings
['MaxExactCount']) {
654 // Get the exact count if approximate count
655 // is less than MaxExactCount
657 * @todo In countRecords(), MaxExactCount is also verified,
658 * so can we avoid checking it twice?
660 $unlimNumRows = $this->dbi
->getTable($db, $table)->countRecords(true);
663 $statement = $statementInfo->statement
;
665 // Remove ORDER BY to decrease unnecessary sorting time
666 if ($statementInfo->order
) {
667 $statement->order
= null;
670 // Removes LIMIT clause that might have been added
671 if ($statementInfo->limit
) {
672 $statement->limit
= false;
675 if (! $statementInfo->isGroup
&& ! $statementInfo->distinct
&& count($statement->expr
) === 1) {
676 $statement->expr
[0] = new Expression();
677 $statement->expr
[0]->expr
= '1';
680 $countQuery = 'SELECT COUNT(*) FROM (' . $statement->build() . ' ) as cnt';
682 $unlimNumRows = (int) $this->dbi
->fetchValue($countQuery);
684 } else {// not $is_select
688 return $unlimNumRows;
692 * Function to handle all aspects relating to executing the query
694 * @param string $fullSqlQuery full sql query
695 * @param bool $isGotoFile whether to go to a file
696 * @param string $db current database
697 * @param string|null $table current table
698 * @param string|null $sqlQueryForBookmark sql query to be stored as bookmark
700 * @psalm-return array{
701 * ResultInterface|false,
702 * int|numeric-string,
703 * int|numeric-string,
704 * list<array{Status: non-empty-string, Duration: numeric-string}>,
708 private function executeTheQuery(
709 StatementInfo
$statementInfo,
710 string $fullSqlQuery,
714 string|
null $sqlQueryForBookmark,
716 $response = ResponseRenderer
::getInstance();
717 $response->getHeader()->getMenu()->setTable($table ??
'');
719 Profiling
::enable($this->dbi
);
721 if (! defined('TESTSUITE')) {
722 // close session in case the query takes too long
723 session_write_close();
726 $result = $this->dbi
->tryQuery($fullSqlQuery);
727 $GLOBALS['querytime'] = $this->dbi
->lastQueryExecutionTime
;
729 if (! defined('TESTSUITE')) {
736 // Displays an error message if required and stop parsing the script
737 $error = $this->dbi
->getError();
738 $config = Config
::getInstance();
739 if ($error && $config->settings
['IgnoreMultiSubmitErrors']) {
740 $errorMessage = $error;
741 } elseif ($error !== '') {
742 $this->handleQueryExecuteError($isGotoFile, $error, $fullSqlQuery);
745 // If there are no errors and bookmarklabel was given,
746 // store the query as a bookmark
747 if (! empty($_POST['bkm_label']) && $sqlQueryForBookmark) {
748 $bookmarkFeature = $this->relation
->getRelationParameters()->bookmarkFeature
;
749 $this->storeTheQueryAsBookmark(
752 $bookmarkFeature !== null ?
$config->selectedServer
['user'] : '',
753 $sqlQueryForBookmark,
755 isset($_POST['bkm_replace']),
759 // Gets the number of rows affected/returned
760 // (This must be done immediately after the query because
761 // mysql_affected_rows() reports about the last query done)
762 $numRows = $this->getNumberOfRowsAffectedOrChanged($statementInfo->isAffected
, $result);
764 $profilingResults = Profiling
::getInformation($this->dbi
);
766 $justBrowsing = self
::isJustBrowsing($statementInfo);
768 $unlimNumRows = $this->countQueryResults($numRows, $justBrowsing, $db, $table ??
'', $statementInfo);
770 $this->cleanupRelations($db, $table ??
'', $_POST['dropped_column'] ??
null, ! empty($_POST['purge']));
772 return [$result, $numRows, $unlimNumRows, $profilingResults, $errorMessage];
776 * Delete related transformation information
778 * @param string $db current database
779 * @param string $table current table
781 private function deleteTransformationInfo(string $db, string $table, StatementInfo
$statementInfo): void
783 if (! isset($statementInfo->statement
)) {
787 $statement = $statementInfo->statement
;
788 if ($statement instanceof AlterStatement
) {
790 ! empty($statement->altered
[0])
791 && $statement->altered
[0]->options
->has('DROP')
792 && ! empty($statement->altered
[0]->field
->column
)
794 $this->transformations
->clear($db, $table, $statement->altered
[0]->field
->column
);
796 } elseif ($statement instanceof DropStatement
) {
797 $this->transformations
->clear($db, $table);
802 * Function to get the message for the no rows returned case
804 * @param string|null $messageToShow message to show
805 * @param int|string $numRows number of rows
807 private function getMessageForNoRowsReturned(
808 string|
null $messageToShow,
809 StatementInfo
$statementInfo,
812 if ($statementInfo->queryType
=== 'DELETE') {
813 $message = Message
::getMessageForDeletedRows($numRows);
814 } elseif ($statementInfo->isInsert
) {
815 if ($statementInfo->queryType
=== 'REPLACE') {
816 // For REPLACE we get DELETED + INSERTED row count,
817 // so we have to call it affected
818 $message = Message
::getMessageForAffectedRows($numRows);
820 $message = Message
::getMessageForInsertedRows($numRows);
823 $insertId = $this->dbi
->insertId();
824 if ($insertId !== 0) {
825 // insert_id is id of FIRST record inserted in one insert,
826 // so if we inserted multiple rows, we had to increment this
827 $message->addText('[br]');
828 // need to use a temporary because the Message class
829 // currently supports adding parameters only to the first
831 $inserted = Message
::notice(__('Inserted row id: %1$d'));
832 $inserted->addParam($insertId +
$numRows - 1);
833 $message->addMessage($inserted);
835 } elseif ($statementInfo->isAffected
) {
836 $message = Message
::getMessageForAffectedRows($numRows);
838 // Ok, here is an explanation for the !$is_select.
839 // The form generated by PhpMyAdmin\SqlQueryForm
840 // and /database/sql has many submit buttons
841 // on the same form, and some confusion arises from the
842 // fact that $message_to_show is sent for every case.
843 // The $message_to_show containing a success message and sent with
844 // the form should not have priority over errors
845 } elseif ($messageToShow && $statementInfo->queryType
!== 'SELECT') {
846 $message = Message
::rawSuccess(htmlspecialchars($messageToShow));
847 } elseif (! empty($GLOBALS['show_as_php'])) {
848 $message = Message
::success(__('Showing as PHP code'));
849 } elseif (isset($GLOBALS['show_as_php'])) {
850 /* User disable showing as PHP, query is only displayed */
851 $message = Message
::notice(__('Showing SQL query'));
853 $message = Message
::success(
854 __('MySQL returned an empty result set (i.e. zero rows).'),
858 if (isset($GLOBALS['querytime'])) {
859 $queryTime = Message
::notice(
860 '(' . __('Query took %01.4f seconds.') . ')',
862 $queryTime->addParam($GLOBALS['querytime']);
863 $message->addMessage($queryTime);
866 // In case of ROLLBACK, notify the user.
867 if (isset($_POST['rollback_query'])) {
868 $message->addText(__('[ROLLBACK occurred.]'));
875 * Function to respond back when the query returns zero rows
876 * This method is called
877 * 1-> When browsing an empty table
878 * 2-> When executing a query on a non empty table which returns zero results
879 * 3-> When executing a query on an empty table
880 * 4-> When executing an INSERT, UPDATE, DELETE query from the SQL tab
881 * 5-> When deleting a row from BROWSE tab
882 * 6-> When searching using the SEARCH tab which returns zero results
883 * 7-> When changing the structure of the table except change operation
885 * @param string $db current database
886 * @param string|null $table current table
887 * @param string|null $messageToShow message to show
888 * @param int|string $numRows number of rows
889 * @param DisplayResults $displayResultsObject DisplayResult instance
890 * @param string $errorMessage error message from tryQuery
891 * @param ResultInterface|false $result executed query results
892 * @param string $sqlQuery sql query
893 * @param string|null $completeQuery complete sql query
894 * @psalm-param int|numeric-string $numRows
895 * @psalm-param list<array{Status: non-empty-string, Duration: numeric-string}> $profilingResults
897 * @return string html
899 private function getQueryResponseForNoResultsReturned(
900 StatementInfo
$statementInfo,
903 string|
null $messageToShow,
905 DisplayResults
$displayResultsObject,
906 string $errorMessage,
907 array $profilingResults,
908 ResultInterface|
false $result,
910 string|
null $completeQuery,
912 if ($this->isDeleteTransformationInfo($statementInfo)) {
913 $this->deleteTransformationInfo($db, $table ??
'', $statementInfo);
916 if ($errorMessage !== '') {
917 $message = Message
::rawError($errorMessage);
919 $message = $this->getMessageForNoRowsReturned($messageToShow, $statementInfo, $numRows);
922 $queryMessage = Generator
::getMessage($message, $GLOBALS['sql_query'], 'success');
924 if (isset($GLOBALS['show_as_php'])) {
925 return $queryMessage;
929 if (! empty($GLOBALS['reload'])) {
930 $extraData['reload'] = 1;
931 $extraData['db'] = Current
::$database;
934 // For ajax requests add message and sql_query as JSON
935 $config = Config
::getInstance();
936 if (empty($_REQUEST['ajax_page_request'])) {
937 $extraData['message'] = $message;
938 if ($config->settings
['ShowSQL']) {
939 $extraData['sql_query'] = $queryMessage;
944 isset($_POST['dropped_column'])
945 && $db !== '' && $table !== null && $table !== ''
947 // to refresh the list of indexes (Ajax mode)
948 $extraData['indexes_list'] = $this->getIndexList($table, $db);
951 $response = ResponseRenderer
::getInstance();
952 $response->addJSON($extraData);
953 $header = $response->getHeader();
954 $scripts = $header->getScripts();
955 $scripts->addFile('sql.js');
957 // We can only skip result fetching if the result contains no columns.
958 if (($result instanceof ResultInterface
&& $result->numFields() === 0) ||
$result === false) {
959 return $queryMessage;
962 $displayParts = DisplayParts
::fromArray([
963 'hasEditLink' => false,
964 'deleteLink' => DeleteLinkEnum
::NO_DELETE
,
965 'hasSortLink' => true,
966 'hasNavigationBar' => false,
967 'hasBookmarkForm' => true,
968 'hasTextButton' => true,
969 'hasPrintLink' => true,
972 $sqlQueryResultsTable = $this->getHtmlForSqlQueryResultsTable(
973 $displayResultsObject,
983 $profilingChart = $this->getProfilingChart($profilingResults);
986 $bookmarkFeature = $this->relation
->getRelationParameters()->bookmarkFeature
;
988 $bookmarkFeature !== null
989 && empty($_GET['id_bookmark'])
992 $bookmark = $this->template
->render('sql/bookmark', [
994 'goto' => Url
::getFromRoute('/sql', [
997 'sql_query' => $sqlQuery,
1000 'user' => $config->selectedServer
['user'],
1001 'sql_query' => $completeQuery ??
$sqlQuery,
1002 'allow_shared_bookmarks' => $config->settings
['AllowSharedBookmarks'],
1006 return $this->template
->render('sql/no_results_returned', [
1007 'message' => $queryMessage,
1008 'sql_query_results_table' => $sqlQueryResultsTable,
1009 'profiling_chart' => $profilingChart,
1010 'bookmark' => $bookmark,
1013 'sql_query' => $sqlQuery,
1014 'is_procedure' => $statementInfo->isProcedure
,
1019 * Function to send response for ajax grid edit
1021 * @param ResultInterface $result result of the executed query
1023 private function getResponseForGridEdit(ResultInterface
$result): void
1025 $row = $result->fetchRow();
1026 $fieldsMeta = $this->dbi
->getFieldsMeta($result);
1028 if (isset($fieldsMeta[0]) && $fieldsMeta[0]->isBinary()) {
1029 $row[0] = bin2hex($row[0]);
1032 $response = ResponseRenderer
::getInstance();
1033 $response->addJSON('value', $row[0]);
1037 * Returns a message for successful creation of a bookmark or null if a bookmark
1040 private function getBookmarkCreatedMessage(): string
1043 if (isset($_GET['label'])) {
1044 $message = Message
::success(
1045 __('Bookmark %s has been created.'),
1047 $message->addParam($_GET['label']);
1048 $output = $message->getDisplay();
1055 * Function to get html for the sql query results table
1057 * @param DisplayResults $displayResultsObject instance of DisplayResult
1058 * @param bool $editable whether the result table is editable or not
1059 * @param int|string $unlimNumRows unlimited number of rows
1060 * @param int|string $numRows number of rows
1061 * @param ResultInterface $result result of the executed query
1062 * @param bool $isLimitedDisplay Show only limited operations or not
1063 * @psalm-param int|numeric-string $unlimNumRows
1064 * @psalm-param int|numeric-string $numRows
1066 private function getHtmlForSqlQueryResultsTable(
1067 DisplayResults
$displayResultsObject,
1068 DisplayParts
$displayParts,
1070 int|
string $unlimNumRows,
1071 int|
string $numRows,
1072 ResultInterface
$result,
1073 StatementInfo
$statementInfo,
1074 bool $isLimitedDisplay = false,
1076 $printView = isset($_POST['printview']) && $_POST['printview'] == '1';
1077 $isBrowseDistinct = ! empty($_POST['is_browse_distinct']);
1079 if ($statementInfo->isProcedure
) {
1080 return $this->getHtmlForStoredProcedureResults(
1082 $displayResultsObject,
1091 $_SESSION['is_multi_query'] = false;
1092 $displayResultsObject->setProperties(
1094 $this->dbi
->getFieldsMeta($result),
1095 $statementInfo->isCount
,
1096 $statementInfo->isExport
,
1097 $statementInfo->isFunction
,
1098 $statementInfo->isAnalyse
,
1100 $GLOBALS['querytime'],
1101 LanguageManager
::$textDir,
1102 $statementInfo->isMaint
,
1103 $statementInfo->isExplain
,
1104 $statementInfo->isShow
,
1110 return $displayResultsObject->getTable($result, $displayParts, $statementInfo, $isLimitedDisplay);
1113 private function getHtmlForStoredProcedureResults(
1114 ResultInterface
$result,
1115 DisplayResults
$displayResultsObject,
1116 StatementInfo
$statementInfo,
1119 bool $isBrowseDistinct,
1120 bool $isLimitedDisplay,
1124 while ($result !== false) {
1125 $numRows = $result->numRows();
1128 $displayResultsObject->setProperties(
1130 $this->dbi
->getFieldsMeta($result),
1131 $statementInfo->isCount
,
1132 $statementInfo->isExport
,
1133 $statementInfo->isFunction
,
1134 $statementInfo->isAnalyse
,
1136 $GLOBALS['querytime'],
1137 LanguageManager
::$textDir,
1138 $statementInfo->isMaint
,
1139 $statementInfo->isExplain
,
1140 $statementInfo->isShow
,
1146 $displayParts = DisplayParts
::fromArray([
1147 'hasEditLink' => false,
1148 'deleteLink' => DeleteLinkEnum
::NO_DELETE
,
1149 'hasSortLink' => true,
1150 'hasNavigationBar' => true,
1151 'hasBookmarkForm' => true,
1152 'hasTextButton' => true,
1153 'hasPrintLink' => true,
1156 $tableHtml .= $displayResultsObject->getTable(
1164 $result = $this->dbi
->nextResult();
1171 * Function to get html for the previous query if there is such.
1173 * @param string|null $displayQuery display query
1174 * @param bool $showSql whether to show sql
1175 * @param mixed[] $sqlData sql data
1176 * @param Message|string $displayMessage display message
1178 private function getHtmlForPreviousUpdateQuery(
1179 string|
null $displayQuery,
1182 Message|
string $displayMessage,
1184 if ($displayQuery !== null && $showSql && $sqlData === []) {
1185 return Generator
::getMessage($displayMessage, $displayQuery, 'success');
1192 * To get the message if a column index is missing. If not will return null
1194 * @param string|null $table current table
1195 * @param string $database current database
1196 * @param bool $editable whether the results table can be editable or not
1197 * @param bool $hasUniqueKey whether there is a unique key
1199 private function getMessageIfMissingColumnIndex(
1205 if ($table === null) {
1210 if (Utilities
::isSystemSchema($database) ||
! $editable) {
1211 $output = Message
::notice(
1214 'Current selection does not contain a unique column.'
1215 . ' Grid edit, checkbox, Edit, Copy and Delete features'
1216 . ' are not available. %s',
1218 MySQLDocumentation
::showDocumentation(
1220 'cfg_RowActionLinksWithoutUnique',
1224 } elseif (! $hasUniqueKey) {
1225 $output = Message
::notice(
1228 'Current selection does not contain a unique column.'
1229 . ' Grid edit, Edit, Copy and Delete features may result in'
1230 . ' undesired behavior. %s',
1232 MySQLDocumentation
::showDocumentation(
1234 'cfg_RowActionLinksWithoutUnique',
1244 * Function to display results when the executed query returns non empty results
1246 * @param ResultInterface $result executed query results
1247 * @param string $db current database
1248 * @param string|null $table current table
1249 * @param mixed[]|null $sqlData sql data
1250 * @param DisplayResults $displayResultsObject Instance of DisplayResults
1251 * @param int|string $unlimNumRows unlimited number of rows
1252 * @param int|string $numRows number of rows
1253 * @param string|null $dispQuery display query
1254 * @param Message|string|null $dispMessage display message
1255 * @param string $sqlQuery sql query
1256 * @param string|null $completeQuery complete sql query
1257 * @psalm-param int|numeric-string $unlimNumRows
1258 * @psalm-param int|numeric-string $numRows
1259 * @psalm-param list<array{Status: non-empty-string, Duration: numeric-string}> $profilingResults
1261 * @return string html
1263 private function getQueryResponseForResultsReturned(
1264 ResultInterface
$result,
1265 StatementInfo
$statementInfo,
1268 array|
null $sqlData,
1269 DisplayResults
$displayResultsObject,
1270 int|
string $unlimNumRows,
1271 int|
string $numRows,
1272 string|
null $dispQuery,
1273 Message|
string|
null $dispMessage,
1274 array $profilingResults,
1276 string|
null $completeQuery,
1278 // If we are retrieving the full value of a truncated field or the original
1279 // value of a transformed field, show it here
1280 if (isset($_POST['grid_edit']) && $_POST['grid_edit'] == true) {
1281 $this->getResponseForGridEdit($result);
1282 ResponseRenderer
::getInstance()->callExit();
1285 // Gets the list of fields properties
1286 $fieldsMeta = $this->dbi
->getFieldsMeta($result);
1288 $response = ResponseRenderer
::getInstance();
1289 $header = $response->getHeader();
1290 $scripts = $header->getScripts();
1292 $justOneTable = $this->resultSetHasJustOneTable($fieldsMeta);
1294 // hide edit and delete links:
1295 // - for information_schema
1296 // - if the result set does not contain all the columns of a unique key
1297 // (unless this is an updatable view)
1298 // - if the SELECT query contains a join or a subquery
1300 $updatableView = false;
1302 $statement = $statementInfo->statement
;
1303 if ($statement instanceof SelectStatement
) {
1304 if ($statement->expr
&& $statement->expr
[0]->expr
=== '*' && $table) {
1305 $tableObj = new Table($table, $db, $this->dbi
);
1306 $updatableView = $tableObj->isUpdatableView();
1310 $statementInfo->join
1311 ||
$statementInfo->isSubquery
1312 ||
count($statementInfo->selectTables
) !== 1
1314 $justOneTable = false;
1318 $hasUnique = $table !== null && $this->resultSetContainsUniqueKey($db, $table, $fieldsMeta);
1320 $config = Config
::getInstance();
1321 $editable = ($hasUnique
1322 ||
$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 $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' => $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->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(
1534 $displayResultsObject->setConfigParamsForDisplayTable($statementInfo);
1536 // assign default full_sql_query
1537 $fullSqlQuery = $sqlQuery;
1539 // Do append a "LIMIT" clause?
1540 if ($this->isAppendLimitClause($statementInfo)) {
1541 $fullSqlQuery = $this->getSqlWithLimitClause($statementInfo);
1544 $GLOBALS['reload'] = $this->hasCurrentDbChanged($db);
1545 $this->dbi
->selectDb($db);
1547 if (isset($GLOBALS['show_as_php'])) {
1548 // Only if we ask to see the php code
1549 // The following was copied from getQueryResponseForNoResultsReturned()
1550 // Delete if it's not needed in this context
1551 if ($this->isDeleteTransformationInfo($statementInfo)) {
1552 $this->deleteTransformationInfo($db, $table ??
'', $statementInfo);
1555 $message = $this->getMessageForNoRowsReturned($messageToShow, $statementInfo, 0);
1557 return Generator
::getMessage($message, $GLOBALS['sql_query'], 'success');
1560 // Handle disable/enable foreign key checks
1561 $defaultFkCheck = ForeignKey
::handleDisableCheckInit();
1563 [$result, $numRows, $unlimNumRows, $profilingResults, $errorMessage] = $this->executeTheQuery(
1569 $sqlQueryForBookmark,
1572 $warningMessages = $this->operations
->getWarningMessagesArray();
1574 // No rows returned -> move back to the calling page
1575 if (($numRows == 0 && $unlimNumRows == 0) ||
$statementInfo->isAffected ||
$result === false) {
1576 $htmlOutput = $this->getQueryResponseForNoResultsReturned(
1582 $displayResultsObject,
1590 // At least one row is returned -> displays a table with results
1591 $htmlOutput = $this->getQueryResponseForResultsReturned(
1597 $displayResultsObject,
1608 // Handle disable/enable foreign key checks
1609 ForeignKey
::handleDisableCheckCleanup($defaultFkCheck);
1611 foreach ($warningMessages as $warning) {
1612 $message = Message
::notice(htmlspecialchars($warning));
1613 $htmlOutput .= $message->getDisplay();
1620 * Function to define pos to display a row
1622 * @param int $numberOfLine Number of the line to display
1624 * @return int Start position to display the line
1626 private function getStartPosToDisplayRow(int $numberOfLine): int
1628 $maxRows = $_SESSION['tmpval']['max_rows'];
1630 return @((int) ceil($numberOfLine / $maxRows) - 1) * $maxRows;
1634 * Function to calculate new pos if pos is higher than number of rows
1635 * of displayed table
1637 * @param string $db Database name
1638 * @param string $table Table name
1639 * @param int|null $pos Initial position
1641 * @return int Number of pos to display last page
1643 public function calculatePosForLastPage(string $db, string $table, int|
null $pos): int
1645 if ($pos === null) {
1646 $pos = $_SESSION['tmpval']['pos'];
1649 $tableObject = new Table($table, $db, $this->dbi
);
1650 $unlimNumRows = $tableObject->countRecords(true);
1651 //If position is higher than number of rows
1652 if ($unlimNumRows <= $pos && $pos != 0) {
1653 return $this->getStartPosToDisplayRow($unlimNumRows);
1659 private function getIndexList(string $table, string $db): string
1661 $indexes = Index
::getFromTable($this->dbi
, $table, $db);
1662 $indexesDuplicates = Index
::findDuplicates($table, $db);
1663 $template = new Template();
1665 return $template->render('indexes', [
1666 'url_params' => $GLOBALS['urlParams'],
1667 'indexes' => $indexes,
1668 'indexes_duplicates' => $indexesDuplicates,
1672 /** @psalm-param list<array{Status: non-empty-string, Duration: numeric-string}> $profilingResults */
1673 private function getProfilingChart(array $profilingResults): string
1675 if ($profilingResults === []) {
1679 $profiling = $this->getDetailedProfilingStats($profilingResults);
1680 if ($profiling === []) {
1684 return $this->template
->render('sql/profiling_chart', ['profiling' => $profiling]);