PageRenderTime 37ms CodeModel.GetById 19ms RepoModel.GetById 0ms app.codeStats 1ms

/libraries/classes/InsertEdit.php

http://github.com/phpmyadmin/phpmyadmin
PHP | 2582 lines | 1689 code | 236 blank | 657 comment | 255 complexity | b062540745e470d7dc86ff763c61d99d MD5 | raw file
Possible License(s): GPL-2.0, MIT, LGPL-3.0
  1. <?php
  2. /**
  3. * set of functions with the insert/edit features in pma
  4. */
  5. declare(strict_types=1);
  6. namespace PhpMyAdmin;
  7. use PhpMyAdmin\Html\Generator;
  8. use PhpMyAdmin\Plugins\TransformationsPlugin;
  9. use PhpMyAdmin\Utils\Gis;
  10. use function __;
  11. use function array_fill;
  12. use function array_merge;
  13. use function array_values;
  14. use function bin2hex;
  15. use function class_exists;
  16. use function count;
  17. use function current;
  18. use function date;
  19. use function explode;
  20. use function htmlspecialchars;
  21. use function implode;
  22. use function in_array;
  23. use function is_array;
  24. use function is_file;
  25. use function is_string;
  26. use function max;
  27. use function mb_stripos;
  28. use function mb_strlen;
  29. use function mb_strstr;
  30. use function mb_substr;
  31. use function md5;
  32. use function method_exists;
  33. use function min;
  34. use function password_hash;
  35. use function preg_match;
  36. use function preg_replace;
  37. use function str_contains;
  38. use function str_replace;
  39. use function stripcslashes;
  40. use function stripslashes;
  41. use function strlen;
  42. use function substr;
  43. use function time;
  44. use function trim;
  45. use const ENT_COMPAT;
  46. use const PASSWORD_DEFAULT;
  47. /**
  48. * PhpMyAdmin\InsertEdit class
  49. */
  50. class InsertEdit
  51. {
  52. /**
  53. * DatabaseInterface instance
  54. *
  55. * @var DatabaseInterface
  56. */
  57. private $dbi;
  58. /** @var Relation */
  59. private $relation;
  60. /** @var Transformations */
  61. private $transformations;
  62. /** @var FileListing */
  63. private $fileListing;
  64. /** @var Template */
  65. public $template;
  66. /**
  67. * @param DatabaseInterface $dbi DatabaseInterface instance
  68. */
  69. public function __construct(DatabaseInterface $dbi)
  70. {
  71. $this->dbi = $dbi;
  72. $this->relation = new Relation($this->dbi);
  73. $this->transformations = new Transformations();
  74. $this->fileListing = new FileListing();
  75. $this->template = new Template();
  76. }
  77. /**
  78. * Retrieve form parameters for insert/edit form
  79. *
  80. * @param string $db name of the database
  81. * @param string $table name of the table
  82. * @param array|null $whereClauses where clauses
  83. * @param array $whereClauseArray array of where clauses
  84. * @param string $errorUrl error url
  85. *
  86. * @return array array of insert/edit form parameters
  87. */
  88. public function getFormParametersForInsertForm(
  89. $db,
  90. $table,
  91. ?array $whereClauses,
  92. array $whereClauseArray,
  93. $errorUrl
  94. ) {
  95. $formParams = [
  96. 'db' => $db,
  97. 'table' => $table,
  98. 'goto' => $GLOBALS['goto'],
  99. 'err_url' => $errorUrl,
  100. 'sql_query' => $_POST['sql_query'] ?? '',
  101. ];
  102. if (isset($whereClauses)) {
  103. foreach ($whereClauseArray as $keyId => $whereClause) {
  104. $formParams['where_clause[' . $keyId . ']'] = trim($whereClause);
  105. }
  106. }
  107. if (isset($_POST['clause_is_unique'])) {
  108. $formParams['clause_is_unique'] = $_POST['clause_is_unique'];
  109. }
  110. return $formParams;
  111. }
  112. /**
  113. * Creates array of where clauses
  114. *
  115. * @param array|string|null $whereClause where clause
  116. *
  117. * @return array whereClauseArray array of where clauses
  118. */
  119. private function getWhereClauseArray($whereClause)
  120. {
  121. if (! isset($whereClause)) {
  122. return [];
  123. }
  124. if (is_array($whereClause)) {
  125. return $whereClause;
  126. }
  127. return [0 => $whereClause];
  128. }
  129. /**
  130. * Analysing where clauses array
  131. *
  132. * @param array $whereClauseArray array of where clauses
  133. * @param string $table name of the table
  134. * @param string $db name of the database
  135. *
  136. * @return array $where_clauses, $result, $rows, $found_unique_key
  137. */
  138. private function analyzeWhereClauses(
  139. array $whereClauseArray,
  140. $table,
  141. $db
  142. ) {
  143. $rows = [];
  144. $result = [];
  145. $whereClauses = [];
  146. $foundUniqueKey = false;
  147. foreach ($whereClauseArray as $keyId => $whereClause) {
  148. $localQuery = 'SELECT * FROM '
  149. . Util::backquote($db) . '.'
  150. . Util::backquote($table)
  151. . ' WHERE ' . $whereClause . ';';
  152. $result[$keyId] = $this->dbi->query(
  153. $localQuery,
  154. DatabaseInterface::CONNECT_USER,
  155. DatabaseInterface::QUERY_STORE
  156. );
  157. $rows[$keyId] = $this->dbi->fetchAssoc($result[$keyId]);
  158. $whereClauses[$keyId] = str_replace('\\', '\\\\', $whereClause);
  159. $hasUniqueCondition = $this->showEmptyResultMessageOrSetUniqueCondition(
  160. $rows,
  161. $keyId,
  162. $whereClauseArray,
  163. $localQuery,
  164. $result
  165. );
  166. if (! $hasUniqueCondition) {
  167. continue;
  168. }
  169. $foundUniqueKey = true;
  170. }
  171. return [
  172. $whereClauses,
  173. $result,
  174. $rows,
  175. $foundUniqueKey,
  176. ];
  177. }
  178. /**
  179. * Show message for empty result or set the unique_condition
  180. *
  181. * @param array $rows MySQL returned rows
  182. * @param string $keyId ID in current key
  183. * @param array $whereClauseArray array of where clauses
  184. * @param string $localQuery query performed
  185. * @param array $result MySQL result handle
  186. */
  187. private function showEmptyResultMessageOrSetUniqueCondition(
  188. array $rows,
  189. $keyId,
  190. array $whereClauseArray,
  191. $localQuery,
  192. array $result
  193. ): bool {
  194. $hasUniqueCondition = false;
  195. // No row returned
  196. if (! $rows[$keyId]) {
  197. unset($rows[$keyId], $whereClauseArray[$keyId]);
  198. ResponseRenderer::getInstance()->addHTML(
  199. Generator::getMessage(
  200. __('MySQL returned an empty result set (i.e. zero rows).'),
  201. $localQuery
  202. )
  203. );
  204. /**
  205. * @todo not sure what should be done at this point, but we must not
  206. * exit if we want the message to be displayed
  207. */
  208. } else {// end if (no row returned)
  209. $meta = $this->dbi->getFieldsMeta($result[$keyId]) ?? [];
  210. [$uniqueCondition, $tmpClauseIsUnique] = Util::getUniqueCondition(
  211. $result[$keyId],
  212. count($meta),
  213. $meta,
  214. $rows[$keyId],
  215. true
  216. );
  217. if (! empty($uniqueCondition)) {
  218. $hasUniqueCondition = true;
  219. }
  220. unset($uniqueCondition, $tmpClauseIsUnique);
  221. }
  222. return $hasUniqueCondition;
  223. }
  224. /**
  225. * No primary key given, just load first row
  226. *
  227. * @param string $table name of the table
  228. * @param string $db name of the database
  229. *
  230. * @return array containing $result and $rows arrays
  231. */
  232. private function loadFirstRow($table, $db)
  233. {
  234. $result = $this->dbi->query(
  235. 'SELECT * FROM ' . Util::backquote($db)
  236. . '.' . Util::backquote($table) . ' LIMIT 1;',
  237. DatabaseInterface::CONNECT_USER,
  238. DatabaseInterface::QUERY_STORE
  239. );
  240. // Can be a string on some old configuration storage settings
  241. $rows = array_fill(0, (int) $GLOBALS['cfg']['InsertRows'], false);
  242. return [
  243. $result,
  244. $rows,
  245. ];
  246. }
  247. /**
  248. * Add some url parameters
  249. *
  250. * @param array $urlParams containing $db and $table as url parameters
  251. * @param array $whereClauseArray where clauses array
  252. *
  253. * @return array Add some url parameters to $url_params array and return it
  254. */
  255. public function urlParamsInEditMode(
  256. array $urlParams,
  257. array $whereClauseArray
  258. ): array {
  259. foreach ($whereClauseArray as $whereClause) {
  260. $urlParams['where_clause'] = trim($whereClause);
  261. }
  262. if (! empty($_POST['sql_query'])) {
  263. $urlParams['sql_query'] = $_POST['sql_query'];
  264. }
  265. return $urlParams;
  266. }
  267. /**
  268. * Show type information or function selectors in Insert/Edit
  269. *
  270. * @param string $which function|type
  271. * @param array $urlParams containing url parameters
  272. * @param bool $isShow whether to show the element in $which
  273. *
  274. * @return string an HTML snippet
  275. */
  276. public function showTypeOrFunction($which, array $urlParams, $isShow)
  277. {
  278. $params = [];
  279. switch ($which) {
  280. case 'function':
  281. $params['ShowFunctionFields'] = ($isShow ? 0 : 1);
  282. $params['ShowFieldTypesInDataEditView'] = $GLOBALS['cfg']['ShowFieldTypesInDataEditView'];
  283. break;
  284. case 'type':
  285. $params['ShowFieldTypesInDataEditView'] = ($isShow ? 0 : 1);
  286. $params['ShowFunctionFields'] = $GLOBALS['cfg']['ShowFunctionFields'];
  287. break;
  288. }
  289. $params['goto'] = Url::getFromRoute('/sql');
  290. $thisUrlParams = array_merge($urlParams, $params);
  291. if (! $isShow) {
  292. return ' : <a href="' . Url::getFromRoute('/table/change') . '" data-post="'
  293. . Url::getCommon($thisUrlParams, '') . '">'
  294. . $this->showTypeOrFunctionLabel($which)
  295. . '</a>';
  296. }
  297. return '<th><a href="' . Url::getFromRoute('/table/change') . '" data-post="'
  298. . Url::getCommon($thisUrlParams, '')
  299. . '" title="' . __('Hide') . '">'
  300. . $this->showTypeOrFunctionLabel($which)
  301. . '</a></th>';
  302. }
  303. /**
  304. * Show type information or function selectors labels in Insert/Edit
  305. *
  306. * @param string $which function|type
  307. *
  308. * @return string|null an HTML snippet
  309. */
  310. private function showTypeOrFunctionLabel($which)
  311. {
  312. switch ($which) {
  313. case 'function':
  314. return __('Function');
  315. case 'type':
  316. return __('Type');
  317. }
  318. return null;
  319. }
  320. /**
  321. * Analyze the table column array
  322. *
  323. * @param array $column description of column in given table
  324. * @param array $commentsMap comments for every column that has a comment
  325. * @param bool $timestampSeen whether a timestamp has been seen
  326. *
  327. * @return array description of column in given table
  328. */
  329. private function analyzeTableColumnsArray(
  330. array $column,
  331. array $commentsMap,
  332. $timestampSeen
  333. ) {
  334. $column['Field_html'] = htmlspecialchars($column['Field']);
  335. $column['Field_md5'] = md5($column['Field']);
  336. // True_Type contains only the type (stops at first bracket)
  337. $column['True_Type'] = preg_replace('@\(.*@s', '', $column['Type']);
  338. $column['len'] = preg_match('@float|double@', $column['Type']) ? 100 : -1;
  339. $column['Field_title'] = $this->getColumnTitle($column, $commentsMap);
  340. $column['is_binary'] = $this->isColumn(
  341. $column,
  342. [
  343. 'binary',
  344. 'varbinary',
  345. ]
  346. );
  347. $column['is_blob'] = $this->isColumn(
  348. $column,
  349. [
  350. 'blob',
  351. 'tinyblob',
  352. 'mediumblob',
  353. 'longblob',
  354. ]
  355. );
  356. $column['is_char'] = $this->isColumn(
  357. $column,
  358. [
  359. 'char',
  360. 'varchar',
  361. ]
  362. );
  363. [
  364. $column['pma_type'],
  365. $column['wrap'],
  366. $column['first_timestamp'],
  367. ] = $this->getEnumSetAndTimestampColumns($column, $timestampSeen);
  368. return $column;
  369. }
  370. /**
  371. * Retrieve the column title
  372. *
  373. * @param array $column description of column in given table
  374. * @param array $commentsMap comments for every column that has a comment
  375. *
  376. * @return string column title
  377. */
  378. private function getColumnTitle(array $column, array $commentsMap)
  379. {
  380. if (isset($commentsMap[$column['Field']])) {
  381. return '<span style="border-bottom: 1px dashed black;" title="'
  382. . htmlspecialchars($commentsMap[$column['Field']]) . '">'
  383. . $column['Field_html'] . '</span>';
  384. }
  385. return $column['Field_html'];
  386. }
  387. /**
  388. * check whether the column is of a certain type
  389. * the goal is to ensure that types such as "enum('one','two','binary',..)"
  390. * or "enum('one','two','varbinary',..)" are not categorized as binary
  391. *
  392. * @param array $column description of column in given table
  393. * @param array $types the types to verify
  394. */
  395. public function isColumn(array $column, array $types): bool
  396. {
  397. foreach ($types as $oneType) {
  398. if (mb_stripos($column['Type'], $oneType) === 0) {
  399. return true;
  400. }
  401. }
  402. return false;
  403. }
  404. /**
  405. * Retrieve set, enum, timestamp table columns
  406. *
  407. * @param array $column description of column in given table
  408. * @param bool $timestampSeen whether a timestamp has been seen
  409. *
  410. * @return array $column['pma_type'], $column['wrap'], $column['first_timestamp']
  411. */
  412. private function getEnumSetAndTimestampColumns(array $column, $timestampSeen)
  413. {
  414. $column['first_timestamp'] = false;
  415. switch ($column['True_Type']) {
  416. case 'set':
  417. $column['pma_type'] = 'set';
  418. $column['wrap'] = '';
  419. break;
  420. case 'enum':
  421. $column['pma_type'] = 'enum';
  422. $column['wrap'] = '';
  423. break;
  424. case 'timestamp':
  425. if (! $timestampSeen) { // can only occur once per table
  426. $column['first_timestamp'] = true;
  427. }
  428. $column['pma_type'] = $column['Type'];
  429. $column['wrap'] = ' text-nowrap';
  430. break;
  431. default:
  432. $column['pma_type'] = $column['Type'];
  433. $column['wrap'] = ' text-nowrap';
  434. break;
  435. }
  436. return [
  437. $column['pma_type'],
  438. $column['wrap'],
  439. $column['first_timestamp'],
  440. ];
  441. }
  442. /**
  443. * Retrieve the nullify code for the null column
  444. *
  445. * @param array $column description of column in given table
  446. * @param array $foreigners keys into foreign fields
  447. * @param array $foreignData data about the foreign keys
  448. */
  449. private function getNullifyCodeForNullColumn(
  450. array $column,
  451. array $foreigners,
  452. array $foreignData
  453. ): string {
  454. $foreigner = $this->relation->searchColumnInForeigners($foreigners, $column['Field']);
  455. if (mb_strstr($column['True_Type'], 'enum')) {
  456. if (mb_strlen((string) $column['Type']) > 20) {
  457. $nullifyCode = '1';
  458. } else {
  459. $nullifyCode = '2';
  460. }
  461. } elseif (mb_strstr($column['True_Type'], 'set')) {
  462. $nullifyCode = '3';
  463. } elseif (! empty($foreigners) && ! empty($foreigner) && $foreignData['foreign_link'] == false) {
  464. // foreign key in a drop-down
  465. $nullifyCode = '4';
  466. } elseif (! empty($foreigners) && ! empty($foreigner) && $foreignData['foreign_link'] == true) {
  467. // foreign key with a browsing icon
  468. $nullifyCode = '6';
  469. } else {
  470. $nullifyCode = '5';
  471. }
  472. return $nullifyCode;
  473. }
  474. /**
  475. * Get HTML textarea for insert form
  476. *
  477. * @param array $column column information
  478. * @param string $backupField hidden input field
  479. * @param string $columnNameAppendix the name attribute
  480. * @param string $onChangeClause onchange clause for fields
  481. * @param int $tabindex tab index
  482. * @param int $tabindexForValue offset for the values tabindex
  483. * @param int $idindex id index
  484. * @param string $textDir text direction
  485. * @param string $specialCharsEncoded replaced char if the string starts
  486. * with a \r\n pair (0x0d0a) add an extra \n
  487. * @param string $dataType the html5 data-* attribute type
  488. * @param bool $readOnly is column read only or not
  489. *
  490. * @return string an html snippet
  491. */
  492. private function getTextarea(
  493. array $column,
  494. $backupField,
  495. $columnNameAppendix,
  496. $onChangeClause,
  497. $tabindex,
  498. $tabindexForValue,
  499. $idindex,
  500. $textDir,
  501. $specialCharsEncoded,
  502. $dataType,
  503. $readOnly
  504. ) {
  505. $theClass = '';
  506. $textAreaRows = $GLOBALS['cfg']['TextareaRows'];
  507. $textareaCols = $GLOBALS['cfg']['TextareaCols'];
  508. if ($column['is_char']) {
  509. /**
  510. * @todo clarify the meaning of the "textfield" class and explain
  511. * why character columns have the "char" class instead
  512. */
  513. $theClass = 'char charField';
  514. $textAreaRows = $GLOBALS['cfg']['CharTextareaRows'];
  515. $textareaCols = $GLOBALS['cfg']['CharTextareaCols'];
  516. $extractedColumnspec = Util::extractColumnSpec($column['Type']);
  517. $maxlength = $extractedColumnspec['spec_in_brackets'];
  518. } elseif ($GLOBALS['cfg']['LongtextDoubleTextarea'] && mb_strstr($column['pma_type'], 'longtext')) {
  519. $textAreaRows = $GLOBALS['cfg']['TextareaRows'] * 2;
  520. $textareaCols = $GLOBALS['cfg']['TextareaCols'] * 2;
  521. }
  522. return $backupField . "\n"
  523. . '<textarea name="fields' . $columnNameAppendix . '"'
  524. . ' class="' . $theClass . '"'
  525. . ($readOnly ? ' readonly="readonly"' : '')
  526. . (isset($maxlength) ? ' data-maxlength="' . $maxlength . '"' : '')
  527. . ' rows="' . $textAreaRows . '"'
  528. . ' cols="' . $textareaCols . '"'
  529. . ' dir="' . $textDir . '"'
  530. . ' id="field_' . $idindex . '_3"'
  531. . (! empty($onChangeClause) ? ' ' . $onChangeClause : '')
  532. . ' tabindex="' . ($tabindex + $tabindexForValue) . '"'
  533. . ' data-type="' . $dataType . '">'
  534. . $specialCharsEncoded
  535. . '</textarea>';
  536. }
  537. /**
  538. * Get column values
  539. *
  540. * @param array $column description of column in given table
  541. * @param array $extractedColumnspec associative array containing type,
  542. * spec_in_brackets and possibly enum_set_values
  543. * (another array)
  544. *
  545. * @return array column values as an associative array
  546. */
  547. private function getColumnEnumValues(array $column, array $extractedColumnspec)
  548. {
  549. $column['values'] = [];
  550. foreach ($extractedColumnspec['enum_set_values'] as $val) {
  551. $column['values'][] = [
  552. 'plain' => $val,
  553. 'html' => htmlspecialchars($val),
  554. ];
  555. }
  556. return $column['values'];
  557. }
  558. /**
  559. * Retrieve column 'set' value and select size
  560. *
  561. * @param array $column description of column in given table
  562. * @param array $extractedColumnspec associative array containing type,
  563. * spec_in_brackets and possibly enum_set_values
  564. * (another array)
  565. *
  566. * @return array $column['values'], $column['select_size']
  567. */
  568. private function getColumnSetValueAndSelectSize(
  569. array $column,
  570. array $extractedColumnspec
  571. ) {
  572. if (! isset($column['values'])) {
  573. $column['values'] = [];
  574. foreach ($extractedColumnspec['enum_set_values'] as $val) {
  575. $column['values'][] = [
  576. 'plain' => $val,
  577. 'html' => htmlspecialchars($val),
  578. ];
  579. }
  580. $column['select_size'] = min(4, count($column['values']));
  581. }
  582. return [
  583. $column['values'],
  584. $column['select_size'],
  585. ];
  586. }
  587. /**
  588. * Get HTML input type
  589. *
  590. * @param array $column description of column in given table
  591. * @param string $columnNameAppendix the name attribute
  592. * @param string $specialChars special characters
  593. * @param int $fieldsize html field size
  594. * @param string $onChangeClause onchange clause for fields
  595. * @param int $tabindex tab index
  596. * @param int $tabindexForValue offset for the values tabindex
  597. * @param int $idindex id index
  598. * @param string $dataType the html5 data-* attribute type
  599. * @param bool $readOnly is column read only or not
  600. *
  601. * @return string an html snippet
  602. */
  603. private function getHtmlInput(
  604. array $column,
  605. $columnNameAppendix,
  606. $specialChars,
  607. $fieldsize,
  608. $onChangeClause,
  609. $tabindex,
  610. $tabindexForValue,
  611. $idindex,
  612. $dataType,
  613. $readOnly
  614. ) {
  615. $inputType = 'text';
  616. // do not use the 'date' or 'time' types here; they have no effect on some
  617. // browsers and create side effects (see bug #4218)
  618. $theClass = 'textfield';
  619. // verify True_Type which does not contain the parentheses and length
  620. if (! $readOnly) {
  621. if ($column['True_Type'] === 'date') {
  622. $theClass .= ' datefield';
  623. } elseif ($column['True_Type'] === 'time') {
  624. $theClass .= ' timefield';
  625. } elseif ($column['True_Type'] === 'datetime' || $column['True_Type'] === 'timestamp') {
  626. $theClass .= ' datetimefield';
  627. }
  628. }
  629. $inputMinMax = false;
  630. if (in_array($column['True_Type'], $this->dbi->types->getIntegerTypes())) {
  631. $extractedColumnspec = Util::extractColumnSpec($column['Type']);
  632. $isUnsigned = $extractedColumnspec['unsigned'];
  633. $minMaxValues = $this->dbi->types->getIntegerRange($column['True_Type'], ! $isUnsigned);
  634. $inputMinMax = 'min="' . $minMaxValues[0] . '" '
  635. . 'max="' . $minMaxValues[1] . '"';
  636. $dataType = 'INT';
  637. }
  638. return '<input type="' . $inputType . '"'
  639. . ' name="fields' . $columnNameAppendix . '"'
  640. . ' value="' . $specialChars . '" size="' . $fieldsize . '"'
  641. . (isset($column['is_char']) && $column['is_char']
  642. ? ' data-maxlength="' . $fieldsize . '"'
  643. : '')
  644. . ($readOnly ? ' readonly="readonly"' : '')
  645. . ($inputMinMax !== false ? ' ' . $inputMinMax : '')
  646. . ' data-type="' . $dataType . '"'
  647. . ($inputType === 'time' ? ' step="1"' : '')
  648. . ' class="' . $theClass . '" ' . $onChangeClause
  649. . ' tabindex="' . ($tabindex + $tabindexForValue) . '"'
  650. . ' id="field_' . $idindex . '_3">';
  651. }
  652. /**
  653. * Get HTML select option for upload
  654. *
  655. * @param string $vkey [multi_edit]['row_id']
  656. * @param array $column description of column in given table
  657. *
  658. * @return string|null an html snippet
  659. */
  660. private function getSelectOptionForUpload($vkey, array $column)
  661. {
  662. $files = $this->fileListing->getFileSelectOptions(
  663. Util::userDir((string) ($GLOBALS['cfg']['UploadDir'] ?? ''))
  664. );
  665. if ($files === false) {
  666. return '<span style="color:red">' . __('Error') . '</span><br>' . "\n"
  667. . __('The directory you set for upload work cannot be reached.') . "\n";
  668. }
  669. if (! empty($files)) {
  670. return "<br>\n"
  671. . '<i>' . __('Or') . '</i> '
  672. . __('web server upload directory:') . '<br>' . "\n"
  673. . '<select size="1" name="fields_uploadlocal'
  674. . $vkey . '[' . $column['Field_md5'] . ']">' . "\n"
  675. . '<option value="" selected="selected"></option>' . "\n"
  676. . $files
  677. . '</select>' . "\n";
  678. }
  679. return null;
  680. }
  681. /**
  682. * Retrieve the maximum upload file size
  683. *
  684. * @param array $column description of column in given table
  685. * @param int $biggestMaxFileSize biggest max file size for uploading
  686. *
  687. * @return array an html snippet and $biggest_max_file_size
  688. */
  689. private function getMaxUploadSize(array $column, $biggestMaxFileSize)
  690. {
  691. // find maximum upload size, based on field type
  692. /**
  693. * @todo with functions this is not so easy, as you can basically
  694. * process any data with function like MD5
  695. */
  696. $maxFieldSizes = [
  697. 'tinyblob' => '256',
  698. 'blob' => '65536',
  699. 'mediumblob' => '16777216',
  700. 'longblob' => '4294967296',// yeah, really
  701. ];
  702. $thisFieldMaxSize = $GLOBALS['config']->get('max_upload_size'); // from PHP max
  703. if ($thisFieldMaxSize > $maxFieldSizes[$column['pma_type']]) {
  704. $thisFieldMaxSize = $maxFieldSizes[$column['pma_type']];
  705. }
  706. $htmlOutput = Util::getFormattedMaximumUploadSize($thisFieldMaxSize) . "\n";
  707. // do not generate here the MAX_FILE_SIZE, because we should
  708. // put only one in the form to accommodate the biggest field
  709. if ($thisFieldMaxSize > $biggestMaxFileSize) {
  710. $biggestMaxFileSize = $thisFieldMaxSize;
  711. }
  712. return [
  713. $htmlOutput,
  714. $biggestMaxFileSize,
  715. ];
  716. }
  717. /**
  718. * Get HTML for the Value column of other datatypes
  719. * (here, "column" is used in the sense of HTML column in HTML table)
  720. *
  721. * @param array $column description of column in given table
  722. * @param string $defaultCharEditing default char editing mode which is stored
  723. * in the config.inc.php script
  724. * @param string $backupField hidden input field
  725. * @param string $columnNameAppendix the name attribute
  726. * @param string $onChangeClause onchange clause for fields
  727. * @param int $tabindex tab index
  728. * @param string $specialChars special characters
  729. * @param int $tabindexForValue offset for the values tabindex
  730. * @param int $idindex id index
  731. * @param string $textDir text direction
  732. * @param string $specialCharsEncoded replaced char if the string starts
  733. * with a \r\n pair (0x0d0a) add an extra \n
  734. * @param string $data data to edit
  735. * @param array $extractedColumnspec associative array containing type,
  736. * spec_in_brackets and possibly
  737. * enum_set_values (another array)
  738. * @param bool $readOnly is column read only or not
  739. *
  740. * @return string an html snippet
  741. */
  742. private function getValueColumnForOtherDatatypes(
  743. array $column,
  744. $defaultCharEditing,
  745. $backupField,
  746. $columnNameAppendix,
  747. $onChangeClause,
  748. $tabindex,
  749. $specialChars,
  750. $tabindexForValue,
  751. $idindex,
  752. $textDir,
  753. $specialCharsEncoded,
  754. $data,
  755. array $extractedColumnspec,
  756. $readOnly
  757. ) {
  758. // HTML5 data-* attribute data-type
  759. $dataType = $this->dbi->types->getTypeClass($column['True_Type']);
  760. $fieldsize = $this->getColumnSize($column, $extractedColumnspec);
  761. $htmlOutput = $backupField . "\n";
  762. if ($column['is_char'] && ($GLOBALS['cfg']['CharEditing'] === 'textarea' || str_contains($data, "\n"))) {
  763. $htmlOutput .= "\n";
  764. $GLOBALS['cfg']['CharEditing'] = $defaultCharEditing;
  765. $htmlOutput .= $this->getTextarea(
  766. $column,
  767. $backupField,
  768. $columnNameAppendix,
  769. $onChangeClause,
  770. $tabindex,
  771. $tabindexForValue,
  772. $idindex,
  773. $textDir,
  774. $specialCharsEncoded,
  775. $dataType,
  776. $readOnly
  777. );
  778. } else {
  779. $htmlOutput .= $this->getHtmlInput(
  780. $column,
  781. $columnNameAppendix,
  782. $specialChars,
  783. $fieldsize,
  784. $onChangeClause,
  785. $tabindex,
  786. $tabindexForValue,
  787. $idindex,
  788. $dataType,
  789. $readOnly
  790. );
  791. if (
  792. preg_match('/(VIRTUAL|PERSISTENT|GENERATED)/', $column['Extra'])
  793. && ! str_contains($column['Extra'], 'DEFAULT_GENERATED')
  794. ) {
  795. $htmlOutput .= '<input type="hidden" name="virtual'
  796. . $columnNameAppendix . '" value="1">';
  797. }
  798. if ($column['Extra'] === 'auto_increment') {
  799. $htmlOutput .= '<input type="hidden" name="auto_increment'
  800. . $columnNameAppendix . '" value="1">';
  801. }
  802. if (substr($column['pma_type'], 0, 9) === 'timestamp') {
  803. $htmlOutput .= '<input type="hidden" name="fields_type'
  804. . $columnNameAppendix . '" value="timestamp">';
  805. }
  806. if (substr($column['pma_type'], 0, 4) === 'date') {
  807. $type = substr($column['pma_type'], 0, 8) === 'datetime' ? 'datetime' : 'date';
  808. $htmlOutput .= '<input type="hidden" name="fields_type'
  809. . $columnNameAppendix . '" value="' . $type . '">';
  810. }
  811. if ($column['True_Type'] === 'bit') {
  812. $htmlOutput .= '<input type="hidden" name="fields_type'
  813. . $columnNameAppendix . '" value="bit">';
  814. }
  815. }
  816. return $htmlOutput;
  817. }
  818. /**
  819. * Get the field size
  820. *
  821. * @param array $column description of column in given table
  822. * @param array $extractedColumnspec associative array containing type,
  823. * spec_in_brackets and possibly enum_set_values
  824. * (another array)
  825. *
  826. * @return int field size
  827. */
  828. private function getColumnSize(array $column, array $extractedColumnspec)
  829. {
  830. if ($column['is_char']) {
  831. $fieldsize = $extractedColumnspec['spec_in_brackets'];
  832. if ($fieldsize > $GLOBALS['cfg']['MaxSizeForInputField']) {
  833. /**
  834. * This case happens for CHAR or VARCHAR columns which have
  835. * a size larger than the maximum size for input field.
  836. */
  837. $GLOBALS['cfg']['CharEditing'] = 'textarea';
  838. }
  839. } else {
  840. /**
  841. * This case happens for example for INT or DATE columns;
  842. * in these situations, the value returned in $column['len']
  843. * seems appropriate.
  844. */
  845. $fieldsize = $column['len'];
  846. }
  847. return min(
  848. max($fieldsize, $GLOBALS['cfg']['MinSizeForInputField']),
  849. $GLOBALS['cfg']['MaxSizeForInputField']
  850. );
  851. }
  852. /**
  853. * get html for continue insertion form
  854. *
  855. * @param string $table name of the table
  856. * @param string $db name of the database
  857. * @param array $whereClauseArray array of where clauses
  858. * @param string $errorUrl error url
  859. *
  860. * @return string an html snippet
  861. */
  862. public function getContinueInsertionForm(
  863. $table,
  864. $db,
  865. array $whereClauseArray,
  866. $errorUrl
  867. ) {
  868. return $this->template->render('table/insert/continue_insertion_form', [
  869. 'db' => $db,
  870. 'table' => $table,
  871. 'where_clause_array' => $whereClauseArray,
  872. 'err_url' => $errorUrl,
  873. 'goto' => $GLOBALS['goto'],
  874. 'sql_query' => $_POST['sql_query'] ?? null,
  875. 'has_where_clause' => isset($_POST['where_clause']),
  876. 'insert_rows_default' => $GLOBALS['cfg']['InsertRows'],
  877. ]);
  878. }
  879. /**
  880. * @param string[]|string|null $whereClause
  881. *
  882. * @psalm-pure
  883. */
  884. public static function isWhereClauseNumeric($whereClause): bool
  885. {
  886. if (! isset($whereClause)) {
  887. return false;
  888. }
  889. $isNumeric = false;
  890. if (! is_array($whereClause)) {
  891. $whereClause = [$whereClause];
  892. }
  893. // If we have just numeric primary key, we can also edit next
  894. // we are looking for `table_name`.`field_name` = numeric_value
  895. foreach ($whereClause as $clause) {
  896. // preg_match() returns 1 if there is a match
  897. $isNumeric = preg_match('@^[\s]*`[^`]*`[\.]`[^`]*` = [0-9]+@', $clause) === 1;
  898. if ($isNumeric === true) {
  899. break;
  900. }
  901. }
  902. return $isNumeric;
  903. }
  904. /**
  905. * Get table head and table foot for insert row table
  906. *
  907. * @param array $urlParams url parameters
  908. *
  909. * @return string an html snippet
  910. */
  911. private function getHeadAndFootOfInsertRowTable(array $urlParams)
  912. {
  913. $type = '';
  914. $function = '';
  915. if ($GLOBALS['cfg']['ShowFieldTypesInDataEditView']) {
  916. $type = $this->showTypeOrFunction('type', $urlParams, true);
  917. }
  918. if ($GLOBALS['cfg']['ShowFunctionFields']) {
  919. $function = $this->showTypeOrFunction('function', $urlParams, true);
  920. }
  921. $template = new Template();
  922. return $template->render('table/insert/get_head_and_foot_of_insert_row_table', [
  923. 'type' => $type,
  924. 'function' => $function,
  925. ]);
  926. }
  927. /**
  928. * Prepares the field value and retrieve special chars, backup field and data array
  929. *
  930. * @param array $currentRow a row of the table
  931. * @param array $column description of column in given table
  932. * @param array $extractedColumnspec associative array containing type,
  933. * spec_in_brackets and possibly
  934. * enum_set_values (another array)
  935. * @param bool $realNullValue whether column value null or not null
  936. * @param array $gisDataTypes list of GIS data types
  937. * @param string $columnNameAppendix string to append to column name in input
  938. * @param bool $asIs use the data as is, used in repopulating
  939. *
  940. * @return array $real_null_value, $data, $special_chars, $backup_field,
  941. * $special_chars_encoded
  942. */
  943. private function getSpecialCharsAndBackupFieldForExistingRow(
  944. array $currentRow,
  945. array $column,
  946. array $extractedColumnspec,
  947. $realNullValue,
  948. array $gisDataTypes,
  949. $columnNameAppendix,
  950. $asIs
  951. ) {
  952. $specialCharsEncoded = '';
  953. $data = null;
  954. // (we are editing)
  955. if (! isset($currentRow[$column['Field']])) {
  956. $realNullValue = true;
  957. $currentRow[$column['Field']] = '';
  958. $specialChars = '';
  959. $data = $currentRow[$column['Field']];
  960. } elseif ($column['True_Type'] === 'bit') {
  961. $specialChars = $asIs
  962. ? $currentRow[$column['Field']]
  963. : Util::printableBitValue(
  964. (int) $currentRow[$column['Field']],
  965. (int) $extractedColumnspec['spec_in_brackets']
  966. );
  967. } elseif (
  968. (substr($column['True_Type'], 0, 9) === 'timestamp'
  969. || $column['True_Type'] === 'datetime'
  970. || $column['True_Type'] === 'time')
  971. && (str_contains($currentRow[$column['Field']], '.'))
  972. ) {
  973. $currentRow[$column['Field']] = $asIs
  974. ? $currentRow[$column['Field']]
  975. : Util::addMicroseconds($currentRow[$column['Field']]);
  976. $specialChars = htmlspecialchars($currentRow[$column['Field']], ENT_COMPAT);
  977. } elseif (in_array($column['True_Type'], $gisDataTypes)) {
  978. // Convert gis data to Well Know Text format
  979. $currentRow[$column['Field']] = $asIs
  980. ? $currentRow[$column['Field']]
  981. : Gis::convertToWellKnownText($currentRow[$column['Field']], true);
  982. $specialChars = htmlspecialchars($currentRow[$column['Field']], ENT_COMPAT);
  983. } else {
  984. // special binary "characters"
  985. if ($column['is_binary'] || ($column['is_blob'] && $GLOBALS['cfg']['ProtectBinary'] !== 'all')) {
  986. $currentRow[$column['Field']] = $asIs
  987. ? $currentRow[$column['Field']]
  988. : bin2hex($currentRow[$column['Field']]);
  989. }
  990. $specialChars = htmlspecialchars($currentRow[$column['Field']], ENT_COMPAT);
  991. //We need to duplicate the first \n or otherwise we will lose
  992. //the first newline entered in a VARCHAR or TEXT column
  993. $specialCharsEncoded = Util::duplicateFirstNewline($specialChars);
  994. $data = $currentRow[$column['Field']];
  995. }
  996. //when copying row, it is useful to empty auto-increment column
  997. // to prevent duplicate key error
  998. if (isset($_POST['default_action']) && $_POST['default_action'] === 'insert') {
  999. if ($column['Key'] === 'PRI' && str_contains($column['Extra'], 'auto_increment')) {
  1000. $data = $specialCharsEncoded = $specialChars = null;
  1001. }
  1002. }
  1003. // If a timestamp field value is not included in an update
  1004. // statement MySQL auto-update it to the current timestamp;
  1005. // however, things have changed since MySQL 4.1, so
  1006. // it's better to set a fields_prev in this situation
  1007. $backupField = '<input type="hidden" name="fields_prev'
  1008. . $columnNameAppendix . '" value="'
  1009. . htmlspecialchars($currentRow[$column['Field']], ENT_COMPAT) . '">';
  1010. return [
  1011. $realNullValue,
  1012. $specialCharsEncoded,
  1013. $specialChars,
  1014. $data,
  1015. $backupField,
  1016. ];
  1017. }
  1018. /**
  1019. * display default values
  1020. *
  1021. * @param array $column description of column in given table
  1022. * @param bool $realNullValue whether column value null or not null
  1023. *
  1024. * @return array $real_null_value, $data, $special_chars,
  1025. * $backup_field, $special_chars_encoded
  1026. */
  1027. private function getSpecialCharsAndBackupFieldForInsertingMode(
  1028. array $column,
  1029. $realNullValue
  1030. ) {
  1031. if (! isset($column['Default'])) {
  1032. $column['Default'] = '';
  1033. $realNullValue = true;
  1034. $data = '';
  1035. } else {
  1036. $data = $column['Default'];
  1037. }
  1038. $trueType = $column['True_Type'];
  1039. if ($trueType === 'bit') {
  1040. $specialChars = Util::convertBitDefaultValue($column['Default']);
  1041. } elseif (substr($trueType, 0, 9) === 'timestamp' || $trueType === 'datetime' || $trueType === 'time') {
  1042. $specialChars = Util::addMicroseconds($column['Default']);
  1043. } elseif ($trueType === 'binary' || $trueType === 'varbinary') {
  1044. $specialChars = bin2hex($column['Default']);
  1045. } elseif (substr($trueType, -4) === 'text') {
  1046. $textDefault = substr($column['Default'], 1, -1);
  1047. $specialChars = stripcslashes($textDefault !== false ? $textDefault : $column['Default']);
  1048. } else {
  1049. $specialChars = htmlspecialchars($column['Default']);
  1050. }
  1051. $backupField = '';
  1052. $specialCharsEncoded = Util::duplicateFirstNewline($specialChars);
  1053. return [
  1054. $realNullValue,
  1055. $data,
  1056. $specialChars,
  1057. $backupField,
  1058. $specialCharsEncoded,
  1059. ];
  1060. }
  1061. /**
  1062. * Prepares the update/insert of a row
  1063. *
  1064. * @return array $loop_array, $using_key, $is_insert, $is_insertignore
  1065. */
  1066. public function getParamsForUpdateOrInsert()
  1067. {
  1068. if (isset($_POST['where_clause'])) {
  1069. // we were editing something => use the WHERE clause
  1070. $loopArray = is_array($_POST['where_clause'])
  1071. ? $_POST['where_clause']
  1072. : [$_POST['where_clause']];
  1073. $usingKey = true;
  1074. $isInsert = isset($_POST['submit_type'])
  1075. && ($_POST['submit_type'] === 'insert'
  1076. || $_POST['submit_type'] === 'showinsert'
  1077. || $_POST['submit_type'] === 'insertignore');
  1078. } else {
  1079. // new row => use indexes
  1080. $loopArray = [];
  1081. if (! empty($_POST['fields'])) {
  1082. foreach ($_POST['fields']['multi_edit'] as $key => $dummy) {
  1083. $loopArray[] = $key;
  1084. }
  1085. }
  1086. $usingKey = false;
  1087. $isInsert = true;
  1088. }
  1089. $isInsertIgnore = isset($_POST['submit_type'])
  1090. && $_POST['submit_type'] === 'insertignore';
  1091. return [
  1092. $loopArray,
  1093. $usingKey,
  1094. $isInsert,
  1095. $isInsertIgnore,
  1096. ];
  1097. }
  1098. /**
  1099. * set $_SESSION for edit_next
  1100. *
  1101. * @param string $oneWhereClause one where clause from where clauses array
  1102. */
  1103. public function setSessionForEditNext($oneWhereClause): void
  1104. {
  1105. $localQuery = 'SELECT * FROM ' . Util::backquote($GLOBALS['db'])
  1106. . '.' . Util::backquote($GLOBALS['table']) . ' WHERE '
  1107. . str_replace('` =', '` >', $oneWhereClause) . ' LIMIT 1;';
  1108. $res = $this->dbi->query($localQuery);
  1109. $row = $this->dbi->fetchRow($res);
  1110. $meta = $this->dbi->getFieldsMeta($res) ?? [];
  1111. // must find a unique condition based on unique key,
  1112. // not a combination of all fields
  1113. [$uniqueCondition, $clauseIsUnique] = Util::getUniqueCondition(
  1114. $res,
  1115. count($meta),
  1116. $meta,
  1117. $row ?? [],
  1118. true
  1119. );
  1120. if (! empty($uniqueCondition)) {
  1121. $_SESSION['edit_next'] = $uniqueCondition;
  1122. }
  1123. unset($uniqueCondition, $clauseIsUnique);
  1124. }
  1125. /**
  1126. * set $goto_include variable for different cases and retrieve like,
  1127. * if $GLOBALS['goto'] empty, if $goto_include previously not defined
  1128. * and new_insert, same_insert, edit_next
  1129. *
  1130. * @param string|false $gotoInclude store some script for include, otherwise it is
  1131. * boolean false
  1132. *
  1133. * @return string|false
  1134. */
  1135. public function getGotoInclude($gotoInclude)
  1136. {
  1137. $validOptions = [
  1138. 'new_insert',
  1139. 'same_insert',
  1140. 'edit_next',
  1141. ];
  1142. if (isset($_POST['after_insert']) && in_array($_POST['after_insert'], $validOptions)) {
  1143. $gotoInclude = '/table/change';
  1144. } elseif (! empty($GLOBALS['goto'])) {
  1145. if (! preg_match('@^[a-z_]+\.php$@', $GLOBALS['goto'])) {
  1146. // this should NOT happen
  1147. //$GLOBALS['goto'] = false;
  1148. if ($GLOBALS['goto'] === 'index.php?route=/sql') {
  1149. $gotoInclude = '/sql';
  1150. } else {
  1151. $gotoInclude = false;
  1152. }
  1153. } else {
  1154. $gotoInclude = $GLOBALS['goto'];
  1155. }
  1156. if ($GLOBALS['goto'] === 'index.php?route=/database/sql' && strlen($GLOBALS['table']) > 0) {
  1157. $GLOBALS['table'] = '';
  1158. }
  1159. }
  1160. if (! $gotoInclude) {
  1161. if (strlen($GLOBALS['table']) === 0) {
  1162. $gotoInclude = '/database/sql';
  1163. } else {
  1164. $gotoInclude = '/table/sql';
  1165. }
  1166. }
  1167. return $gotoInclude;
  1168. }
  1169. /**
  1170. * Defines the url to return in case of failure of the query
  1171. *
  1172. * @param array $urlParams url parameters
  1173. *
  1174. * @return string error url for query failure
  1175. */
  1176. public function getErrorUrl(array $urlParams)
  1177. {
  1178. if (isset($_POST['err_url'])) {
  1179. return $_POST['err_url'];
  1180. }
  1181. return Url::getFromRoute('/table/change', $urlParams);
  1182. }
  1183. /**
  1184. * Builds the sql query
  1185. *
  1186. * @param bool $isInsertIgnore $_POST['submit_type'] === 'insertignore'
  1187. * @param array $queryFields column names array
  1188. * @param array $valueSets array of query values
  1189. *
  1190. * @return array of query
  1191. */
  1192. public function buildSqlQuery($isInsertIgnore, array $queryFields, array $valueSets)
  1193. {
  1194. if ($isInsertIgnore) {
  1195. $insertCommand = 'INSERT IGNORE ';
  1196. } else {
  1197. $insertCommand = 'INSERT ';
  1198. }
  1199. return [
  1200. $insertCommand . 'INTO '
  1201. . Util::backquote($GLOBALS['table'])
  1202. . ' (' . implode(', ', $queryFields) . ') VALUES ('
  1203. . implode('), (', $valueSets) . ')',
  1204. ];
  1205. }
  1206. /**
  1207. * Executes the sql query and get the result, then move back to the calling page
  1208. *
  1209. * @param array $urlParams url parameters array
  1210. * @param array $query built query from buildSqlQuery()
  1211. *
  1212. * @return array $url_params, $total_affected_rows, $last_messages
  1213. * $warning_messages, $error_messages, $return_to_sql_query
  1214. */
  1215. public function executeSqlQuery(array $urlParams, array $query)
  1216. {
  1217. $returnToSqlQuery = '';
  1218. if (! empty($GLOBALS['sql_query'])) {
  1219. $urlParams['sql_query'] = $GLOBALS['sql_query'];
  1220. $returnToSqlQuery = $GLOBALS['sql_query'];
  1221. }
  1222. $GLOBALS['sql_query'] = implode('; ', $query) . ';';
  1223. // to ensure that the query is displayed in case of
  1224. // "insert as new row" and then "insert another new row"
  1225. $GLOBALS['display_query'] = $GLOBALS['sql_query'];
  1226. $totalAffectedRows = 0;
  1227. $lastMessages = [];
  1228. $warningMessages = [];
  1229. $errorMessages = [];
  1230. foreach ($query as $singleQuery) {
  1231. if (isset($_POST['submit_type']) && $_POST['submit_type'] === 'showinsert') {
  1232. $lastMessages[] = Message::notice(__('Showing SQL query'));
  1233. continue;
  1234. }
  1235. if ($GLOBALS['cfg']['IgnoreMultiSubmitErrors']) {
  1236. $result = $this->dbi->tryQuery($singleQuery);
  1237. } else {
  1238. $result = $this->dbi->query($singleQuery);
  1239. }
  1240. if (! $result) {
  1241. $errorMessages[] = $this->dbi->getError();
  1242. } else {
  1243. $tmp = @$this->dbi->affectedRows();
  1244. if ($tmp) {
  1245. $totalAffectedRows += $tmp;
  1246. }
  1247. unset($tmp);
  1248. $insertId = $this->dbi->insertId();
  1249. if ($insertId !== false && $insertId != 0) {
  1250. // insert_id is id of FIRST record inserted in one insert, so if we
  1251. // inserted multiple rows, we had to increment this
  1252. if ($totalAffectedRows > 0) {
  1253. $insertId += $totalAffectedRows - 1;
  1254. }
  1255. $lastMessage = Message::notice(__('Inserted row id: %1$d'));
  1256. $lastMessage->addParam($insertId);
  1257. $lastMessages[] = $lastMessage;
  1258. }
  1259. $this->dbi->freeResult($result);
  1260. }
  1261. $warningMessages = $this->getWarningMessages();
  1262. }
  1263. return [
  1264. $urlParams,
  1265. $totalAffectedRows,
  1266. $lastMessages,
  1267. $warningMessages,
  1268. $errorMessages,
  1269. $returnToSqlQuery,
  1270. ];
  1271. }
  1272. /**
  1273. * get the warning messages array
  1274. *
  1275. * @return string[]
  1276. */
  1277. private function getWarningMessages(): array
  1278. {
  1279. $warningMessages = [];
  1280. foreach ($this->dbi->getWarnings() as $warning) {
  1281. $warningMessages[] = htmlspecialchars((string) $warning);
  1282. }
  1283. return $warningMessages;
  1284. }
  1285. /**
  1286. * Column to display from the foreign table?
  1287. *
  1288. * @param string $whereComparison string that contain relation field value
  1289. * @param array $map all Relations to foreign tables for a given
  1290. * table or optionally a given column in a table
  1291. * @param string $relationField relation field
  1292. *
  1293. * @return string display value from the foreign table
  1294. */
  1295. public function getDisplayValueForForeignTableColumn(
  1296. $whereComparison,
  1297. array $map,
  1298. $relationField
  1299. ) {
  1300. $foreigner = $this->relation->searchColumnInForeigners($map, $relationField);
  1301. if (! is_array($foreigner)) {
  1302. return '';
  1303. }
  1304. $displayField = $this->relation->getDisplayField($foreigner['foreign_db'], $foreigner['foreign_table']);
  1305. // Field to display from the foreign table?
  1306. if (is_string($displayField) && strlen($displayField) > 0) {
  1307. $dispsql = 'SELECT ' . Util::backquote($displayField)
  1308. . ' FROM ' . Util::backquote($foreigner['foreign_db'])
  1309. . '.' . Util::backquote($foreigner['foreign_table'])
  1310. . ' WHERE ' . Util::backquote($foreigner['foreign_field'])
  1311. . $whereComparison;
  1312. $dispresult = $this->dbi->tryQuery(
  1313. $dispsql,
  1314. DatabaseInterface::CONNECT_USER,
  1315. DatabaseInterface::QUERY_STORE
  1316. );
  1317. if ($dispresult && $this->dbi->numRows($dispresult) > 0) {
  1318. [$dispval] = $this->dbi->fetchRow($dispresult);
  1319. } else {
  1320. $dispval = '';
  1321. }
  1322. if ($dispresult) {
  1323. $this->dbi->freeResult($dispresult);
  1324. }
  1325. return $dispval;
  1326. }
  1327. return '';
  1328. }
  1329. /**
  1330. * Display option in the cell according to user choices
  1331. *
  1332. * @param array $map all Relations to foreign tables for a given
  1333. * table or optionally a given column in a table
  1334. * @param string $relationField relation field
  1335. * @param string $whereComparison string that contain relation field value
  1336. * @param string $dispval display value from the foreign table
  1337. * @param string $relationFieldValue relation field value
  1338. *
  1339. * @return string HTML <a> tag
  1340. */
  1341. public function getLinkForRelationalDisplayField(
  1342. array $map,
  1343. $relationField,
  1344. $whereComparison,
  1345. $dispval,
  1346. $relationFieldValue
  1347. ) {
  1348. $foreigner = $this->relation->searchColumnInForeigners($map, $relationField);
  1349. if (! is_array($foreigner)) {
  1350. return '';
  1351. }
  1352. if ($_SESSION['tmpval']['relational_display'] === 'K') {
  1353. // user chose "relational key" in the display options, so
  1354. // the title contains the display field
  1355. $title = ! empty($dispval)
  1356. ? ' title="' . htmlspecialchars($dispval) . '"'
  1357. : '';
  1358. } else {
  1359. $title = ' title="' . htmlspecialchars($relationFieldValue) . '"';
  1360. }
  1361. $sqlQuery = 'SELECT * FROM '
  1362. . Util::backquote($foreigner['foreign_db'])
  1363. . '.' . Util::backquote($foreigner['foreign_table'])
  1364. . ' WHERE ' . Util::backquote($foreigner['foreign_field'])
  1365. . $whereComparison;
  1366. $urlParams = [
  1367. 'db' => $foreigner['foreign_db'],
  1368. 'table' => $foreigner['foreign_table'],
  1369. 'pos' => '0',
  1370. 'sql_signature' => Core::signSqlQuery($sqlQuery),
  1371. 'sql_query' => $sqlQuery,
  1372. ];
  1373. $output = '<a href="' . Url::getFromRoute('/sql', $urlParams) . '"' . $title . '>';
  1374. if ($_SESSION['tmpval']['relational_display'] === 'D') {
  1375. // user chose "relational display field" in the
  1376. // display options, so show display field in the cell
  1377. $output .= ! empty($dispval) ? htmlspecialchars($dispval) : '';
  1378. } else {
  1379. // otherwise display data in the cell
  1380. $output .= htmlspecialchars($relationFieldValue);
  1381. }
  1382. $output .= '</a>';
  1383. return $output;
  1384. }
  1385. /**
  1386. * Transform edited values
  1387. *
  1388. * @param string $db db name
  1389. * @param string $table table name
  1390. * @param array $transformation mimetypes for all columns of a table
  1391. * [field_name][field_key]
  1392. * @param array $editedValues transform columns list and new values
  1393. * @param string $file file containing the transformation plugin
  1394. * @param string $columnName column name
  1395. * @param array $extraData extra data array
  1396. * @param string $type the type of transformation
  1397. *
  1398. * @return array
  1399. */
  1400. public function transformEditedValues(
  1401. $db,
  1402. $table,
  1403. array $transformation,
  1404. array &$editedValues,
  1405. $file,
  1406. $columnName,
  1407. array $extraData,
  1408. $type
  1409. ) {
  1410. $includeFile = 'libraries/classes/Plugins/Transformations/' . $file;
  1411. if (is_file(ROOT_PATH . $includeFile)) {
  1412. // $cfg['SaveCellsAtOnce'] = true; JS code sends an array
  1413. $whereClause = is_array($_POST['where_clause']) ? $_POST['where_clause'][0] : $_POST['where_clause'];
  1414. $urlParams = [
  1415. 'db' => $db,
  1416. 'table' => $table,
  1417. 'where_clause_sign' => Core::signSqlQuery($whereClause),
  1418. 'where_clause' => $whereClause,
  1419. 'transform_key' => $columnName,
  1420. ];
  1421. $transformOptions = $this->transformations->getOptions($transformation[$type . '_options'] ?? '');
  1422. $transformOptions['wrapper_link'] = Url::getCommon($urlParams);
  1423. $transformOptions['wrapper_params'] = $urlParams;
  1424. $className = $this->transformations->getClassName($includeFile);
  1425. if (class_exists($className)) {
  1426. /** @var TransformationsPlugin $transformationPlugin */
  1427. $transformationPlugin = new $className();
  1428. foreach ($editedValues as $cellIndex => $currCellEditedValues) {
  1429. if (! isset($currCellEditedValues[$columnName])) {
  1430. continue;
  1431. }
  1432. $extraData['transformations'][$cellIndex] = $transformationPlugin->applyTransformation(
  1433. $currCellEditedValues[$columnName],
  1434. $transformOptions
  1435. );
  1436. $editedValues[$cellIndex][$columnName] = $extraData['transformations'][$cellIndex];
  1437. }
  1438. }
  1439. }
  1440. return $extraData;
  1441. }
  1442. /**
  1443. * Get current value in multi edit mode
  1444. *
  1445. * @param array $multiEditFuncs multiple edit functions array
  1446. * @param array $multiEditSalt multiple edit array with encryption salt
  1447. * @param array $gisFromTextFunctions array that contains gis from text functions
  1448. * @param string $currentValue current value in the column
  1449. * @param array $gisFromWkbFunctions initially $val is $multi_edit_columns[$key]
  1450. * @param array $funcOptionalParam array('RAND','UNIX_TIMESTAMP')
  1451. * @param array $funcNoParam array of set of string
  1452. * @param string $key an md5 of the column name
  1453. *
  1454. * @return string
  1455. */
  1456. public function getCurrentValueAsAnArrayForMultipleEdit(
  1457. $multiEditFuncs,
  1458. $multiEditSalt,
  1459. $gisFromTextFunctions,
  1460. $currentValue,
  1461. $gisFromWkbFunctions,
  1462. $funcOptionalParam,
  1463. $funcNoParam,
  1464. $key
  1465. ) {
  1466. if (empty($multiEditFuncs[$key])) {
  1467. return $currentValue;
  1468. }
  1469. if ($multiEditFuncs[$key] === 'PHP_PASSWORD_HASH') {
  1470. /**
  1471. * @see https://github.com/vimeo/psalm/issues/3350
  1472. *
  1473. * @psalm-suppress InvalidArgument
  1474. */
  1475. $hash = password_hash($currentValue, PASSWORD_DEFAULT);
  1476. return "'" . $hash . "'";
  1477. }
  1478. if ($multiEditFuncs[$key] === 'UUID') {
  1479. /* This way user will know what UUID new row has */
  1480. $uuid = $this->dbi->fetchValue('SELECT UUID()');
  1481. return "'" . $uuid . "'";
  1482. }
  1483. if (
  1484. in_array($multiEditFuncs[$key], $gisFromTextFunctions)
  1485. || in_array($multiEditFuncs[$key], $gisFromWkbFunctions)
  1486. ) {
  1487. // Remove enclosing apostrophes
  1488. $currentValue = mb_substr($currentValue, 1, -1);
  1489. // Remove escaping apostrophes
  1490. $currentValue = str_replace("''", "'", $currentValue);
  1491. // Remove backslash-escaped apostrophes
  1492. $currentValue = str_replace("\'", "'", $currentValue);
  1493. return $multiEditFuncs[$key] . '(' . $currentValue . ')';
  1494. }
  1495. if (
  1496. ! in_array($multiEditFuncs[$key], $funcNoParam)
  1497. || ($currentValue != "''"
  1498. && in_array($multiEditFuncs[$key], $funcOptionalParam))
  1499. ) {
  1500. if (
  1501. (isset($multiEditSalt[$key])
  1502. && ($multiEditFuncs[$key] === 'AES_ENCRYPT'
  1503. || $multiEditFuncs[$key] === 'AES_DECRYPT'))
  1504. || (! empty($multiEditSalt[$key])
  1505. && ($multiEditFuncs[$key] === 'DES_ENCRYPT'
  1506. || $multiEditFuncs[$key] === 'DES_DECRYPT'
  1507. || $multiEditFuncs[$key] === 'ENCRYPT'))
  1508. ) {
  1509. return $multiEditFuncs[$key] . '(' . $currentValue . ",'"
  1510. . $this->dbi->escapeString($multiEditSalt[$key]) . "')";
  1511. }
  1512. return $multiEditFuncs[$key] . '(' . $currentValue . ')';
  1513. }
  1514. return $multiEditFuncs[$key] . '()';
  1515. }
  1516. /**
  1517. * Get query values array and query fields array for insert and update in multi edit
  1518. *
  1519. * @param array $multiEditColumnsName multiple edit columns name array
  1520. * @param array $multiEditColumnsNull multiple edit columns null array
  1521. * @param string $currentValue current value in the column in loop
  1522. * @param array $multiEditColumnsPrev multiple edit previous columns array
  1523. * @param array $multiEditFuncs multiple edit functions array
  1524. * @param bool $isInsert boolean value whether insert or not
  1525. * @param array $queryValues SET part of the sql query
  1526. * @param array $queryFields array of query fields
  1527. * @param string $currentValueAsAnArray current value in the column
  1528. * as an array
  1529. * @param array $valueSets array of valu sets
  1530. * @param string $key an md5 of the column name
  1531. * @param array $multiEditColumnsNullPrev array of multiple edit columns
  1532. * null previous
  1533. *
  1534. * @return array ($query_values, $query_fields)
  1535. */
  1536. public function getQueryValuesForInsertAndUpdateInMultipleEdit(
  1537. $multiEditColumnsName,
  1538. $multiEditColumnsNull,
  1539. $currentValue,
  1540. $multiEditColumnsPrev,
  1541. $multiEditFuncs,
  1542. $isInsert,
  1543. $queryValues,
  1544. $queryFields,
  1545. $currentValueAsAnArray,
  1546. $valueSets,
  1547. $key,
  1548. $multiEditColumnsNullPrev
  1549. ) {
  1550. // i n s e r t
  1551. if ($isInsert) {
  1552. // no need to add column into the valuelist
  1553. if (strlen($currentValueAsAnArray) > 0) {
  1554. $queryValues[] = $currentValueAsAnArray;
  1555. // first inserted row so prepare the list of fields
  1556. if (empty($valueSets)) {
  1557. $queryFields[] = Util::backquote($multiEditColumnsName[$key]);
  1558. }
  1559. }
  1560. } elseif (! empty($multiEditColumnsNullPrev[$key]) && ! isset($multiEditColumnsNull[$key])) {
  1561. // u p d a t e
  1562. // field had the null checkbox before the update
  1563. // field no longer has the null checkbox
  1564. $queryValues[] = Util::backquote($multiEditColumnsName[$key])
  1565. . ' = ' . $currentValueAsAnArray;
  1566. } elseif (
  1567. ! (empty($multiEditFuncs[$key])
  1568. && isset($multiEditColumnsPrev[$key])
  1569. && (($currentValue === "'" . $this->dbi->escapeString($multiEditColumnsPrev[$key]) . "'")
  1570. || ($currentValue === '0x' . $multiEditColumnsPrev[$key])))
  1571. && ! empty($currentValue)
  1572. ) {
  1573. // avoid setting a field to NULL when it's already NULL
  1574. // (field had the null checkbox before the update
  1575. // field still has the null checkbox)
  1576. if (empty($multiEditColumnsNullPrev[$key]) || empty($multiEditColumnsNull[$key])) {
  1577. $queryValues[] = Util::backquote($multiEditColumnsName[$key])
  1578. . ' = ' . $currentValueAsAnArray;
  1579. }
  1580. }
  1581. return [
  1582. $queryValues,
  1583. $queryFields,
  1584. ];
  1585. }
  1586. /**
  1587. * Get the current column value in the form for different data types
  1588. *
  1589. * @param string|false $possiblyUploadedVal uploaded file content
  1590. * @param string $key an md5 of the column name
  1591. * @param array|null $multiEditColumnsType array of multi edit column types
  1592. * @param string $currentValue current column value in the form
  1593. * @param array|null $multiEditAutoIncrement multi edit auto increment
  1594. * @param int $rownumber index of where clause array
  1595. * @param array $multiEditColumnsName multi edit column names array
  1596. * @param array $multiEditColumnsNull multi edit columns null array
  1597. * @param array $multiEditColumnsNullPrev multi edit columns previous null
  1598. * @param bool $isInsert whether insert or not
  1599. * @param bool $usingKey whether editing or new row
  1600. * @param string $whereClause where clause
  1601. * @param string $table table name
  1602. * @param array $multiEditFuncs multiple edit functions array
  1603. *
  1604. * @return string current column value in the form
  1605. */
  1606. public function getCurrentValueForDifferentTypes(
  1607. $possiblyUploadedVal,
  1608. $key,
  1609. ?array $multiEditColumnsType,
  1610. $currentValue,
  1611. ?array $multiEditAutoIncrement,
  1612. $rownumber,
  1613. $multiEditColumnsName,
  1614. $multiEditColumnsNull,
  1615. $multiEditColumnsNullPrev,
  1616. $isInsert,
  1617. $usingKey,
  1618. $whereClause,
  1619. $table,
  1620. $multiEditFuncs
  1621. ) {
  1622. // Fetch the current values of a row to use in case we have a protected field
  1623. if (
  1624. $isInsert
  1625. && $usingKey && isset($multiEditColumnsType)
  1626. && is_array($multiEditColumnsType) && ! empty($whereClause)
  1627. ) {
  1628. $protectedRow = $this->dbi->fetchSingleRow(
  1629. 'SELECT * FROM ' . Util::backquote($table)
  1630. . ' WHERE ' . $whereClause . ';'
  1631. );
  1632. }
  1633. if ($possiblyUploadedVal !== false) {
  1634. $currentValue = $possiblyUploadedVal;
  1635. } elseif (! empty($multiEditFuncs[$key])) {
  1636. $currentValue = "'" . $this->dbi->escapeString($currentValue)
  1637. . "'";
  1638. } else {
  1639. // c o l u m n v a l u e i n t h e f o r m
  1640. if (isset($multiEditColumnsType[$key])) {
  1641. $type = $multiEditColumnsType[$key];
  1642. } else {
  1643. $type = '';
  1644. }
  1645. if ($type !== 'protected' && $type !== 'set' && strlen($currentValue) === 0) {
  1646. // best way to avoid problems in strict mode
  1647. // (works also in non-strict mode)
  1648. if (isset($multiEditAutoIncrement, $multiEditAutoIncrement[$key])) {
  1649. $currentValue = 'NULL';
  1650. } else {
  1651. $currentValue = "''";
  1652. }
  1653. } elseif ($type === 'set') {
  1654. if (! empty($_POST['fields']['multi_edit'][$rownumber][$key])) {
  1655. $currentValue = implode(',', $_POST['fields']['multi_edit'][$rownumber][$key]);
  1656. $currentValue = "'"
  1657. . $this->dbi->escapeString($currentValue) . "'";
  1658. } else {
  1659. $currentValue = "''";
  1660. }
  1661. } elseif ($type === 'protected') {
  1662. // here we are in protected mode (asked in the config)
  1663. // so tbl_change has put this special value in the
  1664. // columns array, so we do not change the column value
  1665. // but we can still handle column upload
  1666. // when in UPDATE mode, do not alter field's contents. When in INSERT
  1667. // mode, insert empty field because no values were submitted.
  1668. // If protected blobs where set, insert original fields content.
  1669. if (! empty($protectedRow[$multiEditColumnsName[$key]])) {
  1670. $currentValue = '0x'
  1671. . bin2hex($protectedRow[$multiEditColumnsName[$key]]);
  1672. } else {
  1673. $currentValue = '';
  1674. }
  1675. } elseif ($type === 'hex') {
  1676. if (substr($currentValue, 0, 2) != '0x') {
  1677. $currentValue = '0x' . $currentValue;
  1678. }
  1679. } elseif ($type === 'bit') {
  1680. $currentValue = (string) preg_replace('/[^01]/', '0', $currentValue);
  1681. $currentValue = "b'" . $this->dbi->escapeString($currentValue) . "'";
  1682. } elseif (
  1683. ! ($type === 'datetime' || $type === 'timestamp' || $type === 'date')
  1684. || ($currentValue !== 'CURRENT_TIMESTAMP'
  1685. && $currentValue !== 'current_timestamp()')
  1686. ) {
  1687. $currentValue = "'" . $this->dbi->escapeString($currentValue)
  1688. . "'";
  1689. }
  1690. // Was the Null checkbox checked for this field?
  1691. // (if there is a value, we ignore the Null checkbox: this could
  1692. // be possible if Javascript is disabled in the browser)
  1693. if (! empty($multiEditColumnsNull[$key]) && ($currentValue == "''" || $currentValue == '')) {
  1694. $currentValue = 'NULL';
  1695. }
  1696. // The Null checkbox was unchecked for this field
  1697. if (
  1698. empty($currentValue)
  1699. && ! empty($multiEditColumnsNullPrev[$key])
  1700. && ! isset($multiEditColumnsNull[$key])
  1701. ) {
  1702. $currentValue = "''";
  1703. }
  1704. }
  1705. return $currentValue;
  1706. }
  1707. /**
  1708. * Check whether inline edited value can be truncated or not,
  1709. * and add additional parameters for extra_data array if needed
  1710. *
  1711. * @param string $db Database name
  1712. * @param string $table Table name
  1713. * @param string $columnName Column name
  1714. * @param array $extraData Extra data for ajax response
  1715. */
  1716. public function verifyWhetherValueCanBeTruncatedAndAppendExtraData(
  1717. $db,
  1718. $table,
  1719. $columnName,
  1720. array &$extraData
  1721. ): void {
  1722. $extraData['isNeedToRecheck'] = false;
  1723. $sqlForRealValue = 'SELECT ' . Util::backquote($table) . '.'
  1724. . Util::backquote($columnName)
  1725. . ' FROM ' . Util::backquote($db) . '.'
  1726. . Util::backquote($table)
  1727. . ' WHERE ' . $_POST['where_clause'][0];
  1728. $result = $this->dbi->tryQuery($sqlForRealValue);
  1729. $fieldsMeta = $this->dbi->getFieldsMeta($result) ?? [];
  1730. /** @var FieldMetadata $meta */
  1731. $meta = $fieldsMeta[0];
  1732. $row = $this->dbi->fetchRow($result);
  1733. if ($row) {
  1734. $newValue = $row[0];
  1735. if ($meta->isTimeType()) {
  1736. $newValue = Util::addMicroseconds($newValue);
  1737. } elseif ($meta->isBinary()) {
  1738. $newValue = '0x' . bin2hex($newValue);
  1739. }
  1740. $extraData['isNeedToRecheck'] = true;
  1741. $extraData['truncatableFieldValue'] = $newValue;
  1742. }
  1743. $this->dbi->freeResult($result);
  1744. }
  1745. /**
  1746. * Function to get the columns of a table
  1747. *
  1748. * @param string $db current db
  1749. * @param string $table current table
  1750. *
  1751. * @return array
  1752. */
  1753. public function getTableColumns($db, $table)
  1754. {
  1755. $this->dbi->selectDb($db);
  1756. return array_values($this->dbi->getColumns($db, $table, null, true));
  1757. }
  1758. /**
  1759. * Function to determine Insert/Edit rows
  1760. *
  1761. * @param string|null $whereClause where clause
  1762. * @param string $db current database
  1763. * @param string $table current table
  1764. *
  1765. * @return array
  1766. */
  1767. public function determineInsertOrEdit($whereClause, $db, $table): array
  1768. {
  1769. if (isset($_POST['where_clause'])) {
  1770. $whereClause = $_POST['where_clause'];
  1771. }
  1772. if (isset($_SESSION['edit_next'])) {
  1773. $whereClause = $_SESSION['edit_next'];
  1774. unset($_SESSION['edit_next']);
  1775. $afterInsert = 'edit_next';
  1776. }
  1777. if (isset($_POST['ShowFunctionFields'])) {
  1778. $GLOBALS['cfg']['ShowFunctionFields'] = $_POST['ShowFunctionFields'];
  1779. }
  1780. if (isset($_POST['ShowFieldTypesInDataEditView'])) {
  1781. $GLOBALS['cfg']['ShowFieldTypesInDataEditView'] = $_POST['ShowFieldTypesInDataEditView'];
  1782. }
  1783. if (isset($_POST['after_insert'])) {
  1784. $afterInsert = $_POST['after_insert'];
  1785. }
  1786. if (isset($whereClause)) {
  1787. // we are editing
  1788. $insertMode = false;
  1789. $whereClauseArray = $this->getWhereClauseArray($whereClause);
  1790. [$whereClauses, $result, $rows, $foundUniqueKey] = $this->analyzeWhereClauses(
  1791. $whereClauseArray,
  1792. $table,
  1793. $db
  1794. );
  1795. } else {
  1796. // we are inserting
  1797. $insertMode = true;
  1798. $whereClause = null;
  1799. [$result, $rows] = $this->loadFirstRow($table, $db);
  1800. $whereClauses = null;
  1801. $whereClauseArray = [];
  1802. $foundUniqueKey = false;
  1803. }
  1804. // Copying a row - fetched data will be inserted as a new row,
  1805. // therefore the where clause is needless.
  1806. if (isset($_POST['default_action']) && $_POST['default_action'] === 'insert') {
  1807. $whereClause = $whereClauses = null;
  1808. }
  1809. return [
  1810. $insertMode,
  1811. $whereClause,
  1812. $whereClauseArray,
  1813. $whereClauses,
  1814. $result,
  1815. $rows,
  1816. $foundUniqueKey,
  1817. $afterInsert ?? null,
  1818. ];
  1819. }
  1820. /**
  1821. * Function to get comments for the table columns
  1822. *
  1823. * @param string $db current database
  1824. * @param string $table current table
  1825. *
  1826. * @return array comments for columns
  1827. */
  1828. public function getCommentsMap($db, $table)
  1829. {
  1830. $commentsMap = [];
  1831. if ($GLOBALS['cfg']['ShowPropertyComments']) {
  1832. $commentsMap = $this->relation->getComments($db, $table);
  1833. }
  1834. return $commentsMap;
  1835. }
  1836. /**
  1837. * Function to get html for the gis editor div
  1838. *
  1839. * @return string
  1840. */
  1841. public function getHtmlForGisEditor()
  1842. {
  1843. return '<div id="gis_editor"></div><div id="popup_background"></div><br>';
  1844. }
  1845. /**
  1846. * Function to get html for the ignore option in insert mode
  1847. *
  1848. * @param int $rowId row id
  1849. * @param bool $checked ignore option is checked or not
  1850. *
  1851. * @return string
  1852. */
  1853. public function getHtmlForIgnoreOption($rowId, $checked = true)
  1854. {
  1855. return '<input type="checkbox"'
  1856. . ($checked ? ' checked="checked"' : '')
  1857. . ' name="insert_ignore_' . $rowId . '"'
  1858. . ' id="insert_ignore_' . $rowId . '">'
  1859. . '<label for="insert_ignore_' . $rowId . '">'
  1860. . __('Ignore')
  1861. . '</label><br>' . "\n";
  1862. }
  1863. /**
  1864. * Function to get html for the insert edit form header
  1865. *
  1866. * @param bool $hasBlobField whether has blob field
  1867. * @param bool $isUpload whether is upload
  1868. *
  1869. * @return string
  1870. */
  1871. public function getHtmlForInsertEditFormHeader($hasBlobField, $isUpload)
  1872. {
  1873. $template = new Template();
  1874. return $template->render('table/insert/get_html_for_insert_edit_form_header', [
  1875. 'has_blob_field' => $hasBlobField,
  1876. 'is_upload' => $isUpload,
  1877. ]);
  1878. }
  1879. /**
  1880. * Function to get html for each insert/edit column
  1881. *
  1882. * @param array $tableColumns table columns
  1883. * @param int $columnNumber column index in table_columns
  1884. * @param array $commentsMap comments map
  1885. * @param bool $timestampSeen whether timestamp seen
  1886. * @param array $currentResult current result
  1887. * @param string $chgEvtHandler javascript change event handler
  1888. * @param string $jsvkey javascript validation key
  1889. * @param string $vkey validation key
  1890. * @param bool $insertMode whether insert mode
  1891. * @param array $currentRow current row
  1892. * @param int $oRows row offset
  1893. * @param int $tabindex tab index
  1894. * @param int $columnsCnt columns count
  1895. * @param bool $isUpload whether upload
  1896. * @param array $foreigners foreigners
  1897. * @param int $tabindexForValue tab index offset for value
  1898. * @param string $table table
  1899. * @param string $db database
  1900. * @param int $rowId row id
  1901. * @param int $biggestMaxFileSize biggest max file size
  1902. * @param string $defaultCharEditing default char editing mode which is stored in the config.inc.php script
  1903. * @param string $textDir text direction
  1904. * @param array $repopulate the data to be repopulated
  1905. * @param array $columnMime the mime information of column
  1906. * @param string $whereClause the where clause
  1907. *
  1908. * @return string
  1909. */
  1910. private function getHtmlForInsertEditFormColumn(
  1911. array $tableColumns,
  1912. $columnNumber,
  1913. array $commentsMap,
  1914. $timestampSeen,
  1915. $currentResult,
  1916. $chgEvtHandler,
  1917. $jsvkey,
  1918. $vkey,
  1919. $insertMode,
  1920. array $currentRow,
  1921. &$oRows,
  1922. &$tabindex,
  1923. $columnsCnt,
  1924. $isUpload,
  1925. array $foreigners,
  1926. $tabindexForValue,
  1927. $table,
  1928. $db,
  1929. $rowId,
  1930. $biggestMaxFileSize,
  1931. $defaultCharEditing,
  1932. $textDir,
  1933. array $repopulate,
  1934. array $columnMime,
  1935. $whereClause
  1936. ) {
  1937. $column = $tableColumns[$columnNumber];
  1938. $readOnly = false;
  1939. if (! isset($column['processed'])) {
  1940. $column = $this->analyzeTableColumnsArray($column, $commentsMap, $timestampSeen);
  1941. }
  1942. $asIs = false;
  1943. if (! empty($repopulate) && ! empty($currentRow)) {
  1944. $currentRow[$column['Field']] = $repopulate[$column['Field_md5']];
  1945. $asIs = true;
  1946. }
  1947. $extractedColumnspec = Util::extractColumnSpec($column['Type']);
  1948. if ($column['len'] === -1) {
  1949. $column['len'] = $this->dbi->fieldLen($currentResult, $columnNumber);
  1950. // length is unknown for geometry fields,
  1951. // make enough space to edit very simple WKTs
  1952. if ($column['len'] === -1) {
  1953. $column['len'] = 30;
  1954. }
  1955. }
  1956. //Call validation when the form submitted...
  1957. $onChangeClause = $chgEvtHandler
  1958. . "=\"return verificationsAfterFieldChange('"
  1959. . Sanitize::escapeJsString($column['Field_md5']) . "', '"
  1960. . Sanitize::escapeJsString($jsvkey) . "','" . $column['pma_type'] . "')\"";
  1961. // Use an MD5 as an array index to avoid having special characters
  1962. // in the name attribute (see bug #1746964 )
  1963. $columnNameAppendix = $vkey . '[' . $column['Field_md5'] . ']';
  1964. if ($column['Type'] === 'datetime' && $column['Null'] !== 'YES' && ! isset($column['Default']) && $insertMode) {
  1965. $column['Default'] = date('Y-m-d H:i:s', time());
  1966. }
  1967. // Get a list of GIS data types.
  1968. $gisDataTypes = Gis::getDataTypes();
  1969. // Prepares the field value
  1970. $realNullValue = false;
  1971. $specialCharsEncoded = '';
  1972. if (! empty($currentRow)) {
  1973. // (we are editing)
  1974. [
  1975. $realNullValue,
  1976. $specialCharsEncoded,
  1977. $specialChars,
  1978. $data,
  1979. $backupField,
  1980. ] = $this->getSpecialCharsAndBackupFieldForExistingRow(
  1981. $currentRow,
  1982. $column,
  1983. $extractedColumnspec,
  1984. $realNullValue,
  1985. $gisDataTypes,
  1986. $columnNameAppendix,
  1987. $asIs
  1988. );
  1989. } else {
  1990. // (we are inserting)
  1991. // display default values
  1992. $tmp = $column;
  1993. if (isset($repopulate[$column['Field_md5']])) {
  1994. $tmp['Default'] = $repopulate[$column['Field_md5']];
  1995. }
  1996. [
  1997. $realNullValue,
  1998. $data,
  1999. $specialChars,
  2000. $backupField,
  2001. $specialCharsEncoded,
  2002. ] = $this->getSpecialCharsAndBackupFieldForInsertingMode($tmp, $realNullValue);
  2003. unset($tmp);
  2004. }
  2005. $idindex = ($oRows * $columnsCnt) + $columnNumber + 1;
  2006. $tabindex = $idindex;
  2007. // Get a list of data types that are not yet supported.
  2008. $noSupportTypes = Util::unsupportedDatatypes();
  2009. // The function column
  2010. // -------------------
  2011. $foreignData = $this->relation->getForeignData($foreigners, $column['Field'], false, '', '');
  2012. $isColumnBinary = $this->isColumnBinary($column, $isUpload);
  2013. $functionOptions = '';
  2014. if ($GLOBALS['cfg']['ShowFunctionFields']) {
  2015. $functionOptions = Generator::getFunctionsForField($column, $insertMode, $foreignData);
  2016. }
  2017. // nullify code is needed by the js nullify() function to be able to generate calls to nullify() in jQuery
  2018. $nullifyCode = $this->getNullifyCodeForNullColumn($column, $foreigners, $foreignData);
  2019. // The value column (depends on type)
  2020. // ----------------
  2021. // See bug #1667887 for the reason why we don't use the maxlength
  2022. // HTML attribute
  2023. //add data attributes "no of decimals" and "data type"
  2024. $noDecimals = 0;
  2025. $type = current(explode('(', $column['pma_type']));
  2026. if (preg_match('/\(([^()]+)\)/', $column['pma_type'], $match)) {
  2027. $match[0] = trim($match[0], '()');
  2028. $noDecimals = $match[0];
  2029. }
  2030. // Check input transformation of column
  2031. $transformedHtml = '';
  2032. if (! empty($columnMime['input_transformation'])) {
  2033. $file = $columnMime['input_transformation'];
  2034. $includeFile = 'libraries/classes/Plugins/Transformations/' . $file;
  2035. if (is_file(ROOT_PATH . $includeFile)) {
  2036. $className = $this->transformations->getClassName($includeFile);
  2037. if (class_exists($className)) {
  2038. $transformationPlugin = new $className();
  2039. $transformationOptions = $this->transformations->getOptions(
  2040. $columnMime['input_transformation_options']
  2041. );
  2042. $urlParams = [
  2043. 'db' => $db,
  2044. 'table' => $table,
  2045. 'transform_key' => $column['Field'],
  2046. 'where_clause_sign' => Core::signSqlQuery($whereClause),
  2047. 'where_clause' => $whereClause,
  2048. ];
  2049. $transformationOptions['wrapper_link'] = Url::getCommon($urlParams);
  2050. $transformationOptions['wrapper_params'] = $urlParams;
  2051. $currentValue = '';
  2052. if (isset($currentRow[$column['Field']])) {
  2053. $currentValue = $currentRow[$column['Field']];
  2054. }
  2055. if (method_exists($transformationPlugin, 'getInputHtml')) {
  2056. $transformedHtml = $transformationPlugin->getInputHtml(
  2057. $column,
  2058. $rowId,
  2059. $columnNameAppendix,
  2060. $transformationOptions,
  2061. $currentValue,
  2062. $textDir,
  2063. $tabindex,
  2064. $tabindexForValue,
  2065. $idindex
  2066. );
  2067. }
  2068. if (method_exists($transformationPlugin, 'getScripts')) {
  2069. $GLOBALS['plugin_scripts'] = array_merge(
  2070. $GLOBALS['plugin_scripts'],
  2071. $transformationPlugin->getScripts()
  2072. );
  2073. }
  2074. }
  2075. }
  2076. }
  2077. $columnValue = '';
  2078. $foreignDropdown = '';
  2079. $dataType = '';
  2080. $textAreaRows = $GLOBALS['cfg']['TextareaRows'];
  2081. $textareaCols = $GLOBALS['cfg']['TextareaCols'];
  2082. $maxlength = '';
  2083. $enumSelectedValue = '';
  2084. $columnSetValues = [];
  2085. $setSelectSize = 0;
  2086. $isColumnProtectedBlob = false;
  2087. $blobValue = '';
  2088. $blobValueUnit = '';
  2089. $maxUploadSize = 0;
  2090. $selectOptionForUpload = '';
  2091. $inputFieldHtml = '';
  2092. if (empty($transformedHtml)) {
  2093. if (is_array($foreignData['disp_row'])) {
  2094. $foreignDropdown = $this->relation->foreignDropdown(
  2095. $foreignData['disp_row'],
  2096. $foreignData['foreign_field'],
  2097. $foreignData['foreign_display'],
  2098. $data,
  2099. $GLOBALS['cfg']['ForeignKeyMaxLimit']
  2100. );
  2101. }
  2102. $dataType = $this->dbi->types->getTypeClass($column['True_Type']);
  2103. if ($column['is_char']) {
  2104. $textAreaRows = max($GLOBALS['cfg']['CharTextareaRows'], 7);
  2105. $textareaCols = $GLOBALS['cfg']['CharTextareaCols'];
  2106. $extractedColumnspec = Util::extractColumnSpec($column['Type']);
  2107. $maxlength = $extractedColumnspec['spec_in_brackets'];
  2108. } elseif ($GLOBALS['cfg']['LongtextDoubleTextarea'] && mb_strstr($column['pma_type'], 'longtext')) {
  2109. $textAreaRows = $GLOBALS['cfg']['TextareaRows'] * 2;
  2110. $textareaCols = $GLOBALS['cfg']['TextareaCols'] * 2;
  2111. }
  2112. if ($column['pma_type'] === 'enum') {
  2113. if (! isset($column['values'])) {
  2114. $column['values'] = $this->getColumnEnumValues($column, $extractedColumnspec);
  2115. }
  2116. foreach ($column['values'] as $enumValue) {
  2117. if (
  2118. $data == $enumValue['plain'] || ($data == ''
  2119. && (! isset($_POST['where_clause']) || $column['Null'] !== 'YES')
  2120. && isset($column['Default']) && $enumValue['plain'] == $column['Default'])
  2121. ) {
  2122. $enumSelectedValue = $enumValue['plain'];
  2123. break;
  2124. }
  2125. }
  2126. } elseif ($column['pma_type'] === 'set') {
  2127. [$columnSetValues, $setSelectSize] = $this->getColumnSetValueAndSelectSize(
  2128. $column,
  2129. $extractedColumnspec
  2130. );
  2131. } elseif ($column['is_binary'] || $column['is_blob']) {
  2132. $isColumnProtectedBlob = ($GLOBALS['cfg']['ProtectBinary'] === 'blob' && $column['is_blob'])
  2133. || ($GLOBALS['cfg']['ProtectBinary'] === 'all')
  2134. || ($GLOBALS['cfg']['ProtectBinary'] === 'noblob' && ! $column['is_blob']);
  2135. if ($isColumnProtectedBlob && isset($data)) {
  2136. $blobSize = Util::formatByteDown(mb_strlen(stripslashes($data)), 3, 1);
  2137. if ($blobSize !== null) {
  2138. [$blobValue, $blobValueUnit] = $blobSize;
  2139. }
  2140. }
  2141. if ($isUpload && $column['is_blob']) {
  2142. [$maxUploadSize] = $this->getMaxUploadSize($column, $biggestMaxFileSize);
  2143. }
  2144. if (! empty($GLOBALS['cfg']['UploadDir'])) {
  2145. $selectOptionForUpload = $this->getSelectOptionForUpload($vkey, $column);
  2146. }
  2147. if (
  2148. ! $isColumnProtectedBlob
  2149. && ! ($column['is_blob'] || ($column['len'] > $GLOBALS['cfg']['LimitChars']))
  2150. ) {
  2151. $inputFieldHtml = $this->getHtmlInput(
  2152. $column,
  2153. $columnNameAppendix,
  2154. $specialChars,
  2155. min(max($column['len'], 4), $GLOBALS['cfg']['LimitChars']),
  2156. $onChangeClause,
  2157. $tabindex,
  2158. $tabindexForValue,
  2159. $idindex,
  2160. 'HEX',
  2161. $readOnly
  2162. );
  2163. }
  2164. } else {
  2165. $columnValue = $this->getValueColumnForOtherDatatypes(
  2166. $column,
  2167. $defaultCharEditing,
  2168. $backupField,
  2169. $columnNameAppendix,
  2170. $onChangeClause,
  2171. $tabindex,
  2172. $specialChars,
  2173. $tabindexForValue,
  2174. $idindex,
  2175. $textDir,
  2176. $specialCharsEncoded,
  2177. $data,
  2178. $extractedColumnspec,
  2179. $readOnly
  2180. );
  2181. }
  2182. }
  2183. return $this->template->render('table/insert/column_row', [
  2184. 'db' => $db,
  2185. 'table' => $table,
  2186. 'column' => $column,
  2187. 'row_id' => $rowId,
  2188. 'show_field_types_in_data_edit_view' => $GLOBALS['cfg']['ShowFieldTypesInDataEditView'],
  2189. 'show_function_fields' => $GLOBALS['cfg']['ShowFunctionFields'],
  2190. 'is_column_binary' => $isColumnBinary,
  2191. 'function_options' => $functionOptions,
  2192. 'read_only' => $readOnly,
  2193. 'nullify_code' => $nullifyCode,
  2194. 'real_null_value' => $realNullValue,
  2195. 'id_index' => $idindex,
  2196. 'type' => $type,
  2197. 'decimals' => $noDecimals,
  2198. 'special_chars' => $specialChars,
  2199. 'transformed_value' => $transformedHtml,
  2200. 'value' => $columnValue,
  2201. 'is_value_foreign_link' => $foreignData['foreign_link'] === true,
  2202. 'backup_field' => $backupField,
  2203. 'data' => $data,
  2204. 'gis_data_types' => $gisDataTypes,
  2205. 'foreign_dropdown' => $foreignDropdown,
  2206. 'data_type' => $dataType,
  2207. 'textarea_cols' => $textareaCols,
  2208. 'textarea_rows' => $textAreaRows,
  2209. 'text_dir' => $textDir,
  2210. 'max_length' => $maxlength,
  2211. 'longtext_double_textarea' => $GLOBALS['cfg']['LongtextDoubleTextarea'],
  2212. 'enum_selected_value' => $enumSelectedValue,
  2213. 'set_values' => $columnSetValues,
  2214. 'set_select_size' => $setSelectSize,
  2215. 'is_column_protected_blob' => $isColumnProtectedBlob,
  2216. 'blob_value' => $blobValue,
  2217. 'blob_value_unit' => $blobValueUnit,
  2218. 'is_upload' => $isUpload,
  2219. 'max_upload_size' => $maxUploadSize,
  2220. 'select_option_for_upload' => $selectOptionForUpload,
  2221. 'limit_chars' => $GLOBALS['cfg']['LimitChars'],
  2222. 'input_field_html' => $inputFieldHtml,
  2223. ]);
  2224. }
  2225. private function isColumnBinary(array $column, bool $isUpload): bool
  2226. {
  2227. global $cfg;
  2228. if (! $cfg['ShowFunctionFields']) {
  2229. return false;
  2230. }
  2231. return ($cfg['ProtectBinary'] === 'blob' && $column['is_blob'] && ! $isUpload)
  2232. || ($cfg['ProtectBinary'] === 'all' && $column['is_binary'])
  2233. || ($cfg['ProtectBinary'] === 'noblob' && $column['is_binary']);
  2234. }
  2235. /**
  2236. * Function to get html for each insert/edit row
  2237. *
  2238. * @param array $urlParams url parameters
  2239. * @param array $tableColumns table columns
  2240. * @param array $commentsMap comments map
  2241. * @param bool $timestampSeen whether timestamp seen
  2242. * @param array $currentResult current result
  2243. * @param string $chgEvtHandler javascript change event handler
  2244. * @param string $jsvkey javascript validation key
  2245. * @param string $vkey validation key
  2246. * @param bool $insertMode whether insert mode
  2247. * @param array $currentRow current row
  2248. * @param int $oRows row offset
  2249. * @param int $tabindex tab index
  2250. * @param int $columnsCnt columns count
  2251. * @param bool $isUpload whether upload
  2252. * @param array $foreigners foreigners
  2253. * @param int $tabindexForValue tab index offset for value
  2254. * @param string $table table
  2255. * @param string $db database
  2256. * @param int $rowId row id
  2257. * @param int $biggestMaxFileSize biggest max file size
  2258. * @param string $textDir text direction
  2259. * @param array $repopulate the data to be repopulated
  2260. * @param array $whereClauseArray the array of where clauses
  2261. *
  2262. * @return string
  2263. */
  2264. public function getHtmlForInsertEditRow(
  2265. array $urlParams,
  2266. array $tableColumns,
  2267. array $commentsMap,
  2268. $timestampSeen,
  2269. $currentResult,
  2270. $chgEvtHandler,
  2271. $jsvkey,
  2272. $vkey,
  2273. $insertMode,
  2274. array $currentRow,
  2275. &$oRows,
  2276. &$tabindex,
  2277. $columnsCnt,
  2278. $isUpload,
  2279. array $foreigners,
  2280. $tabindexForValue,
  2281. $table,
  2282. $db,
  2283. $rowId,
  2284. $biggestMaxFileSize,
  2285. $textDir,
  2286. array $repopulate,
  2287. array $whereClauseArray
  2288. ) {
  2289. $htmlOutput = $this->getHeadAndFootOfInsertRowTable($urlParams)
  2290. . '<tbody>';
  2291. //store the default value for CharEditing
  2292. $defaultCharEditing = $GLOBALS['cfg']['CharEditing'];
  2293. $mimeMap = $this->transformations->getMime($db, $table);
  2294. $whereClause = '';
  2295. if (isset($whereClauseArray[$rowId])) {
  2296. $whereClause = $whereClauseArray[$rowId];
  2297. }
  2298. for ($columnNumber = 0; $columnNumber < $columnsCnt; $columnNumber++) {
  2299. $tableColumn = $tableColumns[$columnNumber];
  2300. $columnMime = [];
  2301. if (isset($mimeMap[$tableColumn['Field']])) {
  2302. $columnMime = $mimeMap[$tableColumn['Field']];
  2303. }
  2304. $virtual = [
  2305. 'VIRTUAL',
  2306. 'PERSISTENT',
  2307. 'VIRTUAL GENERATED',
  2308. 'STORED GENERATED',
  2309. ];
  2310. if (in_array($tableColumn['Extra'], $virtual)) {
  2311. continue;
  2312. }
  2313. $htmlOutput .= $this->getHtmlForInsertEditFormColumn(
  2314. $tableColumns,
  2315. $columnNumber,
  2316. $commentsMap,
  2317. $timestampSeen,
  2318. $currentResult,
  2319. $chgEvtHandler,
  2320. $jsvkey,
  2321. $vkey,
  2322. $insertMode,
  2323. $currentRow,
  2324. $oRows,
  2325. $tabindex,
  2326. $columnsCnt,
  2327. $isUpload,
  2328. $foreigners,
  2329. $tabindexForValue,
  2330. $table,
  2331. $db,
  2332. $rowId,
  2333. $biggestMaxFileSize,
  2334. $defaultCharEditing,
  2335. $textDir,
  2336. $repopulate,
  2337. $columnMime,
  2338. $whereClause
  2339. );
  2340. }
  2341. $oRows++;
  2342. return $htmlOutput . ' </tbody>'
  2343. . '</table></div><br>'
  2344. . '<div class="clearfloat"></div>';
  2345. }
  2346. }