From 428702387ca071db4686ec6d6c60bd35b01c33e4 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Tue, 12 Jun 2012 01:30:20 +0300 Subject: join() with multiple conditions and optional escape parameter --- system/database/DB_query_builder.php | 52 ++++++++++++++++++++++++++++++------ user_guide_src/source/changelog.rst | 50 ++++++++++++++++++---------------- 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 `. - - Added new :doc:`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 ` 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 ` (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 ` (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 `. - - Added support for listing database schemas to :doc:`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 `. + - Added DSN string support (Easy Connect and TNS). + - Added support for drop_table() in :doc:`Database Forge `. + - Added support for list_databases() in :doc:`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 `. + - Added support for drop_table() in :doc:`Database Forge `. - Added ODBC support for create_database(), drop_database() and drop_table() in :doc:`Database Forge `. - Added PDO support for create_database(), drop_database and drop_table() in :doc:`Database Forge `. - Added unbuffered_row() method for getting a row without prefetching whole result (consume less memory). -- cgit v1.2.3-24-g4f1b