"TYPO3 4.0.1: typo3_src-4.0.1/t3lib/class.t3lib_db.php Source File", "datetime" => "Sat Dec 2 19:22:17 2006", "date" => "2 Dec 2006", "doxygenversion" => "1.4.6", "projectname" => "TYPO3 4.0.1", "projectnumber" => "4.0.1" ); get_header($doxygen_vars); ?>

class.t3lib_db.php

00001 <?php
00002 /***************************************************************
00003 *  Copyright notice
00004 *
00005 *  (c) 2004-2006 Kasper Skaarhoj (kasperYYYY@typo3.com)
00006 *  All rights reserved
00007 *
00008 *  This script is part of the TYPO3 project. The TYPO3 project is
00009 *  free software; you can redistribute it and/or modify
00010 *  it under the terms of the GNU General Public License as published by
00011 *  the Free Software Foundation; either version 2 of the License, or
00012 *  (at your option) any later version.
00013 *
00014 *  The GNU General Public License can be found at
00015 *  http://www.gnu.org/copyleft/gpl.html.
00016 *  A copy is found in the textfile GPL.txt and important notices to the license
00017 *  from the author is found in LICENSE.txt distributed with these scripts.
00018 *
00019 *
00020 *  This script is distributed in the hope that it will be useful,
00021 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
00022 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
00023 *  GNU General Public License for more details.
00024 *
00025 *  This copyright notice MUST APPEAR in all copies of the script!
00026 ***************************************************************/
00138 class t3lib_DB {
00139 
00140 
00141                 // Debug:
00142         var $debugOutput = FALSE;               // Set "TRUE" if you want database errors outputted.
00143         var $debug_lastBuiltQuery = '';         // Internally: Set to last built query (not necessarily executed...)
00144         var $store_lastBuiltQuery = FALSE;      // Set "TRUE" if you want the last built query to be stored in $debug_lastBuiltQuery independent of $this->debugOutput
00145 
00146                 // Default link identifier:
00147         var $link = FALSE;
00148 
00149 
00150 
00151 
00152         /************************************
00153          *
00154          * Query execution
00155          *
00156          * These functions are the RECOMMENDED DBAL functions for use in your applications
00157          * Using these functions will allow the DBAL to use alternative ways of accessing data (contrary to if a query is returned!)
00158          * They compile a query AND execute it immediately and then return the result
00159          * This principle heightens our ability to create various forms of DBAL of the functions.
00160          * Generally: We want to return a result pointer/object, never queries.
00161          * Also, having the table name together with the actual query execution allows us to direct the request to other databases.
00162          *
00163          **************************************/
00164 
00175         function exec_INSERTquery($table,$fields_values,$no_quote_fields=FALSE) {
00176                 $res = mysql_query($this->INSERTquery($table,$fields_values,$no_quote_fields), $this->link);
00177                 if ($this->debugOutput) $this->debug('exec_INSERTquery');
00178                 return $res;
00179         }
00180 
00192         function exec_UPDATEquery($table,$where,$fields_values,$no_quote_fields=FALSE)  {
00193                 $res = mysql_query($this->UPDATEquery($table,$where,$fields_values,$no_quote_fields), $this->link);
00194                 if ($this->debugOutput) $this->debug('exec_UPDATEquery');
00195                 return $res;
00196         }
00197 
00206         function exec_DELETEquery($table,$where)        {
00207                 $res = mysql_query($this->DELETEquery($table,$where), $this->link);
00208                 if ($this->debugOutput) $this->debug('exec_DELETEquery');
00209                 return $res;
00210         }
00211 
00225         function exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='')   {
00226                 $res = mysql_query($this->SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit), $this->link);
00227                 if ($this->debugOutput) $this->debug('exec_SELECTquery');
00228                 return $res;
00229         }
00230 
00250         function exec_SELECT_mm_query($select,$local_table,$mm_table,$foreign_table,$whereClause='',$groupBy='',$orderBy='',$limit='')  {
00251                 if($foreign_table == $local_table) {
00252                         $foreign_table_as = $foreign_table.uniqid('_join');
00253                 }
00254 
00255                 $mmWhere = $local_table ? $local_table.'.uid='.$mm_table.'.uid_local' : '';
00256                 $mmWhere.= ($local_table AND $foreign_table) ? ' AND ' : '';
00257                 $mmWhere.= $foreign_table ? ($foreign_table_as ? $foreign_table_as : $foreign_table).'.uid='.$mm_table.'.uid_foreign' : '';
00258 
00259                 return $GLOBALS['TYPO3_DB']->exec_SELECTquery(
00260                                         $select,
00261                                         ($local_table ? $local_table.',' : '').$mm_table.($foreign_table ? ','. $foreign_table.($foreign_table_as ? ' AS '.$foreign_table_as : '') : ''),
00262                                         $mmWhere.' '.$whereClause,              // whereClauseMightContainGroupOrderBy
00263                                         $groupBy,
00264                                         $orderBy,
00265                                         $limit
00266                                 );
00267         }
00268 
00278         function exec_SELECT_queryArray($queryParts)    {
00279                 return $this->exec_SELECTquery(
00280                                         $queryParts['SELECT'],
00281                                         $queryParts['FROM'],
00282                                         $queryParts['WHERE'],
00283                                         $queryParts['GROUPBY'],
00284                                         $queryParts['ORDERBY'],
00285                                         $queryParts['LIMIT']
00286                                 );
00287         }
00288 
00301         function exec_SELECTgetRows($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='',$uidIndexField='')       {
00302                 $res = $this->exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit);
00303                 if ($this->debugOutput) $this->debug('exec_SELECTquery');
00304 
00305                 if (!$this->sql_error())        {
00306                         $output = array();
00307 
00308                         if ($uidIndexField)     {
00309                                 while($tempRow = $this->sql_fetch_assoc($res))  {
00310                                         $output[$tempRow[$uidIndexField]] = $tempRow;
00311                                 }
00312                         } else {
00313                                 while($output[] = $this->sql_fetch_assoc($res));
00314                                 array_pop($output);
00315                         }
00316                 }
00317                 return $output;
00318         }
00319 
00320 
00321 
00322 
00323 
00324 
00325 
00326 
00327 
00328 
00329 
00330         /**************************************
00331          *
00332          * Query building
00333          *
00334          **************************************/
00335 
00346         function INSERTquery($table,$fields_values,$no_quote_fields=FALSE)      {
00347 
00348                         // Table and fieldnames should be "SQL-injection-safe" when supplied to this function (contrary to values in the arrays which may be insecure).
00349                 if (is_array($fields_values) && count($fields_values))  {
00350 
00351                                 // quote and escape values
00352                         $fields_values = $this->fullQuoteArray($fields_values,$table,$no_quote_fields);
00353 
00354                                 // Build query:
00355                         $query = 'INSERT INTO '.$table.'
00356                                 (
00357                                         '.implode(',
00358                                         ',array_keys($fields_values)).'
00359                                 ) VALUES (
00360                                         '.implode(',
00361                                         ',$fields_values).'
00362                                 )';
00363 
00364                                 // Return query:
00365                         if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;
00366                         return $query;
00367                 }
00368         }
00369 
00381         function UPDATEquery($table,$where,$fields_values,$no_quote_fields=FALSE)       {
00382 
00383                         // Table and fieldnames should be "SQL-injection-safe" when supplied to this function (contrary to values in the arrays which may be insecure).
00384                 if (is_string($where))  {
00385                         if (is_array($fields_values) && count($fields_values))  {
00386 
00387                                         // quote and escape values
00388                                 $nArr = $this->fullQuoteArray($fields_values,$table,$no_quote_fields);
00389 
00390                                 $fields = array();
00391                                 foreach ($nArr as $k => $v) {
00392                                         $fields[] = $k.'='.$v;
00393                                 }
00394 
00395                                         // Build query:
00396                                 $query = 'UPDATE '.$table.'
00397                                         SET
00398                                                 '.implode(',
00399                                                 ',$fields).
00400                                         (strlen($where)>0 ? '
00401                                         WHERE
00402                                                 '.$where : '');
00403 
00404                                         // Return query:
00405                                 if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;
00406                                 return $query;
00407                         }
00408                 } else {
00409                         die('<strong>TYPO3 Fatal Error:</strong> "Where" clause argument for UPDATE query was not a string in $this->UPDATEquery() !');
00410                 }
00411         }
00412 
00422         function DELETEquery($table,$where)     {
00423                 if (is_string($where))  {
00424 
00425                                 // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
00426                         $query = 'DELETE FROM '.$table.
00427                                 (strlen($where)>0 ? '
00428                                 WHERE
00429                                         '.$where : '');
00430 
00431                         if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;
00432                         return $query;
00433                 } else {
00434                         die('<strong>TYPO3 Fatal Error:</strong> "Where" clause argument for DELETE query was not a string in $this->DELETEquery() !');
00435                 }
00436         }
00437 
00451         function SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='')        {
00452 
00453                         // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
00454                         // Build basic query:
00455                 $query = 'SELECT '.$select_fields.'
00456                         FROM '.$from_table.
00457                         (strlen($where_clause)>0 ? '
00458                         WHERE
00459                                 '.$where_clause : '');
00460 
00461                         // Group by:
00462                 if (strlen($groupBy)>0) {
00463                         $query.= '
00464                         GROUP BY '.$groupBy;
00465                 }
00466                         // Order by:
00467                 if (strlen($orderBy)>0) {
00468                         $query.= '
00469                         ORDER BY '.$orderBy;
00470                 }
00471                         // Group by:
00472                 if (strlen($limit)>0)   {
00473                         $query.= '
00474                         LIMIT '.$limit;
00475                 }
00476 
00477                         // Return query:
00478                 if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;
00479                 return $query;
00480         }
00481 
00492         function listQuery($field, $value, $table)      {
00493                 $command = $this->quoteStr($value, $table);
00494                 $where = '('.$field.' LIKE \'%,'.$command.',%\' OR '.$field.' LIKE \''.$command.',%\' OR '.$field.' LIKE \'%,'.$command.'\' OR '.$field.'=\''.$command.'\')';
00495                 return $where;
00496         }
00497 
00506         function searchQuery($searchWords,$fields,$table)       {
00507                 $queryParts = array();
00508 
00509                 foreach($searchWords as $sw)    {
00510                         $like=' LIKE \'%'.$this->quoteStr($sw, $table).'%\'';
00511                         $queryParts[] = $table.'.'.implode($like.' OR '.$table.'.',$fields).$like;
00512                 }
00513                 $query = '('.implode(') AND (',$queryParts).')';
00514                 return $query ;
00515         }
00516 
00517 
00518 
00519 
00520 
00521 
00522 
00523 
00524 
00525 
00526 
00527 
00528 
00529 
00530 
00531 
00532         /**************************************
00533          *
00534          * Various helper functions
00535          *
00536          * Functions recommended to be used for
00537          * - escaping values,
00538          * - cleaning lists of values,
00539          * - stripping of excess ORDER BY/GROUP BY keywords
00540          *
00541          **************************************/
00542 
00552         function fullQuoteStr($str, $table)     {
00553                 if (function_exists('mysql_real_escape_string'))        {
00554                         return '\''.mysql_real_escape_string($str, $this->link).'\'';
00555                 } else {
00556                         return '\''.mysql_escape_string($str).'\'';
00557                 }
00558         }
00559 
00569         function fullQuoteArray($arr, $table, $noQuote=FALSE)   {
00570                 if (is_string($noQuote))        {
00571                         $noQuote = explode(',',$noQuote);
00572                 } elseif (!is_array($noQuote))  {       // sanity check
00573                         $noQuote = FALSE;
00574                 }
00575 
00576                 foreach($arr as $k => $v)       {
00577                         if ($noQuote===FALSE || !in_array($k,$noQuote))     {
00578                                 $arr[$k] = $this->fullQuoteStr($v, $table);
00579                         }
00580                 }
00581                 return $arr;
00582         }
00583 
00596         function quoteStr($str, $table) {
00597                 if (function_exists('mysql_real_escape_string'))        {
00598                         return mysql_real_escape_string($str, $this->link);
00599                 } else {
00600                         return mysql_escape_string($str);
00601                 }
00602         }
00603 
00612         function escapeStrForLike($str, $table) {
00613                 return preg_replace('/[_%]/','\\\$0',$str);
00614         }
00615 
00625         function cleanIntArray($arr)    {
00626                 foreach($arr as $k => $v)       {
00627                         $arr[$k] = intval($arr[$k]);
00628                 }
00629                 return $arr;
00630         }
00631 
00641         function cleanIntList($list)    {
00642                 return implode(',',t3lib_div::intExplode(',',$list));
00643         }
00644 
00655         function stripOrderBy($str)     {
00656                 return preg_replace('/^ORDER[[:space:]]+BY[[:space:]]+/i','',trim($str));
00657         }
00658 
00669         function stripGroupBy($str)     {
00670                 return preg_replace('/^GROUP[[:space:]]+BY[[:space:]]+/i','',trim($str));
00671         }
00672 
00681         function splitGroupOrderLimit($str)     {
00682                 $str = ' '.$str;        // Prepending a space to make sure "[[:space:]]+" will find a space there for the first element.
00683                         // Init output array:
00684                 $wgolParts = array(
00685                         'WHERE' => '',
00686                         'GROUPBY' => '',
00687                         'ORDERBY' => '',
00688                         'LIMIT' => ''
00689                 );
00690 
00691                         // Find LIMIT:
00692                 $reg = array();
00693                 if (preg_match('/^(.*)[[:space:]]+LIMIT[[:space:]]+([[:alnum:][:space:],._]+)$/i',$str,$reg))   {
00694                         $wgolParts['LIMIT'] = trim($reg[2]);
00695                         $str = $reg[1];
00696                 }
00697 
00698                         // Find ORDER BY:
00699                 $reg = array();
00700                 if (preg_match('/^(.*)[[:space:]]+ORDER[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._]+)$/i',$str,$reg))     {
00701                         $wgolParts['ORDERBY'] = trim($reg[2]);
00702                         $str = $reg[1];
00703                 }
00704 
00705                         // Find GROUP BY:
00706                 $reg = array();
00707                 if (preg_match('/^(.*)[[:space:]]+GROUP[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._]+)$/i',$str,$reg))     {
00708                         $wgolParts['GROUPBY'] = trim($reg[2]);
00709                         $str = $reg[1];
00710                 }
00711 
00712                         // Rest is assumed to be "WHERE" clause:
00713                 $wgolParts['WHERE'] = $str;
00714 
00715                 return $wgolParts;
00716         }
00717 
00718 
00719 
00720 
00721 
00722 
00723 
00724 
00725 
00726 
00727 
00728 
00729 
00730 
00731 
00732         /**************************************
00733          *
00734          * MySQL wrapper functions
00735          * (For use in your applications)
00736          *
00737          **************************************/
00738 
00749         function sql($db,$query)        {
00750                 $res = mysql_query($query, $this->link);
00751                 if ($this->debugOutput) $this->debug('sql');
00752                 return $res;
00753         }
00754 
00763         function sql_query($query)      {
00764                 $res = mysql_query($query, $this->link);
00765                 if ($this->debugOutput) $this->debug('sql_query');
00766                 return $res;
00767         }
00768 
00776         function sql_error()    {
00777                 return mysql_error($this->link);
00778         }
00779 
00788         function sql_num_rows($res)     {
00789                 return mysql_num_rows($res);
00790         }
00791 
00800         function sql_fetch_assoc($res)  {
00801                 return mysql_fetch_assoc($res);
00802         }
00803 
00813         function sql_fetch_row($res)    {
00814                 return mysql_fetch_row($res);
00815         }
00816 
00825         function sql_free_result($res)  {
00826                 return mysql_free_result($res);
00827         }
00828 
00836         function sql_insert_id()        {
00837                 return mysql_insert_id($this->link);
00838         }
00839 
00847         function sql_affected_rows()    {
00848                 return mysql_affected_rows($this->link);
00849         }
00850 
00860         function sql_data_seek($res,$seek)      {
00861                 return mysql_data_seek($res,$seek);
00862         }
00863 
00873         function sql_field_type($res,$pointer)  {
00874                 return mysql_field_type($res,$pointer);
00875         }
00876 
00887         function sql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password)   {
00888                 if ($GLOBALS['TYPO3_CONF_VARS']['SYS']['no_pconnect'])  {
00889                         $this->link = @mysql_connect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password);
00890                 } else {
00891                         $this->link = @mysql_pconnect($TYPO3_db_host, $TYPO3_db_username, $TYPO3_db_password);
00892                 }
00893 
00894                 if (!$this->link) {
00895                         t3lib_div::sysLog('Could not connect to Mysql server '.$TYPO3_db_host.' with user '.$TYPO3_db_username.'.','Core',4);
00896                 } else {
00897                         $setDBinit = t3lib_div::trimExplode(chr(10), $GLOBALS['TYPO3_CONF_VARS']['SYS']['setDBinit'],true);
00898                         foreach ($setDBinit as $v)      {
00899                                 if (mysql_query($v, $this->link) === FALSE)     {
00900                                         t3lib_div::sysLog('Could not initialize DB connection with query "'.$v.'": '.mysql_error($this->link),'Core',3);
00901                                 }
00902                         }
00903                 }
00904                 return $this->link;
00905         }
00906 
00915         function sql_select_db($TYPO3_db)       {
00916                 $ret = @mysql_select_db($TYPO3_db, $this->link);
00917                 if (!$ret) {
00918                         t3lib_div::sysLog('Could not select Mysql database '.$TYPO3_db.': '.mysql_error(),'Core',4);
00919         }
00920                 return $ret;
00921         }
00922 
00923 
00924 
00925 
00926 
00927 
00928 
00929 
00930 
00931 
00932         /**************************************
00933          *
00934          * SQL admin functions
00935          * (For use in the Install Tool and Extension Manager)
00936          *
00937          **************************************/
00938 
00947         function admin_get_dbs()        {
00948                 $dbArr = array();
00949                 $db_list = mysql_list_dbs($this->link);
00950                 while ($row = mysql_fetch_object($db_list)) {
00951                         if ($this->sql_select_db($row->Database))       {
00952                                 $dbArr[] = $row->Database;
00953                         }
00954                 }
00955                 return $dbArr;
00956         }
00957 
00965         function admin_get_tables()     {
00966                 $whichTables = array();
00967                 $tables_result = mysql_list_tables(TYPO3_db, $this->link);
00968                 if (!mysql_error())     {
00969                         while ($theTable = mysql_fetch_assoc($tables_result)) {
00970                                 $whichTables[current($theTable)] = current($theTable);
00971                         }
00972                 }
00973                 return $whichTables;
00974         }
00975 
00984         function admin_get_fields($tableName)   {
00985                 $output = array();
00986 
00987                 $columns_res = mysql_query('SHOW columns FROM '.$tableName, $this->link);
00988                 while($fieldRow = mysql_fetch_assoc($columns_res))      {
00989                         $output[$fieldRow['Field']] = $fieldRow;
00990                 }
00991 
00992                 return $output;
00993         }
00994 
01002         function admin_get_keys($tableName)     {
01003                 $output = array();
01004 
01005                 $keyRes = mysql_query('SHOW keys FROM '.$tableName, $this->link);
01006                 while($keyRow = mysql_fetch_assoc($keyRes))     {
01007                         $output[] = $keyRow;
01008                 }
01009 
01010                 return $output;
01011         }
01012 
01020         function admin_query($query)    {
01021                 $res = mysql_query($query, $this->link);
01022                 if ($this->debugOutput) $this->debug('admin_query');
01023                 return $res;
01024         }
01025 
01026 
01027 
01028 
01029 
01030 
01031 
01032 
01033 
01034 
01035 
01036 
01037         /******************************
01038          *
01039          * Connecting service
01040          *
01041          ******************************/
01042 
01048         function connectDB()    {
01049                 if ($this->sql_pconnect(TYPO3_db_host, TYPO3_db_username, TYPO3_db_password))   {
01050                         if (!TYPO3_db)  {
01051                                 die('No database selected');
01052                                 exit;
01053                         } elseif (!$this->sql_select_db(TYPO3_db))      {
01054                                 die('Cannot connect to the current database, "'.TYPO3_db.'"');
01055                                 exit;
01056                         }
01057                 } else {
01058                         die('The current username, password or host was not accepted when the connection to the database was attempted to be established!');
01059                         exit;
01060                 }
01061         }
01062 
01063 
01064 
01065 
01066 
01067 
01068 
01069 
01070 
01071 
01072 
01073 
01074         /******************************
01075          *
01076          * Debugging
01077          *
01078          ******************************/
01079 
01086         function debug($func)   {
01087 
01088                 $error = $this->sql_error();
01089                 if ($error)             {
01090                         echo t3lib_div::view_array(array(
01091                                 'caller' => 't3lib_DB::'.$func,
01092                                 'ERROR' => $error,
01093                                 'lastBuiltQuery' => $this->debug_lastBuiltQuery,
01094                                 'debug_backtrace' => t3lib_div::debug_trail()
01095                         ));
01096                 }
01097         }
01098 }
01099 
01100 
01101 if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_db.php'])        {
01102         include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_db.php']);
01103 }
01104 ?>