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 
19 
46 {
52  const AND_Constraint = 'AND';
53 
59  const OR_Constraint = 'OR';
60 
66  public $debugOutput = false;
67 
73  public $debug_lastBuiltQuery = '';
74 
80  public $store_lastBuiltQuery = false;
81 
88  public $explainOutput = 0;
89 
93  protected $databaseHost = '';
94 
98  protected $databasePort = 3306;
99 
103  protected $databaseSocket = null;
104 
108  protected $databaseName = '';
109 
113  protected $databaseUsername = '';
114 
118  protected $databaseUserPassword = '';
119 
124  protected $persistentDatabaseConnection = false;
125 
129  protected $connectionCompression = false;
130 
137  protected $connectionCharset = 'utf8';
138 
142  protected $initializeCommandsAfterConnect = array();
143 
147  protected $isConnected = false;
148 
152  protected $link = null;
153 
159  public $default_charset = 'utf8';
160 
164  protected $preProcessHookObjects = array();
165 
169  protected $postProcessHookObjects = array();
170 
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 
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 
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) {
227  $hookObject->exec_INSERTquery_postProcessAction($table, $fields_values, $no_quote_fields, $this);
228  }
229  return $res;
230  }
231 
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) {
249  $hookObject->exec_INSERTmultipleRows_postProcessAction($table, $fields, $rows, $no_quote_fields, $this);
250  }
251  return $res;
252  }
253 
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) {
272  $hookObject->exec_UPDATEquery_postProcessAction($table, $where, $fields_values, $no_quote_fields, $this);
273  }
274  return $res;
275  }
276 
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) {
292  $hookObject->exec_DELETEquery_postProcessAction($table, $where, $this);
293  }
294  return $res;
295  }
296 
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) {
321  $hookObject->exec_SELECTquery_postProcessAction($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', $this);
322  }
323  return $res;
324  }
325 
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 
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 
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 
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 
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 
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) {
463  $hookObject->exec_TRUNCATEquery_postProcessAction($table, $this);
464  }
465  return $res;
466  }
467 
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  **************************************/
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 
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) {
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 
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) {
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 
600  public function DELETEquery($table, $where)
601  {
602  if (is_string($where)) {
603  foreach ($this->preProcessHookObjects as $hookObject) {
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 
629  public function SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '')
630  {
631  foreach ($this->preProcessHookObjects as $hookObject) {
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 
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 
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 
701  public function TRUNCATEquery($table)
702  {
703  foreach ($this->preProcessHookObjects as $hookObject) {
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 
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 
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  **************************************/
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);
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 
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 
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  **************************************/
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 
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 
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 
923  public function escapeStrForLike($str, $table)
924  {
925  return addcslashes($str, '_%');
926  }
927 
936  public function cleanIntArray($arr)
937  {
938  return array_map('intval', $arr);
939  }
940 
949  public function cleanIntList($list)
950  {
951  return implode(',', GeneralUtility::intExplode(',', $list));
952  }
953 
963  public function stripOrderBy($str)
964  {
965  return preg_replace('/^(?:ORDER[[:space:]]*BY[[:space:]]*)+/i', '', trim($str));
966  }
967 
977  public function stripGroupBy($str)
978  {
979  return preg_replace('/^(?:GROUP[[:space:]]*BY[[:space:]]*)+/i', '', trim($str));
980  }
981 
988  public function getDateTimeFormats($table)
989  {
990  return self::$dateTimeFormats;
991  }
992 
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  **************************************/
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 
1058  public function sql_error()
1059  {
1060  return $this->link->error;
1061  }
1062 
1068  public function sql_errno()
1069  {
1070  return $this->link->errno;
1071  }
1072 
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 
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 
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 
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 
1153  public function sql_insert_id()
1154  {
1155  return $this->link->insert_id;
1156  }
1157 
1163  public function sql_affected_rows()
1164  {
1165  return $this->link->affected_rows;
1166  }
1167 
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 
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 
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',
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',
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',
1290  );
1291  }
1292  return $this->link;
1293  }
1294 
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',
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  **************************************/
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 
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 
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 
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 
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 
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 
1473  public function setDatabaseHost($host = 'localhost')
1474  {
1475  $this->disconnectIfConnected();
1476  $this->databaseHost = $host;
1477  }
1478 
1484  public function setDatabasePort($port = 3306)
1485  {
1486  $this->disconnectIfConnected();
1487  $this->databasePort = (int)$port;
1488  }
1489 
1495  public function setDatabaseSocket($socket = null)
1496  {
1497  $this->disconnectIfConnected();
1498  $this->databaseSocket = $socket;
1499  }
1500 
1506  public function setDatabaseName($name)
1507  {
1508  $this->disconnectIfConnected();
1509  $this->databaseName = $name;
1510  }
1511 
1517  public function setDatabaseUsername($username)
1518  {
1519  $this->disconnectIfConnected();
1520  $this->databaseUsername = $username;
1521  }
1522 
1528  public function setDatabasePassword($password)
1529  {
1530  $this->disconnectIfConnected();
1531  $this->databaseUserPassword = $password;
1532  }
1533 
1540  public function setPersistentDatabaseConnection($persistentDatabaseConnection)
1541  {
1542  $this->disconnectIfConnected();
1543  $this->persistentDatabaseConnection = (bool)$persistentDatabaseConnection;
1544  }
1545 
1551  public function setConnectionCompression($connectionCompression)
1552  {
1553  $this->disconnectIfConnected();
1554  $this->connectionCompression = (bool)$connectionCompression;
1555  }
1556 
1562  public function setInitializeCommandsAfterConnect(array $commands)
1563  {
1564  $this->disconnectIfConnected();
1565  $this->initializeCommandsAfterConnect = $commands;
1566  }
1567 
1577  public function setConnectionCharset($connectionCharset = 'utf8')
1578  {
1579  $this->disconnectIfConnected();
1580  $this->connectionCharset = $connectionCharset;
1581  }
1582 
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 
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 
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',
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',
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 
1741  protected function disconnectIfConnected()
1742  {
1743  if ($this->isConnected) {
1744  $this->link->close();
1745  $this->isConnected = false;
1746  }
1747  }
1748 
1754  public function getDatabaseHandle()
1755  {
1756  return $this->link;
1757  }
1758 
1764  public function setDatabaseHandle($handle)
1765  {
1766  $this->link = $handle;
1767  }
1768 
1774  public function getServerVersion()
1775  {
1776  return $this->link->server_info;
1777  }
1778 
1779  /******************************
1780  *
1781  * Debugging
1782  *
1783  ******************************/
1791  public function debug($func, $query = '')
1792  {
1793  $error = $this->sql_error();
1794  if ($error || (int)$this->debugOutput === 2) {
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 
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',
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 
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();
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 
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 }
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 debug($var= '', $header= '', $group= 'Debug')
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)