PageRenderTime 25ms CodeModel.GetById 22ms RepoModel.GetById 0ms app.codeStats 0ms

/libraries/classes/Plugins/Import/ImportCsv.php

http://github.com/phpmyadmin/phpmyadmin
PHP | 890 lines | 633 code | 126 blank | 131 comment | 203 complexity | 75f0252ea96f282cfa5c57cb8fd02760 MD5 | raw file
Possible License(s): GPL-2.0, MIT, LGPL-3.0
  1. <?php
  2. /**
  3. * CSV import plugin for phpMyAdmin
  4. *
  5. * @todo add an option for handling NULL values
  6. */
  7. declare(strict_types=1);
  8. namespace PhpMyAdmin\Plugins\Import;
  9. use PhpMyAdmin\File;
  10. use PhpMyAdmin\Html\Generator;
  11. use PhpMyAdmin\Message;
  12. use PhpMyAdmin\Properties\Options\Groups\OptionsPropertyRootGroup;
  13. use PhpMyAdmin\Properties\Options\Items\BoolPropertyItem;
  14. use PhpMyAdmin\Properties\Options\Items\NumberPropertyItem;
  15. use PhpMyAdmin\Properties\Options\Items\TextPropertyItem;
  16. use PhpMyAdmin\Properties\Plugins\ImportPluginProperties;
  17. use PhpMyAdmin\Util;
  18. use function __;
  19. use function array_shift;
  20. use function array_splice;
  21. use function basename;
  22. use function count;
  23. use function is_array;
  24. use function mb_strlen;
  25. use function mb_strtolower;
  26. use function mb_substr;
  27. use function preg_grep;
  28. use function preg_replace;
  29. use function preg_split;
  30. use function rtrim;
  31. use function str_contains;
  32. use function strlen;
  33. use function strtr;
  34. use function trim;
  35. /**
  36. * Handles the import for the CSV format
  37. */
  38. class ImportCsv extends AbstractImportCsv
  39. {
  40. /**
  41. * Whether to analyze tables
  42. *
  43. * @var bool
  44. */
  45. private $analyze;
  46. /**
  47. * @psalm-return non-empty-lowercase-string
  48. */
  49. public function getName(): string
  50. {
  51. return 'csv';
  52. }
  53. protected function setProperties(): ImportPluginProperties
  54. {
  55. $this->setAnalyze(false);
  56. if ($GLOBALS['plugin_param'] !== 'table') {
  57. $this->setAnalyze(true);
  58. }
  59. $importPluginProperties = new ImportPluginProperties();
  60. $importPluginProperties->setText('CSV');
  61. $importPluginProperties->setExtension('csv');
  62. $importPluginProperties->setOptionsText(__('Options'));
  63. // create the root group that will be the options field for
  64. // $importPluginProperties
  65. // this will be shown as "Format specific options"
  66. $importSpecificOptions = new OptionsPropertyRootGroup('Format Specific Options');
  67. $generalOptions = $this->getGeneralOptions();
  68. if ($GLOBALS['plugin_param'] !== 'table') {
  69. $leaf = new TextPropertyItem(
  70. 'new_tbl_name',
  71. __(
  72. 'Name of the new table (optional):'
  73. )
  74. );
  75. $generalOptions->addProperty($leaf);
  76. if ($GLOBALS['plugin_param'] === 'server') {
  77. $leaf = new TextPropertyItem(
  78. 'new_db_name',
  79. __(
  80. 'Name of the new database (optional):'
  81. )
  82. );
  83. $generalOptions->addProperty($leaf);
  84. }
  85. $leaf = new NumberPropertyItem(
  86. 'partial_import',
  87. __(
  88. 'Import these many number of rows (optional):'
  89. )
  90. );
  91. $generalOptions->addProperty($leaf);
  92. $leaf = new BoolPropertyItem(
  93. 'col_names',
  94. __(
  95. 'The first line of the file contains the table column names'
  96. . ' <i>(if this is unchecked, the first line will become part'
  97. . ' of the data)</i>'
  98. )
  99. );
  100. $generalOptions->addProperty($leaf);
  101. } else {
  102. $leaf = new NumberPropertyItem(
  103. 'partial_import',
  104. __(
  105. 'Import these many number of rows (optional):'
  106. )
  107. );
  108. $generalOptions->addProperty($leaf);
  109. $hint = new Message(
  110. __(
  111. 'If the data in each row of the file is not'
  112. . ' in the same order as in the database, list the corresponding'
  113. . ' column names here. Column names must be separated by commas'
  114. . ' and not enclosed in quotations.'
  115. )
  116. );
  117. $leaf = new TextPropertyItem(
  118. 'columns',
  119. __('Column names:') . ' ' . Generator::showHint($hint->getMessage())
  120. );
  121. $generalOptions->addProperty($leaf);
  122. }
  123. $leaf = new BoolPropertyItem(
  124. 'ignore',
  125. __('Do not abort on INSERT error')
  126. );
  127. $generalOptions->addProperty($leaf);
  128. // add the main group to the root group
  129. $importSpecificOptions->addProperty($generalOptions);
  130. // set the options for the import plugin property item
  131. $importPluginProperties->setOptions($importSpecificOptions);
  132. return $importPluginProperties;
  133. }
  134. /**
  135. * Handles the whole import logic
  136. *
  137. * @param array $sql_data 2-element array with sql data
  138. */
  139. public function doImport(?File $importHandle = null, array &$sql_data = []): void
  140. {
  141. global $error, $message, $dbi;
  142. global $db, $table, $csv_terminated, $csv_enclosed, $csv_escaped,
  143. $csv_new_line, $csv_columns, $errorUrl;
  144. // $csv_replace and $csv_ignore should have been here,
  145. // but we use directly from $_POST
  146. global $timeout_passed, $finished;
  147. $replacements = [
  148. '\\n' => "\n",
  149. '\\t' => "\t",
  150. '\\r' => "\r",
  151. ];
  152. $csv_terminated = strtr($csv_terminated, $replacements);
  153. $csv_enclosed = strtr($csv_enclosed, $replacements);
  154. $csv_escaped = strtr($csv_escaped, $replacements);
  155. $csv_new_line = strtr($csv_new_line, $replacements);
  156. [$error, $message] = $this->buildErrorsForParams(
  157. $csv_terminated,
  158. $csv_enclosed,
  159. $csv_escaped,
  160. $csv_new_line,
  161. (string) $errorUrl
  162. );
  163. [$sql_template, $required_fields, $fields] = $this->getSqlTemplateAndRequiredFields($db, $table, $csv_columns);
  164. // Defaults for parser
  165. $i = 0;
  166. $len = 0;
  167. $lastlen = null;
  168. $line = 1;
  169. $lasti = -1;
  170. $values = [];
  171. $csv_finish = false;
  172. $max_lines = 0; // defaults to 0 (get all the lines)
  173. /**
  174. * If we get a negative value, probably someone changed min value
  175. * attribute in DOM or there is an integer overflow, whatever be
  176. * the case, get all the lines.
  177. */
  178. if (isset($_REQUEST['csv_partial_import']) && $_REQUEST['csv_partial_import'] > 0) {
  179. $max_lines = $_REQUEST['csv_partial_import'];
  180. }
  181. $max_lines_constraint = $max_lines + 1;
  182. // if the first row has to be counted as column names, include one more row in the max lines
  183. if (isset($_REQUEST['csv_col_names'])) {
  184. $max_lines_constraint++;
  185. }
  186. $tempRow = [];
  187. $rows = [];
  188. $col_names = [];
  189. $tables = [];
  190. $buffer = '';
  191. $col_count = 0;
  192. $max_cols = 0;
  193. $csv_terminated_len = mb_strlen($csv_terminated);
  194. while (! ($finished && $i >= $len) && ! $error && ! $timeout_passed) {
  195. $data = $this->import->getNextChunk($importHandle);
  196. if ($data === false) {
  197. // subtract data we didn't handle yet and stop processing
  198. $GLOBALS['offset'] -= strlen($buffer);
  199. break;
  200. }
  201. if ($data !== true) {
  202. // Append new data to buffer
  203. $buffer .= $data;
  204. unset($data);
  205. // Force a trailing new line at EOF to prevent parsing problems
  206. if ($finished && $buffer) {
  207. $finalch = mb_substr($buffer, -1);
  208. if ($csv_new_line === 'auto' && $finalch != "\r" && $finalch != "\n") {
  209. $buffer .= "\n";
  210. } elseif ($csv_new_line !== 'auto' && $finalch != $csv_new_line) {
  211. $buffer .= $csv_new_line;
  212. }
  213. }
  214. // Do not parse string when we're not at the end
  215. // and don't have new line inside
  216. if (
  217. ($csv_new_line === 'auto'
  218. && ! str_contains($buffer, "\r")
  219. && ! str_contains($buffer, "\n"))
  220. || ($csv_new_line !== 'auto'
  221. && ! str_contains($buffer, $csv_new_line))
  222. ) {
  223. continue;
  224. }
  225. }
  226. // Current length of our buffer
  227. $len = mb_strlen($buffer);
  228. // Currently parsed char
  229. $ch = mb_substr($buffer, $i, 1);
  230. if ($csv_terminated_len > 1 && $ch == $csv_terminated[0]) {
  231. $ch = $this->readCsvTerminatedString($buffer, $ch, $i, $csv_terminated_len);
  232. $i += $csv_terminated_len - 1;
  233. }
  234. while ($i < $len) {
  235. // Deadlock protection
  236. if ($lasti == $i && $lastlen == $len) {
  237. $message = Message::error(
  238. __('Invalid format of CSV input on line %d.')
  239. );
  240. $message->addParam($line);
  241. $error = true;
  242. break;
  243. }
  244. $lasti = $i;
  245. $lastlen = $len;
  246. // This can happen with auto EOL and \r at the end of buffer
  247. if (! $csv_finish) {
  248. // Grab empty field
  249. if ($ch == $csv_terminated) {
  250. if ($i == $len - 1) {
  251. break;
  252. }
  253. $values[] = '';
  254. $i++;
  255. $ch = mb_substr($buffer, $i, 1);
  256. if ($csv_terminated_len > 1 && $ch == $csv_terminated[0]) {
  257. $ch = $this->readCsvTerminatedString($buffer, $ch, $i, $csv_terminated_len);
  258. $i += $csv_terminated_len - 1;
  259. }
  260. continue;
  261. }
  262. // Grab one field
  263. $fallbacki = $i;
  264. if ($ch == $csv_enclosed) {
  265. if ($i == $len - 1) {
  266. break;
  267. }
  268. $need_end = true;
  269. $i++;
  270. $ch = mb_substr($buffer, $i, 1);
  271. if ($csv_terminated_len > 1 && $ch == $csv_terminated[0]) {
  272. $ch = $this->readCsvTerminatedString($buffer, $ch, $i, $csv_terminated_len);
  273. $i += $csv_terminated_len - 1;
  274. }
  275. } else {
  276. $need_end = false;
  277. }
  278. $fail = false;
  279. $value = '';
  280. while (
  281. ($need_end
  282. && ($ch != $csv_enclosed
  283. || $csv_enclosed == $csv_escaped))
  284. || (! $need_end
  285. && ! ($ch == $csv_terminated
  286. || $ch == $csv_new_line
  287. || ($csv_new_line === 'auto'
  288. && ($ch == "\r" || $ch == "\n"))))
  289. ) {
  290. if ($ch == $csv_escaped) {
  291. if ($i == $len - 1) {
  292. $fail = true;
  293. break;
  294. }
  295. $i++;
  296. $ch = mb_substr($buffer, $i, 1);
  297. if ($csv_terminated_len > 1 && $ch == $csv_terminated[0]) {
  298. $ch = $this->readCsvTerminatedString($buffer, $ch, $i, $csv_terminated_len);
  299. $i += $csv_terminated_len - 1;
  300. }
  301. if (
  302. $csv_enclosed == $csv_escaped
  303. && ($ch == $csv_terminated
  304. || $ch == $csv_new_line
  305. || ($csv_new_line === 'auto'
  306. && ($ch == "\r" || $ch == "\n")))
  307. ) {
  308. break;
  309. }
  310. }
  311. $value .= $ch;
  312. if ($i == $len - 1) {
  313. if (! $finished) {
  314. $fail = true;
  315. }
  316. break;
  317. }
  318. $i++;
  319. $ch = mb_substr($buffer, $i, 1);
  320. if ($csv_terminated_len <= 1 || $ch != $csv_terminated[0]) {
  321. continue;
  322. }
  323. $ch = $this->readCsvTerminatedString($buffer, $ch, $i, $csv_terminated_len);
  324. $i += $csv_terminated_len - 1;
  325. }
  326. // unquoted NULL string
  327. if ($need_end === false && $value === 'NULL') {
  328. $value = null;
  329. }
  330. if ($fail) {
  331. $i = $fallbacki;
  332. $ch = mb_substr($buffer, $i, 1);
  333. if ($csv_terminated_len > 1 && $ch == $csv_terminated[0]) {
  334. $i += $csv_terminated_len - 1;
  335. }
  336. break;
  337. }
  338. // Need to strip trailing enclosing char?
  339. if ($need_end && $ch == $csv_enclosed) {
  340. if ($finished && $i == $len - 1) {
  341. $ch = null;
  342. } elseif ($i == $len - 1) {
  343. $i = $fallbacki;
  344. $ch = mb_substr($buffer, $i, 1);
  345. if ($csv_terminated_len > 1 && $ch == $csv_terminated[0]) {
  346. $i += $csv_terminated_len - 1;
  347. }
  348. break;
  349. } else {
  350. $i++;
  351. $ch = mb_substr($buffer, $i, 1);
  352. if ($csv_terminated_len > 1 && $ch == $csv_terminated[0]) {
  353. $ch = $this->readCsvTerminatedString($buffer, $ch, $i, $csv_terminated_len);
  354. $i += $csv_terminated_len - 1;
  355. }
  356. }
  357. }
  358. // Are we at the end?
  359. if (
  360. $ch == $csv_new_line
  361. || ($csv_new_line === 'auto' && ($ch == "\r" || $ch == "\n"))
  362. || ($finished && $i == $len - 1)
  363. ) {
  364. $csv_finish = true;
  365. }
  366. // Go to next char
  367. if ($ch == $csv_terminated) {
  368. if ($i == $len - 1) {
  369. $i = $fallbacki;
  370. $ch = mb_substr($buffer, $i, 1);
  371. if ($csv_terminated_len > 1 && $ch == $csv_terminated[0]) {
  372. $i += $csv_terminated_len - 1;
  373. }
  374. break;
  375. }
  376. $i++;
  377. $ch = mb_substr($buffer, $i, 1);
  378. if ($csv_terminated_len > 1 && $ch == $csv_terminated[0]) {
  379. $ch = $this->readCsvTerminatedString($buffer, $ch, $i, $csv_terminated_len);
  380. $i += $csv_terminated_len - 1;
  381. }
  382. }
  383. // If everything went okay, store value
  384. $values[] = $value;
  385. }
  386. // End of line
  387. if (
  388. ! $csv_finish
  389. && $ch != $csv_new_line
  390. && ($csv_new_line !== 'auto' || ($ch != "\r" && $ch != "\n"))
  391. ) {
  392. continue;
  393. }
  394. if ($csv_new_line === 'auto' && $ch == "\r") { // Handle "\r\n"
  395. if ($i >= ($len - 2) && ! $finished) {
  396. break; // We need more data to decide new line
  397. }
  398. if (mb_substr($buffer, $i + 1, 1) == "\n") {
  399. $i++;
  400. }
  401. }
  402. // We didn't parse value till the end of line, so there was
  403. // empty one
  404. if (! $csv_finish) {
  405. $values[] = '';
  406. }
  407. if ($this->getAnalyze()) {
  408. foreach ($values as $val) {
  409. $tempRow[] = $val;
  410. ++$col_count;
  411. }
  412. if ($col_count > $max_cols) {
  413. $max_cols = $col_count;
  414. }
  415. $col_count = 0;
  416. $rows[] = $tempRow;
  417. $tempRow = [];
  418. } else {
  419. // Do we have correct count of values?
  420. if (count($values) != $required_fields) {
  421. // Hack for excel
  422. if ($values[count($values) - 1] !== ';') {
  423. $message = Message::error(
  424. __(
  425. 'Invalid column count in CSV input on line %d.'
  426. )
  427. );
  428. $message->addParam($line);
  429. $error = true;
  430. break;
  431. }
  432. unset($values[count($values) - 1]);
  433. }
  434. $first = true;
  435. $sql = $sql_template;
  436. foreach ($values as $key => $val) {
  437. if (! $first) {
  438. $sql .= ', ';
  439. }
  440. if ($val === null) {
  441. $sql .= 'NULL';
  442. } else {
  443. $sql .= '\''
  444. . $dbi->escapeString($val)
  445. . '\'';
  446. }
  447. $first = false;
  448. }
  449. $sql .= ')';
  450. if (isset($_POST['csv_replace'])) {
  451. $sql .= ' ON DUPLICATE KEY UPDATE ';
  452. foreach ($fields as $field) {
  453. $fieldName = Util::backquote($field['Field']);
  454. $sql .= $fieldName . ' = VALUES(' . $fieldName
  455. . '), ';
  456. }
  457. $sql = rtrim($sql, ', ');
  458. }
  459. /**
  460. * @todo maybe we could add original line to verbose
  461. * SQL in comment
  462. */
  463. $this->import->runQuery($sql, $sql, $sql_data);
  464. }
  465. $line++;
  466. $csv_finish = false;
  467. $values = [];
  468. $buffer = mb_substr($buffer, $i + 1);
  469. $len = mb_strlen($buffer);
  470. $i = 0;
  471. $lasti = -1;
  472. $ch = mb_substr($buffer, 0, 1);
  473. if ($max_lines > 0 && $line == $max_lines_constraint) {
  474. $finished = 1;
  475. break;
  476. }
  477. }
  478. if ($max_lines > 0 && $line == $max_lines_constraint) {
  479. $finished = 1;
  480. break;
  481. }
  482. }
  483. if ($this->getAnalyze()) {
  484. /* Fill out all rows */
  485. $num_rows = count($rows);
  486. for ($i = 0; $i < $num_rows; ++$i) {
  487. for ($j = count($rows[$i]); $j < $max_cols; ++$j) {
  488. $rows[$i][] = 'NULL';
  489. }
  490. }
  491. $col_names = $this->getColumnNames($col_names, $max_cols, $rows);
  492. /* Remove the first row if it contains the column names */
  493. if (isset($_REQUEST['csv_col_names'])) {
  494. array_shift($rows);
  495. }
  496. $tbl_name = $this->getTableNameFromImport((string) $db);
  497. $tables[] = [
  498. $tbl_name,
  499. $col_names,
  500. $rows,
  501. ];
  502. /* Obtain the best-fit MySQL types for each column */
  503. $analyses = [];
  504. $analyses[] = $this->import->analyzeTable($tables[0]);
  505. /**
  506. * string $db_name (no backquotes)
  507. *
  508. * array $table = array(table_name, array() column_names, array()() rows)
  509. * array $tables = array of "$table"s
  510. *
  511. * array $analysis = array(array() column_types, array() column_sizes)
  512. * array $analyses = array of "$analysis"s
  513. *
  514. * array $create = array of SQL strings
  515. *
  516. * array $options = an associative array of options
  517. */
  518. /* Set database name to the currently selected one, if applicable,
  519. * Otherwise, check if user provided the database name in the request,
  520. * if not, set the default name
  521. */
  522. if (isset($_REQUEST['csv_new_db_name']) && strlen($_REQUEST['csv_new_db_name']) > 0) {
  523. $newDb = $_REQUEST['csv_new_db_name'];
  524. } else {
  525. $result = $dbi->fetchResult('SHOW DATABASES');
  526. if (! is_array($result)) {
  527. $result = [];
  528. }
  529. $newDb = 'CSV_DB ' . (count($result) + 1);
  530. }
  531. [$db_name, $options] = $this->getDbnameAndOptions($db, $newDb);
  532. /* Non-applicable parameters */
  533. $create = null;
  534. /* Created and execute necessary SQL statements from data */
  535. $this->import->buildSql($db_name, $tables, $analyses, $create, $options, $sql_data);
  536. unset($tables, $analyses);
  537. }
  538. // Commit any possible data in buffers
  539. $this->import->runQuery('', '', $sql_data);
  540. if (count($values) == 0 || $error !== false) {
  541. return;
  542. }
  543. $message = Message::error(
  544. __('Invalid format of CSV input on line %d.')
  545. );
  546. $message->addParam($line);
  547. $error = true;
  548. }
  549. private function buildErrorsForParams(
  550. string $csvTerminated,
  551. string $csvEnclosed,
  552. string $csvEscaped,
  553. string $csvNewLine,
  554. string $errUrl
  555. ): array {
  556. global $error, $message;
  557. $param_error = false;
  558. if (strlen($csvTerminated) === 0) {
  559. $message = Message::error(
  560. __('Invalid parameter for CSV import: %s')
  561. );
  562. $message->addParam(__('Columns terminated with'));
  563. $error = true;
  564. $param_error = true;
  565. // The default dialog of MS Excel when generating a CSV produces a
  566. // semi-colon-separated file with no chance of specifying the
  567. // enclosing character. Thus, users who want to import this file
  568. // tend to remove the enclosing character on the Import dialog.
  569. // I could not find a test case where having no enclosing characters
  570. // confuses this script.
  571. // But the parser won't work correctly with strings so we allow just
  572. // one character.
  573. } elseif (mb_strlen($csvEnclosed) > 1) {
  574. $message = Message::error(
  575. __('Invalid parameter for CSV import: %s')
  576. );
  577. $message->addParam(__('Columns enclosed with'));
  578. $error = true;
  579. $param_error = true;
  580. // I could not find a test case where having no escaping characters
  581. // confuses this script.
  582. // But the parser won't work correctly with strings so we allow just
  583. // one character.
  584. } elseif (mb_strlen($csvEscaped) > 1) {
  585. $message = Message::error(
  586. __('Invalid parameter for CSV import: %s')
  587. );
  588. $message->addParam(__('Columns escaped with'));
  589. $error = true;
  590. $param_error = true;
  591. } elseif (mb_strlen($csvNewLine) != 1 && $csvNewLine !== 'auto') {
  592. $message = Message::error(
  593. __('Invalid parameter for CSV import: %s')
  594. );
  595. $message->addParam(__('Lines terminated with'));
  596. $error = true;
  597. $param_error = true;
  598. }
  599. // If there is an error in the parameters entered,
  600. // indicate that immediately.
  601. if ($param_error) {
  602. Generator::mysqlDie(
  603. $message->getMessage(),
  604. '',
  605. false,
  606. $errUrl
  607. );
  608. }
  609. return [$error, $message];
  610. }
  611. private function getTableNameFromImport(string $databaseName): string
  612. {
  613. global $import_file_name, $dbi;
  614. $importFileName = basename($import_file_name, '.csv');
  615. $importFileName = mb_strtolower($importFileName);
  616. $importFileName = (string) preg_replace('/[^a-zA-Z0-9_]/', '_', $importFileName);
  617. // get new table name, if user didn't provide one, set the default name
  618. if (isset($_REQUEST['csv_new_tbl_name']) && strlen($_REQUEST['csv_new_tbl_name']) > 0) {
  619. return $_REQUEST['csv_new_tbl_name'];
  620. }
  621. if (mb_strlen($databaseName)) {
  622. $result = $dbi->fetchResult('SHOW TABLES');
  623. // logic to get table name from filename
  624. // if no table then use filename as table name
  625. if (count($result) === 0) {
  626. return $importFileName;
  627. }
  628. // check to see if {filename} as table exist
  629. $nameArray = preg_grep('/' . $importFileName . '/isU', $result);
  630. // if no use filename as table name
  631. if ($nameArray === false || count($nameArray) === 0) {
  632. return $importFileName;
  633. }
  634. // check if {filename}_ as table exist
  635. $nameArray = preg_grep('/' . $importFileName . '_/isU', $result);
  636. if ($nameArray === false) {
  637. return $importFileName;
  638. }
  639. return $importFileName . '_' . (count($nameArray) + 1);
  640. }
  641. return $importFileName;
  642. }
  643. private function getColumnNames(array $columnNames, int $maxCols, array $rows): array
  644. {
  645. if (isset($_REQUEST['csv_col_names'])) {
  646. $columnNames = array_splice($rows, 0, 1);
  647. $columnNames = $columnNames[0];
  648. // MySQL column names can't end with a space character.
  649. foreach ($columnNames as $key => $col_name) {
  650. $columnNames[$key] = rtrim($col_name);
  651. }
  652. }
  653. if ((isset($columnNames) && count($columnNames) != $maxCols) || ! isset($columnNames)) {
  654. // Fill out column names
  655. for ($i = 0; $i < $maxCols; ++$i) {
  656. $columnNames[] = 'COL ' . ($i + 1);
  657. }
  658. }
  659. return $columnNames;
  660. }
  661. private function getSqlTemplateAndRequiredFields(
  662. ?string $db,
  663. ?string $table,
  664. ?string $csvColumns
  665. ): array {
  666. global $dbi, $error, $message;
  667. $requiredFields = 0;
  668. $sqlTemplate = '';
  669. $fields = [];
  670. if (! $this->getAnalyze() && $db !== null && $table !== null) {
  671. $sqlTemplate = 'INSERT';
  672. if (isset($_POST['csv_ignore'])) {
  673. $sqlTemplate .= ' IGNORE';
  674. }
  675. $sqlTemplate .= ' INTO ' . Util::backquote($table);
  676. $tmp_fields = $dbi->getColumns($db, $table);
  677. if (empty($csvColumns)) {
  678. $fields = $tmp_fields;
  679. } else {
  680. $sqlTemplate .= ' (';
  681. $fields = [];
  682. $tmp = preg_split('/,( ?)/', $csvColumns);
  683. if ($tmp === false) {
  684. $tmp = [];
  685. }
  686. foreach ($tmp as $val) {
  687. if (count($fields) > 0) {
  688. $sqlTemplate .= ', ';
  689. }
  690. /* Trim also `, if user already included backquoted fields */
  691. $val = trim($val, " \t\r\n\0\x0B`");
  692. $found = false;
  693. foreach ($tmp_fields as $field) {
  694. if ($field['Field'] == $val) {
  695. $found = true;
  696. break;
  697. }
  698. }
  699. if (! $found) {
  700. $message = Message::error(
  701. __(
  702. 'Invalid column (%s) specified! Ensure that columns'
  703. . ' names are spelled correctly, separated by commas'
  704. . ', and not enclosed in quotes.'
  705. )
  706. );
  707. $message->addParam($val);
  708. $error = true;
  709. break;
  710. }
  711. if (isset($field)) {
  712. $fields[] = $field;
  713. }
  714. $sqlTemplate .= Util::backquote($val);
  715. }
  716. $sqlTemplate .= ') ';
  717. }
  718. $requiredFields = count($fields);
  719. $sqlTemplate .= ' VALUES (';
  720. }
  721. return [$sqlTemplate, $requiredFields, $fields];
  722. }
  723. /**
  724. * Read the expected column_separated_with String of length
  725. * $csv_terminated_len from the $buffer
  726. * into variable $ch and return the read string $ch
  727. *
  728. * @param string $buffer The original string buffer read from
  729. * csv file
  730. * @param string $ch Partially read "column Separated with"
  731. * string, also used to return after
  732. * reading length equal $csv_terminated_len
  733. * @param int $i Current read counter of buffer string
  734. * @param int $csv_terminated_len The length of "column separated with"
  735. * String
  736. *
  737. * @return string
  738. */
  739. public function readCsvTerminatedString($buffer, $ch, $i, $csv_terminated_len)
  740. {
  741. for ($j = 0; $j < $csv_terminated_len - 1; $j++) {
  742. $i++;
  743. $ch .= mb_substr($buffer, $i, 1);
  744. }
  745. return $ch;
  746. }
  747. /* ~~~~~~~~~~~~~~~~~~~~ Getters and Setters ~~~~~~~~~~~~~~~~~~~~ */
  748. /**
  749. * Returns true if the table should be analyzed, false otherwise
  750. */
  751. private function getAnalyze(): bool
  752. {
  753. return $this->analyze;
  754. }
  755. /**
  756. * Sets to true if the table should be analyzed, false otherwise
  757. *
  758. * @param bool $analyze status
  759. */
  760. private function setAnalyze($analyze): void
  761. {
  762. $this->analyze = $analyze;
  763. }
  764. }