t3lib/class.t3lib_db.php

   1 <?php
   2 /***************************************************************
   3 *  Copyright notice
   4 *
   5 *  (c) 2004-2007 Kasper Skaarhoj (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 and mysql wrappers, thus providing a foundational API to all database interaction.
  29  * This class is instantiated globally as $TYPO3_DB in TYPO3 scripts.
  30  *
  31  * $Id$
  32  *
  33  * @author      Kasper Skaarhoj <kasperYYYY@typo3.com>
  34  */
  35 /**
  36  * [CLASS/FUNCTION INDEX of SCRIPT]
  37  *
  38  *
  39  *
  40  *  138: class t3lib_DB
  41  *
  42  *              SECTION: Query execution
  43  *  175:     function exec_INSERTquery($table,$fields_values,$no_quote_fields=FALSE)
  44  *  192:     function exec_UPDATEquery($table,$where,$fields_values,$no_quote_fields=FALSE)
  45  *  206:     function exec_DELETEquery($table,$where)
  46  *  225:     function exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='')
  47  *  250:     function exec_SELECT_mm_query($select,$local_table,$mm_table,$foreign_table,$whereClause='',$groupBy='',$orderBy='',$limit='')
  48  *  278:     function exec_SELECT_queryArray($queryParts)
  49  *  301:     function exec_SELECTgetRows($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='',$uidIndexField='')
  50  *
  51  *              SECTION: Query building
  52  *  346:     function INSERTquery($table,$fields_values,$no_quote_fields=FALSE)
  53  *  381:     function UPDATEquery($table,$where,$fields_values,$no_quote_fields=FALSE)
  54  *  422:     function DELETEquery($table,$where)
  55  *  451:     function SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='')
  56  *  492:     function listQuery($field, $value, $table)
  57  *  506:     function searchQuery($searchWords,$fields,$table)
  58  *
  59  *              SECTION: Various helper functions
  60  *  552:     function fullQuoteStr($str, $table)
  61  *  569:     function fullQuoteArray($arr, $table, $noQuote=FALSE)
  62  *  596:     function quoteStr($str, $table)
  63  *  612:     function escapeStrForLike($str, $table)
  64  *  625:     function cleanIntArray($arr)
  65  *  641:     function cleanIntList($list)
  66  *  655:     function stripOrderBy($str)
  67  *  669:     function stripGroupBy($str)
  68  *  681:     function splitGroupOrderLimit($str)
  69  *
  70  *              SECTION: MySQL wrapper functions
  71  *  749:     function sql($db,$query)
  72  *  763:     function sql_query($query)
  73  *  776:     function sql_error()
  74  *  788:     function sql_num_rows($res)
  75  *  800:     function sql_fetch_assoc($res)
  76  *  813:     function sql_fetch_row($res)
  77  *  825:     function sql_free_result($res)
  78  *  836:     function sql_insert_id()
  79  *  847:     function sql_affected_rows()
  80  *  860:     function sql_data_seek($res,$seek)
  81  *  873:     function sql_field_type($res,$pointer)
  82  *  887:     function sql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password)
  83  *  915:     function sql_select_db($TYPO3_db)
  84  *
  85  *              SECTION: SQL admin functions
  86  *  947:     function admin_get_dbs()
  87  *  965:     function admin_get_tables()
  88  *  984:     function admin_get_fields($tableName)
  89  * 1002:     function admin_get_keys($tableName)
  90  * 1020:     function admin_query($query)
  91  *
  92  *              SECTION: Connecting service
  93  * 1048:     function connectDB()
  94  *
  95  *              SECTION: Debugging
  96  * 1086:     function debug($func)
  97  *
  98  * TOTAL FUNCTIONS: 42
  99  * (This index is automatically created/updated by the extension "extdeveval")
 100  *
 101  */
 102
 103
 104
 105
 106
 107
 108
 109
 110
 111
 112
 113
 114 /**
 115  * TYPO3 "database wrapper" class (new in 3.6.0)
 116  * This class contains
 117  * - 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!)
 118  * - 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!
 119  * - 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!
 120  * See the Project Coding Guidelines (doc_core_cgl) for more instructions on best-practise
 121  *
 122  * 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)
 123  * ALL connectivity to the database in TYPO3 must be done through this class!
 124  * The points of this class are:
 125  * - To direct all database calls through this class so it becomes possible to implement DBAL with extensions.
 126  * - 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...
 127  * - To create an interface for DBAL implemented by extensions; (Eg. making possible escaping characters, clob/blob handling, reserved words handling)
 128  * - Benchmarking the DB bottleneck queries will become much easier; Will make it easier to find optimization possibilities.
 129  *
 130  * USE:
 131  * In all TYPO3 scripts the global variable $TYPO3_DB is an instance of this class. Use that.
 132  * Eg.          $GLOBALS['TYPO3_DB']->sql_fetch_assoc()
 133  *
 134  * @author      Kasper Skaarhoj <kasperYYYY@typo3.com>
 135  * @package TYPO3
 136  * @subpackage t3lib
 137  */
 138 class t3lib_DB {
 139
 140
 141                 // Debug:
 142         var $debugOutput = FALSE;               // Set "TRUE" if you want database errors outputted.
 143         var $debug_lastBuiltQuery = '';         // Internally: Set to last built query (not necessarily executed...)
 144         var $store_lastBuiltQuery = FALSE;      // Set "TRUE" if you want the last built query to be stored in $debug_lastBuiltQuery independent of $this->debugOutput
 145
 146                 // Default link identifier:
 147         var $link = FALSE;
 148
 149
 150
 151
 152         /************************************
 153          *
 154          * Query execution
 155          *
 156          * These functions are the RECOMMENDED DBAL functions for use in your applications
 157          * Using these functions will allow the DBAL to use alternative ways of accessing data (contrary to if a query is returned!)
 158          * They compile a query AND execute it immediately and then return the result
 159          * This principle heightens our ability to create various forms of DBAL of the functions.
 160          * Generally: We want to return a result pointer/object, never queries.
 161          * Also, having the table name together with the actual query execution allows us to direct the request to other databases.
 162          *
 163          **************************************/
 164
 165         /**
 166          * Creates and executes an INSERT SQL-statement for $table from the array with field/value pairs $fields_values.
 167          * Using this function specifically allows us to handle BLOB and CLOB fields depending on DB
 168          * Usage count/core: 47
 169          *
 170          * @param       string          Table name
 171          * @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.
 172          * @param       string/array            See fullQuoteArray()
 173          * @return      pointer         MySQL result pointer / DBAL object
 174          */
 175         function exec_INSERTquery($table,$fields_values,$no_quote_fields=FALSE) {
 176                 $res = mysql_query($this->INSERTquery($table,$fields_values,$no_quote_fields), $this->link);
 177                 if ($this->debugOutput) $this->debug('exec_INSERTquery');
 178                 return $res;
 179         }
 180
 181         /**
 182          * Creates and executes an UPDATE SQL-statement for $table where $where-clause (typ. 'uid=...') from the array with field/value pairs $fields_values.
 183          * Using this function specifically allow us to handle BLOB and CLOB fields depending on DB
 184          * Usage count/core: 50
 185          *
 186          * @param       string          Database tablename
 187          * @param       string          WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
 188          * @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.
 189          * @param       string/array            See fullQuoteArray()
 190          * @return      pointer         MySQL result pointer / DBAL object
 191          */
 192         function exec_UPDATEquery($table,$where,$fields_values,$no_quote_fields=FALSE)  {
 193                 $res = mysql_query($this->UPDATEquery($table,$where,$fields_values,$no_quote_fields), $this->link);
 194                 if ($this->debugOutput) $this->debug('exec_UPDATEquery');
 195                 return $res;
 196         }
 197
 198         /**
 199          * Creates and executes a DELETE SQL-statement for $table where $where-clause
 200          * Usage count/core: 40
 201          *
 202          * @param       string          Database tablename
 203          * @param       string          WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
 204          * @return      pointer         MySQL result pointer / DBAL object
 205          */
 206         function exec_DELETEquery($table,$where)        {
 207                 $res = mysql_query($this->DELETEquery($table,$where), $this->link);
 208                 if ($this->debugOutput) $this->debug('exec_DELETEquery');
 209                 return $res;
 210         }
 211
 212         /**
 213          * Creates and executes a SELECT SQL-statement
 214          * Using this function specifically allow us to handle the LIMIT feature independently of DB.
 215          * Usage count/core: 340
 216          *
 217          * @param       string          List of fields to select from the table. This is what comes right after "SELECT ...". Required value.
 218          * @param       string          Table(s) from which to select. This is what comes right after "FROM ...". Required value.
 219          * @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!
 220          * @param       string          Optional GROUP BY field(s), if none, supply blank string.
 221          * @param       string          Optional ORDER BY field(s), if none, supply blank string.
 222          * @param       string          Optional LIMIT value ([begin,]max), if none, supply blank string.
 223          * @return      pointer         MySQL result pointer / DBAL object
 224          */
 225         function exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='')   {
 226                 $res = mysql_query($this->SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit), $this->link);
 227                 if ($this->debugOutput) $this->debug('exec_SELECTquery');
 228                 return $res;
 229         }
 230
 231         /**
 232          * Creates and executes a SELECT query, selecting fields ($select) from two/three tables joined
 233          * 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.
 234          * The JOIN is done with [$local_table].uid <--> [$mm_table].uid_local  / [$mm_table].uid_foreign <--> [$foreign_table].uid
 235          * 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.
 236          *
 237          * Usage: 12 (spec. ext. sys_action, sys_messages, sys_todos)
 238          *
 239          * @param       string          Field list for SELECT
 240          * @param       string          Tablename, local table
 241          * @param       string          Tablename, relation table
 242          * @param       string          Tablename, foreign table
 243          * @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!
 244          * @param       string          Optional GROUP BY field(s), if none, supply blank string.
 245          * @param       string          Optional ORDER BY field(s), if none, supply blank string.
 246          * @param       string          Optional LIMIT value ([begin,]max), if none, supply blank string.
 247          * @return      pointer         MySQL result pointer / DBAL object
 248          * @see exec_SELECTquery()
 249          */
 250         function exec_SELECT_mm_query($select,$local_table,$mm_table,$foreign_table,$whereClause='',$groupBy='',$orderBy='',$limit='')  {
 251                 if($foreign_table == $local_table) {
 252                         $foreign_table_as = $foreign_table.uniqid('_join');
 253                 }
 254
 255                 $mmWhere = $local_table ? $local_table.'.uid='.$mm_table.'.uid_local' : '';
 256                 $mmWhere.= ($local_table AND $foreign_table) ? ' AND ' : '';
 257                 $mmWhere.= $foreign_table ? ($foreign_table_as ? $foreign_table_as : $foreign_table).'.uid='.$mm_table.'.uid_foreign' : '';
 258
 259                 return $GLOBALS['TYPO3_DB']->exec_SELECTquery(
 260                                         $select,
 261                                         ($local_table ? $local_table.',' : '').$mm_table.($foreign_table ? ','. $foreign_table.($foreign_table_as ? ' AS '.$foreign_table_as : '') : ''),
 262                                         $mmWhere.' '.$whereClause,              // whereClauseMightContainGroupOrderBy
 263                                         $groupBy,
 264                                         $orderBy,
 265                                         $limit
 266                                 );
 267         }
 268
 269         /**
 270          * Executes a select based on input query parts array
 271          *
 272          * Usage: 9
 273          *
 274          * @param       array           Query parts array
 275          * @return      pointer         MySQL select result pointer / DBAL object
 276          * @see exec_SELECTquery()
 277          */
 278         function exec_SELECT_queryArray($queryParts)    {
 279                 return $this->exec_SELECTquery(
 280                                         $queryParts['SELECT'],
 281                                         $queryParts['FROM'],
 282                                         $queryParts['WHERE'],
 283                                         $queryParts['GROUPBY'],
 284                                         $queryParts['ORDERBY'],
 285                                         $queryParts['LIMIT']
 286                                 );
 287         }
 288
 289         /**
 290          * Creates and executes a SELECT SQL-statement AND traverse result set and returns array with records in.
 291          *
 292          * @param       string          See exec_SELECTquery()
 293          * @param       string          See exec_SELECTquery()
 294          * @param       string          See exec_SELECTquery()
 295          * @param       string          See exec_SELECTquery()
 296          * @param       string          See exec_SELECTquery()
 297          * @param       string          See exec_SELECTquery()
 298          * @param       string          If set, the result array will carry this field names value as index. Requires that field to be selected of course!
 299          * @return      array           Array of rows.
 300          */
 301         function exec_SELECTgetRows($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='',$uidIndexField='')       {
 302                 $res = $this->exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit);
 303                 if ($this->debugOutput) $this->debug('exec_SELECTquery');
 304
 305                 if (!$this->sql_error())        {
 306                         $output = array();
 307
 308                         if ($uidIndexField)     {
 309                                 while($tempRow = $this->sql_fetch_assoc($res))  {
 310                                         $output[$tempRow[$uidIndexField]] = $tempRow;
 311                                 }
 312                         } else {
 313                                 while($output[] = $this->sql_fetch_assoc($res));
 314                                 array_pop($output);
 315                         }
 316                         $this->sql_free_result($res);
 317                 }
 318                 return $output;
 319         }
 320
 321
 322
 323
 324
 325
 326
 327
 328
 329
 330
 331         /**************************************
 332          *
 333          * Query building
 334          *
 335          **************************************/
 336
 337         /**
 338          * Creates an INSERT SQL-statement for $table from the array with field/value pairs $fields_values.
 339          * Usage count/core: 4
 340          *
 341          * @param       string          See exec_INSERTquery()
 342          * @param       array           See exec_INSERTquery()
 343          * @param       string/array            See fullQuoteArray()
 344          * @return      string          Full SQL query for INSERT (unless $fields_values does not contain any elements in which case it will be false)
 345          * @deprecated                  use exec_INSERTquery() instead if possible!
 346          */
 347         function INSERTquery($table,$fields_values,$no_quote_fields=FALSE)      {
 348
 349                         // Table and fieldnames should be "SQL-injection-safe" when supplied to this function (contrary to values in the arrays which may be insecure).
 350                 if (is_array($fields_values) && count($fields_values))  {
 351
 352                                 // quote and escape values
 353                         $fields_values = $this->fullQuoteArray($fields_values,$table,$no_quote_fields);
 354
 355                                 // Build query:
 356                         $query = 'INSERT INTO '.$table.'
 357                                 (
 358                                         '.implode(',
 359                                         ',array_keys($fields_values)).'
 360                                 ) VALUES (
 361                                         '.implode(',
 362                                         ',$fields_values).'
 363                                 )';
 364
 365                                 // Return query:
 366                         if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;
 367                         return $query;
 368                 }
 369         }
 370
 371         /**
 372          * Creates an UPDATE SQL-statement for $table where $where-clause (typ. 'uid=...') from the array with field/value pairs $fields_values.
 373          * Usage count/core: 6
 374          *
 375          * @param       string          See exec_UPDATEquery()
 376          * @param       string          See exec_UPDATEquery()
 377          * @param       array           See exec_UPDATEquery()
 378          * @param       array           See fullQuoteArray()
 379          * @return      string          Full SQL query for UPDATE (unless $fields_values does not contain any elements in which case it will be false)
 380          * @deprecated                  use exec_UPDATEquery() instead if possible!
 381          */
 382         function UPDATEquery($table,$where,$fields_values,$no_quote_fields=FALSE)       {
 383
 384                         // Table and fieldnames should be "SQL-injection-safe" when supplied to this function (contrary to values in the arrays which may be insecure).
 385                 if (is_string($where))  {
 386                         if (is_array($fields_values) && count($fields_values))  {
 387
 388                                         // quote and escape values
 389                                 $nArr = $this->fullQuoteArray($fields_values,$table,$no_quote_fields);
 390
 391                                 $fields = array();
 392                                 foreach ($nArr as $k => $v) {
 393                                         $fields[] = $k.'='.$v;
 394                                 }
 395
 396                                         // Build query:
 397                                 $query = 'UPDATE '.$table.'
 398                                         SET
 399                                                 '.implode(',
 400                                                 ',$fields).
 401                                         (strlen($where)>0 ? '
 402                                         WHERE
 403                                                 '.$where : '');
 404
 405                                         // Return query:
 406                                 if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;
 407                                 return $query;
 408                         }
 409                 } else {
 410                         die('<strong>TYPO3 Fatal Error:</strong> "Where" clause argument for UPDATE query was not a string in $this->UPDATEquery() !');
 411                 }
 412         }
 413
 414         /**
 415          * Creates a DELETE SQL-statement for $table where $where-clause
 416          * Usage count/core: 3
 417          *
 418          * @param       string          See exec_DELETEquery()
 419          * @param       string          See exec_DELETEquery()
 420          * @return      string          Full SQL query for DELETE
 421          * @deprecated                  use exec_DELETEquery() instead if possible!
 422          */
 423         function DELETEquery($table,$where)     {
 424                 if (is_string($where))  {
 425
 426                                 // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
 427                         $query = 'DELETE FROM '.$table.
 428                                 (strlen($where)>0 ? '
 429                                 WHERE
 430                                         '.$where : '');
 431
 432                         if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;
 433                         return $query;
 434                 } else {
 435                         die('<strong>TYPO3 Fatal Error:</strong> "Where" clause argument for DELETE query was not a string in $this->DELETEquery() !');
 436                 }
 437         }
 438
 439         /**
 440          * Creates a SELECT SQL-statement
 441          * Usage count/core: 11
 442          *
 443          * @param       string          See exec_SELECTquery()
 444          * @param       string          See exec_SELECTquery()
 445          * @param       string          See exec_SELECTquery()
 446          * @param       string          See exec_SELECTquery()
 447          * @param       string          See exec_SELECTquery()
 448          * @param       string          See exec_SELECTquery()
 449          * @return      string          Full SQL query for SELECT
 450          * @deprecated                  use exec_SELECTquery() instead if possible!
 451          */
 452         function SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='')        {
 453
 454                         // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
 455                         // Build basic query:
 456                 $query = 'SELECT '.$select_fields.'
 457                         FROM '.$from_table.
 458                         (strlen($where_clause)>0 ? '
 459                         WHERE
 460                                 '.$where_clause : '');
 461
 462                         // Group by:
 463                 if (strlen($groupBy)>0) {
 464                         $query.= '
 465                         GROUP BY '.$groupBy;
 466                 }
 467                         // Order by:
 468                 if (strlen($orderBy)>0) {
 469                         $query.= '
 470                         ORDER BY '.$orderBy;
 471                 }
 472                         // Group by:
 473                 if (strlen($limit)>0)   {
 474                         $query.= '
 475                         LIMIT '.$limit;
 476                 }
 477
 478                         // Return query:
 479                 if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;
 480                 return $query;
 481         }
 482
 483         /**
 484          * Returns a WHERE clause that can find a value ($value) in a list field ($field)
 485          * For instance a record in the database might contain a list of numbers, "34,234,5" (with no spaces between). This query would be able to select that record based on the value "34", "234" or "5" regardless of their positioni in the list (left, middle or right).
 486          * Is nice to look up list-relations to records or files in TYPO3 database tables.
 487          *
 488          * @param       string          Field name
 489          * @param       string          Value to find in list
 490          * @param       string          Table in which we are searching (for DBAL detection of quoteStr() method)
 491          * @return      string          WHERE clause for a query
 492          */
 493         function listQuery($field, $value, $table)      {
 494                 $command = $this->quoteStr($value, $table);
 495                 $where = '('.$field.' LIKE \'%,'.$command.',%\' OR '.$field.' LIKE \''.$command.',%\' OR '.$field.' LIKE \'%,'.$command.'\' OR '.$field.'=\''.$command.'\')';
 496                 return $where;
 497         }
 498
 499         /**
 500          * 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.
 501          *
 502          * @param       array           Array of search words
 503          * @param       array           Array of fields
 504          * @param       string          Table in which we are searching (for DBAL detection of quoteStr() method)
 505          * @return      string          WHERE clause for search
 506          */
 507         function searchQuery($searchWords,$fields,$table)       {
 508                 $queryParts = array();
 509
 510                 foreach($searchWords as $sw)    {
 511                         $like=' LIKE \'%'.$this->quoteStr($sw, $table).'%\'';
 512                         $queryParts[] = $table.'.'.implode($like.' OR '.$table.'.',$fields).$like;
 513                 }
 514                 $query = '('.implode(') AND (',$queryParts).')';
 515                 return $query ;
 516         }
 517
 518
 519
 520
 521
 522
 523
 524
 525
 526
 527
 528
 529
 530
 531
 532
 533         /**************************************
 534          *
 535          * Various helper functions
 536          *
 537          * Functions recommended to be used for
 538          * - escaping values,
 539          * - cleaning lists of values,
 540          * - stripping of excess ORDER BY/GROUP BY keywords
 541          *
 542          **************************************/
 543
 544         /**
 545          * Escaping and quoting values for SQL statements.
 546          * Usage count/core: 100
 547          *
 548          * @param       string          Input string
 549          * @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!).
 550          * @return      string          Output string; Wrapped in single quotes and quotes in the string (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
 551          * @see quoteStr()
 552          */
 553         function fullQuoteStr($str, $table)     {
 554                 return '\''.mysql_real_escape_string($str, $this->link).'\'';
 555         }
 556
 557         /**
 558          * Will fullquote all values in the one-dimensional array so they are ready to "implode" for an sql query.
 559          *
 560          * @param       array           Array with values (either associative or non-associative array)
 561          * @param       string          Table name for which to quote
 562          * @param       string/array            List/array of keys NOT to quote (eg. SQL functions) - ONLY for associative arrays
 563          * @return      array           The input array with the values quoted
 564          * @see cleanIntArray()
 565          */
 566         function fullQuoteArray($arr, $table, $noQuote=FALSE)   {
 567                 if (is_string($noQuote))        {
 568                         $noQuote = explode(',',$noQuote);
 569                 } elseif (!is_array($noQuote))  {       // sanity check
 570                         $noQuote = FALSE;
 571                 }
 572
 573                 foreach($arr as $k => $v)       {
 574                         if ($noQuote===FALSE || !in_array($k,$noQuote))     {
 575                                 $arr[$k] = $this->fullQuoteStr($v, $table);
 576                         }
 577                 }
 578                 return $arr;
 579         }
 580
 581         /**
 582          * Substitution for PHP function "addslashes()"
 583          * Use this function instead of the PHP addslashes() function when you build queries - this will prepare your code for DBAL.
 584          * 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()!
 585          *
 586          * Usage count/core: 20
 587          *
 588          * @param       string          Input string
 589          * @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!).
 590          * @return      string          Output string; Quotes (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
 591          * @see quoteStr()
 592          */
 593         function quoteStr($str, $table) {
 594                 return mysql_real_escape_string($str, $this->link);
 595         }
 596
 597         /**
 598          * Escaping values for SQL LIKE statements.
 599          *
 600          * @param       string          Input string
 601          * @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!).
 602          * @return      string          Output string; % and _ will be escaped with \ (or otherwise based on DBAL handler)
 603          * @see quoteStr()
 604          */
 605         function escapeStrForLike($str, $table) {
 606                 return preg_replace('/[_%]/','\\\$0',$str);
 607         }
 608
 609         /**
 610          * Will convert all values in the one-dimensional array to integers.
 611          * Useful when you want to make sure an array contains only integers before imploding them in a select-list.
 612          * Usage count/core: 7
 613          *
 614          * @param       array           Array with values
 615          * @return      array           The input array with all values passed through intval()
 616          * @see cleanIntList()
 617          */
 618         function cleanIntArray($arr)    {
 619                 foreach($arr as $k => $v)       {
 620                         $arr[$k] = intval($arr[$k]);
 621                 }
 622                 return $arr;
 623         }
 624
 625         /**
 626          * Will force all entries in the input comma list to integers
 627          * 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.
 628          * Usage count/core: 6
 629          *
 630          * @param       string          List of comma-separated values which should be integers
 631          * @return      string          The input list but with every value passed through intval()
 632          * @see cleanIntArray()
 633          */
 634         function cleanIntList($list)    {
 635                 return implode(',',t3lib_div::intExplode(',',$list));
 636         }
 637
 638         /**
 639          * Removes the prefix "ORDER BY" from the input string.
 640          * 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.
 641          * Generally; This function provides a work-around to the situation where you cannot pass only the fields by which to order the result.
 642          * Usage count/core: 11
 643          *
 644          * @param       string          eg. "ORDER BY title, uid"
 645          * @return      string          eg. "title, uid"
 646          * @see exec_SELECTquery(), stripGroupBy()
 647          */
 648         function stripOrderBy($str)     {
 649                 return preg_replace('/^ORDER[[:space:]]+BY[[:space:]]+/i','',trim($str));
 650         }
 651
 652         /**
 653          * Removes the prefix "GROUP BY" from the input string.
 654          * 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.
 655          * Generally; This function provides a work-around to the situation where you cannot pass only the fields by which to order the result.
 656          * Usage count/core: 1
 657          *
 658          * @param       string          eg. "GROUP BY title, uid"
 659          * @return      string          eg. "title, uid"
 660          * @see exec_SELECTquery(), stripOrderBy()
 661          */
 662         function stripGroupBy($str)     {
 663                 return preg_replace('/^GROUP[[:space:]]+BY[[:space:]]+/i','',trim($str));
 664         }
 665
 666         /**
 667          * 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)
 668          * Work-around function for use where you know some userdefined end to an SQL clause is supplied and you need to separate these factors.
 669          * Usage count/core: 13
 670          *
 671          * @param       string          Input string
 672          * @return      array
 673          */
 674         function splitGroupOrderLimit($str)     {
 675                 $str = ' '.$str;        // Prepending a space to make sure "[[:space:]]+" will find a space there for the first element.
 676                         // Init output array:
 677                 $wgolParts = array(
 678                         'WHERE' => '',
 679                         'GROUPBY' => '',
 680                         'ORDERBY' => '',
 681                         'LIMIT' => ''
 682                 );
 683
 684                         // Find LIMIT:
 685                 $reg = array();
 686                 if (preg_match('/^(.*)[[:space:]]+LIMIT[[:space:]]+([[:alnum:][:space:],._]+)$/i',$str,$reg))   {
 687                         $wgolParts['LIMIT'] = trim($reg[2]);
 688                         $str = $reg[1];
 689                 }
 690
 691                         // Find ORDER BY:
 692                 $reg = array();
 693                 if (preg_match('/^(.*)[[:space:]]+ORDER[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._]+)$/i',$str,$reg))     {
 694                         $wgolParts['ORDERBY'] = trim($reg[2]);
 695                         $str = $reg[1];
 696                 }
 697
 698                         // Find GROUP BY:
 699                 $reg = array();
 700                 if (preg_match('/^(.*)[[:space:]]+GROUP[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._]+)$/i',$str,$reg))     {
 701                         $wgolParts['GROUPBY'] = trim($reg[2]);
 702                         $str = $reg[1];
 703                 }
 704
 705                         // Rest is assumed to be "WHERE" clause:
 706                 $wgolParts['WHERE'] = $str;
 707
 708                 return $wgolParts;
 709         }
 710
 711
 712
 713
 714
 715
 716
 717
 718
 719
 720
 721
 722
 723
 724
 725         /**************************************
 726          *
 727          * MySQL wrapper functions
 728          * (For use in your applications)
 729          *
 730          **************************************/
 731
 732         /**
 733          * Executes query
 734          * mysql() wrapper function
 735          * DEPRECATED - use exec_* functions from this class instead!
 736          * Usage count/core: 9
 737          *
 738          * @param       string          Database name
 739          * @param       string          Query to execute
 740          * @return      pointer         Result pointer / DBAL object
 741          */
 742         function sql($db,$query)        {
 743                 $res = mysql_query($query, $this->link);
 744                 if ($this->debugOutput) $this->debug('sql',$query);
 745                 return $res;
 746         }
 747
 748         /**
 749          * Executes query
 750          * mysql_query() wrapper function
 751          * Usage count/core: 1
 752          *
 753          * @param       string          Query to execute
 754          * @return      pointer         Result pointer / DBAL object
 755          */
 756         function sql_query($query)      {
 757                 $res = mysql_query($query, $this->link);
 758                 if ($this->debugOutput) $this->debug('sql_query',$query);
 759                 return $res;
 760         }
 761
 762         /**
 763          * Returns the error status on the last sql() execution
 764          * mysql_error() wrapper function
 765          * Usage count/core: 32
 766          *
 767          * @return      string          MySQL error string.
 768          */
 769         function sql_error()    {
 770                 return mysql_error($this->link);
 771         }
 772
 773         /**
 774          * Returns the number of selected rows.
 775          * mysql_num_rows() wrapper function
 776          * Usage count/core: 85
 777          *
 778          * @param       pointer         MySQL result pointer (of SELECT query) / DBAL object
 779          * @return      integer         Number of resulting rows.
 780          */
 781         function sql_num_rows($res)     {
 782                 $this->debug_check_recordset($res);
 783                 return mysql_num_rows($res);
 784         }
 785
 786         /**
 787          * Returns an associative array that corresponds to the fetched row, or FALSE if there are no more rows.
 788          * mysql_fetch_assoc() wrapper function
 789          * Usage count/core: 307
 790          *
 791          * @param       pointer         MySQL result pointer (of SELECT query) / DBAL object
 792          * @return      array           Associative array of result row.
 793          */
 794         function sql_fetch_assoc($res)  {
 795                 $this->debug_check_recordset($res);
 796                 return mysql_fetch_assoc($res);
 797         }
 798
 799         /**
 800          * Returns an array that corresponds to the fetched row, or FALSE if there are no more rows.
 801          * The array contains the values in numerical indices.
 802          * mysql_fetch_row() wrapper function
 803          * Usage count/core: 56
 804          *
 805          * @param       pointer         MySQL result pointer (of SELECT query) / DBAL object
 806          * @return      array           Array with result rows.
 807          */
 808         function sql_fetch_row($res)    {
 809                 $this->debug_check_recordset($res);
 810                 return mysql_fetch_row($res);
 811         }
 812
 813         /**
 814          * Free result memory
 815          * mysql_free_result() wrapper function
 816          * Usage count/core: 3
 817          *
 818          * @param       pointer         MySQL result pointer to free / DBAL object
 819          * @return      boolean         Returns TRUE on success or FALSE on failure.
 820          */
 821         function sql_free_result($res)  {
 822                 $this->debug_check_recordset($res);
 823                 return mysql_free_result($res);
 824         }
 825
 826         /**
 827          * Get the ID generated from the previous INSERT operation
 828          * mysql_insert_id() wrapper function
 829          * Usage count/core: 13
 830          *
 831          * @return      integer         The uid of the last inserted record.
 832          */
 833         function sql_insert_id()        {
 834                 return mysql_insert_id($this->link);
 835         }
 836
 837         /**
 838          * Returns the number of rows affected by the last INSERT, UPDATE or DELETE query
 839          * mysql_affected_rows() wrapper function
 840          * Usage count/core: 1
 841          *
 842          * @return      integer         Number of rows affected by last query
 843          */
 844         function sql_affected_rows()    {
 845                 return mysql_affected_rows($this->link);
 846         }
 847
 848         /**
 849          * Move internal result pointer
 850          * mysql_data_seek() wrapper function
 851          * Usage count/core: 3
 852          *
 853          * @param       pointer         MySQL result pointer (of SELECT query) / DBAL object
 854          * @param       integer         Seek result number.
 855          * @return      boolean         Returns TRUE on success or FALSE on failure.
 856          */
 857         function sql_data_seek($res,$seek)      {
 858                 $this->debug_check_recordset($res);
 859                 return mysql_data_seek($res,$seek);
 860         }
 861
 862         /**
 863          * Get the type of the specified field in a result
 864          * mysql_field_type() wrapper function
 865          * Usage count/core: 2
 866          *
 867          * @param       pointer         MySQL result pointer (of SELECT query) / DBAL object
 868          * @param       integer         Field index.
 869          * @return      string          Returns the name of the specified field index
 870          */
 871         function sql_field_type($res,$pointer)  {
 872                 $this->debug_check_recordset($res);
 873                 return mysql_field_type($res,$pointer);
 874         }
 875
 876         /**
 877          * Open a (persistent) connection to a MySQL server
 878          * mysql_pconnect() wrapper function
 879          * Usage count/core: 12
 880          *
 881          * @param       string          Database host IP/domain
 882          * @param       string          Username to connect with.
 883          * @param       string          Password to connect with.
 884          * @return      pointer         Returns a positive MySQL persistent link identifier on success, or FALSE on error.
 885          */
 886         function sql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password)   {
 887                         // mysql_error() is tied to an established connection
 888                         // if the connection fails we need a different method to get the error message
 889                 ini_set('track_errors', 1);
 890                 ini_set('html_errors', 0);
 891                 if ($GLOBALS['TYPO3_CONF_VARS']['SYS']['no_pconnect'])  {
 892                         $this->link = @mysql_connect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password);
 893                 } else {
 894                         $this->link = @mysql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password);
 895                 }
 896                 $error_msg = $php_errormsg;
 897                 ini_restore('track_errors');
 898                 ini_restore('html_errors');
 899
 900                 if (!$this->link) {
 901                         t3lib_div::sysLog('Could not connect to MySQL server '.$TYPO3_db_host.' with user '.$TYPO3_db_username.': '.$error_msg,'Core',4);
 902                 } else {
 903                         $setDBinit = t3lib_div::trimExplode(chr(10), $GLOBALS['TYPO3_CONF_VARS']['SYS']['setDBinit'],TRUE);
 904                         foreach ($setDBinit as $v)      {
 905                                 if (mysql_query($v, $this->link) === FALSE)     {
 906                                         t3lib_div::sysLog('Could not initialize DB connection with query "'.$v.'": '.mysql_error($this->link),'Core',3);
 907                                 }
 908                         }
 909                 }
 910
 911                 return $this->link;
 912         }
 913
 914         /**
 915          * Select a MySQL database
 916          * mysql_select_db() wrapper function
 917          * Usage count/core: 8
 918          *
 919          * @param       string          Database to connect to.
 920          * @return      boolean         Returns TRUE on success or FALSE on failure.
 921          */
 922         function sql_select_db($TYPO3_db)       {
 923                 $ret = @mysql_select_db($TYPO3_db, $this->link);
 924                 if (!$ret) {
 925                         t3lib_div::sysLog('Could not select MySQL database '.$TYPO3_db.': '.mysql_error(),'Core',4);
 926         }
 927                 return $ret;
 928         }
 929
 930
 931
 932
 933
 934
 935
 936
 937
 938
 939         /**************************************
 940          *
 941          * SQL admin functions
 942          * (For use in the Install Tool and Extension Manager)
 943          *
 944          **************************************/
 945
 946         /**
 947          * Listing databases from current MySQL connection. NOTICE: It WILL try to select those databases and thus break selection of current database.
 948          * This is only used as a service function in the (1-2-3 process) of the Install Tool. In any case a lookup should be done in the _DEFAULT handler DBMS then.
 949          * Use in Install Tool only!
 950          * Usage count/core: 1
 951          *
 952          * @return      array           Each entry represents a database name
 953          */
 954         function admin_get_dbs()        {
 955                 $dbArr = array();
 956                 $db_list = mysql_list_dbs($this->link);
 957                 while ($row = mysql_fetch_object($db_list)) {
 958                         if ($this->sql_select_db($row->Database))       {
 959                                 $dbArr[] = $row->Database;
 960                         }
 961                 }
 962                 return $dbArr;
 963         }
 964
 965         /**
 966          * Returns the list of tables from the default database, TYPO3_db (quering the DBMS)
 967          * In a DBAL this method should 1) look up all tables from the DBMS  of the _DEFAULT handler and then 2) add all tables *configured* to be managed by other handlers
 968          * Usage count/core: 2
 969          *
 970          * @return      array           Tables in an array (tablename is in both key and value)
 971          */
 972         function admin_get_tables()     {
 973                 $whichTables = array();
 974                 $tables_result = mysql_list_tables(TYPO3_db, $this->link);
 975                 if (!mysql_error())     {
 976                         while ($theTable = mysql_fetch_assoc($tables_result)) {
 977                                 $whichTables[current($theTable)] = current($theTable);
 978                         }
 979                 }
 980                 return $whichTables;
 981         }
 982
 983         /**
 984          * Returns information about each field in the $table (quering the DBMS)
 985          * In a DBAL this should look up the right handler for the table and return compatible information
 986          * This function is important not only for the Install Tool but probably for DBALs as well since they might need to look up table specific information in order to construct correct queries. In such cases this information should probably be cached for quick delivery.
 987          *
 988          * @param       string          Table name
 989          * @return      array           Field information in an associative array with fieldname => field row
 990          */
 991         function admin_get_fields($tableName)   {
 992                 $output = array();
 993
 994                 $columns_res = mysql_query('SHOW columns FROM `'.$tableName.'`', $this->link);
 995                 while($fieldRow = mysql_fetch_assoc($columns_res))      {
 996                         $output[$fieldRow['Field']] = $fieldRow;
 997                 }
 998
 999                 return $output;
1000         }
1001
1002         /**
1003          * Returns information about each index key in the $table (quering the DBMS)
1004          * In a DBAL this should look up the right handler for the table and return compatible information
1005          *
1006          * @param       string          Table name
1007          * @return      array           Key information in a numeric array
1008          */
1009         function admin_get_keys($tableName)     {
1010                 $output = array();
1011
1012                 $keyRes = mysql_query('SHOW keys FROM `'.$tableName.'`', $this->link);
1013                 while($keyRow = mysql_fetch_assoc($keyRes))     {
1014                         $output[] = $keyRow;
1015                 }
1016
1017                 return $output;
1018         }
1019
1020         /**
1021          * mysql() wrapper function, used by the Install Tool and EM for all queries regarding management of the database!
1022          * Usage count/core: 10
1023          *
1024          * @param       string          Query to execute
1025          * @return      pointer         Result pointer
1026          */
1027         function admin_query($query)    {
1028                 $res = mysql_query($query, $this->link);
1029                 if ($this->debugOutput) $this->debug('admin_query',$query);
1030                 return $res;
1031         }
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044         /******************************
1045          *
1046          * Connecting service
1047          *
1048          ******************************/
1049
1050         /**
1051          * Connects to database for TYPO3 sites:
1052          *
1053          * @return      void
1054          */
1055         function connectDB()    {
1056                 if ($this->sql_pconnect(TYPO3_db_host, TYPO3_db_username, TYPO3_db_password))   {
1057                         if (!TYPO3_db)  {
1058                                 die('No database selected');
1059                                 exit;
1060                         } elseif (!$this->sql_select_db(TYPO3_db))      {
1061                                 die('Cannot connect to the current database, "'.TYPO3_db.'"');
1062                                 exit;
1063                         }
1064                 } else {
1065                         die('The current username, password or host was not accepted when the connection to the database was attempted to be established!');
1066                         exit;
1067                 }
1068         }
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081         /******************************
1082          *
1083          * Debugging
1084          *
1085          ******************************/
1086
1087         /**
1088          * Debug function: Outputs error if any
1089          *
1090          * @param       string          Function calling debug()
1091          * @param       string          Last query if not last built query
1092          * @return      void
1093          */
1094         function debug($func, $query='')        {
1095
1096                 $error = $this->sql_error();
1097                 if ($error)     {
1098                         echo t3lib_div::view_array(array(
1099                                 'caller' => 't3lib_DB::'.$func,
1100                                 'ERROR' => $error,
1101                                 'lastBuiltQuery' => ($query ? $query : $this->debug_lastBuiltQuery),
1102                                 'debug_backtrace' => t3lib_div::debug_trail()
1103                         ));
1104                 }
1105         }
1106
1107         /**
1108          * Checks if recordset is valid and writes debugging inormation into devLog if not.
1109          *
1110          * @param       resource        $res    Recordset
1111          * @return      boolean <code>false</code> if recordset is not valid
1112          */
1113         function debug_check_recordset($res) {
1114                 if (!$res) {
1115                         $trace = FALSE;
1116                         $msg = 'Invalid database result resource detected';
1117                         $trace = debug_backtrace();
1118                         array_shift($trace);
1119                         $cnt = count($trace);
1120                         for ($i=0; $i<$cnt; $i++)       {
1121                                         // complete objects are too large for the log
1122                                 if (isset($trace['object']))    unset($trace['object']);
1123                         }
1124                         $msg .= ': function t3lib_DB->' . $trace[0]['function'] . ' called from file ' . substr($trace[0]['file'],strlen(PATH_site)+2) . ' in line ' . $trace[0]['line'];
1125                         t3lib_div::sysLog($msg.'. Use a devLog extension to get more details.', 'Core/t3lib_db', 3);
1126                         t3lib_div::devLog($msg.'.', 'Core/t3lib_db', 3, $trace);
1127
1128                         return FALSE;
1129                 }
1130                 return TRUE;
1131         }
1132
1133 }
1134
1135
1136 if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_db.php'])        {
1137         include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_db.php']);
1138 }
1139 ?>