class.ux_t3lib_db.php

Typo3, 开源里边最强大的

			}
			else {
				$query = 'INSERT INTO '.$this->quoteFromTables($table).'
				(
					'.implode(',
					',array_keys($nArr)).'
				) VALUES (
					'.implode(',
					',$nArr).'
				)';

				if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;
			}

			return $query;
		}
	}

	/**
	 * Creates an UPDATE SQL-statement for $table where $where-clause (typ. 'uid=...') from the array with field/value pairs $fields_values.
	 * Usage count/core: 6
	 *
	 * @param	string		See exec_UPDATEquery()
	 * @param	string		See exec_UPDATEquery()
	 * @param	array		See exec_UPDATEquery()
	 * @param mixed		See exec_UPDATEquery()
	 * @return	mixed		Full SQL query for UPDATE as string or array (unless $fields_values does not contain any elements in which case it will be false). If BLOB fields will be affected and one is not running the native type, an array will be returned, where 0 => plain SQL, 1 => fieldname/value pairs of BLOB fields
	 * @deprecated			use exec_UPDATEquery() instead if possible!
	 */
	function UPDATEquery($table,$where,$fields_values,$no_quote_fields='')	{
		// 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))	{

				if (is_string($no_quote_fields))        {
					$no_quote_fields = explode(',',$no_quote_fields);
				} elseif (!is_array($no_quote_fields))  {
					$no_quote_fields = array();
				}

				$blobfields = array();
				$nArr = array();
				foreach($fields_values as $k => $v)	{
					if(!$this->runningNative() && $this->sql_field_metatype($table,$k) == 'B') {
							// we skip the field in the regular UPDATE statement, it is only in blobfields
						$blobfields[$this->quoteFieldNames($k)] = $v;
					}
					else {
							// Add slashes old-school:
							// cast numeric values
						$mt = $this->sql_field_metatype($table,$k);
						$v = (($mt{0}=='I')||($mt{0}=='F')) ? (int)$v : $v;
						$nArr[] = $this->quoteFieldNames($k).'='.((!in_array($k,$no_quote_fields)) ? $this->fullQuoteStr($v, $table) : $v);
					}
				}

				if(count($blobfields)) {
					if(count($nArr)) {
						$query[0] = 'UPDATE '.$this->quoteFromTables($table).'
						SET
							'.implode(',
							',$nArr).
							(strlen($where)>0 ? '
						WHERE
							'.$this->quoteWhereClause($where) : '');
					}
					$query[1] = $blobfields;
					if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query[0];
				}
				else {
					$query = 'UPDATE '.$this->quoteFromTables($table).'
					SET
						'.implode(',
						',$nArr).
						(strlen($where)>0 ? '
					WHERE
						'.$this->quoteWhereClause($where) : '');

						if ($this->debugOutput || $this->store_lastBuiltQuery) $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() !');
		}
	}

	/**
	 * Creates a DELETE SQL-statement for $table where $where-clause
	 * Usage count/core: 3
	 *
	 * @param	string		See exec_DELETEquery()
	 * @param	string		See exec_DELETEquery()
	 * @return	string		Full SQL query for DELETE
	 * @deprecated			use exec_DELETEquery() instead if possible!
	 */
	function DELETEquery($table,$where)	{
		if (is_string($where))	{
			$table = $this->quoteFromTables($table);
			$where = $this->quoteWhereClause($where);

			$query = parent::DELETEquery($table, $where);

			if ($this->debugOutput || $this->store_lastBuiltQuery) $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() !');
		}
	}

	/**
	 * Creates a SELECT SQL-statement
	 * Usage count/core: 11
	 *
	 * @param	string		See exec_SELECTquery()
	 * @param	string		See exec_SELECTquery()
	 * @param	string		See exec_SELECTquery()
	 * @param	string		See exec_SELECTquery()
	 * @param	string		See exec_SELECTquery()
	 * @param	string		See exec_SELECTquery()
	 * @return	string		Full SQL query for SELECT
	 * @deprecated			use exec_SELECTquery() instead if possible!
	 */
	function SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='')	{

		$select_fields = $this->quoteFieldNames($select_fields);
		$from_table = $this->quoteFromTables($from_table);
		$where_clause = $this->quoteWhereClause($where_clause);
		$groupBy = $this->quoteGroupBy($groupBy);
		$orderBy = $this->quoteOrderBy($orderBy);

		// call parent method to build actual query
		$query = parent::SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit);

		if ($this->debugOutput || $this->store_lastBuiltQuery) $this->debug_lastBuiltQuery = $query;

		return $query;
	}


	/**************************************
	*
	* Functions for quoting table/field names
	*
	**************************************/

	/**
	 * Quotes field (and table) names with the quote character suitable for the DB being used
	 * Use quoteFieldNames instead!
	 *
	 * @param	string		List of fields to be selected from DB
	 * @return	string		Quoted list of fields to be selected from DB
	 * @deprecated
	 */
	function quoteSelectFields($select_fields) {
		$this->quoteFieldNames($select_fields);
	}

	/**
	 * Quotes field (and table) names with the quote character suitable for the DB being used
	 *
	 * @param	string		List of fields to be used in query to DB
	 * @return	string		Quoted list of fields to be in query to DB
	 */
	function quoteFieldNames($select_fields) {
		if($select_fields == '') return '';
		if($this->runningNative()) return $select_fields;

		$select_fields = $this->SQLparser->parseFieldList($select_fields);
		foreach($select_fields as $k => $v)	{
			if($select_fields[$k]['field'] != '' && $select_fields[$k]['field'] != '*') {
				$select_fields[$k]['field'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$select_fields[$k]['field'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
			}
			if($select_fields[$k]['table'] != '') {
				$select_fields[$k]['table'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$select_fields[$k]['table'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
			}
			if($select_fields[$k]['as'] != '') {
				$select_fields[$k]['as'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$select_fields[$k]['as'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
			}
			if(isset($select_fields[$k]['func_content.']) && $select_fields[$k]['func_content.'][0]['func_content'] != '*'){
				if(strstr($select_fields[$k]['func_content.'][0]['func_content'],'.')) {
					$select_fields[$k]['func_content.'][0]['func_content'] = $this->quoteFieldNames($select_fields[$k]['func_content.'][0]['func_content']);
					$select_fields[$k]['func_content'] = $this->quoteFieldNames($select_fields[$k]['func_content']);
				}
				else {
					$select_fields[$k]['func_content.'][0]['func_content'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$select_fields[$k]['func_content.'][0]['func_content'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
					$select_fields[$k]['func_content'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$select_fields[$k]['func_content'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
				}
			}
		}

		return $this->SQLparser->compileFieldList($select_fields);
	}

	/**
	 * Quotes table names with the quote character suitable for the DB being used
	 *
	 * @param	string		List of tables to be selected from DB
	 * @return	string		Quoted list of tables to be selected from DB
	 */
	function quoteFromTables($from_table) {
		if($from_table == '') return '';
		if($this->runningNative()) return $from_table;

		$from_table = $this->SQLparser->parseFromTables($from_table);
		foreach($from_table as $k => $v)	{
			$from_table[$k]['table'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$from_table[$k]['table'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
			if($from_table[$k]['as'] != '') {
				$from_table[$k]['as'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$from_table[$k]['as'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
			}
			if (is_array($v['JOIN']))	{
				$from_table[$k]['JOIN']['withTable'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$from_table[$k]['JOIN']['withTable'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
				$from_table[$k]['JOIN']['ON'][0]['table'] = ($from_table[$k]['JOIN']['ON'][0]['table']) ? $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$from_table[$k]['JOIN']['ON'][0]['table'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote : '';
				$from_table[$k]['JOIN']['ON'][0]['field'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$from_table[$k]['JOIN']['ON'][0]['field'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
				$from_table[$k]['JOIN']['ON'][1]['table'] = ($from_table[$k]['JOIN']['ON'][1]['table']) ? $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$from_table[$k]['JOIN']['ON'][1]['table'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote : '';
				$from_table[$k]['JOIN']['ON'][1]['field'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$from_table[$k]['JOIN']['ON'][1]['field'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
			}
		}
		return $this->SQLparser->compileFromTables($from_table);
	}

	/**
	 * Quotes the field (and table) names within a where clause with the quote character suitable for the DB being used
	 *
	 * @param	string		A where clause that can e parsed by parseWhereClause
	 * @return	string		Usable where clause with quoted field/table names
	 */
	function quoteWhereClause($where_clause) {
		if($where_clause == '') return '';
		if($this->runningNative()) return $where_clause;

		$where_clause = $this->SQLparser->parseWhereClause($where_clause);
		$where_clause = $this->_quoteWhereClause($where_clause);
		$where_clause = $this->SQLparser->compileWhereClause($where_clause);

		return $where_clause;
	}

	/**
	 * [Describe function...]
	 *
	 * @param	[type]		$$groupBy: ...
	 * @return	[type]		...
	 */
	function _quoteWhereClause($where_clause) {
		foreach($where_clause as $k => $v)	{
			// Look for sublevel:
			if (is_array($where_clause[$k]['sub']))	{
				$where_clause[$k]['sub'] = $this->_quoteWhereClause($where_clause[$k]['sub']);
			} else {
				if($where_clause[$k]['table'] != '') {
					$where_clause[$k]['table'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$where_clause[$k]['table'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
				}
				if(!is_numeric($where_clause[$k]['field'])) {
					$where_clause[$k]['field'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$where_clause[$k]['field'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
				}
			}
			if ($where_clause[$k]['comparator'])	{
				// Detecting value type; list or plain:
				if ((!isset($where_clause[$k]['value'][1]) || $where_clause[$k]['value'][1] == '') && is_string($where_clause[$k]['value'][0]) && strstr($where_clause[$k]['value'][0], '.') && !t3lib_div::inList('NOTIN,IN',strtoupper(str_replace(array(" ","\n","\r","\t"),'',$where_clause[$k]['comparator']))))	{
					$where_clause[$k]['value'][0] = $this->quoteFieldNames($where_clause[$k]['value'][0]);
				}
			}
		}

		return $where_clause;
	}

	/**
	 * [Describe function...]
	 *
	 * @param	[type]		$$groupBy: ...
	 * @return	[type]		...
	 */
	function quoteGroupBy($groupBy) {
		if($groupBy == '') return '';
		if($this->runningNative()) return $groupBy;

		$groupBy = $this->SQLparser->parseFieldList($groupBy);
		foreach($groupBy as $k => $v)	{
			$groupBy[$k]['field'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$groupBy[$k]['field'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
			if($groupBy[$k]['table'] != '') {
				$groupBy[$k]['table'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$groupBy[$k]['table'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
			}
		}
		return $this->SQLparser->compileFieldList($groupBy);
	}

	/**
	 * [Describe function...]
	 *
	 * @param	[type]		$$orderBy: ...
	 * @return	[type]		...
	 */
	function quoteOrderBy($orderBy) {
		if($orderBy == '') return '';
		if($this->runningNative()) return $orderBy;

		$orderBy = $this->SQLparser->parseFieldList($orderBy);
		foreach($orderBy as $k => $v)	{
			$orderBy[$k]['field'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$orderBy[$k]['field'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
			if($orderBy[$k]['table'] != '') {
				$orderBy[$k]['table'] = $this->handlerInstance[$this->lastHandlerKey]->nameQuote.$orderBy[$k]['table'].$this->handlerInstance[$this->lastHandlerKey]->nameQuote;
			}
		}
		return $this->SQLparser->compileFieldList($orderBy);
	}



	/**************************************
	*
	* Various helper functions
	*
	**************************************/

	/**
	 * Escaping and quoting values for SQL statements.
	 *
	 * @param	string		Input string
	 * @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!).
	 * @return	string		Output string; Wrapped in single quotes and quotes in the string (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
	 * @see quoteStr()
	 */
	function fullQuoteStr($str,$table) {
		return '\''.$this->quoteStr($str, $table).'\'';
	}

	/**
	 * Substitution for PHP function "addslashes()"
	 * 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()!
	 *
	 * @param	string		Input string
	 * @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!).
	 * @return	string		Output string; Quotes (" / ') and \ will be backslashed (or otherwise based on DBAL handler)
	 * @see quoteStr()
	 */
	function quoteStr($str, $table)	{
		$this->lastHandlerKey = $this->handler_getFromTableList($table);
		switch((string)$this->handlerCfg[$this->lastHandlerKey]['type'])	{
			case 'native':
				$str = mysql_real_escape_string($str, $this->handlerInstance[$this->lastHandlerKey]['link']);
				break;
			case 'adodb':
				$str = substr($this->handlerInstance[$this->lastHandlerKey]->qstr($str),1,-1);
				break;
			case 'userdefined':
				$str = $this->handlerInstance[$this->lastHandlerKey]->quoteStr($str);
				break;
			default:
				die('No handler found!!!');
				break;
		}

		return $str;
	}