join() with multiple conditions and optional escape parameter

2 files changed, 71 insertions, 31 deletions

diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php
index 645ac3969..488b294e4 100644
--- a/system/database/DB_query_builder.php
+++ b/system/database/DB_query_builder.php
@@ -83,6 +83,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 	 * Generates the SELECT portion of the query
 	 *
 	 * @param	string
+	 * @param	mixed
 	 * @return	object
 	 */
 	public function select($select = '*', $escape = NULL)
@@ -92,6 +93,9 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 			$select = explode(',', $select);
 		}
 
+		// If the escape value was not set will will base it on the global setting
+		is_bool($escape) OR $escape = $this->_protect_identifiers;
+
 		foreach ($select as $val)
 		{
 			$val = trim($val);
@@ -320,15 +324,16 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 	 * @param	string
 	 * @param	string	the join condition
 	 * @param	string	the type of join
+	 * @param	string	wether not to try to escape identifiers
 	 * @return	object
 	 */
-	public function join($table, $cond, $type = '')
+	public function join($table, $cond, $type = '', $escape = TRUE)
 	{
 		if ($type !== '')
 		{
 			$type = strtoupper(trim($type));
 
-			if ( ! in_array($type, array('LEFT', 'RIGHT', 'OUTER', 'INNER', 'LEFT OUTER', 'RIGHT OUTER')))
+			if ( ! in_array($type, array('LEFT', 'RIGHT', 'OUTER', 'INNER', 'LEFT OUTER', 'RIGHT OUTER'), TRUE))
 			{
 				$type = '';
 			}
@@ -342,12 +347,39 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 		// in the protect_identifiers to know whether to add a table prefix
 		$this->_track_aliases($table);
 
-		// Strip apart the condition and protect the identifiers
-		if (preg_match('/([\[\w\.-]+)([\W\s]+)(.+)/', $cond, $match))
+		// Split multiple conditions
+		if ($escape === TRUE && preg_match_all('/\sAND\s|\sOR\s/i', $cond, $m, PREG_SET_ORDER | PREG_OFFSET_CAPTURE))
+		{
+			$newcond = '';
+			$m[0][] = array('', strlen($cond));
+
+			for ($i = 0, $c = count($m[0]), $s = 0;
+				$i < $c;
+				$s += $m[0][$i][1] + strlen($m[0][$i][0]), $i++)
+			{
+				$temp = substr($cond, $s, $m[0][$i][1]);
+
+				$newcond .= preg_match('/([\[\w\.-]+)([\W\s]+)(.+)/i', $temp, $match)
+						? $this->protect_identifiers($match[1]).$match[2].$this->protect_identifiers($match[3])
+						: $temp;
+
+				$newcond .= $m[0][$i][0];
+			}
+
+			$cond = $newcond;
+		}
+		// Split apart the condition and protect the identifiers
+		elseif ($escape === TRUE && preg_match('/([\[\w\.-]+)([\W\s]+)(.+)/i', $cond, $match))
 		{
 			$cond = $this->protect_identifiers($match[1]).$match[2].$this->protect_identifiers($match[3]);
 		}
 
+		// Do we want to escape the table name?
+		if ($escape === TRUE)
+		{
+			$table = $this->protect_identifiers($table, TRUE, NULL, FALSE);
+		}
+
 		// Assemble the JOIN statement
 		$this->qb_join[] = $join = $type.'JOIN '.$this->protect_identifiers($table, TRUE, NULL, FALSE).' ON '.$cond;
 
@@ -370,6 +402,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 	 *
 	 * @param	mixed
 	 * @param	mixed
+	 * @param	bool
 	 * @return	object
 	 */
 	public function where($key, $value = NULL, $escape = TRUE)
@@ -387,6 +420,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 	 *
 	 * @param	mixed
 	 * @param	mixed
+	 * @param	bool
 	 * @return	object
 	 */
 	public function or_where($key, $value = NULL, $escape = TRUE)
@@ -404,6 +438,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 	 * @param	mixed
 	 * @param	mixed
 	 * @param	string
+	 * @param	mixed
 	 * @return	object
 	 */
 	protected function _where($key, $value = NULL, $type = 'AND ', $escape = NULL)
@@ -416,10 +451,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 		}
 
 		// If the escape value was not set will will base it on the global setting
-		if ( ! is_bool($escape))
-		{
-			$escape = $this->_protect_identifiers;
-		}
+		$escape = $this->_protect_identifiers;
 
 		foreach ($key as $k => $v)
 		{
@@ -851,6 +883,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 	 *
 	 * @param	string
 	 * @param	string
+	 * @param	bool
 	 * @return	object
 	 */
 	public function having($key, $value = '', $escape = TRUE)
@@ -867,6 +900,7 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 	 *
 	 * @param	string
 	 * @param	string
+	 * @param	bool
 	 * @return	object
 	 */
 	public function or_having($key, $value = '', $escape = TRUE)
@@ -883,6 +917,8 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 	 *
 	 * @param	string
 	 * @param	string
+	 * @param	string
+	 * @param	bool
 	 * @return	object
 	 */
 	protected function _having($key, $value = '', $type = 'AND ', $escape = TRUE)
diff --git a/user_guide_src/source/changelog.rst b/user_guide_src/source/changelog.rst
index fb137e460..2c76ea43f 100644
--- a/user_guide_src/source/changelog.rst
+++ b/user_guide_src/source/changelog.rst
@@ -63,14 +63,17 @@ Release Date: Not Released
 
 -  Database
 
-   -  Renamed the Active Record class to Query Builder to remove confusion with the Active Record design pattern.
-   -  Added the ability to insert objects with insert_batch() in :doc:`Query Builder <database/query_builder>`.
-   -  Added new :doc:`Query Builder <database/query_builder>` methods that return the SQL string of queries without executing them: get_compiled_select(), get_compiled_insert(), get_compiled_update(), get_compiled_delete().
-   -  Adding $escape parameter to the order_by() method, this enables ordering by custom fields.
+   -  :doc:`Query Builder <database/query_builder>` changes include:
+	 - Renamed the Active Record class to Query Builder to remove confusion with the Active Record design pattern.
+	 - Added the ability to insert objects with insert_batch().
+	 - Added new methods that return the SQL string of queries without executing them: get_compiled_select(), get_compiled_insert(), get_compiled_update(), get_compiled_delete().
+	 - Added an optional order_by() parameter that allows to disable escaping (useful for custom fields).
+	 - Added an optional join() parameter that allows to disable escaping.
+	 - Added support for join() with multiple conditions.
    -  Improved support for the MySQLi driver, including:
-	 -  OOP style of the PHP extension is now used, instead of the procedural aliases.
-	 -  Server version checking is now done via ``mysqli::$server_info`` instead of running an SQL query.
-	 -  Added persistent connections support for PHP >= 5.3.
+	 - OOP style of the PHP extension is now used, instead of the procedural aliases.
+	 - Server version checking is now done via ``mysqli::$server_info`` instead of running an SQL query.
+	 - Added persistent connections support for PHP >= 5.3.
    -  Added 'dsn' configuration setting for drivers that support DSN strings (PDO, PostgreSQL, Oracle, ODBC, CUBRID).
    -  Improved PDO database support.
    -  Added Interbase/Firebird database support via the "interbase" driver.
@@ -78,14 +81,16 @@ Release Date: Not Released
    -  Replaced the _error_message() and _error_number() methods with error(), that returns an array containing the last database error code and message.
    -  Improved version() implementation so that drivers that have a native function to get the version number don't have to be defined in the core DB_driver class.
    -  Improved support of the PostgreSQL driver, including:
-	 -  pg_version() is now used to get the database version number, when possible.
-	 -  Added db_set_charset() support.
-	 -  Added _optimize_table() support for the :doc:`Database Utility Class <database/utilities>` (rebuilds table indexes).
-	 -  Added boolean data type support in escape().
-	 -  Added update_batch() support.
-	 -  Removed limit() and order_by() support for UPDATE and DELETE queries in as PostgreSQL does not support those features.
+	 - pg_version() is now used to get the database version number, when possible.
+	 - Added db_set_charset() support.
+	 - Added _optimize_table() support for the :doc:`Database Utility Class <database/utilities>` (rebuilds table indexes).
+	 - Added boolean data type support in escape().
+	 - Added update_batch() support.
+	 - Removed limit() and order_by() support for UPDATE and DELETE queries in as PostgreSQL does not support those features.
    -  Added a constructor to the DB_result class and moved all driver-specific properties and logic out of the base DB_driver class to allow better abstraction.
    -  Removed protect_identifiers() and renamed internal method _protect_identifiers() to it instead - it was just an alias.
+   -  Renamed internal method _escape_identifiers() to escape_identifiers().
+   -  Updated escape_identifiers() to accept an array of fields as well as strings.
    -  MySQL and MySQLi drivers now require at least MySQL version 5.1.
    -  db_set_charset() now only requires one parameter (collation was only needed due to legacy support for MySQL versions prior to 5.1).
    -  Added support for SQLite3 database driver.
@@ -100,16 +105,15 @@ Release Date: Not Released
 	 - Added port handling support for UNIX-based systems (MSSQL driver).
 	 - Added OFFSET support for SQL Server 2005 and above.
    -  Improved support of the Oracle (OCI8) driver, including:
-	 -  Added DSN string support (Easy Connect and TNS).
-	 -  Added support for dropping tables to :doc:`Database Forge <database/forge>`.
-	 -  Added support for listing database schemas to :doc:`Database Utilities <database/utilities>`.
-	 -  Generally improved for speed and cleaned up all of its components.
-	 -  *Row* result methods now really only fetch only the needed number of rows, instead of depending entirely on result().
-	 -  num_rows() is now only called explicitly by the developer and no longer re-executes statements.
-   -  Added replace() support for SQLite.
-   -  Renamed internal method _escape_identifiers() to escape_identifiers().
-   -  Updated escape_identifiers() to accept an array of fields as well as strings.
-   -  Added SQLite support for drop_table() in :doc:`Database Forge <database/forge>`.
+	 - Added DSN string support (Easy Connect and TNS).
+	 - Added support for drop_table() in :doc:`Database Forge <database/forge>`.
+	 - Added support for list_databases() in :doc:`Database Utilities <database/utilities>`.
+	 - Generally improved for speed and cleaned up all of its components.
+	 - *Row* result methods now really only fetch only the needed number of rows, instead of depending entirely on result().
+	 - num_rows() is now only called explicitly by the developer and no longer re-executes statements.
+   -  Improved support of the Sqlite driver, including:
+	 - Added support for replace() in :doc:`Query Builder <database/query_builder>`.
+	 - Added support for drop_table() in :doc:`Database Forge <database/forge>`.
    -  Added ODBC support for create_database(), drop_database() and drop_table() in :doc:`Database Forge <database/forge>`.
    -  Added PDO support for create_database(), drop_database and drop_table() in :doc:`Database Forge <database/forge>`.
    -  Added unbuffered_row() method for getting a row without prefetching whole result (consume less memory).