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