Fix WHERE escaping/prefixing

2 files changed, 100 insertions, 38 deletions

diff --git a/system/database/DB_driver.php b/system/database/DB_driver.php
index d63a1d955..b7c6b4e8e 100644
--- a/system/database/DB_driver.php
+++ b/system/database/DB_driver.php
@@ -1168,8 +1168,21 @@ abstract class CI_DB_driver {
 	 */
 	protected function _get_operator($str)
 	{
-		return preg_match('/(=|!|<|>| IS NULL| IS NOT NULL| BETWEEN)/i', $str, $match)
-			? $match[1] : FALSE;
+		static $_operators = array(
+			'\s*(?:<|>|!)?=\s*',		// =, <=, >=, !=
+			'\s*<>?\s*',			// <, <>
+			'\s*>\s*',			// >
+			'\s+IS NULL',			// IS NULL
+			'\s+IS NOT NULL',		// IS NOT NULL
+			'\s+LIKE\s+',			// LIKE
+			'\s+NOT LIKE\s+',		// NOT LIKE
+			'\s+BETWEEN\s+\S+\s+AND\s+\S+',	// BETWEEN value AND value
+			'\s+IN\s*\([^\)]+\)',		// IN(list)
+			'\s+NOT IN\s*\([^\)]+\)'	// NOT IN (list)
+		);
+
+		return preg_match('/'.implode('|', $_operators).'/i', $str, $match)
+			? $match[0] : FALSE;
 	}
 
 	// --------------------------------------------------------------------
diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php
index 479b7f24a..92cb8c1d5 100644
--- a/system/database/DB_query_builder.php
+++ b/system/database/DB_query_builder.php
@@ -467,13 +467,6 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 				? $this->_group_get_type('')
 				: $this->_group_get_type($type);
 
-			if ($escape === TRUE)
-			{
-				$k = (($op = $this->_get_operator($k)) !== FALSE)
-					? $this->escape_identifiers(trim(substr($k, 0, strpos($k, $op)))).' '.strstr($k, $op)
-					: $this->escape_identifiers(trim($k));
-			}
-
 			if (is_null($v) && ! $this->_has_operator($k))
 			{
 				// value appears not to have been set, assign the test to IS NULL
@@ -493,10 +486,11 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 				}
 			}
 
-			$this->qb_where[] = $prefix.$k.$v;
+			$this->qb_where[] = array('condition' => $prefix.$k.$v, 'escape' => $escape);
 			if ($this->qb_caching === TRUE)
 			{
-				$this->qb_cache_where[] = $prefix.$k.$v;
+				// check this shit
+				$this->qb_cache_where[] = array('condition' => $prefix.$k.$v, 'escape' => $escape);
 				$this->qb_cache_exists[] = 'where';
 			}
 
@@ -607,14 +601,13 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 			$this->qb_wherein[] = $this->escape($value);
 		}
 
-		if ($escape === TRUE)
-		{
-			$key = $this->escape_identifiers(trim($key));
-		}
-
 		$prefix = (count($this->qb_where) === 0) ? $this->_group_get_type('') : $this->_group_get_type($type);
-		$this->qb_where[] = $where_in = $prefix.$key.$not.' IN ('.implode(', ', $this->qb_wherein).') ';
+		$where_in = array(
+			'condition' => $prefix.$key.$not.' IN('.implode(', ', $this->qb_wherein).')',
+			'escape' => $escape
+		);
 
+		$this->qb_where[] = $where_in;
 		if ($this->qb_caching === TRUE)
 		{
 			$this->qb_cache_where[] = $where_in;
@@ -769,11 +762,15 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 
 		$this->qb_where_group_started = TRUE;
 		$prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0) ? '' : $type;
-		$this->qb_where[] = $value = $prefix.$not.str_repeat(' ', ++$this->qb_where_group_count).' (';
+		$where = array(
+			'condition' => $prefix.$not.str_repeat(' ', ++$this->qb_where_group_count).' (',
+			'escape' => FALSE
+		);
 
+		$this->qb_where[] = $where;
 		if ($this->qb_caching)
 		{
-			$this->qb_cache_where[] = $value;
+			$this->qb_cache_where[] = $where;
 		}
 
 		return $this;
@@ -825,11 +822,15 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 	public function group_end()
 	{
 		$this->qb_where_group_started = FALSE;
-		$this->qb_where[] = $value = str_repeat(' ', $this->qb_where_group_count--) . ')';
+		$where = array(
+			'condition' => str_repeat(' ', $this->qb_where_group_count--).')',
+			'escape' => FALSE
+		);
 
+		$this->qb_where[] = $where;
 		if ($this->qb_caching)
 		{
-			$this->qb_cache_where[] = $value;
+			$this->qb_cache_where[] = $where;
 		}
 
 		return $this;
@@ -2067,49 +2068,97 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 			$sql .= "\n".implode("\n", $this->qb_join);
 		}
 
-		// Write the "WHERE" portion of the query
-		if (count($this->qb_where) > 0 OR count($this->qb_like) > 0)
+		$sql .= $this->_compile_conditions();
+
+		// Write the "LIMIT" portion of the query
+		if (is_numeric($this->qb_limit))
 		{
-			$sql .= "\nWHERE ";
+			return $this->_limit($sql."\n", $this->qb_limit, $this->qb_offset);
 		}
 
-		$sql .= implode("\n", $this->qb_where);
+		return $sql;
+	}
 
-		// Write the "LIKE" portion of the query
-		if (count($this->qb_like) > 0)
+	// --------------------------------------------------------------------
+
+	/**
+	 * Compile WHERE statement
+	 *
+	 * Escapes identifiers in WHERE, LIKE, HAVING, GROUP BY, ORDER BY
+	 * statements at execution time. Required so that aliases are tracked
+	 * properly, regardless of wether e.g. where() is called prior to
+	 * join() and dbprefix is added only if needed.
+	 *
+	 * @return	string
+	 */
+	protected function _compile_conditions()
+	{
+		// WHERE
+		if (count($this->qb_where) > 0)
 		{
-			if (count($this->qb_where) > 0)
+			$sql = "\nWHERE ";
+
+			for ($i = 0, $c = count($this->qb_where); $i < $c; $i++)
 			{
-				$sql .= "\nAND ";
+				if ($this->qb_where[$i]['escape'] === FALSE)
+				{
+					$this->qb_where[$i] = $this->qb_where[$i]['condition'];
+					continue;
+				}
+
+				$op = preg_quote($this->_get_operator($this->qb_where[$i]['condition']));
+				if ( ! preg_match('/^(\s*(?:AND|OR)\s+)?(\(?)(.*)('.$op.')(.*(?<!\)))?(\)?)$/i', $this->qb_where[$i]['condition'], $matches))
+				{
+					$this->qb_where[$i] = $this->qb_where[$i]['condition'];
+					continue;
+				}
+
+				// $matches = array(
+				//	0 => 'OR (test <= foo)',	/* the whole thing */
+				//	1 => 'OR ',			/* optional */
+				//	2 => '(',			/* optional */
+				//	3 => 'test',			/* the field name */
+				//	4 => ' <= ',			/* $op */
+				//	5 => 'foo',			/* optional, if $op is e.g. 'IS NULL' */
+				//	6 => ')'			/* optional */
+				// );
+				empty($matches[5]) OR $matches[5] = ' '.$this->protect_identifiers(trim($matches[5]));
+				$this->qb_where[$i] = $matches[1].$matches[2].$this->protect_identifiers(trim($matches[3]))
+						.' '.trim($matches[4]).$matches[5].$matches[6];
 			}
 
+			$sql .= implode("\n", $this->qb_where);
+		}
+		else
+		{
+			$sql = '';
+		}
+
+		// LIKE
+		if (count($this->qb_like) > 0)
+		{
+			$sql .= ($sql === '') ? "\nWHERE " : "\nAND ";
 			$sql .= implode("\n", $this->qb_like);
 		}
 
-		// Write the "GROUP BY" portion of the query
+		// GROUP BY
 		if (count($this->qb_groupby) > 0)
 		{
 			$sql .= "\nGROUP BY ".implode(', ', $this->qb_groupby);
 		}
 
-		// Write the "HAVING" portion of the query
+		// HAVING
 		if (count($this->qb_having) > 0)
 		{
 			$sql .= "\nHAVING ".implode("\n", $this->qb_having);
 		}
 
-		// Write the "ORDER BY" portion of the query
+		// ORDER BY
 		if (count($this->qb_orderby) > 0)
 		{
 			$sql .= "\nORDER BY ".implode(', ', $this->qb_orderby);
 		}
 
-		// Write the "LIMIT" portion of the query
-		if (is_numeric($this->qb_limit))
-		{
-			return $this->_limit($sql."\n", $this->qb_limit, $this->qb_offset);
-		}
-
 		return $sql;
 	}