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_active_rec.php | 569 ++++++++++++----------- system/database/DB_driver.php | 334 ++++++++----- system/database/DB_forge.php | 9 +- system/database/DB_result.php | 1 - system/database/drivers/mssql/mssql_driver.php | 112 ++--- system/database/drivers/mssql/mssql_forge.php | 4 +- system/database/drivers/mssql/mssql_result.php | 6 - system/database/drivers/oci8/oci8_driver.php | 109 +---- system/database/drivers/sqlite/sqlite_driver.php | 104 +---- system/database/drivers/sqlite/sqlite_forge.php | 2 +- system/database/drivers/sqlite/sqlite_result.php | 6 - system/language/english/db_lang.php | 1 + 12 files changed, 602 insertions(+), 655 deletions(-) diff --git a/system/database/DB_active_rec.php b/system/database/DB_active_rec.php index e7920d083..f4c13cc42 100644 --- a/system/database/DB_active_rec.php +++ b/system/database/DB_active_rec.php @@ -28,57 +28,38 @@ */ class CI_DB_active_record extends CI_DB_driver { - var $ar_select = array(); - var $ar_distinct = FALSE; - var $ar_from = array(); - var $ar_join = array(); - var $ar_where = array(); - var $ar_like = array(); - var $ar_groupby = array(); - var $ar_having = array(); - var $ar_limit = FALSE; - var $ar_offset = FALSE; - var $ar_order = FALSE; - var $ar_orderby = array(); - var $ar_set = array(); - var $ar_wherein = array(); + var $ar_select = array(); + var $ar_distinct = FALSE; + var $ar_from = array(); + var $ar_join = array(); + var $ar_where = array(); + var $ar_like = array(); + var $ar_groupby = array(); + var $ar_having = array(); + var $ar_limit = FALSE; + var $ar_offset = FALSE; + var $ar_order = FALSE; + var $ar_orderby = array(); + var $ar_set = array(); + var $ar_wherein = array(); var $ar_aliased_tables = array(); - var $ar_store_array = array(); - + var $ar_store_array = array(); + // Active Record Caching variables - var $ar_caching = FALSE; - var $ar_cache_select = array(); - var $ar_cache_from = array(); - var $ar_cache_join = array(); - var $ar_cache_where = array(); - var $ar_cache_like = array(); - var $ar_cache_groupby = array(); - var $ar_cache_having = array(); - var $ar_cache_limit = FALSE; - var $ar_cache_offset = FALSE; - var $ar_cache_order = FALSE; - var $ar_cache_orderby = array(); - var $ar_cache_set = array(); - - - /** - * DB Prefix - * - * Prepends a database prefix if one exists in configuration - * - * @access public - * @param string the table - * @return string - */ - function dbprefix($table = '') - { - if ($table == '') - { - $this->display_error('db_table_name_required'); - } + var $ar_caching = FALSE; + var $ar_cache_select = array(); + var $ar_cache_from = array(); + var $ar_cache_join = array(); + var $ar_cache_where = array(); + var $ar_cache_like = array(); + var $ar_cache_groupby = array(); + var $ar_cache_having = array(); + var $ar_cache_limit = FALSE; + var $ar_cache_offset = FALSE; + var $ar_cache_order = FALSE; + var $ar_cache_orderby = array(); + var $ar_cache_set = array(); - return $this->dbprefix.$table; - } // -------------------------------------------------------------------- @@ -91,39 +72,27 @@ class CI_DB_active_record extends CI_DB_driver { * @param string * @return object */ - function select($select = '*', $protect_identifiers = TRUE) + function select($select = '*', $escape = NULL) { + // Set the global value if this was sepecified + if (is_bool($escape)) + { + $this->_protect_identifiers = $escape; + } + if (is_string($select)) { - if ($protect_identifiers !== FALSE) - { - $select = explode(',', $select); - } - else - { - $select = array($select); - } + $select = explode(',', $select); } foreach ($select as $val) { $val = trim($val); - if ($val != '*' && $protect_identifiers !== FALSE) - { - if (strpos($val, '.') !== FALSE) - { - $val = $this->dbprefix.$val; - } - else - { - $val = $this->_protect_identifiers($val); - } - } - if ($val != '') { $this->ar_select[] = $val; + if ($this->ar_caching === TRUE) { $this->ar_cache_select[] = $val; @@ -145,26 +114,11 @@ class CI_DB_active_record extends CI_DB_driver { * @param string an alias * @return object */ - function select_max($select = '', $alias='') + function select_max($select = '', $alias = '') { - if ( ! is_string($select) OR $select == '') - { - $this->display_error('db_invalid_query'); - } - - $alias = ($alias != '') ? $alias : $select; - - $sql = 'MAX('.$this->_protect_identifiers(trim($select)).') AS '.$this->_protect_identifiers(trim($alias)); - - $this->ar_select[] = $sql; - if ($this->ar_caching === TRUE) - { - $this->ar_cache_select[] = $sql; - } - - return $this; + return $this->_max_min_avg_sum($select, $alias, 'MAX'); } - + // -------------------------------------------------------------------- /** @@ -177,24 +131,9 @@ class CI_DB_active_record extends CI_DB_driver { * @param string an alias * @return object */ - function select_min($select = '', $alias='') + function select_min($select = '', $alias = '') { - if ( ! is_string($select) OR $select == '') - { - $this->display_error('db_invalid_query'); - } - - $alias = ($alias != '') ? $alias : $select; - - $sql = 'MIN('.$this->_protect_identifiers(trim($select)).') AS '.$this->_protect_identifiers(trim($alias)); - - $this->ar_select[] = $sql; - if ($this->ar_caching === TRUE) - { - $this->ar_cache_select[] = $sql; - } - - return $this; + return $this->_max_min_avg_sum($select, $alias, 'MIN'); } // -------------------------------------------------------------------- @@ -209,24 +148,9 @@ class CI_DB_active_record extends CI_DB_driver { * @param string an alias * @return object */ - function select_avg($select = '', $alias='') + function select_avg($select = '', $alias = '') { - if ( ! is_string($select) OR $select == '') - { - $this->display_error('db_invalid_query'); - } - - $alias = ($alias != '') ? $alias : $select; - - $sql = 'AVG('.$this->_protect_identifiers(trim($select)).') AS '.$this->_protect_identifiers(trim($alias)); - - $this->ar_select[] = $sql; - if ($this->ar_caching === TRUE) - { - $this->ar_cache_select[] = $sql; - } - - return $this; + return $this->_max_min_avg_sum($select, $alias, 'AVG'); } // -------------------------------------------------------------------- @@ -241,28 +165,78 @@ class CI_DB_active_record extends CI_DB_driver { * @param string an alias * @return object */ - function select_sum($select = '', $alias='') + function select_sum($select = '', $alias = '') + { + return $this->_max_min_avg_sum($select, $alias, 'SUM'); + } + + // -------------------------------------------------------------------- + + /** + * Processing Function for the four functions above: + * + * select_max() + * select_min() + * select_avg() + * select_sum() + * + * @access public + * @param string the field + * @param string an alias + * @return object + */ + function _max_min_avg_sum($select = '', $alias = '', $type = 'MAX') { if ( ! is_string($select) OR $select == '') { $this->display_error('db_invalid_query'); } - - $alias = ($alias != '') ? $alias : $select; - $sql = 'SUM('.$this->_protect_identifiers(trim($select)).') AS '.$this->_protect_identifiers(trim($alias)); + $type = strtoupper($type); + + if ( ! in_array($type, array('MAX', 'MIN', 'AVG', 'SUM'))) + { + show_error('Invalid function type: '.$type); + } + + if ($alias == '') + { + $alias = $this->_create_alias_from_table(trim($select)); + } + + $sql = $type.'('.$this->_protect_identifiers(trim($select)).') AS '.$alias; $this->ar_select[] = $sql; + if ($this->ar_caching === TRUE) { $this->ar_cache_select[] = $sql; } - + return $this; } // -------------------------------------------------------------------- + /** + * Determines the alias name based on the table + * + * @access private + * @param string + * @return string + */ + function _create_alias_from_table($item) + { + if (strpos($item, '.') !== FALSE) + { + return end(explode('.', $item)); + } + + return $item; + } + + // -------------------------------------------------------------------- + /** * DISTINCT * @@ -293,10 +267,15 @@ class CI_DB_active_record extends CI_DB_driver { { foreach ((array)$from as $val) { - $this->ar_from[] = $this->_protect_identifiers($this->_track_aliases($val)); + // Extract any aliases that might exist. We use this information + // in the _protect_identifiers to know whether to add a table prefix + $this->_track_aliases($val); + + $this->ar_from[] = $this->_protect_identifiers($val, TRUE, NULL, FALSE); + if ($this->ar_caching === TRUE) { - $this->ar_cache_from[] = $this->_protect_identifiers($this->_track_aliases($val)); + $this->ar_cache_from[] = $this->_protect_identifiers($val, TRUE, NULL, FALSE); } } @@ -322,7 +301,7 @@ class CI_DB_active_record extends CI_DB_driver { { $type = strtoupper(trim($type)); - if ( ! in_array($type, array('LEFT', 'RIGHT', 'OUTER', 'INNER', 'LEFT OUTER', 'RIGHT OUTER'), TRUE)) + if ( ! in_array($type, array('LEFT', 'RIGHT', 'OUTER', 'INNER', 'LEFT OUTER', 'RIGHT OUTER'))) { $type = ''; } @@ -332,19 +311,21 @@ class CI_DB_active_record extends CI_DB_driver { } } - // If a DB prefix is used we might need to add it to the column names - if ($this->dbprefix) - { - $this->_track_aliases($table); - - // First we remove any existing prefixes in the condition to avoid duplicates - $cond = preg_replace('|('.$this->dbprefix.')([\w\.]+)([\W\s]+)|', "$2$3", $cond); + // Extract any aliases that might exist. We use this information + // in the _protect_identifiers to know whether to add a table prefix + $this->_track_aliases($table); - // Next we add the prefixes to the condition - $cond = preg_replace('|([\w\.]+)([\W\s]+)(.+)|', $this->dbprefix . "$1$2" . $this->dbprefix . "$3", $cond); + // Strip apart the condition and protect the identifiers + if (preg_match('/([\w\.]+)([\W\s]+)(.+)/', $cond, $match)) + { + $match[1] = $this->_protect_identifiers($match[1]); + $match[3] = $this->_protect_identifiers($match[3]); + + $cond = $match[1].$match[2].$match[3]; } - - $join = $type.'JOIN '.$this->_protect_identifiers($this->dbprefix.$table, TRUE).' ON '.$cond; + + // Assemble the JOIN statement + $join = $type.'JOIN '.$this->_protect_identifiers($table, TRUE, NULL, FALSE).' ON '.$cond; $this->ar_join[] = $join; if ($this->ar_caching === TRUE) @@ -416,12 +397,18 @@ class CI_DB_active_record extends CI_DB_driver { * @param string * @return object */ - function _where($key, $value = NULL, $type = 'AND ', $escape = TRUE) + function _where($key, $value = NULL, $type = 'AND ', $escape = NULL) { if ( ! is_array($key)) { $key = array($key => $value); } + + // If the escape value was not set will will base it on the global setting + if ( ! is_bool($escape)) + { + $escape = $this->_protect_identifiers; + } foreach ($key as $k => $v) { @@ -437,15 +424,7 @@ class CI_DB_active_record extends CI_DB_driver { { if ($escape === TRUE) { - // exception for "field<=" keys - if ($this->_has_operator($k)) - { - $k = preg_replace("/([A-Za-z_0-9]+)/", $this->_escape_column($this->_protect_identifiers('$1')), $k); - } - else - { - $k = $this->_protect_identifiers($k); - } + $k = $this->_protect_identifiers($k, FALSE, $escape); $v = ' '.$this->escape($v); } @@ -457,10 +436,7 @@ class CI_DB_active_record extends CI_DB_driver { } else { - if ($escape === TRUE) - { - $k = $this->_protect_identifiers($k, TRUE); - } + $k = $this->_protect_identifiers($k, FALSE, $escape); } $this->ar_where[] = $prefix.$k.$v; @@ -489,7 +465,7 @@ class CI_DB_active_record extends CI_DB_driver { * @return object */ function where_in($key = NULL, $values = NULL) - { + { return $this->_where_in($key, $values); } @@ -557,18 +533,23 @@ class CI_DB_active_record extends CI_DB_driver { * @access public * @param string The field to search * @param array The values searched on - * @param boolean If the statement whould be IN or NOT IN + * @param boolean If the statement would be IN or NOT IN * @param string * @return object */ function _where_in($key = NULL, $values = NULL, $not = FALSE, $type = 'AND ') { - if ($key === NULL OR ! is_array($values)) + if ($key === NULL OR $values === NULL) { return; } - - $not = ($not) ? ' NOT ' : ''; + + if ( ! is_array($values)) + { + $values = array($values); + } + + $not = ($not) ? ' NOT' : ''; foreach ($values as $value) { @@ -623,7 +604,7 @@ class CI_DB_active_record extends CI_DB_driver { */ function not_like($field, $match = '', $side = 'both') { - return $this->_like($field, $match, 'AND ', $side, ' NOT'); + return $this->_like($field, $match, 'AND ', $side, 'NOT'); } // -------------------------------------------------------------------- @@ -659,7 +640,7 @@ class CI_DB_active_record extends CI_DB_driver { */ function or_not_like($field, $match = '', $side = 'both') { - return $this->_like($field, $match, 'OR ', $side, 'NOT '); + return $this->_like($field, $match, 'OR ', $side, 'NOT'); } // -------------------------------------------------------------------- @@ -695,13 +676,12 @@ class CI_DB_active_record extends CI_DB_driver { } foreach ($field as $k => $v) - { - + { $k = $this->_protect_identifiers($k); $prefix = (count($this->ar_like) == 0) ? '' : $type; - $v = $this->escape($v); + $v = $this->escape_str($v); if ($side == 'before') { @@ -749,6 +729,7 @@ class CI_DB_active_record extends CI_DB_driver { if ($val != '') { $this->ar_groupby[] = $this->_protect_identifiers($val); + if ($this->ar_caching === TRUE) { $this->ar_cache_groupby[] = $this->_protect_identifiers($val); @@ -851,7 +832,7 @@ class CI_DB_active_record extends CI_DB_driver { if ($v != '') { - $v = ' '.$this->escape($v); + $v = ' '.$this->escape_str($v); } $this->ar_having[] = $prefix.$k.$v; @@ -886,7 +867,7 @@ class CI_DB_active_record extends CI_DB_driver { $direction = (in_array(strtoupper(trim($direction)), array('ASC', 'DESC'), TRUE)) ? ' '.$direction : ' ASC'; } - $orderby_statement = $this->_protect_identifiers($orderby, TRUE).$direction; + $orderby_statement = $this->_protect_identifiers($orderby).$direction; $this->ar_orderby[] = $orderby_statement; if ($this->ar_caching === TRUE) @@ -984,6 +965,7 @@ class CI_DB_active_record extends CI_DB_driver { if ($escape === FALSE) { $this->ar_set[$this->_protect_identifiers($k)] = $v; + if ($this->ar_caching === TRUE) { $this->ar_cache_offset[$this->_protect_identifiers($k)] = $v; @@ -992,6 +974,7 @@ class CI_DB_active_record extends CI_DB_driver { else { $this->ar_set[$this->_protect_identifiers($k)] = $this->escape($v); + if ($this->ar_caching === TRUE) { $this->ar_cache_offset[$this->_protect_identifiers($k)] = $this->escape($v); @@ -1085,7 +1068,6 @@ class CI_DB_active_record extends CI_DB_driver { { if ($table != '') { - $this->_track_aliases($table); $this->from($table); } @@ -1160,7 +1142,7 @@ class CI_DB_active_record extends CI_DB_driver { $table = $this->ar_from[0]; } - $sql = $this->_insert($this->_protect_identifiers($this->dbprefix.$table), array_keys($this->ar_set), array_values($this->ar_set)); + $sql = $this->_insert($this->_protect_identifiers($table, TRUE, NULL, FALSE), array_keys($this->ar_set), array_values($this->ar_set)); $this->_reset_write(); return $this->query($sql); @@ -1219,7 +1201,7 @@ class CI_DB_active_record extends CI_DB_driver { $this->limit($limit); } - $sql = $this->_update($this->_protect_identifiers($this->dbprefix.$table), $this->ar_set, $this->ar_where, $this->ar_orderby, $this->ar_limit); + $sql = $this->_update($this->_protect_identifiers($table, TRUE, NULL, FALSE), $this->ar_set, $this->ar_where, $this->ar_orderby, $this->ar_limit); $this->_reset_write(); return $this->query($sql); @@ -1253,10 +1235,9 @@ class CI_DB_active_record extends CI_DB_driver { } else { - $table = $this->_protect_identifiers($this->dbprefix.$table); + $table = $this->_protect_identifiers($table, TRUE, NULL, FALSE); } - $sql = $this->_delete($table); $this->_reset_write(); @@ -1294,10 +1275,9 @@ class CI_DB_active_record extends CI_DB_driver { } else { - $table = $this->_protect_identifiers($this->dbprefix.$table); + $table = $this->_protect_identifiers($table, TRUE, NULL, FALSE); } - $sql = $this->_truncate($table); $this->_reset_write(); @@ -1346,7 +1326,7 @@ class CI_DB_active_record extends CI_DB_driver { } else { - $table = $this->_protect_identifiers($this->dbprefix.$table); + $table = $this->_protect_identifiers($table, TRUE, NULL, FALSE); } if ($where != '') @@ -1382,13 +1362,22 @@ class CI_DB_active_record extends CI_DB_driver { // -------------------------------------------------------------------- /** - * Use Table - DEPRECATED + * DB Prefix * - * @deprecated use $this->db->from instead + * Prepends a database prefix if one exists in configuration + * + * @access public + * @param string the table + * @return string */ - function use_table($table) + function dbprefix($table = '') { - return $this->from($table); + if ($table == '') + { + $this->display_error('db_table_name_required'); + } + + return $this->dbprefix.$table; } // -------------------------------------------------------------------- @@ -1404,41 +1393,37 @@ class CI_DB_active_record extends CI_DB_driver { */ function _track_aliases($table) { - // if a table alias is used we can recognize it by a space - if (strpos($table, " ") !== FALSE) + if (is_array($table)) { - // if the alias is written with the AS keyowrd, get it out - $table = preg_replace('/ AS /i', ' ', $table); - - $this->ar_aliased_tables[] = trim(strrchr($table, " ")); + foreach ($table as $t) + { + $this->_track_aliases($t); + } + return; } - - return $this->dbprefix.$table; - } - - // -------------------------------------------------------------------- - - /** - * Filter Table Aliases - * - * Intelligently removes database prefixes from aliased tables - * - * @access private - * @param array An array of compiled SQL - * @return array Cleaned up statement with aliases accounted for - */ - function _filter_table_aliases($statements) - { - - foreach ($statements as $k => $v) + + // Does the string contain a comma? If so, we need to separate + // the string into discreet statements + if (strpos($table, ',') !== FALSE) + { + return $this->_track_aliases(explode(',', $table)); + } + + // if a table alias is used we can recognize it by a space + if (strpos($table, " ") !== FALSE) { - foreach ($this->ar_aliased_tables as $table) + // if the alias is written with the AS keyword, remove it + $table = preg_replace('/ AS /i', ' ', $table); + + // Grab the alias + $table = trim(strrchr($table, " ")); + + // Store the alias, if it doesn't already exist + if ( ! in_array($table, $this->ar_aliased_tables)) { - $statements[$k] = preg_replace('/(\w+\.\w+)/', $this->_protect_identifiers('$0'), $statements[$k]); // makes `table.field` - $statements[$k] = str_replace($this->dbprefix.$table.'.', $table.'.', $statements[$k]); + $this->ar_aliased_tables[] = $table; } } - return $statements; } // -------------------------------------------------------------------- @@ -1456,69 +1441,99 @@ class CI_DB_active_record extends CI_DB_driver { { $this->_merge_cache(); - $sql = ( ! $this->ar_distinct) ? 'SELECT ' : 'SELECT DISTINCT '; - - $sql .= (count($this->ar_select) == 0) ? '*' : implode(', ', $this->_filter_table_aliases($this->ar_select)); + // ---------------------------------------------------------------- + + // Write the "select" portion of the query if ($select_override !== FALSE) { $sql = $select_override; } + else + { + $sql = ( ! $this->ar_distinct) ? 'SELECT ' : 'SELECT DISTINCT '; + + if (count($this->ar_select) == 0) + { + $sql .= '*'; + } + else + { + // Cycle through the "select" portion of the query and prep each column name. + // The reason we protect identifiers here rather then in the select() function + // is because until the user calls the from() function we don't know if there are aliases + foreach ($this->ar_select as $key => $val) + { + $this->ar_select[$key] = $this->_protect_identifiers($val); + } + + $sql .= implode(', ', $this->ar_select); + } + } + + // ---------------------------------------------------------------- + + // Write the "FROM" portion of the query if (count($this->ar_from) > 0) { $sql .= "\nFROM "; + $sql .= $this->_from_tables($this->ar_from); } + // ---------------------------------------------------------------- + + // Write the "JOIN" portion of the query + if (count($this->ar_join) > 0) { $sql .= "\n"; - // special consideration for table aliases - if (count($this->ar_aliased_tables) > 0 && $this->dbprefix) - { - $sql .= implode("\n", $this->_filter_table_aliases($this->ar_join)); - } - else - { - $sql .= implode("\n", $this->ar_join); - } - + $sql .= implode("\n", $this->ar_join); } + // ---------------------------------------------------------------- + + // Write the "WHERE" portion of the query + if (count($this->ar_where) > 0 OR count($this->ar_like) > 0) { - $sql .= "\nWHERE "; + $sql .= "\n"; + + $sql .= "WHERE "; } $sql .= implode("\n", $this->ar_where); + + // ---------------------------------------------------------------- + // Write the "LIKE" portion of the query + if (count($this->ar_like) > 0) { if (count($this->ar_where) > 0) { - $sql .= " AND "; + $sql .= "\nAND "; } $sql .= implode("\n", $this->ar_like); } + + // ---------------------------------------------------------------- + // Write the "GROUP BY" portion of the query + if (count($this->ar_groupby) > 0) { - $sql .= "\nGROUP BY "; - // special consideration for table aliases - if (count($this->ar_aliased_tables) > 0 && $this->dbprefix) - { - $sql .= implode(", ", $this->_filter_table_aliases($this->ar_groupby)); - } - else - { - $sql .= implode(', ', $this->ar_groupby); - } + $sql .= implode(', ', $this->ar_groupby); } + + // ---------------------------------------------------------------- + + // Write the "HAVING" portion of the query if (count($this->ar_having) > 0) { @@ -1526,6 +1541,10 @@ class CI_DB_active_record extends CI_DB_driver { $sql .= implode("\n", $this->ar_having); } + // ---------------------------------------------------------------- + + // Write the "ORDER BY" portion of the query + if (count($this->ar_orderby) > 0) { $sql .= "\nORDER BY "; @@ -1536,6 +1555,10 @@ class CI_DB_active_record extends CI_DB_driver { $sql .= ($this->ar_order == 'desc') ? ' DESC' : ' ASC'; } } + + // ---------------------------------------------------------------- + + // Write the "LIMIT" portion of the query if (is_numeric($this->ar_limit)) { @@ -1569,7 +1592,6 @@ class CI_DB_active_record extends CI_DB_driver { { // There are some built in keys we need to ignore for this conversion if ( ! is_object($val) && ! is_array($val) && $key != '_parent_name' && $key != '_ci_scaffolding' && $key != '_ci_scaff_table') - { $array[$key] = $val; } @@ -1608,7 +1630,6 @@ class CI_DB_active_record extends CI_DB_driver { $this->ar_caching = FALSE; } - // -------------------------------------------------------------------- /** @@ -1622,16 +1643,16 @@ class CI_DB_active_record extends CI_DB_driver { function flush_cache() { $ar_reset_items = array( - 'ar_cache_select' => array(), - 'ar_cache_from' => array(), - 'ar_cache_join' => array(), - 'ar_cache_where' => array(), - 'ar_cache_like' => array(), - 'ar_cache_groupby' => array(), - 'ar_cache_having' =>array(), - 'ar_cache_orderby' => array(), - 'ar_cache_set' => array() - ); + 'ar_cache_select' => array(), + 'ar_cache_from' => array(), + 'ar_cache_join' => array(), + 'ar_cache_where' => array(), + 'ar_cache_like' => array(), + 'ar_cache_groupby' => array(), + 'ar_cache_having' =>array(), + 'ar_cache_orderby' => array(), + 'ar_cache_set' => array() + ); $this->_reset_run($ar_reset_items); } @@ -1649,6 +1670,11 @@ class CI_DB_active_record extends CI_DB_driver { */ function _merge_cache() { + if ($this->ar_caching == FALSE) + { + return; + } + $ar_items = array('select', 'from', 'join', 'where', 'like', 'groupby', 'having', 'orderby', 'set'); foreach ($ar_items as $ar_item) @@ -1657,6 +1683,13 @@ class CI_DB_active_record extends CI_DB_driver { $ar_item = 'ar_'.$ar_item; $this->$ar_item = array_unique(array_merge($this->$ar_item, $this->$ar_cache_item)); } + + // If we are "protecting identifiers" we need to examine the "from" + // portion of the query to determine if there are any aliases + if ($this->_protect_identifiers === TRUE) + { + $this->_track_aliases($this->ar_from); + } } // -------------------------------------------------------------------- @@ -1678,7 +1711,7 @@ class CI_DB_active_record extends CI_DB_driver { } } } - + // -------------------------------------------------------------------- /** @@ -1690,21 +1723,21 @@ class CI_DB_active_record extends CI_DB_driver { function _reset_select() { $ar_reset_items = array( - 'ar_select' => array(), - 'ar_from' => array(), - 'ar_join' => array(), - 'ar_where' => array(), - 'ar_like' => array(), - 'ar_groupby' => array(), - 'ar_having' => array(), - 'ar_orderby' => array(), - 'ar_wherein' => array(), - 'ar_aliased_tables' => array(), - 'ar_distinct' => FALSE, - 'ar_limit' => FALSE, - 'ar_offset' => FALSE, - 'ar_order' => FALSE, - ); + 'ar_select' => array(), + 'ar_from' => array(), + 'ar_join' => array(), + 'ar_where' => array(), + 'ar_like' => array(), + 'ar_groupby' => array(), + 'ar_having' => array(), + 'ar_orderby' => array(), + 'ar_wherein' => array(), + 'ar_aliased_tables' => array(), + 'ar_distinct' => FALSE, + 'ar_limit' => FALSE, + 'ar_offset' => FALSE, + 'ar_order' => FALSE, + ); $this->_reset_run($ar_reset_items); } @@ -1722,14 +1755,14 @@ class CI_DB_active_record extends CI_DB_driver { function _reset_write() { $ar_reset_items = array( - 'ar_set' => array(), - 'ar_from' => array(), - 'ar_where' => array(), - 'ar_like' => array(), - 'ar_orderby' => array(), - 'ar_limit' => FALSE, - 'ar_order' => FALSE - ); + 'ar_set' => array(), + 'ar_from' => array(), + 'ar_where' => array(), + 'ar_like' => array(), + 'ar_orderby' => array(), + 'ar_limit' => FALSE, + 'ar_order' => FALSE + ); $this->_reset_run($ar_reset_items); } 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; + } + + } diff --git a/system/database/DB_forge.php b/system/database/DB_forge.php index 64f95d635..20f0a3087 100644 --- a/system/database/DB_forge.php +++ b/system/database/DB_forge.php @@ -253,7 +253,7 @@ class CI_DB_forge { { if ($table == '') { - show_error('A table name is required for that operation.'); + show_error('A table name is required for that operation.'); } // add field info into field array, but we can only do one at a time @@ -286,12 +286,12 @@ class CI_DB_forge { if ($table == '') { - show_error('A table name is required for that operation.'); + show_error('A table name is required for that operation.'); } if ($column_name == '') { - show_error('A column name is required for that operation.'); + show_error('A column name is required for that operation.'); } $sql = $this->_alter_table('DROP', $this->db->dbprefix.$table, $column_name); @@ -312,10 +312,9 @@ class CI_DB_forge { */ function modify_column($table = '', $field = array()) { - if ($table == '') { - show_error('A table name is required for that operation.'); + show_error('A table name is required for that operation.'); } // add field info into field array, but we can only do one at a time diff --git a/system/database/DB_result.php b/system/database/DB_result.php index 0ec59d568..8f55f6718 100644 --- a/system/database/DB_result.php +++ b/system/database/DB_result.php @@ -329,7 +329,6 @@ class CI_DB_result { function num_rows() { return $this->num_rows; } function num_fields() { return 0; } function list_fields() { return array(); } - function field_names() { return array(); } // Deprecated function field_data() { return array(); } function free_result() { return TRUE; } function _data_seek() { return TRUE; } diff --git a/system/database/drivers/mssql/mssql_driver.php b/system/database/drivers/mssql/mssql_driver.php index 02b975ad2..fbc0701c0 100644 --- a/system/database/drivers/mssql/mssql_driver.php +++ b/system/database/drivers/mssql/mssql_driver.php @@ -30,6 +30,10 @@ */ class CI_DB_mssql_driver extends CI_DB { + var $dbdriver = 'mssql'; + + // The character used for escaping + var $_escape_char = ''; /** * The syntax to count rows is slightly different across different * database engines, so this string appears in each driver and is @@ -37,7 +41,6 @@ class CI_DB_mssql_driver extends CI_DB { */ var $_count_string = "SELECT COUNT(*) AS "; var $_random_keyword = ' ASC'; // not currently supported - var $dbdriver = 'mssql'; /** * Non-persistent database connection @@ -301,7 +304,7 @@ class CI_DB_mssql_driver extends CI_DB { if ($table == '') return '0'; - $query = $this->query($this->_count_string . $this->_protect_identifiers('numrows'). " FROM " . $this->_protect_identifiers($this->dbprefix.$table)); + $query = $this->query($this->_count_string . $this->_protect_identifiers('numrows'). " FROM " . $this->_protect_identifiers($table, TRUE, NULL, FALSE)); if ($query->num_rows() == 0) return '0'; @@ -348,7 +351,7 @@ class CI_DB_mssql_driver extends CI_DB { */ function _list_columns($table = '') { - return "SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = '".$this->_escape_table($table)."'"; + return "SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = '".$table."'"; } // -------------------------------------------------------------------- @@ -364,7 +367,7 @@ class CI_DB_mssql_driver extends CI_DB { */ function _field_data($table) { - return "SELECT TOP 1 * FROM ".$this->_escape_table($table); + return "SELECT TOP 1 * FROM ".$table; } // -------------------------------------------------------------------- @@ -398,99 +401,34 @@ class CI_DB_mssql_driver extends CI_DB { // -------------------------------------------------------------------- /** - * Escape Column Name - * - * This function adds backticks around supplied column name - * - * @access private - * @param string the column name - * @return string - */ - function _escape_column($column) - { - // Not necessary with MS SQL so we simply return the value - return $column; - } - - // -------------------------------------------------------------------- - - /** - * Escape Table Name + * Escape the SQL Identifiers * - * This function adds backticks if the table name has a period - * in it. Some DBs will get cranky unless periods are escaped + * This function escapes column and table names * * @access private - * @param string the table name + * @param string * @return string */ - function _escape_table($table) - { - // Not necessary with MS SQL so we simply return the value - return $table; - } - - // -------------------------------------------------------------------- - - /** - * Protect Identifiers - * - * This function adds backticks if appropriate based on db type - * - * @access private - * @param mixed the item(s) - * @param boolean should spaces be backticked - * @param boolean only affect the first word - * @return mixed the item with backticks - */ - function _protect_identifiers($item, $first_word_only = FALSE) + function _escape_identifiers($item) { - if (is_array($item)) + if ($this->_escape_char == '') { - $escaped_array = array(); - - foreach($item as $k=>$v) - { - $escaped_array[$this->_protect_identifiers($k)] = $this->_protect_identifiers($v, $first_word_only); - } - - return $escaped_array; - } - - // This function may get "item1 item2" as a string, and so - // we may need ""item1" "item2"" and not ""item1 item2"" - if (ctype_alnum($item) === FALSE) + return $item; + } + + if (strpos($item, '.') !== FALSE) { - if (strpos($item, '.') !== FALSE) - { - $aliased_tables = implode(".",$this->ar_aliased_tables).'.'; - $table_name = substr($item, 0, strpos($item, '.')+1); - $item = (strpos($aliased_tables, $table_name) !== FALSE) ? $item = $item : $this->dbprefix.$item; - } - - // This function may get "field >= 1", and need it to return ""field" >= 1" - $lbound = ($first_word_only === TRUE) ? '' : '|\s|\('; - - $item = preg_replace('/(^'.$lbound.')([\w\d\-\_]+?)(\s|\)|$)/iS', '$1$2$3', $item); + $str = $this->_escape_char.str_replace('.', $this->_escape_char.'.'.$this->_escape_char, $item).$this->_escape_char; } else { - return $item; + $str = $this->_escape_char.$item.$this->_escape_char; } - - $exceptions = array('AS', '/', '-', '%', '+', '*', 'OR', 'IS'); - foreach ($exceptions as $exception) - { - - if (stristr($item, " \"{$exception}\" ") !== FALSE) - { - $item = preg_replace('/ "('.preg_quote($exception).')" /i', ' $1 ', $item); - } - } - return $item; + // remove duplicates if the user already included the escape + return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str); } - + // -------------------------------------------------------------------- /** @@ -528,7 +466,7 @@ class CI_DB_mssql_driver extends CI_DB { */ function _insert($table, $keys, $values) { - return "INSERT INTO ".$this->_escape_table($table)." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")"; + return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")"; } // -------------------------------------------------------------------- @@ -557,8 +495,10 @@ class CI_DB_mssql_driver extends CI_DB { $orderby = (count($orderby) >= 1)?' ORDER BY '.implode(", ", $orderby):''; - $sql = "UPDATE ".$this->_escape_table($table)." SET ".implode(', ', $valstr); + $sql = "UPDATE ".$table." SET ".implode(', ', $valstr); + $sql .= ($where != '' AND count($where) >=1) ? " WHERE ".implode(" ", $where) : ''; + $sql .= $orderby.$limit; return $sql; @@ -580,7 +520,7 @@ class CI_DB_mssql_driver extends CI_DB { */ function _truncate($table) { - return "TRUNCATE ".$this->_escape_table($table); + return "TRUNCATE ".$table; } // -------------------------------------------------------------------- diff --git a/system/database/drivers/mssql/mssql_forge.php b/system/database/drivers/mssql/mssql_forge.php index c842ac1e2..8665dc055 100644 --- a/system/database/drivers/mssql/mssql_forge.php +++ b/system/database/drivers/mssql/mssql_forge.php @@ -60,7 +60,7 @@ class CI_DB_mssql_forge extends CI_DB_forge { */ function _drop_table($table) { - return "DROP TABLE ".$this->db->_escape_table($table); + return "DROP TABLE ".$this->db->_escape_identifiers($table); } // -------------------------------------------------------------------- @@ -85,7 +85,7 @@ class CI_DB_mssql_forge extends CI_DB_forge { $sql .= 'IF NOT EXISTS '; } - $sql .= $this->db->_escape_table($table)." ("; + $sql .= $this->db->_escape_identifiers($table)." ("; $current_field_count = 0; foreach ($fields as $field=>$attributes) diff --git a/system/database/drivers/mssql/mssql_result.php b/system/database/drivers/mssql/mssql_result.php index b4c22bec9..33fdda9d4 100644 --- a/system/database/drivers/mssql/mssql_result.php +++ b/system/database/drivers/mssql/mssql_result.php @@ -70,12 +70,6 @@ class CI_DB_mssql_result extends CI_DB_result { return $field_names; } - - // Deprecated - function field_names() - { - return $this->list_fields(); - } // -------------------------------------------------------------------- diff --git a/system/database/drivers/oci8/oci8_driver.php b/system/database/drivers/oci8/oci8_driver.php index 006e6ef1f..0c51a5ea7 100644 --- a/system/database/drivers/oci8/oci8_driver.php +++ b/system/database/drivers/oci8/oci8_driver.php @@ -44,6 +44,9 @@ class CI_DB_oci8_driver extends CI_DB { var $dbdriver = 'oci8'; + + // The character used for excaping + var $_escape_char = '"'; /** * The syntax to count rows is slightly different across different @@ -419,7 +422,7 @@ class CI_DB_oci8_driver extends CI_DB { if ($table == '') return '0'; - $query = $this->query($this->_count_string . $this->_protect_identifiers('numrows'). " FROM " . $this->_protect_identifiers($this->dbprefix.$table)); + $query = $this->query($this->_count_string . $this->_protect_identifiers('numrows'). " FROM " . $this->_protect_identifiers($table, TRUE, NULL, FALSE)); if ($query == FALSE) { @@ -482,7 +485,7 @@ class CI_DB_oci8_driver extends CI_DB { */ function _field_data($table) { - return "SELECT * FROM ".$this->_escape_table($table)." where rownum = 1"; + return "SELECT * FROM ".$table." where rownum = 1"; } // -------------------------------------------------------------------- @@ -516,102 +519,34 @@ class CI_DB_oci8_driver extends CI_DB { // -------------------------------------------------------------------- /** - * Escape Column Name + * Escape the SQL Identifiers * - * This function adds backticks around supplied column name + * This function escapes column and table names * * @access private - * @param string the column name + * @param string * @return string */ - function _escape_column($column) + function _escape_identifiers($item) { - // Probably not necessary with Oracle so we simply return the value - return $column; - } - - // -------------------------------------------------------------------- - - /** - * Escape Table Name - * - * This function adds backticks if the table name has a period - * in it. Some DBs will get cranky unless periods are escaped - * - * @access private - * @param string the table name - * @return string - */ - function _escape_table($table) - { - if (strpos($table, '.') !== FALSE) + if ($this->_escape_char == '') { - $table = '"' . str_replace('.', '"."', $table) . '"'; + return $item; } - - return $table; - } - - // -------------------------------------------------------------------- - - /** - * 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) - { - if (is_array($item)) - { - $escaped_array = array(); - - foreach($item as $k=>$v) - { - $escaped_array[$this->_protect_identifiers($k)] = $this->_protect_identifiers($v, $first_word_only); - } - - return $escaped_array; - } - - // This function may get "item1 item2" as a string, and so - // we may need ""item1" "item2"" and not ""item1 item2"" - if (ctype_alnum($item) === FALSE) + + if (strpos($item, '.') !== FALSE) { - if (strpos($item, '.') !== FALSE) - { - $aliased_tables = implode(".",$this->ar_aliased_tables).'.'; - $table_name = substr($item, 0, strpos($item, '.')+1); - $item = (strpos($aliased_tables, $table_name) !== FALSE) ? $item = $item : $this->dbprefix.$item; - } - - // This function may get "field >= 1", and need it to return ""field" >= 1" - $lbound = ($first_word_only === TRUE) ? '' : '|\s|\('; - - $item = preg_replace('/(^'.$lbound.')([\w\d\-\_]+?)(\s|\)|$)/iS', '$1"$2"$3', $item); + $str = $this->_escape_char.str_replace('.', $this->_escape_char.'.'.$this->_escape_char, $item).$this->_escape_char; } else { - return "\"{$item}\""; + $str = $this->_escape_char.$item.$this->_escape_char; } - - $exceptions = array('AS', '/', '-', '%', '+', '*', 'OR', 'IS'); - - foreach ($exceptions as $exception) - { - if (stristr($item, " \"{$exception}\" ") !== FALSE) - { - $item = preg_replace('/ "('.preg_quote($exception).')" /i', ' $1 ', $item); - } - } - return $item; + // remove duplicates if the user already included the escape + return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str); } - + // -------------------------------------------------------------------- /** @@ -649,7 +584,7 @@ class CI_DB_oci8_driver extends CI_DB { */ function _insert($table, $keys, $values) { - return "INSERT INTO ".$this->_escape_table($table)." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")"; + return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")"; } // -------------------------------------------------------------------- @@ -678,8 +613,10 @@ class CI_DB_oci8_driver extends CI_DB { $orderby = (count($orderby) >= 1)?' ORDER BY '.implode(", ", $orderby):''; - $sql = "UPDATE ".$this->_escape_table($table)." SET ".implode(', ', $valstr); + $sql = "UPDATE ".$table." SET ".implode(', ', $valstr); + $sql .= ($where != '' AND count($where) >=1) ? " WHERE ".implode(" ", $where) : ''; + $sql .= $orderby.$limit; return $sql; @@ -700,7 +637,7 @@ class CI_DB_oci8_driver extends CI_DB { */ function _truncate($table) { - return "TRUNCATE TABLE ".$this->_escape_table($table); + return "TRUNCATE TABLE ".$table; } // -------------------------------------------------------------------- diff --git a/system/database/drivers/sqlite/sqlite_driver.php b/system/database/drivers/sqlite/sqlite_driver.php index df19dba78..0ba483f8b 100644 --- a/system/database/drivers/sqlite/sqlite_driver.php +++ b/system/database/drivers/sqlite/sqlite_driver.php @@ -33,6 +33,9 @@ class CI_DB_sqlite_driver extends CI_DB { var $dbdriver = 'sqlite'; + + // The character used to escape with - not needed for SQLite + var $_escape_char = ''; /** * The syntax to count rows is slightly different across different @@ -300,7 +303,7 @@ class CI_DB_sqlite_driver extends CI_DB { if ($table == '') return '0'; - $query = $this->query($this->_count_string . $this->_protect_identifiers('numrows'). " FROM " . $this->_protect_identifiers($this->dbprefix.$table)); + $query = $this->query($this->_count_string . $this->_protect_identifiers('numrows'). " FROM " . $this->_protect_identifiers($table, TRUE, NULL, FALSE)); if ($query->num_rows() == 0) return '0'; @@ -361,7 +364,7 @@ class CI_DB_sqlite_driver extends CI_DB { */ function _field_data($table) { - return "SELECT * FROM ".$this->_escape_table($table)." LIMIT 1"; + return "SELECT * FROM ".$table." LIMIT 1"; } // -------------------------------------------------------------------- @@ -393,97 +396,32 @@ class CI_DB_sqlite_driver extends CI_DB { // -------------------------------------------------------------------- /** - * Escape Column Name - * - * This function adds backticks around supplied column name - * - * @access private - * @param string the column name - * @return string - */ - function _escape_column($column) - { - // Not necessary with SQLite so we simply return the value - return $column; - } - - // -------------------------------------------------------------------- - - /** - * Escape Table Name + * Escape the SQL Identifiers * - * This function adds backticks if the table name has a period - * in it. Some DBs will get cranky unless periods are escaped + * This function escapes column and table names * * @access private - * @param string the table name + * @param string * @return string */ - function _escape_table($table) + function _escape_identifiers($item) { - // other database drivers use this to add backticks, hence this - // function is simply going to return the tablename for sqlite - return $table; - } - - // -------------------------------------------------------------------- - - /** - * 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) - { - if (is_array($item)) + if ($this->_escape_char == '') { - $escaped_array = array(); - - foreach($item as $k=>$v) - { - $escaped_array[$this->_protect_identifiers($k)] = $this->_protect_identifiers($v, $first_word_only); - } - - return $escaped_array; - } - - // This function may get "item1 item2" as a string, and so - // we may need "item1 item2" and not "item1 item2" - if (ctype_alnum($item) === FALSE) + return $item; + } + + if (strpos($item, '.') !== FALSE) { - if (strpos($item, '.') !== FALSE) - { - $aliased_tables = implode(".",$this->ar_aliased_tables).'.'; - $table_name = substr($item, 0, strpos($item, '.')+1); - $item = (strpos($aliased_tables, $table_name) !== FALSE) ? $item = $item : $this->dbprefix.$item; - } - - // This function may get "field >= 1", and need it to return "field >= 1" - $lbound = ($first_word_only === TRUE) ? '' : '|\s|\('; - - $item = preg_replace('/(^'.$lbound.')([\w\d\-\_]+?)(\s|\)|$)/iS', '$1$2$3', $item); + $str = $this->_escape_char.str_replace('.', $this->_escape_char.'.'.$this->_escape_char, $item).$this->_escape_char; } else { - return "{$item}"; + $str = $this->_escape_char.$item.$this->_escape_char; } - - $exceptions = array('AS', '/', '-', '%', '+', '*', 'OR', 'IS'); - - foreach ($exceptions as $exception) - { - if (stristr($item, " {$exception} ") !== FALSE) - { - $item = preg_replace('/ ('.preg_quote($exception).') /i', ' $1 ', $item); - } - } - return $item; + // remove duplicates if the user already included the escape + return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str); } // -------------------------------------------------------------------- @@ -523,7 +461,7 @@ class CI_DB_sqlite_driver extends CI_DB { */ function _insert($table, $keys, $values) { - return "INSERT INTO ".$this->_escape_table($table)." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")"; + return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")"; } // -------------------------------------------------------------------- @@ -552,8 +490,10 @@ class CI_DB_sqlite_driver extends CI_DB { $orderby = (count($orderby) >= 1)?' ORDER BY '.implode(", ", $orderby):''; - $sql = "UPDATE ".$this->_escape_table($table)." SET ".implode(', ', $valstr); + $sql = "UPDATE ".$table." SET ".implode(', ', $valstr); + $sql .= ($where != '' AND count($where) >=1) ? " WHERE ".implode(" ", $where) : ''; + $sql .= $orderby.$limit; return $sql; diff --git a/system/database/drivers/sqlite/sqlite_forge.php b/system/database/drivers/sqlite/sqlite_forge.php index 631c9c771..2039525be 100644 --- a/system/database/drivers/sqlite/sqlite_forge.php +++ b/system/database/drivers/sqlite/sqlite_forge.php @@ -82,7 +82,7 @@ class CI_DB_sqlite_forge extends CI_DB_forge { $sql .= 'IF NOT EXISTS '; } - $sql .= $this->db->_escape_table($table)."("; + $sql .= $this->db->_escape_identifiers($table)."("; $current_field_count = 0; foreach ($fields as $field=>$attributes) diff --git a/system/database/drivers/sqlite/sqlite_result.php b/system/database/drivers/sqlite/sqlite_result.php index 9fbd72556..735a0736a 100644 --- a/system/database/drivers/sqlite/sqlite_result.php +++ b/system/database/drivers/sqlite/sqlite_result.php @@ -71,12 +71,6 @@ class CI_DB_sqlite_result extends CI_DB_result { return $field_names; } - // Deprecated - function field_names() - { - return $this->list_fields(); - } - // -------------------------------------------------------------------- /** diff --git a/system/language/english/db_lang.php b/system/language/english/db_lang.php index 697eb7880..2f7ccb5f3 100644 --- a/system/language/english/db_lang.php +++ b/system/language/english/db_lang.php @@ -6,6 +6,7 @@ $lang['db_unable_to_select'] = 'Unable to select the specified database: %s'; $lang['db_unable_to_create'] = 'Unable to create the specified database: %s'; $lang['db_invalid_query'] = 'The query you submitted is not valid.'; $lang['db_must_set_table'] = 'You must set the database table to be used with your query.'; +$lang['db_must_set_database'] = 'You must set the database name in your database config file.'; $lang['db_must_use_set'] = 'You must use the "set" method to update an entry.'; $lang['db_must_use_where'] = 'Updates are not allowed unless they contain a "where" clause.'; $lang['db_del_must_use_where'] = 'Deletes are not allowed unless they contain a "where" or "like" clause.'; -- cgit v1.2.3-24-g4f1b