Protect identifiers test

gistfile1.php

<?php

class db {

	var $_protect_identifiers	= FALSE;
	var $_reserved_identifiers	= array('*');
	var $_escape_char = '`';
	var $qb_aliased_tables = array();
	
	public function escape_identifiers($item)
	{
		if ($this->_escape_char === '' OR empty($item))
		{
			return $item;
		}
		elseif (is_array($item))
		{
			foreach ($item as $key => $value)
			{
				$item[$key] = $this->escape_identifiers($value);
			}

			return $item;
		}
		// Avoid breaking functions and literal values inside queries
		elseif (ctype_digit($item) OR $item[0] === "'" OR ($this->_escape_char !== '"' && $item[0] === '"') OR strpos($item, '(') !== FALSE)
		{
			return $item;
		}

		static $preg_ec = array();

		if (empty($preg_ec))
		{
			if (is_array($this->_escape_char))
			{
				$preg_ec = array(
						preg_quote($this->_escape_char[0]), preg_quote($this->_escape_char[1]),
						$this->_escape_char[0], $this->_escape_char[1]
						);
			}
			else
			{
				$preg_ec[0] = $preg_ec[1] = preg_quote($this->_escape_char);
				$preg_ec[2] = $preg_ec[3] = $this->_escape_char;
			}
		}

		foreach ($this->_reserved_identifiers as $id)
		{
			if (strpos($item, '.'.$id) !== FALSE)
			{
				return preg_replace('/'.$preg_ec[0].'?([^'.$preg_ec[1].'\.]+)'.$preg_ec[1].'?\./i', $preg_ec[2].'$1'.$preg_ec[3].'.', $item);
			}
		}

		return preg_replace('/'.$preg_ec[0].'?([^'.$preg_ec[1].'\.]+)'.$preg_ec[1].'?(\.)?/i', $preg_ec[2].'$1'.$preg_ec[3].'$2', $item);
	}
	
	public function protect_identifiers($item, $prefix_single = FALSE, $protect_identifiers = NULL, $field_exists = TRUE)
		{
			if ( ! is_bool($protect_identifiers))
			{
				$protect_identifiers = $this->_protect_identifiers;
			}

			if (is_array($item))
			{
				$escaped_array = array();
				foreach ($item as $k => $v)
				{
					$escaped_array[$this->protect_identifiers($k)] = $this->protect_identifiers($v, $prefix_single, $protect_identifiers, $field_exists);
				}

				return $escaped_array;
			}

			// This is basically a bug fix for queries that use MAX, MIN, etc.
			// If a parenthesis is found we know that we do not need to
			// escape the data or add a prefix. There's probably a more graceful
			// way to deal with this, but I'm not thinking of it -- Rick
			if (strpos($item, '(') !== FALSE)
			{
				return $item;
			}

			// Convert tabs or multiple spaces into single spaces
			$item = preg_replace('/\s+/', ' ', $item);

			// If the item has an alias declaration we remove it and set it aside.
			// Note: strripos() is used in order to support spaces in table names
			if ($offset = strripos($item, ' AS '))
			{
				$alias = ($protect_identifiers)
						? substr($item, $offset, 4).$this->escape_identifiers(substr($item, $offset + 4))
						: substr($item, $offset);
				$item = substr($item, 0, $offset);
			}
			elseif ($offset = strrpos($item, ' '))
			{
				$alias = ($protect_identifiers)
						? ' '.$this->escape_identifiers(substr($item, $offset + 1))
						: substr($item, $offset);
				$item = substr($item, 0, $offset);
			}
			else
			{
				$alias = '';
			}

			// Break the string apart if it contains periods, then insert the table prefix
			// in the correct location, assuming the period doesn't indicate that we're dealing
			// with an alias. While we're at it, we will escape the components
			if (strpos($item, '.') !== FALSE)
			{
				$parts	= explode('.', $item);

				// Does the first segment of the exploded item match
				// one of the aliases previously identified? If so,
				// we have nothing more to do other than escape the item
				if (in_array($parts[0], $this->qb_aliased_tables))
				{
					if ($protect_identifiers === TRUE)
					{
						foreach ($parts as $key => $val)
						{
							if ( ! in_array($val, $this->_reserved_identifiers))
							{
								$parts[$key] = $this->escape_identifiers($val);
							}
						}

						$item = implode('.', $parts);
					}

					return $item.$alias;
				}

				// Is there a table prefix defined in the config file? If not, no need to do anything
				if ($this->dbprefix !== '')
				{
					// We now add the table prefix based on some logic.
					// Do we have 4 segments (hostname.database.table.column)?
					// If so, we add the table prefix to the column name in the 3rd segment.
					if (isset($parts[3]))
					{
						$i = 2;
					}
					// Do we have 3 segments (database.table.column)?
					// If so, we add the table prefix to the column name in 2nd position
					elseif (isset($parts[2]))
					{
						$i = 1;
					}
					// Do we have 2 segments (table.column)?
					// If so, we add the table prefix to the column name in 1st segment
					else
					{
						$i = 0;
					}

					// This flag is set when the supplied $item does not contain a field name.
					// This can happen when this function is being called from a JOIN.
					if ($field_exists === FALSE)
					{
						$i++;
					}

					// Verify table prefix and replace if necessary
					if ($this->swap_pre !== '' && strpos($parts[$i], $this->swap_pre) === 0)
					{
						$parts[$i] = preg_replace('/^'.$this->swap_pre.'(\S+?)/', $this->dbprefix.'\\1', $parts[$i]);
					}
					// We only add the table prefix if it does not already exist
					elseif (strpos($parts[$i], $this->dbprefix) !== 0)
					{
						$parts[$i] = $this->dbprefix.$parts[$i];
					}

					// Put the parts back together
					$item = implode('.', $parts);
				}

				if ($protect_identifiers === TRUE)
				{
					$item = $this->escape_identifiers($item);
				}

				return $item.$alias;
			}

			// Is there a table prefix? If not, no need to insert it
			if ($this->dbprefix !== '')
			{
				// Verify table prefix and replace if necessary
				if ($this->swap_pre !== '' && strpos($item, $this->swap_pre) === 0)
				{
					$item = preg_replace('/^'.$this->swap_pre.'(\S+?)/', $this->dbprefix.'\\1', $item);
				}
				// Do we prefix an item with no segments?
				elseif ($prefix_single === TRUE && strpos($item, $this->dbprefix) !== 0)
				{
					$item = $this->dbprefix.$item;
				}
			}

			if ($protect_identifiers === TRUE && ! in_array($item, $this->_reserved_identifiers))
			{
				$item = $this->escape_identifiers($item);
			}

			return $item.$alias;
		}
	
}

$db = new db();
$db->dbprefix = 'cms_';

$foo = $db->protect_identifiers(array(
	'hostname.database.table.column',
	'database.table.column',
	'table.column',
	'table.column AS c'
));

$db->_protect_identifiers = TRUE;

$bar = $db->protect_identifiers(array(
	'hostname.database.table.column',
	'database.table.column',
	'table.column',
	'table.column AS c'
));

var_dump($foo);
var_dump($bar);