src/Import/SimulateDml.php

   1 <?php
   2
   3 declare(strict_types=1);
   4
   5 namespace PhpMyAdmin\Import;
   6
   7 use PhpMyAdmin\Core;
   8 use PhpMyAdmin\Current;
   9 use PhpMyAdmin\DatabaseInterface;
  10 use PhpMyAdmin\Html;
  11 use PhpMyAdmin\SqlParser\Parser;
  12 use PhpMyAdmin\SqlParser\Statements\DeleteStatement;
  13 use PhpMyAdmin\SqlParser\Statements\UpdateStatement;
  14 use PhpMyAdmin\SqlParser\Utils\Query;
  15 use PhpMyAdmin\Url;
  16 use PhpMyAdmin\Util;
  17 use Webmozart\Assert\Assert;
  18
  19 use function array_key_exists;
  20 use function array_reverse;
  21 use function implode;
  22
  23 final class SimulateDml
  24 {
  25     public function __construct(private DatabaseInterface $dbi)
  26     {
  27     }
  28
  29     public function getError(): string
  30     {
  31         return $this->dbi->getError();
  32     }
  33
  34     /**
  35      * Find the matching rows for UPDATE/DELETE query.
  36      *
  37      * @return array<string, int|string>
  38      * @psalm-return array{
  39      *   sql_query: string,
  40      *   matched_rows: int,
  41      *   matched_rows_url: string
  42      * }
  43      */
  44     public function getMatchedRows(
  45         string $query,
  46         Parser $parser,
  47         DeleteStatement|UpdateStatement $statement,
  48     ): array {
  49         if ($statement instanceof DeleteStatement) {
  50             $matchedRowsQuery = $this->getSimulatedDeleteQuery($parser, $statement);
  51         } else {
  52             $matchedRowsQuery = $this->getSimulatedUpdateQuery($parser, $statement);
  53         }
  54
  55         // Execute the query and get the number of matched rows.
  56         $matchedRows = $this->executeMatchedRowQuery($matchedRowsQuery);
  57         $matchedRowsUrl = Url::getFromRoute('/sql', [
  58             'db' => Current::$database,
  59             'sql_query' => $matchedRowsQuery,
  60             'sql_signature' => Core::signSqlQuery($matchedRowsQuery),
  61         ]);
  62
  63         return [
  64             'sql_query' => Html\Generator::formatSql($query),
  65             'matched_rows' => $matchedRows,
  66             'matched_rows_url' => $matchedRowsUrl,
  67         ];
  68     }
  69
  70     /**
  71      * Executes the matched_row_query and returns the resultant row count.
  72      *
  73      * @param string $matchedRowQuery SQL query
  74      */
  75     private function executeMatchedRowQuery(string $matchedRowQuery): int
  76     {
  77         $this->dbi->selectDb(Current::$database);
  78         $result = $this->dbi->tryQuery($matchedRowQuery);
  79         if ($result === false) {
  80             return 0;
  81         }
  82
  83         return (int) $result->numRows();
  84     }
  85
  86     /**
  87      * Transforms a DELETE query into SELECT statement.
  88      *
  89      * @return string SQL query
  90      */
  91     private function getSimulatedDeleteQuery(Parser $parser, DeleteStatement $statement): string
  92     {
  93         $tableReferences = Query::getTables($statement);
  94         Assert::count($tableReferences, 1, 'No joins allowed in simulation query');
  95         Assert::notNull($parser->list, 'Parser list not set');
  96
  97         $condition = Query::getClause($statement, $parser->list, 'WHERE');
  98         $where = $condition === '' ? '' : ' WHERE ' . $condition;
  99         $order = $statement->order === null || $statement->order === []
 100             ? ''
 101             : ' ORDER BY ' . Query::getClause($statement, $parser->list, 'ORDER BY');
 102         $limit = $statement->limit === null ? '' : ' LIMIT ' . Query::getClause($statement, $parser->list, 'LIMIT');
 103
 104         return 'SELECT * FROM ' . $tableReferences[0] . $where . $order . $limit;
 105     }
 106
 107     /**
 108      * Transforms a UPDATE query into SELECT statement.
 109      *
 110      * @return string SQL query
 111      */
 112     private function getSimulatedUpdateQuery(Parser $parser, UpdateStatement $statement): string
 113     {
 114         $tableReferences = Query::getTables($statement);
 115         Assert::count($tableReferences, 1, 'No joins allowed in simulation query');
 116         Assert::isNonEmptyList($statement->set, 'SET statements missing');
 117         Assert::notNull($parser->list, 'Parser list not set');
 118
 119         $values = [];
 120         $newColumns = [];
 121         $oldColumns = [];
 122         foreach (array_reverse($statement->set) as $set) {
 123             $column = Util::unQuote($set->column);
 124             if (array_key_exists($column, $values)) {
 125                 continue;
 126             }
 127
 128             $oldColumns[] = Util::backquote($column);
 129             $values[$column] = $set->value . ' AS ' . ($newColumns[] = Util::backquote($column . ' `new`'));
 130         }
 131
 132         $condition = Query::getClause($statement, $parser->list, 'WHERE');
 133         $where = $condition === '' ? '' : ' WHERE ' . $condition;
 134         $order = $statement->order === null || $statement->order === []
 135             ? ''
 136             : ' ORDER BY ' . Query::getClause($statement, $parser->list, 'ORDER BY');
 137         $limit = $statement->limit === null ? '' : ' LIMIT ' . Query::getClause($statement, $parser->list, 'LIMIT');
 138
 139         return 'SELECT *' .
 140             ' FROM (' .
 141             'SELECT *, ' . implode(', ', $values) . ' FROM ' . $tableReferences[0] . $where . $order . $limit .
 142             ') AS `pma_tmp`' .
 143             ' WHERE NOT (' . implode(', ', $oldColumns) . ') <=> (' . implode(', ', $newColumns) . ')';
 144     }
 145 }