typo3/sysext/core/Classes/Database/DatabaseConnection.php

   1 <?php
   2 namespace TYPO3\CMS\Core\Database;
   3
   4 /*
   5  * This file is part of the TYPO3 CMS project.
   6  *
   7  * It is free software; you can redistribute it and/or modify it under
   8  * the terms of the GNU General Public License, either version 2
   9  * of the License, or any later version.
  10  *
  11  * For the full copyright and license information, please read the
  12  * LICENSE.txt file that was distributed with this source code.
  13  *
  14  * The TYPO3 project - inspiring people to share!
  15  */
  16
  17 use TYPO3\CMS\Core\Utility\GeneralUtility;
  18 use TYPO3\CMS\Core\Utility\StringUtility;
  19
  20 /**
  21  * Contains the class "DatabaseConnection" containing functions for building SQL queries
  22  * and mysqli wrappers, thus providing a foundational API to all database
  23  * interaction.
  24  * This class is instantiated globally as $TYPO3_DB in TYPO3 scripts.
  25  *
  26  * TYPO3 "database wrapper" class (new in 3.6.0)
  27  * This class contains
  28  * - abstraction functions for executing INSERT/UPDATE/DELETE/SELECT queries ("Query execution"; These are REQUIRED for all future connectivity to the database, thus ensuring DBAL compliance!)
  29  * - functions for building SQL queries (INSERT/UPDATE/DELETE/SELECT) ("Query building"); These are transitional functions for building SQL queries in a more automated way. Use these to build queries instead of doing it manually in your code!
  30  * - mysqli wrapper functions; These are transitional functions. By a simple search/replace you should be able to substitute all mysql*() calls with $GLOBALS['TYPO3_DB']->sql*() and your application will work out of the box. YOU CANNOT (legally) use any mysqli functions not found as wrapper functions in this class!
  31  * See the Project Coding Guidelines (doc_core_cgl) for more instructions on best-practise
  32  *
  33  * This class is not in itself a complete database abstraction layer but can be extended to be a DBAL (by extensions, see "dbal" for example)
  34  * ALL connectivity to the database in TYPO3 must be done through this class!
  35  * The points of this class are:
  36  * - To direct all database calls through this class so it becomes possible to implement DBAL with extensions.
  37  * - To keep it very easy to use for developers used to MySQL in PHP - and preserve as much performance as possible when TYPO3 is used with MySQL directly...
  38  * - To create an interface for DBAL implemented by extensions; (Eg. making possible escaping characters, clob/blob handling, reserved words handling)
  39  * - Benchmarking the DB bottleneck queries will become much easier; Will make it easier to find optimization possibilities.
  40  *
  41  * USE:
  42  * In all TYPO3 scripts the global variable $TYPO3_DB is an instance of this class. Use that.
  43  * Eg. $GLOBALS['TYPO3_DB']->sql_fetch_assoc()
  44  */
  45 class DatabaseConnection
  46 {
  47     /**
  48      * The AND constraint in where clause
  49      *
  50      * @var string
  51      */
  52     const AND_Constraint = 'AND';
  53
  54     /**
  55      * The OR constraint in where clause
  56      *
  57      * @var string
  58      */
  59     const OR_Constraint = 'OR';
  60
  61     /**
  62      * Set "TRUE" or "1" if you want database errors outputted. Set to "2" if you also want successful database actions outputted.
  63      *
  64      * @var bool|int
  65      */
  66     public $debugOutput = false;
  67
  68     /**
  69      * Internally: Set to last built query (not necessarily executed...)
  70      *
  71      * @var string
  72      */
  73     public $debug_lastBuiltQuery = '';
  74
  75     /**
  76      * Set "TRUE" if you want the last built query to be stored in $debug_lastBuiltQuery independent of $this->debugOutput
  77      *
  78      * @var bool
  79      */
  80     public $store_lastBuiltQuery = false;
  81
  82     /**
  83      * Set this to 1 to get queries explained (devIPmask must match). Set the value to 2 to the same but disregarding the devIPmask.
  84      * 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.
  85      *
  86      * @var bool
  87      */
  88     public $explainOutput = 0;
  89
  90     /**
  91      * @var string Database host to connect to
  92      */
  93     protected $databaseHost = '';
  94
  95     /**
  96      * @var int Database port to connect to
  97      */
  98     protected $databasePort = 3306;
  99
 100     /**
 101      * @var string|NULL Database socket to connect to
 102      */
 103     protected $databaseSocket = null;
 104
 105     /**
 106      * @var string Database name to connect to
 107      */
 108     protected $databaseName = '';
 109
 110     /**
 111      * @var string Database user to connect with
 112      */
 113     protected $databaseUsername = '';
 114
 115     /**
 116      * @var string Database password to connect with
 117      */
 118     protected $databaseUserPassword = '';
 119
 120     /**
 121      * @var bool TRUE if database connection should be persistent
 122      * @see http://php.net/manual/de/mysqli.persistconns.php
 123      */
 124     protected $persistentDatabaseConnection = false;
 125
 126     /**
 127      * @var bool TRUE if connection between client and sql server is compressed
 128      */
 129     protected $connectionCompression = false;
 130
 131     /**
 132      * The charset for the connection; will be passed on to
 133      * mysqli_set_charset during connection initialization.
 134      *
 135      * @var string
 136      */
 137     protected $connectionCharset = 'utf8';
 138
 139     /**
 140      * @var array List of commands executed after connection was established
 141      */
 142     protected $initializeCommandsAfterConnect = array();
 143
 144     /**
 145      * @var bool TRUE if database connection is established
 146      */
 147     protected $isConnected = false;
 148
 149     /**
 150      * @var \mysqli $link Default database link object
 151      */
 152     protected $link = null;
 153
 154     /**
 155      * Default character set, applies unless character set or collation are explicitly set
 156      *
 157      * @var string
 158      */
 159     public $default_charset = 'utf8';
 160
 161     /**
 162      * @var array<PostProcessQueryHookInterface>
 163      */
 164     protected $preProcessHookObjects = array();
 165
 166     /**
 167      * @var array<PreProcessQueryHookInterface>
 168      */
 169     protected $postProcessHookObjects = array();
 170
 171     /**
 172      * the date and time formats compatible with the database in general
 173      *
 174      * @var array
 175      */
 176     protected static $dateTimeFormats = array(
 177         'date' => array(
 178             'empty' => '0000-00-00',
 179             'format' => 'Y-m-d'
 180         ),
 181         'datetime' => array(
 182             'empty' => '0000-00-00 00:00:00',
 183             'format' => 'Y-m-d H:i:s'
 184         )
 185     );
 186
 187     /**
 188      * Initialize the database connection
 189      *
 190      * @return void
 191      */
 192     public function initialize()
 193     {
 194         // Intentionally blank as this will be overloaded by DBAL
 195     }
 196
 197     /************************************
 198      *
 199      * Query execution
 200      *
 201      * These functions are the RECOMMENDED DBAL functions for use in your applications
 202      * Using these functions will allow the DBAL to use alternative ways of accessing data (contrary to if a query is returned!)
 203      * They compile a query AND execute it immediately and then return the result
 204      * This principle heightens our ability to create various forms of DBAL of the functions.
 205      * Generally: We want to return a result pointer/object, never queries.
 206      * Also, having the table name together with the actual query execution allows us to direct the request to other databases.
 207      *
 208      **************************************/
 209
 210     /**
 211      * Creates and executes an INSERT SQL-statement for $table from the array with field/value pairs $fields_values.
 212      * Using this function specifically allows us to handle BLOB and CLOB fields depending on DB
 213      *
 214      * @param string $table Table name
 215      * @param array $fields_values Field values as key=>value pairs. Values will be escaped internally. Typically you would fill an array like "$insertFields" with 'fieldname'=>'value' and pass it to this function as argument.
 216      * @param bool|array|string $no_quote_fields See fullQuoteArray()
 217      * @return bool|\mysqli_result|object MySQLi result object / DBAL object
 218      */
 219     public function exec_INSERTquery($table, $fields_values, $no_quote_fields = false)
 220     {
 221         $res = $this->query($this->INSERTquery($table, $fields_values, $no_quote_fields));
 222         if ($this->debugOutput) {
 223             $this->debug('exec_INSERTquery');
 224         }
 225         foreach ($this->postProcessHookObjects as $hookObject) {
 226             /** @var $hookObject PostProcessQueryHookInterface */
 227             $hookObject->exec_INSERTquery_postProcessAction($table, $fields_values, $no_quote_fields, $this);
 228         }
 229         return $res;
 230     }
 231
 232     /**
 233      * Creates and executes an INSERT SQL-statement for $table with multiple rows.
 234      *
 235      * @param string $table Table name
 236      * @param array $fields Field names
 237      * @param array $rows Table rows. Each row should be an array with field values mapping to $fields
 238      * @param bool|array|string $no_quote_fields See fullQuoteArray()
 239      * @return bool|\mysqli_result|object MySQLi result object / DBAL object
 240      */
 241     public function exec_INSERTmultipleRows($table, array $fields, array $rows, $no_quote_fields = false)
 242     {
 243         $res = $this->query($this->INSERTmultipleRows($table, $fields, $rows, $no_quote_fields));
 244         if ($this->debugOutput) {
 245             $this->debug('exec_INSERTmultipleRows');
 246         }
 247         foreach ($this->postProcessHookObjects as $hookObject) {
 248             /** @var $hookObject PostProcessQueryHookInterface */
 249             $hookObject->exec_INSERTmultipleRows_postProcessAction($table, $fields, $rows, $no_quote_fields, $this);
 250         }
 251         return $res;
 252     }
 253
 254     /**
 255      * Creates and executes an UPDATE SQL-statement for $table where $where-clause (typ. 'uid=...') from the array with field/value pairs $fields_values.
 256      * Using this function specifically allow us to handle BLOB and CLOB fields depending on DB
 257      *
 258      * @param string $table Database tablename
 259      * @param string $where WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
 260      * @param array $fields_values Field values as key=>value pairs. Values will be escaped internally. Typically you would fill an array like "$updateFields" with 'fieldname'=>'value' and pass it to this function as argument.
 261      * @param bool|array|string $no_quote_fields See fullQuoteArray()
 262      * @return bool|\mysqli_result|object MySQLi result object / DBAL object
 263      */
 264     public function exec_UPDATEquery($table, $where, $fields_values, $no_quote_fields = false)
 265     {
 266         $res = $this->query($this->UPDATEquery($table, $where, $fields_values, $no_quote_fields));
 267         if ($this->debugOutput) {
 268             $this->debug('exec_UPDATEquery');
 269         }
 270         foreach ($this->postProcessHookObjects as $hookObject) {
 271             /** @var $hookObject PostProcessQueryHookInterface */
 272             $hookObject->exec_UPDATEquery_postProcessAction($table, $where, $fields_values, $no_quote_fields, $this);
 273         }
 274         return $res;
 275     }
 276
 277     /**
 278      * Creates and executes a DELETE SQL-statement for $table where $where-clause
 279      *
 280      * @param string $table Database tablename
 281      * @param string $where WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
 282      * @return bool|\mysqli_result|object MySQLi result object / DBAL object
 283      */
 284     public function exec_DELETEquery($table, $where)
 285     {
 286         $res = $this->query($this->DELETEquery($table, $where));
 287         if ($this->debugOutput) {
 288             $this->debug('exec_DELETEquery');
 289         }
 290         foreach ($this->postProcessHookObjects as $hookObject) {
 291             /** @var $hookObject PostProcessQueryHookInterface */
 292             $hookObject->exec_DELETEquery_postProcessAction($table, $where, $this);
 293         }
 294         return $res;
 295     }
 296
 297     /**
 298      * Creates and executes a SELECT SQL-statement
 299      * Using this function specifically allow us to handle the LIMIT feature independently of DB.
 300      *
 301      * @param string $select_fields List of fields to select from the table. This is what comes right after "SELECT ...". Required value.
 302      * @param string $from_table Table(s) from which to select. This is what comes right after "FROM ...". Required value.
 303      * @param string $where_clause Additional WHERE clauses put in the end of the query. NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself! DO NOT PUT IN GROUP BY, ORDER BY or LIMIT!
 304      * @param string $groupBy Optional GROUP BY field(s), if none, supply blank string.
 305      * @param string $orderBy Optional ORDER BY field(s), if none, supply blank string.
 306      * @param string $limit Optional LIMIT value ([begin,]max), if none, supply blank string.
 307      * @return bool|\mysqli_result|object MySQLi result object / DBAL object
 308      */
 309     public function exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '')
 310     {
 311         $query = $this->SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
 312         $res = $this->query($query);
 313         if ($this->debugOutput) {
 314             $this->debug('exec_SELECTquery');
 315         }
 316         if ($this->explainOutput) {
 317             $this->explain($query, $from_table, $res->num_rows);
 318         }
 319         foreach ($this->postProcessHookObjects as $hookObject) {
 320             /** @var $hookObject PostProcessQueryHookInterface */
 321             $hookObject->exec_SELECTquery_postProcessAction($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', $this);
 322         }
 323         return $res;
 324     }
 325
 326     /**
 327      * Creates and executes a SELECT query, selecting fields ($select) from two/three tables joined
 328      * Use $mm_table together with $local_table or $foreign_table to select over two tables. Or use all three tables to select the full MM-relation.
 329      * The JOIN is done with [$local_table].uid <--> [$mm_table].uid_local  / [$mm_table].uid_foreign <--> [$foreign_table].uid
 330      * The function is very useful for selecting MM-relations between tables adhering to the MM-format used by TCE (TYPO3 Core Engine). See the section on $GLOBALS['TCA'] in Inside TYPO3 for more details.
 331      *
 332      * @param string $select Field list for SELECT
 333      * @param string $local_table Tablename, local table
 334      * @param string $mm_table Tablename, relation table
 335      * @param string $foreign_table Tablename, foreign table
 336      * @param string $whereClause Optional additional WHERE clauses put in the end of the query. NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself! DO NOT PUT IN GROUP BY, ORDER BY or LIMIT! You have to prepend 'AND ' to this parameter yourself!
 337      * @param string $groupBy Optional GROUP BY field(s), if none, supply blank string.
 338      * @param string $orderBy Optional ORDER BY field(s), if none, supply blank string.
 339      * @param string $limit Optional LIMIT value ([begin,]max), if none, supply blank string.
 340      * @return bool|\mysqli_result|object MySQLi result object / DBAL object
 341      * @see exec_SELECTquery()
 342      */
 343     public function exec_SELECT_mm_query($select, $local_table, $mm_table, $foreign_table, $whereClause = '', $groupBy = '', $orderBy = '', $limit = '')
 344     {
 345         $queryParts = $this->getSelectMmQueryParts($select, $local_table, $mm_table, $foreign_table, $whereClause, $groupBy, $orderBy, $limit);
 346         return $this->exec_SELECT_queryArray($queryParts);
 347     }
 348
 349     /**
 350      * Executes a select based on input query parts array
 351      *
 352      * @param array $queryParts Query parts array
 353      * @return bool|\mysqli_result|object MySQLi result object / DBAL object
 354      * @see exec_SELECTquery()
 355      */
 356     public function exec_SELECT_queryArray($queryParts)
 357     {
 358         return $this->exec_SELECTquery($queryParts['SELECT'], $queryParts['FROM'], $queryParts['WHERE'], $queryParts['GROUPBY'], $queryParts['ORDERBY'], $queryParts['LIMIT']);
 359     }
 360
 361     /**
 362      * Creates and executes a SELECT SQL-statement AND traverse result set and returns array with records in.
 363      *
 364      * @param string $select_fields List of fields to select from the table. This is what comes right after "SELECT ...". Required value.
 365      * @param string $from_table Table(s) from which to select. This is what comes right after "FROM ...". Required value.
 366      * @param string $where_clause Additional WHERE clauses put in the end of the query. NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself! DO NOT PUT IN GROUP BY, ORDER BY or LIMIT!
 367      * @param string $groupBy Optional GROUP BY field(s), if none, supply blank string.
 368      * @param string $orderBy Optional ORDER BY field(s), if none, supply blank string.
 369      * @param string $limit Optional LIMIT value ([begin,]max), if none, supply blank string.
 370      * @param string $uidIndexField If set, the result array will carry this field names value as index. Requires that field to be selected of course!
 371      * @return array|NULL Array of rows, or NULL in case of SQL error
 372      * @see exec_SELECTquery()
 373      * @throws \InvalidArgumentException
 374      */
 375     public function exec_SELECTgetRows($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', $uidIndexField = '')
 376     {
 377         $res = $this->exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
 378         if ($this->sql_error()) {
 379             $this->sql_free_result($res);
 380             return null;
 381         }
 382         $output = array();
 383         $firstRecord = true;
 384         while ($record = $this->sql_fetch_assoc($res)) {
 385             if ($uidIndexField) {
 386                 if ($firstRecord) {
 387                     $firstRecord = false;
 388                     if (!array_key_exists($uidIndexField, $record)) {
 389                         $this->sql_free_result($res);
 390                         throw new \InvalidArgumentException('The given $uidIndexField "' . $uidIndexField . '" is not available in the result.', 1432933855);
 391                     }
 392                 }
 393                 $output[$record[$uidIndexField]] = $record;
 394             } else {
 395                 $output[] = $record;
 396             }
 397         }
 398         $this->sql_free_result($res);
 399         return $output;
 400     }
 401
 402     /**
 403      * Creates and executes a SELECT SQL-statement AND gets a result set and returns an array with a single record in.
 404      * LIMIT is automatically set to 1 and can not be overridden.
 405      *
 406      * @param string $select_fields List of fields to select from the table.
 407      * @param string $from_table Table(s) from which to select.
 408      * @param string $where_clause Optional additional WHERE clauses put in the end of the query. NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
 409      * @param string $groupBy Optional GROUP BY field(s), if none, supply blank string.
 410      * @param string $orderBy Optional ORDER BY field(s), if none, supply blank string.
 411      * @param bool $numIndex If set, the result will be fetched with sql_fetch_row, otherwise sql_fetch_assoc will be used.
 412      * @return array|FALSE|NULL Single row, FALSE on empty result, NULL on error
 413      */
 414     public function exec_SELECTgetSingleRow($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $numIndex = false)
 415     {
 416         $res = $this->exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, '1');
 417         $output = null;
 418         if ($res !== false) {
 419             if ($numIndex) {
 420                 $output = $this->sql_fetch_row($res);
 421             } else {
 422                 $output = $this->sql_fetch_assoc($res);
 423             }
 424             $this->sql_free_result($res);
 425         }
 426         return $output;
 427     }
 428
 429     /**
 430      * Counts the number of rows in a table.
 431      *
 432      * @param string $field Name of the field to use in the COUNT() expression (e.g. '*')
 433      * @param string $table Name of the table to count rows for
 434      * @param string $where (optional) WHERE statement of the query
 435      * @return mixed Number of rows counter (int) or FALSE if something went wrong (bool)
 436      */
 437     public function exec_SELECTcountRows($field, $table, $where = '1=1')
 438     {
 439         $count = false;
 440         $resultSet = $this->exec_SELECTquery('COUNT(' . $field . ')', $table, $where);
 441         if ($resultSet !== false) {
 442             list($count) = $this->sql_fetch_row($resultSet);
 443             $count = (int)$count;
 444             $this->sql_free_result($resultSet);
 445         }
 446         return $count;
 447     }
 448
 449     /**
 450      * Truncates a table.
 451      *
 452      * @param string $table Database tablename
 453      * @return mixed Result from handler
 454      */
 455     public function exec_TRUNCATEquery($table)
 456     {
 457         $res = $this->query($this->TRUNCATEquery($table));
 458         if ($this->debugOutput) {
 459             $this->debug('exec_TRUNCATEquery');
 460         }
 461         foreach ($this->postProcessHookObjects as $hookObject) {
 462             /** @var $hookObject PostProcessQueryHookInterface */
 463             $hookObject->exec_TRUNCATEquery_postProcessAction($table, $this);
 464         }
 465         return $res;
 466     }
 467
 468     /**
 469      * Central query method. Also checks if there is a database connection.
 470      * Use this to execute database queries instead of directly calling $this->link->query()
 471      *
 472      * @param string $query The query to send to the database
 473      * @return bool|\mysqli_result
 474      */
 475     protected function query($query)
 476     {
 477         if (!$this->isConnected) {
 478             $this->connectDB();
 479         }
 480         return $this->link->query($query);
 481     }
 482
 483     /**************************************
 484      *
 485      * Query building
 486      *
 487      **************************************/
 488     /**
 489      * Creates an INSERT SQL-statement for $table from the array with field/value pairs $fields_values.
 490      *
 491      * @param string $table See exec_INSERTquery()
 492      * @param array $fields_values See exec_INSERTquery()
 493      * @param bool|array|string $no_quote_fields See fullQuoteArray()
 494      * @return string|NULL Full SQL query for INSERT, NULL if $fields_values is empty
 495      */
 496     public function INSERTquery($table, $fields_values, $no_quote_fields = false)
 497     {
 498         // Table and fieldnames should be "SQL-injection-safe" when supplied to this
 499         // function (contrary to values in the arrays which may be insecure).
 500         if (!is_array($fields_values) || empty($fields_values)) {
 501             return null;
 502         }
 503         foreach ($this->preProcessHookObjects as $hookObject) {
 504             $hookObject->INSERTquery_preProcessAction($table, $fields_values, $no_quote_fields, $this);
 505         }
 506         // Quote and escape values
 507         $fields_values = $this->fullQuoteArray($fields_values, $table, $no_quote_fields, true);
 508         // Build query
 509         $query = 'INSERT INTO ' . $table . ' (' . implode(',', array_keys($fields_values)) . ') VALUES ' . '(' . implode(',', $fields_values) . ')';
 510         // Return query
 511         if ($this->debugOutput || $this->store_lastBuiltQuery) {
 512             $this->debug_lastBuiltQuery = $query;
 513         }
 514         return $query;
 515     }
 516
 517     /**
 518      * Creates an INSERT SQL-statement for $table with multiple rows.
 519      *
 520      * @param string $table Table name
 521      * @param array $fields Field names
 522      * @param array $rows Table rows. Each row should be an array with field values mapping to $fields
 523      * @param bool|array|string $no_quote_fields See fullQuoteArray()
 524      * @return string|NULL Full SQL query for INSERT, NULL if $rows is empty
 525      */
 526     public function INSERTmultipleRows($table, array $fields, array $rows, $no_quote_fields = false)
 527     {
 528         // Table and fieldnames should be "SQL-injection-safe" when supplied to this
 529         // function (contrary to values in the arrays which may be insecure).
 530         if (empty($rows)) {
 531             return null;
 532         }
 533         foreach ($this->preProcessHookObjects as $hookObject) {
 534             /** @var $hookObject PreProcessQueryHookInterface */
 535             $hookObject->INSERTmultipleRows_preProcessAction($table, $fields, $rows, $no_quote_fields, $this);
 536         }
 537         // Build query
 538         $query = 'INSERT INTO ' . $table . ' (' . implode(', ', $fields) . ') VALUES ';
 539         $rowSQL = array();
 540         foreach ($rows as $row) {
 541             // Quote and escape values
 542             $row = $this->fullQuoteArray($row, $table, $no_quote_fields);
 543             $rowSQL[] = '(' . implode(', ', $row) . ')';
 544         }
 545         $query .= implode(', ', $rowSQL);
 546         // Return query
 547         if ($this->debugOutput || $this->store_lastBuiltQuery) {
 548             $this->debug_lastBuiltQuery = $query;
 549         }
 550         return $query;
 551     }
 552
 553     /**
 554      * Creates an UPDATE SQL-statement for $table where $where-clause (typ. 'uid=...') from the array with field/value pairs $fields_values.
 555      *
 556      *
 557      * @param string $table See exec_UPDATEquery()
 558      * @param string $where See exec_UPDATEquery()
 559      * @param array $fields_values See exec_UPDATEquery()
 560      * @param bool|array|string $no_quote_fields See fullQuoteArray()
 561      * @throws \InvalidArgumentException
 562      * @return string Full SQL query for UPDATE
 563      */
 564     public function UPDATEquery($table, $where, $fields_values, $no_quote_fields = false)
 565     {
 566         // Table and fieldnames should be "SQL-injection-safe" when supplied to this
 567         // function (contrary to values in the arrays which may be insecure).
 568         if (is_string($where)) {
 569             foreach ($this->preProcessHookObjects as $hookObject) {
 570                 /** @var $hookObject PreProcessQueryHookInterface */
 571                 $hookObject->UPDATEquery_preProcessAction($table, $where, $fields_values, $no_quote_fields, $this);
 572             }
 573             $fields = array();
 574             if (is_array($fields_values) && !empty($fields_values)) {
 575                 // Quote and escape values
 576                 $nArr = $this->fullQuoteArray($fields_values, $table, $no_quote_fields, true);
 577                 foreach ($nArr as $k => $v) {
 578                     $fields[] = $k . '=' . $v;
 579                 }
 580             }
 581             // Build query
 582             $query = 'UPDATE ' . $table . ' SET ' . implode(',', $fields) . ((string)$where !== '' ? ' WHERE ' . $where : '');
 583             if ($this->debugOutput || $this->store_lastBuiltQuery) {
 584                 $this->debug_lastBuiltQuery = $query;
 585             }
 586             return $query;
 587         } else {
 588             throw new \InvalidArgumentException('TYPO3 Fatal Error: "Where" clause argument for UPDATE query was not a string in $this->UPDATEquery() !', 1270853880);
 589         }
 590     }
 591
 592     /**
 593      * Creates a DELETE SQL-statement for $table where $where-clause
 594      *
 595      * @param string $table See exec_DELETEquery()
 596      * @param string $where See exec_DELETEquery()
 597      * @return string Full SQL query for DELETE
 598      * @throws \InvalidArgumentException
 599      */
 600     public function DELETEquery($table, $where)
 601     {
 602         if (is_string($where)) {
 603             foreach ($this->preProcessHookObjects as $hookObject) {
 604                 /** @var $hookObject PreProcessQueryHookInterface */
 605                 $hookObject->DELETEquery_preProcessAction($table, $where, $this);
 606             }
 607             // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
 608             $query = 'DELETE FROM ' . $table . ((string)$where !== '' ? ' WHERE ' . $where : '');
 609             if ($this->debugOutput || $this->store_lastBuiltQuery) {
 610                 $this->debug_lastBuiltQuery = $query;
 611             }
 612             return $query;
 613         } else {
 614             throw new \InvalidArgumentException('TYPO3 Fatal Error: "Where" clause argument for DELETE query was not a string in $this->DELETEquery() !', 1270853881);
 615         }
 616     }
 617
 618     /**
 619      * Creates a SELECT SQL-statement
 620      *
 621      * @param string $select_fields See exec_SELECTquery()
 622      * @param string $from_table See exec_SELECTquery()
 623      * @param string $where_clause See exec_SELECTquery()
 624      * @param string $groupBy See exec_SELECTquery()
 625      * @param string $orderBy See exec_SELECTquery()
 626      * @param string $limit See exec_SELECTquery()
 627      * @return string Full SQL query for SELECT
 628      */
 629     public function SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '')
 630     {
 631         foreach ($this->preProcessHookObjects as $hookObject) {
 632             /** @var $hookObject PreProcessQueryHookInterface */
 633             $hookObject->SELECTquery_preProcessAction($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit, $this);
 634         }
 635         // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
 636         // Build basic query
 637         $query = 'SELECT ' . $select_fields . ' FROM ' . $from_table . ((string)$where_clause !== '' ? ' WHERE ' . $where_clause : '');
 638         // Group by
 639         $query .= (string)$groupBy !== '' ? ' GROUP BY ' . $groupBy : '';
 640         // Order by
 641         $query .= (string)$orderBy !== '' ? ' ORDER BY ' . $orderBy : '';
 642         // Group by
 643         $query .= (string)$limit !== '' ? ' LIMIT ' . $limit : '';
 644         // Return query
 645         if ($this->debugOutput || $this->store_lastBuiltQuery) {
 646             $this->debug_lastBuiltQuery = $query;
 647         }
 648         return $query;
 649     }
 650
 651     /**
 652      * Creates a SELECT SQL-statement to be used as subquery within another query.
 653      * BEWARE: This method should not be overriden within DBAL to prevent quoting from happening.
 654      *
 655      * @param string $select_fields List of fields to select from the table.
 656      * @param string $from_table Table from which to select.
 657      * @param string $where_clause Conditional WHERE statement
 658      * @return string Full SQL query for SELECT
 659      */
 660     public function SELECTsubquery($select_fields, $from_table, $where_clause)
 661     {
 662         // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
 663         // Build basic query:
 664         $query = 'SELECT ' . $select_fields . ' FROM ' . $from_table . ((string)$where_clause !== '' ? ' WHERE ' . $where_clause : '');
 665         // Return query
 666         if ($this->debugOutput || $this->store_lastBuiltQuery) {
 667             $this->debug_lastBuiltQuery = $query;
 668         }
 669         return $query;
 670     }
 671
 672     /**
 673      * Creates a SELECT query, selecting fields ($select) from two/three tables joined
 674      * Use $mm_table together with $local_table or $foreign_table to select over two tables. Or use all three tables to select the full MM-relation.
 675      * The JOIN is done with [$local_table].uid <--> [$mm_table].uid_local  / [$mm_table].uid_foreign <--> [$foreign_table].uid
 676      * The function is very useful for selecting MM-relations between tables adhering to the MM-format used by TCE (TYPO3 Core Engine). See the section on $GLOBALS['TCA'] in Inside TYPO3 for more details.
 677      *
 678      * @param string $select See exec_SELECT_mm_query()
 679      * @param string $local_table See exec_SELECT_mm_query()
 680      * @param string $mm_table See exec_SELECT_mm_query()
 681      * @param string $foreign_table See exec_SELECT_mm_query()
 682      * @param string $whereClause See exec_SELECT_mm_query()
 683      * @param string $groupBy See exec_SELECT_mm_query()
 684      * @param string $orderBy See exec_SELECT_mm_query()
 685      * @param string $limit See exec_SELECT_mm_query()
 686      * @return string Full SQL query for SELECT
 687      * @see SELECTquery()
 688      */
 689     public function SELECT_mm_query($select, $local_table, $mm_table, $foreign_table, $whereClause = '', $groupBy = '', $orderBy = '', $limit = '')
 690     {
 691         $queryParts = $this->getSelectMmQueryParts($select, $local_table, $mm_table, $foreign_table, $whereClause, $groupBy, $orderBy, $limit);
 692         return $this->SELECTquery($queryParts['SELECT'], $queryParts['FROM'], $queryParts['WHERE'], $queryParts['GROUPBY'], $queryParts['ORDERBY'], $queryParts['LIMIT']);
 693     }
 694
 695     /**
 696      * Creates a TRUNCATE TABLE SQL-statement
 697      *
 698      * @param string $table See exec_TRUNCATEquery()
 699      * @return string Full SQL query for TRUNCATE TABLE
 700      */
 701     public function TRUNCATEquery($table)
 702     {
 703         foreach ($this->preProcessHookObjects as $hookObject) {
 704             /** @var $hookObject PreProcessQueryHookInterface */
 705             $hookObject->TRUNCATEquery_preProcessAction($table, $this);
 706         }
 707         // Table should be "SQL-injection-safe" when supplied to this function
 708         // Build basic query:
 709         $query = 'TRUNCATE TABLE ' . $table;
 710         // Return query:
 711         if ($this->debugOutput || $this->store_lastBuiltQuery) {
 712             $this->debug_lastBuiltQuery = $query;
 713         }
 714         return $query;
 715     }
 716
 717     /**
 718      * Returns a WHERE clause that can find a value ($value) in a list field ($field)
 719      * For instance a record in the database might contain a list of numbers,
 720      * "34,234,5" (with no spaces between). This query would be able to select that
 721      * record based on the value "34", "234" or "5" regardless of their position in
 722      * the list (left, middle or right).
 723      * The value must not contain a comma (,)
 724      * Is nice to look up list-relations to records or files in TYPO3 database tables.
 725      *
 726      * @param string $field Field name
 727      * @param string $value Value to find in list
 728      * @param string $table Table in which we are searching (for DBAL detection of quoteStr() method)
 729      * @return string WHERE clause for a query
 730      * @throws \InvalidArgumentException
 731      */
 732     public function listQuery($field, $value, $table)
 733     {
 734         $value = (string)$value;
 735         if (strpos($value, ',') !== false) {
 736             throw new \InvalidArgumentException('$value must not contain a comma (,) in $this->listQuery() !', 1294585862);
 737         }
 738         $pattern = $this->quoteStr($value, $table);
 739         $where = 'FIND_IN_SET(\'' . $pattern . '\',' . $field . ')';
 740         return $where;
 741     }
 742
 743     /**
 744      * Returns a WHERE clause which will make an AND or OR search for the words in the $searchWords array in any of the fields in array $fields.
 745      *
 746      * @param array $searchWords Array of search words
 747      * @param array $fields Array of fields
 748      * @param string $table Table in which we are searching (for DBAL detection of quoteStr() method)
 749      * @param string $constraint How multiple search words have to match ('AND' or 'OR')
 750      * @return string WHERE clause for search
 751      */
 752     public function searchQuery($searchWords, $fields, $table, $constraint = self::AND_Constraint)
 753     {
 754         switch ($constraint) {
 755             case self::OR_Constraint:
 756                 $constraint = 'OR';
 757                 break;
 758             default:
 759                 $constraint = 'AND';
 760         }
 761
 762         $queryParts = array();
 763         foreach ($searchWords as $sw) {
 764             $like = ' LIKE \'%' . $this->quoteStr($this->escapeStrForLike($sw, $table), $table) . '%\'';
 765             $queryParts[] = $table . '.' . implode(($like . ' OR ' . $table . '.'), $fields) . $like;
 766         }
 767         $query = '(' . implode(') ' . $constraint . ' (', $queryParts) . ')';
 768
 769         return $query;
 770     }
 771
 772     /**************************************
 773      *
 774      * Prepared Query Support
 775      *
 776      **************************************/
 777     /**
 778      * Creates a SELECT prepared SQL statement.
 779      *
 780      * @param string $select_fields See exec_SELECTquery()
 781      * @param string $from_table See exec_SELECTquery()
 782      * @param string $where_clause See exec_SELECTquery()
 783      * @param string $groupBy See exec_SELECTquery()
 784      * @param string $orderBy See exec_SELECTquery()
 785      * @param string $limit See exec_SELECTquery()
 786      * @param array $input_parameters An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_AUTOTYPE.
 787      * @return \TYPO3\CMS\Core\Database\PreparedStatement Prepared statement
 788      */
 789     public function prepare_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', array $input_parameters = array())
 790     {
 791         $query = $this->SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
 792         /** @var $preparedStatement \TYPO3\CMS\Core\Database\PreparedStatement */
 793         $preparedStatement = GeneralUtility::makeInstance(\TYPO3\CMS\Core\Database\PreparedStatement::class, $query, $from_table, array());
 794         // Bind values to parameters
 795         foreach ($input_parameters as $key => $value) {
 796             $preparedStatement->bindValue($key, $value, PreparedStatement::PARAM_AUTOTYPE);
 797         }
 798         // Return prepared statement
 799         return $preparedStatement;
 800     }
 801
 802     /**
 803      * Creates a SELECT prepared SQL statement based on input query parts array
 804      *
 805      * @param array $queryParts Query parts array
 806      * @param array $input_parameters An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_AUTOTYPE.
 807      * @return \TYPO3\CMS\Core\Database\PreparedStatement Prepared statement
 808      */
 809     public function prepare_SELECTqueryArray(array $queryParts, array $input_parameters = array())
 810     {
 811         return $this->prepare_SELECTquery($queryParts['SELECT'], $queryParts['FROM'], $queryParts['WHERE'], $queryParts['GROUPBY'], $queryParts['ORDERBY'], $queryParts['LIMIT'], $input_parameters);
 812     }
 813
 814     /**
 815      * Prepares a prepared query.
 816      *
 817      * @param string $query The query to execute
 818      * @param array $queryComponents The components of the query to execute
 819      * @return \mysqli_stmt|object MySQLi statement / DBAL object
 820      * @internal This method may only be called by \TYPO3\CMS\Core\Database\PreparedStatement
 821      */
 822     public function prepare_PREPAREDquery($query, array $queryComponents)
 823     {
 824         if (!$this->isConnected) {
 825             $this->connectDB();
 826         }
 827         $stmt = $this->link->stmt_init();
 828         $success = $stmt->prepare($query);
 829         if ($this->debugOutput) {
 830             $this->debug('stmt_execute', $query);
 831         }
 832         return $success ? $stmt : null;
 833     }
 834
 835     /**************************************
 836      *
 837      * Various helper functions
 838      *
 839      * Functions recommended to be used for
 840      * - escaping values,
 841      * - cleaning lists of values,
 842      * - stripping of excess ORDER BY/GROUP BY keywords
 843      *
 844      **************************************/
 845     /**
 846      * Escaping and quoting values for SQL statements.
 847      *
 848      * @param string $str Input string
 849      * @param string $table Table name for which to quote string. Just enter the table that the field-value is selected from (and any DBAL will look up which handler to use and then how to quote the string!).
 850      * @param bool $allowNull Whether to allow NULL values
 851      * @return string Output string; Wrapped in single quotes and quotes in the string (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
 852      * @see quoteStr()
 853      */
 854     public function fullQuoteStr($str, $table, $allowNull = false)
 855     {
 856         if (!$this->isConnected) {
 857             $this->connectDB();
 858         }
 859         if ($allowNull && $str === null) {
 860             return 'NULL';
 861         }
 862         if (is_bool($str)) {
 863             $str = (int)$str;
 864         }
 865
 866         return '\'' . $this->link->real_escape_string($str) . '\'';
 867     }
 868
 869     /**
 870      * Will fullquote all values in the one-dimensional array so they are ready to "implode" for an sql query.
 871      *
 872      * @param array $arr Array with values (either associative or non-associative array)
 873      * @param string $table Table name for which to quote
 874      * @param bool|array|string $noQuote List/array of keys NOT to quote (eg. SQL functions) - ONLY for associative arrays
 875      * @param bool $allowNull Whether to allow NULL values
 876      * @return array The input array with the values quoted
 877      * @see cleanIntArray()
 878      */
 879     public function fullQuoteArray($arr, $table, $noQuote = false, $allowNull = false)
 880     {
 881         if (is_string($noQuote)) {
 882             $noQuote = explode(',', $noQuote);
 883         } elseif (!is_array($noQuote)) {
 884             $noQuote = (bool)$noQuote;
 885         }
 886         if ($noQuote === true) {
 887             return $arr;
 888         }
 889         foreach ($arr as $k => $v) {
 890             if ($noQuote === false || !in_array($k, $noQuote)) {
 891                 $arr[$k] = $this->fullQuoteStr($v, $table, $allowNull);
 892             }
 893         }
 894         return $arr;
 895     }
 896
 897     /**
 898      * Substitution for PHP function "addslashes()"
 899      * Use this function instead of the PHP addslashes() function when you build queries - this will prepare your code for DBAL.
 900      * NOTICE: You must wrap the output of this function in SINGLE QUOTES to be DBAL compatible. Unless you have to apply the single quotes yourself you should rather use ->fullQuoteStr()!
 901      *
 902      * @param string $str Input string
 903      * @param string $table Table name for which to quote string. Just enter the table that the field-value is selected from (and any DBAL will look up which handler to use and then how to quote the string!).
 904      * @return string Output string; Quotes (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
 905      * @see quoteStr()
 906      */
 907     public function quoteStr($str, $table)
 908     {
 909         if (!$this->isConnected) {
 910             $this->connectDB();
 911         }
 912         return $this->link->real_escape_string($str);
 913     }
 914
 915     /**
 916      * Escaping values for SQL LIKE statements.
 917      *
 918      * @param string $str Input string
 919      * @param string $table Table name for which to escape string. Just enter the table that the field-value is selected from (and any DBAL will look up which handler to use and then how to quote the string!).
 920      * @return string Output string; % and _ will be escaped with \ (or otherwise based on DBAL handler)
 921      * @see quoteStr()
 922      */
 923     public function escapeStrForLike($str, $table)
 924     {
 925         return addcslashes($str, '_%');
 926     }
 927
 928     /**
 929      * Will convert all values in the one-dimensional array to integers.
 930      * Useful when you want to make sure an array contains only integers before imploding them in a select-list.
 931      *
 932      * @param array $arr Array with values
 933      * @return array The input array with all values cast to (int)
 934      * @see cleanIntList()
 935      */
 936     public function cleanIntArray($arr)
 937     {
 938         return array_map('intval', $arr);
 939     }
 940
 941     /**
 942      * Will force all entries in the input comma list to integers
 943      * Useful when you want to make sure a commalist of supposed integers really contain only integers; You want to know that when you don't trust content that could go into an SQL statement.
 944      *
 945      * @param string $list List of comma-separated values which should be integers
 946      * @return string The input list but with every value cast to (int)
 947      * @see cleanIntArray()
 948      */
 949     public function cleanIntList($list)
 950     {
 951         return implode(',', GeneralUtility::intExplode(',', $list));
 952     }
 953
 954     /**
 955      * Removes the prefix "ORDER BY" from the input string.
 956      * This function is used when you call the exec_SELECTquery() function and want to pass the ORDER BY parameter by can't guarantee that "ORDER BY" is not prefixed.
 957      * Generally; This function provides a work-around to the situation where you cannot pass only the fields by which to order the result.
 958      *
 959      * @param string $str eg. "ORDER BY title, uid
 960      * @return string eg. "title, uid
 961      * @see exec_SELECTquery(), stripGroupBy()
 962      */
 963     public function stripOrderBy($str)
 964     {
 965         return preg_replace('/^(?:ORDER[[:space:]]*BY[[:space:]]*)+/i', '', trim($str));
 966     }
 967
 968     /**
 969      * Removes the prefix "GROUP BY" from the input string.
 970      * This function is used when you call the SELECTquery() function and want to pass the GROUP BY parameter by can't guarantee that "GROUP BY" is not prefixed.
 971      * Generally; This function provides a work-around to the situation where you cannot pass only the fields by which to order the result.
 972      *
 973      * @param string $str eg. "GROUP BY title, uid
 974      * @return string eg. "title, uid
 975      * @see exec_SELECTquery(), stripOrderBy()
 976      */
 977     public function stripGroupBy($str)
 978     {
 979         return preg_replace('/^(?:GROUP[[:space:]]*BY[[:space:]]*)+/i', '', trim($str));
 980     }
 981
 982     /**
 983      * Returns the date and time formats compatible with the given database table.
 984      *
 985      * @param string $table Table name for which to return an empty date. Just enter the table that the field-value is selected from (and any DBAL will look up which handler to use and then how date and time should be formatted).
 986      * @return array
 987      */
 988     public function getDateTimeFormats($table)
 989     {
 990         return self::$dateTimeFormats;
 991     }
 992
 993     /**
 994      * Creates SELECT query components for selecting fields ($select) from two/three tables joined
 995      * Use $mm_table together with $local_table or $foreign_table to select over two tables. Or use all three tables to select the full MM-relation.
 996      * The JOIN is done with [$local_table].uid <--> [$mm_table].uid_local  / [$mm_table].uid_foreign <--> [$foreign_table].uid
 997      * The function is very useful for selecting MM-relations between tables adhering to the MM-format used by TCE (TYPO3 Core Engine). See the section on $GLOBALS['TCA'] in Inside TYPO3 for more details.
 998      *
 999      * @param string $select See exec_SELECT_mm_query()
1000      * @param string $local_table See exec_SELECT_mm_query()
1001      * @param string $mm_table See exec_SELECT_mm_query()
1002      * @param string $foreign_table See exec_SELECT_mm_query()
1003      * @param string $whereClause See exec_SELECT_mm_query()
1004      * @param string $groupBy See exec_SELECT_mm_query()
1005      * @param string $orderBy See exec_SELECT_mm_query()
1006      * @param string $limit See exec_SELECT_mm_query()
1007      * @return array SQL query components
1008      */
1009     protected function getSelectMmQueryParts($select, $local_table, $mm_table, $foreign_table, $whereClause = '', $groupBy = '', $orderBy = '', $limit = '')
1010     {
1011         $foreign_table_as = $foreign_table == $local_table ? $foreign_table . StringUtility::getUniqueId('_join') : '';
1012         $mmWhere = $local_table ? $local_table . '.uid=' . $mm_table . '.uid_local' : '';
1013         $mmWhere .= ($local_table and $foreign_table) ? ' AND ' : '';
1014         $tables = ($local_table ? $local_table . ',' : '') . $mm_table;
1015         if ($foreign_table) {
1016             $mmWhere .= ($foreign_table_as ?: $foreign_table) . '.uid=' . $mm_table . '.uid_foreign';
1017             $tables .= ',' . $foreign_table . ($foreign_table_as ? ' AS ' . $foreign_table_as : '');
1018         }
1019         return array(
1020             'SELECT' => $select,
1021             'FROM' => $tables,
1022             'WHERE' => $mmWhere . ' ' . $whereClause,
1023             'GROUPBY' => $groupBy,
1024             'ORDERBY' => $orderBy,
1025             'LIMIT' => $limit
1026         );
1027     }
1028
1029     /**************************************
1030      *
1031      * MySQL(i) wrapper functions
1032      * (For use in your applications)
1033      *
1034      **************************************/
1035     /**
1036      * Executes query
1037      * MySQLi query() wrapper function
1038      * Beware: Use of this method should be avoided as it is experimentally supported by DBAL. You should consider
1039      * using exec_SELECTquery() and similar methods instead.
1040      *
1041      * @param string $query Query to execute
1042      * @return bool|\mysqli_result|object MySQLi result object / DBAL object
1043      */
1044     public function sql_query($query)
1045     {
1046         $res = $this->query($query);
1047         if ($this->debugOutput) {
1048             $this->debug('sql_query', $query);
1049         }
1050         return $res;
1051     }
1052
1053     /**
1054      * Returns the error status on the last query() execution
1055      *
1056      * @return string MySQLi error string.
1057      */
1058     public function sql_error()
1059     {
1060         return $this->link->error;
1061     }
1062
1063     /**
1064      * Returns the error number on the last query() execution
1065      *
1066      * @return int MySQLi error number
1067      */
1068     public function sql_errno()
1069     {
1070         return $this->link->errno;
1071     }
1072
1073     /**
1074      * Returns the number of selected rows.
1075      *
1076      * @param bool|\mysqli_result|object $res MySQLi result object / DBAL object
1077      * @return int Number of resulting rows
1078      */
1079     public function sql_num_rows($res)
1080     {
1081         if ($this->debug_check_recordset($res)) {
1082             return $res->num_rows;
1083         } else {
1084             return false;
1085         }
1086     }
1087
1088     /**
1089      * Returns an associative array that corresponds to the fetched row, or FALSE if there are no more rows.
1090      * MySQLi fetch_assoc() wrapper function
1091      *
1092      * @param bool|\mysqli_result|object $res MySQLi result object / DBAL object
1093      * @return array|bool Associative array of result row.
1094      */
1095     public function sql_fetch_assoc($res)
1096     {
1097         if ($this->debug_check_recordset($res)) {
1098             $result = $res->fetch_assoc();
1099             if ($result === null) {
1100                 // Needed for compatibility
1101                 $result = false;
1102             }
1103             return $result;
1104         } else {
1105             return false;
1106         }
1107     }
1108
1109     /**
1110      * Returns an array that corresponds to the fetched row, or FALSE if there are no more rows.
1111      * The array contains the values in numerical indices.
1112      * MySQLi fetch_row() wrapper function
1113      *
1114      * @param bool|\mysqli_result|object $res MySQLi result object / DBAL object
1115      * @return array|bool Array with result rows.
1116      */
1117     public function sql_fetch_row($res)
1118     {
1119         if ($this->debug_check_recordset($res)) {
1120             $result = $res->fetch_row();
1121             if ($result === null) {
1122                 // Needed for compatibility
1123                 $result = false;
1124             }
1125             return $result;
1126         } else {
1127             return false;
1128         }
1129     }
1130
1131     /**
1132      * Free result memory
1133      * free_result() wrapper function
1134      *
1135      * @param bool|\mysqli_result|object $res MySQLi result object / DBAL object
1136      * @return bool Returns TRUE on success or FALSE on failure.
1137      */
1138     public function sql_free_result($res)
1139     {
1140         if ($this->debug_check_recordset($res) && is_object($res)) {
1141             $res->free();
1142             return true;
1143         } else {
1144             return false;
1145         }
1146     }
1147
1148     /**
1149      * Get the ID generated from the previous INSERT operation
1150      *
1151      * @return int The uid of the last inserted record.
1152      */
1153     public function sql_insert_id()
1154     {
1155         return $this->link->insert_id;
1156     }
1157
1158     /**
1159      * Returns the number of rows affected by the last INSERT, UPDATE or DELETE query
1160      *
1161      * @return int Number of rows affected by last query
1162      */
1163     public function sql_affected_rows()
1164     {
1165         return $this->link->affected_rows;
1166     }
1167
1168     /**
1169      * Move internal result pointer
1170      *
1171      * @param bool|\mysqli_result|object $res MySQLi result object / DBAL object
1172      * @param int $seek Seek result number.
1173      * @return bool Returns TRUE on success or FALSE on failure.
1174      */
1175     public function sql_data_seek($res, $seek)
1176     {
1177         if ($this->debug_check_recordset($res)) {
1178             return $res->data_seek($seek);
1179         } else {
1180             return false;
1181         }
1182     }
1183
1184     /**
1185      * Get the type of the specified field in a result
1186      * mysql_field_type() wrapper function
1187      *
1188      * @param bool|\mysqli_result|object $res MySQLi result object / DBAL object
1189      * @param int $pointer Field index.
1190      * @return string Returns the name of the specified field index, or FALSE on error
1191      */
1192     public function sql_field_type($res, $pointer)
1193     {
1194         // mysql_field_type compatibility map
1195         // taken from: http://www.php.net/manual/en/mysqli-result.fetch-field-direct.php#89117
1196         // Constant numbers see http://php.net/manual/en/mysqli.constants.php
1197         $mysql_data_type_hash = array(
1198             1=>'tinyint',
1199             2=>'smallint',
1200             3=>'int',
1201             4=>'float',
1202             5=>'double',
1203             7=>'timestamp',
1204             8=>'bigint',
1205             9=>'mediumint',
1206             10=>'date',
1207             11=>'time',
1208             12=>'datetime',
1209             13=>'year',
1210             16=>'bit',
1211             //252 is currently mapped to all text and blob types (MySQL 5.0.51a)
1212             253=>'varchar',
1213             254=>'char',
1214             246=>'decimal'
1215         );
1216         if ($this->debug_check_recordset($res)) {
1217             $metaInfo = $res->fetch_field_direct($pointer);
1218             if ($metaInfo === false) {
1219                 return false;
1220             }
1221             return $mysql_data_type_hash[$metaInfo->type];
1222         } else {
1223             return false;
1224         }
1225     }
1226
1227     /**
1228      * Open a (persistent) connection to a MySQL server
1229      *
1230      * @return bool|void
1231      * @throws \RuntimeException
1232      */
1233     public function sql_pconnect()
1234     {
1235         if ($this->isConnected) {
1236             return $this->link;
1237         }
1238
1239         if (!extension_loaded('mysqli')) {
1240             throw new \RuntimeException(
1241                 'Database Error: PHP mysqli extension not loaded. This is a must have for TYPO3 CMS!',
1242                 1271492607
1243             );
1244         }
1245
1246         $host = $this->persistentDatabaseConnection
1247             ? 'p:' . $this->databaseHost
1248             : $this->databaseHost;
1249
1250         $this->link = mysqli_init();
1251         $connected = $this->link->real_connect(
1252             $host,
1253             $this->databaseUsername,
1254             $this->databaseUserPassword,
1255             null,
1256             (int)$this->databasePort,
1257             $this->databaseSocket,
1258             $this->connectionCompression ? MYSQLI_CLIENT_COMPRESS : 0
1259         );
1260
1261         if ($connected) {
1262             $this->isConnected = true;
1263
1264             if ($this->link->set_charset($this->connectionCharset) === false) {
1265                 GeneralUtility::sysLog(
1266                     'Error setting connection charset to "' . $this->connectionCharset . '"',
1267                     'core',
1268                     GeneralUtility::SYSLOG_SEVERITY_ERROR
1269                 );
1270             }
1271
1272             foreach ($this->initializeCommandsAfterConnect as $command) {
1273                 if ($this->query($command) === false) {
1274                     GeneralUtility::sysLog(
1275                         'Could not initialize DB connection with query "' . $command . '": ' . $this->sql_error(),
1276                         'core',
1277                         GeneralUtility::SYSLOG_SEVERITY_ERROR
1278                     );
1279                 }
1280             }
1281             $this->checkConnectionCharset();
1282         } else {
1283             // @todo This should raise an exception. Would be useful especially to work during installation.
1284             $error_msg = $this->link->connect_error;
1285             $this->link = null;
1286             GeneralUtility::sysLog(
1287                 'Could not connect to MySQL server ' . $host . ' with user ' . $this->databaseUsername . ': ' . $error_msg,
1288                 'core',
1289                 GeneralUtility::SYSLOG_SEVERITY_FATAL
1290             );
1291         }
1292         return $this->link;
1293     }
1294
1295     /**
1296      * Select a SQL database
1297      *
1298      * @return bool Returns TRUE on success or FALSE on failure.
1299      */
1300     public function sql_select_db()
1301     {
1302         if (!$this->isConnected) {
1303             $this->connectDB();
1304         }
1305
1306         $ret = $this->link->select_db($this->databaseName);
1307         if (!$ret) {
1308             GeneralUtility::sysLog(
1309                 'Could not select MySQL database ' . $this->databaseName . ': ' . $this->sql_error(),
1310                 'core',
1311                 GeneralUtility::SYSLOG_SEVERITY_FATAL
1312             );
1313         }
1314         return $ret;
1315     }
1316
1317     /**************************************
1318      *
1319      * SQL admin functions
1320      * (For use in the Install Tool and Extension Manager)
1321      *
1322      **************************************/
1323     /**
1324      * Listing databases from current MySQL connection. NOTICE: It WILL try to select those databases and thus break selection of current database.
1325      * This is only used as a service function in the (1-2-3 process) of the Install Tool.
1326      * In any case a lookup should be done in the _DEFAULT handler DBMS then.
1327      * Use in Install Tool only!
1328      *
1329      * @return array Each entry represents a database name
1330      * @throws \RuntimeException
1331      */
1332     public function admin_get_dbs()
1333     {
1334         $dbArr = array();
1335         $db_list = $this->query('SELECT SCHEMA_NAME FROM information_schema.SCHEMATA');
1336         if ($db_list === false) {
1337             throw new \RuntimeException(
1338                 'MySQL Error: Cannot get tablenames: "' . $this->sql_error() . '"!',
1339                 1378457171
1340             );
1341         } else {
1342             while ($row = $db_list->fetch_object()) {
1343                 try {
1344                     $this->setDatabaseName($row->SCHEMA_NAME);
1345                     if ($this->sql_select_db()) {
1346                         $dbArr[] = $row->SCHEMA_NAME;
1347                     }
1348                 } catch (\RuntimeException $exception) {
1349                     // The exception happens if we cannot connect to the database
1350                     // (usually due to missing permissions). This is ok here.
1351                     // We catch the exception, skip the database and continue.
1352                 }
1353             }
1354         }
1355         return $dbArr;
1356     }
1357
1358     /**
1359      * Returns the list of tables from the default database, TYPO3_db (quering the DBMS)
1360      * In a DBAL this method should 1) look up all tables from the DBMS  of
1361      * the _DEFAULT handler and then 2) add all tables *configured* to be managed by other handlers
1362      *
1363      * @return array Array with tablenames as key and arrays with status information as value
1364      */
1365     public function admin_get_tables()
1366     {
1367         $whichTables = array();
1368         $tables_result = $this->query('SHOW TABLE STATUS FROM `' . $this->databaseName . '`');
1369         if ($tables_result !== false) {
1370             while ($theTable = $tables_result->fetch_assoc()) {
1371                 $whichTables[$theTable['Name']] = $theTable;
1372             }
1373             $tables_result->free();
1374         }
1375         return $whichTables;
1376     }
1377
1378     /**
1379      * Returns information about each field in the $table (quering the DBMS)
1380      * In a DBAL this should look up the right handler for the table and return compatible information
1381      * This function is important not only for the Install Tool but probably for
1382      * DBALs as well since they might need to look up table specific information
1383      * in order to construct correct queries. In such cases this information should
1384      * probably be cached for quick delivery.
1385      *
1386      * @param string $tableName Table name
1387      * @return array Field information in an associative array with fieldname => field row
1388      */
1389     public function admin_get_fields($tableName)
1390     {
1391         $output = array();
1392         $columns_res = $this->query('SHOW FULL COLUMNS FROM `' . $tableName . '`');
1393         if ($columns_res !== false) {
1394             while ($fieldRow = $columns_res->fetch_assoc()) {
1395                 $output[$fieldRow['Field']] = $fieldRow;
1396             }
1397             $columns_res->free();
1398         }
1399         return $output;
1400     }
1401
1402     /**
1403      * Returns information about each index key in the $table (quering the DBMS)
1404      * In a DBAL this should look up the right handler for the table and return compatible information
1405      *
1406      * @param string $tableName Table name
1407      * @return array Key information in a numeric array
1408      */
1409     public function admin_get_keys($tableName)
1410     {
1411         $output = array();
1412         $keyRes = $this->query('SHOW KEYS FROM `' . $tableName . '`');
1413         if ($keyRes !== false) {
1414             while ($keyRow = $keyRes->fetch_assoc()) {
1415                 $output[] = $keyRow;
1416             }
1417             $keyRes->free();
1418         }
1419         return $output;
1420     }
1421
1422     /**
1423      * Returns information about the character sets supported by the current DBM
1424      * This function is important not only for the Install Tool but probably for
1425      * DBALs as well since they might need to look up table specific information
1426      * in order to construct correct queries. In such cases this information should
1427      * probably be cached for quick delivery.
1428      *
1429      * This is used by the Install Tool to convert tables with non-UTF8 charsets
1430      * Use in Install Tool only!
1431      *
1432      * @return array Array with Charset as key and an array of "Charset", "Description", "Default collation", "Maxlen" as values
1433      */
1434     public function admin_get_charsets()
1435     {
1436         $output = array();
1437         $columns_res = $this->query('SHOW CHARACTER SET');
1438         if ($columns_res !== false) {
1439             while ($row = $columns_res->fetch_assoc()) {
1440                 $output[$row['Charset']] = $row;
1441             }
1442             $columns_res->free();
1443         }
1444         return $output;
1445     }
1446
1447     /**
1448      * mysqli() wrapper function, used by the Install Tool and EM for all queries regarding management of the database!
1449      *
1450      * @param string $query Query to execute
1451      * @return bool|\mysqli_result|object MySQLi result object / DBAL object
1452      */
1453     public function admin_query($query)
1454     {
1455         $res = $this->query($query);
1456         if ($this->debugOutput) {
1457             $this->debug('admin_query', $query);
1458         }
1459         return $res;
1460     }
1461
1462     /******************************
1463      *
1464      * Connect handling
1465      *
1466      ******************************/
1467
1468     /**
1469      * Set database host
1470      *
1471      * @param string $host
1472      */
1473     public function setDatabaseHost($host = 'localhost')
1474     {
1475         $this->disconnectIfConnected();
1476         $this->databaseHost = $host;
1477     }
1478
1479     /**
1480      * Set database port
1481      *
1482      * @param int $port
1483      */
1484     public function setDatabasePort($port = 3306)
1485     {
1486         $this->disconnectIfConnected();
1487         $this->databasePort = (int)$port;
1488     }
1489
1490     /**
1491      * Set database socket
1492      *
1493      * @param string|NULL $socket
1494      */
1495     public function setDatabaseSocket($socket = null)
1496     {
1497         $this->disconnectIfConnected();
1498         $this->databaseSocket = $socket;
1499     }
1500
1501     /**
1502      * Set database name
1503      *
1504      * @param string $name
1505      */
1506     public function setDatabaseName($name)
1507     {
1508         $this->disconnectIfConnected();
1509         $this->databaseName = $name;
1510     }
1511
1512     /**
1513      * Set database username
1514      *
1515      * @param string $username
1516      */
1517     public function setDatabaseUsername($username)
1518     {
1519         $this->disconnectIfConnected();
1520         $this->databaseUsername = $username;
1521     }
1522
1523     /**
1524      * Set database password
1525      *
1526      * @param string $password
1527      */
1528     public function setDatabasePassword($password)
1529     {
1530         $this->disconnectIfConnected();
1531         $this->databaseUserPassword = $password;
1532     }
1533
1534     /**
1535      * Set persistent database connection
1536      *
1537      * @param bool $persistentDatabaseConnection
1538      * @see http://php.net/manual/de/mysqli.persistconns.php
1539      */
1540     public function setPersistentDatabaseConnection($persistentDatabaseConnection)
1541     {
1542         $this->disconnectIfConnected();
1543         $this->persistentDatabaseConnection = (bool)$persistentDatabaseConnection;
1544     }
1545
1546     /**
1547      * Set connection compression. Might be an advantage, if SQL server is not on localhost
1548      *
1549      * @param bool $connectionCompression TRUE if connection should be compressed
1550      */
1551     public function setConnectionCompression($connectionCompression)
1552     {
1553         $this->disconnectIfConnected();
1554         $this->connectionCompression = (bool)$connectionCompression;
1555     }
1556
1557     /**
1558      * Set commands to be fired after connection was established
1559      *
1560      * @param array $commands List of SQL commands to be executed after connect
1561      */
1562     public function setInitializeCommandsAfterConnect(array $commands)
1563     {
1564         $this->disconnectIfConnected();
1565         $this->initializeCommandsAfterConnect = $commands;
1566     }
1567
1568     /**
1569      * Set the charset that should be used for the MySQL connection.
1570      * The given value will be passed on to mysqli_set_charset().
1571      *
1572      * The default value of this setting is utf8.
1573      *
1574      * @param string $connectionCharset The connection charset that will be passed on to mysqli_set_charset() when connecting the database. Default is utf8.
1575      * @return void
1576      */
1577     public function setConnectionCharset($connectionCharset = 'utf8')
1578     {
1579         $this->disconnectIfConnected();
1580         $this->connectionCharset = $connectionCharset;
1581     }
1582
1583     /**
1584      * Connects to database for TYPO3 sites:
1585      *
1586      * @throws \RuntimeException
1587      * @throws \UnexpectedValueException
1588      * @return void
1589      */
1590     public function connectDB()
1591     {
1592         // Early return if connected already
1593         if ($this->isConnected) {
1594             return;
1595         }
1596
1597         if (!$this->databaseName) {
1598             throw new \RuntimeException(
1599                 'TYPO3 Fatal Error: No database selected!',
1600                 1270853882
1601             );
1602         }
1603
1604         if ($this->sql_pconnect()) {
1605             if (!$this->sql_select_db()) {
1606                 throw new \RuntimeException(
1607                     'TYPO3 Fatal Error: Cannot connect to the current database, "' . $this->databaseName . '"!',
1608                     1270853883
1609                 );
1610             }
1611         } else {
1612             throw new \RuntimeException(
1613                 'TYPO3 Fatal Error: The current username, password or host was not accepted when the connection to the database was attempted to be established!',
1614                 1270853884
1615             );
1616         }
1617
1618         // Prepare user defined objects (if any) for hooks which extend query methods
1619         $this->preProcessHookObjects = array();
1620         $this->postProcessHookObjects = array();
1621         if (is_array($GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_db.php']['queryProcessors'])) {
1622             foreach ($GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_db.php']['queryProcessors'] as $classRef) {
1623                 $hookObject = GeneralUtility::getUserObj($classRef);
1624                 if (!(
1625                     $hookObject instanceof PreProcessQueryHookInterface
1626                     || $hookObject instanceof PostProcessQueryHookInterface
1627                 )) {
1628                     throw new \UnexpectedValueException(
1629                         '$hookObject must either implement interface TYPO3\\CMS\\Core\\Database\\PreProcessQueryHookInterface or interface TYPO3\\CMS\\Core\\Database\\PostProcessQueryHookInterface',
1630                         1299158548
1631                     );
1632                 }
1633                 if ($hookObject instanceof PreProcessQueryHookInterface) {
1634                     $this->preProcessHookObjects[] = $hookObject;
1635                 }
1636                 if ($hookObject instanceof PostProcessQueryHookInterface) {
1637                     $this->postProcessHookObjects[] = $hookObject;
1638                 }
1639             }
1640         }
1641     }
1642
1643     /**
1644      * Checks if database is connected
1645      *
1646      * @return bool
1647      */
1648     public function isConnected()
1649     {
1650         // We think we're still connected
1651         if ($this->isConnected) {
1652             // Check if this is really the case or if the database server has gone away for some reason
1653             // Using mysqlnd ping() does not reconnect (which we would not want anyway since charset etc would not be reinitialized that way)
1654             $this->isConnected = $this->link->ping();
1655         }
1656         return $this->isConnected;
1657     }
1658
1659     /**
1660      * Checks if the current connection character set has the same value
1661      * as the connectionCharset variable.
1662      *
1663      * To determine the character set these MySQL session variables are
1664      * checked: character_set_client, character_set_results and
1665      * character_set_connection.
1666      *
1667      * If the character set does not match or if the session variables
1668      * can not be read a RuntimeException is thrown.
1669      *
1670      * @return void
1671      * @throws \RuntimeException
1672      */
1673     protected function checkConnectionCharset()
1674     {
1675         $sessionResult = $this->sql_query('SHOW SESSION VARIABLES LIKE \'character_set%\'');
1676
1677         if ($sessionResult === false) {
1678             GeneralUtility::sysLog(
1679                 'Error while retrieving the current charset session variables from the database: ' . $this->sql_error(),
1680                 'core',
1681                 GeneralUtility::SYSLOG_SEVERITY_ERROR
1682             );
1683             throw new \RuntimeException(
1684                 'TYPO3 Fatal Error: Could not determine the current charset of the database.',
1685                 1381847136
1686             );
1687         }
1688
1689         $charsetVariables = array();
1690         while (($row = $this->sql_fetch_row($sessionResult)) !== false) {
1691             $variableName = $row[0];
1692             $variableValue = $row[1];
1693             $charsetVariables[$variableName] = $variableValue;
1694         }
1695         $this->sql_free_result($sessionResult);
1696
1697         // These variables are set with the "Set names" command which was
1698         // used in the past. This is why we check them.
1699         $charsetRequiredVariables = array(
1700             'character_set_client',
1701             'character_set_results',
1702             'character_set_connection',
1703         );
1704
1705         $hasValidCharset = true;
1706         foreach ($charsetRequiredVariables as $variableName) {
1707             if (empty($charsetVariables[$variableName])) {
1708                 GeneralUtility::sysLog(
1709                     'A required session variable is missing in the current MySQL connection: ' . $variableName,
1710                     'core',
1711                     GeneralUtility::SYSLOG_SEVERITY_ERROR
1712                 );
1713                 throw new \RuntimeException(
1714                     'TYPO3 Fatal Error: Could not determine the value of the database session variable: ' . $variableName,
1715                     1381847779
1716                 );
1717             }
1718
1719             if ($charsetVariables[$variableName] !== $this->connectionCharset) {
1720                 $hasValidCharset = false;
1721                 break;
1722             }
1723         }
1724
1725         if (!$hasValidCharset) {
1726             throw new \RuntimeException(
1727                 'It looks like the character set ' . $this->connectionCharset . ' is not used for this connection even though it is configured as connection charset. ' .
1728                 'This TYPO3 installation is using the $GLOBALS[\'TYPO3_CONF_VARS\'][\'SYS\'][\'setDBinit\'] property with the following value: "' .
1729                 $GLOBALS['TYPO3_CONF_VARS']['SYS']['setDBinit'] . '". Please make sure that this command does not overwrite the configured charset. ' .
1730                 'Please note that for the TYPO3 database everything other than utf8 is unsupported since version 4.7.',
1731                 1389697515
1732             );
1733         }
1734     }
1735
1736     /**
1737      * Disconnect from database if connected
1738      *
1739      * @return void
1740      */
1741     protected function disconnectIfConnected()
1742     {
1743         if ($this->isConnected) {
1744             $this->link->close();
1745             $this->isConnected = false;
1746         }
1747     }
1748
1749     /**
1750      * Returns current database handle
1751      *
1752      * @return \mysqli|NULL
1753      */
1754     public function getDatabaseHandle()
1755     {
1756         return $this->link;
1757     }
1758
1759     /**
1760      * Set current database handle, usually \mysqli
1761      *
1762      * @param \mysqli $handle
1763      */
1764     public function setDatabaseHandle($handle)
1765     {
1766         $this->link = $handle;
1767     }
1768
1769     /**
1770      * Get the MySQL server version
1771      *
1772      * @return string
1773      */
1774     public function getServerVersion()
1775     {
1776         return $this->link->server_info;
1777     }
1778
1779     /******************************
1780      *
1781      * Debugging
1782      *
1783      ******************************/
1784     /**
1785      * Debug function: Outputs error if any
1786      *
1787      * @param string $func Function calling debug()
1788      * @param string $query Last query if not last built query
1789      * @return void
1790      */
1791     public function debug($func, $query = '')
1792     {
1793         $error = $this->sql_error();
1794         if ($error || (int)$this->debugOutput === 2) {
1795             \TYPO3\CMS\Core\Utility\DebugUtility::debug(
1796                 array(
1797                     'caller' => \TYPO3\CMS\Core\Database\DatabaseConnection::class . '::' . $func,
1798                     'ERROR' => $error,
1799                     'lastBuiltQuery' => $query ? $query : $this->debug_lastBuiltQuery,
1800                     'debug_backtrace' => \TYPO3\CMS\Core\Utility\DebugUtility::debugTrail()
1801                 ),
1802                 $func,
1803                 is_object($GLOBALS['error']) && @is_callable(array($GLOBALS['error'], 'debug'))
1804                     ? ''
1805                     : 'DB Error'
1806             );
1807         }
1808     }
1809
1810     /**
1811      * Checks if record set is valid and writes debugging information into devLog if not.
1812      *
1813      * @param bool|\mysqli_result|object MySQLi result object / DBAL object
1814      * @return bool TRUE if the  record set is valid, FALSE otherwise
1815      */
1816     public function debug_check_recordset($res)
1817     {
1818         if ($res !== false) {
1819             return true;
1820         }
1821         $trace = debug_backtrace(0);
1822         array_shift($trace);
1823         $msg = 'Invalid database result detected: function TYPO3\\CMS\\Core\\Database\\DatabaseConnection->'
1824             . $trace[0]['function'] . ' called from file ' . substr($trace[0]['file'], (strlen(PATH_site) + 2))
1825             . ' in line ' . $trace[0]['line'] . '.';
1826         GeneralUtility::sysLog(
1827             $msg . ' Use a devLog extension to get more details.',
1828             'core',
1829             GeneralUtility::SYSLOG_SEVERITY_ERROR
1830         );
1831         // Send to devLog if enabled
1832         if (TYPO3_DLOG) {
1833             $debugLogData = array(
1834                 'SQL Error' => $this->sql_error(),
1835                 'Backtrace' => $trace
1836             );
1837             if ($this->debug_lastBuiltQuery) {
1838                 $debugLogData = array('SQL Query' => $this->debug_lastBuiltQuery) + $debugLogData;
1839             }
1840             GeneralUtility::devLog($msg, 'Core/t3lib_db', 3, $debugLogData);
1841         }
1842         return false;
1843     }
1844
1845     /**
1846      * Explain select queries
1847      * If $this->explainOutput is set, SELECT queries will be explained here. Only queries with more than one possible result row will be displayed.
1848      * The output is either printed as raw HTML output or embedded into the TS admin panel (checkbox must be enabled!)
1849      *
1850      * @todo Feature is not DBAL-compliant
1851      *
1852      * @param string $query SQL query
1853      * @param string $from_table Table(s) from which to select. This is what comes right after "FROM ...". Required value.
1854      * @param int $row_count Number of resulting rows
1855      * @return bool TRUE if explain was run, FALSE otherwise
1856      */
1857     protected function explain($query, $from_table, $row_count)
1858     {
1859         $debugAllowedForIp = GeneralUtility::cmpIP(
1860             GeneralUtility::getIndpEnv('REMOTE_ADDR'),
1861             $GLOBALS['TYPO3_CONF_VARS']['SYS']['devIPmask']
1862         );
1863         if (
1864             (int)$this->explainOutput == 1
1865             || ((int)$this->explainOutput == 2 && $debugAllowedForIp)
1866         ) {
1867             // Raw HTML output
1868             $explainMode = 1;
1869         } elseif ((int)$this->explainOutput == 3 && is_object($GLOBALS['TT'])) {
1870             // Embed the output into the TS admin panel
1871             $explainMode = 2;
1872         } else {
1873             return false;
1874         }
1875         $error = $this->sql_error();
1876         $trail = \TYPO3\CMS\Core\Utility\DebugUtility::debugTrail();
1877         $explain_tables = array();
1878         $explain_output = array();
1879         $res = $this->sql_query('EXPLAIN ' . $query, $this->link);
1880         if (is_a($res, '\\mysqli_result')) {
1881             while ($tempRow = $this->sql_fetch_assoc($res)) {
1882                 $explain_output[] = $tempRow;
1883                 $explain_tables[] = $tempRow['table'];
1884             }
1885             $this->sql_free_result($res);
1886         }
1887         $indices_output = array();
1888         // Notice: Rows are skipped if there is only one result, or if no conditions are set
1889         if ($explain_output[0]['rows'] > 1 || $explain_output[0]['type'] === 'ALL') {
1890             // Only enable output if it's really useful
1891             $debug = true;
1892             foreach ($explain_tables as $table) {
1893                 $tableRes = $this->sql_query('SHOW TABLE STATUS LIKE \'' . $table . '\'');
1894                 $isTable = $this->sql_num_rows($tableRes);
1895                 if ($isTable) {
1896                     $res = $this->sql_query('SHOW INDEX FROM ' . $table, $this->link);
1897                     if (is_a($res, '\\mysqli_result')) {
1898                         while ($tempRow = $this->sql_fetch_assoc($res)) {
1899                             $indices_output[] = $tempRow;
1900                         }
1901                         $this->sql_free_result($res);
1902                     }
1903                 }
1904                 $this->sql_free_result($tableRes);
1905             }
1906         } else {
1907             $debug = false;
1908         }
1909         if ($debug) {
1910             if ($explainMode) {
1911                 $data = array();
1912                 $data['query'] = $query;
1913                 $data['trail'] = $trail;
1914                 $data['row_count'] = $row_count;
1915                 if ($error) {
1916                     $data['error'] = $error;
1917                 }
1918                 if (!empty($explain_output)) {
1919                     $data['explain'] = $explain_output;
1920                 }
1921                 if (!empty($indices_output)) {
1922                     $data['indices'] = $indices_output;
1923                 }
1924                 if ($explainMode == 1) {
1925                     \TYPO3\CMS\Core\Utility\DebugUtility::debug($data, 'Tables: ' . $from_table, 'DB SQL EXPLAIN');
1926                 } elseif ($explainMode == 2) {
1927                     $GLOBALS['TT']->setTSselectQuery($data);
1928                 }
1929             }
1930             return true;
1931         }
1932         return false;
1933     }
1934
1935     /**
1936      * Serialize destructs current connection
1937      *
1938      * @return array All protected properties that should be saved
1939      */
1940     public function __sleep()
1941     {
1942         $this->disconnectIfConnected();
1943         return array(
1944             'debugOutput',
1945             'explainOutput',
1946             'databaseHost',
1947             'databasePort',
1948             'databaseSocket',
1949             'databaseName',
1950             'databaseUsername',
1951             'databaseUserPassword',
1952             'persistentDatabaseConnection',
1953             'connectionCompression',
1954             'initializeCommandsAfterConnect',
1955             'default_charset',
1956         );
1957     }
1958 }