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