t3lib/class.t3lib_db.php

   1 <?php
   2 /***************************************************************
   3 *  Copyright notice
   4 *
   5 *  (c) 2004-2010 Kasper Skårhøj (kasperYYYY@typo3.com)
   6 *  All rights reserved
   7 *
   8 *  This script is part of the TYPO3 project. The TYPO3 project is
   9 *  free software; you can redistribute it and/or modify
  10 *  it under the terms of the GNU General Public License as published by
  11 *  the Free Software Foundation; either version 2 of the License, or
  12 *  (at your option) any later version.
  13 *
  14 *  The GNU General Public License can be found at
  15 *  http://www.gnu.org/copyleft/gpl.html.
  16 *  A copy is found in the textfile GPL.txt and important notices to the license
  17 *  from the author is found in LICENSE.txt distributed with these scripts.
  18 *
  19 *
  20 *  This script is distributed in the hope that it will be useful,
  21 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
  22 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  23 *  GNU General Public License for more details.
  24 *
  25 *  This copyright notice MUST APPEAR in all copies of the script!
  26 ***************************************************************/
  27 /**
  28  * Contains the class "t3lib_db" containing functions for building SQL queries
  29  * and mysql wrappers, thus providing a foundational API to all database
  30  * interaction.
  31  * This class is instantiated globally as $TYPO3_DB in TYPO3 scripts.
  32  *
  33  * $Id$
  34  *
  35  * @author      Kasper Skårhøj <kasperYYYY@typo3.com>
  36  */
  37 /**
  38  * [CLASS/FUNCTION INDEX of SCRIPT]
  39  *
  40  *
  41  *
  42  *  138: class t3lib_DB
  43  *
  44  *              SECTION: Query execution
  45  *  175:     function exec_INSERTquery($table,$fields_values,$no_quote_fields=FALSE)
  46  *  192:     function exec_UPDATEquery($table,$where,$fields_values,$no_quote_fields=FALSE)
  47  *  206:     function exec_DELETEquery($table,$where)
  48  *  225:     function exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='')
  49  *  250:     function exec_SELECT_mm_query($select,$local_table,$mm_table,$foreign_table,$whereClause='',$groupBy='',$orderBy='',$limit='')
  50  *  278:     function exec_SELECT_queryArray($queryParts)
  51  *  301:     function exec_SELECTgetRows($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='',$uidIndexField='')
  52  *
  53  *              SECTION: Query building
  54  *  346:     function INSERTquery($table,$fields_values,$no_quote_fields=FALSE)
  55  *  381:     function UPDATEquery($table,$where,$fields_values,$no_quote_fields=FALSE)
  56  *  422:     function DELETEquery($table,$where)
  57  *  451:     function SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='')
  58  *  492:     function listQuery($field, $value, $table)
  59  *  506:     function searchQuery($searchWords,$fields,$table)
  60  *
  61  *              SECTION: Various helper functions
  62  *  552:     function fullQuoteStr($str, $table)
  63  *  569:     function fullQuoteArray($arr, $table, $noQuote=FALSE)
  64  *  596:     function quoteStr($str, $table)
  65  *  612:     function escapeStrForLike($str, $table)
  66  *  625:     function cleanIntArray($arr)
  67  *  641:     function cleanIntList($list)
  68  *  655:     function stripOrderBy($str)
  69  *  669:     function stripGroupBy($str)
  70  *  681:     function splitGroupOrderLimit($str)
  71  *
  72  *              SECTION: MySQL wrapper functions
  73  *  749:     function sql($db,$query)
  74  *  763:     function sql_query($query)
  75  *  776:     function sql_error()
  76  *  788:     function sql_num_rows($res)
  77  *  800:     function sql_fetch_assoc($res)
  78  *  813:     function sql_fetch_row($res)
  79  *  825:     function sql_free_result($res)
  80  *  836:     function sql_insert_id()
  81  *  847:     function sql_affected_rows()
  82  *  860:     function sql_data_seek($res,$seek)
  83  *  873:     function sql_field_type($res,$pointer)
  84  *  887:     function sql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password)
  85  *  915:     function sql_select_db($TYPO3_db)
  86  *
  87  *              SECTION: SQL admin functions
  88  *  947:     function admin_get_dbs()
  89  *  965:     function admin_get_tables()
  90  *  984:     function admin_get_fields($tableName)
  91  * 1002:     function admin_get_keys($tableName)
  92  * 1020:     function admin_query($query)
  93  *
  94  *              SECTION: Connecting service
  95  * 1048:     function connectDB()
  96  *
  97  *              SECTION: Debugging
  98  * 1086:     function debug($func)
  99  *
 100  * TOTAL FUNCTIONS: 42
 101  * (This index is automatically created/updated by the extension "extdeveval")
 102  *
 103  */
 104
 105
 106
 107
 108
 109
 110
 111
 112
 113
 114
 115
 116 /**
 117  * TYPO3 "database wrapper" class (new in 3.6.0)
 118  * This class contains
 119  * - abstraction functions for executing INSERT/UPDATE/DELETE/SELECT queries ("Query execution"; These are REQUIRED for all future connectivity to the database, thus ensuring DBAL compliance!)
 120  * - functions for building SQL queries (INSERT/UPDATE/DELETE/SELECT) ("Query building"); These are transitional functions for building SQL queries in a more automated way. Use these to build queries instead of doing it manually in your code!
 121  * - mysql() wrapper functions; These are transitional functions. By a simple search/replace you should be able to substitute all mysql*() calls with $GLOBALS['TYPO3_DB']->sql*() and your application will work out of the box. YOU CANNOT (legally) use any mysql functions not found as wrapper functions in this class!
 122  * See the Project Coding Guidelines (doc_core_cgl) for more instructions on best-practise
 123  *
 124  * This class is not in itself a complete database abstraction layer but can be extended to be a DBAL (by extensions, see "dbal" for example)
 125  * ALL connectivity to the database in TYPO3 must be done through this class!
 126  * The points of this class are:
 127  * - To direct all database calls through this class so it becomes possible to implement DBAL with extensions.
 128  * - To keep it very easy to use for developers used to MySQL in PHP - and preserve as much performance as possible when TYPO3 is used with MySQL directly...
 129  * - To create an interface for DBAL implemented by extensions; (Eg. making possible escaping characters, clob/blob handling, reserved words handling)
 130  * - Benchmarking the DB bottleneck queries will become much easier; Will make it easier to find optimization possibilities.
 131  *
 132  * USE:
 133  * In all TYPO3 scripts the global variable $TYPO3_DB is an instance of this class. Use that.
 134  * Eg.          $GLOBALS['TYPO3_DB']->sql_fetch_assoc()
 135  *
 136  * @author      Kasper Skårhøj <kasperYYYY@typo3.com>
 137  * @package TYPO3
 138  * @subpackage t3lib
 139  */
 140 class t3lib_DB {
 141
 142
 143                 // Debug:
 144         var $debugOutput = FALSE;               // Set "TRUE" if you want database errors outputted.
 145         var $debug_lastBuiltQuery = '';         // Internally: Set to last built query (not necessarily executed...)
 146         var $store_lastBuiltQuery = FALSE;      // Set "TRUE" if you want the last built query to be stored in $debug_lastBuiltQuery independent of $this->debugOutput
 147         var $explainOutput = 0;                 // Set this to 1 to get queries explained (devIPmask must match). Set the value to 2 to the same but disregarding the devIPmask. 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.
 148
 149                 // Default link identifier:
 150         var $link = FALSE;
 151
 152                 // Default character set, applies unless character set or collation are explicitely set
 153         var $default_charset = 'utf8';
 154
 155
 156
 157
 158         /************************************
 159          *
 160          * Query execution
 161          *
 162          * These functions are the RECOMMENDED DBAL functions for use in your applications
 163          * Using these functions will allow the DBAL to use alternative ways of accessing data (contrary to if a query is returned!)
 164          * They compile a query AND execute it immediately and then return the result
 165          * This principle heightens our ability to create various forms of DBAL of the functions.
 166          * Generally: We want to return a result pointer/object, never queries.
 167          * Also, having the table name together with the actual query execution allows us to direct the request to other databases.
 168          *
 169          **************************************/
 170
 171         /**
 172          * Creates and executes an INSERT SQL-statement for $table from the array with field/value pairs $fields_values.
 173          * Using this function specifically allows us to handle BLOB and CLOB fields depending on DB
 174          * Usage count/core: 47
 175          *
 176          * @param       string          Table name
 177          * @param       array           Field values as key=>value pairs. Values will be escaped internally. Typically you would fill an array like "$insertFields" with 'fieldname'=>'value' and pass it to this function as argument.
 178          * @param       string/array            See fullQuoteArray()
 179          * @return      pointer         MySQL result pointer / DBAL object
 180          */
 181         function exec_INSERTquery($table, $fields_values, $no_quote_fields = FALSE) {
 182                 $res = mysql_query($this->INSERTquery($table, $fields_values, $no_quote_fields), $this->link);
 183                 if ($this->debugOutput) {
 184                         $this->debug('exec_INSERTquery');
 185                 }
 186                 return $res;
 187         }
 188
 189         /**
 190          * Creates and executes an INSERT SQL-statement for $table with multiple rows.
 191          *
 192          * @param       string          Table name
 193          * @param       array           Field names
 194          * @param       array           Table rows. Each row should be an array with field values mapping to $fields
 195          * @param       string/array            See fullQuoteArray()
 196          * @return      pointer         MySQL result pointer / DBAL object
 197          */
 198         public function exec_INSERTmultipleRows($table, array $fields, array $rows, $no_quote_fields = FALSE) {
 199                 $res = mysql_query($this->INSERTmultipleRows($table, $fields, $rows, $no_quote_fields), $this->link);
 200                 if ($this->debugOutput) {
 201                         $this->debug('exec_INSERTmultipleRows');
 202                 }
 203                 return $res;
 204         }
 205
 206         /**
 207          * Creates and executes an UPDATE SQL-statement for $table where $where-clause (typ. 'uid=...') from the array with field/value pairs $fields_values.
 208          * Using this function specifically allow us to handle BLOB and CLOB fields depending on DB
 209          * Usage count/core: 50
 210          *
 211          * @param       string          Database tablename
 212          * @param       string          WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
 213          * @param       array           Field values as key=>value pairs. Values will be escaped internally. Typically you would fill an array like "$updateFields" with 'fieldname'=>'value' and pass it to this function as argument.
 214          * @param       string/array            See fullQuoteArray()
 215          * @return      pointer         MySQL result pointer / DBAL object
 216          */
 217         function exec_UPDATEquery($table, $where, $fields_values, $no_quote_fields = FALSE) {
 218                 $res = mysql_query($this->UPDATEquery($table, $where, $fields_values, $no_quote_fields), $this->link);
 219                 if ($this->debugOutput) {
 220                         $this->debug('exec_UPDATEquery');
 221                 }
 222                 return $res;
 223         }
 224
 225         /**
 226          * Creates and executes a DELETE SQL-statement for $table where $where-clause
 227          * Usage count/core: 40
 228          *
 229          * @param       string          Database tablename
 230          * @param       string          WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
 231          * @return      pointer         MySQL result pointer / DBAL object
 232          */
 233         function exec_DELETEquery($table, $where) {
 234                 $res = mysql_query($this->DELETEquery($table, $where), $this->link);
 235                 if ($this->debugOutput) {
 236                         $this->debug('exec_DELETEquery');
 237                 }
 238                 return $res;
 239         }
 240
 241         /**
 242          * Creates and executes a SELECT SQL-statement
 243          * Using this function specifically allow us to handle the LIMIT feature independently of DB.
 244          * Usage count/core: 340
 245          *
 246          * @param       string          List of fields to select from the table. This is what comes right after "SELECT ...". Required value.
 247          * @param       string          Table(s) from which to select. This is what comes right after "FROM ...". Required value.
 248          * @param       string          Optional additional WHERE clauses put in the end of the query. NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself! DO NOT PUT IN GROUP BY, ORDER BY or LIMIT!
 249          * @param       string          Optional GROUP BY field(s), if none, supply blank string.
 250          * @param       string          Optional ORDER BY field(s), if none, supply blank string.
 251          * @param       string          Optional LIMIT value ([begin,]max), if none, supply blank string.
 252          * @return      pointer         MySQL result pointer / DBAL object
 253          */
 254         function exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '') {
 255                 $query = $this->SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
 256                 $res = mysql_query($query, $this->link);
 257
 258                 if ($this->debugOutput) {
 259                         $this->debug('exec_SELECTquery');
 260                 }
 261                 if ($this->explainOutput) {
 262                         $this->explain($query, $from_table, $this->sql_num_rows($res));
 263                 }
 264
 265                 return $res;
 266         }
 267
 268         /**
 269          * Creates and executes a SELECT query, selecting fields ($select) from two/three tables joined
 270          * Use $mm_table together with $local_table or $foreign_table to select over two tables. Or use all three tables to select the full MM-relation.
 271          * The JOIN is done with [$local_table].uid <--> [$mm_table].uid_local  / [$mm_table].uid_foreign <--> [$foreign_table].uid
 272          * The function is very useful for selecting MM-relations between tables adhering to the MM-format used by TCE (TYPO3 Core Engine). See the section on $TCA in Inside TYPO3 for more details.
 273          *
 274          * Usage: 12 (spec. ext. sys_action, sys_messages, sys_todos)
 275          *
 276          * @param       string          Field list for SELECT
 277          * @param       string          Tablename, local table
 278          * @param       string          Tablename, relation table
 279          * @param       string          Tablename, foreign table
 280          * @param       string          Optional additional WHERE clauses put in the end of the query. NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself! DO NOT PUT IN GROUP BY, ORDER BY or LIMIT! You have to prepend 'AND ' to this parameter yourself!
 281          * @param       string          Optional GROUP BY field(s), if none, supply blank string.
 282          * @param       string          Optional ORDER BY field(s), if none, supply blank string.
 283          * @param       string          Optional LIMIT value ([begin,]max), if none, supply blank string.
 284          * @return      pointer         MySQL result pointer / DBAL object
 285          * @see exec_SELECTquery()
 286          */
 287         function exec_SELECT_mm_query($select, $local_table, $mm_table, $foreign_table, $whereClause = '', $groupBy = '', $orderBy = '', $limit = '') {
 288                 if ($foreign_table == $local_table) {
 289                         $foreign_table_as = $foreign_table . uniqid('_join');
 290                 }
 291
 292                 $mmWhere = $local_table ? $local_table . '.uid=' . $mm_table . '.uid_local' : '';
 293                 $mmWhere .= ($local_table AND $foreign_table) ? ' AND ' : '';
 294
 295                 $tables = ($local_table ? $local_table . ',' : '') . $mm_table;
 296
 297                 if ($foreign_table) {
 298                         $mmWhere .= ($foreign_table_as ? $foreign_table_as : $foreign_table) . '.uid=' . $mm_table . '.uid_foreign';
 299                         $tables .= ',' . $foreign_table . ($foreign_table_as ? ' AS ' . $foreign_table_as : '');
 300                 }
 301
 302                 return $this->exec_SELECTquery(
 303                                         $select,
 304                                         $tables,
 305                                                 // whereClauseMightContainGroupOrderBy
 306                                         $mmWhere . ' ' . $whereClause,
 307                                         $groupBy,
 308                                         $orderBy,
 309                                         $limit
 310                                 );
 311         }
 312
 313         /**
 314          * Executes a select based on input query parts array
 315          *
 316          * Usage: 9
 317          *
 318          * @param       array           Query parts array
 319          * @return      pointer         MySQL select result pointer / DBAL object
 320          * @see exec_SELECTquery()
 321          */
 322         function exec_SELECT_queryArray($queryParts) {
 323                 return $this->exec_SELECTquery(
 324                         $queryParts['SELECT'],
 325                         $queryParts['FROM'],
 326                         $queryParts['WHERE'],
 327                         $queryParts['GROUPBY'],
 328                         $queryParts['ORDERBY'],
 329                         $queryParts['LIMIT']
 330                 );
 331         }
 332
 333         /**
 334          * Creates and executes a SELECT SQL-statement AND traverse result set and returns array with records in.
 335          *
 336          * @param       string          See exec_SELECTquery()
 337          * @param       string          See exec_SELECTquery()
 338          * @param       string          See exec_SELECTquery()
 339          * @param       string          See exec_SELECTquery()
 340          * @param       string          See exec_SELECTquery()
 341          * @param       string          See exec_SELECTquery()
 342          * @param       string          If set, the result array will carry this field names value as index. Requires that field to be selected of course!
 343          * @return      array           Array of rows.
 344          */
 345         function exec_SELECTgetRows($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', $uidIndexField = '') {
 346                 $res = $this->exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
 347                 if ($this->debugOutput) {
 348                         $this->debug('exec_SELECTquery');
 349                 }
 350
 351                 if (!$this->sql_error()) {
 352                         $output = array();
 353
 354                         if ($uidIndexField) {
 355                                 while ($tempRow = $this->sql_fetch_assoc($res)) {
 356                                         $output[$tempRow[$uidIndexField]] = $tempRow;
 357                                 }
 358                         } else {
 359                                 while ($output[] = $this->sql_fetch_assoc($res));
 360                                 array_pop($output);
 361                         }
 362                         $this->sql_free_result($res);
 363                 }
 364                 return $output;
 365         }
 366
 367         /**
 368          * Counts the number of rows in a table.
 369          *
 370          * @param       string          $field: Name of the field to use in the COUNT() expression (e.g. '*')
 371          * @param       string          $table: Name of the table to count rows for
 372          * @param       string          $where: (optional) WHERE statement of the query
 373          * @return      mixed           Number of rows counter (integer) or false if something went wrong (boolean)
 374          */
 375         public function exec_SELECTcountRows($field, $table, $where = '') {
 376                 $count = false;
 377                 $resultSet = $this->exec_SELECTquery('COUNT(' . $field . ')', $table, $where);
 378                 if ($resultSet !== false) {
 379                         list($count) = $this->sql_fetch_row($resultSet);
 380                         $this->sql_free_result($resultSet);
 381                 }
 382                 return $count;
 383         }
 384
 385         /**
 386          * Truncates a table.
 387          *
 388          * @param       string          Database tablename
 389          * @return      mixed           Result from handler
 390          */
 391         public function exec_TRUNCATEquery($table) {
 392                 $res = mysql_query($this->TRUNCATEquery($table), $this->link);
 393                 if ($this->debugOutput) {
 394                         $this->debug('exec_TRUNCATEquery');
 395                 }
 396                 return $res;
 397         }
 398
 399
 400
 401
 402
 403
 404
 405
 406
 407
 408
 409         /**************************************
 410          *
 411          * Query building
 412          *
 413          **************************************/
 414
 415         /**
 416          * Creates an INSERT SQL-statement for $table from the array with field/value pairs $fields_values.
 417          * Usage count/core: 4
 418          *
 419          * @param       string          See exec_INSERTquery()
 420          * @param       array           See exec_INSERTquery()
 421          * @param       string/array            See fullQuoteArray()
 422          * @return      string          Full SQL query for INSERT (unless $fields_values does not contain any elements in which case it will be false)
 423          */
 424         function INSERTquery($table, $fields_values, $no_quote_fields = FALSE) {
 425
 426                         // Table and fieldnames should be "SQL-injection-safe" when supplied to this
 427                         // function (contrary to values in the arrays which may be insecure).
 428                 if (is_array($fields_values) && count($fields_values)) {
 429
 430                                 // quote and escape values
 431                         $fields_values = $this->fullQuoteArray($fields_values, $table, $no_quote_fields);
 432
 433                                 // Build query:
 434                         $query = 'INSERT INTO ' . $table .
 435                                 '(' . implode(',', array_keys($fields_values)) . ') VALUES ' .
 436                                 '(' . implode(',', $fields_values) . ')';
 437
 438                                 // Return query:
 439                         if ($this->debugOutput || $this->store_lastBuiltQuery) {
 440                                 $this->debug_lastBuiltQuery = $query;
 441                         }
 442                         return $query;
 443                 }
 444         }
 445
 446         /**
 447          * Creates an INSERT SQL-statement for $table with multiple rows.
 448          *
 449          * @param       string          Table name
 450          * @param       array           Field names
 451          * @param       array           Table rows. Each row should be an array with field values mapping to $fields
 452          * @param       string/array            See fullQuoteArray()
 453          * @return      string          Full SQL query for INSERT (unless $rows does not contain any elements in which case it will be false)
 454          */
 455         public function INSERTmultipleRows($table, array $fields, array $rows, $no_quote_fields = FALSE) {
 456                         // Table and fieldnames should be "SQL-injection-safe" when supplied to this
 457                         // function (contrary to values in the arrays which may be insecure).
 458                 if (count($rows)) {
 459                                 // Build query:
 460                         $query = 'INSERT INTO ' . $table .
 461                                 ' (' . implode(', ', $fields) . ') VALUES ';
 462
 463                         $rowSQL = array();
 464                         foreach ($rows as $row) {
 465                                         // quote and escape values
 466                                 $row = $this->fullQuoteArray($row, $table, $no_quote_fields);
 467                                 $rowSQL[] = '(' . implode(', ', $row) . ')';
 468                         }
 469
 470                         $query .= implode(', ', $rowSQL);
 471
 472                                 // Return query:
 473                         if ($this->debugOutput || $this->store_lastBuiltQuery) {
 474                                 $this->debug_lastBuiltQuery = $query;
 475                         }
 476
 477                         return $query;
 478                 }
 479         }
 480
 481         /**
 482          * Creates an UPDATE SQL-statement for $table where $where-clause (typ. 'uid=...') from the array with field/value pairs $fields_values.
 483          * Usage count/core: 6
 484          *
 485          * @param       string          See exec_UPDATEquery()
 486          * @param       string          See exec_UPDATEquery()
 487          * @param       array           See exec_UPDATEquery()
 488          * @param       array           See fullQuoteArray()
 489          * @return      string          Full SQL query for UPDATE
 490          */
 491         function UPDATEquery($table, $where, $fields_values, $no_quote_fields = FALSE) {
 492                         // Table and fieldnames should be "SQL-injection-safe" when supplied to this
 493                         // function (contrary to values in the arrays which may be insecure).
 494                 if (is_string($where)) {
 495                         $fields = array();
 496                         if (is_array($fields_values) && count($fields_values)) {
 497
 498                                         // quote and escape values
 499                                 $nArr = $this->fullQuoteArray($fields_values, $table, $no_quote_fields);
 500
 501                                 foreach ($nArr as $k => $v) {
 502                                         $fields[] = $k.'='.$v;
 503                                 }
 504                         }
 505
 506                                 // Build query:
 507                         $query = 'UPDATE ' . $table . ' SET ' . implode(',', $fields) .
 508                                 (strlen($where) > 0 ? ' WHERE ' . $where : '');
 509
 510                         if ($this->debugOutput || $this->store_lastBuiltQuery) {
 511                                 $this->debug_lastBuiltQuery = $query;
 512                         }
 513                         return $query;
 514                 } else {
 515                         throw new InvalidArgumentException(
 516                                 'TYPO3 Fatal Error: "Where" clause argument for UPDATE query was not a string in $this->UPDATEquery() !',
 517                                 1270853880
 518                         );
 519                 }
 520         }
 521
 522         /**
 523          * Creates a DELETE SQL-statement for $table where $where-clause
 524          * Usage count/core: 3
 525          *
 526          * @param       string          See exec_DELETEquery()
 527          * @param       string          See exec_DELETEquery()
 528          * @return      string          Full SQL query for DELETE
 529          */
 530         function DELETEquery($table, $where) {
 531                 if (is_string($where)) {
 532
 533                                 // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
 534                         $query = 'DELETE FROM ' . $table .
 535                                 (strlen($where) > 0 ? ' WHERE ' . $where : '');
 536
 537                         if ($this->debugOutput || $this->store_lastBuiltQuery) {
 538                                 $this->debug_lastBuiltQuery = $query;
 539                         }
 540                         return $query;
 541                 } else {
 542                         throw new InvalidArgumentException(
 543                                 'TYPO3 Fatal Error: "Where" clause argument for DELETE query was not a string in $this->DELETEquery() !',
 544                                 1270853881
 545                         );
 546                 }
 547         }
 548
 549         /**
 550          * Creates a SELECT SQL-statement
 551          * Usage count/core: 11
 552          *
 553          * @param       string          See exec_SELECTquery()
 554          * @param       string          See exec_SELECTquery()
 555          * @param       string          See exec_SELECTquery()
 556          * @param       string          See exec_SELECTquery()
 557          * @param       string          See exec_SELECTquery()
 558          * @param       string          See exec_SELECTquery()
 559          * @return      string          Full SQL query for SELECT
 560          */
 561         function SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '') {
 562
 563                         // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
 564                         // Build basic query:
 565                 $query = 'SELECT ' . $select_fields . ' FROM ' . $from_table .
 566                         (strlen($where_clause) > 0 ? ' WHERE ' . $where_clause : '');
 567
 568                         // Group by:
 569                 $query .= (strlen($groupBy) > 0 ? ' GROUP BY ' . $groupBy : '');
 570
 571                         // Order by:
 572                 $query .= (strlen($orderBy) > 0 ? ' ORDER BY ' . $orderBy : '');
 573
 574                         // Group by:
 575                 $query .= (strlen($limit) > 0 ? ' LIMIT ' . $limit : '');
 576
 577                         // Return query:
 578                 if ($this->debugOutput || $this->store_lastBuiltQuery) {
 579                         $this->debug_lastBuiltQuery = $query;
 580                 }
 581                 return $query;
 582         }
 583
 584         /**
 585          * Creates a SELECT SQL-statement to be used as subquery within another query.
 586          * BEWARE: This method should not be overriden within DBAL to prevent quoting from happening.
 587          *
 588          * @param       string          $select_fields: List of fields to select from the table.
 589          * @param       string          $from_table: Table from which to select.
 590          * @param       string          $where_clause: Conditional WHERE statement
 591          * @return      string          Full SQL query for SELECT
 592          */
 593         public function SELECTsubquery($select_fields, $from_table, $where_clause) {
 594                         // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
 595                         // Build basic query:
 596                 $query = 'SELECT ' . $select_fields . ' FROM ' . $from_table .
 597                         (strlen($where_clause) > 0 ? ' WHERE ' . $where_clause : '');
 598
 599                         // Return query:
 600                 if ($this->debugOutput || $this->store_lastBuiltQuery) {
 601                         $this->debug_lastBuiltQuery = $query;
 602                 }
 603
 604                 return $query;
 605         }
 606
 607         /**
 608          * Creates a TRUNCATE TABLE SQL-statement
 609          *
 610          * @param       string          See exec_TRUNCATEquery()
 611          * @return      string          Full SQL query for TRUNCATE TABLE
 612          */
 613         public function TRUNCATEquery($table) {
 614                         // Table should be "SQL-injection-safe" when supplied to this function
 615                         // Build basic query:
 616                 $query = 'TRUNCATE TABLE ' . $table;
 617
 618                         // Return query:
 619                 if ($this->debugOutput || $this->store_lastBuiltQuery) {
 620                         $this->debug_lastBuiltQuery = $query;
 621                 }
 622
 623                 return $query;
 624         }
 625
 626         /**
 627          * Returns a WHERE clause that can find a value ($value) in a list field ($field)
 628          * For instance a record in the database might contain a list of numbers,
 629          * "34,234,5" (with no spaces between). This query would be able to select that
 630          * record based on the value "34", "234" or "5" regardless of their position in
 631          * the list (left, middle or right).
 632          * The value must not contain a comma (,)
 633          * Is nice to look up list-relations to records or files in TYPO3 database tables.
 634          *
 635          * @param       string          Field name
 636          * @param       string          Value to find in list
 637          * @param       string          Table in which we are searching (for DBAL detection of quoteStr() method)
 638          * @return      string          WHERE clause for a query
 639          */
 640         public function listQuery($field, $value, $table) {
 641                 $value = (string)$value;
 642                 if (strpos(',', $value) !== FALSE) {
 643                         throw new InvalidArgumentException('$value must not contain a comma (,) in $this->listQuery() !');
 644                 }
 645                 $pattern = $this->quoteStr($value, $table);
 646                 $where = 'FIND_IN_SET(\'' . $pattern . '\',' . $field . ')';
 647                 return $where;
 648         }
 649
 650         /**
 651          * Returns a WHERE clause which will make an AND search for the words in the $searchWords array in any of the fields in array $fields.
 652          *
 653          * @param       array           Array of search words
 654          * @param       array           Array of fields
 655          * @param       string          Table in which we are searching (for DBAL detection of quoteStr() method)
 656          * @return      string          WHERE clause for search
 657          */
 658         function searchQuery($searchWords, $fields, $table) {
 659                 $queryParts = array();
 660
 661                 foreach($searchWords as $sw) {
 662                         $like = ' LIKE \'%' . $this->quoteStr($sw, $table) . '%\'';
 663                         $queryParts[] = $table . '.' . implode($like . ' OR ' . $table . '.', $fields) . $like;
 664                 }
 665                 $query = '(' . implode(') AND (', $queryParts) . ')';
 666                 return $query ;
 667         }
 668
 669
 670
 671
 672
 673
 674
 675
 676
 677
 678
 679         /**************************************
 680          *
 681          * Prepared Query Support
 682          *
 683          **************************************/
 684
 685         /**
 686          * Creates a SELECT prepared SQL statement.
 687          *
 688          * @param string See exec_SELECTquery()
 689          * @param string See exec_SELECTquery()
 690          * @param string See exec_SELECTquery()
 691          * @param string See exec_SELECTquery()
 692          * @param string See exec_SELECTquery()
 693          * @param string See exec_SELECTquery()
 694          * @param array $input_parameters An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as t3lib_db_PreparedStatement::PARAM_AUTOTYPE.
 695          * @return t3lib_db_PreparedStatement Prepared statement
 696          */
 697         public function prepare_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', array $input_parameters = array()) {
 698                 $query = $this->SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
 699                 $preparedStatement = t3lib_div::makeInstance('t3lib_db_PreparedStatement', $query, $from_table, array());
 700                 /* @var $preparedStatement t3lib_db_PreparedStatement */
 701
 702                         // Bind values to parameters
 703                 foreach ($input_parameters as $key => $value) {
 704                         $preparedStatement->bindValue($key, $value, t3lib_db_PreparedStatement::PARAM_AUTOTYPE);
 705                 }
 706
 707                         // Return prepared statement
 708                 return $preparedStatement;
 709         }
 710
 711         /**
 712          * Creates a SELECT prepared SQL statement based on input query parts array
 713          *
 714          * @param array Query parts array
 715          * @param array $input_parameters An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as t3lib_db_PreparedStatement::PARAM_AUTOTYPE.
 716          * @return t3lib_db_PreparedStatement Prepared statement
 717          */
 718         public function prepare_SELECTqueryArray(array $queryParts, array $input_parameters = array()) {
 719                 return $this->prepare_SELECTquery(
 720                         $queryParts['SELECT'],
 721                         $queryParts['FROM'],
 722                         $queryParts['WHERE'],
 723                         $queryParts['GROUPBY'],
 724                         $queryParts['ORDERBY'],
 725                         $queryParts['LIMIT'],
 726                         $input_parameters
 727                 );
 728         }
 729
 730         /**
 731          * Executes a prepared query.
 732          * This method may only be called by t3lib_db_PreparedStatement.
 733          *
 734          * @param string $query The query to execute
 735          * @param array $queryComponents The components of the query to execute
 736          * @return pointer MySQL result pointer / DBAL object
 737          * @access private
 738          */
 739         public function exec_PREPAREDquery($query, array $queryComponents) {
 740                 $res = mysql_query($query, $this->link);
 741                 if ($this->debugOutput) {
 742                         $this->debug('stmt_execute', $query);
 743                 }
 744                 return $res;
 745         }
 746
 747
 748
 749
 750
 751
 752
 753
 754
 755
 756
 757         /**************************************
 758          *
 759          * Various helper functions
 760          *
 761          * Functions recommended to be used for
 762          * - escaping values,
 763          * - cleaning lists of values,
 764          * - stripping of excess ORDER BY/GROUP BY keywords
 765          *
 766          **************************************/
 767
 768         /**
 769          * Escaping and quoting values for SQL statements.
 770          * Usage count/core: 100
 771          *
 772          * @param       string          Input string
 773          * @param       string          Table name for which to quote string. Just enter the table that the field-value is selected from (and any DBAL will look up which handler to use and then how to quote the string!).
 774          * @return      string          Output string; Wrapped in single quotes and quotes in the string (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
 775          * @see quoteStr()
 776          */
 777         function fullQuoteStr($str, $table) {
 778                 return '\'' . mysql_real_escape_string($str, $this->link) . '\'';
 779         }
 780
 781         /**
 782          * Will fullquote all values in the one-dimensional array so they are ready to "implode" for an sql query.
 783          *
 784          * @param       array           Array with values (either associative or non-associative array)
 785          * @param       string          Table name for which to quote
 786          * @param       string/array            List/array of keys NOT to quote (eg. SQL functions) - ONLY for associative arrays
 787          * @return      array           The input array with the values quoted
 788          * @see cleanIntArray()
 789          */
 790         function fullQuoteArray($arr, $table, $noQuote = FALSE) {
 791                 if (is_string($noQuote)) {
 792                         $noQuote = explode(',', $noQuote);
 793                         // sanity check
 794                 } elseif (!is_array($noQuote)) {
 795                         $noQuote = FALSE;
 796                 }
 797
 798                 foreach($arr as $k => $v) {
 799                         if ($noQuote === FALSE || !in_array($k, $noQuote)) {
 800                                 $arr[$k] = $this->fullQuoteStr($v, $table);
 801                         }
 802                 }
 803                 return $arr;
 804         }
 805
 806         /**
 807          * Substitution for PHP function "addslashes()"
 808          * Use this function instead of the PHP addslashes() function when you build queries - this will prepare your code for DBAL.
 809          * NOTICE: You must wrap the output of this function in SINGLE QUOTES to be DBAL compatible. Unless you have to apply the single quotes yourself you should rather use ->fullQuoteStr()!
 810          *
 811          * Usage count/core: 20
 812          *
 813          * @param       string          Input string
 814          * @param       string          Table name for which to quote string. Just enter the table that the field-value is selected from (and any DBAL will look up which handler to use and then how to quote the string!).
 815          * @return      string          Output string; Quotes (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
 816          * @see quoteStr()
 817          */
 818         function quoteStr($str, $table) {
 819                 return mysql_real_escape_string($str, $this->link);
 820         }
 821
 822         /**
 823          * Escaping values for SQL LIKE statements.
 824          *
 825          * @param       string          Input string
 826          * @param       string          Table name for which to escape string. Just enter the table that the field-value is selected from (and any DBAL will look up which handler to use and then how to quote the string!).
 827          * @return      string          Output string; % and _ will be escaped with \ (or otherwise based on DBAL handler)
 828          * @see quoteStr()
 829          */
 830         function escapeStrForLike($str, $table) {
 831                 return addcslashes($str, '_%');
 832         }
 833
 834         /**
 835          * Will convert all values in the one-dimensional array to integers.
 836          * Useful when you want to make sure an array contains only integers before imploding them in a select-list.
 837          * Usage count/core: 7
 838          *
 839          * @param       array           Array with values
 840          * @return      array           The input array with all values passed through intval()
 841          * @see cleanIntList()
 842          */
 843         function cleanIntArray($arr) {
 844                 foreach($arr as $k => $v) {
 845                         $arr[$k] = intval($arr[$k]);
 846                 }
 847                 return $arr;
 848         }
 849
 850         /**
 851          * Will force all entries in the input comma list to integers
 852          * Useful when you want to make sure a commalist of supposed integers really contain only integers; You want to know that when you don't trust content that could go into an SQL statement.
 853          * Usage count/core: 6
 854          *
 855          * @param       string          List of comma-separated values which should be integers
 856          * @return      string          The input list but with every value passed through intval()
 857          * @see cleanIntArray()
 858          */
 859         function cleanIntList($list) {
 860                 return implode(',', t3lib_div::intExplode(',', $list));
 861         }
 862
 863         /**
 864          * Removes the prefix "ORDER BY" from the input string.
 865          * This function is used when you call the exec_SELECTquery() function and want to pass the ORDER BY parameter by can't guarantee that "ORDER BY" is not prefixed.
 866          * Generally; This function provides a work-around to the situation where you cannot pass only the fields by which to order the result.
 867          * Usage count/core: 11
 868          *
 869          * @param       string          eg. "ORDER BY title, uid"
 870          * @return      string          eg. "title, uid"
 871          * @see exec_SELECTquery(), stripGroupBy()
 872          */
 873         function stripOrderBy($str) {
 874                 return preg_replace('/^ORDER[[:space:]]+BY[[:space:]]+/i', '', trim($str));
 875         }
 876
 877         /**
 878          * Removes the prefix "GROUP BY" from the input string.
 879          * This function is used when you call the SELECTquery() function and want to pass the GROUP BY parameter by can't guarantee that "GROUP BY" is not prefixed.
 880          * Generally; This function provides a work-around to the situation where you cannot pass only the fields by which to order the result.
 881          * Usage count/core: 1
 882          *
 883          * @param       string          eg. "GROUP BY title, uid"
 884          * @return      string          eg. "title, uid"
 885          * @see exec_SELECTquery(), stripOrderBy()
 886          */
 887         function stripGroupBy($str) {
 888                 return preg_replace('/^GROUP[[:space:]]+BY[[:space:]]+/i', '', trim($str));
 889         }
 890
 891         /**
 892          * Takes the last part of a query, eg. "... uid=123 GROUP BY title ORDER BY title LIMIT 5,2" and splits each part into a table (WHERE, GROUPBY, ORDERBY, LIMIT)
 893          * Work-around function for use where you know some userdefined end to an SQL clause is supplied and you need to separate these factors.
 894          * Usage count/core: 13
 895          *
 896          * @param       string          Input string
 897          * @return      array
 898          */
 899         function splitGroupOrderLimit($str) {
 900                         // Prepending a space to make sure "[[:space:]]+" will find a space there
 901                         // for the first element.
 902                 $str = ' ' . $str;
 903                         // Init output array:
 904                 $wgolParts = array(
 905                         'WHERE' => '',
 906                         'GROUPBY' => '',
 907                         'ORDERBY' => '',
 908                         'LIMIT' => '',
 909                 );
 910
 911                         // Find LIMIT:
 912                 $reg = array();
 913                 if (preg_match('/^(.*)[[:space:]]+LIMIT[[:space:]]+([[:alnum:][:space:],._]+)$/i', $str, $reg)) {
 914                         $wgolParts['LIMIT'] = trim($reg[2]);
 915                         $str = $reg[1];
 916                 }
 917
 918                         // Find ORDER BY:
 919                 $reg = array();
 920                 if (preg_match('/^(.*)[[:space:]]+ORDER[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._]+)$/i', $str, $reg)) {
 921                         $wgolParts['ORDERBY'] = trim($reg[2]);
 922                         $str = $reg[1];
 923                 }
 924
 925                         // Find GROUP BY:
 926                 $reg = array();
 927                 if (preg_match('/^(.*)[[:space:]]+GROUP[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._]+)$/i', $str, $reg)) {
 928                         $wgolParts['GROUPBY'] = trim($reg[2]);
 929                         $str = $reg[1];
 930                 }
 931
 932                         // Rest is assumed to be "WHERE" clause:
 933                 $wgolParts['WHERE'] = $str;
 934
 935                 return $wgolParts;
 936         }
 937
 938
 939
 940
 941
 942
 943
 944
 945
 946
 947
 948
 949
 950
 951
 952         /**************************************
 953          *
 954          * MySQL wrapper functions
 955          * (For use in your applications)
 956          *
 957          **************************************/
 958
 959         /**
 960          * Executes query
 961          * mysql() wrapper function
 962          * Usage count/core: 0
 963          *
 964          * @param       string          Database name
 965          * @param       string          Query to execute
 966          * @return      pointer         Result pointer / DBAL object
 967          * @deprecated since TYPO3 3.6, will be removed in TYPO3 4.6
 968          * @see sql_query()
 969          */
 970         function sql($db, $query) {
 971                 t3lib_div::logDeprecatedFunction();
 972
 973                 $res = mysql_query($query, $this->link);
 974                 if ($this->debugOutput) {
 975                         $this->debug('sql', $query);
 976                 }
 977                 return $res;
 978         }
 979
 980         /**
 981          * Executes query
 982          * mysql_query() wrapper function
 983          * Usage count/core: 1
 984          *
 985          * @param       string          Query to execute
 986          * @return      pointer         Result pointer / DBAL object
 987          */
 988         function sql_query($query) {
 989                 $res = mysql_query($query, $this->link);
 990                 if ($this->debugOutput) {
 991                         $this->debug('sql_query', $query);
 992                 }
 993                 return $res;
 994         }
 995
 996         /**
 997          * Returns the error status on the last sql() execution
 998          * mysql_error() wrapper function
 999          * Usage count/core: 32
1000          *
1001          * @return      string          MySQL error string.
1002          */
1003         function sql_error() {
1004                 return mysql_error($this->link);
1005         }
1006
1007         /**
1008          * Returns the error number on the last sql() execution
1009          * mysql_errno() wrapper function
1010          *
1011          * @return      int             MySQL error number.
1012          */
1013         function sql_errno() {
1014                 return mysql_errno($this->link);
1015         }
1016
1017         /**
1018          * Returns the number of selected rows.
1019          * mysql_num_rows() wrapper function
1020          * Usage count/core: 85
1021          *
1022          * @param       pointer         MySQL result pointer (of SELECT query) / DBAL object
1023          * @return      integer         Number of resulting rows
1024          */
1025         function sql_num_rows($res) {
1026                 if ($this->debug_check_recordset($res)) {
1027                         return mysql_num_rows($res);
1028                 } else {
1029                         return FALSE;
1030                 }
1031         }
1032
1033         /**
1034          * Returns an associative array that corresponds to the fetched row, or FALSE if there are no more rows.
1035          * mysql_fetch_assoc() wrapper function
1036          * Usage count/core: 307
1037          *
1038          * @param       pointer         MySQL result pointer (of SELECT query) / DBAL object
1039          * @return      array           Associative array of result row.
1040          */
1041         function sql_fetch_assoc($res) {
1042                 if ($this->debug_check_recordset($res)) {
1043                         return mysql_fetch_assoc($res);
1044                 } else {
1045                         return FALSE;
1046                 }
1047         }
1048
1049         /**
1050          * Returns an array that corresponds to the fetched row, or FALSE if there are no more rows.
1051          * The array contains the values in numerical indices.
1052          * mysql_fetch_row() wrapper function
1053          * Usage count/core: 56
1054          *
1055          * @param       pointer         MySQL result pointer (of SELECT query) / DBAL object
1056          * @return      array           Array with result rows.
1057          */
1058         function sql_fetch_row($res) {
1059                 if ($this->debug_check_recordset($res)) {
1060                         return mysql_fetch_row($res);
1061                 } else {
1062                         return FALSE;
1063                 }
1064         }
1065
1066         /**
1067          * Free result memory
1068          * mysql_free_result() wrapper function
1069          * Usage count/core: 3
1070          *
1071          * @param       pointer         MySQL result pointer to free / DBAL object
1072          * @return      boolean         Returns TRUE on success or FALSE on failure.
1073          */
1074         function sql_free_result($res) {
1075                 if ($this->debug_check_recordset($res)) {
1076                         return mysql_free_result($res);
1077                 } else {
1078                         return FALSE;
1079                 }
1080         }
1081
1082         /**
1083          * Get the ID generated from the previous INSERT operation
1084          * mysql_insert_id() wrapper function
1085          * Usage count/core: 13
1086          *
1087          * @return      integer         The uid of the last inserted record.
1088          */
1089         function sql_insert_id() {
1090                 return mysql_insert_id($this->link);
1091         }
1092
1093         /**
1094          * Returns the number of rows affected by the last INSERT, UPDATE or DELETE query
1095          * mysql_affected_rows() wrapper function
1096          * Usage count/core: 1
1097          *
1098          * @return      integer         Number of rows affected by last query
1099          */
1100         function sql_affected_rows() {
1101                 return mysql_affected_rows($this->link);
1102         }
1103
1104         /**
1105          * Move internal result pointer
1106          * mysql_data_seek() wrapper function
1107          * Usage count/core: 3
1108          *
1109          * @param       pointer         MySQL result pointer (of SELECT query) / DBAL object
1110          * @param       integer         Seek result number.
1111          * @return      boolean         Returns TRUE on success or FALSE on failure.
1112          */
1113         function sql_data_seek($res, $seek) {
1114                 if ($this->debug_check_recordset($res)) {
1115                         return mysql_data_seek($res, $seek);
1116                 } else {
1117                         return FALSE;
1118                 }
1119         }
1120
1121         /**
1122          * Get the type of the specified field in a result
1123          * mysql_field_type() wrapper function
1124          * Usage count/core: 2
1125          *
1126          * @param       pointer         MySQL result pointer (of SELECT query) / DBAL object
1127          * @param       integer         Field index.
1128          * @return      string          Returns the name of the specified field index
1129          */
1130         function sql_field_type($res, $pointer) {
1131                 if ($this->debug_check_recordset($res)) {
1132                         return mysql_field_type($res, $pointer);
1133                 } else {
1134                         return FALSE;
1135                 }
1136         }
1137
1138         /**
1139          * Open a (persistent) connection to a MySQL server
1140          * mysql_pconnect() wrapper function
1141          * Usage count/core: 12
1142          *
1143          * @param       string          Database host IP/domain
1144          * @param       string          Username to connect with.
1145          * @param       string          Password to connect with.
1146          * @return      pointer         Returns a positive MySQL persistent link identifier on success, or FALSE on error.
1147          */
1148         function sql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password)   {
1149                         // mysql_error() is tied to an established connection
1150                         // if the connection fails we need a different method to get the error message
1151                 @ini_set('track_errors', 1);
1152                 @ini_set('html_errors', 0);
1153
1154                         // check if MySQL extension is loaded
1155                 if (!extension_loaded('mysql')) {
1156                         $message = 'Database Error: It seems that MySQL support for PHP is not installed!';
1157                         throw new RuntimeException($message, 1271492606);
1158                 }
1159
1160                         // Check for client compression
1161                 $isLocalhost = ($TYPO3_db_host == 'localhost' || $TYPO3_db_host == '127.0.0.1');
1162                 if ($GLOBALS['TYPO3_CONF_VARS']['SYS']['no_pconnect']) {
1163                         if ($GLOBALS['TYPO3_CONF_VARS']['SYS']['dbClientCompress'] && !$isLocalhost) {
1164                                         // We use PHP's default value for 4th parameter (new_link), which is false.
1165                                         // See PHP sources, for example: file php-5.2.5/ext/mysql/php_mysql.c,
1166                                         // function php_mysql_do_connect(), near line 525
1167                                 $this->link = @mysql_connect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password, false, MYSQL_CLIENT_COMPRESS);
1168                         } else {
1169                                 $this->link = @mysql_connect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password);
1170                         }
1171                 } else {
1172                         if ($GLOBALS['TYPO3_CONF_VARS']['SYS']['dbClientCompress'] && !$isLocalhost) {
1173                                         // See comment about 4th parameter in block above
1174                                 $this->link = @mysql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password, MYSQL_CLIENT_COMPRESS);
1175                         } else {
1176                                 $this->link = @mysql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password);
1177                         }
1178                 }
1179
1180                 $error_msg = $php_errormsg;
1181                 @ini_restore('track_errors');
1182                 @ini_restore('html_errors');
1183
1184                 if (!$this->link) {
1185                         t3lib_div::sysLog('Could not connect to MySQL server ' . $TYPO3_db_host .
1186                                 ' with user ' . $TYPO3_db_username . ': ' . $error_msg,
1187                                 'Core',
1188                                 4
1189                         );
1190                 } else {
1191                         $setDBinit = t3lib_div::trimExplode(LF, str_replace("' . LF . '", LF, $GLOBALS['TYPO3_CONF_VARS']['SYS']['setDBinit']), TRUE);
1192                         foreach ($setDBinit as $v) {
1193                                 if (mysql_query($v, $this->link) === FALSE) {
1194                                         t3lib_div::sysLog('Could not initialize DB connection with query "' . $v .
1195                                                 '": ' . mysql_error($this->link),
1196                                                 'Core',
1197                                                 3
1198                                         );
1199                                 }
1200                         }
1201                 }
1202
1203                 return $this->link;
1204         }
1205
1206         /**
1207          * Select a MySQL database
1208          * mysql_select_db() wrapper function
1209          * Usage count/core: 8
1210          *
1211          * @param       string          Database to connect to.
1212          * @return      boolean         Returns TRUE on success or FALSE on failure.
1213          */
1214         function sql_select_db($TYPO3_db) {
1215                 $ret = @mysql_select_db($TYPO3_db, $this->link);
1216                 if (!$ret) {
1217                         t3lib_div::sysLog('Could not select MySQL database ' . $TYPO3_db . ': ' .
1218                                 mysql_error(),
1219                                 'Core',
1220                                 4
1221                         );
1222                 }
1223                 return $ret;
1224         }
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235         /**************************************
1236          *
1237          * SQL admin functions
1238          * (For use in the Install Tool and Extension Manager)
1239          *
1240          **************************************/
1241
1242         /**
1243          * Listing databases from current MySQL connection. NOTICE: It WILL try to select those databases and thus break selection of current database.
1244          * This is only used as a service function in the (1-2-3 process) of the Install Tool.
1245          * In any case a lookup should be done in the _DEFAULT handler DBMS then.
1246          * Use in Install Tool only!
1247          * Usage count/core: 1
1248          *
1249          * @return      array           Each entry represents a database name
1250          */
1251         function admin_get_dbs() {
1252                 $dbArr = array();
1253                 $db_list = mysql_list_dbs($this->link);
1254                 while ($row = mysql_fetch_object($db_list)) {
1255                         if ($this->sql_select_db($row->Database)) {
1256                                 $dbArr[] = $row->Database;
1257                         }
1258                 }
1259                 return $dbArr;
1260         }
1261
1262         /**
1263          * Returns the list of tables from the default database, TYPO3_db (quering the DBMS)
1264          * In a DBAL this method should 1) look up all tables from the DBMS  of
1265          * the _DEFAULT handler and then 2) add all tables *configured* to be managed by other handlers
1266          * Usage count/core: 2
1267          *
1268          * @return      array           Array with tablenames as key and arrays with status information as value
1269          */
1270         function admin_get_tables() {
1271                 $whichTables = array();
1272
1273                 $tables_result = mysql_query('SHOW TABLE STATUS FROM `' . TYPO3_db . '`', $this->link);
1274                 if (!mysql_error()) {
1275                         while ($theTable = mysql_fetch_assoc($tables_result)) {
1276                                 $whichTables[$theTable['Name']] = $theTable;
1277                         }
1278
1279                         $this->sql_free_result($tables_result);
1280                 }
1281
1282                 return $whichTables;
1283         }
1284
1285         /**
1286          * Returns information about each field in the $table (quering the DBMS)
1287          * In a DBAL this should look up the right handler for the table and return compatible information
1288          * This function is important not only for the Install Tool but probably for
1289          * DBALs as well since they might need to look up table specific information
1290          * in order to construct correct queries. In such cases this information should
1291          * probably be cached for quick delivery.
1292          *
1293          * @param       string          Table name
1294          * @return      array           Field information in an associative array with fieldname => field row
1295          */
1296         function admin_get_fields($tableName) {
1297                 $output = array();
1298
1299                 $columns_res = mysql_query('SHOW COLUMNS FROM `' . $tableName . '`', $this->link);
1300                 while ($fieldRow = mysql_fetch_assoc($columns_res)) {
1301                         $output[$fieldRow['Field']] = $fieldRow;
1302                 }
1303
1304                 $this->sql_free_result($columns_res);
1305
1306                 return $output;
1307         }
1308
1309         /**
1310          * Returns information about each index key in the $table (quering the DBMS)
1311          * In a DBAL this should look up the right handler for the table and return compatible information
1312          *
1313          * @param       string          Table name
1314          * @return      array           Key information in a numeric array
1315          */
1316         function admin_get_keys($tableName) {
1317                 $output = array();
1318
1319                 $keyRes = mysql_query('SHOW KEYS FROM `' . $tableName . '`', $this->link);
1320                 while ($keyRow = mysql_fetch_assoc($keyRes)) {
1321                         $output[] = $keyRow;
1322                 }
1323
1324                 $this->sql_free_result($keyRes);
1325
1326                 return $output;
1327         }
1328
1329         /**
1330          * Returns information about the character sets supported by the current DBM
1331          * This function is important not only for the Install Tool but probably for
1332          * DBALs as well since they might need to look up table specific information
1333          * in order to construct correct queries. In such cases this information should
1334          * probably be cached for quick delivery.
1335          *
1336          * This is used by the Install Tool to convert tables tables with non-UTF8 charsets
1337          * Use in Install Tool only!
1338          *
1339          * @return      array           Array with Charset as key and an array of "Charset", "Description", "Default collation", "Maxlen" as values
1340          */
1341         function admin_get_charsets() {
1342                 $output = array();
1343
1344                 $columns_res = mysql_query('SHOW CHARACTER SET', $this->link);
1345                 if ($columns_res) {
1346                         while (($row = mysql_fetch_assoc($columns_res))) {
1347                                 $output[$row['Charset']] = $row;
1348                         }
1349
1350                         $this->sql_free_result($columns_res);
1351                 }
1352
1353                 return $output;
1354         }
1355
1356         /**
1357          * mysql() wrapper function, used by the Install Tool and EM for all queries regarding management of the database!
1358          * Usage count/core: 10
1359          *
1360          * @param       string          Query to execute
1361          * @return      pointer         Result pointer
1362          */
1363         function admin_query($query) {
1364                 $res = mysql_query($query, $this->link);
1365                 if ($this->debugOutput) {
1366                         $this->debug('admin_query', $query);
1367                 }
1368                 return $res;
1369         }
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382         /******************************
1383          *
1384          * Connecting service
1385          *
1386          ******************************/
1387
1388         /**
1389          * Connects to database for TYPO3 sites:
1390          *
1391          * @param string $host
1392          * @param string $user
1393          * @param string $password
1394          * @param string $db
1395          * @return      void
1396          */
1397         function connectDB($host = TYPO3_db_host, $user = TYPO3_db_username, $password = TYPO3_db_password, $db = TYPO3_db) {
1398                 if ($this->sql_pconnect($host, $user, $password)) {
1399                         if (!$db) {
1400                                 throw new RuntimeException(
1401                                         'TYPO3 Fatal Error: No database selected!',
1402                                         1270853882
1403                                 );
1404                         } elseif (!$this->sql_select_db($db)) {
1405                                 throw new RuntimeException(
1406                                         'TYPO3 Fatal Error: Cannot connect to the current database, "' . $db . '"!',
1407                                         1270853883
1408                                 );
1409                         }
1410                 } else {
1411                         throw new RuntimeException(
1412                                 'TYPO3 Fatal Error: The current username, password or host was not accepted when the connection to the database was attempted to be established!',
1413                                 1270853884
1414                         );
1415                 }
1416         }
1417
1418         /**
1419          * Checks if database is connected
1420          *
1421          * @return boolean
1422          */
1423         public function isConnected() {
1424                 return is_resource($this->link);
1425         }
1426
1427
1428
1429         /******************************
1430          *
1431          * Debugging
1432          *
1433          ******************************/
1434
1435         /**
1436          * Debug function: Outputs error if any
1437          *
1438          * @param       string          Function calling debug()
1439          * @param       string          Last query if not last built query
1440          * @return      void
1441          */
1442         function debug($func, $query='') {
1443
1444                 $error = $this->sql_error();
1445                 if ($error || $this->debugOutput == 2) {
1446                         debug(
1447                                 array(
1448                                         'caller' => 't3lib_DB::' . $func,
1449                                         'ERROR' => $error,
1450                                         'lastBuiltQuery' => ($query ? $query : $this->debug_lastBuiltQuery),
1451                                         'debug_backtrace' => t3lib_utility_Debug::debugTrail(),
1452                                 ),
1453                                 $func,
1454                                 is_object($GLOBALS['error']) && @is_callable(array($GLOBALS['error'], 'debug')) ? '' : 'DB Error'
1455                         );
1456                 }
1457         }
1458
1459         /**
1460          * Checks if recordset is valid and writes debugging inormation into devLog if not.
1461          *
1462          * @param       resource        $res    Recordset
1463          * @return      boolean <code>false</code> if recordset is not valid
1464          */
1465         function debug_check_recordset($res) {
1466                 if (!$res) {
1467                         $trace = FALSE;
1468                         $msg = 'Invalid database result resource detected';
1469                         $trace = debug_backtrace();
1470                         array_shift($trace);
1471                         $cnt = count($trace);
1472                         for ($i = 0; $i < $cnt; $i++) {
1473                                         // complete objects are too large for the log
1474                                 if (isset($trace['object'])) {
1475                                         unset($trace['object']);
1476                                 }
1477                         }
1478                         $msg .= ': function t3lib_DB->' . $trace[0]['function'] . ' called from file ' .
1479                                 substr($trace[0]['file'], strlen(PATH_site) + 2) . ' in line ' .
1480                                 $trace[0]['line'];
1481                         t3lib_div::sysLog($msg.'. Use a devLog extension to get more details.', 'Core/t3lib_db', 3);
1482                                 // Send to devLog if enabled
1483                         if (TYPO3_DLOG) {
1484                                 $debugLogData = array(
1485                                         'SQL Error' => $this->sql_error(),
1486                                         'Backtrace' => $trace,
1487                                 );
1488                                 if ($this->debug_lastBuiltQuery) {
1489                                         $debugLogData = array('SQL Query' => $this->debug_lastBuiltQuery) + $debugLogData;
1490                                 }
1491                                 t3lib_div::devLog($msg . '.', 'Core/t3lib_db', 3, $debugLogData);
1492                         }
1493
1494                         return FALSE;
1495                 }
1496                 return TRUE;
1497         }
1498
1499         /**
1500          * Explain select queries
1501          * If $this->explainOutput is set, SELECT queries will be explained here. Only queries with more than one possible result row will be displayed.
1502          * The output is either printed as raw HTML output or embedded into the TS admin panel (checkbox must be enabled!)
1503          *
1504          * TODO: Feature is not DBAL-compliant
1505          *
1506          * @param       string          SQL query
1507          * @param       string          Table(s) from which to select. This is what comes right after "FROM ...". Required value.
1508          * @param       integer         Number of resulting rows
1509          * @return      boolean         True if explain was run, false otherwise
1510          */
1511         protected function explain($query, $from_table, $row_count) {
1512
1513                 if ((int)$this->explainOutput == 1 || ((int)$this->explainOutput == 2 &&
1514                         t3lib_div::cmpIP(t3lib_div::getIndpEnv('REMOTE_ADDR'), $GLOBALS['TYPO3_CONF_VARS']['SYS']['devIPmask']))
1515                         ) {
1516                                 // raw HTML output
1517                         $explainMode = 1;
1518                 } elseif ((int)$this->explainOutput == 3 && is_object($GLOBALS['TT'])) {
1519                                 // embed the output into the TS admin panel
1520                         $explainMode = 2;
1521                 } else {
1522                         return false;
1523                 }
1524
1525                 $error = $this->sql_error();
1526                 $trail = t3lib_utility_Debug::debugTrail();
1527
1528                 $explain_tables = array();
1529                 $explain_output = array();
1530                 $res = $this->sql_query('EXPLAIN ' . $query, $this->link);
1531                 if (is_resource($res)) {
1532                         while ($tempRow = $this->sql_fetch_assoc($res)) {
1533                                 $explain_output[] = $tempRow;
1534                                 $explain_tables[] = $tempRow['table'];
1535                         }
1536                         $this->sql_free_result($res);
1537                 }
1538
1539                 $indices_output = array();
1540                         // Notice: Rows are skipped if there is only one result, or if no conditions are set
1541                 if ($explain_output[0]['rows'] > 1 || t3lib_div::inList('ALL', $explain_output[0]['type'])) {
1542                                 // only enable output if it's really useful
1543                         $debug = true;
1544
1545                         foreach ($explain_tables as $table) {
1546                                 $tableRes = $this->sql_query('SHOW TABLE STATUS LIKE \'' . $table . '\'');
1547                                 $isTable = $this->sql_num_rows($tableRes);
1548                                 if ($isTable) {
1549                                         $res = $this->sql_query('SHOW INDEX FROM ' . $table, $this->link);
1550                                         if (is_resource($res)) {
1551                                                 while ($tempRow = $this->sql_fetch_assoc($res)) {
1552                                                         $indices_output[] = $tempRow;
1553                                                 }
1554                                                 $this->sql_free_result($res);
1555                                         }
1556                                 }
1557                                 $this->sql_free_result($tableRes);
1558                         }
1559                 } else {
1560                         $debug = false;
1561                 }
1562
1563                 if ($debug) {
1564                         if ($explainMode) {
1565                                 $data = array();
1566                                 $data['query'] = $query;
1567                                 $data['trail'] = $trail;
1568                                 $data['row_count'] = $row_count;
1569
1570                                 if ($error) {
1571                                         $data['error'] = $error;
1572                                 }
1573                                 if (count($explain_output)) {
1574                                         $data['explain'] = $explain_output;
1575                                 }
1576                                 if (count($indices_output)) {
1577                                         $data['indices'] = $indices_output;
1578                                 }
1579
1580                                 if ($explainMode == 1) {
1581                                         t3lib_utility_Debug::debug($data, 'Tables: ' . $from_table, 'DB SQL EXPLAIN');
1582                                 } elseif ($explainMode == 2) {
1583                                         $GLOBALS['TT']->setTSselectQuery($data);
1584                                 }
1585                         }
1586                         return true;
1587                 }
1588
1589                 return false;
1590         }
1591
1592 }
1593
1594
1595 if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_db.php'])        {
1596         include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_db.php']);
1597 }
1598
1599 ?>