BaseConnection.php

<?php

/**
 * This file is part of CodeIgniter 4 framework.
 *
 * (c) CodeIgniter Foundation <admin@codeigniter.com>
 *
 * For the full copyright and license information, please view
 * the LICENSE file that was distributed with this source code.
 */

namespace CodeIgniter\Database;

use Closure;
use CodeIgniter\Database\Exceptions\DatabaseException;
use CodeIgniter\Events\Events;
use Throwable;

/**
 * @property array      $aliasedTables
 * @property string     $charset
 * @property bool       $compress
 * @property float      $connectDuration
 * @property float      $connectTime
 * @property string     $database
 * @property string     $DBCollat
 * @property bool       $DBDebug
 * @property string     $DBDriver
 * @property string     $DBPrefix
 * @property string     $DSN
 * @property mixed      $encrypt
 * @property array      $failover
 * @property string     $hostname
 * @property mixed      $lastQuery
 * @property string     $password
 * @property bool       $pConnect
 * @property int|string $port
 * @property bool       $pretend
 * @property string     $queryClass
 * @property array      $reservedIdentifiers
 * @property bool       $strictOn
 * @property string     $subdriver
 * @property string     $swapPre
 * @property int        $transDepth
 * @property bool       $transFailure
 * @property bool       $transStatus
 */
abstract class BaseConnection implements ConnectionInterface
{
    /**
     * Data Source Name / Connect string
     *
     * @var string
     */
    protected $DSN;

    /**
     * Database port
     *
     * @var int|string
     */
    protected $port = '';

    /**
     * Hostname
     *
     * @var string
     */
    protected $hostname;

    /**
     * Username
     *
     * @var string
     */
    protected $username;

    /**
     * Password
     *
     * @var string
     */
    protected $password;

    /**
     * Database name
     *
     * @var string
     */
    protected $database;

    /**
     * Database driver
     *
     * @var string
     */
    protected $DBDriver = 'MySQLi';

    /**
     * Sub-driver
     *
     * @used-by CI_DB_pdo_driver
     *
     * @var string
     */
    protected $subdriver;

    /**
     * Table prefix
     *
     * @var string
     */
    protected $DBPrefix = '';

    /**
     * Persistent connection flag
     *
     * @var bool
     */
    protected $pConnect = false;

    /**
     * Debug flag
     *
     * Whether to display error messages.
     *
     * @var bool
     */
    protected $DBDebug = false;

    /**
     * Character set
     *
     * @var string
     */
    protected $charset = 'utf8';

    /**
     * Collation
     *
     * @var string
     */
    protected $DBCollat = 'utf8_general_ci';

    /**
     * Swap Prefix
     *
     * @var string
     */
    protected $swapPre = '';

    /**
     * Encryption flag/data
     *
     * @var mixed
     */
    protected $encrypt = false;

    /**
     * Compression flag
     *
     * @var bool
     */
    protected $compress = false;

    /**
     * Strict ON flag
     *
     * Whether we're running in strict SQL mode.
     *
     * @var bool
     */
    protected $strictOn;

    /**
     * Settings for a failover connection.
     *
     * @var array
     */
    protected $failover = [];

    /**
     * The last query object that was executed
     * on this connection.
     *
     * @var mixed
     */
    protected $lastQuery;

    /**
     * Connection ID
     *
     * @var bool|object|resource
     */
    public $connID = false;

    /**
     * Result ID
     *
     * @var bool|object|resource
     */
    public $resultID = false;

    /**
     * Protect identifiers flag
     *
     * @var bool
     */
    public $protectIdentifiers = true;

    /**
     * List of reserved identifiers
     *
     * Identifiers that must NOT be escaped.
     *
     * @var array
     */
    protected $reservedIdentifiers = ['*'];

    /**
     * Identifier escape character
     *
     * @var array|string
     */
    public $escapeChar = '"';

    /**
     * ESCAPE statement string
     *
     * @var string
     */
    public $likeEscapeStr = " ESCAPE '%s' ";

    /**
     * ESCAPE character
     *
     * @var string
     */
    public $likeEscapeChar = '!';

    /**
     * RegExp used to escape identifiers
     *
     * @var array
     */
    protected $pregEscapeChar = [];

    /**
     * Holds previously looked up data
     * for performance reasons.
     *
     * @var array
     */
    public $dataCache = [];

    /**
     * Microtime when connection was made
     *
     * @var float
     */
    protected $connectTime;

    /**
     * How long it took to establish connection.
     *
     * @var float
     */
    protected $connectDuration;

    /**
     * If true, no queries will actually be
     * ran against the database.
     *
     * @var bool
     */
    protected $pretend = false;

    /**
     * Transaction enabled flag
     *
     * @var bool
     */
    public $transEnabled = true;

    /**
     * Strict transaction mode flag
     *
     * @var bool
     */
    public $transStrict = true;

    /**
     * Transaction depth level
     *
     * @var int
     */
    protected $transDepth = 0;

    /**
     * Transaction status flag
     *
     * Used with transactions to determine if a rollback should occur.
     *
     * @var bool
     */
    protected $transStatus = true;

    /**
     * Transaction failure flag
     *
     * Used with transactions to determine if a transaction has failed.
     *
     * @var bool
     */
    protected $transFailure = false;

    /**
     * Array of table aliases.
     *
     * @var array
     */
    protected $aliasedTables = [];

    /**
     * Query Class
     *
     * @var string
     */
    protected $queryClass = 'CodeIgniter\\Database\\Query';

    /**
     * Saves our connection settings.
     */
    public function __construct(array $params)
    {
        foreach ($params as $key => $value) {
            $this->{$key} = $value;
        }

        $queryClass = str_replace('Connection', 'Query', static::class);

        if (class_exists($queryClass)) {
            $this->queryClass = $queryClass;
        }
    }

    /**
     * Initializes the database connection/settings.
     *
     * @throws DatabaseException
     *
     * @return mixed
     */
    public function initialize()
    {
        /* If an established connection is available, then there's
         * no need to connect and select the database.
         *
         * Depending on the database driver, conn_id can be either
         * boolean TRUE, a resource or an object.
         */
        if ($this->connID) {
            return;
        }

        $this->connectTime = microtime(true);
        $connectionErrors  = [];

        try {
            // Connect to the database and set the connection ID
            $this->connID = $this->connect($this->pConnect);
        } catch (Throwable $e) {
            $connectionErrors[] = sprintf('Main connection [%s]: %s', $this->DBDriver, $e->getMessage());
            log_message('error', 'Error connecting to the database: ' . $e->getMessage());
        }

        // No connection resource? Check if there is a failover else throw an error
        if (! $this->connID) {
            // Check if there is a failover set
            if (! empty($this->failover) && is_array($this->failover)) {
                // Go over all the failovers
                foreach ($this->failover as $index => $failover) {
                    // Replace the current settings with those of the failover
                    foreach ($failover as $key => $val) {
                        if (property_exists($this, $key)) {
                            $this->{$key} = $val;
                        }
                    }

                    try {
                        // Try to connect
                        $this->connID = $this->connect($this->pConnect);
                    } catch (Throwable $e) {
                        $connectionErrors[] = sprintf('Failover #%d [%s]: %s', ++$index, $this->DBDriver, $e->getMessage());
                        log_message('error', 'Error connecting to the database: ' . $e->getMessage());
                    }

                    // If a connection is made break the foreach loop
                    if ($this->connID) {
                        break;
                    }
                }
            }

            // We still don't have a connection?
            if (! $this->connID) {
                throw new DatabaseException(sprintf(
                    'Unable to connect to the database.%s%s',
                    PHP_EOL,
                    implode(PHP_EOL, $connectionErrors)
                ));
            }
        }

        $this->connectDuration = microtime(true) - $this->connectTime;
    }

    /**
     * Connect to the database.
     *
     * @return mixed
     */
    abstract public function connect(bool $persistent = false);

    /**
     * Close the database connection.
     */
    public function close()
    {
        if ($this->connID) {
            $this->_close();
            $this->connID = false;
        }
    }

    /**
     * Platform dependent way method for closing the connection.
     *
     * @return mixed
     */
    abstract protected function _close();

    /**
     * Create a persistent database connection.
     *
     * @return mixed
     */
    public function persistentConnect()
    {
        return $this->connect(true);
    }

    /**
     * Keep or establish the connection if no queries have been sent for
     * a length of time exceeding the server's idle timeout.
     *
     * @return mixed
     */
    abstract public function reconnect();

    /**
     * Returns the actual connection object. If both a 'read' and 'write'
     * connection has been specified, you can pass either term in to
     * get that connection. If you pass either alias in and only a single
     * connection is present, it must return the sole connection.
     *
     * @return mixed
     */
    public function getConnection(?string $alias = null)
    {
        // @todo work with read/write connections
        return $this->connID;
    }

    /**
     * Select a specific database table to use.
     *
     * @return mixed
     */
    abstract public function setDatabase(string $databaseName);

    /**
     * Returns the name of the current database being used.
     */
    public function getDatabase(): string
    {
        return empty($this->database) ? '' : $this->database;
    }

    /**
     * Set DB Prefix
     *
     * Set's the DB Prefix to something new without needing to reconnect
     *
     * @param string $prefix The prefix
     */
    public function setPrefix(string $prefix = ''): string
    {
        return $this->DBPrefix = $prefix;
    }

    /**
     * Returns the database prefix.
     */
    public function getPrefix(): string
    {
        return $this->DBPrefix;
    }

    /**
     * The name of the platform in use (MySQLi, mssql, etc)
     */
    public function getPlatform(): string
    {
        return $this->DBDriver;
    }

    /**
     * Returns a string containing the version of the database being used.
     */
    abstract public function getVersion(): string;

    /**
     * Sets the Table Aliases to use. These are typically
     * collected during use of the Builder, and set here
     * so queries are built correctly.
     *
     * @return $this
     */
    public function setAliasedTables(array $aliases)
    {
        $this->aliasedTables = $aliases;

        return $this;
    }

    /**
     * Add a table alias to our list.
     *
     * @return $this
     */
    public function addTableAlias(string $table)
    {
        if (! in_array($table, $this->aliasedTables, true)) {
            $this->aliasedTables[] = $table;
        }

        return $this;
    }

    /**
     * Executes the query against the database.
     *
     * @return mixed
     */
    abstract protected function execute(string $sql);

    /**
     * Orchestrates a query against the database. Queries must use
     * Database\Statement objects to store the query and build it.
     * This method works with the cache.
     *
     * Should automatically handle different connections for read/write
     * queries if needed.
     *
     * @param mixed ...$binds
     *
     * @return BaseResult|bool|Query BaseResult when “read” type query, bool when “write” type query, Query when prepared query
     *
     * @todo BC set $queryClass default as null in 4.1
     */
    public function query(string $sql, $binds = null, bool $setEscapeFlags = true, string $queryClass = '')
    {
        $queryClass = $queryClass ?: $this->queryClass;

        if (empty($this->connID)) {
            $this->initialize();
        }

        /**
         * @var Query $query
         */
        $query = new $queryClass($this);

        $query->setQuery($sql, $binds, $setEscapeFlags);

        if (! empty($this->swapPre) && ! empty($this->DBPrefix)) {
            $query->swapPrefix($this->DBPrefix, $this->swapPre);
        }

        $startTime = microtime(true);

        // Always save the last query so we can use
        // the getLastQuery() method.
        $this->lastQuery = $query;

        // Run the query for real
        if (! $this->pretend && false === ($this->resultID = $this->simpleQuery($query->getQuery()))) {
            $query->setDuration($startTime, $startTime);

            // This will trigger a rollback if transactions are being used
            if ($this->transDepth !== 0) {
                $this->transStatus = false;
            }

            if ($this->DBDebug) {
                // We call this function in order to roll-back queries
                // if transactions are enabled. If we don't call this here
                // the error message will trigger an exit, causing the
                // transactions to remain in limbo.
                while ($this->transDepth !== 0) {
                    $transDepth = $this->transDepth;
                    $this->transComplete();

                    if ($transDepth === $this->transDepth) {
                        log_message('error', 'Database: Failure during an automated transaction commit/rollback!');
                        break;
                    }
                }

                return false;
            }

            if (! $this->pretend) {
                // Let others do something with this query.
                Events::trigger('DBQuery', $query);
            }

            return false;
        }

        $query->setDuration($startTime);

        if (! $this->pretend) {
            // Let others do something with this query
            Events::trigger('DBQuery', $query);
        }

        // If $pretend is true, then we just want to return
        // the actual query object here. There won't be
        // any results to return.
        if ($this->pretend) {
            return $query;
        }

        // resultID is not false, so it must be successful
        if ($this->isWriteType($sql)) {
            return true;
        }

        // query is not write-type, so it must be read-type query; return QueryResult
        $resultClass = str_replace('Connection', 'Result', static::class);

        return new $resultClass($this->connID, $this->resultID);
    }

    /**
     * Performs a basic query against the database. No binding or caching
     * is performed, nor are transactions handled. Simply takes a raw
     * query string and returns the database-specific result id.
     *
     * @return mixed
     */
    public function simpleQuery(string $sql)
    {
        if (empty($this->connID)) {
            $this->initialize();
        }

        return $this->execute($sql);
    }

    /**
     * Disable Transactions
     *
     * This permits transactions to be disabled at run-time.
     */
    public function transOff()
    {
        $this->transEnabled = false;
    }

    /**
     * Enable/disable Transaction Strict Mode
     *
     * When strict mode is enabled, if you are running multiple groups of
     * transactions, if one group fails all subsequent groups will be
     * rolled back.
     *
     * If strict mode is disabled, each group is treated autonomously,
     * meaning a failure of one group will not affect any others
     *
     * @param bool $mode = true
     *
     * @return $this
     */
    public function transStrict(bool $mode = true)
    {
        $this->transStrict = $mode;

        return $this;
    }

    /**
     * Start Transaction
     */
    public function transStart(bool $testMode = false): bool
    {
        if (! $this->transEnabled) {
            return false;
        }

        return $this->transBegin($testMode);
    }

    /**
     * Complete Transaction
     */
    public function transComplete(): bool
    {
        if (! $this->transEnabled) {
            return false;
        }

        // The query() function will set this flag to FALSE in the event that a query failed
        if ($this->transStatus === false || $this->transFailure === true) {
            $this->transRollback();

            // If we are NOT running in strict mode, we will reset
            // the _trans_status flag so that subsequent groups of
            // transactions will be permitted.
            if ($this->transStrict === false) {
                $this->transStatus = true;
            }

            return false;
        }

        return $this->transCommit();
    }

    /**
     * Lets you retrieve the transaction flag to determine if it has failed
     */
    public function transStatus(): bool
    {
        return $this->transStatus;
    }

    /**
     * Begin Transaction
     */
    public function transBegin(bool $testMode = false): bool
    {
        if (! $this->transEnabled) {
            return false;
        }

        // When transactions are nested we only begin/commit/rollback the outermost ones
        if ($this->transDepth > 0) {
            $this->transDepth++;

            return true;
        }

        if (empty($this->connID)) {
            $this->initialize();
        }

        // Reset the transaction failure flag.
        // If the $test_mode flag is set to TRUE transactions will be rolled back
        // even if the queries produce a successful result.
        $this->transFailure = ($testMode === true);

        if ($this->_transBegin()) {
            $this->transDepth++;

            return true;
        }

        return false;
    }

    /**
     * Commit Transaction
     */
    public function transCommit(): bool
    {
        if (! $this->transEnabled || $this->transDepth === 0) {
            return false;
        }

        // When transactions are nested we only begin/commit/rollback the outermost ones
        if ($this->transDepth > 1 || $this->_transCommit()) {
            $this->transDepth--;

            return true;
        }

        return false;
    }

    /**
     * Rollback Transaction
     */
    public function transRollback(): bool
    {
        if (! $this->transEnabled || $this->transDepth === 0) {
            return false;
        }

        // When transactions are nested we only begin/commit/rollback the outermost ones
        if ($this->transDepth > 1 || $this->_transRollback()) {
            $this->transDepth--;

            return true;
        }

        return false;
    }

    /**
     * Begin Transaction
     */
    abstract protected function _transBegin(): bool;

    /**
     * Commit Transaction
     */
    abstract protected function _transCommit(): bool;

    /**
     * Rollback Transaction
     */
    abstract protected function _transRollback(): bool;

    /**
     * Returns an instance of the query builder for this connection.
     *
     * @param array|string $tableName
     *
     * @throws DatabaseException
     *
     * @return BaseBuilder
     */
    public function table($tableName)
    {
        if (empty($tableName)) {
            throw new DatabaseException('You must set the database table to be used with your query.');
        }

        $className = str_replace('Connection', 'Builder', static::class);

        return new $className($tableName, $this);
    }

    /**
     * Creates a prepared statement with the database that can then
     * be used to execute multiple statements against. Within the
     * closure, you would build the query in any normal way, though
     * the Query Builder is the expected manner.
     *
     * Example:
     *    $stmt = $db->prepare(function($db)
     *           {
     *             return $db->table('users')
     *                   ->where('id', 1)
     *                     ->get();
     *           })
     *
     * @return BasePreparedQuery|null
     */
    public function prepare(Closure $func, array $options = [])
    {
        if (empty($this->connID)) {
            $this->initialize();
        }

        $this->pretend();

        $sql = $func($this);

        $this->pretend(false);

        if ($sql instanceof QueryInterface) {
            // @phpstan-ignore-next-line
            $sql = $sql->getOriginalQuery();
        }

        $class = str_ireplace('Connection', 'PreparedQuery', static::class);
        /** @var BasePreparedQuery $class */
        $class = new $class($this);

        return $class->prepare($sql, $options);
    }

    /**
     * Returns the last query's statement object.
     *
     * @return mixed
     */
    public function getLastQuery()
    {
        return $this->lastQuery;
    }

    /**
     * Returns a string representation of the last query's statement object.
     */
    public function showLastQuery(): string
    {
        return (string) $this->lastQuery;
    }

    /**
     * Returns the time we started to connect to this database in
     * seconds with microseconds.
     *
     * Used by the Debug Toolbar's timeline.
     */
    public function getConnectStart(): ?float
    {
        return $this->connectTime;
    }

    /**
     * Returns the number of seconds with microseconds that it took
     * to connect to the database.
     *
     * Used by the Debug Toolbar's timeline.
     */
    public function getConnectDuration(int $decimals = 6): string
    {
        return number_format($this->connectDuration, $decimals);
    }

    /**
     * Protect Identifiers
     *
     * This function is used extensively by the Query Builder class, and by
     * a couple functions in this class.
     * It takes a column or table name (optionally with an alias) and inserts
     * the table prefix onto it. Some logic is necessary in order to deal with
     * column names that include the path. Consider a query like this:
     *
     * SELECT hostname.database.table.column AS c FROM hostname.database.table
     *
     * Or a query with aliasing:
     *
     * SELECT m.member_id, m.member_name FROM members AS m
     *
     * Since the column name can include up to four segments (host, DB, table, column)
     * or also have an alias prefix, we need to do a bit of work to figure this out and
     * insert the table prefix (if it exists) in the proper position, and escape only
     * the correct identifiers.
     *
     * @param array|string $item
     * @param bool         $prefixSingle Prefix an item with no segments?
     * @param bool         $fieldExists  Supplied $item contains a field name?
     *
     * @return array|string
     */
    public function protectIdentifiers($item, bool $prefixSingle = false, ?bool $protectIdentifiers = null, bool $fieldExists = true)
    {
        if (! is_bool($protectIdentifiers)) {
            $protectIdentifiers = $this->protectIdentifiers;
        }

        if (is_array($item)) {
            $escapedArray = [];

            foreach ($item as $k => $v) {
                $escapedArray[$this->protectIdentifiers($k)] = $this->protectIdentifiers($v, $prefixSingle, $protectIdentifiers, $fieldExists);
            }

            return $escapedArray;
        }

        // This is basically a bug fix for queries that use MAX, MIN, etc.
        // If a parenthesis is found we know that we do not need to
        // escape the data or add a prefix. There's probably a more graceful
        // way to deal with this, but I'm not thinking of it
        //
        // Added exception for single quotes as well, we don't want to alter
        // literal strings.
        if (strcspn($item, "()'") !== strlen($item)) {
            return $item;
        }

        // Convert tabs or multiple spaces into single spaces
        $item = preg_replace('/\s+/', ' ', trim($item));

        // If the item has an alias declaration we remove it and set it aside.
        // Note: strripos() is used in order to support spaces in table names
        if ($offset = strripos($item, ' AS ')) {
            $alias = ($protectIdentifiers) ? substr($item, $offset, 4) . $this->escapeIdentifiers(substr($item, $offset + 4)) : substr($item, $offset);
            $item  = substr($item, 0, $offset);
        } elseif ($offset = strrpos($item, ' ')) {
            $alias = ($protectIdentifiers) ? ' ' . $this->escapeIdentifiers(substr($item, $offset + 1)) : substr($item, $offset);
            $item  = substr($item, 0, $offset);