TYPO3CMS  7
 All Classes Namespaces Files Functions Variables Pages
core/Classes/Database/DatabaseConnection.php
Go to the documentation of this file.
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 
20 
47 {
53  const AND_Constraint = 'AND';
54 
60  const OR_Constraint = 'OR';
61 
67  public $debugOutput = false;
68 
74  public $debug_lastBuiltQuery = '';
75 
81  public $store_lastBuiltQuery = false;
82 
89  public $explainOutput = 0;
90 
94  protected $databaseHost = '';
95 
99  protected $databasePort = 3306;
100 
104  protected $databaseSocket = null;
105 
109  protected $databaseName = '';
110 
114  protected $databaseUsername = '';
115 
119  protected $databaseUserPassword = '';
120 
125  protected $persistentDatabaseConnection = false;
126 
130  protected $connectionCompression = false;
131 
138  protected $connectionCharset = 'utf8';
139 
143  protected $initializeCommandsAfterConnect = array();
144 
148  protected $isConnected = false;
149 
153  protected $link = null;
154 
160  public $default_charset = 'utf8';
161 
165  protected $preProcessHookObjects = array();
166 
170  protected $postProcessHookObjects = array();
171 
177  protected static $dateTimeFormats = array(
178  'date' => array(
179  'empty' => '0000-00-00',
180  'format' => 'Y-m-d'
181  ),
182  'datetime' => array(
183  'empty' => '0000-00-00 00:00:00',
184  'format' => 'Y-m-d H:i:s'
185  )
186  );
187 
193  public function initialize()
194  {
195  // Intentionally blank as this will be overloaded by DBAL
196  }
197 
198  /************************************
199  *
200  * Query execution
201  *
202  * These functions are the RECOMMENDED DBAL functions for use in your applications
203  * Using these functions will allow the DBAL to use alternative ways of accessing data (contrary to if a query is returned!)
204  * They compile a query AND execute it immediately and then return the result
205  * This principle heightens our ability to create various forms of DBAL of the functions.
206  * Generally: We want to return a result pointer/object, never queries.
207  * Also, having the table name together with the actual query execution allows us to direct the request to other databases.
208  *
209  **************************************/
210 
220  public function exec_INSERTquery($table, $fields_values, $no_quote_fields = false)
221  {
222  $res = $this->query($this->INSERTquery($table, $fields_values, $no_quote_fields));
223  if ($this->debugOutput) {
224  $this->debug('exec_INSERTquery');
225  }
226  foreach ($this->postProcessHookObjects as $hookObject) {
228  $hookObject->exec_INSERTquery_postProcessAction($table, $fields_values, $no_quote_fields, $this);
229  }
230  return $res;
231  }
232 
242  public function exec_INSERTmultipleRows($table, array $fields, array $rows, $no_quote_fields = false)
243  {
244  $res = $this->query($this->INSERTmultipleRows($table, $fields, $rows, $no_quote_fields));
245  if ($this->debugOutput) {
246  $this->debug('exec_INSERTmultipleRows');
247  }
248  foreach ($this->postProcessHookObjects as $hookObject) {
250  $hookObject->exec_INSERTmultipleRows_postProcessAction($table, $fields, $rows, $no_quote_fields, $this);
251  }
252  return $res;
253  }
254 
265  public function exec_UPDATEquery($table, $where, $fields_values, $no_quote_fields = false)
266  {
267  $res = $this->query($this->UPDATEquery($table, $where, $fields_values, $no_quote_fields));
268  if ($this->debugOutput) {
269  $this->debug('exec_UPDATEquery');
270  }
271  foreach ($this->postProcessHookObjects as $hookObject) {
273  $hookObject->exec_UPDATEquery_postProcessAction($table, $where, $fields_values, $no_quote_fields, $this);
274  }
275  return $res;
276  }
277 
285  public function exec_DELETEquery($table, $where)
286  {
287  $res = $this->query($this->DELETEquery($table, $where));
288  if ($this->debugOutput) {
289  $this->debug('exec_DELETEquery');
290  }
291  foreach ($this->postProcessHookObjects as $hookObject) {
293  $hookObject->exec_DELETEquery_postProcessAction($table, $where, $this);
294  }
295  return $res;
296  }
297 
310  public function exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '')
311  {
312  $query = $this->SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
313  $res = $this->query($query);
314  if ($this->debugOutput) {
315  $this->debug('exec_SELECTquery');
316  }
317  if ($this->explainOutput) {
318  $this->explain($query, $from_table, $res->num_rows);
319  }
320  foreach ($this->postProcessHookObjects as $hookObject) {
322  $hookObject->exec_SELECTquery_postProcessAction($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', $this);
323  }
324  return $res;
325  }
326 
344  public function exec_SELECT_mm_query($select, $local_table, $mm_table, $foreign_table, $whereClause = '', $groupBy = '', $orderBy = '', $limit = '')
345  {
346  $queryParts = $this->getSelectMmQueryParts($select, $local_table, $mm_table, $foreign_table, $whereClause, $groupBy, $orderBy, $limit);
347  return $this->exec_SELECT_queryArray($queryParts);
348  }
349 
357  public function exec_SELECT_queryArray($queryParts)
358  {
359  return $this->exec_SELECTquery($queryParts['SELECT'], $queryParts['FROM'], $queryParts['WHERE'], $queryParts['GROUPBY'], $queryParts['ORDERBY'], $queryParts['LIMIT']);
360  }
361 
376  public function exec_SELECTgetRows($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', $uidIndexField = '')
377  {
378  $res = $this->exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
379  if ($this->sql_error()) {
380  $this->sql_free_result($res);
381  return null;
382  }
383  $output = array();
384  $firstRecord = true;
385  while ($record = $this->sql_fetch_assoc($res)) {
386  if ($uidIndexField) {
387  if ($firstRecord) {
388  $firstRecord = false;
389  if (!array_key_exists($uidIndexField, $record)) {
390  $this->sql_free_result($res);
391  throw new \InvalidArgumentException('The given $uidIndexField "' . $uidIndexField . '" is not available in the result.', 1432933855);
392  }
393  }
394  $output[$record[$uidIndexField]] = $record;
395  } else {
396  $output[] = $record;
397  }
398  }
399  $this->sql_free_result($res);
400  return $output;
401  }
402 
415  public function exec_SELECTgetSingleRow($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $numIndex = false)
416  {
417  $res = $this->exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, '1');
418  $output = null;
419  if ($res !== false) {
420  if ($numIndex) {
421  $output = $this->sql_fetch_row($res);
422  } else {
423  $output = $this->sql_fetch_assoc($res);
424  }
425  $this->sql_free_result($res);
426  }
427  return $output;
428  }
429 
438  public function exec_SELECTcountRows($field, $table, $where = '1=1')
439  {
440  $count = false;
441  $resultSet = $this->exec_SELECTquery('COUNT(' . $field . ')', $table, $where);
442  if ($resultSet !== false) {
443  list($count) = $this->sql_fetch_row($resultSet);
444  $count = (int)$count;
445  $this->sql_free_result($resultSet);
446  }
447  return $count;
448  }
449 
456  public function exec_TRUNCATEquery($table)
457  {
458  $res = $this->query($this->TRUNCATEquery($table));
459  if ($this->debugOutput) {
460  $this->debug('exec_TRUNCATEquery');
461  }
462  foreach ($this->postProcessHookObjects as $hookObject) {
464  $hookObject->exec_TRUNCATEquery_postProcessAction($table, $this);
465  }
466  return $res;
467  }
468 
476  protected function query($query)
477  {
478  if (!$this->isConnected) {
479  $this->connectDB();
480  }
481  return $this->link->query($query);
482  }
483 
484  /**************************************
485  *
486  * Query building
487  *
488  **************************************/
497  public function INSERTquery($table, $fields_values, $no_quote_fields = false)
498  {
499  // Table and fieldnames should be "SQL-injection-safe" when supplied to this
500  // function (contrary to values in the arrays which may be insecure).
501  if (!is_array($fields_values) || empty($fields_values)) {
502  return null;
503  }
504  foreach ($this->preProcessHookObjects as $hookObject) {
505  $hookObject->INSERTquery_preProcessAction($table, $fields_values, $no_quote_fields, $this);
506  }
507  // Quote and escape values
508  $fields_values = $this->fullQuoteArray($fields_values, $table, $no_quote_fields, true);
509  // Build query
510  $query = 'INSERT INTO ' . $table . ' (' . implode(',', array_keys($fields_values)) . ') VALUES ' . '(' . implode(',', $fields_values) . ')';
511  // Return query
512  if ($this->debugOutput || $this->store_lastBuiltQuery) {
513  $this->debug_lastBuiltQuery = $query;
514  }
515  return $query;
516  }
517 
527  public function INSERTmultipleRows($table, array $fields, array $rows, $no_quote_fields = false)
528  {
529  // Table and fieldnames should be "SQL-injection-safe" when supplied to this
530  // function (contrary to values in the arrays which may be insecure).
531  if (empty($rows)) {
532  return null;
533  }
534  foreach ($this->preProcessHookObjects as $hookObject) {
536  $hookObject->INSERTmultipleRows_preProcessAction($table, $fields, $rows, $no_quote_fields, $this);
537  }
538  // Build query
539  $query = 'INSERT INTO ' . $table . ' (' . implode(', ', $fields) . ') VALUES ';
540  $rowSQL = array();
541  foreach ($rows as $row) {
542  // Quote and escape values
543  $row = $this->fullQuoteArray($row, $table, $no_quote_fields);
544  $rowSQL[] = '(' . implode(', ', $row) . ')';
545  }
546  $query .= implode(', ', $rowSQL);
547  // Return query
548  if ($this->debugOutput || $this->store_lastBuiltQuery) {
549  $this->debug_lastBuiltQuery = $query;
550  }
551  return $query;
552  }
553 
565  public function UPDATEquery($table, $where, $fields_values, $no_quote_fields = false)
566  {
567  // Table and fieldnames should be "SQL-injection-safe" when supplied to this
568  // function (contrary to values in the arrays which may be insecure).
569  if (is_string($where)) {
570  foreach ($this->preProcessHookObjects as $hookObject) {
572  $hookObject->UPDATEquery_preProcessAction($table, $where, $fields_values, $no_quote_fields, $this);
573  }
574  $fields = array();
575  if (is_array($fields_values) && !empty($fields_values)) {
576  // Quote and escape values
577  $nArr = $this->fullQuoteArray($fields_values, $table, $no_quote_fields, true);
578  foreach ($nArr as $k => $v) {
579  $fields[] = $k . '=' . $v;
580  }
581  }
582  // Build query
583  $query = 'UPDATE ' . $table . ' SET ' . implode(',', $fields) . ((string)$where !== '' ? ' WHERE ' . $where : '');
584  if ($this->debugOutput || $this->store_lastBuiltQuery) {
585  $this->debug_lastBuiltQuery = $query;
586  }
587  return $query;
588  } else {
589  throw new \InvalidArgumentException('TYPO3 Fatal Error: "Where" clause argument for UPDATE query was not a string in $this->UPDATEquery() !', 1270853880);
590  }
591  }
592 
601  public function DELETEquery($table, $where)
602  {
603  if (is_string($where)) {
604  foreach ($this->preProcessHookObjects as $hookObject) {
606  $hookObject->DELETEquery_preProcessAction($table, $where, $this);
607  }
608  // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
609  $query = 'DELETE FROM ' . $table . ((string)$where !== '' ? ' WHERE ' . $where : '');
610  if ($this->debugOutput || $this->store_lastBuiltQuery) {
611  $this->debug_lastBuiltQuery = $query;
612  }
613  return $query;
614  } else {
615  throw new \InvalidArgumentException('TYPO3 Fatal Error: "Where" clause argument for DELETE query was not a string in $this->DELETEquery() !', 1270853881);
616  }
617  }
618 
630  public function SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '')
631  {
632  foreach ($this->preProcessHookObjects as $hookObject) {
634  $hookObject->SELECTquery_preProcessAction($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit, $this);
635  }
636  // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
637  // Build basic query
638  $query = 'SELECT ' . $select_fields . ' FROM ' . $from_table . ((string)$where_clause !== '' ? ' WHERE ' . $where_clause : '');
639  // Group by
640  $query .= (string)$groupBy !== '' ? ' GROUP BY ' . $groupBy : '';
641  // Order by
642  $query .= (string)$orderBy !== '' ? ' ORDER BY ' . $orderBy : '';
643  // Group by
644  $query .= (string)$limit !== '' ? ' LIMIT ' . $limit : '';
645  // Return query
646  if ($this->debugOutput || $this->store_lastBuiltQuery) {
647  $this->debug_lastBuiltQuery = $query;
648  }
649  return $query;
650  }
651 
661  public function SELECTsubquery($select_fields, $from_table, $where_clause)
662  {
663  // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
664  // Build basic query:
665  $query = 'SELECT ' . $select_fields . ' FROM ' . $from_table . ((string)$where_clause !== '' ? ' WHERE ' . $where_clause : '');
666  // Return query
667  if ($this->debugOutput || $this->store_lastBuiltQuery) {
668  $this->debug_lastBuiltQuery = $query;
669  }
670  return $query;
671  }
672 
690  public function SELECT_mm_query($select, $local_table, $mm_table, $foreign_table, $whereClause = '', $groupBy = '', $orderBy = '', $limit = '')
691  {
692  $queryParts = $this->getSelectMmQueryParts($select, $local_table, $mm_table, $foreign_table, $whereClause, $groupBy, $orderBy, $limit);
693  return $this->SELECTquery($queryParts['SELECT'], $queryParts['FROM'], $queryParts['WHERE'], $queryParts['GROUPBY'], $queryParts['ORDERBY'], $queryParts['LIMIT']);
694  }
695 
702  public function TRUNCATEquery($table)
703  {
704  foreach ($this->preProcessHookObjects as $hookObject) {
706  $hookObject->TRUNCATEquery_preProcessAction($table, $this);
707  }
708  // Table should be "SQL-injection-safe" when supplied to this function
709  // Build basic query:
710  $query = 'TRUNCATE TABLE ' . $table;
711  // Return query:
712  if ($this->debugOutput || $this->store_lastBuiltQuery) {
713  $this->debug_lastBuiltQuery = $query;
714  }
715  return $query;
716  }
717 
733  public function listQuery($field, $value, $table)
734  {
735  $value = (string)$value;
736  if (strpos($value, ',') !== false) {
737  throw new \InvalidArgumentException('$value must not contain a comma (,) in $this->listQuery() !', 1294585862);
738  }
739  $pattern = $this->quoteStr($value, $table);
740  $where = 'FIND_IN_SET(\'' . $pattern . '\',' . $field . ')';
741  return $where;
742  }
743 
753  public function searchQuery($searchWords, $fields, $table, $constraint = self::AND_Constraint)
754  {
755  switch ($constraint) {
756  case self::OR_Constraint:
757  $constraint = 'OR';
758  break;
759  default:
760  $constraint = 'AND';
761  }
762 
763  $queryParts = array();
764  foreach ($searchWords as $sw) {
765  $like = ' LIKE \'%' . $this->quoteStr($this->escapeStrForLike($sw, $table), $table) . '%\'';
766  $queryParts[] = $table . '.' . implode(($like . ' OR ' . $table . '.'), $fields) . $like;
767  }
768  $query = '(' . implode(') ' . $constraint . ' (', $queryParts) . ')';
769 
770  return $query;
771  }
772 
773  /**************************************
774  *
775  * Prepared Query Support
776  *
777  **************************************/
790  public function prepare_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', array $input_parameters = array())
791  {
792  $query = $this->SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
794  $preparedStatement = GeneralUtility::makeInstance(\TYPO3\CMS\Core\Database\PreparedStatement::class, $query, $from_table, array());
795  // Bind values to parameters
796  foreach ($input_parameters as $key => $value) {
797  $preparedStatement->bindValue($key, $value, PreparedStatement::PARAM_AUTOTYPE);
798  }
799  // Return prepared statement
800  return $preparedStatement;
801  }
802 
810  public function prepare_SELECTqueryArray(array $queryParts, array $input_parameters = array())
811  {
812  return $this->prepare_SELECTquery($queryParts['SELECT'], $queryParts['FROM'], $queryParts['WHERE'], $queryParts['GROUPBY'], $queryParts['ORDERBY'], $queryParts['LIMIT'], $input_parameters);
813  }
814 
823  public function prepare_PREPAREDquery($query, array $queryComponents)
824  {
825  if (!$this->isConnected) {
826  $this->connectDB();
827  }
828  $stmt = $this->link->stmt_init();
829  $success = $stmt->prepare($query);
830  if ($this->debugOutput) {
831  $this->debug('stmt_execute', $query);
832  }
833  return $success ? $stmt : null;
834  }
835 
836  /**************************************
837  *
838  * Various helper functions
839  *
840  * Functions recommended to be used for
841  * - escaping values,
842  * - cleaning lists of values,
843  * - stripping of excess ORDER BY/GROUP BY keywords
844  *
845  **************************************/
855  public function fullQuoteStr($str, $table, $allowNull = false)
856  {
857  if (!$this->isConnected) {
858  $this->connectDB();
859  }
860  if ($allowNull && $str === null) {
861  return 'NULL';
862  }
863  if (is_bool($str)) {
864  $str = (int)$str;
865  }
866 
867  return '\'' . $this->link->real_escape_string($str) . '\'';
868  }
869 
880  public function fullQuoteArray($arr, $table, $noQuote = false, $allowNull = false)
881  {
882  if (is_string($noQuote)) {
883  $noQuote = explode(',', $noQuote);
884  } elseif (!is_array($noQuote)) {
885  $noQuote = (bool)$noQuote;
886  }
887  if ($noQuote === true) {
888  return $arr;
889  }
890  foreach ($arr as $k => $v) {
891  if ($noQuote === false || !in_array($k, $noQuote)) {
892  $arr[$k] = $this->fullQuoteStr($v, $table, $allowNull);
893  }
894  }
895  return $arr;
896  }
897 
908  public function quoteStr($str, $table)
909  {
910  if (!$this->isConnected) {
911  $this->connectDB();
912  }
913  return $this->link->real_escape_string($str);
914  }
915 
924  public function escapeStrForLike($str, $table)
925  {
926  return addcslashes($str, '_%');
927  }
928 
937  public function cleanIntArray($arr)
938  {
939  return array_map('intval', $arr);
940  }
941 
950  public function cleanIntList($list)
951  {
952  return implode(',', GeneralUtility::intExplode(',', $list));
953  }
954 
964  public function stripOrderBy($str)
965  {
966  return preg_replace('/^(?:ORDER[[:space:]]*BY[[:space:]]*)+/i', '', trim($str));
967  }
968 
978  public function stripGroupBy($str)
979  {
980  return preg_replace('/^(?:GROUP[[:space:]]*BY[[:space:]]*)+/i', '', trim($str));
981  }
982 
989  public function getDateTimeFormats($table)
990  {
991  return self::$dateTimeFormats;
992  }
993 
1010  protected function getSelectMmQueryParts($select, $local_table, $mm_table, $foreign_table, $whereClause = '', $groupBy = '', $orderBy = '', $limit = '')
1011  {
1012  $foreign_table_as = $foreign_table == $local_table ? $foreign_table . StringUtility::getUniqueId('_join') : '';
1013  $mmWhere = $local_table ? $local_table . '.uid=' . $mm_table . '.uid_local' : '';
1014  $mmWhere .= ($local_table and $foreign_table) ? ' AND ' : '';
1015  $tables = ($local_table ? $local_table . ',' : '') . $mm_table;
1016  if ($foreign_table) {
1017  $mmWhere .= ($foreign_table_as ?: $foreign_table) . '.uid=' . $mm_table . '.uid_foreign';
1018  $tables .= ',' . $foreign_table . ($foreign_table_as ? ' AS ' . $foreign_table_as : '');
1019  }
1020  return array(
1021  'SELECT' => $select,
1022  'FROM' => $tables,
1023  'WHERE' => $mmWhere . ' ' . $whereClause,
1024  'GROUPBY' => $groupBy,
1025  'ORDERBY' => $orderBy,
1026  'LIMIT' => $limit
1027  );
1028  }
1029 
1030  /**************************************
1031  *
1032  * MySQL(i) wrapper functions
1033  * (For use in your applications)
1034  *
1035  **************************************/
1045  public function sql_query($query)
1046  {
1047  $res = $this->query($query);
1048  if ($this->debugOutput) {
1049  $this->debug('sql_query', $query);
1050  }
1051  return $res;
1052  }
1053 
1059  public function sql_error()
1060  {
1061  return $this->link->error;
1062  }
1063 
1069  public function sql_errno()
1070  {
1071  return $this->link->errno;
1072  }
1073 
1080  public function sql_num_rows($res)
1081  {
1082  if ($this->debug_check_recordset($res)) {
1083  return $res->num_rows;
1084  } else {
1085  return false;
1086  }
1087  }
1088 
1096  public function sql_fetch_assoc($res)
1097  {
1098  if ($this->debug_check_recordset($res)) {
1099  $result = $res->fetch_assoc();
1100  if ($result === null) {
1101  // Needed for compatibility
1102  $result = false;
1103  }
1104  return $result;
1105  } else {
1106  return false;
1107  }
1108  }
1109 
1118  public function sql_fetch_row($res)
1119  {
1120  if ($this->debug_check_recordset($res)) {
1121  $result = $res->fetch_row();
1122  if ($result === null) {
1123  // Needed for compatibility
1124  $result = false;
1125  }
1126  return $result;
1127  } else {
1128  return false;
1129  }
1130  }
1131 
1139  public function sql_free_result($res)
1140  {
1141  if ($this->debug_check_recordset($res) && is_object($res)) {
1142  $res->free();
1143  return true;
1144  } else {
1145  return false;
1146  }
1147  }
1148 
1154  public function sql_insert_id()
1155  {
1156  return $this->link->insert_id;
1157  }
1158 
1164  public function sql_affected_rows()
1165  {
1166  return $this->link->affected_rows;
1167  }
1168 
1176  public function sql_data_seek($res, $seek)
1177  {
1178  if ($this->debug_check_recordset($res)) {
1179  return $res->data_seek($seek);
1180  } else {
1181  return false;
1182  }
1183  }
1184 
1193  public function sql_field_type($res, $pointer)
1194  {
1195  // mysql_field_type compatibility map
1196  // taken from: http://www.php.net/manual/en/mysqli-result.fetch-field-direct.php#89117
1197  // Constant numbers see http://php.net/manual/en/mysqli.constants.php
1198  $mysql_data_type_hash = array(
1199  1=>'tinyint',
1200  2=>'smallint',
1201  3=>'int',
1202  4=>'float',
1203  5=>'double',
1204  7=>'timestamp',
1205  8=>'bigint',
1206  9=>'mediumint',
1207  10=>'date',
1208  11=>'time',
1209  12=>'datetime',
1210  13=>'year',
1211  16=>'bit',
1212  //252 is currently mapped to all text and blob types (MySQL 5.0.51a)
1213  253=>'varchar',
1214  254=>'char',
1215  246=>'decimal'
1216  );
1217  if ($this->debug_check_recordset($res)) {
1218  $metaInfo = $res->fetch_field_direct($pointer);
1219  if ($metaInfo === false) {
1220  return false;
1221  }
1222  return $mysql_data_type_hash[$metaInfo->type];
1223  } else {
1224  return false;
1225  }
1226  }
1227 
1234  public function sql_pconnect()
1235  {
1236  if ($this->isConnected) {
1237  return $this->link;
1238  }
1239 
1240  if (!extension_loaded('mysqli')) {
1241  throw new \RuntimeException(
1242  'Database Error: PHP mysqli extension not loaded. This is a must have for TYPO3 CMS!',
1243  1271492607
1244  );
1245  }
1246 
1247  $host = $this->persistentDatabaseConnection
1248  ? 'p:' . $this->databaseHost
1249  : $this->databaseHost;
1250 
1251  // We are not using the TYPO3 CMS shim here as the database parameters in this class
1252  // are settable externally. This requires building the connection parameter array
1253  // just in time when establishing the connection.
1254  $connection = \Doctrine\DBAL\DriverManager::getConnection([
1255  'driver' => 'mysqli',
1256  'wrapperClass' => Connection::class,
1257  'host' => $host,
1258  'port' => (int)$this->databasePort,
1259  'unix_socket' => $this->databaseSocket,
1260  'user' => $this->databaseUsername,
1261  'password' => $this->databaseUserPassword,
1262  'charset' => $this->connectionCharset,
1263  ]);
1264 
1265  // Mimic the previous behavior of returning false on connection errors
1266  try {
1268  $mysqliConnection = $connection->getWrappedConnection();
1269  $this->link = $mysqliConnection->getWrappedResourceHandle();
1270  } catch (\Doctrine\DBAL\Exception\ConnectionException $exception) {
1271  return false;
1272  }
1273 
1274  if ($connection->isConnected()) {
1275  $this->isConnected = true;
1276 
1277  foreach ($this->initializeCommandsAfterConnect as $command) {
1278  if ($this->query($command) === false) {
1279  GeneralUtility::sysLog(
1280  'Could not initialize DB connection with query "' . $command . '": ' . $this->sql_error(),
1281  'core',
1283  );
1284  }
1285  }
1286  $this->checkConnectionCharset();
1287  } else {
1288  // @todo This should raise an exception. Would be useful especially to work during installation.
1289  $error_msg = $this->link->connect_error;
1290  $this->link = null;
1291  GeneralUtility::sysLog(
1292  'Could not connect to MySQL server ' . $host . ' with user ' . $this->databaseUsername . ': '
1293  . $error_msg,
1294  'core',
1296  );
1297  }
1298 
1299  return $this->link;
1300  }
1301 
1307  public function sql_select_db()
1308  {
1309  if (!$this->isConnected) {
1310  $this->connectDB();
1311  }
1312 
1313  $ret = $this->link->select_db($this->databaseName);
1314  if (!$ret) {
1315  GeneralUtility::sysLog(
1316  'Could not select MySQL database ' . $this->databaseName . ': ' . $this->sql_error(),
1317  'core',
1319  );
1320  }
1321  return $ret;
1322  }
1323 
1324  /**************************************
1325  *
1326  * SQL admin functions
1327  * (For use in the Install Tool and Extension Manager)
1328  *
1329  **************************************/
1339  public function admin_get_dbs()
1340  {
1341  $dbArr = array();
1342  $db_list = $this->query('SELECT SCHEMA_NAME FROM information_schema.SCHEMATA');
1343  if ($db_list === false) {
1344  throw new \RuntimeException(
1345  'MySQL Error: Cannot get tablenames: "' . $this->sql_error() . '"!',
1346  1378457171
1347  );
1348  } else {
1349  while ($row = $db_list->fetch_object()) {
1350  try {
1351  $this->setDatabaseName($row->SCHEMA_NAME);
1352  if ($this->sql_select_db()) {
1353  $dbArr[] = $row->SCHEMA_NAME;
1354  }
1355  } catch (\RuntimeException $exception) {
1356  // The exception happens if we cannot connect to the database
1357  // (usually due to missing permissions). This is ok here.
1358  // We catch the exception, skip the database and continue.
1359  }
1360  }
1361  }
1362  return $dbArr;
1363  }
1364 
1372  public function admin_get_tables()
1373  {
1374  $whichTables = array();
1375  $tables_result = $this->query('SHOW TABLE STATUS FROM `' . $this->databaseName . '`');
1376  if ($tables_result !== false) {
1377  while ($theTable = $tables_result->fetch_assoc()) {
1378  $whichTables[$theTable['Name']] = $theTable;
1379  }
1380  $tables_result->free();
1381  }
1382  return $whichTables;
1383  }
1384 
1396  public function admin_get_fields($tableName)
1397  {
1398  $output = array();
1399  $columns_res = $this->query('SHOW FULL COLUMNS FROM `' . $tableName . '`');
1400  if ($columns_res !== false) {
1401  while ($fieldRow = $columns_res->fetch_assoc()) {
1402  $output[$fieldRow['Field']] = $fieldRow;
1403  }
1404  $columns_res->free();
1405  }
1406  return $output;
1407  }
1408 
1416  public function admin_get_keys($tableName)
1417  {
1418  $output = array();
1419  $keyRes = $this->query('SHOW KEYS FROM `' . $tableName . '`');
1420  if ($keyRes !== false) {
1421  while ($keyRow = $keyRes->fetch_assoc()) {
1422  $output[] = $keyRow;
1423  }
1424  $keyRes->free();
1425  }
1426  return $output;
1427  }
1428 
1441  public function admin_get_charsets()
1442  {
1443  $output = array();
1444  $columns_res = $this->query('SHOW CHARACTER SET');
1445  if ($columns_res !== false) {
1446  while ($row = $columns_res->fetch_assoc()) {
1447  $output[$row['Charset']] = $row;
1448  }
1449  $columns_res->free();
1450  }
1451  return $output;
1452  }
1453 
1460  public function admin_query($query)
1461  {
1462  $res = $this->query($query);
1463  if ($this->debugOutput) {
1464  $this->debug('admin_query', $query);
1465  }
1466  return $res;
1467  }
1468 
1469  /******************************
1470  *
1471  * Connect handling
1472  *
1473  ******************************/
1474 
1480  public function setDatabaseHost($host = 'localhost')
1481  {
1482  $this->disconnectIfConnected();
1483  $this->databaseHost = $host;
1484  }
1485 
1491  public function setDatabasePort($port = 3306)
1492  {
1493  $this->disconnectIfConnected();
1494  $this->databasePort = (int)$port;
1495  }
1496 
1502  public function setDatabaseSocket($socket = null)
1503  {
1504  $this->disconnectIfConnected();
1505  $this->databaseSocket = $socket;
1506  }
1507 
1513  public function setDatabaseName($name)
1514  {
1515  $this->disconnectIfConnected();
1516  $this->databaseName = $name;
1517  }
1518 
1524  public function setDatabaseUsername($username)
1525  {
1526  $this->disconnectIfConnected();
1527  $this->databaseUsername = $username;
1528  }
1529 
1535  public function setDatabasePassword($password)
1536  {
1537  $this->disconnectIfConnected();
1538  $this->databaseUserPassword = $password;
1539  }
1540 
1547  public function setPersistentDatabaseConnection($persistentDatabaseConnection)
1548  {
1549  $this->disconnectIfConnected();
1550  $this->persistentDatabaseConnection = (bool)$persistentDatabaseConnection;
1551  }
1552 
1558  public function setConnectionCompression($connectionCompression)
1559  {
1560  $this->disconnectIfConnected();
1561  $this->connectionCompression = (bool)$connectionCompression;
1562  }
1563 
1569  public function setInitializeCommandsAfterConnect(array $commands)
1570  {
1571  $this->disconnectIfConnected();
1572  $this->initializeCommandsAfterConnect = $commands;
1573  }
1574 
1584  public function setConnectionCharset($connectionCharset = 'utf8')
1585  {
1586  $this->disconnectIfConnected();
1587  $this->connectionCharset = $connectionCharset;
1588  }
1589 
1597  public function connectDB()
1598  {
1599  // Early return if connected already
1600  if ($this->isConnected) {
1601  return;
1602  }
1603 
1604  if (!$this->databaseName) {
1605  throw new \RuntimeException(
1606  'TYPO3 Fatal Error: No database selected!',
1607  1270853882
1608  );
1609  }
1610 
1611  if ($this->sql_pconnect()) {
1612  if (!$this->sql_select_db()) {
1613  throw new \RuntimeException(
1614  'TYPO3 Fatal Error: Cannot connect to the current database, "' . $this->databaseName . '"!',
1615  1270853883
1616  );
1617  }
1618  } else {
1619  throw new \RuntimeException(
1620  'TYPO3 Fatal Error: The current username, password or host was not accepted when the connection to the database was attempted to be established!',
1621  1270853884
1622  );
1623  }
1624 
1625  // Prepare user defined objects (if any) for hooks which extend query methods
1626  $this->preProcessHookObjects = array();
1627  $this->postProcessHookObjects = array();
1628  if (is_array($GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_db.php']['queryProcessors'])) {
1629  foreach ($GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_db.php']['queryProcessors'] as $classRef) {
1630  $hookObject = GeneralUtility::getUserObj($classRef);
1631  if (!(
1632  $hookObject instanceof PreProcessQueryHookInterface
1633  || $hookObject instanceof PostProcessQueryHookInterface
1634  )) {
1635  throw new \UnexpectedValueException(
1636  '$hookObject must either implement interface TYPO3\\CMS\\Core\\Database\\PreProcessQueryHookInterface or interface TYPO3\\CMS\\Core\\Database\\PostProcessQueryHookInterface',
1637  1299158548
1638  );
1639  }
1640  if ($hookObject instanceof PreProcessQueryHookInterface) {
1641  $this->preProcessHookObjects[] = $hookObject;
1642  }
1643  if ($hookObject instanceof PostProcessQueryHookInterface) {
1644  $this->postProcessHookObjects[] = $hookObject;
1645  }
1646  }
1647  }
1648  }
1649 
1655  public function isConnected()
1656  {
1657  // We think we're still connected
1658  if ($this->isConnected) {
1659  // Check if this is really the case or if the database server has gone away for some reason
1660  // Using mysqlnd ping() does not reconnect (which we would not want anyway since charset etc would not be reinitialized that way)
1661  $this->isConnected = $this->link->ping();
1662  }
1663  return $this->isConnected;
1664  }
1665 
1680  protected function checkConnectionCharset()
1681  {
1682  $sessionResult = $this->sql_query('SHOW SESSION VARIABLES LIKE \'character_set%\'');
1683 
1684  if ($sessionResult === false) {
1685  GeneralUtility::sysLog(
1686  'Error while retrieving the current charset session variables from the database: ' . $this->sql_error(),
1687  'core',
1689  );
1690  throw new \RuntimeException(
1691  'TYPO3 Fatal Error: Could not determine the current charset of the database.',
1692  1381847136
1693  );
1694  }
1695 
1696  $charsetVariables = array();
1697  while (($row = $this->sql_fetch_row($sessionResult)) !== false) {
1698  $variableName = $row[0];
1699  $variableValue = $row[1];
1700  $charsetVariables[$variableName] = $variableValue;
1701  }
1702  $this->sql_free_result($sessionResult);
1703 
1704  // These variables are set with the "Set names" command which was
1705  // used in the past. This is why we check them.
1706  $charsetRequiredVariables = array(
1707  'character_set_client',
1708  'character_set_results',
1709  'character_set_connection',
1710  );
1711 
1712  $hasValidCharset = true;
1713  foreach ($charsetRequiredVariables as $variableName) {
1714  if (empty($charsetVariables[$variableName])) {
1715  GeneralUtility::sysLog(
1716  'A required session variable is missing in the current MySQL connection: ' . $variableName,
1717  'core',
1719  );
1720  throw new \RuntimeException(
1721  'TYPO3 Fatal Error: Could not determine the value of the database session variable: ' . $variableName,
1722  1381847779
1723  );
1724  }
1725 
1726  if ($charsetVariables[$variableName] !== $this->connectionCharset) {
1727  $hasValidCharset = false;
1728  break;
1729  }
1730  }
1731 
1732  if (!$hasValidCharset) {
1733  throw new \RuntimeException(
1734  'It looks like the character set ' . $this->connectionCharset . ' is not used for this connection even though it is configured as connection charset. ' .
1735  'This TYPO3 installation is using the $GLOBALS[\'TYPO3_CONF_VARS\'][\'SYS\'][\'setDBinit\'] property with the following value: "' .
1736  $GLOBALS['TYPO3_CONF_VARS']['SYS']['setDBinit'] . '". Please make sure that this command does not overwrite the configured charset. ' .
1737  'Please note that for the TYPO3 database everything other than utf8 is unsupported since version 4.7.',
1738  1389697515
1739  );
1740  }
1741  }
1742 
1748  protected function disconnectIfConnected()
1749  {
1750  if ($this->isConnected) {
1751  $this->link->close();
1752  $this->isConnected = false;
1753  }
1754  }
1755 
1761  public function getDatabaseHandle()
1762  {
1763  return $this->link;
1764  }
1765 
1771  public function setDatabaseHandle($handle)
1772  {
1773  $this->link = $handle;
1774  }
1775 
1781  public function getServerVersion()
1782  {
1783  return $this->link->server_info;
1784  }
1785 
1786  /******************************
1787  *
1788  * Debugging
1789  *
1790  ******************************/
1798  public function debug($func, $query = '')
1799  {
1800  $error = $this->sql_error();
1801  if ($error || (int)$this->debugOutput === 2) {
1803  array(
1804  'caller' => \TYPO3\CMS\Core\Database\DatabaseConnection::class . '::' . $func,
1805  'ERROR' => $error,
1806  'lastBuiltQuery' => $query ? $query : $this->debug_lastBuiltQuery,
1807  'debug_backtrace' => \TYPO3\CMS\Core\Utility\DebugUtility::debugTrail()
1808  ),
1809  $func,
1810  is_object($GLOBALS['error']) && @is_callable(array($GLOBALS['error'], 'debug'))
1811  ? ''
1812  : 'DB Error'
1813  );
1814  }
1815  }
1816 
1823  public function debug_check_recordset($res)
1824  {
1825  if ($res !== false) {
1826  return true;
1827  }
1828  $trace = debug_backtrace(0);
1829  array_shift($trace);
1830  $msg = 'Invalid database result detected: function TYPO3\\CMS\\Core\\Database\\DatabaseConnection->'
1831  . $trace[0]['function'] . ' called from file ' . substr($trace[0]['file'], (strlen(PATH_site) + 2))
1832  . ' in line ' . $trace[0]['line'] . '.';
1833  GeneralUtility::sysLog(
1834  $msg . ' Use a devLog extension to get more details.',
1835  'core',
1837  );
1838  // Send to devLog if enabled
1839  if (TYPO3_DLOG) {
1840  $debugLogData = array(
1841  'SQL Error' => $this->sql_error(),
1842  'Backtrace' => $trace
1843  );
1844  if ($this->debug_lastBuiltQuery) {
1845  $debugLogData = array('SQL Query' => $this->debug_lastBuiltQuery) + $debugLogData;
1846  }
1847  GeneralUtility::devLog($msg, 'Core/t3lib_db', 3, $debugLogData);
1848  }
1849  return false;
1850  }
1851 
1864  protected function explain($query, $from_table, $row_count)
1865  {
1866  $debugAllowedForIp = GeneralUtility::cmpIP(
1867  GeneralUtility::getIndpEnv('REMOTE_ADDR'),
1868  $GLOBALS['TYPO3_CONF_VARS']['SYS']['devIPmask']
1869  );
1870  if (
1871  (int)$this->explainOutput == 1
1872  || ((int)$this->explainOutput == 2 && $debugAllowedForIp)
1873  ) {
1874  // Raw HTML output
1875  $explainMode = 1;
1876  } elseif ((int)$this->explainOutput == 3) {
1877  // Embed the output into the TS admin panel
1878  $explainMode = 2;
1879  } else {
1880  return false;
1881  }
1882  $error = $this->sql_error();
1884  $explain_tables = array();
1885  $explain_output = array();
1886  $res = $this->sql_query('EXPLAIN ' . $query, $this->link);
1887  if (is_a($res, '\\mysqli_result')) {
1888  while ($tempRow = $this->sql_fetch_assoc($res)) {
1889  $explain_output[] = $tempRow;
1890  $explain_tables[] = $tempRow['table'];
1891  }
1892  $this->sql_free_result($res);
1893  }
1894  $indices_output = array();
1895  // Notice: Rows are skipped if there is only one result, or if no conditions are set
1896  if ($explain_output[0]['rows'] > 1 || $explain_output[0]['type'] === 'ALL') {
1897  // Only enable output if it's really useful
1898  $debug = true;
1899  foreach ($explain_tables as $table) {
1900  $tableRes = $this->sql_query('SHOW TABLE STATUS LIKE \'' . $table . '\'');
1901  $isTable = $this->sql_num_rows($tableRes);
1902  if ($isTable) {
1903  $res = $this->sql_query('SHOW INDEX FROM ' . $table, $this->link);
1904  if (is_a($res, '\\mysqli_result')) {
1905  while ($tempRow = $this->sql_fetch_assoc($res)) {
1906  $indices_output[] = $tempRow;
1907  }
1908  $this->sql_free_result($res);
1909  }
1910  }
1911  $this->sql_free_result($tableRes);
1912  }
1913  } else {
1914  $debug = false;
1915  }
1916  if ($debug) {
1917  if ($explainMode) {
1918  $data = array();
1919  $data['query'] = $query;
1920  $data['trail'] = $trail;
1921  $data['row_count'] = $row_count;
1922  if ($error) {
1923  $data['error'] = $error;
1924  }
1925  if (!empty($explain_output)) {
1926  $data['explain'] = $explain_output;
1927  }
1928  if (!empty($indices_output)) {
1929  $data['indices'] = $indices_output;
1930  }
1931  if ($explainMode == 1) {
1932  \TYPO3\CMS\Core\Utility\DebugUtility::debug($data, 'Tables: ' . $from_table, 'DB SQL EXPLAIN');
1933  } elseif ($explainMode == 2) {
1935  $timeTracker = GeneralUtility::makeInstance(TimeTracker::class);
1936  $timeTracker->setTSselectQuery($data);
1937  }
1938  }
1939  return true;
1940  }
1941  return false;
1942  }
1943 
1949  public function __sleep()
1950  {
1951  $this->disconnectIfConnected();
1952  return array(
1953  'debugOutput',
1954  'explainOutput',
1955  'databaseHost',
1956  'databasePort',
1957  'databaseSocket',
1958  'databaseName',
1959  'databaseUsername',
1960  'databaseUserPassword',
1961  'persistentDatabaseConnection',
1962  'connectionCompression',
1963  'initializeCommandsAfterConnect',
1964  'default_charset',
1965  );
1966  }
1967 }
SELECT_mm_query($select, $local_table, $mm_table, $foreign_table, $whereClause= '', $groupBy= '', $orderBy= '', $limit= '')
SELECTsubquery($select_fields, $from_table, $where_clause)
exec_SELECTgetSingleRow($select_fields, $from_table, $where_clause, $groupBy= '', $orderBy= '', $numIndex=false)
exec_SELECT_mm_query($select, $local_table, $mm_table, $foreign_table, $whereClause= '', $groupBy= '', $orderBy= '', $limit= '')
INSERTquery($table, $fields_values, $no_quote_fields=false)
exec_SELECTgetRows($select_fields, $from_table, $where_clause, $groupBy= '', $orderBy= '', $limit= '', $uidIndexField= '')
getSelectMmQueryParts($select, $local_table, $mm_table, $foreign_table, $whereClause= '', $groupBy= '', $orderBy= '', $limit= '')
prepare_SELECTqueryArray(array $queryParts, array $input_parameters=array())
fullQuoteArray($arr, $table, $noQuote=false, $allowNull=false)
if(TYPO3_MODE=== 'BE') $GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_tsfebeuserauth.php']['frontendEditingController']['default']
static debugTrail($prependFileNames=false)
static debug($var= '', $header= '', $group= 'Debug')
static makeInstance($className,...$constructorArguments)
debug($variable= '', $name= '*variable *', $line= '*line *', $file= '*file *', $recursiveDepth=3, $debugLevel=E_DEBUG)
static intExplode($delimiter, $string, $removeEmptyValues=false, $limit=0)
static devLog($msg, $extKey, $severity=0, $dataVar=false)