libraries/classes/Controllers/Table/ChartController.php

   1 <?php
   2 declare(strict_types=1);
   3
   4 namespace PhpMyAdmin\Controllers\Table;
   5
   6 use PhpMyAdmin\Common;
   7 use PhpMyAdmin\Message;
   8 use PhpMyAdmin\SqlParser\Components\Limit;
   9 use PhpMyAdmin\SqlParser\Parser;
  10 use PhpMyAdmin\SqlParser\Statements\SelectStatement;
  11 use PhpMyAdmin\Url;
  12 use PhpMyAdmin\Util;
  13 use function array_keys;
  14 use function htmlspecialchars;
  15 use function in_array;
  16 use function json_encode;
  17 use function min;
  18 use function strlen;
  19
  20 /**
  21  * Handles creation of the chart.
  22  */
  23 class ChartController extends AbstractController
  24 {
  25     /**
  26      * Execute the query and return the result
  27      */
  28     public function index(): void
  29     {
  30         global $db, $table, $cfg, $sql_query, $url_query;
  31
  32         if (isset($_REQUEST['pos'], $_REQUEST['session_max_rows']) && $this->response->isAjax()
  33         ) {
  34             $this->ajax();
  35             return;
  36         }
  37
  38         // Throw error if no sql query is set
  39         if (! isset($sql_query) || $sql_query == '') {
  40             $this->response->setRequestStatus(false);
  41             $this->response->addHTML(
  42                 Message::error(__('No SQL query was set to fetch data.'))
  43             );
  44             return;
  45         }
  46
  47         $this->response->getHeader()->getScripts()->addFiles(
  48             [
  49                 'chart.js',
  50                 'table/chart.js',
  51                 'vendor/jqplot/jquery.jqplot.js',
  52                 'vendor/jqplot/plugins/jqplot.barRenderer.js',
  53                 'vendor/jqplot/plugins/jqplot.canvasAxisLabelRenderer.js',
  54                 'vendor/jqplot/plugins/jqplot.canvasTextRenderer.js',
  55                 'vendor/jqplot/plugins/jqplot.categoryAxisRenderer.js',
  56                 'vendor/jqplot/plugins/jqplot.dateAxisRenderer.js',
  57                 'vendor/jqplot/plugins/jqplot.pointLabels.js',
  58                 'vendor/jqplot/plugins/jqplot.pieRenderer.js',
  59                 'vendor/jqplot/plugins/jqplot.enhancedPieLegendRenderer.js',
  60                 'vendor/jqplot/plugins/jqplot.highlighter.js',
  61             ]
  62         );
  63
  64         $url_params = [];
  65
  66         /**
  67          * Runs common work
  68          */
  69         if (strlen($table) > 0) {
  70             $url_params['goto'] = Util::getScriptNameForOption(
  71                 $cfg['DefaultTabTable'],
  72                 'table'
  73             );
  74             $url_params['back'] = Url::getFromRoute('/table/sql');
  75             Common::table();
  76             $this->dbi->selectDb($db);
  77         } elseif (strlen($db) > 0) {
  78             $url_params['goto'] = Util::getScriptNameForOption(
  79                 $cfg['DefaultTabDatabase'],
  80                 'database'
  81             );
  82             $url_params['back'] = Url::getFromRoute('/sql');
  83             Common::database();
  84         } else {
  85             $url_params['goto'] = Util::getScriptNameForOption(
  86                 $cfg['DefaultTabServer'],
  87                 'server'
  88             );
  89             $url_params['back'] = Url::getFromRoute('/sql');
  90             Common::server();
  91         }
  92
  93         $data = [];
  94
  95         $result = $this->dbi->tryQuery($sql_query);
  96         $fields_meta = $this->dbi->getFieldsMeta($result);
  97         while ($row = $this->dbi->fetchAssoc($result)) {
  98             $data[] = $row;
  99         }
 100
 101         $keys = array_keys($data[0]);
 102
 103         $numeric_types = [
 104             'int',
 105             'real',
 106         ];
 107         $numeric_column_count = 0;
 108         foreach ($keys as $idx => $key) {
 109             if (in_array($fields_meta[$idx]->type, $numeric_types)) {
 110                 $numeric_column_count++;
 111             }
 112         }
 113
 114         if ($numeric_column_count == 0) {
 115             $this->response->setRequestStatus(false);
 116             $this->response->addJSON(
 117                 'message',
 118                 __('No numeric columns present in the table to plot.')
 119             );
 120             return;
 121         }
 122
 123         $url_params['db'] = $db;
 124         $url_params['reload'] = 1;
 125
 126         /**
 127          * Displays the page
 128          */
 129         $this->render('table/chart/tbl_chart', [
 130             'url_query' => $url_query,
 131             'url_params' => $url_params,
 132             'keys' => $keys,
 133             'fields_meta' => $fields_meta,
 134             'numeric_types' => $numeric_types,
 135             'numeric_column_count' => $numeric_column_count,
 136             'sql_query' => $sql_query,
 137         ]);
 138     }
 139
 140     /**
 141      * Handle ajax request
 142      */
 143     public function ajax(): void
 144     {
 145         global $db, $table, $sql_query;
 146
 147         if (strlen($table) > 0 && strlen($db) > 0) {
 148             Common::table();
 149         }
 150
 151         $parser = new Parser($sql_query);
 152         /**
 153          * @var SelectStatement $statement
 154          */
 155         $statement = $parser->statements[0];
 156         if (empty($statement->limit)) {
 157             $statement->limit = new Limit(
 158                 $_REQUEST['session_max_rows'],
 159                 $_REQUEST['pos']
 160             );
 161         } else {
 162             $start = $statement->limit->offset + $_REQUEST['pos'];
 163             $rows = min(
 164                 $_REQUEST['session_max_rows'],
 165                 $statement->limit->rowCount - $_REQUEST['pos']
 166             );
 167             $statement->limit = new Limit($rows, $start);
 168         }
 169         $sql_with_limit = $statement->build();
 170
 171         $data = [];
 172         $result = $this->dbi->tryQuery($sql_with_limit);
 173         while ($row = $this->dbi->fetchAssoc($result)) {
 174             $data[] = $row;
 175         }
 176
 177         if (empty($data)) {
 178             $this->response->setRequestStatus(false);
 179             $this->response->addJSON('message', __('No data to display'));
 180             return;
 181         }
 182         $sanitized_data = [];
 183
 184         foreach ($data as $data_row_number => $data_row) {
 185             $tmp_row = [];
 186             foreach ($data_row as $data_column => $data_value) {
 187                 $escaped_value = $data_value === null ? null : htmlspecialchars($data_value);
 188                 $tmp_row[htmlspecialchars($data_column)] = $escaped_value;
 189             }
 190             $sanitized_data[] = $tmp_row;
 191         }
 192         $this->response->setRequestStatus(true);
 193         $this->response->addJSON('message', null);
 194         $this->response->addJSON('chartData', json_encode($sanitized_data));
 195     }
 196 }