From 59523592c58abc303a6eae1904c80fa6a428c3d3 Mon Sep 17 00:00:00 2001 From: Rick Ellis Date: Fri, 17 Oct 2008 04:07:40 +0000 Subject: Fixed a number of bug reports related to table/db names not being escaped or prefixed correctly. --- system/database/DB_driver.php | 334 ++++++++++++++++++++++++++++-------------- 1 file changed, 222 insertions(+), 112 deletions(-) (limited to 'system/database/DB_driver.php') 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; } } @@ -193,6 +158,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...) * @@ -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) { @@ -865,16 +840,6 @@ class CI_DB_driver { // -------------------------------------------------------------------- - /** - * DEPRECATED - use list_fields() - */ - function field_names($table = '') - { - return $this->list_fields($table); - } - - // -------------------------------------------------------------------- - /** * Returns an object with field data * @@ -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; + } + + } -- cgit v1.2.3-24-g4f1b