SaveController.php 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411
  1. <?php
  2. declare(strict_types=1);
  3. namespace PhpMyAdmin\Controllers\Table\Structure;
  4. use PhpMyAdmin\ConfigStorage\Relation;
  5. use PhpMyAdmin\Controllers\Table\AbstractController;
  6. use PhpMyAdmin\Controllers\Table\StructureController;
  7. use PhpMyAdmin\Core;
  8. use PhpMyAdmin\DatabaseInterface;
  9. use PhpMyAdmin\Html\Generator;
  10. use PhpMyAdmin\Index;
  11. use PhpMyAdmin\Message;
  12. use PhpMyAdmin\ResponseRenderer;
  13. use PhpMyAdmin\Table;
  14. use PhpMyAdmin\Template;
  15. use PhpMyAdmin\Transformations;
  16. use PhpMyAdmin\Url;
  17. use PhpMyAdmin\Util;
  18. use function __;
  19. use function count;
  20. use function implode;
  21. use function in_array;
  22. use function is_array;
  23. use function mb_strpos;
  24. use function sprintf;
  25. use function strlen;
  26. final class SaveController extends AbstractController
  27. {
  28. /** @var Table The table object */
  29. private $tableObj;
  30. /** @var Relation */
  31. private $relation;
  32. /** @var Transformations */
  33. private $transformations;
  34. /** @var DatabaseInterface */
  35. private $dbi;
  36. /** @var StructureController */
  37. private $structureController;
  38. public function __construct(
  39. ResponseRenderer $response,
  40. Template $template,
  41. string $db,
  42. string $table,
  43. Relation $relation,
  44. Transformations $transformations,
  45. DatabaseInterface $dbi,
  46. StructureController $structureController
  47. ) {
  48. parent::__construct($response, $template, $db, $table);
  49. $this->relation = $relation;
  50. $this->transformations = $transformations;
  51. $this->dbi = $dbi;
  52. $this->structureController = $structureController;
  53. $this->tableObj = $this->dbi->getTable($this->db, $this->table);
  54. }
  55. public function __invoke(): void
  56. {
  57. $regenerate = $this->updateColumns();
  58. if (! $regenerate) {
  59. // continue to show the table's structure
  60. unset($_POST['selected']);
  61. }
  62. ($this->structureController)();
  63. }
  64. /**
  65. * Update the table's structure based on $_REQUEST
  66. *
  67. * @return bool true if error occurred
  68. */
  69. private function updateColumns(): bool
  70. {
  71. $err_url = Url::getFromRoute('/table/structure', [
  72. 'db' => $this->db,
  73. 'table' => $this->table,
  74. ]);
  75. $regenerate = false;
  76. $field_cnt = count($_POST['field_name'] ?? []);
  77. $changes = [];
  78. $adjust_privileges = [];
  79. $columns_with_index = $this->dbi
  80. ->getTable($this->db, $this->table)
  81. ->getColumnsWithIndex(Index::PRIMARY | Index::UNIQUE);
  82. for ($i = 0; $i < $field_cnt; $i++) {
  83. if (! $this->columnNeedsAlterTable($i)) {
  84. continue;
  85. }
  86. $changes[] = 'CHANGE ' . Table::generateAlter(
  87. Util::getValueByKey($_POST, 'field_orig.' . $i, ''),
  88. $_POST['field_name'][$i],
  89. $_POST['field_type'][$i],
  90. $_POST['field_length'][$i],
  91. $_POST['field_attribute'][$i],
  92. Util::getValueByKey($_POST, 'field_collation.' . $i, ''),
  93. Util::getValueByKey($_POST, 'field_null.' . $i, 'NO'),
  94. $_POST['field_default_type'][$i],
  95. $_POST['field_default_value'][$i],
  96. Util::getValueByKey($_POST, 'field_extra.' . $i, false),
  97. Util::getValueByKey($_POST, 'field_comments.' . $i, ''),
  98. Util::getValueByKey($_POST, 'field_virtuality.' . $i, ''),
  99. Util::getValueByKey($_POST, 'field_expression.' . $i, ''),
  100. Util::getValueByKey($_POST, 'field_move_to.' . $i, ''),
  101. $columns_with_index
  102. );
  103. // find the remembered sort expression
  104. $sorted_col = $this->tableObj->getUiProp(Table::PROP_SORTED_COLUMN);
  105. // if the old column name is part of the remembered sort expression
  106. if (mb_strpos((string) $sorted_col, Util::backquote($_POST['field_orig'][$i])) !== false) {
  107. // delete the whole remembered sort expression
  108. $this->tableObj->removeUiProp(Table::PROP_SORTED_COLUMN);
  109. }
  110. if (
  111. ! isset($_POST['field_adjust_privileges'][$i])
  112. || empty($_POST['field_adjust_privileges'][$i])
  113. || $_POST['field_orig'][$i] == $_POST['field_name'][$i]
  114. ) {
  115. continue;
  116. }
  117. $adjust_privileges[$_POST['field_orig'][$i]] = $_POST['field_name'][$i];
  118. }
  119. if (count($changes) > 0 || isset($_POST['preview_sql'])) {
  120. // Builds the primary keys statements and updates the table
  121. $key_query = '';
  122. /**
  123. * this is a little bit more complex
  124. *
  125. * @todo if someone selects A_I when altering a column we need to check:
  126. * - no other column with A_I
  127. * - the column has an index, if not create one
  128. */
  129. // To allow replication, we first select the db to use
  130. // and then run queries on this db.
  131. if (! $this->dbi->selectDb($this->db)) {
  132. Generator::mysqlDie(
  133. $this->dbi->getError(),
  134. 'USE ' . Util::backquote($this->db) . ';',
  135. false,
  136. $err_url
  137. );
  138. }
  139. $sql_query = 'ALTER TABLE ' . Util::backquote($this->table) . ' ';
  140. $sql_query .= implode(', ', $changes) . $key_query;
  141. if (isset($_POST['online_transaction'])) {
  142. $sql_query .= ', ALGORITHM=INPLACE, LOCK=NONE';
  143. }
  144. $sql_query .= ';';
  145. // If there is a request for SQL previewing.
  146. if (isset($_POST['preview_sql'])) {
  147. Core::previewSQL(count($changes) > 0 ? $sql_query : '');
  148. exit;
  149. }
  150. $columns_with_index = $this->dbi
  151. ->getTable($this->db, $this->table)
  152. ->getColumnsWithIndex(Index::PRIMARY | Index::UNIQUE | Index::INDEX | Index::SPATIAL | Index::FULLTEXT);
  153. $changedToBlob = [];
  154. // While changing the Column Collation
  155. // First change to BLOB, MEDIUMBLOB, or LONGBLOB (depending on the original field type)
  156. for ($i = 0; $i < $field_cnt; $i++) {
  157. if (
  158. isset($_POST['field_collation'][$i], $_POST['field_collation_orig'][$i])
  159. && $_POST['field_collation'][$i] !== $_POST['field_collation_orig'][$i]
  160. && ! in_array($_POST['field_orig'][$i], $columns_with_index)
  161. ) {
  162. if ($_POST['field_type_orig'][$i] === 'MEDIUMTEXT') {
  163. $blob_type = 'MEDIUMBLOB';
  164. } elseif ($_POST['field_type_orig'][$i] === 'LONGTEXT') {
  165. $blob_type = 'LONGBLOB';
  166. } else {
  167. $blob_type = 'BLOB';
  168. }
  169. $secondary_query = 'ALTER TABLE ' . Util::backquote($this->table)
  170. . ' CHANGE ' . Util::backquote($_POST['field_orig'][$i])
  171. . ' ' . Util::backquote($_POST['field_orig'][$i])
  172. . ' ' . $blob_type;
  173. if (isset($_POST['field_virtuality'][$i], $_POST['field_expression'][$i])) {
  174. if ($_POST['field_virtuality'][$i]) {
  175. $secondary_query .= ' AS (' . $_POST['field_expression'][$i] . ') '
  176. . $_POST['field_virtuality'][$i];
  177. }
  178. }
  179. $secondary_query .= ';';
  180. $this->dbi->query($secondary_query);
  181. $changedToBlob[$i] = true;
  182. } else {
  183. $changedToBlob[$i] = false;
  184. }
  185. }
  186. // Then make the requested changes
  187. $result = $this->dbi->tryQuery($sql_query);
  188. if ($result !== false) {
  189. $changed_privileges = $this->adjustColumnPrivileges($adjust_privileges);
  190. if ($changed_privileges) {
  191. $message = Message::success(
  192. __(
  193. 'Table %1$s has been altered successfully. Privileges have been adjusted.'
  194. )
  195. );
  196. } else {
  197. $message = Message::success(
  198. __('Table %1$s has been altered successfully.')
  199. );
  200. }
  201. $message->addParam($this->table);
  202. $this->response->addHTML(
  203. Generator::getMessage($message, $sql_query, 'success')
  204. );
  205. } else {
  206. // An error happened while inserting/updating a table definition
  207. // Save the Original Error
  208. $orig_error = $this->dbi->getError();
  209. $changes_revert = [];
  210. // Change back to Original Collation and data type
  211. for ($i = 0; $i < $field_cnt; $i++) {
  212. if (! $changedToBlob[$i]) {
  213. continue;
  214. }
  215. $changes_revert[] = 'CHANGE ' . Table::generateAlter(
  216. Util::getValueByKey($_POST, 'field_orig.' . $i, ''),
  217. $_POST['field_name'][$i],
  218. $_POST['field_type_orig'][$i],
  219. $_POST['field_length_orig'][$i],
  220. $_POST['field_attribute_orig'][$i],
  221. Util::getValueByKey($_POST, 'field_collation_orig.' . $i, ''),
  222. Util::getValueByKey($_POST, 'field_null_orig.' . $i, 'NO'),
  223. $_POST['field_default_type_orig'][$i],
  224. $_POST['field_default_value_orig'][$i],
  225. Util::getValueByKey($_POST, 'field_extra_orig.' . $i, false),
  226. Util::getValueByKey($_POST, 'field_comments_orig.' . $i, ''),
  227. Util::getValueByKey($_POST, 'field_virtuality_orig.' . $i, ''),
  228. Util::getValueByKey($_POST, 'field_expression_orig.' . $i, ''),
  229. Util::getValueByKey($_POST, 'field_move_to_orig.' . $i, '')
  230. );
  231. }
  232. $revert_query = 'ALTER TABLE ' . Util::backquote($this->table)
  233. . ' ';
  234. $revert_query .= implode(', ', $changes_revert) . '';
  235. $revert_query .= ';';
  236. // Column reverted back to original
  237. $this->dbi->query($revert_query);
  238. $this->response->setRequestStatus(false);
  239. $message = Message::rawError(
  240. __('Query error') . ':<br>' . $orig_error
  241. );
  242. $this->response->addHTML(
  243. Generator::getMessage($message, $sql_query, 'error')
  244. );
  245. $regenerate = true;
  246. }
  247. }
  248. // update field names in relation
  249. if (isset($_POST['field_orig']) && is_array($_POST['field_orig'])) {
  250. foreach ($_POST['field_orig'] as $fieldindex => $fieldcontent) {
  251. if ($_POST['field_name'][$fieldindex] == $fieldcontent) {
  252. continue;
  253. }
  254. $this->relation->renameField($this->db, $this->table, $fieldcontent, $_POST['field_name'][$fieldindex]);
  255. }
  256. }
  257. // update mime types
  258. if (isset($_POST['field_mimetype']) && is_array($_POST['field_mimetype']) && $GLOBALS['cfg']['BrowseMIME']) {
  259. foreach ($_POST['field_mimetype'] as $fieldindex => $mimetype) {
  260. if (! isset($_POST['field_name'][$fieldindex]) || strlen($_POST['field_name'][$fieldindex]) <= 0) {
  261. continue;
  262. }
  263. $this->transformations->setMime(
  264. $this->db,
  265. $this->table,
  266. $_POST['field_name'][$fieldindex],
  267. $mimetype,
  268. $_POST['field_transformation'][$fieldindex],
  269. $_POST['field_transformation_options'][$fieldindex],
  270. $_POST['field_input_transformation'][$fieldindex],
  271. $_POST['field_input_transformation_options'][$fieldindex]
  272. );
  273. }
  274. }
  275. return $regenerate;
  276. }
  277. /**
  278. * Verifies if some elements of a column have changed
  279. *
  280. * @param int $i column index in the request
  281. */
  282. private function columnNeedsAlterTable($i): bool
  283. {
  284. // these two fields are checkboxes so might not be part of the
  285. // request; therefore we define them to avoid notices below
  286. if (! isset($_POST['field_null'][$i])) {
  287. $_POST['field_null'][$i] = 'NO';
  288. }
  289. if (! isset($_POST['field_extra'][$i])) {
  290. $_POST['field_extra'][$i] = '';
  291. }
  292. // field_name does not follow the convention (corresponds to field_orig)
  293. if ($_POST['field_name'][$i] != $_POST['field_orig'][$i]) {
  294. return true;
  295. }
  296. $fields = [
  297. 'field_attribute',
  298. 'field_collation',
  299. 'field_comments',
  300. 'field_default_value',
  301. 'field_default_type',
  302. 'field_extra',
  303. 'field_length',
  304. 'field_null',
  305. 'field_type',
  306. 'field_virtuality',
  307. ];
  308. foreach ($fields as $field) {
  309. if ($_POST[$field][$i] != $_POST[$field . '_orig'][$i]) {
  310. return true;
  311. }
  312. }
  313. return ! empty($_POST['field_move_to'][$i]);
  314. }
  315. /**
  316. * Adjusts the Privileges for all the columns whose names have changed
  317. *
  318. * @param array $adjust_privileges assoc array of old col names mapped to new
  319. * cols
  320. */
  321. private function adjustColumnPrivileges(array $adjust_privileges): bool
  322. {
  323. $changed = false;
  324. if (
  325. Util::getValueByKey($GLOBALS, 'col_priv', false)
  326. && Util::getValueByKey($GLOBALS, 'is_reload_priv', false)
  327. ) {
  328. $this->dbi->selectDb('mysql');
  329. // For Column specific privileges
  330. foreach ($adjust_privileges as $oldCol => $newCol) {
  331. $this->dbi->query(
  332. sprintf(
  333. 'UPDATE %s SET Column_name = "%s"
  334. WHERE Db = "%s"
  335. AND Table_name = "%s"
  336. AND Column_name = "%s";',
  337. Util::backquote('columns_priv'),
  338. $newCol,
  339. $this->db,
  340. $this->table,
  341. $oldCol
  342. )
  343. );
  344. // i.e. if atleast one column privileges adjusted
  345. $changed = true;
  346. }
  347. if ($changed) {
  348. // Finally FLUSH the new privileges
  349. $this->dbi->query('FLUSH PRIVILEGES;');
  350. }
  351. }
  352. return $changed;
  353. }
  354. }