Fixed a number of bug reports related to table/db names not being escaped or prefixed correctly.

1 files changed, 222 insertions, 112 deletions

diff --git a/system/database/DB_driver.php b/system/database/DB_driver.php
index 572595f42..9508ded8c 100644
--- a/system/database/DB_driver.php
+++ b/system/database/DB_driver.php
@@ -61,6 +61,9 @@ class CI_DB_driver {
 	var $cache_autodel	= FALSE;

 	var $CACHE; // The cache class object

 

+	// Private variables

+	var $_protect_identifiers	= TRUE;

+	var $_reserved_identifiers	= array('*'); // Identifiers that should NOT be escaped

 

 	// These are use with Oracle

 	var $stmt_id;

@@ -97,19 +100,21 @@ class CI_DB_driver {
 	 * @param	mixed

 	 * @return	void

 	 */	

-	function initialize($create_db = FALSE)

+	function initialize()

 	{

-		// If an existing DB connection resource is supplied

+		// If an existing connection resource is available

 		// there is no need to connect and select the database

 		if (is_resource($this->conn_id) OR is_object($this->conn_id))

 		{

 			return TRUE;

 		}

+	

+		// ----------------------------------------------------------------

 		

-		// Connect to the database

+		// Connect to the database and set the connection ID

 		$this->conn_id = ($this->pconnect == FALSE) ? $this->db_connect() : $this->db_pconnect();

 

-		// No connection?  Throw an error

+		// No connection resource?  Throw an error

 		if ( ! $this->conn_id)

 		{

 			log_message('error', 'Unable to connect to the database');

@@ -121,70 +126,30 @@ class CI_DB_driver {
 			return FALSE;

 		}

 

-		// Select the database

+		// ----------------------------------------------------------------

+

+		// Select the DB... assuming a database name is specified in the config file

 		if ($this->database != '')

 		{

 			if ( ! $this->db_select())

 			{

-				// Should we attempt to create the database?

-				if ($create_db == TRUE)

-				{ 

-					// Load the DB utility class

-					$CI =& get_instance();

-					$CI->load->dbutil();

-					

-					// Create the DB

-					if ( ! $CI->dbutil->create_database($this->database))

-					{

-						log_message('error', 'Unable to create database: '.$this->database);

-					

-						if ($this->db_debug)

-						{

-							$this->display_error('db_unable_to_create', $this->database);

-						}

-						return FALSE;

-					}

-					else

-					{

-						// In the event the DB was created we need to select it

-						if ($this->db_select())

-						{

-							if ( ! $this->db_set_charset($this->char_set, $this->dbcollat))

-							{

-								log_message('error', 'Unable to set database connection charset: '.$this->char_set);

-

-								if ($this->db_debug)

-								{

-									$this->display_error('db_unable_to_set_charset', $this->char_set);

-								}

-

-								return FALSE;

-							}

-							

-							return TRUE;

-						}

-					}

-				}

-			

 				log_message('error', 'Unable to select database: '.$this->database);

 			

 				if ($this->db_debug)

 				{

 					$this->display_error('db_unable_to_select', $this->database);

 				}

-				return FALSE;

+				return FALSE;			

 			}

-			

-			if ( ! $this->db_set_charset($this->char_set, $this->dbcollat))

+			else

 			{

-				log_message('error', 'Unable to set database connection charset: '.$this->char_set);

-			

-				if ($this->db_debug)

+				// We've selected the DB. Now we set the character set

+				if ( ! $this->db_set_charset($this->char_set, $this->dbcollat))

 				{

-					$this->display_error('db_unable_to_set_charset', $this->char_set);

+					return FALSE;

 				}

-				

-				return FALSE;

+		

+				return TRUE;

 			}

 		}

 

@@ -194,6 +159,33 @@ class CI_DB_driver {
 	// --------------------------------------------------------------------

 

 	/**

+	 * Set client character set

+	 *

+	 * @access	public

+	 * @param	string

+	 * @param	string

+	 * @return	resource

+	 */

+	function db_set_charset($charset, $collation)

+	{

+		if ( ! $this->_db_set_charset($this->char_set, $this->dbcollat))

+		{

+			log_message('error', 'Unable to set database connection charset: '.$this->char_set);

+		

+			if ($this->db_debug)

+			{

+				$this->display_error('db_unable_to_set_charset', $this->char_set);

+			}

+			

+			return FALSE;

+		}

+		

+		return TRUE;

+	}

+	

+	// --------------------------------------------------------------------

+

+	/**

 	 * The name of the platform in use (mysql, mssql, etc...)

 	 *

 	 * @access	public

@@ -667,23 +659,6 @@ class CI_DB_driver {
 	{

 		return end($this->queries);

 	}

-	

-	// --------------------------------------------------------------------

-

-	/**

-	 * Protect Identifiers

-	 *

-	 * This function adds backticks if appropriate based on db type

-	 *

-	 * @access	private

-	 * @param	mixed	the item to escape

-	 * @param	boolean	only affect the first word

-	 * @return	mixed	the item with backticks

-	 */

-	function protect_identifiers($item, $first_word_only = FALSE)

-	{

-		return $this->_protect_identifiers($item, $first_word_only);

-	}

 

 	// --------------------------------------------------------------------

 

@@ -791,8 +766,8 @@ class CI_DB_driver {
 	 * @return	boolean

 	 */

 	function table_exists($table_name)

-	{

-		return ( ! in_array($this->prep_tablename($table_name), $this->list_tables())) ? FALSE : TRUE;

+	{	

+		return ( ! in_array($this->_protect_identifiers($table_name, TRUE, NULL, FALSE), $this->list_tables())) ? FALSE : TRUE;

 	}

 	

 	// --------------------------------------------------------------------

@@ -821,7 +796,7 @@ class CI_DB_driver {
 			return FALSE;

 		}

 		

-		if (FALSE === ($sql = $this->_list_columns($this->prep_tablename($table))))

+		if (FALSE === ($sql = $this->_list_columns($this->_protect_identifiers($table, TRUE, NULL, FALSE))))

 		{

 			if ($this->db_debug)

 			{

@@ -866,16 +841,6 @@ class CI_DB_driver {
 	// --------------------------------------------------------------------

 

 	/**

-	 * DEPRECATED - use list_fields()

-	 */

-	function field_names($table = '')

-	{

-		return $this->list_fields($table);

-	}

-	

-	// --------------------------------------------------------------------

-

-	/**

 	 * Returns an object with field data

 	 *

 	 * @access	public

@@ -893,7 +858,8 @@ class CI_DB_driver {
 			return FALSE;

 		}

 		

-		$query = $this->query($this->_field_data($this->prep_tablename($table)));

+		$query = $this->query($this->_field_data($this->_protect_identifiers($table, TRUE, NULL, FALSE)));

+

 		return $query->field_data();

 	}	

 

@@ -914,11 +880,11 @@ class CI_DB_driver {
 		

 		foreach($data as $key => $val)

 		{

-			$fields[] = $this->_escape_column($key);

+			$fields[] = $this->_escape_identifiers($key);

 			$values[] = $this->escape($val);

 		}

 				

-		return $this->_insert($this->prep_tablename($table), $fields, $values);

+		return $this->_insert($this->_protect_identifiers($table, TRUE, NULL, FALSE), $fields, $values);

 	}	

 	

 	// --------------------------------------------------------------------

@@ -942,7 +908,7 @@ class CI_DB_driver {
 		$fields = array();

 		foreach($data as $key => $val)

 		{

-			$fields[$this->_escape_column($key)] = $this->escape($val);

+			$fields[$this->_protect_identifiers($key)] = $this->escape($val);

 		}

 

 		if ( ! is_array($where))

@@ -970,7 +936,7 @@ class CI_DB_driver {
 			}

 		}		

 

-		return $this->_update($this->prep_tablename($table), $fields, $dest);

+		return $this->_update($this->_protect_identifiers($table, TRUE, NULL, FALSE), $fields, $dest);

 	}	

 

 	// --------------------------------------------------------------------

@@ -992,29 +958,6 @@ class CI_DB_driver {
 

 		return TRUE;

 	}

-	

-	// --------------------------------------------------------------------

-

-	/**

-	 * Prep the table name - simply adds the table prefix if needed

-	 *

-	 * @access	public

-	 * @param	string	the table name

-	 * @return	string		

-	 */	

-	function prep_tablename($table = '')

-	{

-		// Do we need to add the table prefix?

-		if ($this->dbprefix != '')

-		{

-			if (substr($table, 0, strlen($this->dbprefix)) != $this->dbprefix)

-			{

-				$table = $this->dbprefix.$table;

-			}

-		}

-

-		return $table;

-	}

 

 	// --------------------------------------------------------------------

 

@@ -1201,7 +1144,174 @@ class CI_DB_driver {
 		echo $error->show_error($heading, $message, 'error_db');

 		exit;

 	}

+

+	// --------------------------------------------------------------------

+

+	/**

+	 * Protect Identifiers

+	 *

+	 * This function adds backticks if appropriate based on db type

+	 *

+	 * @access	private

+	 * @param	mixed	the item to escape

+	 * @return	mixed	the item with backticks

+	 */

+	function protect_identifiers($item, $prefix_single = FALSE)

+	{

+		return $this->_protect_identifiers($item, $prefix_single);

+	}

+

+	// --------------------------------------------------------------------

+

+	/**

+	 * Protect Identifiers

+	 *

+	 * This function is used extensively by the Active Record class, and by

+	 * a couple functions in this class. 

+	 * It takes a column or table name (optionally with an alias) and inserts

+	 * the table prefix onto it.  Some logic is necessary in order to deal with

+	 * column names that include the path.  Consider a query like this:

+	 *

+	 * SELECT * FROM hostname.database.table.column AS c FROM hostname.database.table

+	 *

+	 * Or a query with aliasing:

+	 *

+	 * SELECT m.member_id, m.member_name FROM members AS m

+	 *

+	 * Since the column name can include up to four segments (host, DB, table, column)

+	 * or also have an alias prefix, we need to do a bit of work to figure this out and

+	 * insert the table prefix (if it exists) in the proper position, and escape only

+	 * the correct identifiers.

+	 *

+	 * @access	private

+	 * @param	string

+	 * @param	bool

+	 * @param	mixed

+	 * @param	bool

+	 * @return	string

+	 */	

+	function _protect_identifiers($item, $prefix_single = FALSE, $protect_identifiers = NULL, $field_exists = TRUE)

+	{

+		if ( ! is_bool($protect_identifiers))

+		{

+			$protect_identifiers = $this->_protect_identifiers;

+		}

+		

+		// Convert tabs or multiple spaces into single spaces

+		$item = preg_replace('/[\t| ]+/', ' ', $item);

 	

+		// If the item has an alias declaration we remove it and set it aside.

+		// Basically we remove everything to the right of the first space

+		$alias = '';

+		if (strpos($item, ' ') !== FALSE)

+		{		

+			$alias = strstr($item, " ");

+			$item = substr($item, 0, - strlen($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 then escape the item

+			if (in_array($parts[0], $this->ar_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++;

+				}

+				

+				// We only add the table prefix if it does not already exist

+				if (substr($parts[$i], 0, strlen($this->dbprefix)) != $this->dbprefix)

+				{

+					$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;

+		}

+

+		// 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.$alias;

+		}

+		

+		// Is there a table prefix?  If not, no need to insert it

+		if ($this->dbprefix != '')

+		{

+			// Do we prefix an item with no segments?

+			if ($prefix_single == TRUE AND substr($item, 0, strlen($this->dbprefix)) != $this->dbprefix)

+			{

+				$item = $this->dbprefix.$item;

+			}		

+		}

+		

+		if ($protect_identifiers === TRUE AND ! in_array($item, $this->_reserved_identifiers))

+		{

+			$item = $this->_escape_identifiers($item);

+		}

+		

+		return $item.$alias;

+	}

+

+

 }