TYPO3CMS  6.2
 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  * Copyright notice
6  *
7  * (c) 2004-2013 Kasper Skårhøj (kasperYYYY@typo3.com)
8  * All rights reserved
9  *
10  * This script is part of the TYPO3 project. The TYPO3 project is
11  * free software; you can redistribute it and/or modify
12  * it under the terms of the GNU General Public License as published by
13  * the Free Software Foundation; either version 2 of the License, or
14  * (at your option) any later version.
15  *
16  * The GNU General Public License can be found at
17  * http://www.gnu.org/copyleft/gpl.html.
18  * A copy is found in the text file GPL.txt and important notices to the license
19  * from the author is found in LICENSE.txt distributed with these scripts.
20  *
21  *
22  * This script is distributed in the hope that it will be useful,
23  * but WITHOUT ANY WARRANTY; without even the implied warranty of
24  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25  * GNU General Public License for more details.
26  *
27  * This copyright notice MUST APPEAR in all copies of the script!
28  ***************************************************************/
29 
31 
60 
66  const AND_Constraint = 'AND';
67 
73  const OR_Constraint = 'OR';
74 
75  // Set "TRUE" or "1" if you want database errors outputted. Set to "2" if you also want successful database actions outputted.
79  public $debugOutput = FALSE;
80 
81  // Internally: Set to last built query (not necessarily executed...)
85  public $debug_lastBuiltQuery = '';
86 
87  // Set "TRUE" if you want the last built query to be stored in $debug_lastBuiltQuery independent of $this->debugOutput
91  public $store_lastBuiltQuery = FALSE;
92 
93  // Set this to 1 to get queries explained (devIPmask must match). Set the value to 2 to the same but disregarding the devIPmask.
94  // There is an alternative option to enable explain output in the admin panel under "TypoScript", which will produce much nicer output, but only works in FE.
98  public $explainOutput = 0;
99 
103  protected $databaseHost = '';
104 
108  protected $databasePort = 3306;
109 
113  protected $databaseSocket = NULL;
114 
118  protected $databaseName = '';
119 
123  protected $databaseUsername = '';
124 
128  protected $databaseUserPassword = '';
129 
134  protected $persistentDatabaseConnection = FALSE;
135 
139  protected $connectionCompression = FALSE;
140 
147  protected $connectionCharset = 'utf8';
148 
152  protected $initializeCommandsAfterConnect = array();
153 
157  protected $isConnected = FALSE;
158 
162  protected $link = NULL;
163 
164  // Default character set, applies unless character set or collation are explicitly set
168  public $default_charset = 'utf8';
169 
173  protected $preProcessHookObjects = array();
174 
178  protected $postProcessHookObjects = array();
179 
180 
186  public function initialize() {
187  // Intentionally blank as this will be overloaded by DBAL
188  }
189 
190  /************************************
191  *
192  * Query execution
193  *
194  * These functions are the RECOMMENDED DBAL functions for use in your applications
195  * Using these functions will allow the DBAL to use alternative ways of accessing data (contrary to if a query is returned!)
196  * They compile a query AND execute it immediately and then return the result
197  * This principle heightens our ability to create various forms of DBAL of the functions.
198  * Generally: We want to return a result pointer/object, never queries.
199  * Also, having the table name together with the actual query execution allows us to direct the request to other databases.
200  *
201  **************************************/
202 
212  public function exec_INSERTquery($table, $fields_values, $no_quote_fields = FALSE) {
213  $res = $this->query($this->INSERTquery($table, $fields_values, $no_quote_fields));
214  if ($this->debugOutput) {
215  $this->debug('exec_INSERTquery');
216  }
217  foreach ($this->postProcessHookObjects as $hookObject) {
219  $hookObject->exec_INSERTquery_postProcessAction($table, $fields_values, $no_quote_fields, $this);
220  }
221  return $res;
222  }
223 
233  public function exec_INSERTmultipleRows($table, array $fields, array $rows, $no_quote_fields = FALSE) {
234  $res = $this->query($this->INSERTmultipleRows($table, $fields, $rows, $no_quote_fields));
235  if ($this->debugOutput) {
236  $this->debug('exec_INSERTmultipleRows');
237  }
238  foreach ($this->postProcessHookObjects as $hookObject) {
240  $hookObject->exec_INSERTmultipleRows_postProcessAction($table, $fields, $rows, $no_quote_fields, $this);
241  }
242  return $res;
243  }
244 
255  public function exec_UPDATEquery($table, $where, $fields_values, $no_quote_fields = FALSE) {
256  $res = $this->query($this->UPDATEquery($table, $where, $fields_values, $no_quote_fields));
257  if ($this->debugOutput) {
258  $this->debug('exec_UPDATEquery');
259  }
260  foreach ($this->postProcessHookObjects as $hookObject) {
262  $hookObject->exec_UPDATEquery_postProcessAction($table, $where, $fields_values, $no_quote_fields, $this);
263  }
264  return $res;
265  }
266 
274  public function exec_DELETEquery($table, $where) {
275  $res = $this->query($this->DELETEquery($table, $where));
276  if ($this->debugOutput) {
277  $this->debug('exec_DELETEquery');
278  }
279  foreach ($this->postProcessHookObjects as $hookObject) {
281  $hookObject->exec_DELETEquery_postProcessAction($table, $where, $this);
282  }
283  return $res;
284  }
285 
298  public function exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '') {
299  $query = $this->SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
300  $res = $this->query($query);
301  if ($this->debugOutput) {
302  $this->debug('exec_SELECTquery');
303  }
304  if ($this->explainOutput) {
305  $this->explain($query, $from_table, $res->num_rows);
306  }
307  foreach ($this->postProcessHookObjects as $hookObject) {
309  $hookObject->exec_SELECTquery_postProcessAction($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', $this);
310  }
311  return $res;
312  }
313 
331  public function exec_SELECT_mm_query($select, $local_table, $mm_table, $foreign_table, $whereClause = '', $groupBy = '', $orderBy = '', $limit = '') {
332  $foreign_table_as = $foreign_table == $local_table ? $foreign_table . uniqid('_join') : '';
333  $mmWhere = $local_table ? $local_table . '.uid=' . $mm_table . '.uid_local' : '';
334  $mmWhere .= ($local_table and $foreign_table) ? ' AND ' : '';
335  $tables = ($local_table ? $local_table . ',' : '') . $mm_table;
336  if ($foreign_table) {
337  $mmWhere .= ($foreign_table_as ?: $foreign_table) . '.uid=' . $mm_table . '.uid_foreign';
338  $tables .= ',' . $foreign_table . ($foreign_table_as ? ' AS ' . $foreign_table_as : '');
339  }
340  return $this->exec_SELECTquery($select, $tables, $mmWhere . ' ' . $whereClause, $groupBy, $orderBy, $limit);
341  }
342 
350  public function exec_SELECT_queryArray($queryParts) {
351  return $this->exec_SELECTquery($queryParts['SELECT'], $queryParts['FROM'], $queryParts['WHERE'], $queryParts['GROUPBY'], $queryParts['ORDERBY'], $queryParts['LIMIT']);
352  }
353 
366  public function exec_SELECTgetRows($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', $uidIndexField = '') {
367  $res = $this->exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
368  if ($this->debugOutput) {
369  $this->debug('exec_SELECTquery');
370  }
371  if (!$this->sql_error()) {
372  $output = array();
373  if ($uidIndexField) {
374  while ($tempRow = $this->sql_fetch_assoc($res)) {
375  $output[$tempRow[$uidIndexField]] = $tempRow;
376  }
377  } else {
378  while ($output[] = $this->sql_fetch_assoc($res)) {
379 
380  }
381  array_pop($output);
382  }
383  $this->sql_free_result($res);
384  } else {
385  $output = NULL;
386  }
387  return $output;
388  }
389 
402  public function exec_SELECTgetSingleRow($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $numIndex = FALSE) {
403  $res = $this->exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, '1');
404  if ($this->debugOutput) {
405  $this->debug('exec_SELECTquery');
406  }
407  $output = NULL;
408  if ($res !== FALSE) {
409  if ($numIndex) {
410  $output = $this->sql_fetch_row($res);
411  } else {
412  $output = $this->sql_fetch_assoc($res);
413  }
414  $this->sql_free_result($res);
415  }
416  return $output;
417  }
418 
427  public function exec_SELECTcountRows($field, $table, $where = '') {
428  $count = FALSE;
429  $resultSet = $this->exec_SELECTquery('COUNT(' . $field . ')', $table, $where);
430  if ($resultSet !== FALSE) {
431  list($count) = $this->sql_fetch_row($resultSet);
432  $count = (int)$count;
433  $this->sql_free_result($resultSet);
434  }
435  return $count;
436  }
437 
444  public function exec_TRUNCATEquery($table) {
445  $res = $this->query($this->TRUNCATEquery($table));
446  if ($this->debugOutput) {
447  $this->debug('exec_TRUNCATEquery');
448  }
449  foreach ($this->postProcessHookObjects as $hookObject) {
451  $hookObject->exec_TRUNCATEquery_postProcessAction($table, $this);
452  }
453  return $res;
454  }
455 
463  protected function query($query) {
464  if (!$this->isConnected) {
465  $this->connectDB();
466  }
467  return $this->link->query($query);
468  }
469 
470  /**************************************
471  *
472  * Query building
473  *
474  **************************************/
483  public function INSERTquery($table, $fields_values, $no_quote_fields = FALSE) {
484  // Table and fieldnames should be "SQL-injection-safe" when supplied to this
485  // function (contrary to values in the arrays which may be insecure).
486  if (!is_array($fields_values) || count($fields_values) === 0) {
487  return NULL;
488  }
489  foreach ($this->preProcessHookObjects as $hookObject) {
490  $hookObject->INSERTquery_preProcessAction($table, $fields_values, $no_quote_fields, $this);
491  }
492  // Quote and escape values
493  $fields_values = $this->fullQuoteArray($fields_values, $table, $no_quote_fields, TRUE);
494  // Build query
495  $query = 'INSERT INTO ' . $table . ' (' . implode(',', array_keys($fields_values)) . ') VALUES ' . '(' . implode(',', $fields_values) . ')';
496  // Return query
497  if ($this->debugOutput || $this->store_lastBuiltQuery) {
498  $this->debug_lastBuiltQuery = $query;
499  }
500  return $query;
501  }
502 
512  public function INSERTmultipleRows($table, array $fields, array $rows, $no_quote_fields = FALSE) {
513  // Table and fieldnames should be "SQL-injection-safe" when supplied to this
514  // function (contrary to values in the arrays which may be insecure).
515  if (count($rows) === 0) {
516  return NULL;
517  }
518  foreach ($this->preProcessHookObjects as $hookObject) {
520  $hookObject->INSERTmultipleRows_preProcessAction($table, $fields, $rows, $no_quote_fields, $this);
521  }
522  // Build query
523  $query = 'INSERT INTO ' . $table . ' (' . implode(', ', $fields) . ') VALUES ';
524  $rowSQL = array();
525  foreach ($rows as $row) {
526  // Quote and escape values
527  $row = $this->fullQuoteArray($row, $table, $no_quote_fields);
528  $rowSQL[] = '(' . implode(', ', $row) . ')';
529  }
530  $query .= implode(', ', $rowSQL);
531  // Return query
532  if ($this->debugOutput || $this->store_lastBuiltQuery) {
533  $this->debug_lastBuiltQuery = $query;
534  }
535  return $query;
536  }
537 
549  public function UPDATEquery($table, $where, $fields_values, $no_quote_fields = FALSE) {
550  // Table and fieldnames should be "SQL-injection-safe" when supplied to this
551  // function (contrary to values in the arrays which may be insecure).
552  if (is_string($where)) {
553  foreach ($this->preProcessHookObjects as $hookObject) {
555  $hookObject->UPDATEquery_preProcessAction($table, $where, $fields_values, $no_quote_fields, $this);
556  }
557  $fields = array();
558  if (is_array($fields_values) && count($fields_values)) {
559  // Quote and escape values
560  $nArr = $this->fullQuoteArray($fields_values, $table, $no_quote_fields, TRUE);
561  foreach ($nArr as $k => $v) {
562  $fields[] = $k . '=' . $v;
563  }
564  }
565  // Build query
566  $query = 'UPDATE ' . $table . ' SET ' . implode(',', $fields) . ((string)$where !== '' ? ' WHERE ' . $where : '');
567  if ($this->debugOutput || $this->store_lastBuiltQuery) {
568  $this->debug_lastBuiltQuery = $query;
569  }
570  return $query;
571  } else {
572  throw new \InvalidArgumentException('TYPO3 Fatal Error: "Where" clause argument for UPDATE query was not a string in $this->UPDATEquery() !', 1270853880);
573  }
574  }
575 
584  public function DELETEquery($table, $where) {
585  if (is_string($where)) {
586  foreach ($this->preProcessHookObjects as $hookObject) {
588  $hookObject->DELETEquery_preProcessAction($table, $where, $this);
589  }
590  // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
591  $query = 'DELETE FROM ' . $table . ((string)$where !== '' ? ' WHERE ' . $where : '');
592  if ($this->debugOutput || $this->store_lastBuiltQuery) {
593  $this->debug_lastBuiltQuery = $query;
594  }
595  return $query;
596  } else {
597  throw new \InvalidArgumentException('TYPO3 Fatal Error: "Where" clause argument for DELETE query was not a string in $this->DELETEquery() !', 1270853881);
598  }
599  }
600 
612  public function SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '') {
613  foreach ($this->preProcessHookObjects as $hookObject) {
615  $hookObject->SELECTquery_preProcessAction($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit, $this);
616  }
617  // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
618  // Build basic query
619  $query = 'SELECT ' . $select_fields . ' FROM ' . $from_table . ((string)$where_clause !== '' ? ' WHERE ' . $where_clause : '');
620  // Group by
621  $query .= (string)$groupBy !== '' ? ' GROUP BY ' . $groupBy : '';
622  // Order by
623  $query .= (string)$orderBy !== '' ? ' ORDER BY ' . $orderBy : '';
624  // Group by
625  $query .= (string)$limit !== '' ? ' LIMIT ' . $limit : '';
626  // Return query
627  if ($this->debugOutput || $this->store_lastBuiltQuery) {
628  $this->debug_lastBuiltQuery = $query;
629  }
630  return $query;
631  }
632 
642  public function SELECTsubquery($select_fields, $from_table, $where_clause) {
643  // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
644  // Build basic query:
645  $query = 'SELECT ' . $select_fields . ' FROM ' . $from_table . ((string)$where_clause !== '' ? ' WHERE ' . $where_clause : '');
646  // Return query
647  if ($this->debugOutput || $this->store_lastBuiltQuery) {
648  $this->debug_lastBuiltQuery = $query;
649  }
650  return $query;
651  }
652 
659  public function TRUNCATEquery($table) {
660  foreach ($this->preProcessHookObjects as $hookObject) {
662  $hookObject->TRUNCATEquery_preProcessAction($table, $this);
663  }
664  // Table should be "SQL-injection-safe" when supplied to this function
665  // Build basic query:
666  $query = 'TRUNCATE TABLE ' . $table;
667  // Return query:
668  if ($this->debugOutput || $this->store_lastBuiltQuery) {
669  $this->debug_lastBuiltQuery = $query;
670  }
671  return $query;
672  }
673 
689  public function listQuery($field, $value, $table) {
690  $value = (string)$value;
691  if (strpos($value, ',') !== FALSE) {
692  throw new \InvalidArgumentException('$value must not contain a comma (,) in $this->listQuery() !', 1294585862);
693  }
694  $pattern = $this->quoteStr($value, $table);
695  $where = 'FIND_IN_SET(\'' . $pattern . '\',' . $field . ')';
696  return $where;
697  }
698 
708  public function searchQuery($searchWords, $fields, $table, $constraint = self::AND_Constraint) {
709  switch ($constraint) {
710  case self::OR_Constraint:
711  $constraint = 'OR';
712  break;
713  default:
714  $constraint = 'AND';
715  }
716 
717  $queryParts = array();
718  foreach ($searchWords as $sw) {
719  $like = ' LIKE \'%' . $this->quoteStr($sw, $table) . '%\'';
720  $queryParts[] = $table . '.' . implode(($like . ' OR ' . $table . '.'), $fields) . $like;
721  }
722  $query = '(' . implode(') ' . $constraint . ' (', $queryParts) . ')';
723 
724  return $query;
725  }
726 
727  /**************************************
728  *
729  * Prepared Query Support
730  *
731  **************************************/
744  public function prepare_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', array $input_parameters = array()) {
745  $query = $this->SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
747  $preparedStatement = GeneralUtility::makeInstance('TYPO3\\CMS\\Core\\Database\\PreparedStatement', $query, $from_table, array());
748  // Bind values to parameters
749  foreach ($input_parameters as $key => $value) {
750  $preparedStatement->bindValue($key, $value, PreparedStatement::PARAM_AUTOTYPE);
751  }
752  // Return prepared statement
753  return $preparedStatement;
754  }
755 
763  public function prepare_SELECTqueryArray(array $queryParts, array $input_parameters = array()) {
764  return $this->prepare_SELECTquery($queryParts['SELECT'], $queryParts['FROM'], $queryParts['WHERE'], $queryParts['GROUPBY'], $queryParts['ORDERBY'], $queryParts['LIMIT'], $input_parameters);
765  }
766 
775  public function prepare_PREPAREDquery($query, array $queryComponents) {
776  if (!$this->isConnected) {
777  $this->connectDB();
778  }
779  $stmt = $this->link->stmt_init();
780  $success = $stmt->prepare($query);
781  if ($this->debugOutput) {
782  $this->debug('stmt_execute', $query);
783  }
784  return $success ? $stmt : NULL;
785  }
786 
787  /**************************************
788  *
789  * Various helper functions
790  *
791  * Functions recommended to be used for
792  * - escaping values,
793  * - cleaning lists of values,
794  * - stripping of excess ORDER BY/GROUP BY keywords
795  *
796  **************************************/
806  public function fullQuoteStr($str, $table, $allowNull = FALSE) {
807  if (!$this->isConnected) {
808  $this->connectDB();
809  }
810  if ($allowNull && $str === NULL) {
811  return 'NULL';
812  }
813 
814  return '\'' . $this->link->real_escape_string($str) . '\'';
815  }
816 
827  public function fullQuoteArray($arr, $table, $noQuote = FALSE, $allowNull = FALSE) {
828  if (is_string($noQuote)) {
829  $noQuote = explode(',', $noQuote);
830  } elseif (!is_array($noQuote)) {
831  $noQuote = FALSE;
832  }
833  foreach ($arr as $k => $v) {
834  if ($noQuote === FALSE || !in_array($k, $noQuote)) {
835  $arr[$k] = $this->fullQuoteStr($v, $table, $allowNull);
836  }
837  }
838  return $arr;
839  }
840 
851  public function quoteStr($str, $table) {
852  if (!$this->isConnected) {
853  $this->connectDB();
854  }
855  return $this->link->real_escape_string($str);
856  }
857 
866  public function escapeStrForLike($str, $table) {
867  return addcslashes($str, '_%');
868  }
869 
878  public function cleanIntArray($arr) {
879  return array_map('intval', $arr);
880  }
881 
890  public function cleanIntList($list) {
891  return implode(',', GeneralUtility::intExplode(',', $list));
892  }
893 
903  public function stripOrderBy($str) {
904  return preg_replace('/^(?:ORDER[[:space:]]*BY[[:space:]]*)+/i', '', trim($str));
905  }
906 
916  public function stripGroupBy($str) {
917  return preg_replace('/^(?:GROUP[[:space:]]*BY[[:space:]]*)+/i', '', trim($str));
918  }
919 
927  public function splitGroupOrderLimit($str) {
928  // Prepending a space to make sure "[[:space:]]+" will find a space there
929  // for the first element.
930  $str = ' ' . $str;
931  // Init output array:
932  $wgolParts = array(
933  'WHERE' => '',
934  'GROUPBY' => '',
935  'ORDERBY' => '',
936  'LIMIT' => ''
937  );
938  // Find LIMIT
939  $reg = array();
940  if (preg_match('/^(.*)[[:space:]]+LIMIT[[:space:]]+([[:alnum:][:space:],._]+)$/i', $str, $reg)) {
941  $wgolParts['LIMIT'] = trim($reg[2]);
942  $str = $reg[1];
943  }
944  // Find ORDER BY
945  $reg = array();
946  if (preg_match('/^(.*)[[:space:]]+ORDER[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._]+)$/i', $str, $reg)) {
947  $wgolParts['ORDERBY'] = trim($reg[2]);
948  $str = $reg[1];
949  }
950  // Find GROUP BY
951  $reg = array();
952  if (preg_match('/^(.*)[[:space:]]+GROUP[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._]+)$/i', $str, $reg)) {
953  $wgolParts['GROUPBY'] = trim($reg[2]);
954  $str = $reg[1];
955  }
956  // Rest is assumed to be "WHERE" clause
957  $wgolParts['WHERE'] = $str;
958  return $wgolParts;
959  }
960 
967  public function getDateTimeFormats($table) {
968  return array(
969  'date' => array(
970  'empty' => '0000-00-00',
971  'format' => 'Y-m-d'
972  ),
973  'datetime' => array(
974  'empty' => '0000-00-00 00:00:00',
975  'format' => 'Y-m-d H:i:s'
976  )
977  );
978  }
979 
980  /**************************************
981  *
982  * MySQL(i) wrapper functions
983  * (For use in your applications)
984  *
985  **************************************/
995  public function sql_query($query) {
996  $res = $this->query($query);
997  if ($this->debugOutput) {
998  $this->debug('sql_query', $query);
999  }
1000  return $res;
1001  }
1002 
1008  public function sql_error() {
1009  return $this->link->error;
1010  }
1011 
1017  public function sql_errno() {
1018  return $this->link->errno;
1019  }
1020 
1027  public function sql_num_rows($res) {
1028  if ($this->debug_check_recordset($res)) {
1029  return $res->num_rows;
1030  } else {
1031  return FALSE;
1032  }
1033  }
1034 
1042  public function sql_fetch_assoc($res) {
1043  if ($this->debug_check_recordset($res)) {
1044  $result = $res->fetch_assoc();
1045  if ($result === NULL) {
1046  // Needed for compatibility
1047  $result = FALSE;
1048  }
1049  return $result;
1050  } else {
1051  return FALSE;
1052  }
1053  }
1054 
1063  public function sql_fetch_row($res) {
1064  if ($this->debug_check_recordset($res)) {
1065  $result = $res->fetch_row();
1066  if ($result === NULL) {
1067  // Needed for compatibility
1068  $result = FALSE;
1069  }
1070  return $result;
1071  } else {
1072  return FALSE;
1073  }
1074  }
1075 
1083  public function sql_free_result($res) {
1084  if ($this->debug_check_recordset($res) && is_object($res)) {
1085  return $res->free();
1086  } else {
1087  return FALSE;
1088  }
1089  }
1090 
1096  public function sql_insert_id() {
1097  return $this->link->insert_id;
1098  }
1099 
1105  public function sql_affected_rows() {
1106  return $this->link->affected_rows;
1107  }
1108 
1116  public function sql_data_seek($res, $seek) {
1117  if ($this->debug_check_recordset($res)) {
1118  return $res->data_seek($seek);
1119  } else {
1120  return FALSE;
1121  }
1122  }
1123 
1132  public function sql_field_type($res, $pointer) {
1133  // mysql_field_type compatibility map
1134  // taken from: http://www.php.net/manual/en/mysqli-result.fetch-field-direct.php#89117
1135  // Constant numbers see http://php.net/manual/en/mysqli.constants.php
1136  $mysql_data_type_hash = array(
1137  1=>'tinyint',
1138  2=>'smallint',
1139  3=>'int',
1140  4=>'float',
1141  5=>'double',
1142  7=>'timestamp',
1143  8=>'bigint',
1144  9=>'mediumint',
1145  10=>'date',
1146  11=>'time',
1147  12=>'datetime',
1148  13=>'year',
1149  16=>'bit',
1150  //252 is currently mapped to all text and blob types (MySQL 5.0.51a)
1151  253=>'varchar',
1152  254=>'char',
1153  246=>'decimal'
1154  );
1155  if ($this->debug_check_recordset($res)) {
1156  $metaInfo = $res->fetch_field_direct($pointer);
1157  if ($metaInfo === FALSE) {
1158  return FALSE;
1159  }
1160  return $mysql_data_type_hash[$metaInfo->type];
1161  } else {
1162  return FALSE;
1163  }
1164  }
1165 
1175  public function sql_pconnect($host = NULL, $username = NULL, $password = NULL) {
1176  if ($this->isConnected) {
1177  return $this->link;
1178  }
1179 
1180  if (!extension_loaded('mysqli')) {
1181  throw new \RuntimeException(
1182  'Database Error: PHP mysqli extension not loaded. This is a must have for TYPO3 CMS!',
1183  1271492607
1184  );
1185  }
1186 
1187  if ($host || $username || $password) {
1188  $this->handleDeprecatedConnectArguments($host, $username, $password);
1189  }
1190 
1191  $host = $this->persistentDatabaseConnection
1192  ? 'p:' . $this->databaseHost
1193  : $this->databaseHost;
1194 
1195  $this->link = mysqli_init();
1196  $connected = $this->link->real_connect(
1197  $host,
1198  $this->databaseUsername,
1199  $this->databaseUserPassword,
1200  NULL,
1201  (int)$this->databasePort,
1202  $this->databaseSocket,
1203  $this->connectionCompression ? MYSQLI_CLIENT_COMPRESS : 0
1204  );
1205 
1206  if ($connected) {
1207  $this->isConnected = TRUE;
1208 
1209  if ($this->link->set_charset($this->connectionCharset) === FALSE) {
1210  GeneralUtility::sysLog(
1211  'Error setting connection charset to "' . $this->connectionCharset . '"',
1212  'Core',
1214  );
1215  }
1216 
1217  foreach ($this->initializeCommandsAfterConnect as $command) {
1218  if ($this->query($command) === FALSE) {
1219  GeneralUtility::sysLog(
1220  'Could not initialize DB connection with query "' . $command . '": ' . $this->sql_error(),
1221  'Core',
1223  );
1224  }
1225  }
1226  $this->setSqlMode();
1227  $this->checkConnectionCharset();
1228  } else {
1229  // @TODO: This should raise an exception. Would be useful especially to work during installation.
1230  $error_msg = $this->link->connect_error;
1231  $this->link = NULL;
1232  GeneralUtility::sysLog(
1233  'Could not connect to MySQL server ' . $host . ' with user ' . $username . ': ' . $error_msg,
1234  'Core',
1236  );
1237  }
1238  return $this->link;
1239  }
1240 
1246  protected function setSqlMode() {
1247  $resource = $this->sql_query('SELECT @@SESSION.sql_mode;');
1248  if ($resource) {
1249  $result = $this->sql_fetch_row($resource);
1250  if (isset($result[0]) && $result[0] && strpos($result[0], 'NO_BACKSLASH_ESCAPES') !== FALSE) {
1251  $modes = array_diff(GeneralUtility::trimExplode(',', $result[0]), array('NO_BACKSLASH_ESCAPES'));
1252  $query = 'SET sql_mode=\'' . $this->link->real_escape_string(implode(',', $modes)) . '\';';
1253  $this->sql_query($query);
1254  GeneralUtility::sysLog(
1255  'NO_BACKSLASH_ESCAPES could not be removed from SQL mode: ' . $this->sql_error(),
1256  'Core',
1257  GeneralUtility::SYSLOG_SEVERITY_ERROR
1258  );
1259  }
1260  }
1261  }
1262 
1269  public function sql_select_db($TYPO3_db = NULL) {
1270  if (!$this->isConnected) {
1271  $this->connectDB();
1272  }
1273 
1274  if ($TYPO3_db) {
1275  GeneralUtility::deprecationLog(
1276  'DatabaseConnection->sql_select_db() should be called without arguments.' .
1277  ' Use the setDatabaseName() before. Will be removed two versions after 6.1.'
1278  );
1279  } else {
1280  $TYPO3_db = $this->databaseName;
1281  }
1282 
1283  $ret = $this->link->select_db($TYPO3_db);
1284  if (!$ret) {
1285  GeneralUtility::sysLog(
1286  'Could not select MySQL database ' . $TYPO3_db . ': ' . $this->sql_error(),
1287  'Core',
1289  );
1290  }
1291  return $ret;
1292  }
1293 
1294  /**************************************
1295  *
1296  * SQL admin functions
1297  * (For use in the Install Tool and Extension Manager)
1298  *
1299  **************************************/
1309  public function admin_get_dbs() {
1310  $dbArr = array();
1311  $db_list = $this->query("SELECT SCHEMA_NAME FROM information_schema.SCHEMATA");
1312  if ($db_list === FALSE) {
1313  throw new \RuntimeException(
1314  'MySQL Error: Cannot get tablenames: "' . $this->sql_error() . '"!',
1315  1378457171
1316  );
1317  } else {
1318  while ($row = $db_list->fetch_object()) {
1319  try {
1320  $this->setDatabaseName($row->SCHEMA_NAME);
1321  if ($this->sql_select_db()) {
1322  $dbArr[] = $row->SCHEMA_NAME;
1323  }
1324  } catch (\RuntimeException $exception) {
1325  // The exception happens if we cannot connect to the database
1326  // (usually due to missing permissions). This is ok here.
1327  // We catch the exception, skip the database and continue.
1328  }
1329  }
1330  }
1331  return $dbArr;
1332  }
1333 
1341  public function admin_get_tables() {
1342  $whichTables = array();
1343  $tables_result = $this->query('SHOW TABLE STATUS FROM `' . $this->databaseName . '`');
1344  if ($tables_result !== FALSE) {
1345  while ($theTable = $tables_result->fetch_assoc()) {
1346  $whichTables[$theTable['Name']] = $theTable;
1347  }
1348  $tables_result->free();
1349  }
1350  return $whichTables;
1351  }
1352 
1364  public function admin_get_fields($tableName) {
1365  $output = array();
1366  $columns_res = $this->query('SHOW COLUMNS FROM `' . $tableName . '`');
1367  if ($columns_res !== FALSE) {
1368  while ($fieldRow = $columns_res->fetch_assoc()) {
1369  $output[$fieldRow['Field']] = $fieldRow;
1370  }
1371  $columns_res->free();
1372  }
1373  return $output;
1374  }
1375 
1383  public function admin_get_keys($tableName) {
1384  $output = array();
1385  $keyRes = $this->query('SHOW KEYS FROM `' . $tableName . '`');
1386  if ($keyRes !== FALSE) {
1387  while ($keyRow = $keyRes->fetch_assoc()) {
1388  $output[] = $keyRow;
1389  }
1390  $keyRes->free();
1391  }
1392  return $output;
1393  }
1394 
1407  public function admin_get_charsets() {
1408  $output = array();
1409  $columns_res = $this->query('SHOW CHARACTER SET');
1410  if ($columns_res !== FALSE) {
1411  while ($row = $columns_res->fetch_assoc()) {
1412  $output[$row['Charset']] = $row;
1413  }
1414  $columns_res->free();
1415  }
1416  return $output;
1417  }
1418 
1425  public function admin_query($query) {
1426  $res = $this->query($query);
1427  if ($this->debugOutput) {
1428  $this->debug('admin_query', $query);
1429  }
1430  return $res;
1431  }
1432 
1433  /******************************
1434  *
1435  * Connect handling
1436  *
1437  ******************************/
1438 
1444  public function setDatabaseHost($host = 'localhost') {
1445  $this->disconnectIfConnected();
1446  $this->databaseHost = $host;
1447  }
1448 
1454  public function setDatabasePort($port = 3306) {
1455  $this->disconnectIfConnected();
1456  $this->databasePort = (int)$port;
1457  }
1458 
1464  public function setDatabaseSocket($socket = NULL) {
1465  $this->disconnectIfConnected();
1466  $this->databaseSocket = $socket;
1467  }
1468 
1474  public function setDatabaseName($name) {
1475  $this->disconnectIfConnected();
1476  $this->databaseName = $name;
1477  }
1478 
1484  public function setDatabaseUsername($username) {
1485  $this->disconnectIfConnected();
1486  $this->databaseUsername = $username;
1487  }
1488 
1494  public function setDatabasePassword($password) {
1495  $this->disconnectIfConnected();
1496  $this->databaseUserPassword = $password;
1497  }
1498 
1505  public function setPersistentDatabaseConnection($persistentDatabaseConnection) {
1506  $this->disconnectIfConnected();
1507  $this->persistentDatabaseConnection = (bool)$persistentDatabaseConnection;
1508  }
1509 
1515  public function setConnectionCompression($connectionCompression) {
1516  $this->disconnectIfConnected();
1517  $this->connectionCompression = (bool)$connectionCompression;
1518  }
1519 
1525  public function setInitializeCommandsAfterConnect(array $commands) {
1526  $this->disconnectIfConnected();
1527  $this->initializeCommandsAfterConnect = $commands;
1528  }
1529 
1539  public function setConnectionCharset($connectionCharset = 'utf8') {
1540  $this->disconnectIfConnected();
1541  $this->connectionCharset = $connectionCharset;
1542  }
1543 
1556  public function connectDB($host = NULL, $username = NULL, $password = NULL, $db = NULL) {
1557  // Early return if connected already
1558  if ($this->isConnected) {
1559  return;
1560  }
1561 
1562  if (!$this->databaseName && !$db) {
1563  throw new \RuntimeException(
1564  'TYPO3 Fatal Error: No database selected!',
1565  1270853882
1566  );
1567  }
1568 
1569  if ($host || $username || $password || $db) {
1570  $this->handleDeprecatedConnectArguments($host, $username, $password, $db);
1571  }
1572 
1573  if ($this->sql_pconnect()) {
1574  if (!$this->sql_select_db()) {
1575  throw new \RuntimeException(
1576  'TYPO3 Fatal Error: Cannot connect to the current database, "' . $this->databaseName . '"!',
1577  1270853883
1578  );
1579  }
1580  } else {
1581  throw new \RuntimeException(
1582  'TYPO3 Fatal Error: The current username, password or host was not accepted when the connection to the database was attempted to be established!',
1583  1270853884
1584  );
1585  }
1586 
1587  // Prepare user defined objects (if any) for hooks which extend query methods
1588  $this->preProcessHookObjects = array();
1589  $this->postProcessHookObjects = array();
1590  if (is_array($GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_db.php']['queryProcessors'])) {
1591  foreach ($GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_db.php']['queryProcessors'] as $classRef) {
1592  $hookObject = GeneralUtility::getUserObj($classRef);
1593  if (!(
1594  $hookObject instanceof PreProcessQueryHookInterface
1595  || $hookObject instanceof PostProcessQueryHookInterface
1596  )) {
1597  throw new \UnexpectedValueException(
1598  '$hookObject must either implement interface TYPO3\\CMS\\Core\\Database\\PreProcessQueryHookInterface or interface TYPO3\\CMS\\Core\\Database\\PostProcessQueryHookInterface',
1599  1299158548
1600  );
1601  }
1602  if ($hookObject instanceof PreProcessQueryHookInterface) {
1603  $this->preProcessHookObjects[] = $hookObject;
1604  }
1605  if ($hookObject instanceof PostProcessQueryHookInterface) {
1606  $this->postProcessHookObjects[] = $hookObject;
1607  }
1608  }
1609  }
1610  }
1611 
1617  public function isConnected() {
1618  // We think we're still connected
1619  if ($this->isConnected) {
1620  // Check if this is really the case or if the database server has gone away for some reason
1621  $this->isConnected = $this->link->ping();
1622  }
1623  return $this->isConnected;
1624  }
1625 
1640  protected function checkConnectionCharset() {
1641  $sessionResult = $this->sql_query('SHOW SESSION VARIABLES LIKE \'character_set%\'');
1642 
1643  if ($sessionResult === FALSE) {
1644  GeneralUtility::sysLog(
1645  'Error while retrieving the current charset session variables from the database: ' . $this->sql_error(),
1646  'Core',
1648  );
1649  throw new \RuntimeException(
1650  'TYPO3 Fatal Error: Could not determine the current charset of the database.',
1651  1381847136
1652  );
1653  }
1654 
1655  $charsetVariables = array();
1656  while (($row = $this->sql_fetch_row($sessionResult)) !== FALSE) {
1657  $variableName = $row[0];
1658  $variableValue = $row[1];
1659  $charsetVariables[$variableName] = $variableValue;
1660  }
1661  $this->sql_free_result($sessionResult);
1662 
1663  // These variables are set with the "Set names" command which was
1664  // used in the past. This is why we check them.
1665  $charsetRequiredVariables = array(
1666  'character_set_client',
1667  'character_set_results',
1668  'character_set_connection',
1669  );
1670 
1671  $hasValidCharset = TRUE;
1672  foreach ($charsetRequiredVariables as $variableName) {
1673  if (empty($charsetVariables[$variableName])) {
1674  GeneralUtility::sysLog(
1675  'A required session variable is missing in the current MySQL connection: ' . $variableName,
1676  'Core',
1678  );
1679  throw new \RuntimeException(
1680  'TYPO3 Fatal Error: Could not determine the value of the database session variable: ' . $variableName,
1681  1381847779
1682  );
1683  }
1684 
1685  if ($charsetVariables[$variableName] !== $this->connectionCharset) {
1686  $hasValidCharset = FALSE;
1687  break;
1688  }
1689  }
1690 
1691  if (!$hasValidCharset) {
1692  throw new \RuntimeException(
1693  'It looks like the character set ' . $this->connectionCharset . ' is not used for this connection even though it is configured as connection charset. ' .
1694  'This TYPO3 installation is using the $GLOBALS[\'TYPO3_CONF_VARS\'][\'SYS\'][\'setDBinit\'] property with the following value: "' .
1695  $GLOBALS['TYPO3_CONF_VARS']['SYS']['setDBinit'] . '". Please make sure that this command does not overwrite the configured charset. ' .
1696  'Please note that for the TYPO3 database everything other than utf8 is unsupported since version 4.7.',
1697  1389697515
1698  );
1699  }
1700  }
1701 
1707  protected function disconnectIfConnected() {
1708  if ($this->isConnected) {
1709  $this->link->close();
1710  $this->isConnected = FALSE;
1711  }
1712  }
1713 
1719  public function getDatabaseHandle() {
1720  return $this->link;
1721  }
1722 
1728  public function setDatabaseHandle($handle) {
1729  $this->link = $handle;
1730  }
1731 
1740  protected function handleDeprecatedConnectArguments($host = NULL, $username = NULL, $password = NULL, $db = NULL) {
1742  'DatabaseConnection->sql_pconnect() and DatabaseConnection->connectDB() should be ' .
1743  'called without arguments. Use the setters instead.'
1744  );
1745  if ($host) {
1746  if (strpos($host, ':') > 0) {
1747  list($databaseHost, $databasePort) = explode(':', $host);
1748  $this->setDatabaseHost($databaseHost);
1749  $this->setDatabasePort($databasePort);
1750  } else {
1751  $this->setDatabaseHost($host);
1752  }
1753  }
1754  if ($username) {
1755  $this->setDatabaseUsername($username);
1756  }
1757  if ($password) {
1758  $this->setDatabasePassword($password);
1759  }
1760  if ($db) {
1761  $this->setDatabaseName($db);
1762  }
1763  }
1764 
1765  /******************************
1766  *
1767  * Debugging
1768  *
1769  ******************************/
1778  public function debug($func, $query = '') {
1779  $error = $this->sql_error();
1780  if ($error || (int)$this->debugOutput === 2) {
1782  array(
1783  'caller' => 'TYPO3\\CMS\\Core\\Database\\DatabaseConnection::' . $func,
1784  'ERROR' => $error,
1785  'lastBuiltQuery' => $query ? $query : $this->debug_lastBuiltQuery,
1786  'debug_backtrace' => \TYPO3\CMS\Core\Utility\DebugUtility::debugTrail()
1787  ),
1788  $func,
1789  is_object($GLOBALS['error']) && @is_callable(array($GLOBALS['error'], 'debug'))
1790  ? ''
1791  : 'DB Error'
1792  );
1793  }
1794  }
1795 
1803  public function debug_check_recordset($res) {
1804  if ($res !== FALSE) {
1805  return TRUE;
1806  }
1807  $msg = 'Invalid database result detected';
1808  $trace = debug_backtrace();
1809  array_shift($trace);
1810  $cnt = count($trace);
1811  for ($i = 0; $i < $cnt; $i++) {
1812  // Complete objects are too large for the log
1813  if (isset($trace['object'])) {
1814  unset($trace['object']);
1815  }
1816  }
1817  $msg .= ': function TYPO3\\CMS\\Core\\Database\\DatabaseConnection->' . $trace[0]['function'] . ' called from file ' . substr($trace[0]['file'], (strlen(PATH_site) + 2)) . ' in line ' . $trace[0]['line'];
1818  GeneralUtility::sysLog(
1819  $msg . '. Use a devLog extension to get more details.',
1820  'Core/t3lib_db',
1822  );
1823  // Send to devLog if enabled
1824  if (TYPO3_DLOG) {
1825  $debugLogData = array(
1826  'SQL Error' => $this->sql_error(),
1827  'Backtrace' => $trace
1828  );
1829  if ($this->debug_lastBuiltQuery) {
1830  $debugLogData = array('SQL Query' => $this->debug_lastBuiltQuery) + $debugLogData;
1831  }
1832  GeneralUtility::devLog($msg . '.', 'Core/t3lib_db', 3, $debugLogData);
1833  }
1834  return FALSE;
1835  }
1836 
1849  protected function explain($query, $from_table, $row_count) {
1850  $debugAllowedForIp = GeneralUtility::cmpIP(
1851  GeneralUtility::getIndpEnv('REMOTE_ADDR'),
1852  $GLOBALS['TYPO3_CONF_VARS']['SYS']['devIPmask']
1853  );
1854  if (
1855  (int)$this->explainOutput == 1
1856  || ((int)$this->explainOutput == 2 && $debugAllowedForIp)
1857  ) {
1858  // Raw HTML output
1859  $explainMode = 1;
1860  } elseif ((int)$this->explainOutput == 3 && is_object($GLOBALS['TT'])) {
1861  // Embed the output into the TS admin panel
1862  $explainMode = 2;
1863  } else {
1864  return FALSE;
1865  }
1866  $error = $this->sql_error();
1867  $trail = \TYPO3\CMS\Core\Utility\DebugUtility::debugTrail();
1868  $explain_tables = array();
1869  $explain_output = array();
1870  $res = $this->sql_query('EXPLAIN ' . $query, $this->link);
1871  if (is_a($res, '\\mysqli_result')) {
1872  while ($tempRow = $this->sql_fetch_assoc($res)) {
1873  $explain_output[] = $tempRow;
1874  $explain_tables[] = $tempRow['table'];
1875  }
1876  $this->sql_free_result($res);
1877  }
1878  $indices_output = array();
1879  // Notice: Rows are skipped if there is only one result, or if no conditions are set
1880  if (
1881  $explain_output[0]['rows'] > 1
1882  || GeneralUtility::inList('ALL', $explain_output[0]['type'])
1883  ) {
1884  // Only enable output if it's really useful
1885  $debug = TRUE;
1886  foreach ($explain_tables as $table) {
1887  $tableRes = $this->sql_query('SHOW TABLE STATUS LIKE \'' . $table . '\'');
1888  $isTable = $this->sql_num_rows($tableRes);
1889  if ($isTable) {
1890  $res = $this->sql_query('SHOW INDEX FROM ' . $table, $this->link);
1891  if (is_a($res, '\\mysqli_result')) {
1892  while ($tempRow = $this->sql_fetch_assoc($res)) {
1893  $indices_output[] = $tempRow;
1894  }
1895  $this->sql_free_result($res);
1896  }
1897  }
1898  $this->sql_free_result($tableRes);
1899  }
1900  } else {
1901  $debug = FALSE;
1902  }
1903  if ($debug) {
1904  if ($explainMode) {
1905  $data = array();
1906  $data['query'] = $query;
1907  $data['trail'] = $trail;
1908  $data['row_count'] = $row_count;
1909  if ($error) {
1910  $data['error'] = $error;
1911  }
1912  if (count($explain_output)) {
1913  $data['explain'] = $explain_output;
1914  }
1915  if (count($indices_output)) {
1916  $data['indices'] = $indices_output;
1917  }
1918  if ($explainMode == 1) {
1919  \TYPO3\CMS\Core\Utility\DebugUtility::debug($data, 'Tables: ' . $from_table, 'DB SQL EXPLAIN');
1920  } elseif ($explainMode == 2) {
1921  $GLOBALS['TT']->setTSselectQuery($data);
1922  }
1923  }
1924  return TRUE;
1925  }
1926  return FALSE;
1927  }
1928 
1934  public function __sleep() {
1935  $this->disconnectIfConnected();
1936  return array(
1937  'debugOutput',
1938  'explainOutput',
1939  'databaseHost',
1940  'databasePort',
1941  'databaseSocket',
1942  'databaseName',
1943  'databaseUsername',
1944  'databaseUserPassword',
1945  'persistentDatabaseConnection',
1946  'connectionCompression',
1947  'initializeCommandsAfterConnect',
1948  'default_charset',
1949  );
1950  }
1951 }