diff options
Diffstat (limited to 'core/modules/mysqli')
25 files changed, 1675 insertions, 0 deletions
diff --git a/core/modules/mysqli/mysqli.info.yml b/core/modules/mysqli/mysqli.info.yml new file mode 100644 index 000000000000..38a9239f3e95 --- /dev/null +++ b/core/modules/mysqli/mysqli.info.yml @@ -0,0 +1,9 @@ +name: MySQLi +type: module +description: 'Database driver for MySQLi.' +version: VERSION +package: Core (Experimental) +lifecycle: experimental +hidden: true +dependencies: + - drupal:mysql diff --git a/core/modules/mysqli/mysqli.install b/core/modules/mysqli/mysqli.install new file mode 100644 index 000000000000..7f1147d63adb --- /dev/null +++ b/core/modules/mysqli/mysqli.install @@ -0,0 +1,78 @@ +<?php + +/** + * @file + * Install, update and uninstall functions for the mysqli module. + */ + +use Drupal\Core\Database\Database; +use Drupal\Core\Extension\Requirement\RequirementSeverity; +use Drupal\Core\Render\Markup; + +/** + * Implements hook_requirements(). + */ +function mysqli_requirements($phase): array { + $requirements = []; + + if ($phase === 'runtime') { + // Test with MySql databases. + if (Database::isActiveConnection()) { + $connection = Database::getConnection(); + // Only show requirements when MySQLi is the default database connection. + if (!($connection->driver() === 'mysqli' && $connection->getProvider() === 'mysqli')) { + return []; + } + + $query = $connection->isMariaDb() ? 'SELECT @@SESSION.tx_isolation' : 'SELECT @@SESSION.transaction_isolation'; + + $isolation_level = $connection->query($query)->fetchField(); + + $tables_missing_primary_key = []; + $tables = $connection->schema()->findTables('%'); + foreach ($tables as $table) { + $primary_key_column = Database::getConnection()->query("SHOW KEYS FROM {" . $table . "} WHERE Key_name = 'PRIMARY'")->fetchAllAssoc('Column_name'); + if (empty($primary_key_column)) { + $tables_missing_primary_key[] = $table; + } + } + + $description = []; + if ($isolation_level == 'READ-COMMITTED') { + if (empty($tables_missing_primary_key)) { + $severity_level = RequirementSeverity::OK; + } + else { + $severity_level = RequirementSeverity::Error; + } + } + else { + if ($isolation_level == 'REPEATABLE-READ') { + $severity_level = RequirementSeverity::Warning; + } + else { + $severity_level = RequirementSeverity::Error; + $description[] = t('This is not supported by Drupal.'); + } + $description[] = t('The recommended level for Drupal is "READ COMMITTED".'); + } + + if (!empty($tables_missing_primary_key)) { + $description[] = t('For this to work correctly, all tables must have a primary key. The following table(s) do not have a primary key: @tables.', ['@tables' => implode(', ', $tables_missing_primary_key)]); + } + + $description[] = t('See the <a href=":performance_doc">setting MySQL transaction isolation level</a> page for more information.', [ + ':performance_doc' => 'https://www.drupal.org/docs/system-requirements/setting-the-mysql-transaction-isolation-level', + ]); + + $requirements['mysql_transaction_level'] = [ + 'title' => t('Transaction isolation level'), + 'severity' => $severity_level, + 'value' => $isolation_level, + 'description' => Markup::create(implode(' ', $description)), + ]; + } + } + + return $requirements; +} diff --git a/core/modules/mysqli/mysqli.services.yml b/core/modules/mysqli/mysqli.services.yml new file mode 100644 index 000000000000..82a476ceb9e8 --- /dev/null +++ b/core/modules/mysqli/mysqli.services.yml @@ -0,0 +1,4 @@ +services: + mysqli.views.cast_sql: + class: Drupal\mysqli\Plugin\views\query\MysqliCastSql + public: false diff --git a/core/modules/mysqli/src/Driver/Database/mysqli/Connection.php b/core/modules/mysqli/src/Driver/Database/mysqli/Connection.php new file mode 100644 index 000000000000..e41df23075a3 --- /dev/null +++ b/core/modules/mysqli/src/Driver/Database/mysqli/Connection.php @@ -0,0 +1,191 @@ +<?php + +declare(strict_types=1); + +namespace Drupal\mysqli\Driver\Database\mysqli; + +use Drupal\Core\Database\Connection as BaseConnection; +use Drupal\Core\Database\ConnectionNotDefinedException; +use Drupal\Core\Database\Database; +use Drupal\Core\Database\DatabaseAccessDeniedException; +use Drupal\Core\Database\DatabaseNotFoundException; +use Drupal\Core\Database\Transaction\TransactionManagerInterface; +use Drupal\mysql\Driver\Database\mysql\Connection as BaseMySqlConnection; + +/** + * MySQLi implementation of \Drupal\Core\Database\Connection. + */ +class Connection extends BaseMySqlConnection { + + /** + * {@inheritdoc} + */ + protected $statementWrapperClass = Statement::class; + + public function __construct( + \mysqli $connection, + array $connectionOptions = [], + ) { + // If the SQL mode doesn't include 'ANSI_QUOTES' (explicitly or via a + // combination mode), then MySQL doesn't interpret a double quote as an + // identifier quote, in which case use the non-ANSI-standard backtick. + // + // @see https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_ansi_quotes + $ansiQuotesModes = ['ANSI_QUOTES', 'ANSI']; + $isAnsiQuotesMode = FALSE; + if (isset($connectionOptions['init_commands']['sql_mode'])) { + foreach ($ansiQuotesModes as $mode) { + // None of the modes in $ansiQuotesModes are substrings of other modes + // that are not in $ansiQuotesModes, so a simple stripos() does not + // return false positives. + if (stripos($connectionOptions['init_commands']['sql_mode'], $mode) !== FALSE) { + $isAnsiQuotesMode = TRUE; + break; + } + } + } + + if ($this->identifierQuotes === ['"', '"'] && !$isAnsiQuotesMode) { + $this->identifierQuotes = ['`', '`']; + } + + BaseConnection::__construct($connection, $connectionOptions); + } + + /** + * {@inheritdoc} + */ + public static function open(array &$connection_options = []) { + // Sets mysqli error reporting mode to report errors from mysqli function + // calls and to throw mysqli_sql_exception for errors. + // @see https://www.php.net/manual/en/mysqli-driver.report-mode.php + mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); + + // Allow PDO options to be overridden. + $connection_options += [ + 'pdo' => [], + ]; + + try { + $mysqli = @new \mysqli( + $connection_options['host'], + $connection_options['username'], + $connection_options['password'], + $connection_options['database'] ?? '', + !empty($connection_options['port']) ? (int) $connection_options['port'] : 3306, + $connection_options['unix_socket'] ?? '' + ); + if (!$mysqli->set_charset('utf8mb4')) { + throw new InvalidCharsetException('Invalid charset utf8mb4'); + } + } + catch (\mysqli_sql_exception $e) { + if ($e->getCode() === static::DATABASE_NOT_FOUND) { + throw new DatabaseNotFoundException($e->getMessage(), $e->getCode(), $e); + } + elseif ($e->getCode() === static::ACCESS_DENIED) { + throw new DatabaseAccessDeniedException($e->getMessage(), $e->getCode(), $e); + } + + throw new ConnectionNotDefinedException('Invalid database connection: ' . $e->getMessage(), $e->getCode(), $e); + } + + // Force MySQL to use the UTF-8 character set. Also set the collation, if a + // certain one has been set; otherwise, MySQL defaults to + // 'utf8mb4_0900_ai_ci' for the 'utf8mb4' character set. + if (!empty($connection_options['collation'])) { + $mysqli->query('SET NAMES utf8mb4 COLLATE ' . $connection_options['collation']); + } + else { + $mysqli->query('SET NAMES utf8mb4'); + } + + // Set MySQL init_commands if not already defined. Default Drupal's MySQL + // behavior to conform more closely to SQL standards. This allows Drupal + // to run almost seamlessly on many different kinds of database systems. + // These settings force MySQL to behave the same as postgresql, or sqlite + // in regard to syntax interpretation and invalid data handling. See + // https://www.drupal.org/node/344575 for further discussion. Also, as MySQL + // 5.5 changed the meaning of TRADITIONAL we need to spell out the modes one + // by one. + $connection_options += [ + 'init_commands' => [], + ]; + + $connection_options['init_commands'] += [ + 'sql_mode' => "SET sql_mode = 'ANSI,TRADITIONAL'", + ]; + if (!empty($connection_options['isolation_level'])) { + $connection_options['init_commands'] += [ + 'isolation_level' => 'SET SESSION TRANSACTION ISOLATION LEVEL ' . strtoupper($connection_options['isolation_level']), + ]; + } + + // Execute initial commands. + foreach ($connection_options['init_commands'] as $sql) { + $mysqli->query($sql); + } + + return $mysqli; + } + + /** + * {@inheritdoc} + */ + public function driver() { + return 'mysqli'; + } + + /** + * {@inheritdoc} + */ + public function clientVersion() { + return \mysqli_get_client_info(); + } + + /** + * {@inheritdoc} + */ + public function createDatabase($database): void { + // Escape the database name. + $database = Database::getConnection()->escapeDatabase($database); + + try { + // Create the database and set it as active. + $this->connection->query("CREATE DATABASE $database"); + $this->connection->query("USE $database"); + } + catch (\Exception $e) { + throw new DatabaseNotFoundException($e->getMessage()); + } + } + + /** + * {@inheritdoc} + */ + public function quote($string, $parameter_type = \PDO::PARAM_STR) { + return "'" . $this->connection->escape_string((string) $string) . "'"; + } + + /** + * {@inheritdoc} + */ + public function lastInsertId(?string $name = NULL): string { + return (string) $this->connection->insert_id; + } + + /** + * {@inheritdoc} + */ + public function exceptionHandler() { + return new ExceptionHandler(); + } + + /** + * {@inheritdoc} + */ + protected function driverTransactionManager(): TransactionManagerInterface { + return new TransactionManager($this); + } + +} diff --git a/core/modules/mysqli/src/Driver/Database/mysqli/ExceptionHandler.php b/core/modules/mysqli/src/Driver/Database/mysqli/ExceptionHandler.php new file mode 100644 index 000000000000..78e7a331f121 --- /dev/null +++ b/core/modules/mysqli/src/Driver/Database/mysqli/ExceptionHandler.php @@ -0,0 +1,30 @@ +<?php + +declare(strict_types=1); + +namespace Drupal\mysqli\Driver\Database\mysqli; + +use Drupal\Core\Database\StatementInterface; +use Drupal\mysql\Driver\Database\mysql\ExceptionHandler as BaseMySqlExceptionHandler; + +/** + * MySQLi database exception handler class. + */ +class ExceptionHandler extends BaseMySqlExceptionHandler { + + /** + * {@inheritdoc} + */ + public function handleExecutionException(\Exception $exception, StatementInterface $statement, array $arguments = [], array $options = []): void { + // Close the client statement to release handles. + if ($statement->hasClientStatement()) { + $statement->getClientStatement()->close(); + } + + if (!($exception instanceof \mysqli_sql_exception)) { + throw $exception; + } + $this->rethrowNormalizedException($exception, $exception->getSqlState(), $exception->getCode(), $statement->getQueryString(), $arguments); + } + +} diff --git a/core/modules/mysqli/src/Driver/Database/mysqli/Install/Tasks.php b/core/modules/mysqli/src/Driver/Database/mysqli/Install/Tasks.php new file mode 100644 index 000000000000..f27a083541e6 --- /dev/null +++ b/core/modules/mysqli/src/Driver/Database/mysqli/Install/Tasks.php @@ -0,0 +1,28 @@ +<?php + +declare(strict_types=1); + +namespace Drupal\mysqli\Driver\Database\mysqli\Install; + +use Drupal\mysql\Driver\Database\mysql\Install\Tasks as BaseInstallTasks; + +/** + * Specifies installation tasks for MySQLi. + */ +class Tasks extends BaseInstallTasks { + + /** + * {@inheritdoc} + */ + public function installable() { + return extension_loaded('mysqli'); + } + + /** + * {@inheritdoc} + */ + public function name() { + return $this->t('@parent via mysqli (Experimental)', ['@parent' => parent::name()]); + } + +} diff --git a/core/modules/mysqli/src/Driver/Database/mysqli/InvalidCharsetException.php b/core/modules/mysqli/src/Driver/Database/mysqli/InvalidCharsetException.php new file mode 100644 index 000000000000..e6f2c86148c5 --- /dev/null +++ b/core/modules/mysqli/src/Driver/Database/mysqli/InvalidCharsetException.php @@ -0,0 +1,13 @@ +<?php + +declare(strict_types=1); + +namespace Drupal\mysqli\Driver\Database\mysqli; + +use Drupal\Core\Database\DatabaseExceptionWrapper; + +/** + * This exception class signals an invalid charset is being used. + */ +class InvalidCharsetException extends DatabaseExceptionWrapper { +} diff --git a/core/modules/mysqli/src/Driver/Database/mysqli/NamedPlaceholderConverter.php b/core/modules/mysqli/src/Driver/Database/mysqli/NamedPlaceholderConverter.php new file mode 100644 index 000000000000..31386bc907bc --- /dev/null +++ b/core/modules/mysqli/src/Driver/Database/mysqli/NamedPlaceholderConverter.php @@ -0,0 +1,250 @@ +<?php + +declare(strict_types=1); + +namespace Drupal\mysqli\Driver\Database\mysqli; + +// cspell:ignore DBAL MULTICHAR + +/** + * A class to convert a SQL statement with named placeholders to positional. + * + * The parsing logic and the implementation is inspired by the PHP PDO parser, + * and a simplified copy of the parser implementation done by the Doctrine DBAL + * project. + * + * This class is a near-copy of Doctrine\DBAL\SQL\Parser, which is part of the + * Doctrine project: <http://www.doctrine-project.org>. It was copied from + * version 4.0.0. + * + * Original copyright: + * + * Copyright (c) 2006-2018 Doctrine Project + * + * Permission is hereby granted, free of charge, to any person obtaining a copy + * of this software and associated documentation files (the "Software"), to deal + * in the Software without restriction, including without limitation the rights + * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell + * copies of the Software, and to permit persons to whom the Software is + * furnished to do so, subject to the following conditions: + * + * The above copyright notice and this permission notice shall be included in + * all copies or substantial portions of the Software. + * + * @see https://github.com/doctrine/dbal/blob/4.0.0/src/SQL/Parser.php + * + * @internal + */ +final class NamedPlaceholderConverter { + /** + * A list of regex patterns for parsing. + */ + private const string SPECIAL_CHARS = ':\?\'"`\\[\\-\\/'; + private const string BACKTICK_IDENTIFIER = '`[^`]*`'; + private const string BRACKET_IDENTIFIER = '(?<!\b(?i:ARRAY))\[(?:[^\]])*\]'; + private const string MULTICHAR = ':{2,}'; + private const string NAMED_PARAMETER = ':[a-zA-Z0-9_]+'; + private const string POSITIONAL_PARAMETER = '(?<!\\?)\\?(?!\\?)'; + private const string ONE_LINE_COMMENT = '--[^\r\n]*'; + private const string MULTI_LINE_COMMENT = '/\*([^*]+|\*+[^/*])*\**\*/'; + private const string SPECIAL = '[' . self::SPECIAL_CHARS . ']'; + private const string OTHER = '[^' . self::SPECIAL_CHARS . ']+'; + + /** + * The combined regex pattern for parsing. + */ + private string $sqlPattern; + + /** + * The list of original named arguments. + * + * The initial placeholder colon is removed. + * + * @var array<string|int, mixed> + */ + private array $originalParameters = []; + + /** + * The maximum positional placeholder parsed. + * + * Normally Drupal does not produce SQL with positional placeholders, but + * this is to manage the edge case. + */ + private int $originalParameterIndex = 0; + + /** + * The converted SQL statement in its parts. + * + * @var list<string> + */ + private array $convertedSQL = []; + + /** + * The list of converted arguments. + * + * @var list<mixed> + */ + private array $convertedParameters = []; + + public function __construct() { + // Builds the combined regex pattern for parsing. + $this->sqlPattern = sprintf('(%s)', implode('|', [ + $this->getAnsiSQLStringLiteralPattern("'"), + $this->getAnsiSQLStringLiteralPattern('"'), + self::BACKTICK_IDENTIFIER, + self::BRACKET_IDENTIFIER, + self::MULTICHAR, + self::ONE_LINE_COMMENT, + self::MULTI_LINE_COMMENT, + self::OTHER, + ])); + } + + /** + * Parses an SQL statement with named placeholders. + * + * This method explodes the SQL statement in parts that can be reassembled + * into a string with positional placeholders. + * + * @param string $sql + * The SQL statement with named placeholders. + * @param array<string|int, mixed> $args + * The statement arguments. + */ + public function parse(string $sql, array $args): void { + // Reset the object state. + $this->originalParameters = []; + $this->originalParameterIndex = 0; + $this->convertedSQL = []; + $this->convertedParameters = []; + + foreach ($args as $key => $value) { + if (is_int($key)) { + // Positional placeholder; edge case. + $this->originalParameters[$key] = $value; + } + else { + // Named placeholder like ':placeholder'; remove the initial colon. + $parameter = $key[0] === ':' ? substr($key, 1) : $key; + $this->originalParameters[$parameter] = $value; + } + } + + /** @var array<string,callable> $patterns */ + $patterns = [ + self::NAMED_PARAMETER => function (string $sql): void { + $this->addNamedParameter($sql); + }, + self::POSITIONAL_PARAMETER => function (string $sql): void { + $this->addPositionalParameter($sql); + }, + $this->sqlPattern => function (string $sql): void { + $this->addOther($sql); + }, + self::SPECIAL => function (string $sql): void { + $this->addOther($sql); + }, + ]; + + $offset = 0; + + while (($handler = current($patterns)) !== FALSE) { + if (preg_match('~\G' . key($patterns) . '~s', $sql, $matches, 0, $offset) === 1) { + $handler($matches[0]); + reset($patterns); + $offset += strlen($matches[0]); + } + elseif (preg_last_error() !== PREG_NO_ERROR) { + throw new \RuntimeException('Regular expression error'); + } + else { + next($patterns); + } + } + + assert($offset === strlen($sql)); + } + + /** + * Helper to return a regex pattern from a delimiter character. + * + * @param string $delimiter + * A delimiter character. + * + * @return string + * The regex pattern. + */ + private function getAnsiSQLStringLiteralPattern(string $delimiter): string { + return $delimiter . '[^' . $delimiter . ']*' . $delimiter; + } + + /** + * Adds a positional placeholder to the converted parts. + * + * Normally Drupal does not produce SQL with positional placeholders, but + * this is to manage the edge case. + * + * @param string $sql + * The SQL part. + */ + private function addPositionalParameter(string $sql): void { + $index = $this->originalParameterIndex; + + if (!array_key_exists($index, $this->originalParameters)) { + throw new \RuntimeException('Missing Positional Parameter ' . $index); + } + + $this->convertedSQL[] = '?'; + $this->convertedParameters[] = $this->originalParameters[$index]; + + $this->originalParameterIndex++; + } + + /** + * Adds a named placeholder to the converted parts. + * + * @param string $sql + * The SQL part. + */ + private function addNamedParameter(string $sql): void { + $name = substr($sql, 1); + + if (!array_key_exists($name, $this->originalParameters)) { + throw new \RuntimeException('Missing Named Parameter ' . $name); + } + + $this->convertedSQL[] = '?'; + $this->convertedParameters[] = $this->originalParameters[$name]; + } + + /** + * Adds a generic SQL string fragment to the converted parts. + * + * @param string $sql + * The SQL part. + */ + private function addOther(string $sql): void { + $this->convertedSQL[] = $sql; + } + + /** + * Returns the converted SQL statement with positional placeholders. + * + * @return string + * The converted SQL statement with positional placeholders. + */ + public function getConvertedSQL(): string { + return implode('', $this->convertedSQL); + } + + /** + * Returns the array of arguments for use with positional placeholders. + * + * @return list<mixed> + * The array of arguments for use with positional placeholders. + */ + public function getConvertedParameters(): array { + return $this->convertedParameters; + } + +} diff --git a/core/modules/mysqli/src/Driver/Database/mysqli/Result.php b/core/modules/mysqli/src/Driver/Database/mysqli/Result.php new file mode 100644 index 000000000000..2c5e57c3aa82 --- /dev/null +++ b/core/modules/mysqli/src/Driver/Database/mysqli/Result.php @@ -0,0 +1,95 @@ +<?php + +declare(strict_types=1); + +namespace Drupal\mysqli\Driver\Database\mysqli; + +use Drupal\Core\Database\DatabaseExceptionWrapper; +use Drupal\Core\Database\FetchModeTrait; +use Drupal\Core\Database\Statement\FetchAs; +use Drupal\Core\Database\Statement\ResultBase; + +/** + * Class for mysqli-provided results of a data query language (DQL) statement. + */ +class Result extends ResultBase { + + use FetchModeTrait; + + /** + * Constructor. + * + * @param \Drupal\Core\Database\Statement\FetchAs $fetchMode + * The fetch mode. + * @param array{class: class-string, constructor_args: list<mixed>, column: int, cursor_orientation?: int, cursor_offset?: int} $fetchOptions + * The fetch options. + * @param \mysqli_result|false $mysqliResult + * The MySQLi result object. + * @param \mysqli $mysqliConnection + * Client database connection object. + */ + public function __construct( + FetchAs $fetchMode, + array $fetchOptions, + protected readonly \mysqli_result|false $mysqliResult, + protected readonly \mysqli $mysqliConnection, + ) { + parent::__construct($fetchMode, $fetchOptions); + } + + /** + * {@inheritdoc} + */ + public function rowCount(): ?int { + // The most accurate value to return for Drupal here is the first + // occurrence of an integer in the string stored by the connection's + // $info property. + // This is something like 'Rows matched: 1 Changed: 1 Warnings: 0' for + // UPDATE or DELETE operations, 'Records: 2 Duplicates: 1 Warnings: 0' + // for INSERT ones. + // This however requires a regex parsing of the string which is expensive; + // $affected_rows would be less accurate but much faster. We would need + // Drupal to be less strict in testing, and never rely on this value in + // runtime (which would be healthy anyway). + if ($this->mysqliConnection->info !== NULL) { + $matches = []; + if (preg_match('/\s(\d+)\s/', $this->mysqliConnection->info, $matches) === 1) { + return (int) $matches[0]; + } + else { + throw new DatabaseExceptionWrapper('Invalid data in the $info property of the mysqli connection - ' . $this->mysqliConnection->info); + } + } + elseif ($this->mysqliConnection->affected_rows !== NULL) { + return $this->mysqliConnection->affected_rows; + } + throw new DatabaseExceptionWrapper('Unable to retrieve affected rows data'); + } + + /** + * {@inheritdoc} + */ + public function setFetchMode(FetchAs $mode, array $fetchOptions): bool { + // There are no methods to set fetch mode in \mysqli_result. + return TRUE; + } + + /** + * {@inheritdoc} + */ + public function fetch(FetchAs $mode, array $fetchOptions): array|object|int|float|string|bool|NULL { + assert($this->mysqliResult instanceof \mysqli_result); + + $mysqli_row = $this->mysqliResult->fetch_assoc(); + + if (!$mysqli_row) { + return FALSE; + } + + // Stringify all non-NULL column values. + $row = array_map(fn ($value) => $value === NULL ? NULL : (string) $value, $mysqli_row); + + return $this->assocToFetchMode($row, $mode, $fetchOptions); + } + +} diff --git a/core/modules/mysqli/src/Driver/Database/mysqli/Statement.php b/core/modules/mysqli/src/Driver/Database/mysqli/Statement.php new file mode 100644 index 000000000000..f3b4346992df --- /dev/null +++ b/core/modules/mysqli/src/Driver/Database/mysqli/Statement.php @@ -0,0 +1,126 @@ +<?php + +declare(strict_types=1); + +namespace Drupal\mysqli\Driver\Database\mysqli; + +use Drupal\Core\Database\Connection; +use Drupal\Core\Database\Statement\FetchAs; +use Drupal\Core\Database\Statement\StatementBase; + +/** + * MySQLi implementation of \Drupal\Core\Database\Query\StatementInterface. + */ +class Statement extends StatementBase { + + /** + * Holds the index position of named parameters. + * + * The mysqli driver only allows positional placeholders '?', whereas in + * Drupal the SQL is generated with named placeholders ':name'. In order to + * execute the SQL, the string containing the named placeholders is converted + * to using positional ones, and the position (index) of each named + * placeholder in the string is stored here. + */ + protected array $paramsPositions; + + /** + * Constructs a Statement object. + * + * @param \Drupal\Core\Database\Connection $connection + * Drupal database connection object. + * @param \mysqli $clientConnection + * Client database connection object. + * @param string $queryString + * The SQL query string. + * @param array $driverOpts + * (optional) Array of query options. + * @param bool $rowCountEnabled + * (optional) Enables counting the rows affected. Defaults to FALSE. + */ + public function __construct( + Connection $connection, + \mysqli $clientConnection, + string $queryString, + protected array $driverOpts = [], + bool $rowCountEnabled = FALSE, + ) { + parent::__construct($connection, $clientConnection, $queryString, $rowCountEnabled); + $this->setFetchMode(FetchAs::Object); + } + + /** + * Returns the client-level database statement object. + * + * This method should normally be used only within database driver code. + * + * @return \mysqli_stmt + * The client-level database statement. + */ + public function getClientStatement(): \mysqli_stmt { + if ($this->hasClientStatement()) { + assert($this->clientStatement instanceof \mysqli_stmt); + return $this->clientStatement; + } + throw new \LogicException('\\mysqli_stmt not initialized'); + } + + /** + * {@inheritdoc} + */ + public function execute($args = [], $options = []) { + if (isset($options['fetch'])) { + if (is_string($options['fetch'])) { + $this->setFetchMode(FetchAs::ClassObject, $options['fetch']); + } + else { + if (is_int($options['fetch'])) { + @trigger_error("Passing the 'fetch' key as an integer to \$options in execute() is deprecated in drupal:11.2.0 and is removed from drupal:12.0.0. Use a case of \Drupal\Core\Database\Statement\FetchAs enum instead. See https://www.drupal.org/node/3488338", E_USER_DEPRECATED); + } + $this->setFetchMode($options['fetch']); + } + } + + $startEvent = $this->dispatchStatementExecutionStartEvent($args ?? []); + + try { + // Prepare the lower-level statement if it's not been prepared already. + if (!$this->hasClientStatement()) { + // Replace named placeholders with positional ones if needed. + $this->paramsPositions = array_flip(array_keys($args)); + $converter = new NamedPlaceholderConverter(); + $converter->parse($this->queryString, $args); + [$convertedQueryString, $args] = [$converter->getConvertedSQL(), $converter->getConvertedParameters()]; + $this->clientStatement = $this->clientConnection->prepare($convertedQueryString); + } + else { + // Transform the $args to positional. + $tmp = []; + foreach ($this->paramsPositions as $param => $pos) { + $tmp[$pos] = $args[$param]; + } + $args = $tmp; + } + + // In mysqli, the results of the statement execution are returned in a + // different object than the statement itself. + $return = $this->getClientStatement()->execute($args); + $this->result = new Result( + $this->fetchMode, + $this->fetchOptions, + $this->getClientStatement()->get_result(), + $this->clientConnection, + ); + $this->markResultsetIterable($return); + } + catch (\Exception $e) { + $this->dispatchStatementExecutionFailureEvent($startEvent, $e); + throw $e; + } + + $this->dispatchStatementExecutionEndEvent($startEvent); + + return $return; + } + +} diff --git a/core/modules/mysqli/src/Driver/Database/mysqli/TransactionManager.php b/core/modules/mysqli/src/Driver/Database/mysqli/TransactionManager.php new file mode 100644 index 000000000000..90237fd6a43c --- /dev/null +++ b/core/modules/mysqli/src/Driver/Database/mysqli/TransactionManager.php @@ -0,0 +1,82 @@ +<?php + +declare(strict_types=1); + +namespace Drupal\mysqli\Driver\Database\mysqli; + +use Drupal\Core\Database\Transaction\ClientConnectionTransactionState; +use Drupal\Core\Database\Transaction\TransactionManagerBase; + +/** + * MySqli implementation of TransactionManagerInterface. + */ +class TransactionManager extends TransactionManagerBase { + + /** + * {@inheritdoc} + */ + protected function beginClientTransaction(): bool { + return $this->connection->getClientConnection()->begin_transaction(); + } + + /** + * {@inheritdoc} + */ + protected function addClientSavepoint(string $name): bool { + return $this->connection->getClientConnection()->savepoint($name); + } + + /** + * {@inheritdoc} + */ + protected function rollbackClientSavepoint(string $name): bool { + // Mysqli does not have a rollback_to_savepoint method, and it does not + // allow a prepared statement for 'ROLLBACK TO SAVEPOINT', so we need to + // fallback to querying on the client connection directly. + try { + return (bool) $this->connection->getClientConnection()->query('ROLLBACK TO SAVEPOINT ' . $name); + } + catch (\mysqli_sql_exception) { + // If the rollback failed, most likely the savepoint was not there + // because the transaction is no longer active. In this case we void the + // transaction stack. + $this->voidClientTransaction(); + return TRUE; + } + } + + /** + * {@inheritdoc} + */ + protected function releaseClientSavepoint(string $name): bool { + return $this->connection->getClientConnection()->release_savepoint($name); + } + + /** + * {@inheritdoc} + */ + protected function rollbackClientTransaction(): bool { + // Note: mysqli::rollback() returns TRUE if there's no active transaction. + // This is diverging from PDO MySql. A PHP bug report exists. + // @see https://bugs.php.net/bug.php?id=81533. + $clientRollback = $this->connection->getClientConnection()->rollBack(); + $this->setConnectionTransactionState($clientRollback ? + ClientConnectionTransactionState::RolledBack : + ClientConnectionTransactionState::RollbackFailed + ); + return $clientRollback; + } + + /** + * {@inheritdoc} + */ + protected function commitClientTransaction(): bool { + $clientCommit = $this->connection->getClientConnection()->commit(); + $this->setConnectionTransactionState($clientCommit ? + ClientConnectionTransactionState::Committed : + ClientConnectionTransactionState::CommitFailed + ); + return $clientCommit; + } + +} diff --git a/core/modules/mysqli/src/Hook/MysqliHooks.php b/core/modules/mysqli/src/Hook/MysqliHooks.php new file mode 100644 index 000000000000..5fae187d16c7 --- /dev/null +++ b/core/modules/mysqli/src/Hook/MysqliHooks.php @@ -0,0 +1,32 @@ +<?php + +namespace Drupal\mysqli\Hook; + +use Drupal\Core\Hook\Attribute\Hook; +use Drupal\Core\Routing\RouteMatchInterface; +use Drupal\Core\StringTranslation\StringTranslationTrait; + +/** + * Hook implementations for mysqli. + */ +class MysqliHooks { + + use StringTranslationTrait; + + /** + * Implements hook_help(). + */ + #[Hook('help')] + public function help($route_name, RouteMatchInterface $route_match): ?string { + switch ($route_name) { + case 'help.page.mysqli': + $output = ''; + $output .= '<h3>' . $this->t('About') . '</h3>'; + $output .= '<p>' . $this->t('The MySQLi module provides the connection between Drupal and a MySQL, MariaDB or equivalent database using the mysqli PHP extension. For more information, see the <a href=":mysqli">online documentation for the MySQLi module</a>.', [':mysqli' => 'https://www.drupal.org/documentation/modules/mysqli']) . '</p>'; + return $output; + + } + return NULL; + } + +} diff --git a/core/modules/mysqli/src/Plugin/views/query/MysqliCastSql.php b/core/modules/mysqli/src/Plugin/views/query/MysqliCastSql.php new file mode 100644 index 000000000000..d1f1ca55f8f5 --- /dev/null +++ b/core/modules/mysqli/src/Plugin/views/query/MysqliCastSql.php @@ -0,0 +1,11 @@ +<?php + +namespace Drupal\mysqli\Plugin\views\query; + +use Drupal\mysql\Plugin\views\query\MysqlCastSql; + +/** + * MySQLi specific cast handling. + */ +class MysqliCastSql extends MysqlCastSql { +} diff --git a/core/modules/mysqli/tests/src/Functional/GenericTest.php b/core/modules/mysqli/tests/src/Functional/GenericTest.php new file mode 100644 index 000000000000..736381069606 --- /dev/null +++ b/core/modules/mysqli/tests/src/Functional/GenericTest.php @@ -0,0 +1,28 @@ +<?php + +declare(strict_types=1); + +namespace Drupal\Tests\mysqli\Functional; + +use Drupal\Core\Extension\ExtensionLifecycle; +use Drupal\Tests\system\Functional\Module\GenericModuleTestBase; +use PHPUnit\Framework\Attributes\Group; + +/** + * Generic module test for mysqli. + */ +#[Group('mysqli')] +class GenericTest extends GenericModuleTestBase { + + /** + * Checks visibility of the module. + */ + public function testMysqliModule(): void { + $module = $this->getModule(); + \Drupal::service('module_installer')->install([$module]); + $info = \Drupal::service('extension.list.module')->getExtensionInfo($module); + $this->assertTrue($info['hidden']); + $this->assertSame(ExtensionLifecycle::EXPERIMENTAL, $info['lifecycle']); + } + +} diff --git a/core/modules/mysqli/tests/src/Kernel/mysqli/ConnectionTest.php b/core/modules/mysqli/tests/src/Kernel/mysqli/ConnectionTest.php new file mode 100644 index 000000000000..c940eb919d33 --- /dev/null +++ b/core/modules/mysqli/tests/src/Kernel/mysqli/ConnectionTest.php @@ -0,0 +1,15 @@ +<?php + +declare(strict_types=1); + +namespace Drupal\Tests\mysqli\Kernel\mysqli; + +use Drupal\Tests\mysql\Kernel\mysql\ConnectionTest as BaseMySqlTest; +use PHPUnit\Framework\Attributes\Group; + +/** + * MySQL-specific connection tests. + */ +#[Group('Database')] +class ConnectionTest extends BaseMySqlTest { +} diff --git a/core/modules/mysqli/tests/src/Kernel/mysqli/ConnectionUnitTest.php b/core/modules/mysqli/tests/src/Kernel/mysqli/ConnectionUnitTest.php new file mode 100644 index 000000000000..42fa5d733dfd --- /dev/null +++ b/core/modules/mysqli/tests/src/Kernel/mysqli/ConnectionUnitTest.php @@ -0,0 +1,23 @@ +<?php + +declare(strict_types=1); + +namespace Drupal\Tests\mysqli\Kernel\mysqli; + +use Drupal\Tests\mysql\Kernel\mysql\ConnectionUnitTest as BaseMySqlTest; +use PHPUnit\Framework\Attributes\Group; + +/** + * MySQL-specific connection unit tests. + */ +#[Group('Database')] +class ConnectionUnitTest extends BaseMySqlTest { + + /** + * Tests pdo options override. + */ + public function testConnectionOpen(): void { + $this->markTestSkipped('mysqli is not a pdo driver.'); + } + +} diff --git a/core/modules/mysqli/tests/src/Kernel/mysqli/DatabaseExceptionWrapperTest.php b/core/modules/mysqli/tests/src/Kernel/mysqli/DatabaseExceptionWrapperTest.php new file mode 100644 index 000000000000..2e27fff09f5a --- /dev/null +++ b/core/modules/mysqli/tests/src/Kernel/mysqli/DatabaseExceptionWrapperTest.php @@ -0,0 +1,38 @@ +<?php + +declare(strict_types=1); + +namespace Drupal\Tests\mysqli\Kernel\mysqli; + +use Drupal\Tests\mysql\Kernel\mysql\DatabaseExceptionWrapperTest as BaseMySqlTest; +use PHPUnit\Framework\Attributes\Group; + +/** + * Tests exceptions thrown by queries. + */ +#[Group('Database')] +class DatabaseExceptionWrapperTest extends BaseMySqlTest { + + /** + * Tests Connection::prepareStatement exceptions on preparation. + * + * Core database drivers use PDO emulated statements or the StatementPrefetch + * class, which defer the statement check to the moment of the execution. In + * order to test a failure at preparation time, we have to force the + * connection not to emulate statement preparation. Still, this is only valid + * for the MySql driver. + */ + public function testPrepareStatementFailOnPreparation(): void { + $this->markTestSkipped('mysqli is not a pdo driver.'); + } + + /** + * Tests Connection::prepareStatement exception on execution. + */ + public function testPrepareStatementFailOnExecution(): void { + $this->expectException(\mysqli_sql_exception::class); + $stmt = $this->connection->prepareStatement('bananas', []); + $stmt->execute(); + } + +} diff --git a/core/modules/mysqli/tests/src/Kernel/mysqli/LargeQueryTest.php b/core/modules/mysqli/tests/src/Kernel/mysqli/LargeQueryTest.php new file mode 100644 index 000000000000..ead54a27c012 --- /dev/null +++ b/core/modules/mysqli/tests/src/Kernel/mysqli/LargeQueryTest.php @@ -0,0 +1,52 @@ +<?php + +declare(strict_types=1); + +namespace Drupal\Tests\mysqli\Kernel\mysqli; + +use Drupal\Component\Utility\Environment; +use Drupal\Core\Database\Database; +use Drupal\Core\Database\DatabaseExceptionWrapper; +use Drupal\Tests\mysql\Kernel\mysql\LargeQueryTest as BaseMySqlTest; +use PHPUnit\Framework\Attributes\Group; + +/** + * Tests handling of large queries. + */ +#[Group('Database')] +class LargeQueryTest extends BaseMySqlTest { + + /** + * Tests truncation of messages when max_allowed_packet exception occurs. + */ + public function testMaxAllowedPacketQueryTruncating(): void { + $connectionInfo = Database::getConnectionInfo(); + Database::addConnectionInfo('default', 'testMaxAllowedPacketQueryTruncating', $connectionInfo['default']); + $testConnection = Database::getConnection('testMaxAllowedPacketQueryTruncating'); + + // The max_allowed_packet value is configured per database instance. + // Retrieve the max_allowed_packet value from the current instance and + // check if PHP is configured with sufficient allowed memory to be able + // to generate a query larger than max_allowed_packet. + $max_allowed_packet = $testConnection->query('SELECT @@global.max_allowed_packet')->fetchField(); + if (!Environment::checkMemoryLimit($max_allowed_packet + (16 * 1024 * 1024))) { + $this->markTestSkipped('The configured max_allowed_packet exceeds the php memory limit. Therefore the test is skipped.'); + } + + $long_name = str_repeat('a', $max_allowed_packet + 1); + try { + $testConnection->query('SELECT [name] FROM {test} WHERE [name] = :name', [':name' => $long_name]); + $this->fail("An exception should be thrown for queries larger than 'max_allowed_packet'"); + } + catch (\Throwable $e) { + Database::closeConnection('testMaxAllowedPacketQueryTruncating'); + // Got a packet bigger than 'max_allowed_packet' bytes exception thrown. + $this->assertInstanceOf(DatabaseExceptionWrapper::class, $e); + $this->assertEquals(1153, $e->getPrevious()->getCode()); + // 'max_allowed_packet' exception message truncated. + // Use strlen() to count the bytes exactly, not the Unicode chars. + $this->assertLessThanOrEqual($max_allowed_packet, strlen($e->getMessage())); + } + } + +} diff --git a/core/modules/mysqli/tests/src/Kernel/mysqli/PrefixInfoTest.php b/core/modules/mysqli/tests/src/Kernel/mysqli/PrefixInfoTest.php new file mode 100644 index 000000000000..894245826cb3 --- /dev/null +++ b/core/modules/mysqli/tests/src/Kernel/mysqli/PrefixInfoTest.php @@ -0,0 +1,15 @@ +<?php + +declare(strict_types=1); + +namespace Drupal\Tests\mysqli\Kernel\mysqli; + +use Drupal\Tests\mysql\Kernel\mysql\PrefixInfoTest as BaseMySqlTest; +use PHPUnit\Framework\Attributes\Group; + +/** + * Tests that the prefix info for a database schema is correct. + */ +#[Group('Database')] +class PrefixInfoTest extends BaseMySqlTest { +} diff --git a/core/modules/mysqli/tests/src/Kernel/mysqli/SchemaTest.php b/core/modules/mysqli/tests/src/Kernel/mysqli/SchemaTest.php new file mode 100644 index 000000000000..23fa565156fb --- /dev/null +++ b/core/modules/mysqli/tests/src/Kernel/mysqli/SchemaTest.php @@ -0,0 +1,15 @@ +<?php + +declare(strict_types=1); + +namespace Drupal\Tests\mysqli\Kernel\mysqli; + +use Drupal\Tests\mysql\Kernel\mysql\SchemaTest as BaseMySqlTest; +use PHPUnit\Framework\Attributes\Group; + +/** + * Tests schema API for the MySQL driver. + */ +#[Group('Database')] +class SchemaTest extends BaseMySqlTest { +} diff --git a/core/modules/mysqli/tests/src/Kernel/mysqli/SqlModeTest.php b/core/modules/mysqli/tests/src/Kernel/mysqli/SqlModeTest.php new file mode 100644 index 000000000000..7bbf1b85b391 --- /dev/null +++ b/core/modules/mysqli/tests/src/Kernel/mysqli/SqlModeTest.php @@ -0,0 +1,43 @@ +<?php + +declare(strict_types=1); + +namespace Drupal\Tests\mysqli\Kernel\mysqli; + +use Drupal\KernelTests\Core\Database\DriverSpecificDatabaseTestBase; +use PHPUnit\Framework\Attributes\Group; + +/** + * Tests compatibility of the MySQL driver with various sql_mode options. + */ +#[Group('Database')] +class SqlModeTest extends DriverSpecificDatabaseTestBase { + + /** + * Tests quoting identifiers in queries. + */ + public function testQuotingIdentifiers(): void { + // Use SQL-reserved words for both the table and column names. + $query = $this->connection->query('SELECT [update] FROM {select}'); + $this->assertEquals('Update value 1', $query->fetchObject()->update); + $this->assertStringContainsString('SELECT `update` FROM `', $query->getQueryString()); + } + + /** + * {@inheritdoc} + */ + protected function getDatabaseConnectionInfo() { + $info = parent::getDatabaseConnectionInfo(); + + // This runs during setUp(), so is not yet skipped for non MySQL databases. + // We defer skipping the test to later in setUp(), so that that can be + // based on databaseType() rather than 'driver', but here all we have to go + // on is 'driver'. + if ($info['default']['driver'] === 'mysqli') { + $info['default']['init_commands']['sql_mode'] = "SET sql_mode = ''"; + } + + return $info; + } + +} diff --git a/core/modules/mysqli/tests/src/Kernel/mysqli/SyntaxTest.php b/core/modules/mysqli/tests/src/Kernel/mysqli/SyntaxTest.php new file mode 100644 index 000000000000..7ccdcf1022f9 --- /dev/null +++ b/core/modules/mysqli/tests/src/Kernel/mysqli/SyntaxTest.php @@ -0,0 +1,28 @@ +<?php + +declare(strict_types=1); + +namespace Drupal\Tests\mysqli\Kernel\mysqli; + +use Drupal\KernelTests\Core\Database\DriverSpecificSyntaxTestBase; +use PHPUnit\Framework\Attributes\Group; + +/** + * Tests MySql syntax interpretation. + */ +#[Group('Database')] +class SyntaxTest extends DriverSpecificSyntaxTestBase { + + /** + * Tests string concatenation with separator, with field values. + */ + public function testConcatWsFields(): void { + $result = $this->connection->query("SELECT CONCAT_WS('-', CONVERT(:a1 USING utf8mb4), [name], CONVERT(:a2 USING utf8mb4), [age]) FROM {test} WHERE [age] = :age", [ + ':a1' => 'name', + ':a2' => 'age', + ':age' => 25, + ]); + $this->assertSame('name-John-age-25', $result->fetchField()); + } + +} diff --git a/core/modules/mysqli/tests/src/Kernel/mysqli/TemporaryQueryTest.php b/core/modules/mysqli/tests/src/Kernel/mysqli/TemporaryQueryTest.php new file mode 100644 index 000000000000..19539fa65877 --- /dev/null +++ b/core/modules/mysqli/tests/src/Kernel/mysqli/TemporaryQueryTest.php @@ -0,0 +1,15 @@ +<?php + +declare(strict_types=1); + +namespace Drupal\Tests\mysqli\Kernel\mysqli; + +use Drupal\Tests\mysql\Kernel\mysql\TemporaryQueryTest as BaseMySqlTest; +use PHPUnit\Framework\Attributes\Group; + +/** + * Tests the temporary query functionality. + */ +#[Group('Database')] +class TemporaryQueryTest extends BaseMySqlTest { +} diff --git a/core/modules/mysqli/tests/src/Kernel/mysqli/TransactionTest.php b/core/modules/mysqli/tests/src/Kernel/mysqli/TransactionTest.php new file mode 100644 index 000000000000..60f6c27540dc --- /dev/null +++ b/core/modules/mysqli/tests/src/Kernel/mysqli/TransactionTest.php @@ -0,0 +1,54 @@ +<?php + +declare(strict_types=1); + +namespace Drupal\Tests\mysqli\Kernel\mysqli; + +use Drupal\KernelTests\Core\Database\DriverSpecificTransactionTestBase; +use PHPUnit\Framework\Attributes\Group; + +/** + * Tests transaction for the MySQLi driver. + */ +#[Group('Database')] +class TransactionTest extends DriverSpecificTransactionTestBase { + + /** + * Tests starting a transaction when there's one active on the client. + * + * MySQLi does not fail if multiple commits are made on the client, so this + * test is failing. Let's change this if/when MySQLi will provide a way to + * check if a client transaction is active. + * + * This is mitigated by the fact that transaction should not be initiated from + * code outside the TransactionManager, that keeps track of the stack of + * transaction-related operations in its stack. + */ + public function testStartTransactionWhenActive(): void { + $this->markTestSkipped('Skipping this while MySQLi cannot detect if a client transaction is active.'); + $this->connection->getClientConnection()->begin_transaction(); + $this->connection->startTransaction(); + $this->assertFalse($this->connection->inTransaction()); + } + + /** + * Tests committing a transaction when there's none active on the client. + * + * MySQLi does not fail if multiple commits are made on the client, so this + * test is failing. Let's change this if/when MySQLi will provide a way to + * check if a client transaction is active. + * + * This is mitigated by the fact that transaction should not be initiated from + * code outside the TransactionManager, that keeps track of the stack of + * transaction-related operations in its stack. + */ + public function testCommitTransactionWhenInactive(): void { + $this->markTestSkipped('Skipping this while MySQLi cannot detect if a client transaction is active.'); + $transaction = $this->connection->startTransaction(); + $this->assertTrue($this->connection->inTransaction()); + $this->connection->getClientConnection()->commit(); + $this->assertFalse($this->connection->inTransaction()); + unset($transaction); + } + +} diff --git a/core/modules/mysqli/tests/src/Unit/NamedPlaceholderConverterTest.php b/core/modules/mysqli/tests/src/Unit/NamedPlaceholderConverterTest.php new file mode 100644 index 000000000000..a000a132e203 --- /dev/null +++ b/core/modules/mysqli/tests/src/Unit/NamedPlaceholderConverterTest.php @@ -0,0 +1,400 @@ +<?php + +declare(strict_types=1); + +namespace Drupal\Tests\mysqli\Unit; + +use Drupal\mysqli\Driver\Database\mysqli\NamedPlaceholderConverter; +use Drupal\Tests\UnitTestCase; +use PHPUnit\Framework\Attributes\CoversClass; +use PHPUnit\Framework\Attributes\DataProvider; +use PHPUnit\Framework\Attributes\Group; + +/** + * Tests \Drupal\mysqli\Driver\Database\mysqli\NamedPlaceholderConverter. + */ +#[CoversClass(NamedPlaceholderConverter::class)] +#[Group('Database')] +class NamedPlaceholderConverterTest extends UnitTestCase { + + /** + * Tests ::parse(). + * + * @legacy-covers ::parse + * @legacy-covers ::getConvertedSQL + * @legacy-covers ::getConvertedParameters + */ + #[DataProvider('statementsWithParametersProvider')] + public function testParse(string $sql, array $parameters, string $expectedSql, array $expectedParameters): void { + $converter = new NamedPlaceholderConverter(); + $converter->parse($sql, $parameters); + $this->assertSame($expectedSql, $converter->getConvertedSQL()); + $this->assertSame($expectedParameters, $converter->getConvertedParameters()); + } + + /** + * Data for testParse. + */ + public static function statementsWithParametersProvider(): iterable { + yield [ + 'SELECT ?', + ['foo'], + 'SELECT ?', + ['foo'], + ]; + + yield [ + 'SELECT * FROM Foo WHERE bar IN (?, ?, ?)', + ['baz', 'qux', 'fred'], + 'SELECT * FROM Foo WHERE bar IN (?, ?, ?)', + ['baz', 'qux', 'fred'], + ]; + + yield [ + 'SELECT ? FROM ?', + ['baz', 'qux'], + 'SELECT ? FROM ?', + ['baz', 'qux'], + ]; + + yield [ + 'SELECT "?" FROM foo WHERE bar = ?', + ['baz'], + 'SELECT "?" FROM foo WHERE bar = ?', + ['baz'], + ]; + + yield [ + "SELECT '?' FROM foo WHERE bar = ?", + ['baz'], + "SELECT '?' FROM foo WHERE bar = ?", + ['baz'], + ]; + + yield [ + 'SELECT `?` FROM foo WHERE bar = ?', + ['baz'], + 'SELECT `?` FROM foo WHERE bar = ?', + ['baz'], + ]; + + yield [ + 'SELECT [?] FROM foo WHERE bar = ?', + ['baz'], + 'SELECT [?] FROM foo WHERE bar = ?', + ['baz'], + ]; + + yield [ + 'SELECT * FROM foo WHERE jsonb_exists_any(foo.bar, ARRAY[?])', + ['baz'], + 'SELECT * FROM foo WHERE jsonb_exists_any(foo.bar, ARRAY[?])', + ['baz'], + ]; + + yield [ + "SELECT 'foo-bar?' FROM foo WHERE bar = ?", + ['baz'], + "SELECT 'foo-bar?' FROM foo WHERE bar = ?", + ['baz'], + ]; + + yield [ + 'SELECT "foo-bar?" FROM foo WHERE bar = ?', + ['baz'], + 'SELECT "foo-bar?" FROM foo WHERE bar = ?', + ['baz'], + ]; + + yield [ + 'SELECT `foo-bar?` FROM foo WHERE bar = ?', + ['baz'], + 'SELECT `foo-bar?` FROM foo WHERE bar = ?', + ['baz'], + ]; + + yield [ + 'SELECT [foo-bar?] FROM foo WHERE bar = ?', + ['baz'], + 'SELECT [foo-bar?] FROM foo WHERE bar = ?', + ['baz'], + ]; + + yield [ + 'SELECT :foo FROM :bar', + [':foo' => 'baz', ':bar' => 'qux'], + 'SELECT ? FROM ?', + ['baz', 'qux'], + ]; + + yield [ + 'SELECT * FROM Foo WHERE bar IN (:name1, :name2)', + [':name1' => 'baz', ':name2' => 'qux'], + 'SELECT * FROM Foo WHERE bar IN (?, ?)', + ['baz', 'qux'], + ]; + + yield [ + 'SELECT ":foo" FROM Foo WHERE bar IN (:name1, :name2)', + [':name1' => 'baz', ':name2' => 'qux'], + 'SELECT ":foo" FROM Foo WHERE bar IN (?, ?)', + ['baz', 'qux'], + ]; + + yield [ + "SELECT ':foo' FROM Foo WHERE bar IN (:name1, :name2)", + [':name1' => 'baz', ':name2' => 'qux'], + "SELECT ':foo' FROM Foo WHERE bar IN (?, ?)", + ['baz', 'qux'], + ]; + + yield [ + 'SELECT :foo_id', + [':foo_id' => 'bar'], + 'SELECT ?', + ['bar'], + ]; + + yield [ + 'SELECT @rank := 1 AS rank, :foo AS foo FROM :bar', + [':foo' => 'baz', ':bar' => 'qux'], + 'SELECT @rank := 1 AS rank, ? AS foo FROM ?', + ['baz', 'qux'], + ]; + + yield [ + 'SELECT * FROM Foo WHERE bar > :start_date AND baz > :start_date', + [':start_date' => 'qux'], + 'SELECT * FROM Foo WHERE bar > ? AND baz > ?', + ['qux', 'qux'], + ]; + + yield [ + 'SELECT foo::date as date FROM Foo WHERE bar > :start_date AND baz > :start_date', + [':start_date' => 'qux'], + 'SELECT foo::date as date FROM Foo WHERE bar > ? AND baz > ?', + ['qux', 'qux'], + ]; + + yield [ + 'SELECT `d.ns:col_name` FROM my_table d WHERE `d.date` >= :param1', + [':param1' => 'qux'], + 'SELECT `d.ns:col_name` FROM my_table d WHERE `d.date` >= ?', + ['qux'], + ]; + + yield [ + 'SELECT [d.ns:col_name] FROM my_table d WHERE [d.date] >= :param1', + [':param1' => 'qux'], + 'SELECT [d.ns:col_name] FROM my_table d WHERE [d.date] >= ?', + ['qux'], + ]; + + yield [ + 'SELECT * FROM foo WHERE jsonb_exists_any(foo.bar, ARRAY[:foo])', + [':foo' => 'qux'], + 'SELECT * FROM foo WHERE jsonb_exists_any(foo.bar, ARRAY[?])', + ['qux'], + ]; + + yield [ + 'SELECT * FROM foo WHERE jsonb_exists_any(foo.bar, array[:foo])', + [':foo' => 'qux'], + 'SELECT * FROM foo WHERE jsonb_exists_any(foo.bar, array[?])', + ['qux'], + ]; + + yield [ + "SELECT table.column1, ARRAY['3'] FROM schema.table table WHERE table.f1 = :foo AND ARRAY['3']", + [':foo' => 'qux'], + "SELECT table.column1, ARRAY['3'] FROM schema.table table WHERE table.f1 = ? AND ARRAY['3']", + ['qux'], + ]; + + yield [ + "SELECT table.column1, ARRAY['3']::integer[] FROM schema.table table WHERE table.f1 = :foo AND ARRAY['3']::integer[]", + [':foo' => 'qux'], + "SELECT table.column1, ARRAY['3']::integer[] FROM schema.table table WHERE table.f1 = ? AND ARRAY['3']::integer[]", + ['qux'], + ]; + + yield [ + "SELECT table.column1, ARRAY[:foo] FROM schema.table table WHERE table.f1 = :bar AND ARRAY['3']", + [':foo' => 'qux', ':bar' => 'git'], + "SELECT table.column1, ARRAY[?] FROM schema.table table WHERE table.f1 = ? AND ARRAY['3']", + ['qux', 'git'], + ]; + + yield [ + 'SELECT table.column1, ARRAY[:foo]::integer[] FROM schema.table table' . " WHERE table.f1 = :bar AND ARRAY['3']::integer[]", + [':foo' => 'qux', ':bar' => 'git'], + 'SELECT table.column1, ARRAY[?]::integer[] FROM schema.table table' . " WHERE table.f1 = ? AND ARRAY['3']::integer[]", + ['qux', 'git'], + ]; + + yield 'Parameter array with placeholder keys missing starting colon' => [ + 'SELECT table.column1, ARRAY[:foo]::integer[] FROM schema.table table' . " WHERE table.f1 = :bar AND ARRAY['3']::integer[]", + ['foo' => 'qux', 'bar' => 'git'], + 'SELECT table.column1, ARRAY[?]::integer[] FROM schema.table table' . " WHERE table.f1 = ? AND ARRAY['3']::integer[]", + ['qux', 'git'], + ]; + + yield 'Quotes inside literals escaped by doubling' => [ + <<<'SQL' +SELECT * FROM foo +WHERE bar = ':not_a_param1 ''":not_a_param2"''' + OR bar=:a_param1 + OR bar=:a_param2||':not_a_param3' + OR bar=':not_a_param4 '':not_a_param5'' :not_a_param6' + OR bar='' + OR bar=:a_param3 +SQL, + [':a_param1' => 'qux', ':a_param2' => 'git', ':a_param3' => 'foo'], + <<<'SQL' +SELECT * FROM foo +WHERE bar = ':not_a_param1 ''":not_a_param2"''' + OR bar=? + OR bar=?||':not_a_param3' + OR bar=':not_a_param4 '':not_a_param5'' :not_a_param6' + OR bar='' + OR bar=? +SQL, + ['qux', 'git', 'foo'], + ]; + + yield [ + 'SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id FROM test_data data WHERE (data.description LIKE :condition_0 ESCAPE \'\\\\\') AND (data.description LIKE :condition_1 ESCAPE \'\\\\\') ORDER BY id ASC', + [':condition_0' => 'qux', ':condition_1' => 'git'], + 'SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id FROM test_data data WHERE (data.description LIKE ? ESCAPE \'\\\\\') AND (data.description LIKE ? ESCAPE \'\\\\\') ORDER BY id ASC', + ['qux', 'git'], + ]; + + yield [ + 'SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id FROM test_data data WHERE (data.description LIKE :condition_0 ESCAPE "\\\\") AND (data.description LIKE :condition_1 ESCAPE "\\\\") ORDER BY id ASC', + [':condition_0' => 'qux', ':condition_1' => 'git'], + 'SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id FROM test_data data WHERE (data.description LIKE ? ESCAPE "\\\\") AND (data.description LIKE ? ESCAPE "\\\\") ORDER BY id ASC', + ['qux', 'git'], + ]; + + yield 'Combined single and double quotes' => [ + <<<'SQL' +SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id + FROM test_data data + WHERE (data.description LIKE :condition_0 ESCAPE "\\") + AND (data.description LIKE :condition_1 ESCAPE '\\') ORDER BY id ASC +SQL, + [':condition_0' => 'qux', ':condition_1' => 'git'], + <<<'SQL' +SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id + FROM test_data data + WHERE (data.description LIKE ? ESCAPE "\\") + AND (data.description LIKE ? ESCAPE '\\') ORDER BY id ASC +SQL, + ['qux', 'git'], + ]; + + yield [ + 'SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id FROM test_data data WHERE (data.description LIKE :condition_0 ESCAPE `\\\\`) AND (data.description LIKE :condition_1 ESCAPE `\\\\`) ORDER BY id ASC', + [':condition_0' => 'qux', ':condition_1' => 'git'], + 'SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id FROM test_data data WHERE (data.description LIKE ? ESCAPE `\\\\`) AND (data.description LIKE ? ESCAPE `\\\\`) ORDER BY id ASC', + ['qux', 'git'], + ]; + + yield 'Combined single quotes and backticks' => [ + <<<'SQL' +SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id + FROM test_data data + WHERE (data.description LIKE :condition_0 ESCAPE '\\') + AND (data.description LIKE :condition_1 ESCAPE `\\`) ORDER BY id ASC +SQL, + [':condition_0' => 'qux', ':condition_1' => 'git'], + <<<'SQL' +SELECT data.age AS age, data.id AS id, data.name AS name, data.id AS id + FROM test_data data + WHERE (data.description LIKE ? ESCAPE '\\') + AND (data.description LIKE ? ESCAPE `\\`) ORDER BY id ASC +SQL, + ['qux', 'git'], + ]; + + yield '? placeholders inside comments' => [ + <<<'SQL' +/* + * test placeholder ? + */ +SELECT dummy as "dummy?" + FROM DUAL + WHERE '?' = '?' +-- AND dummy <> ? + AND dummy = ? +SQL, + ['baz'], + <<<'SQL' +/* + * test placeholder ? + */ +SELECT dummy as "dummy?" + FROM DUAL + WHERE '?' = '?' +-- AND dummy <> ? + AND dummy = ? +SQL, + ['baz'], + ]; + + yield 'Named placeholders inside comments' => [ + <<<'SQL' +/* + * test :placeholder + */ +SELECT dummy as "dummy?" + FROM DUAL + WHERE '?' = '?' +-- AND dummy <> :dummy + AND dummy = :key +SQL, + [':key' => 'baz'], + <<<'SQL' +/* + * test :placeholder + */ +SELECT dummy as "dummy?" + FROM DUAL + WHERE '?' = '?' +-- AND dummy <> :dummy + AND dummy = ? +SQL, + ['baz'], + ]; + + yield 'Escaped question' => [ + <<<'SQL' +SELECT '{"a":null}'::jsonb ?? :key +SQL, + [':key' => 'qux'], + <<<'SQL' +SELECT '{"a":null}'::jsonb ?? ? +SQL, + ['qux'], + ]; + } + + /** + * Tests reusing the parser object. + * + * @legacy-covers ::parse + * @legacy-covers ::getConvertedSQL + * @legacy-covers ::getConvertedParameters + */ + public function testParseReuseObject(): void { + $converter = new NamedPlaceholderConverter(); + $converter->parse('SELECT ?', ['foo']); + $this->assertSame('SELECT ?', $converter->getConvertedSQL()); + $this->assertSame(['foo'], $converter->getConvertedParameters()); + + $this->expectException(\RuntimeException::class); + $this->expectExceptionMessage('Missing Positional Parameter 0'); + $converter->parse('SELECT ?', []); + } + +} |