view_create.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * handles creation of VIEWs
   5  *
   6  * @todo js error when view name is empty (strFormEmpty)
   7  * @todo (also validate if js is disabled, after form submission?)
   8  * @package PhpMyAdmin
   9  */
  10 declare(strict_types=1);
  11
  12 use PhpMyAdmin\Core;
  13 use PhpMyAdmin\DatabaseInterface;
  14 use PhpMyAdmin\Message;
  15 use PhpMyAdmin\Response;
  16 use PhpMyAdmin\SqlParser\Parser;
  17 use PhpMyAdmin\SqlParser\Statements\CreateStatement;
  18 use PhpMyAdmin\SqlParser\TokensList;
  19 use PhpMyAdmin\Template;
  20 use PhpMyAdmin\Util;
  21
  22 if (! defined('ROOT_PATH')) {
  23     define('ROOT_PATH', __DIR__ . DIRECTORY_SEPARATOR);
  24 }
  25
  26 global $text_dir;
  27
  28 require_once ROOT_PATH . 'libraries/common.inc.php';
  29 require ROOT_PATH . 'libraries/db_common.inc.php';
  30
  31 /** @var Response $response */
  32 $response = $containerBuilder->get(Response::class);
  33
  34 /** @var DatabaseInterface $dbi */
  35 $dbi = $containerBuilder->get(DatabaseInterface::class);
  36
  37 $url_params['goto'] = 'tbl_structure.php';
  38 $url_params['back'] = 'view_create.php';
  39
  40 /** @var Template $template */
  41 $template = $containerBuilder->get('template');
  42
  43 $view_algorithm_options = [
  44     'UNDEFINED',
  45     'MERGE',
  46     'TEMPTABLE',
  47 ];
  48
  49 $view_with_options = [
  50     'CASCADED',
  51     'LOCAL',
  52 ];
  53
  54 $view_security_options = [
  55     'DEFINER',
  56     'INVOKER',
  57 ];
  58
  59 // View name is a compulsory field
  60 if (isset($_POST['view']['name'])
  61     && empty($_POST['view']['name'])
  62 ) {
  63     $message = Message::error(__('View name can not be empty!'));
  64     $response->addJSON(
  65         'message',
  66         $message
  67     );
  68     $response->setRequestStatus(false);
  69     exit;
  70 }
  71
  72 if (isset($_POST['createview']) || isset($_POST['alterview'])) {
  73     /**
  74      * Creates the view
  75      */
  76     $sep = "\r\n";
  77
  78     if (isset($_POST['createview'])) {
  79         $sql_query = 'CREATE';
  80         if (isset($_POST['view']['or_replace'])) {
  81             $sql_query .= ' OR REPLACE';
  82         }
  83     } else {
  84         $sql_query = 'ALTER';
  85     }
  86
  87     if (Core::isValid($_POST['view']['algorithm'], $view_algorithm_options)) {
  88         $sql_query .= $sep . ' ALGORITHM = ' . $_POST['view']['algorithm'];
  89     }
  90
  91     if (! empty($_POST['view']['definer'])) {
  92         if (strpos($_POST['view']['definer'], '@') === false) {
  93             $sql_query .= $sep . 'DEFINER='
  94                 . Util::backquote($_POST['view']['definer']);
  95         } else {
  96             $arr = explode('@', $_POST['view']['definer']);
  97             $sql_query .= $sep . 'DEFINER=' . Util::backquote($arr[0]);
  98             $sql_query .= '@' . Util::backquote($arr[1]) . ' ';
  99         }
 100     }
 101
 102     if (isset($_POST['view']['sql_security']) && in_array($_POST['view']['sql_security'], $view_security_options)) {
 103         $sql_query .= $sep . ' SQL SECURITY '
 104             . $_POST['view']['sql_security'];
 105     }
 106
 107     $sql_query .= $sep . ' VIEW '
 108         . Util::backquote($_POST['view']['name']);
 109
 110     if (! empty($_POST['view']['column_names'])) {
 111         $sql_query .= $sep . ' (' . $_POST['view']['column_names'] . ')';
 112     }
 113
 114     $sql_query .= $sep . ' AS ' . $_POST['view']['as'];
 115
 116     if (isset($_POST['view']['with'])) {
 117         if (in_array($_POST['view']['with'], $view_with_options)) {
 118             $sql_query .= $sep . ' WITH ' . $_POST['view']['with']
 119                 . '  CHECK OPTION';
 120         }
 121     }
 122
 123     if (! $dbi->tryQuery($sql_query)) {
 124         if (! isset($_POST['ajax_dialog'])) {
 125             $message = Message::rawError($dbi->getError());
 126             return;
 127         }
 128
 129         $response->addJSON(
 130             'message',
 131             Message::error(
 132                 "<i>" . htmlspecialchars($sql_query) . "</i><br><br>"
 133                 . $dbi->getError()
 134             )
 135         );
 136         $response->setRequestStatus(false);
 137         exit;
 138     }
 139
 140     // If different column names defined for VIEW
 141     $view_columns = [];
 142     if (isset($_POST['view']['column_names'])) {
 143         $view_columns = explode(',', $_POST['view']['column_names']);
 144     }
 145
 146     $column_map = $dbi->getColumnMapFromSql(
 147         $_POST['view']['as'],
 148         $view_columns
 149     );
 150
 151     $systemDb = $dbi->getSystemDatabase();
 152     $pma_transformation_data = $systemDb->getExistingTransformationData(
 153         $GLOBALS['db']
 154     );
 155
 156     if ($pma_transformation_data !== false) {
 157         // SQL for store new transformation details of VIEW
 158         $new_transformations_sql = $systemDb->getNewTransformationDataSql(
 159             $pma_transformation_data,
 160             $column_map,
 161             $_POST['view']['name'],
 162             $GLOBALS['db']
 163         );
 164
 165         // Store new transformations
 166         if ($new_transformations_sql != '') {
 167             $dbi->tryQuery($new_transformations_sql);
 168         }
 169     }
 170     unset($pma_transformation_data);
 171
 172     if (! isset($_POST['ajax_dialog'])) {
 173         $message = Message::success();
 174         include ROOT_PATH . 'tbl_structure.php';
 175     } else {
 176         $response->addJSON(
 177             'message',
 178             Util::getMessage(
 179                 Message::success(),
 180                 $sql_query
 181             )
 182         );
 183         $response->setRequestStatus(true);
 184     }
 185
 186     exit;
 187 }
 188
 189 $sql_query = ! empty($_POST['sql_query']) ? $_POST['sql_query'] : '';
 190
 191 // prefill values if not already filled from former submission
 192 $view = [
 193     'operation' => 'create',
 194     'or_replace' => '',
 195     'algorithm' => '',
 196     'definer' => '',
 197     'sql_security' => '',
 198     'name' => '',
 199     'column_names' => '',
 200     'as' => $sql_query,
 201     'with' => '',
 202 ];
 203
 204 // Used to prefill the fields when editing a view
 205 if (isset($_GET['db']) && isset($_GET['table'])) {
 206     $item = $dbi->fetchSingleRow(
 207         sprintf(
 208             "SELECT `VIEW_DEFINITION`, `CHECK_OPTION`, `DEFINER`,
 209             `SECURITY_TYPE`
 210             FROM `INFORMATION_SCHEMA`.`VIEWS`
 211             WHERE TABLE_SCHEMA='%s'
 212             AND TABLE_NAME='%s';",
 213             $dbi->escapeString($_GET['db']),
 214             $dbi->escapeString($_GET['table'])
 215         )
 216     );
 217     $createView = $dbi->getTable($_GET['db'], $_GET['table'])
 218         ->showCreate();
 219
 220     // CREATE ALGORITHM=<ALGORITHM> DE...
 221     $parts = explode(" ", substr($createView, 17));
 222     $item['ALGORITHM'] = $parts[0];
 223
 224     $view['operation'] = 'alter';
 225     $view['definer'] = $item['DEFINER'];
 226     $view['sql_security'] = $item['SECURITY_TYPE'];
 227     $view['name'] = $_GET['table'];
 228     $view['as'] = $item['VIEW_DEFINITION'];
 229     $view['with'] = $item['CHECK_OPTION'];
 230     $view['algorithm'] = $item['ALGORITHM'];
 231
 232     if (empty($view['as']) && is_string($createView)) {
 233         $parser = new Parser($createView);
 234         /**
 235          * @var CreateStatement $stmt
 236          */
 237         $stmt = $parser->statements[0];
 238         $view['as'] = isset($stmt->body) ? TokensList::build($stmt->body) : $view['as'];
 239     }
 240 }
 241
 242 if (Core::isValid($_POST['view'], 'array')) {
 243     $view = array_merge($view, $_POST['view']);
 244 }
 245
 246 $url_params['db'] = $GLOBALS['db'];
 247 $url_params['reload'] = 1;
 248
 249 echo $template->render('view_create', [
 250     'ajax_dialog' => isset($_POST['ajax_dialog']),
 251     'text_dir' => $text_dir,
 252     'url_params' => $url_params,
 253     'view' => $view,
 254     'view_algorithm_options' => $view_algorithm_options,
 255     'view_with_options' => $view_with_options,
 256     'view_security_options' => $view_security_options,
 257 ]);