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