3 declare(strict_types
=1);
5 namespace PhpMyAdmin\Import
;
8 use PhpMyAdmin\Current
;
9 use PhpMyAdmin\DatabaseInterface
;
10 use PhpMyAdmin\Encoding
;
12 use PhpMyAdmin\FileListing
;
13 use PhpMyAdmin\Message
;
14 use PhpMyAdmin\Plugins\ImportPlugin
;
15 use PhpMyAdmin\ResponseRenderer
;
16 use PhpMyAdmin\SqlParser\Parser
;
17 use PhpMyAdmin\SqlParser\Statements\DeleteStatement
;
18 use PhpMyAdmin\SqlParser\Statements\InsertStatement
;
19 use PhpMyAdmin\SqlParser\Statements\ReplaceStatement
;
20 use PhpMyAdmin\SqlParser\Statements\UpdateStatement
;
21 use PhpMyAdmin\SqlParser\Utils\Query
;
22 use PhpMyAdmin\Table\Table
;
28 use function array_key_last
;
29 use function array_map
;
32 use function function_exists
;
33 use function htmlspecialchars
;
35 use function in_array
;
36 use function is_numeric
;
40 use function mb_stripos
;
41 use function mb_strlen
;
42 use function mb_strtoupper
;
43 use function mb_substr
;
44 use function mb_substr_count
;
45 use function preg_grep
;
46 use function preg_match
;
47 use function preg_quote
;
48 use function preg_replace
;
51 use function str_contains
;
52 use function str_starts_with
;
58 * Library that provides common import functions that are used by import plugins
62 private string|
null $importRunBuffer = null;
64 public function __construct()
66 Config
::getInstance()->selectedServer
['DisableIS'] = false;
70 * Checks whether timeout is getting close
72 public function checkTimeout(): bool
74 if (ImportSettings
::$maximumTime === 0) {
78 if (ImportSettings
::$timeoutPassed) {
81 /* 5 in next row might be too much */
84 if (time() - ImportSettings
::$timestamp > ImportSettings
::$maximumTime - 5) {
85 ImportSettings
::$timeoutPassed = true;
94 * Runs query inside import buffer. This is needed to allow displaying
95 * of last SELECT, SHOW or HANDLER results and similar nice stuff.
97 * @param string $sql query to run
98 * @param string[] $sqlData SQL parse data storage
100 public function executeQuery(string $sql, array &$sqlData): void
102 $GLOBALS['error'] ??
= null;
103 $dbi = DatabaseInterface
::getInstance();
104 $GLOBALS['result'] = $dbi->tryQuery($sql);
106 // USE query changes the database, son need to track
107 // while running multiple queries
108 $isUseQuery = mb_stripos($sql, 'use ') !== false;
110 ImportSettings
::$message = '# ';
111 if ($GLOBALS['result'] === false) {
112 ImportSettings
::$failedQueries[] = ['sql' => $sql, 'error' => $dbi->getError()];
114 ImportSettings
::$message .= __('Error');
116 if (! Config
::getInstance()->settings
['IgnoreMultiSubmitErrors']) {
117 $GLOBALS['error'] = true;
122 $aNumRows = (int) $GLOBALS['result']->numRows();
123 $aAffectedRows = (int) @$dbi->affectedRows();
125 ImportSettings
::$message .= __('Rows') . ': ' . $aNumRows;
126 } elseif ($aAffectedRows > 0) {
127 $message = Message
::getMessageForAffectedRows($aAffectedRows);
128 ImportSettings
::$message .= $message->getMessage();
130 ImportSettings
::$message .= __('MySQL returned an empty result set (i.e. zero rows).');
133 if ($aNumRows > 0 ||
$isUseQuery) {
138 if (! ImportSettings
::$sqlQueryDisabled) {
139 $GLOBALS['sql_query'] .= ImportSettings
::$message . "\n";
142 // If a 'USE <db>' SQL-clause was found and the query
143 // succeeded, set our current $db to the new one
144 if ($GLOBALS['result'] != false) {
145 $dbNameInsideUse = $this->lookForUse($sql);
146 if ($dbNameInsideUse !== '') {
147 Current
::$database = $dbNameInsideUse;
148 $GLOBALS['reload'] = true;
152 $pattern = '@^[\s]*(DROP|CREATE)[\s]+(IF EXISTS[[:space:]]+)?(TABLE|DATABASE)[[:space:]]+(.+)@im';
153 if ($GLOBALS['result'] == false ||
! preg_match($pattern, $sql)) {
157 $GLOBALS['reload'] = true;
161 * Runs query inside import buffer. This is needed to allow displaying
162 * of last SELECT, SHOW or HANDLER results and similar nice stuff.
164 * @param string $sql query to run
165 * @param string[] $sqlData SQL parse data storage
167 public function runQuery(string $sql, array &$sqlData): void
169 $GLOBALS['complete_query'] ??
= null;
170 $GLOBALS['display_query'] ??
= null;
172 ImportSettings
::$readMultiply = 1;
173 if ($this->importRunBuffer
=== null) {
174 // Do we have something to push into buffer?
175 $this->importRunBuffer
= $sql !== '' ?
$sql . ';' : null;
180 // Should we skip something?
181 if (ImportSettings
::$skipQueries > 0) {
182 ImportSettings
::$skipQueries--;
183 // Do we have something to push into buffer?
184 $this->importRunBuffer
= $sql !== '' ?
$sql . ';' : null;
189 ImportSettings
::$maxSqlLength = max(
190 ImportSettings
::$maxSqlLength,
191 mb_strlen($this->importRunBuffer
),
193 if (! ImportSettings
::$sqlQueryDisabled) {
194 $GLOBALS['sql_query'] .= $this->importRunBuffer
;
197 ImportSettings
::$executedQueries++
;
199 if (ImportSettings
::$runQuery && ImportSettings
::$executedQueries < 50) {
200 ImportSettings
::$goSql = true;
202 if (! ImportSettings
::$sqlQueryDisabled) {
203 $GLOBALS['complete_query'] = $GLOBALS['sql_query'];
204 $GLOBALS['display_query'] = $GLOBALS['sql_query'];
206 $GLOBALS['complete_query'] = '';
207 $GLOBALS['display_query'] = '';
210 $GLOBALS['sql_query'] = $this->importRunBuffer
;
211 $sqlData[] = $this->importRunBuffer
;
212 } elseif (ImportSettings
::$runQuery) {
213 /* Handle rollback from go_sql */
214 if (ImportSettings
::$goSql && $sqlData !== []) {
217 ImportSettings
::$goSql = false;
219 foreach ($queries as $query) {
220 $this->executeQuery($query, $sqlData);
224 $this->executeQuery($this->importRunBuffer
, $sqlData);
227 // check length of query unless we decided to pass it to /sql
228 // (if $run_query is false, we are just displaying so show
229 // the complete query in the textarea)
230 if (! ImportSettings
::$goSql && ImportSettings
::$runQuery && ! empty($GLOBALS['sql_query'])) {
232 mb_strlen($GLOBALS['sql_query']) > 50000
233 || ImportSettings
::$executedQueries > 50
234 || ImportSettings
::$maxSqlLength > 1000
236 $GLOBALS['sql_query'] = '';
237 ImportSettings
::$sqlQueryDisabled = true;
241 // Do we have something to push into buffer?
242 $this->importRunBuffer
= $sql !== '' ?
$sql . ';' : null;
246 * Looks for the presence of USE to possibly change current db
248 public function lookForUse(string $buffer): string
250 if (preg_match('@^[\s]*USE[[:space:]]+([\S]+)@i', $buffer, $match)) {
251 $db = trim($match[1]);
252 $db = trim($db, ';'); // for example, USE abc;
254 // $db must not contain the escape characters generated by backquote()
255 // ( used in buildSql() as: backquote($db_name), and then called
256 // in runQuery() which in turn calls lookForUse() )
257 return Util
::unQuote($db);
264 * Returns next part of imported file/buffer
266 * @param int $size size of buffer to read (this is maximal size function will return)
268 * @return string|bool part of file/buffer
270 public function getNextChunk(File|
null $importHandle = null, int $size = 32768): string|
bool
272 // Add some progression while reading large amount of data
273 if (ImportSettings
::$readMultiply <= 8) {
274 $size *= ImportSettings
::$readMultiply;
279 ImportSettings
::$readMultiply++
;
281 // We can not read too much
282 if ($size > ImportSettings
::$readLimit) {
283 $size = ImportSettings
::$readLimit;
286 if ($this->checkTimeout()) {
290 if (ImportSettings
::$finished) {
294 if (ImportSettings
::$importFile === 'none') {
295 // Well this is not yet supported and tested,
296 // but should return content of textarea
297 if (mb_strlen($GLOBALS['import_text']) < $size) {
298 ImportSettings
::$finished = true;
300 return $GLOBALS['import_text'];
303 $r = mb_substr($GLOBALS['import_text'], 0, $size);
304 ImportSettings
::$offset +
= $size;
305 $GLOBALS['import_text'] = mb_substr($GLOBALS['import_text'], $size);
310 if ($importHandle === null) {
314 $result = $importHandle->read($size);
315 ImportSettings
::$finished = $importHandle->eof();
316 ImportSettings
::$offset +
= $size;
318 if (ImportSettings
::$charsetConversion) {
319 return Encoding
::convertString(ImportSettings
::$charsetOfFile, 'utf-8', $result);
322 if (ImportSettings
::$offset === $size) {
323 return $this->skipByteOrderMarksFromContents($result);
330 * Skip possible byte order marks (I do not think we need more
331 * charsets, but feel free to add more, you can use wikipedia for
332 * reference: <https://en.wikipedia.org/wiki/Byte_Order_Mark>)
334 * @todo BOM could be used for charset autodetection
336 public function skipByteOrderMarksFromContents(string $contents): string
338 // Do not use mb_ functions they are sensible to mb_internal_encoding()
341 if (str_starts_with($contents, "\xEF\xBB\xBF")) {
342 return substr($contents, 3);
346 if (str_starts_with($contents, "\xFE\xFF") ||
str_starts_with($contents, "\xFF\xFE")) {
347 return substr($contents, 2);
354 * Returns the "Excel" column name (i.e. 1 = "A", 26 = "Z", 27 = "AA", etc.)
356 * This functions uses recursion to build the Excel column name.
358 * The column number (1-26) is converted to the responding
359 * ASCII character (A-Z) and returned.
361 * If the column number is bigger than 26 (= num of letters in alphabet),
362 * an extra character needs to be added. To find this extra character,
363 * the number is divided by 26 and this value is passed to another instance
364 * of the same function (hence recursion). In that new instance the number is
365 * evaluated again, and if it is still bigger than 26, it is divided again
366 * and passed to another instance of the same function. This continues until
367 * the number is smaller than 26. Then the last called function returns
368 * the corresponding ASCII character to the function that called it.
369 * Each time a called function ends an extra character is added to the column name.
370 * When the first function is reached, the last character is added and the complete
371 * column name is returned.
373 * @param int $num the column number
375 * @return string The column's "Excel" name
377 public function getColumnAlphaName(int $num): string
379 $capitalA = 65; // ASCII value for capital "A"
382 /** @infection-ignore-all */
384 $div = (int) ($num / 26);
387 // subtract 1 of divided value in case the modulus is 0,
388 // this is necessary because A-Z has no 'zero'
393 // recursive function call
394 $colName = $this->getColumnAlphaName($div);
395 // use modulus as new column number
400 // use 'Z' if column number is 0,
401 // this is necessary because A-Z has no 'zero'
402 $colName .= mb_chr($capitalA +
26 - 1);
404 // convert column number to ASCII character
405 $colName .= mb_chr($capitalA +
$num - 1);
412 * Returns the column number based on the Excel name.
413 * So "A" = 1, "Z" = 26, "AA" = 27, etc.
415 * Basically this is a base26 (A-Z) to base10 (0-9) conversion.
416 * It iterates through all characters in the column name and
417 * calculates the corresponding value, based on character value
418 * (A = 1, ..., Z = 26) and position in the string.
420 * @param string $name column name(i.e. "A", or "BC", etc.)
422 * @return int The column number
424 public function getColumnNumberFromName(string $name): int
430 $name = mb_strtoupper($name);
431 $numChars = mb_strlen($name);
433 for ($i = 0; $i < $numChars; ++
$i) {
434 // read string from back to front
435 $charPos = $numChars - 1 - $i;
437 // convert capital character to ASCII value
438 // and subtract 64 to get corresponding decimal value
439 // ASCII value of "A" is 65, "B" is 66, etc.
440 // Decimal equivalent of "A" is 1, "B" is 2, etc.
441 $number = mb_ord($name[$charPos]) - 64;
443 // base26 to base10 conversion : multiply each number
444 // with corresponding value of the position, in this case
445 // $i=0 : 1; $i=1 : 26; $i=2 : 676; ...
446 $columnNumber +
= $number * 26 ** $i;
449 return (int) $columnNumber;
453 * Obtains the size of the given cell
455 * @param DecimalSize|int $lastCumulativeSize Last cumulative column size
456 * @param ColumnType|null $lastCumulativeType Last cumulative column type
457 * @param ColumnType $currentCellType Type of the current cell
458 * @param string $cell The current cell
460 * @return DecimalSize|int Size of the given cell in the type-appropriate format
462 * @todo Handle the error cases more elegantly
464 private function detectSize(
465 DecimalSize|
int $lastCumulativeSize,
466 ColumnType|
null $lastCumulativeType,
467 ColumnType
$currentCellType,
470 $currSize = mb_strlen($cell);
473 * If the cell is NULL, don't treat it as a varchar
475 if ($cell === 'NULL') {
476 return $lastCumulativeSize;
479 if ($currentCellType === ColumnType
::Varchar
) {
480 if ($lastCumulativeType === ColumnType
::Varchar
) {
481 if ($currSize >= $lastCumulativeSize) {
485 return $lastCumulativeSize;
488 if ($lastCumulativeType === ColumnType
::Decimal
) {
489 if ($currSize >= $lastCumulativeSize->precision
) {
493 return $lastCumulativeSize->precision
;
496 if ($lastCumulativeType === ColumnType
::BigInt ||
$lastCumulativeType === ColumnType
::Int) {
497 if ($currSize >= $lastCumulativeSize) {
501 return $lastCumulativeSize;
504 if ($lastCumulativeType === null ||
$lastCumulativeType === ColumnType
::None
) {
506 * This is the first row to be analyzed
510 } elseif ($currentCellType === ColumnType
::Decimal
) {
511 if ($lastCumulativeType === ColumnType
::Varchar
) {
512 /* Convert $last_cumulative_size from varchar to decimal format */
513 $size = DecimalSize
::fromCell($cell);
515 if ($size->precision
>= $lastCumulativeSize) {
516 return $size->precision
;
519 return $lastCumulativeSize;
522 if ($lastCumulativeType === ColumnType
::Decimal
) {
523 $size = DecimalSize
::fromCell($cell);
525 if ($size->precision
> $lastCumulativeSize->precision ||
$size->scale
> $lastCumulativeSize->scale
) {
526 /* Take the largest of both types */
527 return DecimalSize
::fromPrecisionAndScale(
528 max($size->precision
, $lastCumulativeSize->precision
),
529 max($size->scale
, $lastCumulativeSize->scale
),
533 return $lastCumulativeSize;
536 if ($lastCumulativeType === ColumnType
::BigInt ||
$lastCumulativeType === ColumnType
::Int) {
537 /* Convert $last_cumulative_size from int to decimal format */
538 $size = DecimalSize
::fromCell($cell);
540 if ($size->precision
>= $lastCumulativeSize) {
544 return DecimalSize
::fromPrecisionAndScale($lastCumulativeSize, $size->scale
);
547 if ($lastCumulativeType === null ||
$lastCumulativeType === ColumnType
::None
) {
549 * This is the first row to be analyzed
552 /* First row of the column */
553 return DecimalSize
::fromCell($cell);
555 } elseif ($currentCellType === ColumnType
::BigInt ||
$currentCellType === ColumnType
::Int) {
556 if ($lastCumulativeType === ColumnType
::Varchar
) {
557 if ($currSize >= $lastCumulativeSize) {
561 return $lastCumulativeSize;
564 if ($lastCumulativeType === ColumnType
::Decimal
) {
565 $oldInt = $lastCumulativeSize->precision
- $lastCumulativeSize->scale
;
566 $newInt = mb_strlen($cell);
568 if ($oldInt >= $newInt) {
569 /* Use old decimal size */
570 return $lastCumulativeSize;
573 return DecimalSize
::fromPrecisionAndScale(
574 $newInt +
$lastCumulativeSize->scale
,
575 $lastCumulativeSize->scale
,
579 if ($lastCumulativeType === ColumnType
::BigInt ||
$lastCumulativeType === ColumnType
::Int) {
580 if ($currSize >= $lastCumulativeSize) {
584 return $lastCumulativeSize;
587 if ($lastCumulativeType === null ||
$lastCumulativeType === ColumnType
::None
) {
589 * This is the first row to be analyzed
596 * An error has DEFINITELY occurred
599 * TODO: Handle this MUCH more elegantly
605 public function detectType(ColumnType|
null $lastCumulativeType, string|
null $cell): ColumnType
608 * If numeric, determine if decimal, int or bigint
609 * Else, we call it varchar for simplicity
612 if ($cell === 'NULL') {
613 return $lastCumulativeType ?? ColumnType
::None
;
616 if (! is_numeric($cell)) {
617 return ColumnType
::Varchar
;
621 $cell === (string) (float) $cell
622 && str_contains($cell, '.')
623 && mb_substr_count($cell, '.') === 1
625 return ColumnType
::Decimal
;
628 if (abs((int) $cell) > 2147483647) {
629 return ColumnType
::BigInt
;
632 if ($cell !== (string) (int) $cell) {
633 return ColumnType
::Varchar
;
636 return ColumnType
::Int;
640 * Determines if the column types are int, decimal, or string
642 * @link https://wiki.phpmyadmin.net/pma/Import
644 * @return AnalysedColumn[]
646 public function analyzeTable(ImportTable
$table): array
648 /* Get number of rows in table */
649 /* Get number of columns */
650 $numberOfColumns = count($table->columns
);
653 for ($i = 0; $i < $numberOfColumns; ++
$i) {
654 $columns[] = new AnalysedColumn(ColumnType
::None
, 0);
657 /* Analyze each column */
658 for ($i = 0; $i < $numberOfColumns; ++
$i) {
659 /* Analyze the column in each row */
660 foreach ($table->rows
as $row) {
661 $cellValue = $row[$i];
662 /* Determine type of the current cell */
663 $currType = $this->detectType($columns[$i]->type
, $cellValue === null ?
null : (string) $cellValue);
664 /* Determine size of the current cell */
665 $columns[$i]->size
= $this->detectSize(
673 * If a type for this column has already been declared,
674 * only alter it if it was a number and a varchar was found
676 if ($currType === ColumnType
::None
) {
680 if ($currType === ColumnType
::Varchar
) {
681 $columns[$i]->type
= ColumnType
::Varchar
;
682 } elseif ($currType === ColumnType
::Decimal
) {
683 if ($columns[$i]->type
!== ColumnType
::Varchar
) {
684 $columns[$i]->type
= ColumnType
::Decimal
;
686 } elseif ($currType === ColumnType
::BigInt
) {
687 if ($columns[$i]->type
!== ColumnType
::Varchar
&& $columns[$i]->type
!== ColumnType
::Decimal
) {
688 $columns[$i]->type
= ColumnType
::BigInt
;
690 } elseif ($currType === ColumnType
::Int) {
692 $columns[$i]->type
!== ColumnType
::Varchar
693 && $columns[$i]->type
!== ColumnType
::Decimal
694 && $columns[$i]->type
!== ColumnType
::BigInt
696 $columns[$i]->type
= ColumnType
::Int;
702 /* Check to ensure that all types are valid */
703 foreach ($columns as $column) {
704 if ($column->type
!== ColumnType
::None
) {
708 $column->type
= ColumnType
::Varchar
;
716 * Builds and executes SQL statements to create the database and tables
717 * as necessary, as well as insert all the data.
719 * @link https://wiki.phpmyadmin.net/pma/Import
721 * @param ImportTable[] $tables
722 * @param AnalysedColumn[][]|null $analyses Analyses of the tables
723 * @param string[]|null $additionalSql Additional SQL to be executed
724 * @param string[] $sqlData List of SQL to be executed
726 public function buildSql(
729 array|
null $analyses = null,
730 array|
null $additionalSql = null,
731 array &$sqlData = [],
733 /* Needed to quell the beast that is Message */
734 ImportSettings
::$importNotice = '';
736 /* Run the $additional_sql statements supplied by the caller plug-in */
737 if ($additionalSql != null) {
738 /* Clean the SQL first */
741 * Only match tables for now, because CREATE IF NOT EXISTS
742 * syntax is lacking or nonexisting for views, triggers,
743 * functions, and procedures.
745 * See: https://bugs.mysql.com/bug.php?id=15287
747 * To the best of my knowledge this is still an issue.
749 * $pattern = 'CREATE (TABLE|VIEW|TRIGGER|FUNCTION|PROCEDURE)';
751 $pattern = '/CREATE [^`]*(TABLE)/';
752 $replacement = 'CREATE \\1 IF NOT EXISTS';
754 // Change CREATE statements to CREATE IF NOT EXISTS to support inserting into existing structures.
755 foreach ($additionalSql as $i => $singleAdditionalSql) {
756 $additionalSql[$i] = preg_replace($pattern, $replacement, $singleAdditionalSql);
757 /* Execute the resulting statements */
758 $this->runQuery($additionalSql[$i], $sqlData);
762 if ($analyses !== null) {
763 /* TODO: Do more checking here to make sure they really are matched */
764 if (count($tables) !== count($analyses)) {
765 ResponseRenderer
::getInstance()->callExit();
768 /* Create SQL code to create the tables */
769 foreach ($tables as $i => $table) {
770 $lastColumnKey = array_key_last($table->columns
);
771 $tempSQLStr = 'CREATE TABLE IF NOT EXISTS '
772 . Util
::backquote($dbName)
773 . '.' . Util
::backquote($table->tableName
) . ' (';
774 foreach ($table->columns
as $j => $column) {
775 $size = $analyses[$i][$j]->size
;
780 $tempSQLStr .= Util
::backquote($column) . ' ' . match ($analyses[$i][$j]->type
) {
781 ColumnType
::None
=> 'NULL',
782 ColumnType
::Varchar
=> 'varchar',
783 ColumnType
::Int => 'int',
784 ColumnType
::Decimal
=> 'decimal',
785 ColumnType
::BigInt
=> 'bigint',
786 ColumnType
::Geometry
=> 'geometry',
788 if ($analyses[$i][$j]->type
!== ColumnType
::Geometry
) {
789 $tempSQLStr .= '(' . $size . ')';
792 if ($j === $lastColumnKey) {
802 * Each SQL statement is executed immediately
803 * after it is formed so that we don't have
804 * to store them in a (possibly large) buffer
806 $this->runQuery($tempSQLStr, $sqlData);
811 * Create the SQL statements to insert all the data
813 * Only one insert query is formed for each table
815 $dbi = DatabaseInterface
::getInstance();
816 foreach ($tables as $tableIndex => $table) {
817 $numCols = count($table->columns
);
818 $lastColumnKey = array_key_last($table->columns
);
820 if ($table->rows
=== []) {
824 $tempSQLStr = 'INSERT INTO ' . Util
::backquote($dbName) . '.'
825 . Util
::backquote($table->tableName
) . ' (';
827 $tempSQLStr .= implode(', ', array_map(Util
::backquote(...), $table->columns
));
829 $tempSQLStr .= ') VALUES ';
831 $lastRowKey = array_key_last($table->rows
);
832 foreach ($table->rows
as $rowIndex => $row) {
835 for ($columnIndex = 0; $columnIndex < $numCols; ++
$columnIndex) {
836 // If fully formatted SQL, no need to enclose
837 // with apostrophes, add slashes etc.
840 && $analyses[$tableIndex][$columnIndex]->isFullyFormattedSql
842 $tempSQLStr .= (string) $row[$columnIndex];
844 if ($analyses !== null) {
845 $isVarchar = $analyses[$tableIndex][$columnIndex]->type
=== ColumnType
::Varchar
;
847 $isVarchar = ! is_numeric($row[$columnIndex]);
850 /* Don't put quotes around NULL fields */
851 if ((string) $row[$columnIndex] === 'NULL') {
855 $tempSQLStr .= $isVarchar
856 ?
$dbi->quoteString((string) $row[$columnIndex])
857 : (string) $row[$columnIndex];
860 if ($columnIndex === $lastColumnKey) {
869 if ($rowIndex !== $lastRowKey) {
870 $tempSQLStr .= ",\n ";
873 /* Delete the row after we are done with it */
874 unset($table->rows
[$rowIndex]);
878 * Each SQL statement is executed immediately
879 * after it is formed so that we don't have
880 * to store them in a (possibly large) buffer
882 $this->runQuery($tempSQLStr, $sqlData);
890 * Add the viewable structures from $additional_sql
891 * to $tables so they are also displayed
893 $viewPattern = '@VIEW `[^`]+`\.`([^`]+)@';
894 $tablePattern = '@CREATE TABLE IF NOT EXISTS `([^`]+)`@';
895 /* Check a third pattern to make sure its not a "USE `db_name`;" statement */
897 /** @var string $sql */
898 foreach ($additionalSql ??
[] as $sql) {
900 preg_match($viewPattern, $sql, $regs);
903 preg_match($tablePattern, $sql, $regs);
910 foreach ($tables as $table) {
911 if ($regs[1] === $table->tableName
) {
916 $tables[] = new ImportTable($regs[1]);
919 $message = $this->getSuccessMessage($dbName, $tables, $dbi);
921 ImportSettings
::$importNotice = $message;
924 public function handleRollbackRequest(string $sqlQuery): void
926 $sqlDelimiter = $_POST['sql_delimiter'];
927 $queries = explode($sqlDelimiter, $sqlQuery);
928 $dbi = DatabaseInterface
::getInstance();
929 foreach ($queries as $sqlQuery) {
930 if ($sqlQuery === '') {
934 // Check each query for ROLLBACK support.
935 if ($this->checkIfRollbackPossible($sqlQuery)) {
939 $sqlError = $dbi->getError();
940 $error = $sqlError !== '' ?
$sqlError : __(
941 'Only INSERT, UPDATE, DELETE and REPLACE '
942 . 'SQL queries containing transactional engine tables can be rolled back.',
945 $response = ResponseRenderer
::getInstance();
946 $response->addJSON('message', Message
::rawError($error));
947 $response->callExit();
950 // If everything fine, START a transaction.
951 $dbi->query('START TRANSACTION');
955 * Checks if ROLLBACK is possible for a SQL query or not.
957 * @param string $sqlQuery SQL query
959 public function checkIfRollbackPossible(string $sqlQuery): bool
961 $parser = new Parser($sqlQuery);
963 if (empty($parser->statements
[0])) {
967 $statement = $parser->statements
[0];
969 // Check if query is supported.
971 ! ($statement instanceof InsertStatement
972 ||
$statement instanceof UpdateStatement
973 ||
$statement instanceof DeleteStatement
974 ||
$statement instanceof ReplaceStatement
)
979 // Get table_references from the query.
980 $tables = Query
::getTables($statement);
982 // Check if each table is 'InnoDB'.
983 foreach ($tables as $table) {
984 if (! $this->isTableTransactional($table)) {
993 * Checks if a table is 'InnoDB' or not.
995 * @param string $table Table details
997 public function isTableTransactional(string $table): bool
999 $table = explode('.', $table);
1000 if (count($table) === 2) {
1001 $db = Util
::unQuote($table[0]);
1002 $table = Util
::unQuote($table[1]);
1004 $db = Current
::$database;
1005 $table = Util
::unQuote($table[0]);
1008 // Query to check if table exists.
1009 $checkTableQuery = 'SELECT * FROM ' . Util
::backquote($db)
1010 . '.' . Util
::backquote($table) . ' '
1013 $dbi = DatabaseInterface
::getInstance();
1014 $result = $dbi->tryQuery($checkTableQuery);
1020 // List of Transactional Engines.
1021 $transactionalEngines = [
1033 // Query to check if table is 'Transactional'.
1034 $checkQuery = 'SELECT `ENGINE` FROM `information_schema`.`tables` '
1035 . 'WHERE `table_name` = ' . $dbi->quoteString($table) . ' '
1036 . 'AND `table_schema` = ' . $dbi->quoteString($db) . ' '
1037 . 'AND UPPER(`engine`) IN ("'
1038 . implode('", "', $transactionalEngines)
1041 $result = $dbi->tryQuery($checkQuery);
1043 return $result && $result->numRows() == 1;
1046 /** @return string[] */
1047 public static function getCompressions(): array
1051 $config = Config
::getInstance();
1052 if ($config->settings
['GZipDump'] && function_exists('gzopen')) {
1053 $compressions[] = 'gzip';
1056 if ($config->settings
['BZipDump'] && function_exists('bzopen')) {
1057 $compressions[] = 'bzip2';
1060 if ($config->settings
['ZipDump'] && function_exists('zip_open')) {
1061 $compressions[] = 'zip';
1064 return $compressions;
1067 /** @param ImportPlugin[] $importList List of plugin instances. */
1068 public static function getLocalFiles(array $importList): false|
string
1070 $fileListing = new FileListing();
1073 foreach ($importList as $importPlugin) {
1074 if ($extensions !== '') {
1078 $extensions .= $importPlugin->getProperties()->getExtension();
1081 $matcher = '@\.(' . $extensions . ')(\.(' . $fileListing->supportedDecompressions() . '))?$@';
1083 $active = ImportSettings
::$localImportFile !== '' && ImportSettings
::$timeoutPassed
1084 ? ImportSettings
::$localImportFile
1087 return $fileListing->getFileSelectOptions(
1088 Util
::userDir(Config
::getInstance()->settings
['UploadDir'] ??
''),
1094 public function getNextAvailableTableName(string $databaseName, string $proposedTableName): string
1096 if ($proposedTableName === '') {
1097 $proposedTableName = 'TABLE';
1100 $importFileName = rtrim($proposedTableName);
1101 $importFileName = (string) preg_replace('/[^\x{0001}-\x{FFFF}]/u', '_', $importFileName);
1103 if ($databaseName !== '') {
1104 $existingTables = DatabaseInterface
::getInstance()->getTables($databaseName);
1106 // check to see if {filename} as table exist
1107 // if no use filename as table name
1108 if (! in_array($importFileName, $existingTables, true)) {
1109 return $importFileName;
1112 // check if {filename}_ as table exist
1113 $nameArray = preg_grep('/^' . preg_quote($importFileName, '/') . '_/isU', $existingTables);
1114 if ($nameArray === false) {
1115 return $importFileName;
1118 return $importFileName . '_' . (count($nameArray) +
1);
1121 return $importFileName;
1125 * @param string[] $sqlData List of SQL statements to be executed
1129 public function createDatabase(string $dbName, string $charset, string $collation, array $sqlData): array
1131 $sql = 'CREATE DATABASE IF NOT EXISTS ' . Util
::backquote($dbName)
1132 . ' DEFAULT CHARACTER SET ' . $charset . ' COLLATE ' . $collation;
1133 $this->runQuery($sql, $sqlData);
1138 /** @param ImportTable[] $tables */
1139 private function getHtmlListForAllTables(array $tables, string $dbName, DatabaseInterface
$dbi): string
1143 foreach ($tables as $table) {
1144 $params = ['db' => $dbName, 'table' => $table->tableName
];
1145 $tblUrl = Url
::getFromRoute('/sql', $params);
1146 $tblStructUrl = Url
::getFromRoute('/table/structure', $params);
1147 $tblOpsUrl = Url
::getFromRoute('/table/operations', $params);
1149 $tableObj = new Table($table->tableName
, $dbName, $dbi);
1150 if (! $tableObj->isView()) {
1151 $message .= sprintf(
1152 '<li><a href="%s" title="%s">%s</a> (<a href="%s" title="%s">' . __(
1154 ) . '</a>) (<a href="%s" title="%s">' . __('Options') . '</a>)</li>',
1157 __('Go to table: %s'),
1159 Util
::backquote($table->tableName
),
1162 htmlspecialchars($table->tableName
),
1165 __('Structure of %s'),
1167 Util
::backquote($table->tableName
),
1172 __('Edit settings for %s'),
1174 Util
::backquote($table->tableName
),
1179 $message .= sprintf(
1180 '<li><a href="%s" title="%s">%s</a></li>',
1183 __('Go to view: %s'),
1185 Util
::backquote($table->tableName
),
1188 htmlspecialchars($table->tableName
),
1193 return $message . '</ul></ul>';
1196 /** @param ImportTable[] $tables */
1197 private function getSuccessMessage(string $dbName, array $tables, DatabaseInterface
$dbi): string
1199 $dbUrl = Url
::getFromRoute('/database/structure', ['db' => $dbName]);
1200 $dbOperationsUrl = Url
::getFromRoute('/database/operations', ['db' => $dbName]);
1202 $message = '<br><br>';
1203 $message .= '<strong>' . __(
1204 'The following structures have either been created or altered. Here you can:',
1205 ) . '</strong><br>';
1206 $message .= '<ul><li>' . __("View a structure's contents by clicking on its name.") . '</li>';
1207 $message .= '<li>' . __('Change any of its settings by clicking the corresponding "Options" link.') . '</li>';
1208 $message .= '<li>' . __('Edit structure by following the "Structure" link.')
1210 $message .= sprintf(
1211 '<br><li><a href="%s" title="%s">%s</a> (<a href="%s" title="%s">'
1212 . __('Options') . '</a>)</li>',
1215 __('Go to database: %s'),
1216 htmlspecialchars(Util
::backquote($dbName)),
1218 htmlspecialchars($dbName),
1221 __('Edit settings for %s'),
1222 htmlspecialchars(Util
::backquote($dbName)),
1226 $message .= $this->getHtmlListForAllTables($tables, $dbName, $dbi);