join() with multiple conditions and optional escape parameter
diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php
index 645ac39..488b294 100644
--- a/system/database/DB_query_builder.php
+++ b/system/database/DB_query_builder.php
@@ -83,6 +83,7 @@
* Generates the SELECT portion of the query
*
* @param string
+ * @param mixed
* @return object
*/
public function select($select = '*', $escape = NULL)
@@ -92,6 +93,9 @@
$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 @@
* @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 @@
// 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 @@
*
* @param mixed
* @param mixed
+ * @param bool
* @return object
*/
public function where($key, $value = NULL, $escape = TRUE)
@@ -387,6 +420,7 @@
*
* @param mixed
* @param mixed
+ * @param bool
* @return object
*/
public function or_where($key, $value = NULL, $escape = TRUE)
@@ -404,6 +438,7 @@
* @param mixed
* @param mixed
* @param string
+ * @param mixed
* @return object
*/
protected function _where($key, $value = NULL, $type = 'AND ', $escape = NULL)
@@ -416,10 +451,7 @@
}
// 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 @@
*
* @param string
* @param string
+ * @param bool
* @return object
*/
public function having($key, $value = '', $escape = TRUE)
@@ -867,6 +900,7 @@
*
* @param string
* @param string
+ * @param bool
* @return object
*/
public function or_having($key, $value = '', $escape = TRUE)
@@ -883,6 +917,8 @@
*
* @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 fb137e4..2c76ea4 100644
--- a/user_guide_src/source/changelog.rst
+++ b/user_guide_src/source/changelog.rst
@@ -63,14 +63,17 @@
- 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 @@
- 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 @@
- 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).