[Typo3-dev] DBAL issues solved for postgresql and mysql

[Ries van Twisk]

Dear developers,

we found some isues using dbal with the folowing functions, comments are added
with the marking RVT.
Please note that the functions 'searchQuery' and 'listQuery' are UNTESTED!!!!!

The changed are made in such a way that it doesn't break existing code (while
using the backend mainly, adding deleting pages etc.)

We found this issues because we where using the functions in DBAL like
exec_INSERTquery(), exec_UPDATEquery(), exec_SELECTquery

We currently use postgres 7.4.6 and mysql version 4.0.21 and typo3 version 3.7.0

Pleae contact me for more information
cheers,
Ries
www.livetravelguides.com (typo3 enabled ;) )





// IN ux_t3lib_DB::quoteStr($str, $table)
/**
	 * Perform context sensitive escaping of strings. This may be "addslashes()" or
whatever method fits the current database driver.
	 *
	 * @param	string		Input string
	 * @param	string		Table name for which to quote string. Just enter which
table(s) that goes into the query. Important for detection of DBMS handler of
the query!
	 * @return	string		Output string; Quotes (" / ') and \ will be backslashed
according to DBMS used.
	 */
	function quoteStr($str, $table)	{
		$this->lastHandlerKey = $this->handler_getFromTableList($table);
		switch((string)$this->handlerCfg[$this->lastHandlerKey]['type'])	{
			case 'native':
				$str = addslashes($str);
			break;
			case 'adodb':
				$str = $this->handlerInstance[$this->lastHandlerKey]->qstr($str);
				// RVT remove begin and end quotes which has been added by adodb, addslashes
does not do such a thing
				// so we always return a quoted string WITHOUT begin and end quotes!!!!!
				$str = substr($str, 1, strlen($str)-2);
			break;
			case 'pear':
				$str = $this->handlerInstance[$this->lastHandlerKey]->quoteString($str);
			break;
			case 'userdefined':
				$str = $this->handlerInstance[$this->lastHandlerKey]->quoteStr($str);
			break;
			default:
				die('No handler found!!!');
			break;
		}

		return $str;
	}


// in t3lib_DB::INSERTquery($table,$fields_values)
	/**
	 * Creates an INSERT SQL-statement for $table from the array with field/value
pairs $fields_values.
	 * Usage count/core: 4
	 *
	 * @param	string		See exec_INSERTquery()
	 * @param	array		See exec_INSERTquery()
	 * @return	string		Full SQL query for INSERT (unless $fields_values does not
contain any elements in which case it will be false)
	 * @depreciated			use exec_INSERTquery() instead if possible!
	 */
	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)	{
				// RVT In case of postgress we don't want to quote a DEFAULT!!
				// is THIS the right place to check for this condition???, maby better to
override this function in DBAL!!!
				if ($v != 'DEFAULT') {
				  // RVT add begin and end quotes
				  $fields_values[$k] = '\''.$this->quoteStr($v, $table).'\'';
				}
			}
				// Build query:
			// RVT removed the double quote entry (") and use a comma (,) instead which
should work correct with mysql and others
			$query = 'INSERT INTO '.$table.'
				(
					'.implode(',
					',array_keys($fields_values)).'
				) VALUES (
					'.implode(',',$fields_values).'
				)';
				// Return query:
			if ($this->debugOutput) $this->debug_lastBuiltQuery = $query;
			return $query;
		}
	}



// in t3lib_DB::UPDATEquery($table,$where,$fields_values)
/**
	 * 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()
	 * @return	string		Full SQL query for UPDATE (unless $fields_values does not
contain any elements in which case it will be false)
	 * @depreciated			use exec_UPDATEquery() instead if possible!
	 */
	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)	{
					// RVT In case of postgress we don't want to quote a DEFAULT!!
			  		// is THIS the right place to check for this condition???, maby better to
override this function in DBAL!!!
				    if ($v != 'DEFAULT') {
				    	// Use of single quotes (') instead of double quotes (")
						$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() !');
		}
	}

// in t3lib_DB::listQuery($field, $value, $table)
	/**
	 * Returns a WHERE clause that can find a value ($value) in a list field
($field)
	 * 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).
	 * Is nice to look up list-relations to records or files in TYPO3 database
tables.
	 *
	 * @param	string		Field name
	 * @param	string		Value to find in list
	 * @param	string		Table in which we are searching (for DBAL detection of
quoteStr() method)
	 * @return	string		WHERE clause for a query
	 */
	function listQuery($field, $value, $table)	{
		$command = $this->quoteStr($value, $table);
		// RVT replaced double quotes (") with single quotes (') UNTESTED!!!!!!!
		$where = '('.$field.' LIKE \'%,'.$command.',%\' OR '.$field.' LIKE
\''.$command.',%\' OR '.$field.' LIKE \'%,'.$command.'\' OR
'.$field.'=\''.$command.'\')';
		return $where;
	}

// in t3lib_DB::searchQuery($searchWords,$fields,$table)
	/**
	 * 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.
	 *
	 * @param	array		Array of search words
	 * @param	array		Array of fields
	 * @param	string		Table in which we are searching (for DBAL detection of
quoteStr() method)
	 * @return	string		WHERE clause for search
	 */
	function searchQuery($searchWords,$fields,$table)	{
		$queryParts = array();

		foreach($searchWords as $sw)	{
			// RVT replaced double quotes (") with single quotes (') UNTESTED!!!!!!!
			$like=' LIKE \'%'.$this->quoteStr($sw, $table).'%\'';
			$queryParts[] = $table.'.'.implode($like.' OR '.$table.'.',$fields).$like;
		}
		$query = '('.implode(') AND (',$queryParts).')';
		return $query ;
	}