/libraries/classes/Display/Results.php

Large files files are truncated, but you can click here to view the full file

<?php

declare(strict_types=1);

namespace PhpMyAdmin\Display;

use PhpMyAdmin\Config\SpecialSchemaLinks;
use PhpMyAdmin\Core;
use PhpMyAdmin\DatabaseInterface;
use PhpMyAdmin\FieldMetadata;
use PhpMyAdmin\Html\Generator;
use PhpMyAdmin\Index;
use PhpMyAdmin\Message;
use PhpMyAdmin\Plugins\Transformations\Output\Text_Octetstream_Sql;
use PhpMyAdmin\Plugins\Transformations\Output\Text_Plain_Json;
use PhpMyAdmin\Plugins\Transformations\Output\Text_Plain_Sql;
use PhpMyAdmin\Plugins\Transformations\Text_Plain_Link;
use PhpMyAdmin\Plugins\TransformationsPlugin;
use PhpMyAdmin\Relation;
use PhpMyAdmin\ResponseRenderer;
use PhpMyAdmin\Sanitize;
use PhpMyAdmin\Sql;
use PhpMyAdmin\SqlParser\Parser;
use PhpMyAdmin\SqlParser\Statements\SelectStatement;
use PhpMyAdmin\SqlParser\Utils\Query;
use PhpMyAdmin\Table;
use PhpMyAdmin\Template;
use PhpMyAdmin\Theme;
use PhpMyAdmin\Transformations;
use PhpMyAdmin\Url;
use PhpMyAdmin\Util;
use PhpMyAdmin\Utils\Gis;

use function __;
use function _pgettext;
use function array_filter;
use function array_keys;
use function array_merge;
use function array_shift;
use function bin2hex;
use function ceil;
use function class_exists;
use function count;
use function explode;
use function file_exists;
use function floor;
use function htmlspecialchars;
use function implode;
use function in_array;
use function intval;
use function is_array;
use function is_numeric;
use function json_encode;
use function mb_check_encoding;
use function mb_strlen;
use function mb_strpos;
use function mb_strtolower;
use function mb_strtoupper;
use function mb_substr;
use function md5;
use function mt_getrandmax;
use function pack;
use function preg_match;
use function preg_replace;
use function random_int;
use function str_contains;
use function str_replace;
use function strcasecmp;
use function strip_tags;
use function stripos;
use function strlen;
use function strpos;
use function strtoupper;
use function substr;
use function trim;

/**
 * Handle all the functionalities related to displaying results
 * of sql queries, stored procedure, browsing sql processes or
 * displaying binary log.
 */
class Results
{
    // Define constants
    public const NO_EDIT_OR_DELETE = 'nn';
    public const UPDATE_ROW = 'ur';
    public const DELETE_ROW = 'dr';
    public const KILL_PROCESS = 'kp';

    public const POSITION_LEFT = 'left';
    public const POSITION_RIGHT = 'right';
    public const POSITION_BOTH = 'both';
    public const POSITION_NONE = 'none';

    public const DISPLAY_FULL_TEXT = 'F';
    public const DISPLAY_PARTIAL_TEXT = 'P';

    public const HEADER_FLIP_TYPE_AUTO = 'auto';
    public const HEADER_FLIP_TYPE_CSS = 'css';
    public const HEADER_FLIP_TYPE_FAKE = 'fake';

    public const RELATIONAL_KEY = 'K';
    public const RELATIONAL_DISPLAY_COLUMN = 'D';

    public const GEOMETRY_DISP_GEOM = 'GEOM';
    public const GEOMETRY_DISP_WKT = 'WKT';
    public const GEOMETRY_DISP_WKB = 'WKB';

    public const SMART_SORT_ORDER = 'SMART';
    public const ASCENDING_SORT_DIR = 'ASC';
    public const DESCENDING_SORT_DIR = 'DESC';

    public const TABLE_TYPE_INNO_DB = 'InnoDB';
    public const ALL_ROWS = 'all';
    public const QUERY_TYPE_SELECT = 'SELECT';

    public const ROUTINE_PROCEDURE = 'procedure';
    public const ROUTINE_FUNCTION = 'function';

    public const ACTION_LINK_CONTENT_ICONS = 'icons';
    public const ACTION_LINK_CONTENT_TEXT = 'text';

    /**
     * @psalm-var array{
     *   server: int,
     *   db: string,
     *   table: string,
     *   goto: string,
     *   sql_query: string,
     *   unlim_num_rows: int|numeric-string,
     *   fields_meta: FieldMetadata[],
     *   is_count: bool|null,
     *   is_export: bool|null,
     *   is_func: bool|null,
     *   is_analyse: bool|null,
     *   num_rows: int|numeric-string,
     *   fields_cnt: int,
     *   querytime: float|null,
     *   text_dir: string|null,
     *   is_maint: bool|null,
     *   is_explain: bool|null,
     *   is_show: bool|null,
     *   is_browse_distinct: bool|null,
     *   showtable: array<string, mixed>|null,
     *   printview: string|null,
     *   highlight_columns: array|null,
     *   display_params: array|null,
     *   mime_map: array|null,
     *   editable: bool|null,
     *   unique_id: int,
     *   whereClauseMap: array,
     * }
     */
    public $properties = [
        /* server id */
        'server' => 0,

        /* Database name */
        'db' => '',

        /* Table name */
        'table' => '',

        /* the URL to go back in case of errors */
        'goto' => '',

        /* the SQL query */
        'sql_query' => '',

        /* the total number of rows returned by the SQL query without any appended "LIMIT" clause programmatically */
        'unlim_num_rows' => 0,

        /* meta information about fields */
        'fields_meta' => [],

        'is_count' => null,

        'is_export' => null,

        'is_func' => null,

        'is_analyse' => null,

        /* the total number of rows returned by the SQL query */
        'num_rows' => 0,

        /* the total number of fields returned by the SQL query */
        'fields_cnt' => 0,

        /* time taken for execute the SQL query */
        'querytime' => null,

        'text_dir' => null,

        'is_maint' => null,

        'is_explain' => null,

        'is_show' => null,

        'is_browse_distinct' => null,

        /* table definitions */
        'showtable' => null,

        'printview' => null,

        /* column names to highlight */
        'highlight_columns' => null,

        /* display information */
        'display_params' => null,

        /* mime types information of fields */
        'mime_map' => null,

        'editable' => null,

        /* random unique ID to distinguish result set */
        'unique_id' => 0,

        /* where clauses for each row, each table in the row */
        'whereClauseMap' => [],
    ];

    /**
     * This variable contains the column transformation information
     * for some of the system databases.
     * One element of this array represent all relevant columns in all tables in
     * one specific database
     *
     * @var array<string, array<string, array<string, string[]>>>
     * @psalm-var array<string, array<string, array<string, array{string, class-string, string}>>> $transformationInfo
     */
    public $transformationInfo;

    /** @var Relation */
    private $relation;

    /** @var Transformations */
    private $transformations;

    /** @var Template */
    public $template;

    /**
     * @param string $db       the database name
     * @param string $table    the table name
     * @param int    $server   the server id
     * @param string $goto     the URL to go back in case of errors
     * @param string $sqlQuery the SQL query
     *
     * @access public
     */
    public function __construct($db, $table, $server, $goto, $sqlQuery)
    {
        global $dbi;

        $this->relation = new Relation($dbi);
        $this->transformations = new Transformations();
        $this->template = new Template();

        $this->setDefaultTransformations();

        $this->properties['db'] = $db;
        $this->properties['table'] = $table;
        $this->properties['server'] = $server;
        $this->properties['goto'] = $goto;
        $this->properties['sql_query'] = $sqlQuery;
        $this->properties['unique_id'] = random_int(0, mt_getrandmax());
    }

    /**
     * Sets default transformations for some columns
     */
    private function setDefaultTransformations(): void
    {
        $jsonHighlightingData = [
            'libraries/classes/Plugins/Transformations/Output/Text_Plain_Json.php',
            Text_Plain_Json::class,
            'Text_Plain',
        ];
        $sqlHighlightingData = [
            'libraries/classes/Plugins/Transformations/Output/Text_Plain_Sql.php',
            Text_Plain_Sql::class,
            'Text_Plain',
        ];
        $blobSqlHighlightingData = [
            'libraries/classes/Plugins/Transformations/Output/Text_Octetstream_Sql.php',
            Text_Octetstream_Sql::class,
            'Text_Octetstream',
        ];
        $linkData = [
            'libraries/classes/Plugins/Transformations/Text_Plain_Link.php',
            Text_Plain_Link::class,
            'Text_Plain',
        ];
        $this->transformationInfo = [
            'information_schema' => [
                'events' => ['event_definition' => $sqlHighlightingData],
                'processlist' => ['info' => $sqlHighlightingData],
                'routines' => ['routine_definition' => $sqlHighlightingData],
                'triggers' => ['action_statement' => $sqlHighlightingData],
                'views' => ['view_definition' => $sqlHighlightingData],
            ],
            'mysql' => [
                'event' => [
                    'body' => $blobSqlHighlightingData,
                    'body_utf8' => $blobSqlHighlightingData,
                ],
                'general_log' => ['argument' => $sqlHighlightingData],
                'help_category' => ['url' => $linkData],
                'help_topic' => [
                    'example' => $sqlHighlightingData,
                    'url' => $linkData,
                ],
                'proc' => [
                    'param_list' => $blobSqlHighlightingData,
                    'returns' => $blobSqlHighlightingData,
                    'body' => $blobSqlHighlightingData,
                    'body_utf8' => $blobSqlHighlightingData,
                ],
                'slow_log' => ['sql_text' => $sqlHighlightingData],
            ],
        ];

        $cfgRelation = $this->relation->getRelationsParam();
        if (! $cfgRelation['db']) {
            return;
        }

        $relDb = [];
        if (! empty($cfgRelation['history'])) {
            $relDb[$cfgRelation['history']] = ['sqlquery' => $sqlHighlightingData];
        }

        if (! empty($cfgRelation['bookmark'])) {
            $relDb[$cfgRelation['bookmark']] = ['query' => $sqlHighlightingData];
        }

        if (! empty($cfgRelation['tracking'])) {
            $relDb[$cfgRelation['tracking']] = [
                'schema_sql' => $sqlHighlightingData,
                'data_sql' => $sqlHighlightingData,
            ];
        }

        if (! empty($cfgRelation['favorite'])) {
            $relDb[$cfgRelation['favorite']] = ['tables' => $jsonHighlightingData];
        }

        if (! empty($cfgRelation['recent'])) {
            $relDb[$cfgRelation['recent']] = ['tables' => $jsonHighlightingData];
        }

        if (! empty($cfgRelation['savedsearches'])) {
            $relDb[$cfgRelation['savedsearches']] = ['search_data' => $jsonHighlightingData];
        }

        if (! empty($cfgRelation['designer_settings'])) {
            $relDb[$cfgRelation['designer_settings']] = ['settings_data' => $jsonHighlightingData];
        }

        if (! empty($cfgRelation['table_uiprefs'])) {
            $relDb[$cfgRelation['table_uiprefs']] = ['prefs' => $jsonHighlightingData];
        }

        if (! empty($cfgRelation['userconfig'])) {
            $relDb[$cfgRelation['userconfig']] = ['config_data' => $jsonHighlightingData];
        }

        if (! empty($cfgRelation['export_templates'])) {
            $relDb[$cfgRelation['export_templates']] = ['template_data' => $jsonHighlightingData];
        }

        $this->transformationInfo[$cfgRelation['db']] = $relDb;
    }

    /**
     * Set properties which were not initialized at the constructor
     *
     * @param int|string                $unlimNumRows     the total number of rows returned by the SQL query without
     *                                                    any appended "LIMIT" clause programmatically
     * @param FieldMetadata[]           $fieldsMeta       meta information about fields
     * @param bool                      $isCount          statement is SELECT COUNT
     * @param bool                      $isExport         statement contains INTO OUTFILE
     * @param bool                      $isFunction       statement contains a function like SUM()
     * @param bool                      $isAnalyse        statement contains PROCEDURE ANALYSE
     * @param int|string                $numRows          total no. of rows returned by SQL query
     * @param int                       $fieldsCount      total no.of fields returned by SQL query
     * @param double                    $queryTime        time taken for execute the SQL query
     * @param string                    $textDirection    text direction
     * @param bool                      $isMaintenance    statement contains a maintenance command
     * @param bool                      $isExplain        statement contains EXPLAIN
     * @param bool                      $isShow           statement contains SHOW
     * @param array<string, mixed>|null $showTable        table definitions
     * @param string|null               $printView        print view was requested
     * @param bool                      $editable         whether the results set is editable
     * @param bool                      $isBrowseDistinct whether browsing distinct values
     * @psalm-param int|numeric-string $unlimNumRows
     * @psalm-param int|numeric-string $numRows
     */
    public function setProperties(
        $unlimNumRows,
        array $fieldsMeta,
        $isCount,
        $isExport,
        $isFunction,
        $isAnalyse,
        $numRows,
        $fieldsCount,
        $queryTime,
        $textDirection,
        $isMaintenance,
        $isExplain,
        $isShow,
        ?array $showTable,
        $printView,
        $editable,
        $isBrowseDistinct
    ): void {
        $this->properties['unlim_num_rows'] = $unlimNumRows;
        $this->properties['fields_meta'] = $fieldsMeta;
        $this->properties['is_count'] = $isCount;
        $this->properties['is_export'] = $isExport;
        $this->properties['is_func'] = $isFunction;
        $this->properties['is_analyse'] = $isAnalyse;
        $this->properties['num_rows'] = $numRows;
        $this->properties['fields_cnt'] = $fieldsCount;
        $this->properties['querytime'] = $queryTime;
        $this->properties['text_dir'] = $textDirection;
        $this->properties['is_maint'] = $isMaintenance;
        $this->properties['is_explain'] = $isExplain;
        $this->properties['is_show'] = $isShow;
        $this->properties['showtable'] = $showTable;
        $this->properties['printview'] = $printView;
        $this->properties['editable'] = $editable;
        $this->properties['is_browse_distinct'] = $isBrowseDistinct;
    }

    /**
     * Defines the parts to display for a print view
     *
     * @param array $displayParts the parts to display
     *
     * @return array the modified display parts
     *
     * @access private
     */
    private function setDisplayPartsForPrintView(array $displayParts)
    {
        // set all elements to false!
        $displayParts['edit_lnk'] = self::NO_EDIT_OR_DELETE; // no edit link
        $displayParts['del_lnk'] = self::NO_EDIT_OR_DELETE; // no delete link
        $displayParts['sort_lnk'] = '0';
        $displayParts['nav_bar'] = '0';
        $displayParts['bkm_form'] = '0';
        $displayParts['text_btn'] = '0';
        $displayParts['pview_lnk'] = '0';

        return $displayParts;
    }

    /**
     * Defines the parts to display for a SHOW statement
     *
     * @param array $displayParts the parts to display
     *
     * @return array the modified display parts
     *
     * @access private
     */
    private function setDisplayPartsForShow(array $displayParts)
    {
        preg_match(
            '@^SHOW[[:space:]]+(VARIABLES|(FULL[[:space:]]+)?'
            . 'PROCESSLIST|STATUS|TABLE|GRANTS|CREATE|LOGS|DATABASES|FIELDS'
            . ')@i',
            $this->properties['sql_query'],
            $which
        );

        $bIsProcessList = isset($which[1]);
        if ($bIsProcessList) {
            $str = ' ' . strtoupper($which[1]);
            $bIsProcessList = $bIsProcessList
                && strpos($str, 'PROCESSLIST') > 0;
        }

        if ($bIsProcessList) {
            // no edit link
            $displayParts['edit_lnk'] = self::NO_EDIT_OR_DELETE;
            // "kill process" type edit link
            $displayParts['del_lnk'] = self::KILL_PROCESS;
        } else {
            // Default case -> no links
            // no edit link
            $displayParts['edit_lnk'] = self::NO_EDIT_OR_DELETE;
            // no delete link
            $displayParts['del_lnk'] = self::NO_EDIT_OR_DELETE;
        }

        // Other settings
        $displayParts['sort_lnk'] = '0';
        $displayParts['nav_bar'] = '0';
        $displayParts['bkm_form'] = '1';
        $displayParts['text_btn'] = '1';
        $displayParts['pview_lnk'] = '1';

        return $displayParts;
    }

    /**
     * Defines the parts to display for statements not related to data
     *
     * @param array $displayParts the parts to display
     *
     * @return array the modified display parts
     *
     * @access private
     */
    private function setDisplayPartsForNonData(array $displayParts)
    {
        // Statement is a "SELECT COUNT", a
        // "CHECK/ANALYZE/REPAIR/OPTIMIZE/CHECKSUM", an "EXPLAIN" one or
        // contains a "PROC ANALYSE" part
        $displayParts['edit_lnk'] = self::NO_EDIT_OR_DELETE; // no edit link
        $displayParts['del_lnk'] = self::NO_EDIT_OR_DELETE; // no delete link
        $displayParts['sort_lnk'] = '0';
        $displayParts['nav_bar'] = '0';
        $displayParts['bkm_form'] = '1';

        if ($this->properties['is_maint']) {
            $displayParts['text_btn'] = '1';
        } else {
            $displayParts['text_btn'] = '0';
        }

        $displayParts['pview_lnk'] = '1';

        return $displayParts;
    }

    /**
     * Defines the parts to display for other statements (probably SELECT)
     *
     * @param array $displayParts the parts to display
     *
     * @return array the modified display parts
     *
     * @access private
     */
    private function setDisplayPartsForSelect(array $displayParts)
    {
        // Other statements (ie "SELECT" ones) -> updates
        // $displayParts['edit_lnk'], $displayParts['del_lnk'] and
        // $displayParts['text_btn'] (keeps other default values)

        /** @var FieldMetadata[] $fieldsMeta */
        $fieldsMeta = $this->properties['fields_meta'];
        $previousTable = '';
        $displayParts['text_btn'] = '1';
        $numberOfColumns = $this->properties['fields_cnt'];

        for ($i = 0; $i < $numberOfColumns; $i++) {
            $isLink = ($displayParts['edit_lnk'] != self::NO_EDIT_OR_DELETE)
                || ($displayParts['del_lnk'] != self::NO_EDIT_OR_DELETE)
                || ($displayParts['sort_lnk'] != '0');

            // Displays edit/delete/sort/insert links?
            if (
                $isLink
                && $previousTable != ''
                && $fieldsMeta[$i]->table != ''
                && $fieldsMeta[$i]->table != $previousTable
            ) {
                // don't display links
                $displayParts['edit_lnk'] = self::NO_EDIT_OR_DELETE;
                $displayParts['del_lnk'] = self::NO_EDIT_OR_DELETE;
                /**
                 * @todo May be problematic with same field names
                 * in two joined table.
                 */
                if ($displayParts['text_btn'] == '1') {
                    break;
                }
            }

            // Always display print view link
            $displayParts['pview_lnk'] = '1';
            if ($fieldsMeta[$i]->table == '') {
                continue;
            }

            $previousTable = $fieldsMeta[$i]->table;
        }

        if ($previousTable == '') { // no table for any of the columns
            // don't display links
            $displayParts['edit_lnk'] = self::NO_EDIT_OR_DELETE;
            $displayParts['del_lnk'] = self::NO_EDIT_OR_DELETE;
        }

        return $displayParts;
    }

    /**
     * Defines the parts to display for the results of a SQL query
     * and the total number of rows
     *
     * @see     getTable()
     *
     * @param array $displayParts the parts to display (see a few
     *                            lines above for explanations)
     *
     * @return array the first element is an array with explicit indexes
     *               for all the display elements
     *               the second element is the total number of rows returned
     *               by the SQL query without any programmatically appended
     *               LIMIT clause (just a copy of $unlim_num_rows if it exists,
     *               else computed inside this function)
     *
     * @access private
     */
    private function setDisplayPartsAndTotal(array $displayParts)
    {
        global $dbi;

        $theTotal = 0;

        // 1. Following variables are needed for use in isset/empty or
        //    use with array indexes or safe use in foreach
        $db = $this->properties['db'];
        $table = $this->properties['table'];
        $unlimNumRows = $this->properties['unlim_num_rows'];
        $numRows = $this->properties['num_rows'];
        $printView = $this->properties['printview'];

        // 2. Updates the display parts
        if ($printView == '1') {
            $displayParts = $this->setDisplayPartsForPrintView($displayParts);
        } elseif (
            $this->properties['is_count'] || $this->properties['is_analyse']
            || $this->properties['is_maint'] || $this->properties['is_explain']
        ) {
            $displayParts = $this->setDisplayPartsForNonData($displayParts);
        } elseif ($this->properties['is_show']) {
            $displayParts = $this->setDisplayPartsForShow($displayParts);
        } else {
            $displayParts = $this->setDisplayPartsForSelect($displayParts);
        }

        // 3. Gets the total number of rows if it is unknown
        if ($unlimNumRows > 0) {
            $theTotal = $unlimNumRows;
        } elseif (
            ($displayParts['nav_bar'] == '1')
            || ($displayParts['sort_lnk'] == '1')
            && (strlen($db) > 0 && strlen($table) > 0)
        ) {
            $theTotal = $dbi->getTable($db, $table)->countRecords();
        }

        // if for COUNT query, number of rows returned more than 1
        // (may be being used GROUP BY)
        if ($this->properties['is_count'] && $numRows > 1) {
            $displayParts['nav_bar'] = '1';
            $displayParts['sort_lnk'] = '1';
        }

        // 4. If navigation bar or sorting fields names URLs should be
        //    displayed but there is only one row, change these settings to
        //    false
        if ($displayParts['nav_bar'] == '1' || $displayParts['sort_lnk'] == '1') {
            // - Do not display sort links if less than 2 rows.
            // - For a VIEW we (probably) did not count the number of rows
            //   so don't test this number here, it would remove the possibility
            //   of sorting VIEW results.
            $tableObject = new Table($table, $db);
            if ($unlimNumRows < 2 && ! $tableObject->isView()) {
                $displayParts['sort_lnk'] = '0';
            }
        }

        return [
            $displayParts,
            $theTotal,
        ];
    }

    /**
     * Return true if we are executing a query in the form of
     * "SELECT * FROM <a table> ..."
     *
     * @see getTableHeaders(), getColumnParams()
     *
     * @param array $analyzedSqlResults analyzed sql results
     *
     * @access private
     */
    private function isSelect(array $analyzedSqlResults): bool
    {
        return ! ($this->properties['is_count']
                || $this->properties['is_export']
                || $this->properties['is_func']
                || $this->properties['is_analyse'])
            && ! empty($analyzedSqlResults['select_from'])
            && ! empty($analyzedSqlResults['statement']->from)
            && (count($analyzedSqlResults['statement']->from) === 1)
            && ! empty($analyzedSqlResults['statement']->from[0]->table);
    }

    /**
     * Get a navigation button
     *
     * @see     getMoveBackwardButtonsForTableNavigation(),
     *          getMoveForwardButtonsForTableNavigation()
     *
     * @param string $caption         iconic caption for button
     * @param string $title           text for button
     * @param int    $pos             position for next query
     * @param string $htmlSqlQuery    query ready for display
     * @param bool   $back            whether 'begin' or 'previous'
     * @param string $onsubmit        optional onsubmit clause
     * @param string $inputForRealEnd optional hidden field for special treatment
     * @param string $onclick         optional onclick clause
     *
     * @return string                     html content
     *
     * @access private
     */
    private function getTableNavigationButton(
        $caption,
        $title,
        $pos,
        $htmlSqlQuery,
        $back,
        $onsubmit = '',
        $inputForRealEnd = '',
        $onclick = ''
    ) {
        $captionOutput = '';
        if ($back) {
            if (Util::showIcons('TableNavigationLinksMode')) {
                $captionOutput .= $caption;
            }

            if (Util::showText('TableNavigationLinksMode')) {
                $captionOutput .= '&nbsp;' . $title;
            }
        } else {
            if (Util::showText('TableNavigationLinksMode')) {
                $captionOutput .= $title;
            }

            if (Util::showIcons('TableNavigationLinksMode')) {
                $captionOutput .= '&nbsp;' . $caption;
            }
        }

        return $this->template->render('display/results/table_navigation_button', [
            'db' => $this->properties['db'],
            'table' => $this->properties['table'],
            'sql_query' => $htmlSqlQuery,
            'pos' => $pos,
            'is_browse_distinct' => $this->properties['is_browse_distinct'],
            'goto' => $this->properties['goto'],
            'input_for_real_end' => $inputForRealEnd,
            'caption_output' => $captionOutput,
            'title' => $title,
            'onsubmit' => $onsubmit,
            'onclick' => $onclick,
        ]);
    }

    /**
     * Possibly return a page selector for table navigation
     *
     * @return array ($output, $nbTotalPage)
     *
     * @access private
     */
    private function getHtmlPageSelector(): array
    {
        $pageNow = (int) floor($_SESSION['tmpval']['pos'] / $_SESSION['tmpval']['max_rows']) + 1;

        $nbTotalPage = (int) ceil($this->properties['unlim_num_rows'] / $_SESSION['tmpval']['max_rows']);

        $output = '';
        if ($nbTotalPage > 1) {
            $urlParams = [
                'db' => $this->properties['db'],
                'table' => $this->properties['table'],
                'sql_query' => $this->properties['sql_query'],
                'goto' => $this->properties['goto'],
                'is_browse_distinct' => $this->properties['is_browse_distinct'],
            ];

            $output = $this->template->render('display/results/page_selector', [
                'url_params' => $urlParams,
                'page_selector' => Util::pageselector(
                    'pos',
                    $_SESSION['tmpval']['max_rows'],
                    $pageNow,
                    $nbTotalPage,
                    200,
                    5,
                    5,
                    20,
                    10
                ),
            ]);
        }

        return [
            $output,
            $nbTotalPage,
        ];
    }

    /**
     * Get a navigation bar to browse among the results of a SQL query
     *
     * @see getTable()
     *
     * @param int   $posNext       the offset for the "next" page
     * @param int   $posPrevious   the offset for the "previous" page
     * @param bool  $isInnodb      whether its InnoDB or not
     * @param array $sortByKeyData the sort by key dialog
     *
     * @return array
     */
    private function getTableNavigation(
        $posNext,
        $posPrevious,
        $isInnodb,
        array $sortByKeyData
    ): array {
        $isShowingAll = $_SESSION['tmpval']['max_rows'] === self::ALL_ROWS;

        // Move to the beginning or to the previous page
        $moveBackwardButtons = '';
        if ($_SESSION['tmpval']['pos'] && ! $isShowingAll) {
            $moveBackwardButtons = $this->getMoveBackwardButtonsForTableNavigation(
                htmlspecialchars($this->properties['sql_query']),
                $posPrevious
            );
        }

        $pageSelector = '';
        $numberTotalPage = 1;
        if (! $isShowingAll) {
            [
                $pageSelector,
                $numberTotalPage,
            ] = $this->getHtmlPageSelector();
        }

        // Move to the next page or to the last one
        $moveForwardButtons = '';
        if (
            $this->properties['unlim_num_rows'] === -1 // view with unknown number of rows
            || (! $isShowingAll
            && intval($_SESSION['tmpval']['pos']) + intval($_SESSION['tmpval']['max_rows'])
                < $this->properties['unlim_num_rows']
            && $this->properties['num_rows'] >= $_SESSION['tmpval']['max_rows'])
        ) {
            $moveForwardButtons = $this->getMoveForwardButtonsForTableNavigation(
                htmlspecialchars($this->properties['sql_query']),
                $posNext,
                $isInnodb
            );
        }

        $hiddenFields = [
            'db' => $this->properties['db'],
            'table' => $this->properties['table'],
            'server' => $this->properties['server'],
            'sql_query' => $this->properties['sql_query'],
            'is_browse_distinct' => $this->properties['is_browse_distinct'],
            'goto' => $this->properties['goto'],
        ];

        return [
            'move_backward_buttons' => $moveBackwardButtons,
            'page_selector' => $pageSelector,
            'move_forward_buttons' => $moveForwardButtons,
            'number_total_page' => $numberTotalPage,
            'has_show_all' => $GLOBALS['cfg']['ShowAll'] || ($this->properties['unlim_num_rows'] <= 500),
            'hidden_fields' => $hiddenFields,
            'session_max_rows' => $isShowingAll ? $GLOBALS['cfg']['MaxRows'] : 'all',
            'is_showing_all' => $isShowingAll,
            'max_rows' => $_SESSION['tmpval']['max_rows'],
            'pos' => $_SESSION['tmpval']['pos'],
            'sort_by_key' => $sortByKeyData,
        ];
    }

    /**
     * Prepare move backward buttons - previous and first
     *
     * @see getTableNavigation()
     *
     * @param string $htmlSqlQuery the sql encoded by html special characters
     * @param int    $posPrev      the offset for the "previous" page
     *
     * @return string                 html content
     *
     * @access private
     */
    private function getMoveBackwardButtonsForTableNavigation(
        $htmlSqlQuery,
        $posPrev
    ) {
        return $this->getTableNavigationButton(
            '&lt;&lt;',
            _pgettext('First page', 'Begin'),
            0,
            $htmlSqlQuery,
            true
        )
        . $this->getTableNavigationButton(
            '&lt;',
            _pgettext('Previous page', 'Previous'),
            $posPrev,
            $htmlSqlQuery,
            true
        );
    }

    /**
     * Prepare move forward buttons - next and last
     *
     * @see getTableNavigation()
     *
     * @param string $htmlSqlQuery the sql encoded by htmlspecialchars()
     * @param int    $posNext      the offset for the "next" page
     * @param bool   $isInnodb     whether it's InnoDB or not
     *
     * @return string   html content
     *
     * @access private
     */
    private function getMoveForwardButtonsForTableNavigation(
        $htmlSqlQuery,
        $posNext,
        $isInnodb
    ) {
        // display the Next button
        $buttonsHtml = $this->getTableNavigationButton(
            '&gt;',
            _pgettext('Next page', 'Next'),
            $posNext,
            $htmlSqlQuery,
            false
        );

        // prepare some options for the End button
        if ($isInnodb && $this->properties['unlim_num_rows'] > $GLOBALS['cfg']['MaxExactCount']) {
            $inputForRealEnd = '<input id="real_end_input" type="hidden" name="find_real_end" value="1">';
            // no backquote around this message
            $onclick = '';
        } else {
            $inputForRealEnd = $onclick = '';
        }

        $maxRows = (int) $_SESSION['tmpval']['max_rows'];
        $onsubmit = 'onsubmit="return '
            . (intval($_SESSION['tmpval']['pos'])
                + $maxRows
                < $this->properties['unlim_num_rows']
                && $this->properties['num_rows'] >= $maxRows
            ? 'true'
            : 'false') . '"';

        // display the End button
        return $buttonsHtml . $this->getTableNavigationButton(
            '&gt;&gt;',
            _pgettext('Last page', 'End'),
            @((int) ceil(
                $this->properties['unlim_num_rows']
                / $_SESSION['tmpval']['max_rows']
            ) - 1) * $maxRows,
            $htmlSqlQuery,
            false,
            $onsubmit,
            $inputForRealEnd,
            $onclick
        );
    }

    /**
     * Get the headers of the results table, for all of the columns
     *
     * @see getTableHeaders()
     *
     * @param array  $displayParts              which elements to display
     * @param array  $analyzedSqlResults        analyzed sql results
     * @param array  $sortExpression            sort expression
     * @param array  $sortExpressionNoDirection sort expression
     *                                            without direction
     * @param array  $sortDirection             sort direction
     * @param bool   $isLimitedDisplay          with limited operations
     *                                            or not
     * @param string $unsortedSqlQuery          query without the sort part
     *
     * @return string html content
     *
     * @access private
     */
    private function getTableHeadersForColumns(
        array $displayParts,
        array $analyzedSqlResults,
        array $sortExpression,
        array $sortExpressionNoDirection,
        array $sortDirection,
        $isLimitedDisplay,
        $unsortedSqlQuery
    ) {
        // required to generate sort links that will remember whether the
        // "Show all" button has been clicked
        $sqlMd5 = md5($this->properties['server'] . $this->properties['db'] . $this->properties['sql_query']);
        $sessionMaxRows = $isLimitedDisplay
            ? 0
            : $_SESSION['tmpval']['query'][$sqlMd5]['max_rows'];

        // Following variable are needed for use in isset/empty or
        // use with array indexes/safe use in the for loop
        $highlightColumns = $this->properties['highlight_columns'];
        /** @var FieldMetadata[] $fieldsMeta */
        $fieldsMeta = $this->properties['fields_meta'];

        // Prepare Display column comments if enabled
        // ($GLOBALS['cfg']['ShowBrowseComments']).
        $commentsMap = $this->getTableCommentsArray($analyzedSqlResults);

        [$colOrder, $colVisib] = $this->getColumnParams($analyzedSqlResults);

        // optimize: avoid calling a method on each iteration
        $numberOfColumns = $this->properties['fields_cnt'];

        $columns = [];

        for ($j = 0; $j < $numberOfColumns; $j++) {
            // PHP 7.4 fix for accessing array offset on bool
            $colVisibCurrent = is_array($colVisib) && isset($colVisib[$j]) ? $colVisib[$j] : null;

            // assign $i with the appropriate column order
            $i = $colOrder ? $colOrder[$j] : $j;

            //  See if this column should get highlight because it's used in the
            //  where-query.
            $name = $fieldsMeta[$i]->name;
            $conditionField = isset($highlightColumns[$name])
                || isset($highlightColumns[Util::backquote($name)]);

            // Prepare comment-HTML-wrappers for each row, if defined/enabled.
            $comments = $this->getCommentForRow($commentsMap, $fieldsMeta[$i]);
            $displayParams = $this->properties['display_params'] ?? [];

            if (($displayParts['sort_lnk'] == '1') && ! $isLimitedDisplay) {
                [$orderLink, $sortedHeaderData] = $this->getOrderLinkAndSortedHeaderHtml(
                    $fieldsMeta[$i],
                    $sortExpression,
                    $sortExpressionNoDirection,
                    $i,
                    $unsortedSqlQuery,
                    $sessionMaxRows,
                    $comments,
                    $sortDirection,
                    $colVisib,
                    $colVisibCurrent
                );

                $columns[] = $sortedHeaderData;

                $displayParams['desc'][] = '    <th '
                    . 'class="draggable'
                    . ($conditionField ? ' condition' : '')
                    . '" data-column="' . htmlspecialchars($fieldsMeta[$i]->name)
                    . '">' . "\n" . $orderLink . $comments . '    </th>' . "\n";
            } else {
                // Results can't be sorted
                $columns[] = $this->getDraggableClassForNonSortableColumns(
                    $colVisib,
                    $colVisibCurrent,
                    $conditionField,
                    $fieldsMeta[$i],
                    $comments
                );

                $displayParams['desc'][] = '    <th '
                    . 'class="draggable'
                    . ($conditionField ? ' condition"' : '')
                    . '" data-column="' . htmlspecialchars((string) $fieldsMeta[$i]->name)
                    . '">        '
                    . htmlspecialchars((string) $fieldsMeta[$i]->name)
                    . $comments . '    </th>';
            }

            $this->properties['display_params'] = $displayParams;
        }

        return $this->template->render('display/results/table_headers_for_columns', [
            'is_sortable' => $displayParts['sort_lnk'] == '1' && ! $isLimitedDisplay,
            'columns' => $columns,
        ]);
    }

    /**
     * Get the headers of the results table
     *
     * @see getTable()
     *
     * @param array        $displayParts              which elements to display
     * @param array        $analyzedSqlResults        analyzed sql results
     * @param string       $unsortedSqlQuery          the unsorted sql query
     * @param array        $sortExpression            sort expression
     * @param array|string $sortExpressionNoDirection sort expression without direction
     * @param array        $sortDirection             sort direction
     * @param bool         $isLimitedDisplay          with limited operations or not
     *
     * @return array
     */
    private function getTableHeaders(
        array &$displayParts,
        array $analyzedSqlResults,
        $unsortedSqlQuery,
        array $sortExpression = [],
        $sortExpressionNoDirection = '',
        array $sortDirection = [],
        $isLimitedDisplay = false
    ): array {
        // Needed for use in isset/empty or
        // use with array indexes/safe use in foreach
        $printView = $this->properties['printview'];
        $displayParams = $this->properties['display_params'];

        // Output data needed for column reordering and show/hide column
        $columnOrder = $this->getDataForResettingColumnOrder($analyzedSqlResults);

        $displayParams['emptypre'] = 0;
        $displayParams['emptyafter'] = 0;
        $displayParams['textbtn'] = '';
        $fullOrPartialTextLink = '';

        $this->properties['display_params'] = $displayParams;

        // Display options (if we are not in print view)
        $optionsBlock = [];
        if (! (isset($printView) && ($printView == '1')) && ! $isLimitedDisplay) {
            $optionsBlock = $this->getOptionsBlock();

            // prepare full/partial text button or link
            $fullOrPartialTextLink = $this->getFullOrPartialTextButtonOrLink();
        }

        // 1. Set $colspan and generate html with full/partial
        // text button or link
        [$colspan, $buttonHtml] = $this->getFieldVisibilityParams($displayParts, $fullOrPartialTextLink);

        // 2. Displays the fields' name
        // 2.0 If sorting links should be used, checks if the query is a "JOIN"
        //     statement (see 2.1.3)

        // See if we have to highlight any header fields of a WHERE query.
        // Uses SQL-Parser results.
        $this->setHighlightedColumnGlobalField($analyzedSqlResults);

        // Get the headers for all of the columns
        $tableHeadersForColumns = $this->getTableHeadersForColumns(
            $displayParts,
            $analyzedSqlResults,
            $sortExpression,
            $sortExpressionNoDirection,
            $sortDirection,
            $isLimitedDisplay,
            $unsortedSqlQuery
        );

        // Display column at rightside - checkboxes or empty column
        $columnAtRightSide = '';
        if (! $printView) {
            $columnAtRightSide = $this->getColumnAtRightSide($displayParts, $fullOrPartialTextLink, $colspan);
        }

        return [
            'column_order' => $columnOrder,
            'options' => $optionsBlock,
            'has_bulk_actions_form' => $displayParts['del_lnk'] === self::DELETE_ROW
                || $displayParts['del_lnk'] === self::KILL_PROCESS,
            'button' => $buttonHtml,
            'table_headers_for_columns' => $tableHeadersForColumns,
            'column_at_right_side' => $columnAtRightSide,
        ];
    }

    /**
     * Prepare unsorted sql query and sort by key drop down
     *
     * @see getTableHeaders()
     *
     * @param array      $analyzedSqlResults analyzed sql results
     * @param array|null $sortExpression     sort expression
     *
     * @return array     two element array - $unsorted_sql_query, $drop_down_html
     *
     * @access private
     */
    private function getUnsortedSqlAndSortByKeyDropDown(
        array $analyzedSqlResults,
        ?array $sortExpression
    ) {
        $dropDownData = [];

        $unsortedSqlQuery = Query::replaceClause(
            $analyzedSqlResults['statement'],
            $analyzedSqlResults['parser']->list,
            'ORDER BY',
            ''
        );

        // Data is sorted by indexes only if it there is only one table.
        if ($this->isSelect($analyzedSqlResults)) {
            // grab indexes data:
            $indexes = Index::getFromTable($this->properties['table'], $this->properties['db']);

            // do we have any index?
            if (! empty($indexes)) {
                $dropDownData = $this->getSortByKeyDropDown($indexes, $sortExpression, $unsortedSqlQuery);
            }
        }

        return [$unsortedSqlQuery, $dropDownData];
    }

    /**
     * Prepare sort by key dropdown - html code segment
     *
     * @see getTableHeaders()
     *
     * @param Index[]    $indexes          the indexes of the table for sort criteria
     * @param array|null $sortExpression   the sort expression
     * @param string     $unsortedSqlQuery the unsorted sql query
     *
     * @return array
     */
    private function getSortByKeyDropDown(
        $indexes,
        ?array $sortExpression,
        $unsortedSqlQuery
    ): array {
        $hiddenFields = [
            'db' => $this->properties['db'],
            'table' => $this->properties['table'],
            'server' => $this->properties['server'],
            'sort_by_key' => '1',
        ];

        // Keep the number of rows (25, 50, 100, ...) when changing sort key value
        if (isset($_SESSION['tmpval']) && isset($_SESSION['tmpval']['max_rows'])) {
            $hiddenFields['session_max_rows'] = $_SESSION['tmpval']['max_rows'];
        }

        $isIndexUsed = false;
        $localOrder = is_array($sortExpression) ? implode(', ', $sortExpression) : '';

        $options = [];
        foreach ($indexes as $index) {
            $ascSort = '`'
                . implode('` ASC, `', array_keys($index->getColumns()))
                . '` ASC';

            $descSort = '`'
                . implode('` DESC, `', array_keys($index->getColumns()))
                . '` DESC';

            $isIndexUsed = $isIndexUsed
                || $localOrder === $ascSort
                || $localOrder === $descSort;

            $unsortedSqlQueryFirstPart = $unsortedSqlQuery;
            $unsortedSqlQuerySecondPart = '';
            if (
                preg_match(
                    '@(.*)([[:space:]](LIMIT (.*)|PROCEDURE (.*)|FOR UPDATE|LOCK IN SHARE MODE))@is',
                    $unsortedSqlQuery,
                    $myReg
                )
            ) {
                $unsortedSqlQueryFirstPart = $myReg[1];
                $unsortedSqlQuerySecondPart = $myReg[2];
            }

            $options[] = [
                'value' => $unsortedSqlQueryFirstPart . ' ORDER BY '
                    . $ascSort . $unsortedSqlQuerySecondPart,
                'content' => $index->getName() . ' (ASC)',
                'is_selected' => $localOrder === $ascSort,
            ];
            $options[] = [
                'value' => $unsortedSqlQueryFirstPart . ' ORDER BY '
                    . $descSort . $unsortedSqlQuerySecondPart,
                'content' => $index->getName() . ' (DESC)',
                'is_selected' => $localOrder === $descSort,
            ];
        }

        $options[] = [
            'value' => $unsortedSqlQuery,
            'content' => __('None'),
            'is_selected' => ! $isIndexUsed,
        ];

        return ['hidden_fields' => $hiddenFields, 'options' => $options];
    }

    /**
     * Set column span, row span and prepare html with full/partial
     * text button or link
     *
     * @see getTableHeaders()
     *
     * @param array  $displayParts          which elements to display
     * @param string $fullOrPartialTextLink full/partial link or text button
     *
     * @return array 2 element array - $colspan, $button_html
     *
     * @access private
     */
    private function getFieldVisibilityParams(
        array &$displayParts,
        $fullOrPartialTextLink
    ) {
        $buttonHtml = '';
        $displayParams = $this->properties['display_params'];

        // 1. Displays the full/partial text button (part 1)...
        $buttonHtml .= '<thead class="table-light"><tr>' . "\n";

        $emptyPreCondition = $displayParts['edit_lnk'] != self::NO_EDIT_OR_DELETE
                           && $displayParts['del_lnk'] != self::NO_EDIT_OR_DELETE;

        $colspan = $emptyPreCondition ? ' colspan="4"'
            : '';

        $leftOrBoth = $GLOBALS['cfg']['RowActionLinks'] === self::POSITION_LEFT
                   || $GLOBALS['cfg']['RowActionLinks'] === self::POSITION_BOTH;

        //     ... before the result table
        if (
            ($displayParts['edit_lnk'] === self::NO_EDIT_OR_DELETE)
            && ($displayParts['del_lnk'] === self::NO_EDIT_OR_DELETE)
            && ($displayParts['text_btn'] == '1')
        ) {
            $displayParams['emptypre'] = $emptyPreCondition ? 4 : 0;
        } elseif ($leftOrBoth && ($displayParts['text_btn'] == '1')) {
            //     ... at the left column of the result table header if possible
            //     and required

            $displayParams['emptypre'] = $emptyPreCondition ? 4 : 0;

            $buttonHtml .= '<th class="column_action sticky d-print-none" ' . $colspan
                . '>' . $fullOrPartialTextLink . '</th>';
        } elseif (
            $leftOrBoth
            && (($displayParts['edit_lnk'] != self::NO_EDIT_OR_DELETE)
            || ($displayParts['del_lnk'] != self::NO_EDIT_OR_DELETE))
        ) {
            //     ... elseif no button, displays empty(ies) col(s) if required

            $displayParams['emptypre'] = $emptyPreCondition ? 4 : 0;

            $buttonHtml .= '<td ' . $colspan . '></td>';
        } elseif ($GLOBALS['cfg']['RowActionLinks'] === self::POSITION_NONE) {
            // ... elseif display an empty column if the actions links are
            //  disabled to match the rest of the table
            $buttonHtml .= '<th class="column_action sticky"></th>';
        }

        $this->properties['display_params'] = $displayParams;

        return [
            $colspan,
            $buttonHtml,
        ];
    }

    /**
     * Get table comments as array
     *
     * @see getTableHeaders()
     *
     * @param array $analyzedSqlResults analyzed sql results
     *
     * @return array table comments
     *
     * @access private
     */
    private function getTableCommentsArray(array $analyzedSqlResults)
    {
        if (! $GLOBALS['cfg']['ShowBrowseComments'] || empty($analyzedSqlResults['statement']->from)) {
            return [];
        }

        $ret = [];
        foreach ($analyzedSqlResults['statement']->from as $field) {
            if (empty($field->table)) {
                continue;
            }

            $ret[$field->table] = $this->relation->getComments(
                empty($field->database) ? $this->properties['db'] : $field->database,
                $field->table
            );
        }

        return $ret;
    }

    /**
     * Set global array for store highlighted header fields
     *
     * @see getTableHeaders()
     *
     * @param array $analyzedSqlResults analyzed sql results
     *
     * @access private
     */
    private function setHighlightedColumnGlobalField(array $analyzedSqlResults): void…

Large files files are truncated, but you can click here to view the full file