DatabaseConnection.php

Go to the documentation of this file.

<?php
namespace TYPO3\CMS\Core\Database;

use TYPO3\CMS\Core\Utility\GeneralUtility;

class DatabaseConnection {

    const AND_Constraint = 'AND';

    const OR_Constraint = 'OR';

    // Set "TRUE" or "1" if you want database errors outputted. Set to "2" if you also want successful database actions outputted.
    public $debugOutput = FALSE;

    // Internally: Set to last built query (not necessarily executed...)
    public $debug_lastBuiltQuery = '';

    // Set "TRUE" if you want the last built query to be stored in $debug_lastBuiltQuery independent of $this->debugOutput
    public $store_lastBuiltQuery = FALSE;

    // Set this to 1 to get queries explained (devIPmask must match). Set the value to 2 to the same but disregarding the devIPmask.
    // There is an alternative option to enable explain output in the admin panel under "TypoScript", which will produce much nicer output, but only works in FE.
    public $explainOutput = 0;

    protected $databaseHost = '';

    protected $databasePort = 3306;

    protected $databaseSocket = NULL;

    protected $databaseName = '';

    protected $databaseUsername = '';

    protected $databaseUserPassword = '';

    protected $persistentDatabaseConnection = FALSE;

    protected $connectionCompression = FALSE;

    protected $connectionCharset = 'utf8';

    protected $initializeCommandsAfterConnect = array();

    protected $isConnected = FALSE;

    protected $link = NULL;

    // Default character set, applies unless character set or collation are explicitly set
    public $default_charset = 'utf8';

    protected $preProcessHookObjects = array();

    protected $postProcessHookObjects = array();


    static protected $dateTimeFormats = array(
        'date' => array(
            'empty' => '0000-00-00',
            'format' => 'Y-m-d'
        ),
        'datetime' => array(
            'empty' => '0000-00-00 00:00:00',
            'format' => 'Y-m-d H:i:s'
        )
    );

    public function initialize() {
        // Intentionally blank as this will be overloaded by DBAL
    }

    /************************************
     *
     * Query execution
     *
     * These functions are the RECOMMENDED DBAL functions for use in your applications
     * Using these functions will allow the DBAL to use alternative ways of accessing data (contrary to if a query is returned!)
     * They compile a query AND execute it immediately and then return the result
     * This principle heightens our ability to create various forms of DBAL of the functions.
     * Generally: We want to return a result pointer/object, never queries.
     * Also, having the table name together with the actual query execution allows us to direct the request to other databases.
     *
     **************************************/

    public function exec_INSERTquery($table, $fields_values, $no_quote_fields = FALSE) {
        $res = $this->query($this->INSERTquery($table, $fields_values, $no_quote_fields));
        if ($this->debugOutput) {
            $this->debug('exec_INSERTquery');
        }
        foreach ($this->postProcessHookObjects as $hookObject) {
            $hookObject->exec_INSERTquery_postProcessAction($table, $fields_values, $no_quote_fields, $this);
        }
        return $res;
    }

    public function exec_INSERTmultipleRows($table, array $fields, array $rows, $no_quote_fields = FALSE) {
        $res = $this->query($this->INSERTmultipleRows($table, $fields, $rows, $no_quote_fields));
        if ($this->debugOutput) {
            $this->debug('exec_INSERTmultipleRows');
        }
        foreach ($this->postProcessHookObjects as $hookObject) {
            $hookObject->exec_INSERTmultipleRows_postProcessAction($table, $fields, $rows, $no_quote_fields, $this);
        }
        return $res;
    }

    public function exec_UPDATEquery($table, $where, $fields_values, $no_quote_fields = FALSE) {
        $res = $this->query($this->UPDATEquery($table, $where, $fields_values, $no_quote_fields));
        if ($this->debugOutput) {
            $this->debug('exec_UPDATEquery');
        }
        foreach ($this->postProcessHookObjects as $hookObject) {
            $hookObject->exec_UPDATEquery_postProcessAction($table, $where, $fields_values, $no_quote_fields, $this);
        }
        return $res;
    }

    public function exec_DELETEquery($table, $where) {
        $res = $this->query($this->DELETEquery($table, $where));
        if ($this->debugOutput) {
            $this->debug('exec_DELETEquery');
        }
        foreach ($this->postProcessHookObjects as $hookObject) {
            $hookObject->exec_DELETEquery_postProcessAction($table, $where, $this);
        }
        return $res;
    }

    public function exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '') {
        $query = $this->SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
        $res = $this->query($query);
        if ($this->debugOutput) {
            $this->debug('exec_SELECTquery');
        }
        if ($this->explainOutput) {
            $this->explain($query, $from_table, $res->num_rows);
        }
        foreach ($this->postProcessHookObjects as $hookObject) {
            $hookObject->exec_SELECTquery_postProcessAction($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', $this);
        }
        return $res;
    }

    public function exec_SELECT_mm_query($select, $local_table, $mm_table, $foreign_table, $whereClause = '', $groupBy = '', $orderBy = '', $limit = '') {
        $foreign_table_as = $foreign_table == $local_table ? $foreign_table . str_replace('.', '', uniqid('_join', TRUE)) : '';
        $mmWhere = $local_table ? $local_table . '.uid=' . $mm_table . '.uid_local' : '';
        $mmWhere .= ($local_table and $foreign_table) ? ' AND ' : '';
        $tables = ($local_table ? $local_table . ',' : '') . $mm_table;
        if ($foreign_table) {
            $mmWhere .= ($foreign_table_as ?: $foreign_table) . '.uid=' . $mm_table . '.uid_foreign';
            $tables .= ',' . $foreign_table . ($foreign_table_as ? ' AS ' . $foreign_table_as : '');
        }
        return $this->exec_SELECTquery($select, $tables, $mmWhere . ' ' . $whereClause, $groupBy, $orderBy, $limit);
    }

    public function exec_SELECT_queryArray($queryParts) {
        return $this->exec_SELECTquery($queryParts['SELECT'], $queryParts['FROM'], $queryParts['WHERE'], $queryParts['GROUPBY'], $queryParts['ORDERBY'], $queryParts['LIMIT']);
    }

    public function exec_SELECTgetRows($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', $uidIndexField = '') {
        $res = $this->exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
        if ($this->debugOutput) {
            $this->debug('exec_SELECTquery');
        }
        if (!$this->sql_error()) {
            $output = array();
            if ($uidIndexField) {
                while ($tempRow = $this->sql_fetch_assoc($res)) {
                    $output[$tempRow[$uidIndexField]] = $tempRow;
                }
            } else {
                while ($output[] = $this->sql_fetch_assoc($res)) {

                }
                array_pop($output);
            }
            $this->sql_free_result($res);
        } else {
            $output = NULL;
        }
        return $output;
    }

    public function exec_SELECTgetSingleRow($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $numIndex = FALSE) {
        $res = $this->exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, '1');
        if ($this->debugOutput) {
            $this->debug('exec_SELECTquery');
        }
        $output = NULL;
        if ($res !== FALSE) {
            if ($numIndex) {
                $output = $this->sql_fetch_row($res);
            } else {
                $output = $this->sql_fetch_assoc($res);
            }
            $this->sql_free_result($res);
        }
        return $output;
    }

    public function exec_SELECTcountRows($field, $table, $where = '1=1') {
        $count = FALSE;
        $resultSet = $this->exec_SELECTquery('COUNT(' . $field . ')', $table, $where);
        if ($resultSet !== FALSE) {
            list($count) = $this->sql_fetch_row($resultSet);
            $count = (int)$count;
            $this->sql_free_result($resultSet);
        }
        return $count;
    }

    public function exec_TRUNCATEquery($table) {
        $res = $this->query($this->TRUNCATEquery($table));
        if ($this->debugOutput) {
            $this->debug('exec_TRUNCATEquery');
        }
        foreach ($this->postProcessHookObjects as $hookObject) {
            $hookObject->exec_TRUNCATEquery_postProcessAction($table, $this);
        }
        return $res;
    }

    protected function query($query) {
        if (!$this->isConnected) {
            $this->connectDB();
        }
        return $this->link->query($query);
    }

    /**************************************
     *
     * Query building
     *
     **************************************/
    public function INSERTquery($table, $fields_values, $no_quote_fields = FALSE) {
        // Table and fieldnames should be "SQL-injection-safe" when supplied to this
        // function (contrary to values in the arrays which may be insecure).
        if (!is_array($fields_values) || count($fields_values) === 0) {
            return NULL;
        }
        foreach ($this->preProcessHookObjects as $hookObject) {
            $hookObject->INSERTquery_preProcessAction($table, $fields_values, $no_quote_fields, $this);
        }
        // Quote and escape values
        $fields_values = $this->fullQuoteArray($fields_values, $table, $no_quote_fields, TRUE);
        // Build query
        $query = 'INSERT INTO ' . $table . ' (' . implode(',', array_keys($fields_values)) . ') VALUES ' . '(' . implode(',', $fields_values) . ')';
        // Return query
        if ($this->debugOutput || $this->store_lastBuiltQuery) {
            $this->debug_lastBuiltQuery = $query;
        }
        return $query;
    }

    public function INSERTmultipleRows($table, array $fields, array $rows, $no_quote_fields = FALSE) {
        // Table and fieldnames should be "SQL-injection-safe" when supplied to this
        // function (contrary to values in the arrays which may be insecure).
        if (count($rows) === 0) {
            return NULL;
        }
        foreach ($this->preProcessHookObjects as $hookObject) {
            $hookObject->INSERTmultipleRows_preProcessAction($table, $fields, $rows, $no_quote_fields, $this);
        }
        // Build query
        $query = 'INSERT INTO ' . $table . ' (' . implode(', ', $fields) . ') VALUES ';
        $rowSQL = array();
        foreach ($rows as $row) {
            // Quote and escape values
            $row = $this->fullQuoteArray($row, $table, $no_quote_fields);
            $rowSQL[] = '(' . implode(', ', $row) . ')';
        }
        $query .= implode(', ', $rowSQL);
        // Return query
        if ($this->debugOutput || $this->store_lastBuiltQuery) {
            $this->debug_lastBuiltQuery = $query;
        }
        return $query;
    }

    public function UPDATEquery($table, $where, $fields_values, $no_quote_fields = FALSE) {
        // Table and fieldnames should be "SQL-injection-safe" when supplied to this
        // function (contrary to values in the arrays which may be insecure).
        if (is_string($where)) {
            foreach ($this->preProcessHookObjects as $hookObject) {
                $hookObject->UPDATEquery_preProcessAction($table, $where, $fields_values, $no_quote_fields, $this);
            }
            $fields = array();
            if (is_array($fields_values) && count($fields_values)) {
                // Quote and escape values
                $nArr = $this->fullQuoteArray($fields_values, $table, $no_quote_fields, TRUE);
                foreach ($nArr as $k => $v) {
                    $fields[] = $k . '=' . $v;
                }
            }
            // Build query
            $query = 'UPDATE ' . $table . ' SET ' . implode(',', $fields) . ((string)$where !== '' ? ' WHERE ' . $where : '');
            if ($this->debugOutput || $this->store_lastBuiltQuery) {
                $this->debug_lastBuiltQuery = $query;
            }
            return $query;
        } else {
            throw new \InvalidArgumentException('TYPO3 Fatal Error: "Where" clause argument for UPDATE query was not a string in $this->UPDATEquery() !', 1270853880);
        }
    }

    public function DELETEquery($table, $where) {
        if (is_string($where)) {
            foreach ($this->preProcessHookObjects as $hookObject) {
                $hookObject->DELETEquery_preProcessAction($table, $where, $this);
            }
            // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
            $query = 'DELETE FROM ' . $table . ((string)$where !== '' ? ' WHERE ' . $where : '');
            if ($this->debugOutput || $this->store_lastBuiltQuery) {
                $this->debug_lastBuiltQuery = $query;
            }
            return $query;
        } else {
            throw new \InvalidArgumentException('TYPO3 Fatal Error: "Where" clause argument for DELETE query was not a string in $this->DELETEquery() !', 1270853881);
        }
    }

    public function SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '') {
        foreach ($this->preProcessHookObjects as $hookObject) {
            $hookObject->SELECTquery_preProcessAction($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit, $this);
        }
        // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
        // Build basic query
        $query = 'SELECT ' . $select_fields . ' FROM ' . $from_table . ((string)$where_clause !== '' ? ' WHERE ' . $where_clause : '');
        // Group by
        $query .= (string)$groupBy !== '' ? ' GROUP BY ' . $groupBy : '';
        // Order by
        $query .= (string)$orderBy !== '' ? ' ORDER BY ' . $orderBy : '';
        // Group by
        $query .= (string)$limit !== '' ? ' LIMIT ' . $limit : '';
        // Return query
        if ($this->debugOutput || $this->store_lastBuiltQuery) {
            $this->debug_lastBuiltQuery = $query;
        }
        return $query;
    }

    public function SELECTsubquery($select_fields, $from_table, $where_clause) {
        // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
        // Build basic query:
        $query = 'SELECT ' . $select_fields . ' FROM ' . $from_table . ((string)$where_clause !== '' ? ' WHERE ' . $where_clause : '');
        // Return query
        if ($this->debugOutput || $this->store_lastBuiltQuery) {
            $this->debug_lastBuiltQuery = $query;
        }
        return $query;
    }

    public function TRUNCATEquery($table) {
        foreach ($this->preProcessHookObjects as $hookObject) {
            $hookObject->TRUNCATEquery_preProcessAction($table, $this);
        }
        // Table should be "SQL-injection-safe" when supplied to this function
        // Build basic query:
        $query = 'TRUNCATE TABLE ' . $table;
        // Return query:
        if ($this->debugOutput || $this->store_lastBuiltQuery) {
            $this->debug_lastBuiltQuery = $query;
        }
        return $query;
    }

    public function listQuery($field, $value, $table) {
        $value = (string)$value;
        if (strpos($value, ',') !== FALSE) {
            throw new \InvalidArgumentException('$value must not contain a comma (,) in $this->listQuery() !', 1294585862);
        }
        $pattern = $this->quoteStr($value, $table);
        $where = 'FIND_IN_SET(\'' . $pattern . '\',' . $field . ')';
        return $where;
    }

    public function searchQuery($searchWords, $fields, $table, $constraint = self::AND_Constraint) {
        switch ($constraint) {
            case self::OR_Constraint:
                $constraint = 'OR';
                break;
            default:
                $constraint = 'AND';
        }

        $queryParts = array();
        foreach ($searchWords as $sw) {
            $like = ' LIKE \'%' . $this->quoteStr($sw, $table) . '%\'';
            $queryParts[] = $table . '.' . implode(($like . ' OR ' . $table . '.'), $fields) . $like;
        }
        $query = '(' . implode(') ' . $constraint . ' (', $queryParts) . ')';

        return $query;
    }

    /**************************************
     *
     * Prepared Query Support
     *
     **************************************/
    public function prepare_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', array $input_parameters = array()) {
        $query = $this->SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
        $preparedStatement = GeneralUtility::makeInstance('TYPO3\\CMS\\Core\\Database\\PreparedStatement', $query, $from_table, array());
        // Bind values to parameters
        foreach ($input_parameters as $key => $value) {
            $preparedStatement->bindValue($key, $value, PreparedStatement::PARAM_AUTOTYPE);
        }
        // Return prepared statement
        return $preparedStatement;
    }

    public function prepare_SELECTqueryArray(array $queryParts, array $input_parameters = array()) {
        return $this->prepare_SELECTquery($queryParts['SELECT'], $queryParts['FROM'], $queryParts['WHERE'], $queryParts['GROUPBY'], $queryParts['ORDERBY'], $queryParts['LIMIT'], $input_parameters);
    }

    public function prepare_PREPAREDquery($query, array $queryComponents) {
        if (!$this->isConnected) {
            $this->connectDB();
        }
        $stmt = $this->link->stmt_init();
        $success = $stmt->prepare($query);
        if ($this->debugOutput) {
            $this->debug('stmt_execute', $query);
        }
        return $success ? $stmt : NULL;
    }

    /**************************************
     *
     * Various helper functions
     *
     * Functions recommended to be used for
     * - escaping values,
     * - cleaning lists of values,
     * - stripping of excess ORDER BY/GROUP BY keywords
     *
     **************************************/
    public function fullQuoteStr($str, $table, $allowNull = FALSE) {
        if (!$this->isConnected) {
            $this->connectDB();
        }
        if ($allowNull && $str === NULL) {
            return 'NULL';
        }

        return '\'' . $this->link->real_escape_string($str) . '\'';
    }

    public function fullQuoteArray($arr, $table, $noQuote = FALSE, $allowNull = FALSE) {
        if (is_string($noQuote)) {
            $noQuote = explode(',', $noQuote);
        } elseif (!is_array($noQuote)) {
            $noQuote = FALSE;
        }
        foreach ($arr as $k => $v) {
            if ($noQuote === FALSE || !in_array($k, $noQuote)) {
                $arr[$k] = $this->fullQuoteStr($v, $table, $allowNull);
            }
        }
        return $arr;
    }

    public function quoteStr($str, $table) {
        if (!$this->isConnected) {
            $this->connectDB();
        }
        return $this->link->real_escape_string($str);
    }

    public function escapeStrForLike($str, $table) {
        return addcslashes($str, '_%');
    }

    public function cleanIntArray($arr) {
        return array_map('intval', $arr);
    }

    public function cleanIntList($list) {
        return implode(',', GeneralUtility::intExplode(',', $list));
    }

    public function stripOrderBy($str) {
        return preg_replace('/^(?:ORDER[[:space:]]*BY[[:space:]]*)+/i', '', trim($str));
    }

    public function stripGroupBy($str) {
        return preg_replace('/^(?:GROUP[[:space:]]*BY[[:space:]]*)+/i', '', trim($str));
    }

    public function splitGroupOrderLimit($str) {
        // Prepending a space to make sure "[[:space:]]+" will find a space there
        // for the first element.
        $str = ' ' . $str;
        // Init output array:
        $wgolParts = array(
            'WHERE' => '',
            'GROUPBY' => '',
            'ORDERBY' => '',
            'LIMIT' => ''
        );
        // Find LIMIT
        $reg = array();
        if (preg_match('/^(.*)[[:space:]]+LIMIT[[:space:]]+([[:alnum:][:space:],._]+)$/i', $str, $reg)) {
            $wgolParts['LIMIT'] = trim($reg[2]);
            $str = $reg[1];
        }
        // Find ORDER BY
        $reg = array();
        if (preg_match('/^(.*)[[:space:]]+ORDER[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._]+)$/i', $str, $reg)) {
            $wgolParts['ORDERBY'] = trim($reg[2]);
            $str = $reg[1];
        }
        // Find GROUP BY
        $reg = array();
        if (preg_match('/^(.*)[[:space:]]+GROUP[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._]+)$/i', $str, $reg)) {
            $wgolParts['GROUPBY'] = trim($reg[2]);
            $str = $reg[1];
        }
        // Rest is assumed to be "WHERE" clause
        $wgolParts['WHERE'] = $str;
        return $wgolParts;
    }

    public function getDateTimeFormats($table) {
        return self::$dateTimeFormats;
    }

    /**************************************
     *
     * MySQL(i) wrapper functions
     * (For use in your applications)
     *
     **************************************/
    public function sql_query($query) {
        $res = $this->query($query);
        if ($this->debugOutput) {
            $this->debug('sql_query', $query);
        }
        return $res;
    }

    public function sql_error() {
        return $this->link->error;
    }

    public function sql_errno() {
        return $this->link->errno;
    }

    public function sql_num_rows($res) {
        if ($this->debug_check_recordset($res)) {
            return $res->num_rows;
        } else {
            return FALSE;
        }
    }

    public function sql_fetch_assoc($res) {
        if ($this->debug_check_recordset($res)) {
            $result = $res->fetch_assoc();
            if ($result === NULL) {
                // Needed for compatibility
                $result = FALSE;
            }
            return $result;
        } else {
            return FALSE;
        }
    }

    public function sql_fetch_row($res) {
        if ($this->debug_check_recordset($res)) {
            $result = $res->fetch_row();
            if ($result === NULL) {
                // Needed for compatibility
                $result = FALSE;
            }
            return $result;
        } else {
            return FALSE;
        }
    }

    public function sql_free_result($res) {
        if ($this->debug_check_recordset($res) && is_object($res)) {
            $res->free();
            return TRUE;
        } else {
            return FALSE;
        }
    }

    public function sql_insert_id() {
        return $this->link->insert_id;
    }

    public function sql_affected_rows() {
        return $this->link->affected_rows;
    }

    public function sql_data_seek($res, $seek) {
        if ($this->debug_check_recordset($res)) {
            return $res->data_seek($seek);
        } else {
            return FALSE;
        }
    }

    public function sql_field_type($res, $pointer) {
        // mysql_field_type compatibility map
        // taken from: http://www.php.net/manual/en/mysqli-result.fetch-field-direct.php#89117
        // Constant numbers see http://php.net/manual/en/mysqli.constants.php
        $mysql_data_type_hash = array(
            1=>'tinyint',
            2=>'smallint',
            3=>'int',
            4=>'float',
            5=>'double',
            7=>'timestamp',
            8=>'bigint',
            9=>'mediumint',
            10=>'date',
            11=>'time',
            12=>'datetime',
            13=>'year',
            16=>'bit',
            //252 is currently mapped to all text and blob types (MySQL 5.0.51a)
            253=>'varchar',
            254=>'char',
            246=>'decimal'
        );
        if ($this->debug_check_recordset($res)) {
            $metaInfo = $res->fetch_field_direct($pointer);
            if ($metaInfo === FALSE) {
                return FALSE;
            }
            return $mysql_data_type_hash[$metaInfo->type];
        } else {
            return FALSE;
        }
    }

    public function sql_pconnect($host = NULL, $username = NULL, $password = NULL) {
        if ($this->isConnected) {
            return $this->link;
        }

        if (!extension_loaded('mysqli')) {
            throw new \RuntimeException(
                'Database Error: PHP mysqli extension not loaded. This is a must have for TYPO3 CMS!',
                1271492607
            );
        }

        if ($host || $username || $password) {
            $this->handleDeprecatedConnectArguments($host, $username, $password);
        }

        $host = $this->persistentDatabaseConnection
            ? 'p:' . $this->databaseHost
            : $this->databaseHost;

        $this->link = mysqli_init();
        $connected = $this->link->real_connect(
            $host,
            $this->databaseUsername,
            $this->databaseUserPassword,
            NULL,
            (int)$this->databasePort,
            $this->databaseSocket,
            $this->connectionCompression ? MYSQLI_CLIENT_COMPRESS : 0
        );

        if ($connected) {
            $this->isConnected = TRUE;

            if ($this->link->set_charset($this->connectionCharset) === FALSE) {
                GeneralUtility::sysLog(
                    'Error setting connection charset to "' . $this->connectionCharset . '"',
                    'Core',
                    GeneralUtility::SYSLOG_SEVERITY_ERROR
                );
            }

            foreach ($this->initializeCommandsAfterConnect as $command) {
                if ($this->query($command) === FALSE) {
                    GeneralUtility::sysLog(
                        'Could not initialize DB connection with query "' . $command . '": ' . $this->sql_error(),
                        'Core',
                        GeneralUtility::SYSLOG_SEVERITY_ERROR
                    );
                }
            }
            $this->setSqlMode();
            $this->checkConnectionCharset();
        } else {
            // @TODO: This should raise an exception. Would be useful especially to work during installation.
            $error_msg = $this->link->connect_error;
            $this->link = NULL;
            GeneralUtility::sysLog(
                'Could not connect to MySQL server ' . $host . ' with user ' . $username . ': ' . $error_msg,
                'Core',
                GeneralUtility::SYSLOG_SEVERITY_FATAL
            );
        }
        return $this->link;
    }

    protected function setSqlMode() {
        $resource = $this->sql_query('SELECT @@SESSION.sql_mode;');
        if ($resource) {
            $result = $this->sql_fetch_row($resource);
            if (isset($result[0]) && $result[0] && strpos($result[0], 'NO_BACKSLASH_ESCAPES') !== FALSE) {
                $modes = array_diff(GeneralUtility::trimExplode(',', $result[0]), array('NO_BACKSLASH_ESCAPES'));
                $query = 'SET sql_mode=\'' . $this->link->real_escape_string(implode(',', $modes)) . '\';';
                $this->sql_query($query);
                GeneralUtility::sysLog(
                    'NO_BACKSLASH_ESCAPES could not be removed from SQL mode: ' . $this->sql_error(),
                    'Core',
                    GeneralUtility::SYSLOG_SEVERITY_ERROR
                );
            }
        }
    }

    public function sql_select_db($TYPO3_db = NULL) {
        if (!$this->isConnected) {
            $this->connectDB();
        }

        if ($TYPO3_db) {
            GeneralUtility::deprecationLog(
                'DatabaseConnection->sql_select_db() should be called without arguments.' .
                    ' Use the setDatabaseName() before. Will be removed two versions after 6.1.'
            );
        } else {
            $TYPO3_db = $this->databaseName;
        }

        $ret = $this->link->select_db($TYPO3_db);
        if (!$ret) {
            GeneralUtility::sysLog(
                'Could not select MySQL database ' . $TYPO3_db . ': ' . $this->sql_error(),
                'Core',
                GeneralUtility::SYSLOG_SEVERITY_FATAL
            );
        }
        return $ret;
    }

    /**************************************
     *
     * SQL admin functions
     * (For use in the Install Tool and Extension Manager)
     *
     **************************************/
    public function admin_get_dbs() {
        $dbArr = array();
        $db_list = $this->query("SELECT SCHEMA_NAME FROM information_schema.SCHEMATA");
        if ($db_list === FALSE) {
            throw new \RuntimeException(
                'MySQL Error: Cannot get tablenames: "' . $this->sql_error() . '"!',
                1378457171
            );
        } else {
            while ($row = $db_list->fetch_object()) {
                try {
                    $this->setDatabaseName($row->SCHEMA_NAME);
                    if ($this->sql_select_db()) {
                        $dbArr[] = $row->SCHEMA_NAME;
                    }
                } catch (\RuntimeException $exception) {
                    // The exception happens if we cannot connect to the database
                    // (usually due to missing permissions). This is ok here.
                    // We catch the exception, skip the database and continue.
                }
            }
        }
        return $dbArr;
    }

    public function admin_get_tables() {
        $whichTables = array();
        $tables_result = $this->query('SHOW TABLE STATUS FROM `' . $this->databaseName . '`');
        if ($tables_result !== FALSE) {
            while ($theTable = $tables_result->fetch_assoc()) {
                $whichTables[$theTable['Name']] = $theTable;
            }
            $tables_result->free();
        }
        return $whichTables;
    }

    public function admin_get_fields($tableName) {
        $output = array();
        $columns_res = $this->query('SHOW COLUMNS FROM `' . $tableName . '`');
        if ($columns_res !== FALSE) {
            while ($fieldRow = $columns_res->fetch_assoc()) {
                $output[$fieldRow['Field']] = $fieldRow;
            }
            $columns_res->free();
        }
        return $output;
    }

    public function admin_get_keys($tableName) {
        $output = array();
        $keyRes = $this->query('SHOW KEYS FROM `' . $tableName . '`');
        if ($keyRes !== FALSE) {
            while ($keyRow = $keyRes->fetch_assoc()) {
                $output[] = $keyRow;
            }
            $keyRes->free();
        }
        return $output;
    }

    public function admin_get_charsets() {
        $output = array();
        $columns_res = $this->query('SHOW CHARACTER SET');
        if ($columns_res !== FALSE) {
            while ($row = $columns_res->fetch_assoc()) {
                $output[$row['Charset']] = $row;
            }
            $columns_res->free();
        }
        return $output;
    }

    public function admin_query($query) {
        $res = $this->query($query);
        if ($this->debugOutput) {
            $this->debug('admin_query', $query);
        }
        return $res;
    }

    /******************************
     *
     * Connect handling
     *
     ******************************/

    public function setDatabaseHost($host = 'localhost') {
        $this->disconnectIfConnected();
        $this->databaseHost = $host;
    }

    public function setDatabasePort($port = 3306) {
        $this->disconnectIfConnected();
        $this->databasePort = (int)$port;
    }

    public function setDatabaseSocket($socket = NULL) {
        $this->disconnectIfConnected();
        $this->databaseSocket = $socket;
    }

    public function setDatabaseName($name) {
        $this->disconnectIfConnected();
        $this->databaseName = $name;
    }

    public function setDatabaseUsername($username) {
        $this->disconnectIfConnected();
        $this->databaseUsername = $username;
    }

    public function setDatabasePassword($password) {
        $this->disconnectIfConnected();
        $this->databaseUserPassword = $password;
    }

    public function setPersistentDatabaseConnection($persistentDatabaseConnection) {
        $this->disconnectIfConnected();
        $this->persistentDatabaseConnection = (bool)$persistentDatabaseConnection;
    }

    public function setConnectionCompression($connectionCompression) {
        $this->disconnectIfConnected();
        $this->connectionCompression = (bool)$connectionCompression;
    }

    public function setInitializeCommandsAfterConnect(array $commands) {
        $this->disconnectIfConnected();
        $this->initializeCommandsAfterConnect = $commands;
    }

    public function setConnectionCharset($connectionCharset = 'utf8') {
        $this->disconnectIfConnected();
        $this->connectionCharset = $connectionCharset;
    }

    public function connectDB($host = NULL, $username = NULL, $password = NULL, $db = NULL) {
        // Early return if connected already
        if ($this->isConnected) {
            return;
        }

        if (!$this->databaseName && !$db) {
            throw new \RuntimeException(
                'TYPO3 Fatal Error: No database selected!',
                1270853882
            );
        }

        if ($host || $username || $password || $db) {
            $this->handleDeprecatedConnectArguments($host, $username, $password, $db);
        }

        if ($this->sql_pconnect()) {
            if (!$this->sql_select_db()) {
                throw new \RuntimeException(
                    'TYPO3 Fatal Error: Cannot connect to the current database, "' . $this->databaseName . '"!',
                    1270853883
                );
            }
        } else {
            throw new \RuntimeException(
                'TYPO3 Fatal Error: The current username, password or host was not accepted when the connection to the database was attempted to be established!',
                1270853884
            );
        }

        // Prepare user defined objects (if any) for hooks which extend query methods
        $this->preProcessHookObjects = array();
        $this->postProcessHookObjects = array();
        if (is_array($GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_db.php']['queryProcessors'])) {
            foreach ($GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_db.php']['queryProcessors'] as $classRef) {
                $hookObject = GeneralUtility::getUserObj($classRef);
                if (!(
                    $hookObject instanceof PreProcessQueryHookInterface
                    || $hookObject instanceof PostProcessQueryHookInterface
                )) {
                    throw new \UnexpectedValueException(
                        '$hookObject must either implement interface TYPO3\\CMS\\Core\\Database\\PreProcessQueryHookInterface or interface TYPO3\\CMS\\Core\\Database\\PostProcessQueryHookInterface',
                        1299158548
                    );
                }
                if ($hookObject instanceof PreProcessQueryHookInterface) {
                    $this->preProcessHookObjects[] = $hookObject;
                }
                if ($hookObject instanceof PostProcessQueryHookInterface) {
                    $this->postProcessHookObjects[] = $hookObject;
                }
            }
        }
    }

    public function isConnected() {
        // We think we're still connected
        if ($this->isConnected) {
            // Check if this is really the case or if the database server has gone away for some reason
            // Using mysqlnd ping() does not reconnect (which we would not want anyway since charset etc would not be reinitialized that way)
            $this->isConnected = $this->link->ping();
        }
        return $this->isConnected;
    }

    protected function checkConnectionCharset() {
        $sessionResult = $this->sql_query('SHOW SESSION VARIABLES LIKE \'character_set%\'');

        if ($sessionResult === FALSE) {
            GeneralUtility::sysLog(
                'Error while retrieving the current charset session variables from the database: ' . $this->sql_error(),
                'Core',
                GeneralUtility::SYSLOG_SEVERITY_ERROR
            );
            throw new \RuntimeException(
                'TYPO3 Fatal Error: Could not determine the current charset of the database.',
                1381847136
            );
        }

        $charsetVariables = array();
        while (($row = $this->sql_fetch_row($sessionResult)) !== FALSE) {
            $variableName = $row[0];
            $variableValue = $row[1];
            $charsetVariables[$variableName] = $variableValue;
        }
        $this->sql_free_result($sessionResult);

        // These variables are set with the "Set names" command which was
        // used in the past. This is why we check them.
        $charsetRequiredVariables = array(
            'character_set_client',
            'character_set_results',
            'character_set_connection',
        );

        $hasValidCharset = TRUE;
        foreach ($charsetRequiredVariables as $variableName) {
            if (empty($charsetVariables[$variableName])) {
                GeneralUtility::sysLog(
                    'A required session variable is missing in the current MySQL connection: ' . $variableName,
                    'Core',
                    GeneralUtility::SYSLOG_SEVERITY_ERROR
                );
                throw new \RuntimeException(
                    'TYPO3 Fatal Error: Could not determine the value of the database session variable: ' . $variableName,
                    1381847779
                );
            }

            if ($charsetVariables[$variableName] !== $this->connectionCharset) {
                $hasValidCharset = FALSE;
                break;
            }
        }

        if (!$hasValidCharset) {
            throw new \RuntimeException(
                'It looks like the character set ' . $this->connectionCharset . ' is not used for this connection even though it is configured as connection charset. ' .
                'This TYPO3 installation is using the $GLOBALS[\'TYPO3_CONF_VARS\'][\'SYS\'][\'setDBinit\'] property with the following value: "' .
                $GLOBALS['TYPO3_CONF_VARS']['SYS']['setDBinit'] . '". Please make sure that this command does not overwrite the configured charset. ' .
                'Please note that for the TYPO3 database everything other than utf8 is unsupported since version 4.7.',
                1389697515
            );
        }
    }

    protected function disconnectIfConnected() {
        if ($this->isConnected) {
            $this->link->close();
            $this->isConnected = FALSE;
        }
    }

    public function getDatabaseHandle() {
        return $this->link;
    }

    public function setDatabaseHandle($handle) {
        $this->link = $handle;
    }

    protected function handleDeprecatedConnectArguments($host = NULL, $username = NULL, $password = NULL, $db = NULL) {
        GeneralUtility::deprecationLog(
            'DatabaseConnection->sql_pconnect() and DatabaseConnection->connectDB() should be ' .
            'called without arguments. Use the setters instead.'
        );
        if ($host) {
            if (strpos($host, ':') > 0) {
                list($databaseHost, $databasePort) = explode(':', $host);
                $this->setDatabaseHost($databaseHost);
                $this->setDatabasePort($databasePort);
            } else {
                $this->setDatabaseHost($host);
            }
        }
        if ($username) {
            $this->setDatabaseUsername($username);
        }
        if ($password) {
            $this->setDatabasePassword($password);
        }
        if ($db) {
            $this->setDatabaseName($db);
        }
    }

    /******************************
     *
     * Debugging
     *
     ******************************/
    public function debug($func, $query = '') {
        $error = $this->sql_error();
        if ($error || (int)$this->debugOutput === 2) {
            \TYPO3\CMS\Core\Utility\DebugUtility::debug(
                array(
                    'caller' => 'TYPO3\\CMS\\Core\\Database\\DatabaseConnection::' . $func,
                    'ERROR' => $error,
                    'lastBuiltQuery' => $query ? $query : $this->debug_lastBuiltQuery,
                    'debug_backtrace' => \TYPO3\CMS\Core\Utility\DebugUtility::debugTrail()
                ),
                $func,
                is_object($GLOBALS['error']) && @is_callable(array($GLOBALS['error'], 'debug'))
                    ? ''
                    : 'DB Error'
            );
        }
    }

    public function debug_check_recordset($res) {
        if ($res !== FALSE && $res !== NULL) {
            return TRUE;
        }
        $msg = 'Invalid database result detected';
        $trace = debug_backtrace();
        array_shift($trace);
        $cnt = count($trace);
        for ($i = 0; $i < $cnt; $i++) {
            // Complete objects are too large for the log
            if (isset($trace['object'])) {
                unset($trace['object']);
            }
        }
        $msg .= ': function TYPO3\\CMS\\Core\\Database\\DatabaseConnection->' . $trace[0]['function'] . ' called from file ' . substr($trace[0]['file'], (strlen(PATH_site) + 2)) . ' in line ' . $trace[0]['line'];
        GeneralUtility::sysLog(
            $msg . '. Use a devLog extension to get more details.',
            'Core/t3lib_db',
            GeneralUtility::SYSLOG_SEVERITY_ERROR
        );
        // Send to devLog if enabled
        if (TYPO3_DLOG) {
            $debugLogData = array(
                'SQL Error' => $this->sql_error(),
                'Backtrace' => $trace
            );
            if ($this->debug_lastBuiltQuery) {
                $debugLogData = array('SQL Query' => $this->debug_lastBuiltQuery) + $debugLogData;
            }
            GeneralUtility::devLog($msg . '.', 'Core/t3lib_db', 3, $debugLogData);
        }
        return FALSE;
    }

    protected function explain($query, $from_table, $row_count) {
        $debugAllowedForIp = GeneralUtility::cmpIP(
            GeneralUtility::getIndpEnv('REMOTE_ADDR'),
            $GLOBALS['TYPO3_CONF_VARS']['SYS']['devIPmask']
        );
        if (
            (int)$this->explainOutput == 1
            || ((int)$this->explainOutput == 2 && $debugAllowedForIp)
        ) {
            // Raw HTML output
            $explainMode = 1;
        } elseif ((int)$this->explainOutput == 3 && is_object($GLOBALS['TT'])) {
            // Embed the output into the TS admin panel
            $explainMode = 2;
        } else {
            return FALSE;
        }
        $error = $this->sql_error();
        $trail = \TYPO3\CMS\Core\Utility\DebugUtility::debugTrail();
        $explain_tables = array();
        $explain_output = array();
        $res = $this->sql_query('EXPLAIN ' . $query, $this->link);
        if (is_a($res, '\\mysqli_result')) {
            while ($tempRow = $this->sql_fetch_assoc($res)) {
                $explain_output[] = $tempRow;
                $explain_tables[] = $tempRow['table'];
            }
            $this->sql_free_result($res);
        }
        $indices_output = array();
        // Notice: Rows are skipped if there is only one result, or if no conditions are set
        if (
            $explain_output[0]['rows'] > 1
            || GeneralUtility::inList('ALL', $explain_output[0]['type'])
        ) {
            // Only enable output if it's really useful
            $debug = TRUE;
            foreach ($explain_tables as $table) {
                $tableRes = $this->sql_query('SHOW TABLE STATUS LIKE \'' . $table . '\'');
                $isTable = $this->sql_num_rows($tableRes);
                if ($isTable) {
                    $res = $this->sql_query('SHOW INDEX FROM ' . $table, $this->link);
                    if (is_a($res, '\\mysqli_result')) {
                        while ($tempRow = $this->sql_fetch_assoc($res)) {
                            $indices_output[] = $tempRow;
                        }
                        $this->sql_free_result($res);
                    }
                }
                $this->sql_free_result($tableRes);
            }
        } else {
            $debug = FALSE;
        }
        if ($debug) {
            if ($explainMode) {
                $data = array();
                $data['query'] = $query;
                $data['trail'] = $trail;
                $data['row_count'] = $row_count;
                if ($error) {
                    $data['error'] = $error;
                }
                if (count($explain_output)) {
                    $data['explain'] = $explain_output;
                }
                if (count($indices_output)) {
                    $data['indices'] = $indices_output;
                }
                if ($explainMode == 1) {
                    \TYPO3\CMS\Core\Utility\DebugUtility::debug($data, 'Tables: ' . $from_table, 'DB SQL EXPLAIN');
                } elseif ($explainMode == 2) {
                    $GLOBALS['TT']->setTSselectQuery($data);
                }
            }
            return TRUE;
        }
        return FALSE;
    }

    public function __sleep() {
        $this->disconnectIfConnected();
        return array(
            'debugOutput',
            'explainOutput',
            'databaseHost',
            'databasePort',
            'databaseSocket',
            'databaseName',
            'databaseUsername',
            'databaseUserPassword',
            'persistentDatabaseConnection',
            'connectionCompression',
            'initializeCommandsAfterConnect',
            'default_charset',
        );
    }
}