File manager

File manager - Edit - /home/opticamezl/www/newok/Sqlsrv.tar

Back
SqlsrvDriver.php��0000644��00000053316�15174223451�0007737 0��ustar�00��<?php /** * Part of the Joomla Framework Database Package * * @copyright Copyright (C) 2005 - 2021 Open Source Matters, Inc. All rights reserved. * @license GNU General Public License version 2 or later; see LICENSE / namespace Joomla\Database\Sqlsrv; use Joomla\Database\DatabaseDriver; use Joomla\Database\DatabaseEvents; use Joomla\Database\Event\ConnectionEvent; use Joomla\Database\Exception\ConnectionFailureException; use Joomla\Database\Exception\ExecutionFailureException; use Joomla\Database\Exception\PrepareStatementFailureException; use Joomla\Database\Exception\UnsupportedAdapterException; use Joomla\Database\StatementInterface; /* * SQL Server Database Driver * * @link https://www.php.net/manual/en/book.sqlsrv.php * @since 1.0 / class SqlsrvDriver extends DatabaseDriver { /* * The name of the database driver. * * @var string * @since 1.0 / public $name = 'sqlsrv'; /* * The character(s) used to quote SQL statement names such as table names or field names, etc. * * If a single character string the same character is used for both sides of the quoted name, else the first character will be used for the * opening quote and the second for the closing quote. * * @var string * @since 1.0 / protected $nameQuote = '[]'; /* * The null or zero representation of a timestamp for the database driver. * * @var string * @since 1.0 / protected $nullDate = '1900-01-01 00:00:00'; /* * The minimum supported database version. * * @var string * @since 1.0 / protected static $dbMinimum = '11.0.2100.60'; /* * Test to see if the SQLSRV connector is available. * * @return boolean True on success, false otherwise. * * @since 1.0 / public static function isSupported() { return \function_exists('sqlsrv_connect'); } /* * Constructor. * * @param array $options List of options used to configure the connection * * @since 1.0 / public function __construct(array $options) { // Get some basic values from the options. $options['host'] = $options['host'] ?? 'localhost'; $options['user'] = $options['user'] ?? ''; $options['password'] = $options['password'] ?? ''; $options['database'] = $options['database'] ?? ''; $options['select'] = isset($options['select']) ? (bool) $options['select'] : true; $options['trust_certificate'] = isset($options['trust_certificate']) ? (bool) $options['trust_certificate'] : false; // Finalize initialisation parent::__construct($options); } /* * Connects to the database if needed. * * @return void Returns void if the database connected successfully. * * @since 1.0 * @throws \RuntimeException / public function connect() { if ($this->connection) { return; } // Make sure the SQLSRV extension for PHP is installed and enabled. if (!static::isSupported()) { throw new UnsupportedAdapterException('PHP extension sqlsrv_connect is not available.'); } // Build the connection configuration array. $config = [ 'Database' => $this->options['database'], 'uid' => $this->options['user'], 'pwd' => $this->options['password'], 'CharacterSet' => 'UTF-8', 'ReturnDatesAsStrings' => true, 'TrustServerCertificate' => $this->options['trust_certificate'], ]; // Attempt to connect to the server. if (!($this->connection = @ sqlsrv_connect($this->options['host'], $config))) { throw new ConnectionFailureException('Could not connect to SQL Server'); } // Make sure that DB warnings are not returned as errors. sqlsrv_configure('WarningsReturnAsErrors', 0); // If auto-select is enabled select the given database. if ($this->options['select'] && !empty($this->options['database'])) { $this->select($this->options['database']); } $this->dispatchEvent(new ConnectionEvent(DatabaseEvents::POST_CONNECT, $this)); } /* * Disconnects the database. * * @return void * * @since 1.0 / public function disconnect() { // Close the connection. if (\is_resource($this->connection)) { sqlsrv_close($this->connection); } parent::disconnect(); } /* * Get table constraints * * @param string $tableName The name of the database table. * * @return array Any constraints available for the table. * * @since 1.0 / protected function getTableConstraints($tableName) { $this->connect(); return $this->setQuery('SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = ' . $this->quote($tableName)) ->loadColumn(); } /* * Rename constraints. * * @param array $constraints Array(strings) of table constraints * @param string $prefix A string * @param string $backup A string * * @return void * * @since 1.0 / protected function renameConstraints($constraints = [], $prefix = null, $backup = null) { $this->connect(); foreach ($constraints as $constraint) { $this->setQuery('sp_rename ' . $constraint . ',' . str_replace($prefix, $backup, $constraint)) ->execute(); } } /* * Method to escape a string for usage in an SQL statement. * * The escaping for MSSQL isn't handled in the driver though that would be nice. Because of this we need to handle the escaping ourselves. * * @param string $text The string to be escaped. * @param boolean $extra Optional parameter to provide extra escaping. * * @return string The escaped string. * * @since 1.0 / public function escape($text, $extra = false) { if (\is_int($text)) { return $text; } if (\is_float($text)) { // Force the dot as a decimal point. return str_replace(',', '.', $text); } $result = str_replace("'", "''", $text); // SQL Server does not accept NULL byte in query string $result = str_replace("\0", "' + CHAR(0) + N'", $result); // Fix for SQL Sever escape sequence, see https://support.microsoft.com/en-us/kb/164291 $result = str_replace( ["\\\n", "\\\r", "\\\\\r\r\n"], ["\\\\\n\n", "\\\\\r\r", "\\\\\r\n\r\n"], $result ); if ($extra) { // Escape special chars $result = str_replace( ['[', '_', '%'], ['[[]', '[_]', '[%]'], $result ); } return $result; } /* * Quotes and optionally escapes a string to database requirements for use in database queries. * * @param mixed $text A string or an array of strings to quote. * @param boolean $escape True (default) to escape the string, false to leave it unchanged. * * @return string The quoted input string. * * @since 1.6.0 / public function quote($text, $escape = true) { if (\is_array($text)) { return parent::quote($text, $escape); } // To support unicode on MSSQL we have to add prefix N return 'N\'' . ($escape ? $this->escape($text) : $text) . '\''; } /* * Quotes a binary string to database requirements for use in database queries. * * @param string $data A binary string to quote. * * @return string The binary quoted input string. * * @since 1.7.0 / public function quoteBinary($data) { // ODBC syntax for hexadecimal literals return '0x' . bin2hex($data); } /* * Determines if the connection to the server is active. * * @return boolean True if connected to the database engine. * * @since 1.0 / public function connected() { // TODO: Run a blank query here return true; } /* * Drops a table from the database. * * @param string $table The name of the database table to drop. * @param boolean $ifExists Optionally specify that the table must exist before it is dropped. * * @return $this * * @since 1.0 / public function dropTable($table, $ifExists = true) { $this->connect(); if ($ifExists) { $this->setQuery( 'IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ' . $this->quote($table) . ') DROP TABLE ' . $table ); } else { $this->setQuery('DROP TABLE ' . $table); } $this->execute(); return $this; } /* * Method to get the database collation in use by sampling a text field of a table in the database. * * @return string\|boolean The collation in use by the database or boolean false if not supported. * * @since 1.0 / public function getCollation() { // TODO: Not fake this return 'MSSQL UTF-8 (UCS2)'; } /* * Method to get the database connection collation in use by sampling a text field of a table in the database. * * @return string\|boolean The collation in use by the database connection (string) or boolean false if not supported. * * @since 1.6.0 * @throws \RuntimeException / public function getConnectionCollation() { // TODO: Not fake this return 'MSSQL UTF-8 (UCS2)'; } /* * Method to get the database encryption details (cipher and protocol) in use. * * @return string The database encryption details. * * @since 2.0.0 * @throws \RuntimeException / public function getConnectionEncryption(): string { // TODO: Not fake this return ''; } /* * Method to test if the database TLS connections encryption are supported. * * @return boolean Whether the database supports TLS connections encryption. * * @since 2.0.0 / public function isConnectionEncryptionSupported(): bool { // TODO: Not fake this return false; } /* * Retrieves field information about the given tables. * * @param mixed $table A table name * @param boolean $typeOnly True to only return field types. * * @return array An array of fields. * * @since 1.0 * @throws \RuntimeException / public function getTableColumns($table, $typeOnly = true) { $result = []; $table_temp = $this->replacePrefix((string) $table); // Set the query to get the table fields statement. $this->setQuery( 'SELECT column_name as Field, data_type as Type, is_nullable as \'Null\', column_default as \'Default\'' . ' FROM information_schema.columns WHERE table_name = ' . $this->quote($table_temp) ); $fields = $this->loadObjectList(); // If we only want the type as the value add just that to the list. if ($typeOnly) { foreach ($fields as $field) { $result[$field->Field] = preg_replace('/[(0-9)]/', '', $field->Type); } } else { // If we want the whole field data object add that to the list. foreach ($fields as $field) { $field->Default = preg_replace("/(^($\(\|\('\|\(N'\|\()\|(('$\|(?<!$)$\)\|\))$))/i", '', $field->Default); $result[$field->Field] = $field; } } return $result; } /* * Shows the table CREATE statement that creates the given tables. * * This is unsupported by MSSQL. * * @param mixed $tables A table name or a list of table names. * * @return array A list of the create SQL for the tables. * * @since 1.0 * @throws \RuntimeException / public function getTableCreate($tables) { $this->connect(); return ''; } /* * Get the details list of keys for a table. * * @param string $table The name of the table. * * @return array An array of the column specification for the table. * * @since 1.0 * @throws \RuntimeException / public function getTableKeys($table) { $this->connect(); // TODO To implement. return []; } /* * Method to get an array of all tables in the database. * * @return array An array of all the tables in the database. * * @since 1.0 * @throws \RuntimeException / public function getTableList() { $this->connect(); // Set the query to get the tables statement. return $this->setQuery('SELECT name FROM ' . $this->getDatabase() . '.sys.Tables WHERE type = \'U\';')->loadColumn(); } /* * Get the version of the database connector. * * @return string The database connector version. * * @since 1.0 / public function getVersion() { $this->connect(); $version = sqlsrv_server_info($this->connection); return $version['SQLServerVersion']; } /* * Inserts a row into a table based on an object's properties. * * @param string $table The name of the database table to insert into. * @param object $object A reference to an object whose public properties match the table fields. * @param string $key The name of the primary key. If provided the object property is updated. * * @return boolean True on success. * * @since 1.0 * @throws \RuntimeException / public function insertObject($table, &$object, $key = null) { $fields = []; $values = []; $tableColumns = $this->getTableColumns($table); $statement = 'INSERT INTO ' . $this->quoteName($table) . ' (%s) VALUES (%s)'; foreach (get_object_vars($object) as $k => $v) { // Skip columns that don't exist in the table. if (!\array_key_exists($k, $tableColumns)) { continue; } // Only process non-null scalars. if (\is_array($v) \|\| \is_object($v) \|\| $v === null) { continue; } if (!$this->checkFieldExists($table, $k)) { continue; } if ($k[0] === '_') { // Internal field continue; } if ($k === $key && $key == 0) { continue; } $fields[] = $this->quoteName($k); $values[] = $this->quote($v); } // Set the query and execute the insert. $this->setQuery(sprintf($statement, implode(',', $fields), implode(',', $values)))->execute(); $id = $this->insertid(); if ($key && $id) { $object->$key = $id; } return true; } /* * Method to get the auto-incremented value from the last INSERT statement. * * @return integer The value of the auto-increment field from the last inserted row. * * @since 1.0 / public function insertid() { $this->connect(); // TODO: SELECT IDENTITY $this->setQuery('SELECT @@IDENTITY'); return (int) $this->loadResult(); } /* * Execute the SQL statement. * * @return boolean * * @since 1.0 * @throws \RuntimeException / public function execute() { $this->connect(); // Take a local copy so that we don't modify the original query and cause issues later $sql = $this->replacePrefix((string) $this->sql); // Increment the query counter. $this->count++; // Get list of bounded parameters $bounded =& $this->sql->getBounded(); // If there is a monitor registered, let it know we are starting this query if ($this->monitor) { $this->monitor->startQuery($sql, $bounded); } // Execute the query. $this->executed = false; // Bind the variables foreach ($bounded as $key => $obj) { $this->statement->bindParam($key, $obj->value, $obj->dataType); } try { $this->executed = $this->statement->execute(); // If there is a monitor registered, let it know we have finished this query if ($this->monitor) { $this->monitor->stopQuery(); } return true; } catch (ExecutionFailureException $exception) { // If there is a monitor registered, let it know we have finished this query if ($this->monitor) { $this->monitor->stopQuery(); } // Check if the server was disconnected. if (!$this->connected()) { try { // Attempt to reconnect. $this->connection = null; $this->connect(); } catch (ConnectionFailureException $e) { // If connect fails, ignore that exception and throw the normal exception. throw $exception; } // Since we were able to reconnect, run the query again. return $this->execute(); } // Throw the normal query exception. throw $exception; } } /* * This function replaces a string identifier with the configured table prefix. * * @param string $sql The SQL statement to prepare. * @param string $prefix The table prefix. * * @return string The processed SQL statement. * * @since 1.0 / public function replacePrefix($sql, $prefix = '#__') { $escaped = false; $startPos = 0; $quoteChar = ''; $literal = ''; $sql = trim($sql); $n = \strlen($sql); while ($startPos < $n) { $ip = strpos($sql, $prefix, $startPos); if ($ip === false) { break; } $j = strpos($sql, "N'", $startPos); $k = strpos($sql, '"', $startPos); if (($k !== false) && (($k < $j) \|\| ($j === false))) { $quoteChar = '"'; $j = $k; } else { $quoteChar = "'"; } if ($j === false) { $j = $n; } $literal .= str_replace($prefix, $this->tablePrefix, substr($sql, $startPos, $j - $startPos)); $startPos = $j; $j = $startPos + 1; if ($j >= $n) { break; } // Quote comes first, find end of quote while (true) { $k = strpos($sql, $quoteChar, $j); $escaped = false; if ($k === false) { break; } $l = $k - 1; while ($l >= 0 && $sql[$l] === '\\') { $l--; $escaped = !$escaped; } if ($escaped) { $j = $k + 1; continue; } break; } if ($k === false) { // Error in the query - no end quote; ignore it break; } $literal .= substr($sql, $startPos, $k - $startPos + 1); $startPos = $k + 1; } if ($startPos < $n) { $literal .= substr($sql, $startPos, $n - $startPos); } return $literal; } /* * Select a database for use. * * @param string $database The name of the database to select for use. * * @return boolean True if the database was successfully selected. * * @since 1.0 * @throws ConnectionFailureException / public function select($database) { $this->connect(); if (!$database) { return false; } if (!sqlsrv_query($this->connection, 'USE [' . $database . ']', null, ['scrollable' => \SQLSRV_CURSOR_STATIC])) { throw new ConnectionFailureException('Could not connect to database'); } return true; } /* * Set the connection to use UTF-8 character encoding. * * @return boolean True on success. * * @since 1.0 / public function setUtf() { return true; } /* * Method to commit a transaction. * * @param boolean $toSavepoint If true, commit to the last savepoint. * * @return void * * @since 1.0 * @throws \RuntimeException / public function transactionCommit($toSavepoint = false) { $this->connect(); if (!$toSavepoint \|\| $this->transactionDepth <= 1) { $this->setQuery('COMMIT TRANSACTION')->execute(); $this->transactionDepth = 0; return; } $this->transactionDepth--; } /* * Method to roll back a transaction. * * @param boolean $toSavepoint If true, rollback to the last savepoint. * * @return void * * @since 1.0 * @throws \RuntimeException / public function transactionRollback($toSavepoint = false) { $this->connect(); if (!$toSavepoint \|\| $this->transactionDepth <= 1) { $this->setQuery('ROLLBACK TRANSACTION')->execute(); $this->transactionDepth = 0; return; } $savepoint = 'SP_' . ($this->transactionDepth - 1); $this->setQuery('ROLLBACK TRANSACTION ' . $this->quoteName($savepoint))->execute(); $this->transactionDepth--; } /* * Method to initialize a transaction. * * @param boolean $asSavepoint If true and a transaction is already active, a savepoint will be created. * * @return void * * @since 1.0 * @throws \RuntimeException / public function transactionStart($asSavepoint = false) { $this->connect(); if (!$asSavepoint \|\| !$this->transactionDepth) { $this->setQuery('BEGIN TRANSACTION')->execute(); $this->transactionDepth = 1; return; } $savepoint = 'SP_' . $this->transactionDepth; $this->setQuery('BEGIN TRANSACTION ' . $this->quoteName($savepoint))->execute(); $this->transactionDepth++; } /* * Method to check and see if a field exists in a table. * * @param string $table The table in which to verify the field. * @param string $field The field to verify. * * @return boolean True if the field exists in the table. * * @since 1.0 / protected function checkFieldExists($table, $field) { $this->connect(); $table = $this->replacePrefix((string) $table); $this->setQuery( "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '$table' AND COLUMN_NAME = '$field' ORDER BY ORDINAL_POSITION" ); return (bool) $this->loadResult(); } /* * Prepares a SQL statement for execution * * @param string $query The SQL query to be prepared. * * @return StatementInterface * * @since 2.0.0 * @throws PrepareStatementFailureException / protected function prepareStatement(string $query): StatementInterface { return new SqlsrvStatement($this->connection, $query); } /* * Renames a table in the database. * * @param string $oldTable The name of the table to be renamed * @param string $newTable The new name for the table. * @param string $backup Table prefix * @param string $prefix For the table - used to rename constraints in non-mysql databases * * @return $this * * @since 1.0 * @throws \RuntimeException / public function renameTable($oldTable, $newTable, $backup = null, $prefix = null) { $constraints = []; if ($prefix !== null && $backup !== null) { $constraints = $this->getTableConstraints($oldTable); } if (!empty($constraints)) { $this->renameConstraints($constraints, $prefix, $backup); } $this->setQuery("sp_rename '" . $oldTable . "', '" . $newTable . "'"); $this->execute(); return $this; } /* * Locks a table in the database. * * @param string $tableName The name of the table to lock. * * @return $this * * @since 1.0 * @throws \RuntimeException / public function lockTable($tableName) { return $this; } /* * Unlocks tables in the database. * * @return $this * * @since 1.0 * @throws \RuntimeException / public function unlockTables() { return $this; } } ��SqlsrvStatement.php��0000644��00000035317�15174223451�0010451 0��ustar�00��<?php /* * Part of the Joomla Framework Database Package * * @copyright Copyright (C) 2005 - 2021 Open Source Matters, Inc. All rights reserved. * @license GNU General Public License version 2 or later; see LICENSE / namespace Joomla\Database\Sqlsrv; use Joomla\Database\Exception\ExecutionFailureException; use Joomla\Database\Exception\PrepareStatementFailureException; use Joomla\Database\FetchMode; use Joomla\Database\FetchOrientation; use Joomla\Database\ParameterType; use Joomla\Database\StatementInterface; /* * SQL Server Database Statement. * * This class is modeled on \Doctrine\DBAL\Driver\SQLSrv\SQLSrvStatement * * @since 2.0.0 / class SqlsrvStatement implements StatementInterface { /* * The database connection resource. * * @var resource * @since 2.0.0 / protected $connection; /* * The default fetch mode for the statement. * * @var integer * @since 2.0.0 / protected $defaultFetchStyle = FetchMode::MIXED; /* * The default class to use for building object result sets. * * @var integer * @since 2.0.0 / protected $defaultObjectClass = \stdClass::class; /* * Mapping array converting fetch modes to the native engine type. * * @var array * @since 2.0.0 / private $fetchMap = [ FetchMode::MIXED => SQLSRV_FETCH_BOTH, FetchMode::ASSOCIATIVE => SQLSRV_FETCH_ASSOC, FetchMode::NUMERIC => SQLSRV_FETCH_NUMERIC, ]; /* * The query string being prepared. * * @var string * @since 2.0.0 / protected $query; /* * Internal tracking flag to set whether there is a result set available for processing * * @var boolean * @since 2.0.0 / private $result = false; /* * The prepared statement. * * @var resource * @since 2.0.0 / protected $statement; /* * Bound parameter types. * * @var array * @since 2.0.0 / protected $typesKeyMapping; /* * References to the variables bound as statement parameters. * * @var array * @since 2.0.0 / private $bindedValues = []; /* * Mapping between named parameters and position in query. * * @var array * @since 2.0.0 / protected $parameterKeyMapping; /* * Mapping array for parameter types. * * @var array * @since 2.0.0 / protected $parameterTypeMapping = [ ParameterType::BOOLEAN => ParameterType::BOOLEAN, ParameterType::INTEGER => ParameterType::INTEGER, ParameterType::LARGE_OBJECT => ParameterType::LARGE_OBJECT, ParameterType::NULL => ParameterType::NULL, ParameterType::STRING => ParameterType::STRING, ]; /* * Constructor. * * @param resource $connection The database connection resource * @param string $query The query this statement will process * * @since 2.0.0 * @throws PrepareStatementFailureException / public function __construct($connection, string $query) { // Initial parameter types for prepared statements $this->parameterTypeMapping = [ ParameterType::BOOLEAN => SQLSRV_PHPTYPE_INT, ParameterType::INTEGER => SQLSRV_PHPTYPE_INT, ParameterType::LARGE_OBJECT => SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY), ParameterType::NULL => SQLSRV_PHPTYPE_NULL, ParameterType::STRING => SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR), ]; $this->connection = $connection; $this->query = $this->prepareParameterKeyMapping($query); } /* * Replace named parameters with numbered parameters * * @param string $sql The SQL statement to prepare. * * @return string The processed SQL statement. * * @since 2.0.0 / public function prepareParameterKeyMapping($sql) { $escaped = false; $startPos = 0; $quoteChar = ''; $literal = ''; $mapping = []; $replace = []; $matches = []; $pattern = '/([:][a-zA-Z0-9_]+)/'; if (!preg_match($pattern, $sql, $matches)) { return $sql; } $sql = trim($sql); $n = \strlen($sql); while ($startPos < $n) { if (!preg_match($pattern, $sql, $matches, 0, $startPos)) { break; } $j = strpos($sql, "'", $startPos); $k = strpos($sql, '"', $startPos); if (($k !== false) && (($k < $j) \|\| ($j === false))) { $quoteChar = '"'; $j = $k; } else { $quoteChar = "'"; } if ($j === false) { $j = $n; } // Search for named prepared parameters and replace it with ? and save its position $substring = substr($sql, $startPos, $j - $startPos); if (preg_match_all($pattern, $substring, $matches, PREG_PATTERN_ORDER + PREG_OFFSET_CAPTURE)) { foreach ($matches[0] as $i => $match) { if ($i === 0) { $literal .= substr($substring, 0, $match[1]); } $mapping[$match[0]] = \count($mapping); $endOfPlaceholder = $match[1] + strlen($match[0]); $beginOfNextPlaceholder = $matches[0][$i + 1][1] ?? strlen($substring); $beginOfNextPlaceholder -= $endOfPlaceholder; $literal .= '?' . substr($substring, $endOfPlaceholder, $beginOfNextPlaceholder); } } else { $literal .= $substring; } $startPos = $j; $j++; if ($j >= $n) { break; } // Quote comes first, find end of quote while (true) { $k = strpos($sql, $quoteChar, $j); $escaped = false; if ($k === false) { break; } $l = $k - 1; while ($l >= 0 && $sql[$l] === '\\') { $l--; $escaped = !$escaped; } if ($escaped) { $j = $k + 1; continue; } break; } if ($k === false) { // Error in the query - no end quote; ignore it break; } $literal .= substr($sql, $startPos, $k - $startPos + 1); $startPos = $k + 1; } if ($startPos < $n) { $literal .= substr($sql, $startPos, $n - $startPos); } $this->parameterKeyMapping = $mapping; return $literal; } /* * Binds a parameter to the specified variable name. * * @param string\|integer $parameter Parameter identifier. For a prepared statement using named placeholders, this will be a parameter * name of the form `:name`. For a prepared statement using question mark placeholders, this will be * the 1-indexed position of the parameter. * @param mixed $variable Name of the PHP variable to bind to the SQL statement parameter. * @param string $dataType Constant corresponding to a SQL datatype, this should be the processed type from the QueryInterface. * @param integer $length The length of the variable. Usually required for OUTPUT parameters. * @param array $driverOptions Optional driver options to be used. * * @return boolean * * @since 2.0.0 / public function bindParam($parameter, &$variable, string $dataType = ParameterType::STRING, ?int $length = null, ?array $driverOptions = null) { $this->bindedValues[$parameter] =& $variable; // Validate parameter type if (!isset($this->parameterTypeMapping[$dataType])) { throw new \InvalidArgumentException(sprintf('Unsupported parameter type `%s`', $dataType)); } $this->typesKeyMapping[$parameter] = $this->parameterTypeMapping[$dataType]; $this->statement = null; return true; } /* * Binds a value to the specified variable. * * @param string\|integer $parameter Parameter identifier. For a prepared statement using named placeholders, this will be a parameter * name of the form `:name`. For a prepared statement using question mark placeholders, this will be * the 1-indexed position of the parameter. * @param mixed $variable Name of the PHP variable to bind to the SQL statement parameter. * @param string $dataType Constant corresponding to a SQL datatype, this should be the processed type from the QueryInterface. * * @return void * * @since 2.0.0 / private function bindValue($parameter, $variable, $dataType = ParameterType::STRING) { $this->bindedValues[$parameter] = $variable; $this->typesKeyMapping[$parameter] = $dataType; } /* * Closes the cursor, enabling the statement to be executed again. * * @return void * * @since 2.0.0 / public function closeCursor(): void { if (!$this->result \|\| !\is_resource($this->statement)) { return; } // Emulate freeing the result fetching and discarding rows, similarly to what PDO does in this case while (sqlsrv_fetch($this->statement)) { // Do nothing (see above) } $this->result = false; } /* * Fetches the SQLSTATE associated with the last operation on the statement handle. * * @return string * * @since 2.0.0 / public function errorCode() { $errors = sqlsrv_errors(SQLSRV_ERR_ERRORS); if ($errors) { return $errors[0]['code']; } return false; } /* * Fetches extended error information associated with the last operation on the statement handle. * * @return array * * @since 2.0.0 / public function errorInfo() { return sqlsrv_errors(SQLSRV_ERR_ERRORS); } /* * Executes a prepared statement * * @param array\|null $parameters An array of values with as many elements as there are bound parameters in the SQL statement being executed. * * @return boolean * * @since 2.0.0 / public function execute(?array $parameters = null) { if (empty($this->bindedValues) && $parameters !== null) { $hasZeroIndex = array_key_exists(0, $parameters); foreach ($parameters as $key => $val) { $key = ($hasZeroIndex && is_numeric($key)) ? $key + 1 : $key; $this->bindValue($key, $val); } } if (!$this->statement) { $this->statement = $this->prepare(); } if (!sqlsrv_execute($this->statement)) { $errors = $this->errorInfo(); throw new ExecutionFailureException($this->query, $errors[0]['message'], $errors[0]['code']); } $this->result = true; return true; } /* * Fetches the next row from a result set * * @param integer\|null $fetchStyle Controls how the next row will be returned to the caller. This value must be one of the * FetchMode constants, defaulting to value of FetchMode::MIXED. * @param integer $cursorOrientation For a StatementInterface object representing a scrollable cursor, this value determines which row * will be returned to the caller. This value must be one of the FetchOrientation constants, * defaulting to FetchOrientation::NEXT. * @param integer $cursorOffset For a StatementInterface object representing a scrollable cursor for which the cursorOrientation * parameter is set to FetchOrientation::ABS, this value specifies the absolute number of the row in * the result set that shall be fetched. For a StatementInterface object representing a scrollable * cursor for which the cursorOrientation parameter is set to FetchOrientation::REL, this value * specifies the row to fetch relative to the cursor position before `fetch()` was called. * * @return mixed The return value of this function on success depends on the fetch type. In all cases, boolean false is returned on failure. * * @since 2.0.0 / public function fetch(?int $fetchStyle = null, int $cursorOrientation = FetchOrientation::NEXT, int $cursorOffset = 0) { if (!$this->result) { return false; } $fetchStyle = $fetchStyle ?: $this->defaultFetchStyle; if ($fetchStyle === FetchMode::COLUMN) { return $this->fetchColumn(); } if (isset($this->fetchMap[$fetchStyle])) { return sqlsrv_fetch_array($this->statement, $this->fetchMap[$fetchStyle]) ?: false; } if (\in_array($fetchStyle, [FetchMode::STANDARD_OBJECT, FetchMode::CUSTOM_OBJECT], true)) { return sqlsrv_fetch_object($this->statement, $this->defaultObjectClass) ?: false; } throw new \InvalidArgumentException("Unknown fetch type '{$fetchStyle}'"); } /* * Returns a single column from the next row of a result set * * @param integer $columnIndex 0-indexed number of the column you wish to retrieve from the row. * If no value is supplied, the first column is retrieved. * * @return mixed Returns a single column from the next row of a result set or boolean false if there are no more rows. * * @since 2.0.0 / public function fetchColumn($columnIndex = 0) { $row = $this->fetch(FetchMode::NUMERIC); if ($row === false) { return false; } return $row[$columnIndex] ?? null; } /* * Prepares the SQL Server statement resource for execution * * @return resource * * @since 2.0.0 / private function prepare() { $params = []; $options = []; foreach ($this->bindedValues as $key => &$value) { $variable = [ &$value, SQLSRV_PARAM_IN ]; if ($this->typesKeyMapping[$key] === $this->parameterTypeMapping[ParameterType::LARGE_OBJECT]) { $variable[] = $this->typesKeyMapping[$key]; $variable[] = SQLSRV_SQLTYPE_VARBINARY('max'); } if (isset($this->parameterKeyMapping[$key])) { $params[$this->parameterKeyMapping[$key]] = $variable; } else { $params[] = $variable; } } // Cleanup referenced variable unset($value); // SQLSRV Function sqlsrv_num_rows requires a static or keyset cursor. if (strncmp(strtoupper(ltrim($this->query)), 'SELECT', \strlen('SELECT')) === 0) { $options = ['Scrollable' => SQLSRV_CURSOR_KEYSET]; } $statement = sqlsrv_prepare($this->connection, $this->query, $params, $options); if (!$statement) { $errors = $this->errorInfo(); throw new PrepareStatementFailureException($errors[0]['message'], $errors[0]['code']); } return $statement; } /* * Returns the number of rows affected by the last SQL statement. * * @return integer * * @since 2.0.0 / public function rowCount(): int { if (strncmp(strtoupper(ltrim($this->query)), 'SELECT', \strlen('SELECT')) === 0) { return sqlsrv_num_rows($this->statement); } return sqlsrv_rows_affected($this->statement); } /* * Sets the fetch mode to use while iterating this statement. * * @param integer $fetchMode The fetch mode, must be one of the FetchMode constants. * @param mixed ...$args Optional mode-specific arguments. * * @return void * * @since 2.0.0 / public function setFetchMode(int $fetchMode, ...$args): void { $this->defaultFetchStyle = $fetchMode; if (isset($args[0])) { $this->defaultObjectClass = $args[0]; } } } ��SqlsrvQuery.php��0000644��00000067307�15174223451�0007616 0��ustar�00��<?php /* * Part of the Joomla Framework Database Package * * @copyright Copyright (C) 2005 - 2021 Open Source Matters, Inc. All rights reserved. * @license GNU General Public License version 2 or later; see LICENSE / namespace Joomla\Database\Sqlsrv; use Joomla\Database\DatabaseInterface; use Joomla\Database\DatabaseQuery; use Joomla\Database\Query\QueryElement; /* * SQL Server Query Building Class. * * @since 1.0 / class SqlsrvQuery extends DatabaseQuery { /* * The list of zero or null representation of a datetime. * * @var array * @since 2.0.0 / protected $nullDatetimeList = ['1900-01-01 00:00:00']; /* * Magic function to convert the query to a string. * * @return string The completed query. * * @since 1.0 / public function __toString() { // For the moment if we are given a query string we can't effectively process limits, fix this later if ($this->sql) { return $this->sql; } $query = ''; switch ($this->type) { case 'select': // Add required aliases for offset or fixGroupColumns method $columns = $this->fixSelectAliases(); $query = (string) $this->select; if ($this->group) { $this->fixGroupColumns($columns); } $query .= (string) $this->from; if ($this->join) { // Special case for joins foreach ($this->join as $join) { $query .= (string) $join; } } if ($this->where) { $query .= (string) $this->where; } if ($this->selectRowNumber === null) { if ($this->group) { $query .= (string) $this->group; } if ($this->having) { $query .= (string) $this->having; } if ($this->merge) { // Special case for merge foreach ($this->merge as $idx => $element) { $query .= (string) $element . ' AS merge_' . (int) ($idx + 1); } } } if ($this->order) { $query .= (string) $this->order; } else { $query .= PHP_EOL . '/ORDER BY (SELECT 0)/'; } $query = $this->processLimit($query, $this->limit, $this->offset); if ($this->alias !== null) { $query = '(' . $query . ') AS ' . $this->alias; } break; case 'querySet': $query = $this->querySet; if ($query->order \|\| $query->limit \|\| $query->offset) { // If ORDER BY or LIMIT statement exist then parentheses is required for the first query $query = PHP_EOL . "SELECT FROM ($query) AS merge_0"; } if ($this->merge) { // Special case for merge foreach ($this->merge as $idx => $element) { $query .= (string) $element . ' AS merge_' . (int) ($idx + 1); } } if ($this->order) { $query .= (string) $this->order; } $query = $this->processLimit($query, $this->limit, $this->offset); break; case 'insert': $query .= (string) $this->insert; // Set method if ($this->set) { $query .= (string) $this->set; } elseif ($this->values) { // Columns-Values method if ($this->columns) { $query .= (string) $this->columns; } $elements = $this->insert->getElements(); $tableName = array_shift($elements); $query .= 'VALUES '; $query .= (string) $this->values; if ($this->autoIncrementField) { $query = 'SET IDENTITY_INSERT ' . $tableName . ' ON;' . $query . 'SET IDENTITY_INSERT ' . $tableName . ' OFF;'; } if ($this->where) { $query .= (string) $this->where; } } break; case 'delete': $query .= (string) $this->delete; $query .= (string) $this->from; if ($this->join) { // Special case for joins foreach ($this->join as $join) { $query .= (string) $join; } } if ($this->where) { $query .= (string) $this->where; } if ($this->order) { $query .= (string) $this->order; } break; case 'update': if ($this->join) { $tmpUpdate = $this->update; $tmpFrom = $this->from; $this->update = null; $this->from = null; $updateElem = $tmpUpdate->getElements(); $updateArray = explode(' ', $updateElem[0]); // Use table alias if exists $this->update(end($updateArray)); $this->from($updateElem[0]); $query .= (string) $this->update; $query .= (string) $this->set; $query .= (string) $this->from; $this->update = $tmpUpdate; $this->from = $tmpFrom; // Special case for joins foreach ($this->join as $join) { $query .= (string) $join; } } else { $query .= (string) $this->update; $query .= (string) $this->set; } if ($this->where) { $query .= (string) $this->where; } if ($this->order) { $query .= (string) $this->order; } break; default: $query = parent::__toString(); break; } return $query; } /** * Casts a value to a char. * * Ensure that the value is properly quoted before passing to the method. * * Usage: * $query->select($query->castAs('CHAR', 'a')); * * @param string $type The type of string to cast as. * @param string $value The value to cast as a char. * @param string $length The value to cast as a char. * * @return string SQL statement to cast the value as a char type. * * @since 1.0 / public function castAs(string $type, string $value, ?string $length = null) { switch (strtoupper($type)) { case 'CHAR': if (!$length) { $length = '10'; } return 'CAST(' . $value . ' as NVARCHAR(' . $length . '))'; case 'INT': return 'CAST(' . $value . ' AS INT)'; } return parent::castAs($type, $value, $length); } /* * Gets the function to determine the length of a character string. * * @param string $field A value. * @param string\|null $operator Comparison operator between charLength integer value and $condition * @param string\|null $condition Integer value to compare charLength with. * * @return string The required char length call. * * @since 1.0 / public function charLength($field, $operator = null, $condition = null) { $statement = 'DATALENGTH(' . $field . ')'; if ($operator !== null && $condition !== null) { $statement .= ' ' . $operator . ' ' . $condition; } return $statement; } /* * Concatenates an array of column names or values. * * @param string[] $values An array of values to concatenate. * @param string\|null $separator As separator to place between each value. * * @return string The concatenated values. * * @since 1.0 / public function concatenate($values, $separator = null) { if ($separator !== null) { return '(' . implode('+' . $this->quote($separator) . '+', $values) . ')'; } return '(' . implode('+', $values) . ')'; } /* * Gets the current date and time. * * @return string * * @since 1.0 / public function currentTimestamp() { return 'GETDATE()'; } /* * Get the length of a string in bytes. * * @param string $value The string to measure. * * @return integer * * @since 1.0 / public function length($value) { return 'LEN(' . $value . ')'; } /* * Add a grouping column to the GROUP clause of the query. * * Usage: * $query->group('id'); * * @param mixed $columns A string or array of ordering columns. * * @return SqlsrvQuery Returns this object to allow chaining. * * @since 1.5.0 / public function group($columns) { if (!($this->db instanceof DatabaseInterface)) { throw new \RuntimeException('JLIB_DATABASE_ERROR_INVALID_DB_OBJECT'); } // Transform $columns into an array for filtering purposes \is_string($columns) && $columns = explode(',', str_replace(' ', '', $columns)); // Get the _formatted_ FROM string and remove everything except `table AS alias` $fromStr = str_replace(['[', ']'], '', str_replace('#__', $this->db->getPrefix(), str_replace('FROM ', '', (string) $this->from))); // Start setting up an array of alias => table list($table, $alias) = preg_split("/\sAS\s/i", $fromStr); $tmpCols = $this->db->getTableColumns(trim($table)); $cols = []; foreach ($tmpCols as $name => $type) { $cols[] = $alias . '.' . $name; } // Now we need to get all tables from any joins // Go through all joins and add them to the tables array foreach ($this->join as $join) { $joinTbl = str_replace( '#__', $this->db->getPrefix(), str_replace( ']', '', preg_replace("/.(#.+\sAS\s[^\s])./i", '$1', (string) $join) ) ); list($table, $alias) = preg_split("/\sAS\s/i", $joinTbl); $tmpCols = $this->db->getTableColumns(trim($table)); foreach ($tmpCols as $name => $tmpColType) { $cols[] = $alias . '.' . $name; } } $selectStr = str_replace('SELECT ', '', (string) $this->select); // Remove any functions (e.g. COUNT(), SUM(), CONCAT()) $selectCols = preg_replace("/([^,]$[^$]\)[^,],?)/", '', $selectStr); // Remove any "as alias" statements $selectCols = preg_replace("/(\sas\s[^,])/i", '', $selectCols); // Remove any extra commas $selectCols = preg_replace('/,{2,}/', ',', $selectCols); // Remove any trailing commas and all whitespaces $selectCols = trim(str_replace(' ', '', preg_replace('/,?$/', '', $selectCols))); // Get an array to compare against $selectCols = explode(',', $selectCols); // Find all alias.* and fill with proper table column names foreach ($selectCols as $key => $aliasColName) { if (preg_match("/.+\/", $aliasColName, $match)) { // Grab the table alias minus the . $aliasStar = preg_replace("/(.+)\.\/", '$1', $aliasColName); // Unset the array key unset($selectCols[$key]); // Get the table name $tableColumns = preg_grep("/{$aliasStar}\.+/", $cols); $columns = array_merge($columns, $tableColumns); } } // Finally, get a unique string of all column names that need to be included in the group statement $columns = array_unique(array_merge($columns, $selectCols)); $columns = implode(',', $columns); // Recreate it every time, to ensure we have checked _all_ select statements $this->group = new QueryElement('GROUP BY', $columns); return $this; } /* * Aggregate function to get input values concatenated into a string, separated by delimiter * * Usage: * $query->groupConcat('id', ','); * * @param string $expression The expression to apply concatenation to, this may be a column name or complex SQL statement. * @param string $separator The delimiter of each concatenated value * * @return string Input values concatenated into a string, separated by delimiter * * @since 2.0.0 / public function groupConcat($expression, $separator = ',') { return 'string_agg(' . $expression . ', ' . $this->quote($separator) . ')'; } /* * Get the function to return a random floating-point value * * Usage: * $query->rand(); * * @return string * * @since 1.5.0 / public function rand() { return ' NEWID() '; } /* * Find a value in a varchar used like a set. * * Ensure that the value is an integer before passing to the method. * * Usage: * $query->findInSet((int) $parent->id, 'a.assigned_cat_ids') * * @param string $value The value to search for. * @param string $set The set of values. * * @return string A representation of the MySQL find_in_set() function for the driver. * * @since 1.5.0 / public function findInSet($value, $set) { return "CHARINDEX(',$value,', ',' + $set + ',') > 0"; } /* * Add required aliases to columns for select statement in subquery. * * @return array[] Array of columns with added missing aliases. * * @since 2.0.0 / protected function fixSelectAliases() { $operators = [ '+' => '', '-' => '', '' => '', '/' => '', '%' => '', '&' => '', '\|' => '', '~' => '', '^' => '', ]; // Split into array and remove comments $columns = $this->splitSqlExpression(implode(',', $this->select->getElements())); foreach ($columns as $i => $column) { $size = \count($column); if ($size == 0) { continue; } if ($size > 2 && strcasecmp($column[$size - 2], 'AS') === 0) { // Alias exists, replace it to uppercase $columns[$i][$size - 2] = 'AS'; continue; } if ($i == 0 && stripos(' DISTINCT ALL ', " $column[0] ") !== false) { // This words are reserved, they are not column names array_shift($column); $size--; } $lastWord = strtoupper($column[$size - 1]); $length = \strlen($lastWord); $lastChar = $lastWord[$length - 1]; if ($lastChar == '') { // Skip on wildcard continue; } if ($lastChar == ')' \|\| ($size == 1 && $lastChar == "'") \|\| $lastWord[0] == '@' \|\| $lastWord == 'NULL' \|\| $lastWord == 'END' \|\| is_numeric($lastWord)) { / * Ends with: * - SQL function * - single static value like 'only '+'string' * - @@var * - NULL * - CASE ... END * - Numeric / $columns[$i][] = 'AS'; $columns[$i][] = $this->quoteName('columnAlias' . $i); continue; } if ($size == 1) { continue; } $lastChar2 = substr($column[$size - 2], -1); // Check if column ends with '- a.x' or '- a. x' if (isset($operators[$lastChar2]) \|\| ($size > 2 && $lastChar2 === '.' && isset($operators[substr($column[$size - 3], -1)]))) { // Ignore plus signs if column start with them if ($size != 2 \|\| ltrim($column[0], '+') !== '' \|\| $column[1][0] === "'") { // If operator exists before last word then alias is required for subquery $columns[$i][] = 'AS'; $columns[$i][] = $this->quoteName('columnAlias' . $i); continue; } } elseif ($column[$size - 1][0] !== '.' && $lastChar2 !== '.') { // If columns is like name name2 then second word is alias. // Add missing AS before the alias, exception for 'a. x' and 'a .x' array_splice($columns[$i], -1, 0, 'AS'); } } $selectColumns = []; foreach ($columns as $i => $column) { $selectColumns[$i] = implode(' ', $column); } $this->select = new QueryElement('SELECT', $selectColumns); return $columns; } /* * Add missing columns names to GROUP BY clause. * * @param array[] $selectColumns Array of columns from splitSqlExpression method. * * @return $this * * @since 2.0.0 / protected function fixGroupColumns($selectColumns) { // Cache tables columns static $cacheCols = []; // Known columns of all included tables $knownColumnsByAlias = []; $iquotes = ['"' => '', '[' => '', "'" => '']; $nquotes = ['"', '[', ']']; // Aggregate functions $aFuncs = [ 'AVG(', 'CHECKSUM_AGG(', 'COUNT(', 'COUNT_BIG(', 'GROUPING(', 'GROUPING_ID(', 'MIN(', 'MAX(', 'SUM(', 'STDEV(', 'STDEVP(', 'VAR(', 'VARP(', ]; // Aggregated columns $filteredColumns = []; // Aliases found in SELECT statement $knownAliases = []; $wildcardTables = []; foreach ($selectColumns as $i => $column) { $size = \count($column); if ($size === 0) { continue; } if ($i == 0 && stripos(' DISTINCT ALL ', " $column[0] ") !== false) { // These words are reserved, they are not column names array_shift($selectColumns[0]); array_shift($column); $size--; } if ($size > 2 && $column[$size - 2] === 'AS') { // Save and remove AS alias $alias = $column[$size - 1]; if (isset($iquotes[$alias[0]])) { $alias = substr($alias, 1, -1); } // Remove alias $selectColumns[$i] = $column = \array_slice($column, 0, -2); if ($size === 3 \|\| ($size === 4 && strpos('+-/%&\|~^', $column[0][0]) !== false)) { $lastWord = $column[$size - 3]; if ($lastWord[0] === "'" \|\| $lastWord === 'NULL' \|\| is_numeric($lastWord)) { unset($selectColumns[$i]); continue; } } // Remember pair alias => column expression $knownAliases[$alias] = implode(' ', $column); } $aggregated = false; foreach ($column as $j => $block) { if (substr($block, -2) === '.') { // Found column ends with . if (isset($iquotes[$block[0]])) { // Quoted table $wildcardTables[] = substr($block, 1, -3); } else { $wildcardTables[] = substr($block, 0, -2); } } elseif (str_ireplace($aFuncs, '', $block) != $block) { $aggregated = true; } if ($block[0] === "'") { // Shrink static strings which could contain column name $column[$j] = "''"; } } if (!$aggregated) { // Without aggregated columns and aliases $filteredColumns[] = implode(' ', $selectColumns[$i]); } // Without aliases and static strings $selectColumns[$i] = implode(' ', $column); } // If select statement use table.* expression if ($wildcardTables) { // Split FROM statement into list of tables $tables = $this->splitSqlExpression(implode(',', $this->from->getElements())); foreach ($tables as $i => $table) { $table = implode(' ', $table); // Exclude subquery from the FROM clause if (strpos($table, '(') === false) { // Unquote $table = str_replace($nquotes, '', $table); $table = str_replace('#__', $this->db->getPrefix(), $table); $table = explode(' ', $table); $alias = end($table); $table = $table[0]; // Chek if exists a wildcard with current alias table? if (\in_array($alias, $wildcardTables, true)) { if (!isset($cacheCols[$table])) { $cacheCols[$table] = $this->db->getTableColumns($table); } if ($this->join \|\| $table != $alias) { foreach ($cacheCols[$table] as $name => $type) { $knownColumnsByAlias[$alias][] = $alias . '.' . $name; } } else { foreach ($cacheCols[$table] as $name => $type) { $knownColumnsByAlias[$alias][] = $name; } } } } } // Now we need to get all tables from any joins // Go through all joins and add them to the tables array if ($this->join) { foreach ($this->join as $join) { // Unquote and replace prefix $joinTbl = str_replace($nquotes, '', (string) $join); $joinTbl = str_replace('#__', $this->db->getPrefix(), $joinTbl); // Exclude subquery if (preg_match('/JOIN\s+(\w+)(?:\s+AS)?(?:\s+(\w+))?/i', $joinTbl, $matches)) { $table = $matches[1]; $alias = $matches[2] ?? $table; // Chek if exists a wildcard with current alias table? if (\in_array($alias, $wildcardTables, true)) { if (!isset($cacheCols[$table])) { $cacheCols[$table] = $this->db->getTableColumns($table); } foreach ($cacheCols[$table] as $name => $type) { $knownColumnsByAlias[$alias][] = $alias . '.' . $name; } } } } } } $selectExpression = implode(',', $selectColumns); // Split into the right columns $groupColumns = $this->splitSqlExpression(implode(',', $this->group->getElements())); // Remove column aliases from GROUP statement - SQLSRV does not support it foreach ($groupColumns as $i => $column) { $groupColumns[$i] = implode(' ', $column); $column = str_replace($nquotes, '', $groupColumns[$i]); if (isset($knownAliases[$column])) { // Be sure that this is not a valid column name if (!preg_match('/\b' . preg_quote($column, '/') . '\b/', $selectExpression)) { // Replace column alias by column expression $groupColumns[$i] = $knownAliases[$column]; } } } // Find all alias.* and fill with proper table column names foreach ($filteredColumns as $i => $column) { if (substr($column, -2) === '.') { unset($filteredColumns[$i]); // Extract alias. columns into GROUP BY statement $groupColumns = array_merge($groupColumns, $knownColumnsByAlias[substr($column, 0, -2)]); } } $groupColumns = array_merge($groupColumns, $filteredColumns); if ($this->order) { // Remove direction suffixes $dir = [" DESC\v", " ASC\v"]; $orderColumns = $this->splitSqlExpression(implode(',', $this->order->getElements())); foreach ($orderColumns as $i => $column) { $column = implode(' ', $column); $orderColumns[$i] = $column = trim(str_ireplace($dir, '', "$column\v"), "\v"); if (isset($knownAliases[str_replace($nquotes, '', $column)])) { unset($orderColumns[$i]); } if (str_ireplace($aFuncs, '', $column) != $column) { // Do not add aggregate expression unset($orderColumns[$i]); } } $groupColumns = array_merge($groupColumns, $orderColumns); } // Get a unique string of all column names that need to be included in the group statement $this->group = new QueryElement('GROUP BY', array_unique($groupColumns)); return $this; } /** * Split a string of sql expression into an array of individual columns. * Single line or line end comments and multi line comments are stripped off. * Always return at least one column. * * @param string $string Input string of sql expression like select expression. * * @return array[] The columns from the input string separated into an array. * * @since 2.0.0 / protected function splitSqlExpression($string) { // Append whitespace as equivalent to the last comma $string .= ' '; $colIdx = 0; $start = 0; $open = false; $openC = 0; $comment = false; $endString = ''; $length = \strlen($string); $columns = []; $column = []; $current = ''; $previous = null; $operators = [ '+' => '', '-' => '', '' => '', '/' => '', '%' => '', '&' => '', '\|' => '', '~' => '', '^' => '', ]; $addBlock = function ($block) use (&$column, &$colIdx) { if (isset($column[$colIdx])) { $column[$colIdx] .= $block; } else { $column[$colIdx] = $block; } }; for ($i = 0; $i < $length; $i++) { $current = substr($string, $i, 1); $current2 = substr($string, $i, 2); $current3 = substr($string, $i, 3); $lenEndString = \strlen($endString); $testEnd = substr($string, $i, $lenEndString); if ($current == '[' \|\| $current == '"' \|\| $current == "'" \|\| $current2 == '--' \|\| ($current2 == '/') \|\| ($current == '#' && $current3 != '#__') \|\| ($lenEndString && $testEnd == $endString)) { if ($open) { if ($testEnd === $endString) { if ($comment) { if ($lenEndString > 1) { $i += ($lenEndString - 1); } // Move cursor after close tag of comment $start = $i + 1; $comment = false; } elseif ($current == "'" \|\| $current == ']' \|\| $current == '"') { // Check for escaped quote like '', ]] or "" $n = 1; while ($i + $n < $length && $string[$i + $n] == $current) { $n++; } // Jump to the last quote $i += $n - 1; if ($n % 2 === 0) { // There is only escaped quote continue; } if ($n > 2) { // The last right close quote is not escaped $current = $string[$i]; } } $open = false; $endString = ''; } } else { $open = true; if ($current == '#' \|\| $current2 == '--') { $endString = "\n"; $comment = true; } elseif ($current2 == '/') { $endString = '/'; $comment = true; } elseif ($current == '[') { $endString = ']'; } else { $endString = $current; } if ($comment && $start < $i) { // Add string exists before comment $addBlock(substr($string, $start, $i - $start)); $previous = $string[$i - 1]; $start = $i; } } } elseif (!$open) { if ($current == '(') { $openC++; $previous = $current; } elseif ($current == ')') { $openC--; $previous = $current; } elseif ($current == '.') { if ($i === $start && $colIdx > 0 && !isset($column[$colIdx])) { // Remove whitespace placed before dot $colIdx--; } $previous = $current; } elseif ($openC === 0) { if (ctype_space($current)) { // Normalize whitespace $string[$i] = ' '; if ($start < $i) { // Add text placed before whitespace $addBlock(substr($string, $start, $i - $start)); $colIdx++; $previous = $string[$i - 1]; } elseif (isset($column[$colIdx])) { if ($colIdx > 1 \|\| !isset($operators[$previous])) { // There was whitespace after comment $colIdx++; } } // Move cursor forward $start = $i + 1; } elseif (isset($operators[$current]) && ($current !== '' \|\| $previous !== '.')) { if ($start < $i) { // Add text before operator $addBlock(substr($string, $start, $i - $start)); $colIdx++; } elseif (!isset($column[$colIdx]) && isset($operators[$previous])) { // Do not create whitespace between operators $colIdx--; } // Add operator $addBlock($current); $previous = $current; $colIdx++; // Move cursor forward $start = $i + 1; } else { $previous = $current; } } } if (($current == ',' && !$open && $openC == 0) \|\| $i == $length - 1) { if ($start < $i && !$comment) { // Save remaining text $addBlock(substr($string, $start, $i - $start)); } $columns[] = $column; // Reset values $column = []; $colIdx = 0; $previous = null; // Column saved, move cursor forward after comma $start = $i + 1; } } return $columns; } /** * Method to modify a query already in string format with the needed additions to make the query limited to a particular number of * results, or start at a particular offset. * * @param string $query The query in string format * @param integer $limit The limit for the result set * @param integer $offset The offset for the result set * * @return string * * @since 2.0.0 / public function processLimit($query, $limit, $offset = 0) { if ($offset > 0) { // Find a position of the last comment $commentPos = strrpos($query, '/ORDER BY (SELECT 0)/'); // If the last comment belongs to this query, not previous subquery if ($commentPos !== false && $commentPos + 2 === strripos($query, 'ORDER BY', $commentPos + 2)) { // We can not use OFFSET without ORDER BY $query = substr_replace($query, 'ORDER BY (SELECT 0)', $commentPos, 23); } $query .= PHP_EOL . 'OFFSET ' . (int) $offset . ' ROWS'; if ($limit > 0) { $query .= PHP_EOL . 'FETCH NEXT ' . (int) $limit . ' ROWS ONLY'; } } elseif ($limit > 0) { $position = stripos($query, 'SELECT'); $distinct = stripos($query, 'SELECT DISTINCT'); if ($position === $distinct) { $query = substr_replace($query, 'SELECT DISTINCT TOP ' . (int) $limit, $position, 15); } else { $query = substr_replace($query, 'SELECT TOP ' . (int) $limit, $position, 6); } } return $query; } /* * Add a query to UNION with the current query. * * Usage: * $query->union('SELECT name FROM #__foo') * $query->union('SELECT name FROM #__foo', true) * * @param DatabaseQuery\|string $query The DatabaseQuery object or string to union. * @param boolean $distinct True to only return distinct rows from the union. * * @return $this * * @since 1.0 / public function union($query, $distinct = true) { // Set up the name with parentheses, the DISTINCT flag is redundant return $this->merge($distinct ? 'UNION SELECT FROM ()' : 'UNION ALL SELECT * FROM ()', $query); } } ��

| ver. 1.4 | Github | . | PHP 8.3.23 | Generation time: 0 | proxy | phpinfo | Settings