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 = false;
885  }
886  foreach ($arr as $k => $v) {
887  if ($noQuote === false || !in_array($k, $noQuote)) {
888  $arr[$k] = $this->fullQuoteStr($v, $table, $allowNull);
889  }
890  }
891  return $arr;
892  }
893 
904  public function quoteStr($str, $table)
905  {
906  if (!$this->isConnected) {
907  $this->connectDB();
908  }
909  return $this->link->real_escape_string($str);
910  }
911 
920  public function escapeStrForLike($str, $table)
921  {
922  return addcslashes($str, '_%');
923  }
924 
933  public function cleanIntArray($arr)
934  {
935  return array_map('intval', $arr);
936  }
937 
946  public function cleanIntList($list)
947  {
948  return implode(',', GeneralUtility::intExplode(',', $list));
949  }
950 
960  public function stripOrderBy($str)
961  {
962  return preg_replace('/^(?:ORDER[[:space:]]*BY[[:space:]]*)+/i', '', trim($str));
963  }
964 
974  public function stripGroupBy($str)
975  {
976  return preg_replace('/^(?:GROUP[[:space:]]*BY[[:space:]]*)+/i', '', trim($str));
977  }
978 
987  public function splitGroupOrderLimit($str)
988  {
990  // Prepending a space to make sure "[[:space:]]+" will find a space there
991  // for the first element.
992  $str = ' ' . $str;
993  // Init output array:
994  $wgolParts = array(
995  'WHERE' => '',
996  'GROUPBY' => '',
997  'ORDERBY' => '',
998  'LIMIT' => ''
999  );
1000  // Find LIMIT
1001  $reg = array();
1002  if (preg_match('/^(.*)[[:space:]]+LIMIT[[:space:]]+([[:alnum:][:space:],._]+)$/i', $str, $reg)) {
1003  $wgolParts['LIMIT'] = trim($reg[2]);
1004  $str = $reg[1];
1005  }
1006  // Find ORDER BY
1007  $reg = array();
1008  if (preg_match('/^(.*)[[:space:]]+ORDER[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._]+)$/i', $str, $reg)) {
1009  $wgolParts['ORDERBY'] = trim($reg[2]);
1010  $str = $reg[1];
1011  }
1012  // Find GROUP BY
1013  $reg = array();
1014  if (preg_match('/^(.*)[[:space:]]+GROUP[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._]+)$/i', $str, $reg)) {
1015  $wgolParts['GROUPBY'] = trim($reg[2]);
1016  $str = $reg[1];
1017  }
1018  // Rest is assumed to be "WHERE" clause
1019  $wgolParts['WHERE'] = $str;
1020  return $wgolParts;
1021  }
1022 
1029  public function getDateTimeFormats($table)
1030  {
1031  return self::$dateTimeFormats;
1032  }
1033 
1050  protected function getSelectMmQueryParts($select, $local_table, $mm_table, $foreign_table, $whereClause = '', $groupBy = '', $orderBy = '', $limit = '')
1051  {
1052  $foreign_table_as = $foreign_table == $local_table ? $foreign_table . StringUtility::getUniqueId('_join') : '';
1053  $mmWhere = $local_table ? $local_table . '.uid=' . $mm_table . '.uid_local' : '';
1054  $mmWhere .= ($local_table and $foreign_table) ? ' AND ' : '';
1055  $tables = ($local_table ? $local_table . ',' : '') . $mm_table;
1056  if ($foreign_table) {
1057  $mmWhere .= ($foreign_table_as ?: $foreign_table) . '.uid=' . $mm_table . '.uid_foreign';
1058  $tables .= ',' . $foreign_table . ($foreign_table_as ? ' AS ' . $foreign_table_as : '');
1059  }
1060  return array(
1061  'SELECT' => $select,
1062  'FROM' => $tables,
1063  'WHERE' => $mmWhere . ' ' . $whereClause,
1064  'GROUPBY' => $groupBy,
1065  'ORDERBY' => $orderBy,
1066  'LIMIT' => $limit
1067  );
1068  }
1069 
1070  /**************************************
1071  *
1072  * MySQL(i) wrapper functions
1073  * (For use in your applications)
1074  *
1075  **************************************/
1085  public function sql_query($query)
1086  {
1087  $res = $this->query($query);
1088  if ($this->debugOutput) {
1089  $this->debug('sql_query', $query);
1090  }
1091  return $res;
1092  }
1093 
1099  public function sql_error()
1100  {
1101  return $this->link->error;
1102  }
1103 
1109  public function sql_errno()
1110  {
1111  return $this->link->errno;
1112  }
1113 
1120  public function sql_num_rows($res)
1121  {
1122  if ($this->debug_check_recordset($res)) {
1123  return $res->num_rows;
1124  } else {
1125  return false;
1126  }
1127  }
1128 
1136  public function sql_fetch_assoc($res)
1137  {
1138  if ($this->debug_check_recordset($res)) {
1139  $result = $res->fetch_assoc();
1140  if ($result === null) {
1141  // Needed for compatibility
1142  $result = false;
1143  }
1144  return $result;
1145  } else {
1146  return false;
1147  }
1148  }
1149 
1158  public function sql_fetch_row($res)
1159  {
1160  if ($this->debug_check_recordset($res)) {
1161  $result = $res->fetch_row();
1162  if ($result === null) {
1163  // Needed for compatibility
1164  $result = false;
1165  }
1166  return $result;
1167  } else {
1168  return false;
1169  }
1170  }
1171 
1179  public function sql_free_result($res)
1180  {
1181  if ($this->debug_check_recordset($res) && is_object($res)) {
1182  $res->free();
1183  return true;
1184  } else {
1185  return false;
1186  }
1187  }
1188 
1194  public function sql_insert_id()
1195  {
1196  return $this->link->insert_id;
1197  }
1198 
1204  public function sql_affected_rows()
1205  {
1206  return $this->link->affected_rows;
1207  }
1208 
1216  public function sql_data_seek($res, $seek)
1217  {
1218  if ($this->debug_check_recordset($res)) {
1219  return $res->data_seek($seek);
1220  } else {
1221  return false;
1222  }
1223  }
1224 
1233  public function sql_field_type($res, $pointer)
1234  {
1235  // mysql_field_type compatibility map
1236  // taken from: http://www.php.net/manual/en/mysqli-result.fetch-field-direct.php#89117
1237  // Constant numbers see http://php.net/manual/en/mysqli.constants.php
1238  $mysql_data_type_hash = array(
1239  1=>'tinyint',
1240  2=>'smallint',
1241  3=>'int',
1242  4=>'float',
1243  5=>'double',
1244  7=>'timestamp',
1245  8=>'bigint',
1246  9=>'mediumint',
1247  10=>'date',
1248  11=>'time',
1249  12=>'datetime',
1250  13=>'year',
1251  16=>'bit',
1252  //252 is currently mapped to all text and blob types (MySQL 5.0.51a)
1253  253=>'varchar',
1254  254=>'char',
1255  246=>'decimal'
1256  );
1257  if ($this->debug_check_recordset($res)) {
1258  $metaInfo = $res->fetch_field_direct($pointer);
1259  if ($metaInfo === false) {
1260  return false;
1261  }
1262  return $mysql_data_type_hash[$metaInfo->type];
1263  } else {
1264  return false;
1265  }
1266  }
1267 
1274  public function sql_pconnect()
1275  {
1276  if ($this->isConnected) {
1277  return $this->link;
1278  }
1279 
1280  if (!extension_loaded('mysqli')) {
1281  throw new \RuntimeException(
1282  'Database Error: PHP mysqli extension not loaded. This is a must have for TYPO3 CMS!',
1283  1271492607
1284  );
1285  }
1286 
1287  $host = $this->persistentDatabaseConnection
1288  ? 'p:' . $this->databaseHost
1289  : $this->databaseHost;
1290 
1291  $this->link = mysqli_init();
1292  $connected = $this->link->real_connect(
1293  $host,
1294  $this->databaseUsername,
1295  $this->databaseUserPassword,
1296  null,
1297  (int)$this->databasePort,
1298  $this->databaseSocket,
1299  $this->connectionCompression ? MYSQLI_CLIENT_COMPRESS : 0
1300  );
1301 
1302  if ($connected) {
1303  $this->isConnected = true;
1304 
1305  if ($this->link->set_charset($this->connectionCharset) === false) {
1306  GeneralUtility::sysLog(
1307  'Error setting connection charset to "' . $this->connectionCharset . '"',
1308  'core',
1310  );
1311  }
1312 
1313  foreach ($this->initializeCommandsAfterConnect as $command) {
1314  if ($this->query($command) === false) {
1315  GeneralUtility::sysLog(
1316  'Could not initialize DB connection with query "' . $command . '": ' . $this->sql_error(),
1317  'core',
1319  );
1320  }
1321  }
1322  $this->checkConnectionCharset();
1323  } else {
1324  // @todo This should raise an exception. Would be useful especially to work during installation.
1325  $error_msg = $this->link->connect_error;
1326  $this->link = null;
1327  GeneralUtility::sysLog(
1328  'Could not connect to MySQL server ' . $host . ' with user ' . $this->databaseUsername . ': ' . $error_msg,
1329  'core',
1331  );
1332  }
1333  return $this->link;
1334  }
1335 
1341  public function sql_select_db()
1342  {
1343  if (!$this->isConnected) {
1344  $this->connectDB();
1345  }
1346 
1347  $ret = $this->link->select_db($this->databaseName);
1348  if (!$ret) {
1349  GeneralUtility::sysLog(
1350  'Could not select MySQL database ' . $this->databaseName . ': ' . $this->sql_error(),
1351  'core',
1353  );
1354  }
1355  return $ret;
1356  }
1357 
1358  /**************************************
1359  *
1360  * SQL admin functions
1361  * (For use in the Install Tool and Extension Manager)
1362  *
1363  **************************************/
1373  public function admin_get_dbs()
1374  {
1375  $dbArr = array();
1376  $db_list = $this->query('SELECT SCHEMA_NAME FROM information_schema.SCHEMATA');
1377  if ($db_list === false) {
1378  throw new \RuntimeException(
1379  'MySQL Error: Cannot get tablenames: "' . $this->sql_error() . '"!',
1380  1378457171
1381  );
1382  } else {
1383  while ($row = $db_list->fetch_object()) {
1384  try {
1385  $this->setDatabaseName($row->SCHEMA_NAME);
1386  if ($this->sql_select_db()) {
1387  $dbArr[] = $row->SCHEMA_NAME;
1388  }
1389  } catch (\RuntimeException $exception) {
1390  // The exception happens if we cannot connect to the database
1391  // (usually due to missing permissions). This is ok here.
1392  // We catch the exception, skip the database and continue.
1393  }
1394  }
1395  }
1396  return $dbArr;
1397  }
1398 
1406  public function admin_get_tables()
1407  {
1408  $whichTables = array();
1409  $tables_result = $this->query('SHOW TABLE STATUS FROM `' . $this->databaseName . '`');
1410  if ($tables_result !== false) {
1411  while ($theTable = $tables_result->fetch_assoc()) {
1412  $whichTables[$theTable['Name']] = $theTable;
1413  }
1414  $tables_result->free();
1415  }
1416  return $whichTables;
1417  }
1418 
1430  public function admin_get_fields($tableName)
1431  {
1432  $output = array();
1433  $columns_res = $this->query('SHOW FULL COLUMNS FROM `' . $tableName . '`');
1434  if ($columns_res !== false) {
1435  while ($fieldRow = $columns_res->fetch_assoc()) {
1436  $output[$fieldRow['Field']] = $fieldRow;
1437  }
1438  $columns_res->free();
1439  }
1440  return $output;
1441  }
1442 
1450  public function admin_get_keys($tableName)
1451  {
1452  $output = array();
1453  $keyRes = $this->query('SHOW KEYS FROM `' . $tableName . '`');
1454  if ($keyRes !== false) {
1455  while ($keyRow = $keyRes->fetch_assoc()) {
1456  $output[] = $keyRow;
1457  }
1458  $keyRes->free();
1459  }
1460  return $output;
1461  }
1462 
1475  public function admin_get_charsets()
1476  {
1477  $output = array();
1478  $columns_res = $this->query('SHOW CHARACTER SET');
1479  if ($columns_res !== false) {
1480  while ($row = $columns_res->fetch_assoc()) {
1481  $output[$row['Charset']] = $row;
1482  }
1483  $columns_res->free();
1484  }
1485  return $output;
1486  }
1487 
1494  public function admin_query($query)
1495  {
1496  $res = $this->query($query);
1497  if ($this->debugOutput) {
1498  $this->debug('admin_query', $query);
1499  }
1500  return $res;
1501  }
1502 
1503  /******************************
1504  *
1505  * Connect handling
1506  *
1507  ******************************/
1508 
1514  public function setDatabaseHost($host = 'localhost')
1515  {
1516  $this->disconnectIfConnected();
1517  $this->databaseHost = $host;
1518  }
1519 
1525  public function setDatabasePort($port = 3306)
1526  {
1527  $this->disconnectIfConnected();
1528  $this->databasePort = (int)$port;
1529  }
1530 
1536  public function setDatabaseSocket($socket = null)
1537  {
1538  $this->disconnectIfConnected();
1539  $this->databaseSocket = $socket;
1540  }
1541 
1547  public function setDatabaseName($name)
1548  {
1549  $this->disconnectIfConnected();
1550  $this->databaseName = $name;
1551  }
1552 
1558  public function setDatabaseUsername($username)
1559  {
1560  $this->disconnectIfConnected();
1561  $this->databaseUsername = $username;
1562  }
1563 
1569  public function setDatabasePassword($password)
1570  {
1571  $this->disconnectIfConnected();
1572  $this->databaseUserPassword = $password;
1573  }
1574 
1581  public function setPersistentDatabaseConnection($persistentDatabaseConnection)
1582  {
1583  $this->disconnectIfConnected();
1584  $this->persistentDatabaseConnection = (bool)$persistentDatabaseConnection;
1585  }
1586 
1592  public function setConnectionCompression($connectionCompression)
1593  {
1594  $this->disconnectIfConnected();
1595  $this->connectionCompression = (bool)$connectionCompression;
1596  }
1597 
1603  public function setInitializeCommandsAfterConnect(array $commands)
1604  {
1605  $this->disconnectIfConnected();
1606  $this->initializeCommandsAfterConnect = $commands;
1607  }
1608 
1618  public function setConnectionCharset($connectionCharset = 'utf8')
1619  {
1620  $this->disconnectIfConnected();
1621  $this->connectionCharset = $connectionCharset;
1622  }
1623 
1631  public function connectDB()
1632  {
1633  // Early return if connected already
1634  if ($this->isConnected) {
1635  return;
1636  }
1637 
1638  if (!$this->databaseName) {
1639  throw new \RuntimeException(
1640  'TYPO3 Fatal Error: No database selected!',
1641  1270853882
1642  );
1643  }
1644 
1645  if ($this->sql_pconnect()) {
1646  if (!$this->sql_select_db()) {
1647  throw new \RuntimeException(
1648  'TYPO3 Fatal Error: Cannot connect to the current database, "' . $this->databaseName . '"!',
1649  1270853883
1650  );
1651  }
1652  } else {
1653  throw new \RuntimeException(
1654  'TYPO3 Fatal Error: The current username, password or host was not accepted when the connection to the database was attempted to be established!',
1655  1270853884
1656  );
1657  }
1658 
1659  // Prepare user defined objects (if any) for hooks which extend query methods
1660  $this->preProcessHookObjects = array();
1661  $this->postProcessHookObjects = array();
1662  if (is_array($GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_db.php']['queryProcessors'])) {
1663  foreach ($GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_db.php']['queryProcessors'] as $classRef) {
1664  $hookObject = GeneralUtility::getUserObj($classRef);
1665  if (!(
1666  $hookObject instanceof PreProcessQueryHookInterface
1667  || $hookObject instanceof PostProcessQueryHookInterface
1668  )) {
1669  throw new \UnexpectedValueException(
1670  '$hookObject must either implement interface TYPO3\\CMS\\Core\\Database\\PreProcessQueryHookInterface or interface TYPO3\\CMS\\Core\\Database\\PostProcessQueryHookInterface',
1671  1299158548
1672  );
1673  }
1674  if ($hookObject instanceof PreProcessQueryHookInterface) {
1675  $this->preProcessHookObjects[] = $hookObject;
1676  }
1677  if ($hookObject instanceof PostProcessQueryHookInterface) {
1678  $this->postProcessHookObjects[] = $hookObject;
1679  }
1680  }
1681  }
1682  }
1683 
1689  public function isConnected()
1690  {
1691  // We think we're still connected
1692  if ($this->isConnected) {
1693  // Check if this is really the case or if the database server has gone away for some reason
1694  // Using mysqlnd ping() does not reconnect (which we would not want anyway since charset etc would not be reinitialized that way)
1695  $this->isConnected = $this->link->ping();
1696  }
1697  return $this->isConnected;
1698  }
1699 
1714  protected function checkConnectionCharset()
1715  {
1716  $sessionResult = $this->sql_query('SHOW SESSION VARIABLES LIKE \'character_set%\'');
1717 
1718  if ($sessionResult === false) {
1719  GeneralUtility::sysLog(
1720  'Error while retrieving the current charset session variables from the database: ' . $this->sql_error(),
1721  'core',
1723  );
1724  throw new \RuntimeException(
1725  'TYPO3 Fatal Error: Could not determine the current charset of the database.',
1726  1381847136
1727  );
1728  }
1729 
1730  $charsetVariables = array();
1731  while (($row = $this->sql_fetch_row($sessionResult)) !== false) {
1732  $variableName = $row[0];
1733  $variableValue = $row[1];
1734  $charsetVariables[$variableName] = $variableValue;
1735  }
1736  $this->sql_free_result($sessionResult);
1737 
1738  // These variables are set with the "Set names" command which was
1739  // used in the past. This is why we check them.
1740  $charsetRequiredVariables = array(
1741  'character_set_client',
1742  'character_set_results',
1743  'character_set_connection',
1744  );
1745 
1746  $hasValidCharset = true;
1747  foreach ($charsetRequiredVariables as $variableName) {
1748  if (empty($charsetVariables[$variableName])) {
1749  GeneralUtility::sysLog(
1750  'A required session variable is missing in the current MySQL connection: ' . $variableName,
1751  'core',
1753  );
1754  throw new \RuntimeException(
1755  'TYPO3 Fatal Error: Could not determine the value of the database session variable: ' . $variableName,
1756  1381847779
1757  );
1758  }
1759 
1760  if ($charsetVariables[$variableName] !== $this->connectionCharset) {
1761  $hasValidCharset = false;
1762  break;
1763  }
1764  }
1765 
1766  if (!$hasValidCharset) {
1767  throw new \RuntimeException(
1768  'It looks like the character set ' . $this->connectionCharset . ' is not used for this connection even though it is configured as connection charset. ' .
1769  'This TYPO3 installation is using the $GLOBALS[\'TYPO3_CONF_VARS\'][\'SYS\'][\'setDBinit\'] property with the following value: "' .
1770  $GLOBALS['TYPO3_CONF_VARS']['SYS']['setDBinit'] . '". Please make sure that this command does not overwrite the configured charset. ' .
1771  'Please note that for the TYPO3 database everything other than utf8 is unsupported since version 4.7.',
1772  1389697515
1773  );
1774  }
1775  }
1776 
1782  protected function disconnectIfConnected()
1783  {
1784  if ($this->isConnected) {
1785  $this->link->close();
1786  $this->isConnected = false;
1787  }
1788  }
1789 
1795  public function getDatabaseHandle()
1796  {
1797  return $this->link;
1798  }
1799 
1805  public function setDatabaseHandle($handle)
1806  {
1807  $this->link = $handle;
1808  }
1809 
1815  public function getServerVersion()
1816  {
1817  return $this->link->server_info;
1818  }
1819 
1820  /******************************
1821  *
1822  * Debugging
1823  *
1824  ******************************/
1832  public function debug($func, $query = '')
1833  {
1834  $error = $this->sql_error();
1835  if ($error || (int)$this->debugOutput === 2) {
1837  array(
1838  'caller' => \TYPO3\CMS\Core\Database\DatabaseConnection::class . '::' . $func,
1839  'ERROR' => $error,
1840  'lastBuiltQuery' => $query ? $query : $this->debug_lastBuiltQuery,
1841  'debug_backtrace' => \TYPO3\CMS\Core\Utility\DebugUtility::debugTrail()
1842  ),
1843  $func,
1844  is_object($GLOBALS['error']) && @is_callable(array($GLOBALS['error'], 'debug'))
1845  ? ''
1846  : 'DB Error'
1847  );
1848  }
1849  }
1850 
1857  public function debug_check_recordset($res)
1858  {
1859  if ($res !== false) {
1860  return true;
1861  }
1862  $trace = debug_backtrace(0);
1863  array_shift($trace);
1864  $msg = 'Invalid database result detected: function TYPO3\\CMS\\Core\\Database\\DatabaseConnection->'
1865  . $trace[0]['function'] . ' called from file ' . substr($trace[0]['file'], (strlen(PATH_site) + 2))
1866  . ' in line ' . $trace[0]['line'] . '.';
1867  GeneralUtility::sysLog(
1868  $msg . ' Use a devLog extension to get more details.',
1869  'core',
1871  );
1872  // Send to devLog if enabled
1873  if (TYPO3_DLOG) {
1874  $debugLogData = array(
1875  'SQL Error' => $this->sql_error(),
1876  'Backtrace' => $trace
1877  );
1878  if ($this->debug_lastBuiltQuery) {
1879  $debugLogData = array('SQL Query' => $this->debug_lastBuiltQuery) + $debugLogData;
1880  }
1881  GeneralUtility::devLog($msg, 'Core/t3lib_db', 3, $debugLogData);
1882  }
1883  return false;
1884  }
1885 
1898  protected function explain($query, $from_table, $row_count)
1899  {
1900  $debugAllowedForIp = GeneralUtility::cmpIP(
1901  GeneralUtility::getIndpEnv('REMOTE_ADDR'),
1902  $GLOBALS['TYPO3_CONF_VARS']['SYS']['devIPmask']
1903  );
1904  if (
1905  (int)$this->explainOutput == 1
1906  || ((int)$this->explainOutput == 2 && $debugAllowedForIp)
1907  ) {
1908  // Raw HTML output
1909  $explainMode = 1;
1910  } elseif ((int)$this->explainOutput == 3 && is_object($GLOBALS['TT'])) {
1911  // Embed the output into the TS admin panel
1912  $explainMode = 2;
1913  } else {
1914  return false;
1915  }
1916  $error = $this->sql_error();
1918  $explain_tables = array();
1919  $explain_output = array();
1920  $res = $this->sql_query('EXPLAIN ' . $query, $this->link);
1921  if (is_a($res, '\\mysqli_result')) {
1922  while ($tempRow = $this->sql_fetch_assoc($res)) {
1923  $explain_output[] = $tempRow;
1924  $explain_tables[] = $tempRow['table'];
1925  }
1926  $this->sql_free_result($res);
1927  }
1928  $indices_output = array();
1929  // Notice: Rows are skipped if there is only one result, or if no conditions are set
1930  if ($explain_output[0]['rows'] > 1 || $explain_output[0]['type'] === 'ALL') {
1931  // Only enable output if it's really useful
1932  $debug = true;
1933  foreach ($explain_tables as $table) {
1934  $tableRes = $this->sql_query('SHOW TABLE STATUS LIKE \'' . $table . '\'');
1935  $isTable = $this->sql_num_rows($tableRes);
1936  if ($isTable) {
1937  $res = $this->sql_query('SHOW INDEX FROM ' . $table, $this->link);
1938  if (is_a($res, '\\mysqli_result')) {
1939  while ($tempRow = $this->sql_fetch_assoc($res)) {
1940  $indices_output[] = $tempRow;
1941  }
1942  $this->sql_free_result($res);
1943  }
1944  }
1945  $this->sql_free_result($tableRes);
1946  }
1947  } else {
1948  $debug = false;
1949  }
1950  if ($debug) {
1951  if ($explainMode) {
1952  $data = array();
1953  $data['query'] = $query;
1954  $data['trail'] = $trail;
1955  $data['row_count'] = $row_count;
1956  if ($error) {
1957  $data['error'] = $error;
1958  }
1959  if (!empty($explain_output)) {
1960  $data['explain'] = $explain_output;
1961  }
1962  if (!empty($indices_output)) {
1963  $data['indices'] = $indices_output;
1964  }
1965  if ($explainMode == 1) {
1966  \TYPO3\CMS\Core\Utility\DebugUtility::debug($data, 'Tables: ' . $from_table, 'DB SQL EXPLAIN');
1967  } elseif ($explainMode == 2) {
1968  $GLOBALS['TT']->setTSselectQuery($data);
1969  }
1970  }
1971  return true;
1972  }
1973  return false;
1974  }
1975 
1981  public function __sleep()
1982  {
1983  $this->disconnectIfConnected();
1984  return array(
1985  'debugOutput',
1986  'explainOutput',
1987  'databaseHost',
1988  'databasePort',
1989  'databaseSocket',
1990  'databaseName',
1991  'databaseUsername',
1992  'databaseUserPassword',
1993  'persistentDatabaseConnection',
1994  'connectionCompression',
1995  'initializeCommandsAfterConnect',
1996  'default_charset',
1997  );
1998  }
1999 }
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)