TYPO3CMS  8
 All Classes Namespaces Files Functions Variables Pages
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 
21 
51 {
57  const AND_Constraint = 'AND';
58 
64  const OR_Constraint = 'OR';
65 
71  public $debugOutput = false;
72 
78  public $debug_lastBuiltQuery = '';
79 
85  public $store_lastBuiltQuery = false;
86 
93  public $explainOutput = 0;
94 
98  protected $databaseHost = '';
99 
103  protected $databasePort = 3306;
104 
108  protected $databaseSocket = null;
109 
113  protected $databaseName = '';
114 
118  protected $databaseUsername = '';
119 
123  protected $databaseUserPassword = '';
124 
129  protected $persistentDatabaseConnection = false;
130 
134  protected $connectionCompression = false;
135 
142  protected $connectionCharset = 'utf8';
143 
148 
152  protected $isConnected = false;
153 
157  protected $link = null;
158 
164  public $default_charset = 'utf8';
165 
169  protected $preProcessHookObjects = [];
170 
174  protected $postProcessHookObjects = [];
175 
181  protected $deprecationWarningThrown = false;
182 
188  public function initialize()
189  {
190  // Intentionally blank as this will be overloaded by DBAL
191  }
192 
193  /************************************
194  *
195  * Query execution
196  *
197  * These functions are the RECOMMENDED DBAL functions for use in your applications
198  * Using these functions will allow the DBAL to use alternative ways of accessing data (contrary to if a query is returned!)
199  * They compile a query AND execute it immediately and then return the result
200  * This principle heightens our ability to create various forms of DBAL of the functions.
201  * Generally: We want to return a result pointer/object, never queries.
202  * Also, having the table name together with the actual query execution allows us to direct the request to other databases.
203  *
204  **************************************/
205 
215  public function exec_INSERTquery($table, $fields_values, $no_quote_fields = false)
216  {
217  $this->logDeprecation();
218  $res = $this->query($this->INSERTquery($table, $fields_values, $no_quote_fields));
219  if ($this->debugOutput) {
220  $this->debug('exec_INSERTquery');
221  }
222  foreach ($this->postProcessHookObjects as $hookObject) {
224  $hookObject->exec_INSERTquery_postProcessAction($table, $fields_values, $no_quote_fields, $this);
225  }
226  return $res;
227  }
228 
238  public function exec_INSERTmultipleRows($table, array $fields, array $rows, $no_quote_fields = false)
239  {
240  $this->logDeprecation();
241  $res = $this->query($this->INSERTmultipleRows($table, $fields, $rows, $no_quote_fields));
242  if ($this->debugOutput) {
243  $this->debug('exec_INSERTmultipleRows');
244  }
245  foreach ($this->postProcessHookObjects as $hookObject) {
247  $hookObject->exec_INSERTmultipleRows_postProcessAction($table, $fields, $rows, $no_quote_fields, $this);
248  }
249  return $res;
250  }
251 
262  public function exec_UPDATEquery($table, $where, $fields_values, $no_quote_fields = false)
263  {
264  $this->logDeprecation();
265  $res = $this->query($this->UPDATEquery($table, $where, $fields_values, $no_quote_fields));
266  if ($this->debugOutput) {
267  $this->debug('exec_UPDATEquery');
268  }
269  foreach ($this->postProcessHookObjects as $hookObject) {
271  $hookObject->exec_UPDATEquery_postProcessAction($table, $where, $fields_values, $no_quote_fields, $this);
272  }
273  return $res;
274  }
275 
283  public function exec_DELETEquery($table, $where)
284  {
285  $this->logDeprecation();
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  $this->logDeprecation();
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  $this->logDeprecation();
347  $queryParts = $this->getSelectMmQueryParts($select, $local_table, $mm_table, $foreign_table, $whereClause, $groupBy, $orderBy, $limit);
348  return $this->exec_SELECT_queryArray($queryParts);
349  }
350 
358  public function exec_SELECT_queryArray($queryParts)
359  {
360  $this->logDeprecation();
361  return $this->exec_SELECTquery($queryParts['SELECT'], $queryParts['FROM'], $queryParts['WHERE'], $queryParts['GROUPBY'], $queryParts['ORDERBY'], $queryParts['LIMIT']);
362  }
363 
378  public function exec_SELECTgetRows($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', $uidIndexField = '')
379  {
380  $this->logDeprecation();
381  $res = $this->exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
382  if ($this->sql_error()) {
383  $this->sql_free_result($res);
384  return null;
385  }
386  $output = [];
387  $firstRecord = true;
388  while ($record = $this->sql_fetch_assoc($res)) {
389  if ($uidIndexField) {
390  if ($firstRecord) {
391  $firstRecord = false;
392  if (!array_key_exists($uidIndexField, $record)) {
393  $this->sql_free_result($res);
394  throw new \InvalidArgumentException('The given $uidIndexField "' . $uidIndexField . '" is not available in the result.', 1432933855);
395  }
396  }
397  $output[$record[$uidIndexField]] = $record;
398  } else {
399  $output[] = $record;
400  }
401  }
402  $this->sql_free_result($res);
403  return $output;
404  }
405 
418  public function exec_SELECTgetSingleRow($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $numIndex = false)
419  {
420  $this->logDeprecation();
421  $res = $this->exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, '1');
422  $output = null;
423  if ($res !== false) {
424  if ($numIndex) {
425  $output = $this->sql_fetch_row($res);
426  } else {
427  $output = $this->sql_fetch_assoc($res);
428  }
429  $this->sql_free_result($res);
430  }
431  return $output;
432  }
433 
442  public function exec_SELECTcountRows($field, $table, $where = '1=1')
443  {
444  $this->logDeprecation();
445  $count = false;
446  $resultSet = $this->exec_SELECTquery('COUNT(' . $field . ')', $table, $where);
447  if ($resultSet !== false) {
448  list($count) = $this->sql_fetch_row($resultSet);
449  $count = (int)$count;
450  $this->sql_free_result($resultSet);
451  }
452  return $count;
453  }
454 
461  public function exec_TRUNCATEquery($table)
462  {
463  $this->logDeprecation();
464  $res = $this->query($this->TRUNCATEquery($table));
465  if ($this->debugOutput) {
466  $this->debug('exec_TRUNCATEquery');
467  }
468  foreach ($this->postProcessHookObjects as $hookObject) {
470  $hookObject->exec_TRUNCATEquery_postProcessAction($table, $this);
471  }
472  return $res;
473  }
474 
482  protected function query($query)
483  {
484  $this->logDeprecation();
485  if (!$this->isConnected) {
486  $this->connectDB();
487  }
488  return $this->link->query($query);
489  }
490 
491  /**************************************
492  *
493  * Query building
494  *
495  **************************************/
504  public function INSERTquery($table, $fields_values, $no_quote_fields = false)
505  {
506  $this->logDeprecation();
507  // Table and fieldnames should be "SQL-injection-safe" when supplied to this
508  // function (contrary to values in the arrays which may be insecure).
509  if (!is_array($fields_values) || empty($fields_values)) {
510  return null;
511  }
512  foreach ($this->preProcessHookObjects as $hookObject) {
513  $hookObject->INSERTquery_preProcessAction($table, $fields_values, $no_quote_fields, $this);
514  }
515  // Quote and escape values
516  $fields_values = $this->fullQuoteArray($fields_values, $table, $no_quote_fields, true);
517  // Build query
518  $query = 'INSERT INTO ' . $table . ' (' . implode(',', array_keys($fields_values)) . ') VALUES ' . '(' . implode(',', $fields_values) . ')';
519  // Return query
520  if ($this->debugOutput || $this->store_lastBuiltQuery) {
521  $this->debug_lastBuiltQuery = $query;
522  }
523  return $query;
524  }
525 
535  public function INSERTmultipleRows($table, array $fields, array $rows, $no_quote_fields = false)
536  {
537  $this->logDeprecation();
538  // Table and fieldnames should be "SQL-injection-safe" when supplied to this
539  // function (contrary to values in the arrays which may be insecure).
540  if (empty($rows)) {
541  return null;
542  }
543  foreach ($this->preProcessHookObjects as $hookObject) {
545  $hookObject->INSERTmultipleRows_preProcessAction($table, $fields, $rows, $no_quote_fields, $this);
546  }
547  // Build query
548  $query = 'INSERT INTO ' . $table . ' (' . implode(', ', $fields) . ') VALUES ';
549  $rowSQL = [];
550  foreach ($rows as $row) {
551  // Quote and escape values
552  $row = $this->fullQuoteArray($row, $table, $no_quote_fields);
553  $rowSQL[] = '(' . implode(', ', $row) . ')';
554  }
555  $query .= implode(', ', $rowSQL);
556  // Return query
557  if ($this->debugOutput || $this->store_lastBuiltQuery) {
558  $this->debug_lastBuiltQuery = $query;
559  }
560  return $query;
561  }
562 
574  public function UPDATEquery($table, $where, $fields_values, $no_quote_fields = false)
575  {
576  $this->logDeprecation();
577  // Table and fieldnames should be "SQL-injection-safe" when supplied to this
578  // function (contrary to values in the arrays which may be insecure).
579  if (is_string($where)) {
580  foreach ($this->preProcessHookObjects as $hookObject) {
582  $hookObject->UPDATEquery_preProcessAction($table, $where, $fields_values, $no_quote_fields, $this);
583  }
584  $fields = [];
585  if (is_array($fields_values) && !empty($fields_values)) {
586  // Quote and escape values
587  $nArr = $this->fullQuoteArray($fields_values, $table, $no_quote_fields, true);
588  foreach ($nArr as $k => $v) {
589  $fields[] = $k . '=' . $v;
590  }
591  }
592  // Build query
593  $query = 'UPDATE ' . $table . ' SET ' . implode(',', $fields) . ((string)$where !== '' ? ' WHERE ' . $where : '');
594  if ($this->debugOutput || $this->store_lastBuiltQuery) {
595  $this->debug_lastBuiltQuery = $query;
596  }
597  return $query;
598  } else {
599  throw new \InvalidArgumentException('TYPO3 Fatal Error: "Where" clause argument for UPDATE query was not a string in $this->UPDATEquery() !', 1270853880);
600  }
601  }
602 
611  public function DELETEquery($table, $where)
612  {
613  $this->logDeprecation();
614  if (is_string($where)) {
615  foreach ($this->preProcessHookObjects as $hookObject) {
617  $hookObject->DELETEquery_preProcessAction($table, $where, $this);
618  }
619  // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
620  $query = 'DELETE FROM ' . $table . ((string)$where !== '' ? ' WHERE ' . $where : '');
621  if ($this->debugOutput || $this->store_lastBuiltQuery) {
622  $this->debug_lastBuiltQuery = $query;
623  }
624  return $query;
625  } else {
626  throw new \InvalidArgumentException('TYPO3 Fatal Error: "Where" clause argument for DELETE query was not a string in $this->DELETEquery() !', 1270853881);
627  }
628  }
629 
641  public function SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '')
642  {
643  $this->logDeprecation();
644  foreach ($this->preProcessHookObjects as $hookObject) {
646  $hookObject->SELECTquery_preProcessAction($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit, $this);
647  }
648  // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
649  // Build basic query
650  $query = 'SELECT ' . $select_fields . ' FROM ' . $from_table . ((string)$where_clause !== '' ? ' WHERE ' . $where_clause : '');
651  // Group by
652  $query .= (string)$groupBy !== '' ? ' GROUP BY ' . $groupBy : '';
653  // Order by
654  $query .= (string)$orderBy !== '' ? ' ORDER BY ' . $orderBy : '';
655  // Group by
656  $query .= (string)$limit !== '' ? ' LIMIT ' . $limit : '';
657  // Return query
658  if ($this->debugOutput || $this->store_lastBuiltQuery) {
659  $this->debug_lastBuiltQuery = $query;
660  }
661  return $query;
662  }
663 
673  public function SELECTsubquery($select_fields, $from_table, $where_clause)
674  {
675  $this->logDeprecation();
676  // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
677  // Build basic query:
678  $query = 'SELECT ' . $select_fields . ' FROM ' . $from_table . ((string)$where_clause !== '' ? ' WHERE ' . $where_clause : '');
679  // Return query
680  if ($this->debugOutput || $this->store_lastBuiltQuery) {
681  $this->debug_lastBuiltQuery = $query;
682  }
683  return $query;
684  }
685 
703  public function SELECT_mm_query($select, $local_table, $mm_table, $foreign_table, $whereClause = '', $groupBy = '', $orderBy = '', $limit = '')
704  {
705  $this->logDeprecation();
706  $queryParts = $this->getSelectMmQueryParts($select, $local_table, $mm_table, $foreign_table, $whereClause, $groupBy, $orderBy, $limit);
707  return $this->SELECTquery($queryParts['SELECT'], $queryParts['FROM'], $queryParts['WHERE'], $queryParts['GROUPBY'], $queryParts['ORDERBY'], $queryParts['LIMIT']);
708  }
709 
716  public function TRUNCATEquery($table)
717  {
718  $this->logDeprecation();
719  foreach ($this->preProcessHookObjects as $hookObject) {
721  $hookObject->TRUNCATEquery_preProcessAction($table, $this);
722  }
723  // Table should be "SQL-injection-safe" when supplied to this function
724  // Build basic query:
725  $query = 'TRUNCATE TABLE ' . $table;
726  // Return query:
727  if ($this->debugOutput || $this->store_lastBuiltQuery) {
728  $this->debug_lastBuiltQuery = $query;
729  }
730  return $query;
731  }
732 
748  public function listQuery($field, $value, $table)
749  {
750  $this->logDeprecation();
751  $value = (string)$value;
752  if (strpos($value, ',') !== false) {
753  throw new \InvalidArgumentException('$value must not contain a comma (,) in $this->listQuery() !', 1294585862);
754  }
755  $pattern = $this->quoteStr($value, $table);
756  $where = 'FIND_IN_SET(\'' . $pattern . '\',' . $field . ')';
757  return $where;
758  }
759 
769  public function searchQuery($searchWords, $fields, $table, $constraint = self::AND_Constraint)
770  {
771  $this->logDeprecation();
772  switch ($constraint) {
773  case self::OR_Constraint:
774  $constraint = 'OR';
775  break;
776  default:
777  $constraint = 'AND';
778  }
779 
780  $queryParts = [];
781  foreach ($searchWords as $sw) {
782  $like = ' LIKE \'%' . $this->quoteStr($this->escapeStrForLike($sw, $table), $table) . '%\'';
783  $queryParts[] = $table . '.' . implode(($like . ' OR ' . $table . '.'), $fields) . $like;
784  }
785  $query = '(' . implode(') ' . $constraint . ' (', $queryParts) . ')';
786 
787  return $query;
788  }
789 
790  /**************************************
791  *
792  * Prepared Query Support
793  *
794  **************************************/
807  public function prepare_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', array $input_parameters = [])
808  {
809  $this->logDeprecation();
810  $query = $this->SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
812  $preparedStatement = GeneralUtility::makeInstance(\TYPO3\CMS\Core\Database\PreparedStatement::class, $query, $from_table, []);
813  // Bind values to parameters
814  foreach ($input_parameters as $key => $value) {
815  $preparedStatement->bindValue($key, $value, PreparedStatement::PARAM_AUTOTYPE);
816  }
817  // Return prepared statement
818  return $preparedStatement;
819  }
820 
828  public function prepare_SELECTqueryArray(array $queryParts, array $input_parameters = [])
829  {
830  $this->logDeprecation();
831  return $this->prepare_SELECTquery($queryParts['SELECT'], $queryParts['FROM'], $queryParts['WHERE'], $queryParts['GROUPBY'], $queryParts['ORDERBY'], $queryParts['LIMIT'], $input_parameters);
832  }
833 
842  public function prepare_PREPAREDquery($query, array $queryComponents)
843  {
844  $this->logDeprecation();
845  if (!$this->isConnected) {
846  $this->connectDB();
847  }
848  $stmt = $this->link->stmt_init();
849  $success = $stmt->prepare($query);
850  if ($this->debugOutput) {
851  $this->debug('stmt_execute', $query);
852  }
853  return $success ? $stmt : null;
854  }
855 
856  /**************************************
857  *
858  * Various helper functions
859  *
860  * Functions recommended to be used for
861  * - escaping values,
862  * - cleaning lists of values,
863  * - stripping of excess ORDER BY/GROUP BY keywords
864  *
865  **************************************/
875  public function fullQuoteStr($str, $table, $allowNull = false)
876  {
877  $this->logDeprecation();
878  if (!$this->isConnected) {
879  $this->connectDB();
880  }
881  if ($allowNull && $str === null) {
882  return 'NULL';
883  }
884  if (is_bool($str)) {
885  $str = (int)$str;
886  }
887 
888  return '\'' . $this->link->real_escape_string($str) . '\'';
889  }
890 
901  public function fullQuoteArray($arr, $table, $noQuote = false, $allowNull = false)
902  {
903  $this->logDeprecation();
904  if (is_string($noQuote)) {
905  $noQuote = explode(',', $noQuote);
906  } elseif (!is_array($noQuote)) {
907  $noQuote = (bool)$noQuote;
908  }
909  if ($noQuote === true) {
910  return $arr;
911  }
912  foreach ($arr as $k => $v) {
913  if ($noQuote === false || !in_array($k, $noQuote)) {
914  $arr[$k] = $this->fullQuoteStr($v, $table, $allowNull);
915  }
916  }
917  return $arr;
918  }
919 
930  public function quoteStr($str, $table)
931  {
932  $this->logDeprecation();
933  if (!$this->isConnected) {
934  $this->connectDB();
935  }
936  return $this->link->real_escape_string($str);
937  }
938 
947  public function escapeStrForLike($str, $table)
948  {
949  $this->logDeprecation();
950  return addcslashes($str, '_%');
951  }
952 
961  public function cleanIntArray($arr)
962  {
963  $this->logDeprecation();
964  return array_map('intval', $arr);
965  }
966 
975  public function cleanIntList($list)
976  {
977  $this->logDeprecation();
978  return implode(',', GeneralUtility::intExplode(',', $list));
979  }
980 
990  public function stripOrderBy($str)
991  {
992  $this->logDeprecation();
993  return preg_replace('/^(?:ORDER[[:space:]]*BY[[:space:]]*)+/i', '', trim($str));
994  }
995 
1005  public function stripGroupBy($str)
1006  {
1007  $this->logDeprecation();
1008  return preg_replace('/^(?:GROUP[[:space:]]*BY[[:space:]]*)+/i', '', trim($str));
1009  }
1010 
1017  public function getDateTimeFormats($table)
1018  {
1019  $this->logDeprecation();
1021  }
1022 
1039  protected function getSelectMmQueryParts($select, $local_table, $mm_table, $foreign_table, $whereClause = '', $groupBy = '', $orderBy = '', $limit = '')
1040  {
1041  $foreign_table_as = $foreign_table == $local_table ? $foreign_table . StringUtility::getUniqueId('_join') : '';
1042  $mmWhere = $local_table ? $local_table . '.uid=' . $mm_table . '.uid_local' : '';
1043  $mmWhere .= ($local_table and $foreign_table) ? ' AND ' : '';
1044  $tables = ($local_table ? $local_table . ',' : '') . $mm_table;
1045  if ($foreign_table) {
1046  $mmWhere .= ($foreign_table_as ?: $foreign_table) . '.uid=' . $mm_table . '.uid_foreign';
1047  $tables .= ',' . $foreign_table . ($foreign_table_as ? ' AS ' . $foreign_table_as : '');
1048  }
1049  return [
1050  'SELECT' => $select,
1051  'FROM' => $tables,
1052  'WHERE' => $mmWhere . ' ' . $whereClause,
1053  'GROUPBY' => $groupBy,
1054  'ORDERBY' => $orderBy,
1055  'LIMIT' => $limit
1056  ];
1057  }
1058 
1059  /**************************************
1060  *
1061  * MySQL(i) wrapper functions
1062  * (For use in your applications)
1063  *
1064  **************************************/
1074  public function sql_query($query)
1075  {
1076  $this->logDeprecation();
1077  $res = $this->query($query);
1078  if ($this->debugOutput) {
1079  $this->debug('sql_query', $query);
1080  }
1081  return $res;
1082  }
1083 
1089  public function sql_error()
1090  {
1091  $this->logDeprecation();
1092  return $this->link->error;
1093  }
1094 
1100  public function sql_errno()
1101  {
1102  $this->logDeprecation();
1103  return $this->link->errno;
1104  }
1105 
1112  public function sql_num_rows($res)
1113  {
1114  $this->logDeprecation();
1115  if ($this->debug_check_recordset($res)) {
1116  return $res->num_rows;
1117  } else {
1118  return false;
1119  }
1120  }
1121 
1129  public function sql_fetch_assoc($res)
1130  {
1131  $this->logDeprecation();
1132  if ($this->debug_check_recordset($res)) {
1133  $result = $res->fetch_assoc();
1134  if ($result === null) {
1135  // Needed for compatibility
1136  $result = false;
1137  }
1138  return $result;
1139  } else {
1140  return false;
1141  }
1142  }
1143 
1152  public function sql_fetch_row($res)
1153  {
1154  $this->logDeprecation();
1155  if ($this->debug_check_recordset($res)) {
1156  $result = $res->fetch_row();
1157  if ($result === null) {
1158  // Needed for compatibility
1159  $result = false;
1160  }
1161  return $result;
1162  } else {
1163  return false;
1164  }
1165  }
1166 
1174  public function sql_free_result($res)
1175  {
1176  $this->logDeprecation();
1177  if ($this->debug_check_recordset($res) && is_object($res)) {
1178  $res->free();
1179  return true;
1180  } else {
1181  return false;
1182  }
1183  }
1184 
1190  public function sql_insert_id()
1191  {
1192  $this->logDeprecation();
1193  return $this->link->insert_id;
1194  }
1195 
1201  public function sql_affected_rows()
1202  {
1203  return $this->link->affected_rows;
1204  }
1205 
1213  public function sql_data_seek($res, $seek)
1214  {
1215  $this->logDeprecation();
1216  if ($this->debug_check_recordset($res)) {
1217  return $res->data_seek($seek);
1218  } else {
1219  return false;
1220  }
1221  }
1222 
1231  public function sql_field_type($res, $pointer)
1232  {
1233  // mysql_field_type compatibility map
1234  // taken from: http://www.php.net/manual/en/mysqli-result.fetch-field-direct.php#89117
1235  // Constant numbers see http://php.net/manual/en/mysqli.constants.php
1236  $mysql_data_type_hash = [
1237  1=>'tinyint',
1238  2=>'smallint',
1239  3=>'int',
1240  4=>'float',
1241  5=>'double',
1242  7=>'timestamp',
1243  8=>'bigint',
1244  9=>'mediumint',
1245  10=>'date',
1246  11=>'time',
1247  12=>'datetime',
1248  13=>'year',
1249  16=>'bit',
1250  //252 is currently mapped to all text and blob types (MySQL 5.0.51a)
1251  253=>'varchar',
1252  254=>'char',
1253  246=>'decimal'
1254  ];
1255  if ($this->debug_check_recordset($res)) {
1256  $metaInfo = $res->fetch_field_direct($pointer);
1257  if ($metaInfo === false) {
1258  return false;
1259  }
1260  return $mysql_data_type_hash[$metaInfo->type];
1261  } else {
1262  return false;
1263  }
1264  }
1265 
1272  public function sql_pconnect()
1273  {
1274  if ($this->isConnected) {
1275  return $this->link;
1276  }
1277 
1278  if (!extension_loaded('mysqli')) {
1279  throw new \RuntimeException(
1280  'Database Error: PHP mysqli extension not loaded. This is a must have for TYPO3 CMS!',
1281  1271492607
1282  );
1283  }
1284 
1285  $host = $this->persistentDatabaseConnection
1286  ? 'p:' . $this->databaseHost
1287  : $this->databaseHost;
1288 
1289  // We are not using the TYPO3 CMS shim here as the database parameters in this class
1290  // are settable externally. This requires building the connection parameter array
1291  // just in time when establishing the connection.
1292  $connection = \Doctrine\DBAL\DriverManager::getConnection([
1293  'driver' => 'mysqli',
1294  'wrapperClass' => Connection::class,
1295  'host' => $host,
1296  'port' => (int)$this->databasePort,
1297  'unix_socket' => $this->databaseSocket,
1298  'user' => $this->databaseUsername,
1299  'password' => $this->databaseUserPassword,
1300  'charset' => $this->connectionCharset,
1301  ]);
1302 
1303  // Mimic the previous behavior of returning false on connection errors
1304  try {
1306  $mysqliConnection = $connection->getWrappedConnection();
1307  $this->link = $mysqliConnection->getWrappedResourceHandle();
1308  } catch (\Doctrine\DBAL\Exception\ConnectionException $exception) {
1309  return false;
1310  }
1311 
1312  if ($connection->isConnected()) {
1313  $this->isConnected = true;
1314 
1315  foreach ($this->initializeCommandsAfterConnect as $command) {
1316  if ($this->query($command) === false) {
1317  GeneralUtility::sysLog(
1318  'Could not initialize DB connection with query "' . $command . '": ' . $this->sql_error(),
1319  'core',
1321  );
1322  }
1323  }
1324  $this->checkConnectionCharset();
1325  } else {
1326  // @todo This should raise an exception. Would be useful especially to work during installation.
1327  $error_msg = $this->link->connect_error;
1328  $this->link = null;
1329  GeneralUtility::sysLog(
1330  'Could not connect to MySQL server ' . $host . ' with user ' . $this->databaseUsername . ': '
1331  . $error_msg,
1332  'core',
1334  );
1335  }
1336 
1337  return $this->link;
1338  }
1339 
1345  public function sql_select_db()
1346  {
1347  if (!$this->isConnected) {
1348  $this->connectDB();
1349  }
1350 
1351  $ret = $this->link->select_db($this->databaseName);
1352  if (!$ret) {
1353  GeneralUtility::sysLog(
1354  'Could not select MySQL database ' . $this->databaseName . ': ' . $this->sql_error(),
1355  'core',
1357  );
1358  }
1359  return $ret;
1360  }
1361 
1362  /**************************************
1363  *
1364  * SQL admin functions
1365  * (For use in the Install Tool and Extension Manager)
1366  *
1367  **************************************/
1377  public function admin_get_dbs()
1378  {
1379  $this->logDeprecation();
1380  $dbArr = [];
1381  $db_list = $this->query('SELECT SCHEMA_NAME FROM information_schema.SCHEMATA');
1382  if ($db_list === false) {
1383  throw new \RuntimeException(
1384  'MySQL Error: Cannot get tablenames: "' . $this->sql_error() . '"!',
1385  1378457171
1386  );
1387  } else {
1388  while ($row = $db_list->fetch_object()) {
1389  try {
1390  $this->setDatabaseName($row->SCHEMA_NAME);
1391  if ($this->sql_select_db()) {
1392  $dbArr[] = $row->SCHEMA_NAME;
1393  }
1394  } catch (\RuntimeException $exception) {
1395  // The exception happens if we cannot connect to the database
1396  // (usually due to missing permissions). This is ok here.
1397  // We catch the exception, skip the database and continue.
1398  }
1399  }
1400  }
1401  return $dbArr;
1402  }
1403 
1411  public function admin_get_tables()
1412  {
1413  $this->logDeprecation();
1414  $whichTables = [];
1415  $tables_result = $this->query('SHOW TABLE STATUS FROM `' . $this->databaseName . '`');
1416  if ($tables_result !== false) {
1417  while ($theTable = $tables_result->fetch_assoc()) {
1418  $whichTables[$theTable['Name']] = $theTable;
1419  }
1420  $tables_result->free();
1421  }
1422  return $whichTables;
1423  }
1424 
1436  public function admin_get_fields($tableName)
1437  {
1438  $this->logDeprecation();
1439  $output = [];
1440  $columns_res = $this->query('SHOW FULL COLUMNS FROM `' . $tableName . '`');
1441  if ($columns_res !== false) {
1442  while ($fieldRow = $columns_res->fetch_assoc()) {
1443  $output[$fieldRow['Field']] = $fieldRow;
1444  }
1445  $columns_res->free();
1446  }
1447  return $output;
1448  }
1449 
1457  public function admin_get_keys($tableName)
1458  {
1459  $this->logDeprecation();
1460  $output = [];
1461  $keyRes = $this->query('SHOW KEYS FROM `' . $tableName . '`');
1462  if ($keyRes !== false) {
1463  while ($keyRow = $keyRes->fetch_assoc()) {
1464  $output[] = $keyRow;
1465  }
1466  $keyRes->free();
1467  }
1468  return $output;
1469  }
1470 
1483  public function admin_get_charsets()
1484  {
1485  $this->logDeprecation();
1486  $output = [];
1487  $columns_res = $this->query('SHOW CHARACTER SET');
1488  if ($columns_res !== false) {
1489  while ($row = $columns_res->fetch_assoc()) {
1490  $output[$row['Charset']] = $row;
1491  }
1492  $columns_res->free();
1493  }
1494  return $output;
1495  }
1496 
1503  public function admin_query($query)
1504  {
1505  $this->logDeprecation();
1506  $res = $this->query($query);
1507  if ($this->debugOutput) {
1508  $this->debug('admin_query', $query);
1509  }
1510  return $res;
1511  }
1512 
1513  /******************************
1514  *
1515  * Connect handling
1516  *
1517  ******************************/
1518 
1524  public function setDatabaseHost($host = 'localhost')
1525  {
1526  $this->disconnectIfConnected();
1527  $this->databaseHost = $host;
1528  }
1529 
1535  public function setDatabasePort($port = 3306)
1536  {
1537  $this->disconnectIfConnected();
1538  $this->databasePort = (int)$port;
1539  }
1540 
1546  public function setDatabaseSocket($socket = null)
1547  {
1548  $this->disconnectIfConnected();
1549  $this->databaseSocket = $socket;
1550  }
1551 
1557  public function setDatabaseName($name)
1558  {
1559  $this->disconnectIfConnected();
1560  $this->databaseName = $name;
1561  }
1562 
1568  public function setDatabaseUsername($username)
1569  {
1570  $this->disconnectIfConnected();
1571  $this->databaseUsername = $username;
1572  }
1573 
1579  public function setDatabasePassword($password)
1580  {
1581  $this->disconnectIfConnected();
1582  $this->databaseUserPassword = $password;
1583  }
1584 
1591  public function setPersistentDatabaseConnection($persistentDatabaseConnection)
1592  {
1593  $this->disconnectIfConnected();
1594  $this->persistentDatabaseConnection = (bool)$persistentDatabaseConnection;
1595  }
1596 
1602  public function setConnectionCompression($connectionCompression)
1603  {
1604  $this->disconnectIfConnected();
1605  $this->connectionCompression = (bool)$connectionCompression;
1606  }
1607 
1613  public function setInitializeCommandsAfterConnect(array $commands)
1614  {
1615  $this->disconnectIfConnected();
1616  $this->initializeCommandsAfterConnect = $commands;
1617  }
1618 
1628  public function setConnectionCharset($connectionCharset = 'utf8')
1629  {
1630  $this->disconnectIfConnected();
1631  $this->connectionCharset = $connectionCharset;
1632  }
1633 
1641  public function connectDB()
1642  {
1643  $this->logDeprecation();
1644  // Early return if connected already
1645  if ($this->isConnected) {
1646  return;
1647  }
1648 
1649  if (!$this->databaseName) {
1650  throw new \RuntimeException(
1651  'TYPO3 Fatal Error: No database selected!',
1652  1270853882
1653  );
1654  }
1655 
1656  if ($this->sql_pconnect()) {
1657  if (!$this->sql_select_db()) {
1658  throw new \RuntimeException(
1659  'TYPO3 Fatal Error: Cannot connect to the current database, "' . $this->databaseName . '"!',
1660  1270853883
1661  );
1662  }
1663  } else {
1664  throw new \RuntimeException(
1665  'TYPO3 Fatal Error: The current username, password or host was not accepted when the connection to the database was attempted to be established!',
1666  1270853884
1667  );
1668  }
1669 
1670  // Prepare user defined objects (if any) for hooks which extend query methods
1671  $this->preProcessHookObjects = [];
1672  $this->postProcessHookObjects = [];
1673  if (is_array($GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_db.php']['queryProcessors'])) {
1674  foreach ($GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_db.php']['queryProcessors'] as $classRef) {
1675  $hookObject = GeneralUtility::getUserObj($classRef);
1676  if (!(
1677  $hookObject instanceof PreProcessQueryHookInterface
1678  || $hookObject instanceof PostProcessQueryHookInterface
1679  )) {
1680  throw new \UnexpectedValueException(
1681  '$hookObject must either implement interface TYPO3\\CMS\\Core\\Database\\PreProcessQueryHookInterface or interface TYPO3\\CMS\\Core\\Database\\PostProcessQueryHookInterface',
1682  1299158548
1683  );
1684  }
1685  if ($hookObject instanceof PreProcessQueryHookInterface) {
1686  $this->preProcessHookObjects[] = $hookObject;
1687  }
1688  if ($hookObject instanceof PostProcessQueryHookInterface) {
1689  $this->postProcessHookObjects[] = $hookObject;
1690  }
1691  }
1692  }
1693  }
1694 
1700  public function isConnected()
1701  {
1702  // We think we're still connected
1703  if ($this->isConnected) {
1704  // Check if this is really the case or if the database server has gone away for some reason
1705  // Using mysqlnd ping() does not reconnect (which we would not want anyway since charset etc would not be reinitialized that way)
1706  $this->isConnected = $this->link->ping();
1707  }
1708  return $this->isConnected;
1709  }
1710 
1725  protected function checkConnectionCharset()
1726  {
1727  $sessionResult = $this->sql_query('SHOW SESSION VARIABLES LIKE \'character_set%\'');
1728 
1729  if ($sessionResult === false) {
1730  GeneralUtility::sysLog(
1731  'Error while retrieving the current charset session variables from the database: ' . $this->sql_error(),
1732  'core',
1734  );
1735  throw new \RuntimeException(
1736  'TYPO3 Fatal Error: Could not determine the current charset of the database.',
1737  1381847136
1738  );
1739  }
1740 
1741  $charsetVariables = [];
1742  while (($row = $this->sql_fetch_row($sessionResult)) !== false) {
1743  $variableName = $row[0];
1744  $variableValue = $row[1];
1745  $charsetVariables[$variableName] = $variableValue;
1746  }
1747  $this->sql_free_result($sessionResult);
1748 
1749  // These variables are set with the "Set names" command which was
1750  // used in the past. This is why we check them.
1751  $charsetRequiredVariables = [
1752  'character_set_client',
1753  'character_set_results',
1754  'character_set_connection',
1755  ];
1756 
1757  $hasValidCharset = true;
1758  foreach ($charsetRequiredVariables as $variableName) {
1759  if (empty($charsetVariables[$variableName])) {
1760  GeneralUtility::sysLog(
1761  'A required session variable is missing in the current MySQL connection: ' . $variableName,
1762  'core',
1764  );
1765  throw new \RuntimeException(
1766  'TYPO3 Fatal Error: Could not determine the value of the database session variable: ' . $variableName,
1767  1381847779
1768  );
1769  }
1770 
1771  if ($charsetVariables[$variableName] !== $this->connectionCharset) {
1772  $hasValidCharset = false;
1773  break;
1774  }
1775  }
1776 
1777  if (!$hasValidCharset) {
1778  throw new \RuntimeException(
1779  'It looks like the character set ' . $this->connectionCharset . ' is not used for this connection even though it is configured as connection charset. ' .
1780  'This TYPO3 installation is using the $GLOBALS[\'TYPO3_CONF_VARS\'][\'SYS\'][\'setDBinit\'] property with the following value: "' .
1781  $GLOBALS['TYPO3_CONF_VARS']['SYS']['setDBinit'] . '". Please make sure that this command does not overwrite the configured charset. ' .
1782  'Please note that for the TYPO3 database everything other than utf8 is unsupported since version 4.7.',
1783  1389697515
1784  );
1785  }
1786  }
1787 
1793  protected function disconnectIfConnected()
1794  {
1795  if ($this->isConnected) {
1796  $this->link->close();
1797  $this->isConnected = false;
1798  }
1799  }
1800 
1806  public function getDatabaseHandle()
1807  {
1808  $this->logDeprecation();
1809  return $this->link;
1810  }
1811 
1817  public function setDatabaseHandle($handle)
1818  {
1819  $this->link = $handle;
1820  }
1821 
1827  public function getServerVersion()
1828  {
1829  $this->logDeprecation();
1830  return $this->link->server_info;
1831  }
1832 
1833  /******************************
1834  *
1835  * Debugging
1836  *
1837  ******************************/
1845  public function debug($func, $query = '')
1846  {
1847  $this->logDeprecation();
1848  $error = $this->sql_error();
1849  if ($error || (int)$this->debugOutput === 2) {
1851  [
1852  'caller' => \TYPO3\CMS\Core\Database\DatabaseConnection::class . '::' . $func,
1853  'ERROR' => $error,
1854  'lastBuiltQuery' => $query ? $query : $this->debug_lastBuiltQuery,
1855  'debug_backtrace' => \TYPO3\CMS\Core\Utility\DebugUtility::debugTrail()
1856  ],
1857  $func,
1858  is_object($GLOBALS['error']) && @is_callable([$GLOBALS['error'], 'debug'])
1859  ? ''
1860  : 'DB Error'
1861  );
1862  }
1863  }
1864 
1871  public function debug_check_recordset($res)
1872  {
1873  $this->logDeprecation();
1874  if ($res !== false && $res !== null) {
1875  return true;
1876  }
1877  $trace = debug_backtrace(0);
1878  array_shift($trace);
1879  $msg = 'Invalid database result detected: function TYPO3\\CMS\\Core\\Database\\DatabaseConnection->'
1880  . $trace[0]['function'] . ' called from file ' . substr($trace[0]['file'], (strlen(PATH_site) + 2))
1881  . ' in line ' . $trace[0]['line'] . '.';
1882  GeneralUtility::sysLog(
1883  $msg . ' Use a devLog extension to get more details.',
1884  'core',
1886  );
1887  // Send to devLog if enabled
1888  if (TYPO3_DLOG) {
1889  $debugLogData = [
1890  'SQL Error' => $this->sql_error(),
1891  'Backtrace' => $trace
1892  ];
1893  if ($this->debug_lastBuiltQuery) {
1894  $debugLogData = ['SQL Query' => $this->debug_lastBuiltQuery] + $debugLogData;
1895  }
1896  GeneralUtility::devLog($msg, 'Core/t3lib_db', 3, $debugLogData);
1897  }
1898  return false;
1899  }
1900 
1913  protected function explain($query, $from_table, $row_count)
1914  {
1915  $debugAllowedForIp = GeneralUtility::cmpIP(
1916  GeneralUtility::getIndpEnv('REMOTE_ADDR'),
1917  $GLOBALS['TYPO3_CONF_VARS']['SYS']['devIPmask']
1918  );
1919  if (
1920  (int)$this->explainOutput == 1
1921  || ((int)$this->explainOutput == 2 && $debugAllowedForIp)
1922  ) {
1923  // Raw HTML output
1924  $explainMode = 1;
1925  } elseif ((int)$this->explainOutput == 3) {
1926  // Embed the output into the TS admin panel
1927  $explainMode = 2;
1928  } else {
1929  return false;
1930  }
1931  $error = $this->sql_error();
1933  $explain_tables = [];
1934  $explain_output = [];
1935  $res = $this->sql_query('EXPLAIN ' . $query, $this->link);
1936  if (is_a($res, '\\mysqli_result')) {
1937  while ($tempRow = $this->sql_fetch_assoc($res)) {
1938  $explain_output[] = $tempRow;
1939  $explain_tables[] = $tempRow['table'];
1940  }
1941  $this->sql_free_result($res);
1942  }
1943  $indices_output = [];
1944  // Notice: Rows are skipped if there is only one result, or if no conditions are set
1945  if ($explain_output[0]['rows'] > 1 || $explain_output[0]['type'] === 'ALL') {
1946  // Only enable output if it's really useful
1947  $debug = true;
1948  foreach ($explain_tables as $table) {
1949  $tableRes = $this->sql_query('SHOW TABLE STATUS LIKE \'' . $table . '\'');
1950  $isTable = $this->sql_num_rows($tableRes);
1951  if ($isTable) {
1952  $res = $this->sql_query('SHOW INDEX FROM ' . $table, $this->link);
1953  if (is_a($res, '\\mysqli_result')) {
1954  while ($tempRow = $this->sql_fetch_assoc($res)) {
1955  $indices_output[] = $tempRow;
1956  }
1957  $this->sql_free_result($res);
1958  }
1959  }
1960  $this->sql_free_result($tableRes);
1961  }
1962  } else {
1963  $debug = false;
1964  }
1965  if ($debug) {
1966  if ($explainMode) {
1967  $data = [];
1968  $data['query'] = $query;
1969  $data['trail'] = $trail;
1970  $data['row_count'] = $row_count;
1971  if ($error) {
1972  $data['error'] = $error;
1973  }
1974  if (!empty($explain_output)) {
1975  $data['explain'] = $explain_output;
1976  }
1977  if (!empty($indices_output)) {
1978  $data['indices'] = $indices_output;
1979  }
1980  if ($explainMode == 1) {
1981  \TYPO3\CMS\Core\Utility\DebugUtility::debug($data, 'Tables: ' . $from_table, 'DB SQL EXPLAIN');
1982  } elseif ($explainMode == 2) {
1984  $timeTracker = GeneralUtility::makeInstance(TimeTracker::class);
1985  $timeTracker->setTSselectQuery($data);
1986  }
1987  }
1988  return true;
1989  }
1990  return false;
1991  }
1992 
1998  public function __sleep()
1999  {
2000  $this->disconnectIfConnected();
2001  return [
2002  'debugOutput',
2003  'explainOutput',
2004  'databaseHost',
2005  'databasePort',
2006  'databaseSocket',
2007  'databaseName',
2008  'databaseUsername',
2009  'databaseUserPassword',
2010  'persistentDatabaseConnection',
2011  'connectionCompression',
2012  'initializeCommandsAfterConnect',
2013  'default_charset',
2014  ];
2015  }
2016 
2020  protected function logDeprecation()
2021  {
2022  if (!$this->deprecationWarningThrown) {
2023  $this->deprecationWarningThrown = true;
2024  GeneralUtility::deprecationLog('DatabaseConnection a.k.a. $["TYPO3_DB"] has been marked as deprecated in'
2025  . ' TYPO3 v8 and will be removed in TYPO3 v9. Please use the newly available ConnectionPool and QueryBuilder'
2026  . ' classes.');
2027  }
2028  }
2029 }
setPersistentDatabaseConnection($persistentDatabaseConnection)
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)
prepare_PREPAREDquery($query, array $queryComponents)
exec_SELECT_mm_query($select, $local_table, $mm_table, $foreign_table, $whereClause= '', $groupBy= '', $orderBy= '', $limit= '')
prepare_SELECTqueryArray(array $queryParts, array $input_parameters=[])
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= '')
exec_SELECTcountRows($field, $table, $where= '1=1')
fullQuoteStr($str, $table, $allowNull=false)
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)
setConnectionCharset($connectionCharset= 'utf8')
static intExplode($delimiter, $string, $removeEmptyValues=false, $limit=0)
static devLog($msg, $extKey, $severity=0, $dataVar=false)