class.t3lib_db.php

Go to the documentation of this file.

<?php
/***************************************************************
*  Copyright notice
*
*  (c) 2004 Kasper Skaarhoj (kasperYYYY@typo3.com)
*  All rights reserved
*
*  This script is part of the TYPO3 project. The TYPO3 project is
*  free software; you can redistribute it and/or modify
*  it under the terms of the GNU General Public License as published by
*  the Free Software Foundation; either version 2 of the License, or
*  (at your option) any later version.
*
*  The GNU General Public License can be found at
*  http://www.gnu.org/copyleft/gpl.html.
*  A copy is found in the textfile GPL.txt and important notices to the license
*  from the author is found in LICENSE.txt distributed with these scripts.
*
*
*  This script is distributed in the hope that it will be useful,
*  but WITHOUT ANY WARRANTY; without even the implied warranty of
*  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
*  GNU General Public License for more details.
*
*  This copyright notice MUST APPEAR in all copies of the script!
***************************************************************/
class t3lib_DB {


      // Debug:
   var $debugOutput = FALSE;        // Set "TRUE" if you want database errors outputted.
   var $debug_lastBuiltQuery = '';     // Internally: Set to last built query (not necessarily executed...)

      // Default link identifier:
   var $link;




   /************************************
    *
    * Query execution
    *
    * These functions are the RECOMMENDED DBAL functions for use in your applications
    * Using these functions will allow the DBAL to use alternative ways of accessing data (contrary to if a query is returned!)
    * They compile a query AND execute it immediately and then return the result
    * This principle heightens our ability to create various forms of DBAL of the functions.
    * Generally: We want to return a result pointer/object, never queries.
    * Also, having the table name together with the actual query execution allows us to direct the request to other databases.
    *
    **************************************/

   function exec_INSERTquery($table,$fields_values)   {
      $res = mysql_query($this->INSERTquery($table,$fields_values), $this->link);
      if ($this->debugOutput) $this->debug('exec_INSERTquery');
      return $res;
   }

   function exec_UPDATEquery($table,$where,$fields_values)  {
      $res = mysql_query($this->UPDATEquery($table,$where,$fields_values), $this->link);
      if ($this->debugOutput) $this->debug('exec_UPDATEquery');
      return $res;
   }

   function exec_DELETEquery($table,$where)  {
      $res = mysql_query($this->DELETEquery($table,$where), $this->link);
      if ($this->debugOutput) $this->debug('exec_DELETEquery');
      return $res;
   }

   function exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='') {
      $res = mysql_query($this->SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit), $this->link);
      if ($this->debugOutput) $this->debug('exec_SELECTquery');
      return $res;
   }

   function exec_SELECT_mm_query($select,$local_table,$mm_table,$foreign_table,$whereClause='',$groupBy='',$orderBy='',$limit='')   {
      $mmWhere = $local_table ? $local_table.'.uid='.$mm_table.'.uid_local' : '';
      $mmWhere.= ($local_table AND $foreign_table) ? ' AND ' : '';
      $mmWhere.= $foreign_table ? $foreign_table.'.uid='.$mm_table.'.uid_foreign' : '';
      return $GLOBALS['TYPO3_DB']->exec_SELECTquery(
               $select,
               ($local_table ? $local_table.',' : '').$mm_table.($foreign_table ? ','.$foreign_table : ''),
               $mmWhere.' '.$whereClause,    // whereClauseMightContainGroupOrderBy
               $groupBy,
               $orderBy,
               $limit
            );
   }

   function exec_SELECT_queryArray($queryParts) {
      return $this->exec_SELECTquery(
               $queryParts['SELECT'],
               $queryParts['FROM'],
               $queryParts['WHERE'],
               $queryParts['GROUPBY'],
               $queryParts['ORDERBY'],
               $queryParts['LIMIT']
            );
   }

   function exec_SELECTgetRows($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='',$uidIndexField='')  {
      $res = mysql_query($this->SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit), $this->link);
      if ($this->debugOutput) $this->debug('exec_SELECTquery');

      unset($output);
      if (!$this->sql_error())   {
         $output = array();

         if ($uidIndexField)  {
            while($tempRow = $this->sql_fetch_assoc($res))  {
               $output[$tempRow[$uidIndexField]] = $tempRow;
            }
         } else {
            while($output[] = $this->sql_fetch_assoc($res));
            array_pop($output);
         }
      }
      return $output;
   }











   /**************************************
    *
    * Query building
    *
    **************************************/

   function INSERTquery($table,$fields_values)  {

         // Table and fieldnames should be "SQL-injection-safe" when supplied to this function (contrary to values in the arrays which may be insecure).
      if (is_array($fields_values) && count($fields_values))   {

            // Add slashes old-school:
         foreach($fields_values as $k => $v) {
            $fields_values[$k] = $this->quoteStr($fields_values[$k], $table);
         }

            // Build query:
         $query = 'INSERT INTO '.$table.'
            (
               '.implode(',
               ',array_keys($fields_values)).'
            ) VALUES (
               "'.implode('",
               "',$fields_values).'"
            )';

            // Return query:
         if ($this->debugOutput) $this->debug_lastBuiltQuery = $query;
         return $query;
      }
   }

   function UPDATEquery($table,$where,$fields_values) {

         // Table and fieldnames should be "SQL-injection-safe" when supplied to this function (contrary to values in the arrays which may be insecure).
      if (is_string($where))  {
         if (is_array($fields_values) && count($fields_values))   {

               // Add slashes old-school:
            $nArr = array();
            foreach($fields_values as $k => $v) {
               $nArr[] = $k.'="'.$this->quoteStr($v, $table).'"';
            }

               // Build query:
            $query = 'UPDATE '.$table.'
               SET
                  '.implode(',
                  ',$nArr).
               (strlen($where)>0 ? '
               WHERE
                  '.$where : '');

               // Return query:
            if ($this->debugOutput) $this->debug_lastBuiltQuery = $query;
            return $query;
         }
      } else {
         die('<strong>TYPO3 Fatal Error:</strong> "Where" clause argument for UPDATE query was not a string in $this->UPDATEquery() !');
      }
   }

   function DELETEquery($table,$where) {
      if (is_string($where))  {

            // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
         $query = 'DELETE FROM '.$table.
            (strlen($where)>0 ? '
            WHERE
               '.$where : '');

         if ($this->debugOutput) $this->debug_lastBuiltQuery = $query;
         return $query;
      } else {
         die('<strong>TYPO3 Fatal Error:</strong> "Where" clause argument for DELETE query was not a string in $this->DELETEquery() !');
      }
   }

   function SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='')   {

         // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
         // Build basic query:
      $query = 'SELECT '.$select_fields.'
         FROM '.$from_table.
         (strlen($where_clause)>0 ? '
         WHERE
            '.$where_clause : '');

         // Group by:
      if (strlen($groupBy)>0) {
         $query.= '
         GROUP BY '.$groupBy;
      }
         // Order by:
      if (strlen($orderBy)>0) {
         $query.= '
         ORDER BY '.$orderBy;
      }
         // Group by:
      if (strlen($limit)>0)   {
         $query.= '
         LIMIT '.$limit;
      }

         // Return query:
      if ($this->debugOutput) $this->debug_lastBuiltQuery = $query;
      return $query;
   }

   function listQuery($field, $value, $table)   {
      $command = $this->quoteStr($value, $table);
      $where = '('.$field.' LIKE "%,'.$command.',%" OR '.$field.' LIKE "'.$command.',%" OR '.$field.' LIKE "%,'.$command.'" OR '.$field.'="'.$command.'")';
      return $where;
   }

   function searchQuery($searchWords,$fields,$table)  {
      $queryParts = array();

      foreach($searchWords as $sw)  {
         $like=' LIKE "%'.$this->quoteStr($sw, $table).'%"';
         $queryParts[] = $table.'.'.implode($like.' OR '.$table.'.',$fields).$like;
      }
      $query = '('.implode(') AND (',$queryParts).')';
      return $query ;
   }
















   /**************************************
    *
    * Various helper functions
    *
    * Functions recommended to be used for
    * - escaping values,
    * - cleaning lists of values,
    * - stripping of excess ORDER BY/GROUP BY keywords
    *
    **************************************/

   function quoteStr($str, $table)  {
      return addslashes($str);
   }

   function cleanIntArray($arr)  {
      foreach($arr as $k => $v)  {
         $arr[$k] = intval($arr[$k]);
      }
      return $arr;
   }

   function cleanIntList($list)  {
      return implode(',',t3lib_div::intExplode(',',$list));
   }

   function stripOrderBy($str)   {
      return eregi_replace('^ORDER[[:space:]]+BY[[:space:]]+','',trim($str));
   }

   function stripGroupBy($str)   {
      return eregi_replace('^GROUP[[:space:]]+BY[[:space:]]+','',trim($str));
   }

   function splitGroupOrderLimit($str) {
      $str = ' '.$str;  // Prepending a space to make sure "[[:space:]]+" will find a space there for the first element.
         // Init output array:
      $wgolParts = array(
         'WHERE' => '',
         'GROUPBY' => '',
         'ORDERBY' => '',
         'LIMIT' => ''
      );

         // Find LIMIT:
      if (eregi('^(.*)[[:space:]]+LIMIT[[:space:]]+([[:alnum:][:space:],._]+)$',$str,$reg))  {
         $wgolParts['LIMIT'] = trim($reg[2]);
         $str = $reg[1];
      }

         // Find ORDER BY:
      if (eregi('^(.*)[[:space:]]+ORDER[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._]+)$',$str,$reg))   {
         $wgolParts['ORDERBY'] = trim($reg[2]);
         $str = $reg[1];
      }

         // Find GROUP BY:
      if (eregi('^(.*)[[:space:]]+GROUP[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._]+)$',$str,$reg))   {
         $wgolParts['GROUPBY'] = trim($reg[2]);
         $str = $reg[1];
      }

         // Rest is assumed to be "WHERE" clause:
      $wgolParts['WHERE'] = $str;

      return $wgolParts;
   }















   /**************************************
    *
    * MySQL wrapper functions
    * (For use in your applications)
    *
    **************************************/

   function sql($db,$query)   {
      $res = mysql_query($query, $this->link);
      if ($this->debugOutput) $this->debug('sql');
      return $res;
   }

   function sql_query($query) {
      $res = mysql_query($query, $this->link);
      if ($this->debugOutput) $this->debug('sql_query');
      return $res;
   }

   function sql_error() {
      return mysql_error($this->link);
   }

   function sql_num_rows($res)   {
      return mysql_num_rows($res);
   }

   function sql_fetch_assoc($res)   {
      return mysql_fetch_assoc($res);
   }

   function sql_fetch_row($res)  {
      return mysql_fetch_row($res);
   }

   function sql_free_result($res)   {
      return mysql_free_result($res);
   }

   function sql_insert_id()   {
      return mysql_insert_id($this->link);
   }

   function sql_affected_rows()  {
      return mysql_affected_rows($this->link);
   }

   function sql_data_seek($res,$seek)  {
      return mysql_data_seek($res,$seek);
   }

   function sql_field_type($res,$pointer) {
      return mysql_field_type($res,$pointer);
   }

   function sql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password) {
      if ($GLOBALS['TYPO3_CONF_VARS']['SYS']['no_pconnect'])   {
         $this->link = mysql_connect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password);
      } else {
         $this->link = mysql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password);
      }
      return $this->link;
   }

   function sql_select_db($TYPO3_db)   {
      return mysql_select_db($TYPO3_db, $this->link);
   }










   /**************************************
    *
    * SQL admin functions
    * (For use in the Install Tool and Extension Manager)
    *
    **************************************/

   function admin_get_dbs()   {
      $dbArr = array();
      $db_list = mysql_list_dbs($this->link);
      while ($row = mysql_fetch_object($db_list)) {
         if ($this->sql_select_db($row->Database)) {
            $dbArr[] = $row->Database;
         }
      }
      return $dbArr;
   }

   function admin_get_tables()   {
      $whichTables = array();
      $tables_result = mysql_list_tables(TYPO3_db, $this->link);
      if (!mysql_error())  {
         while ($theTable = mysql_fetch_assoc($tables_result)) {
            $whichTables[current($theTable)] = current($theTable);
         }
      }
      return $whichTables;
   }

   function admin_get_fields($tableName)  {
      $output = array();

      $columns_res = mysql_query('SHOW columns FROM '.$tableName, $this->link);
      while($fieldRow = mysql_fetch_assoc($columns_res)) {
         $output[$fieldRow["Field"]] = $fieldRow;
      }

      return $output;
   }

   function admin_get_keys($tableName) {
      $output = array();

      $keyRes = mysql_query('SHOW keys FROM '.$tableName, $this->link);
      while($keyRow = mysql_fetch_assoc($keyRes))  {
         $output[] = $keyRow;
      }

      return $output;
   }

   function admin_query($query)  {
      $res = mysql_query($query, $this->link);
      if ($this->debugOutput) $this->debug('admin_query');
      return $res;
   }










   /******************************
    *
    * Debugging
    *
    ******************************/

   function debug($func)   {

      $error = $this->sql_error();
      if ($error)    {
         echo t3lib_div::view_array(array(
            'caller' => 't3lib_DB::'.$func,
            'ERROR' => $error,
            'lastBuiltQuery' => $this->debug_lastBuiltQuery,
            'debug_backtrace' => function_exists('debug_backtrace') ? next(debug_backtrace()) : 'N/A'
         ));
      }
   }
}


if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_db.php'])   {
   include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_db.php']);
}
?>

---