From b5a43b08bdc7353e1c54d6012be1b0dd008a4aa0 Mon Sep 17 00:00:00 2001 From: Timothy Warren Date: Tue, 4 Oct 2011 17:26:04 -0400 Subject: Added batch functions, fixed excaping function --- system/database/drivers/pdo/pdo_driver.php | 93 +++++++++++++++++++++++++++++- 1 file changed, 90 insertions(+), 3 deletions(-) diff --git a/system/database/drivers/pdo/pdo_driver.php b/system/database/drivers/pdo/pdo_driver.php index c5a215b82..244a15e1e 100644 --- a/system/database/drivers/pdo/pdo_driver.php +++ b/system/database/drivers/pdo/pdo_driver.php @@ -49,7 +49,7 @@ class CI_DB_pdo_driver extends CI_DB { function __construct($params) { - parent::CI_DB($params); + parent::__construct($params); // clause and character used for LIKE escape sequences if(strpos($this->hostname, 'mysql') !== FALSE) @@ -180,7 +180,14 @@ class CI_DB_pdo_driver extends CI_DB { $sql = $this->_prep_query($sql); $result_id = $this->conn_id->query($sql); - $this->affect_rows = $result_id->rowCount(); + if(is_object($result_id)) + { + $this->affect_rows = $result_id->rowCount(); + } + else + { + $this->affect_rows = 0; + } return $result_id; } @@ -302,8 +309,18 @@ class CI_DB_pdo_driver extends CI_DB { return $str; } - // PDO doesn't require escaping + // Remove invisible characters $str = remove_invisible_characters($str); + + //Make sure to escape slashes and quotes + $replace = array( + "\\" => "\\\\", + "'" => "\\'", + "\"" => "\\\"", + ); + + $str = strtr($str, $replace); + // escape LIKE condition wildcards if ($like === TRUE) @@ -538,6 +555,24 @@ class CI_DB_pdo_driver extends CI_DB { { return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")"; } + + // -------------------------------------------------------------------- + + /** + * Insert_batch statement + * + * Generates a platform-specific insert string from the supplied data + * + * @access public + * @param string the table name + * @param array the insert keys + * @param array the insert values + * @return string + */ + function _insert_batch($table, $keys, $values) + { + return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES ".implode(', ', $values); + } // -------------------------------------------------------------------- @@ -573,6 +608,58 @@ class CI_DB_pdo_driver extends CI_DB { return $sql; } + + // -------------------------------------------------------------------- + + /** + * Update_Batch statement + * + * Generates a platform-specific batch update string from the supplied data + * + * @access public + * @param string the table name + * @param array the update data + * @param array the where clause + * @return string + */ + function _update_batch($table, $values, $index, $where = NULL) + { + $ids = array(); + $where = ($where != '' AND count($where) >=1) ? implode(" ", $where).' AND ' : ''; + + foreach ($values as $key => $val) + { + $ids[] = $val[$index]; + + foreach (array_keys($val) as $field) + { + if ($field != $index) + { + $final[$field][] = 'WHEN '.$index.' = '.$val[$index].' THEN '.$val[$field]; + } + } + } + + $sql = "UPDATE ".$table." SET "; + $cases = ''; + + foreach ($final as $k => $v) + { + $cases .= $k.' = CASE '."\n"; + foreach ($v as $row) + { + $cases .= $row."\n"; + } + + $cases .= 'ELSE '.$k.' END, '; + } + + $sql .= substr($cases, 0, -2); + + $sql .= ' WHERE '.$where.$index.' IN ('.implode(',', $ids).')'; + + return $sql; + } // -------------------------------------------------------------------- -- cgit v1.2.3-24-g4f1b From 47663970e357c51ad16d1a1a3d3b52428c022505 Mon Sep 17 00:00:00 2001 From: Timothy Warren Date: Wed, 5 Oct 2011 16:44:50 -0400 Subject: Changed to escape using PDO::quote() --- system/database/drivers/pdo/pdo_driver.php | 15 ++++----------- 1 file changed, 4 insertions(+), 11 deletions(-) diff --git a/system/database/drivers/pdo/pdo_driver.php b/system/database/drivers/pdo/pdo_driver.php index 244a15e1e..568819a08 100644 --- a/system/database/drivers/pdo/pdo_driver.php +++ b/system/database/drivers/pdo/pdo_driver.php @@ -308,19 +308,12 @@ class CI_DB_pdo_driver extends CI_DB { return $str; } - - // Remove invisible characters - $str = remove_invisible_characters($str); - //Make sure to escape slashes and quotes - $replace = array( - "\\" => "\\\\", - "'" => "\\'", - "\"" => "\\\"", - ); - - $str = strtr($str, $replace); + //Escape the string + $str = $this->conn_id->quote($str); + //If there are duplicated quotes, trim them away + $str = substr($str, 1, -1); // escape LIKE condition wildcards if ($like === TRUE) -- cgit v1.2.3-24-g4f1b From f7a8d86dbc6805a4e52964bbea76738df75b5f35 Mon Sep 17 00:00:00 2001 From: Timothy Warren Date: Wed, 5 Oct 2011 20:41:38 -0400 Subject: Changed all db constructors to newer syntax, made insert_id() function more convenient for postgres on pdo driver --- system/database/DB_cache.php | 2 +- system/database/DB_driver.php | 2 +- system/database/DB_forge.php | 2 +- system/database/DB_utility.php | 2 +- system/database/drivers/odbc/odbc_driver.php | 4 ++-- system/database/drivers/pdo/pdo_driver.php | 20 +++++++++++++++++++- 6 files changed, 25 insertions(+), 7 deletions(-) diff --git a/system/database/DB_cache.php b/system/database/DB_cache.php index 3bf065ca5..ad1c28d72 100644 --- a/system/database/DB_cache.php +++ b/system/database/DB_cache.php @@ -33,7 +33,7 @@ class CI_DB_Cache { * Grabs the CI super object instance so we can access it. * */ - function CI_DB_Cache(&$db) + function __construct(&$db) { // Assign the main CI object to $this->CI // and load the file helper since we use it a lot diff --git a/system/database/DB_driver.php b/system/database/DB_driver.php index 237a4fcea..d7b63b9dc 100644 --- a/system/database/DB_driver.php +++ b/system/database/DB_driver.php @@ -78,7 +78,7 @@ class CI_DB_driver { * * @param array */ - function CI_DB_driver($params) + function __construct($params) { if (is_array($params)) { diff --git a/system/database/DB_forge.php b/system/database/DB_forge.php index 0dd29c238..6bc40411b 100644 --- a/system/database/DB_forge.php +++ b/system/database/DB_forge.php @@ -35,7 +35,7 @@ class CI_DB_forge { * Grabs the CI super object instance so we can access it. * */ - function CI_DB_forge() + function __construct() { // Assign the main database object to $this->db $CI =& get_instance(); diff --git a/system/database/DB_utility.php b/system/database/DB_utility.php index a5f174f0a..52196b7ce 100644 --- a/system/database/DB_utility.php +++ b/system/database/DB_utility.php @@ -33,7 +33,7 @@ class CI_DB_utility extends CI_DB_forge { * Grabs the CI super object instance so we can access it. * */ - function CI_DB_utility() + function __construct() { // Assign the main database object to $this->db $CI =& get_instance(); diff --git a/system/database/drivers/odbc/odbc_driver.php b/system/database/drivers/odbc/odbc_driver.php index 08cd27b6c..bcd7937d9 100644 --- a/system/database/drivers/odbc/odbc_driver.php +++ b/system/database/drivers/odbc/odbc_driver.php @@ -48,9 +48,9 @@ class CI_DB_odbc_driver extends CI_DB { var $_random_keyword; - function CI_DB_odbc_driver($params) + function __construct($params) { - parent::CI_DB_driver($params); + parent::__construct($params); $this->_random_keyword = ' RND('.time().')'; // database specific random keyword } diff --git a/system/database/drivers/pdo/pdo_driver.php b/system/database/drivers/pdo/pdo_driver.php index 568819a08..1a84404bb 100644 --- a/system/database/drivers/pdo/pdo_driver.php +++ b/system/database/drivers/pdo/pdo_driver.php @@ -349,7 +349,25 @@ class CI_DB_pdo_driver extends CI_DB { */ function insert_id($name=NULL) { - return $this->conn_id->lastInsertId($name); + //Convenience method for postgres insertid + if(strpos($this->hostname, 'pgsql') !== FALSE) + { + $v = $this->_version(); + + $table = func_num_args() > 0 ? func_get_arg(0) : NULL; + + if ($table == NULL && $v >= '8.1') + { + $sql='SELECT LASTVAL() as ins_id'; + } + $query = $this->query($sql); + $row = $query->row(); + return $row->ins_id; + } + else + { + return $this->conn_id->lastInsertId($name); + } } // -------------------------------------------------------------------- -- cgit v1.2.3-24-g4f1b From 0e762b32a003dd8a9b805fb95ee7aeb3616c41e3 Mon Sep 17 00:00:00 2001 From: Timothy Warren Date: Fri, 7 Oct 2011 09:21:40 -0400 Subject: Added check for quote mark --- system/database/drivers/pdo/pdo_driver.php | 26 +++++--------------------- 1 file changed, 5 insertions(+), 21 deletions(-) diff --git a/system/database/drivers/pdo/pdo_driver.php b/system/database/drivers/pdo/pdo_driver.php index 1a84404bb..750c02e27 100644 --- a/system/database/drivers/pdo/pdo_driver.php +++ b/system/database/drivers/pdo/pdo_driver.php @@ -313,7 +313,10 @@ class CI_DB_pdo_driver extends CI_DB { $str = $this->conn_id->quote($str); //If there are duplicated quotes, trim them away - $str = substr($str, 1, -1); + if(strpos($str, "'") === 0) + { + $str = substr($str, 1, -1); + } // escape LIKE condition wildcards if ($like === TRUE) @@ -349,25 +352,7 @@ class CI_DB_pdo_driver extends CI_DB { */ function insert_id($name=NULL) { - //Convenience method for postgres insertid - if(strpos($this->hostname, 'pgsql') !== FALSE) - { - $v = $this->_version(); - - $table = func_num_args() > 0 ? func_get_arg(0) : NULL; - - if ($table == NULL && $v >= '8.1') - { - $sql='SELECT LASTVAL() as ins_id'; - } - $query = $this->query($sql); - $row = $query->row(); - return $row->ins_id; - } - else - { - return $this->conn_id->lastInsertId($name); - } + return $this->conn_id->lastInsertId($name); } // -------------------------------------------------------------------- @@ -418,7 +403,6 @@ class CI_DB_pdo_driver extends CI_DB { if ($prefix_limit !== FALSE AND $this->dbprefix != '') { - //$sql .= " LIKE '".$this->escape_like_str($this->dbprefix)."%' ".sprintf($this->_like_escape_str, $this->_like_escape_chr); return FALSE; // not currently supported } -- cgit v1.2.3-24-g4f1b From e7608b264443bb9803e580f884c44fef46d00fba Mon Sep 17 00:00:00 2001 From: Timothy Warren Date: Fri, 7 Oct 2011 09:50:05 -0400 Subject: Revert "Changed all db constructors to newer syntax, made insert_id() function more convenient for postgres on pdo driver" This reverts commit f7a8d86dbc6805a4e52964bbea76738df75b5f35. --- system/database/DB_cache.php | 2 +- system/database/DB_driver.php | 2 +- system/database/DB_forge.php | 2 +- system/database/DB_utility.php | 2 +- system/database/drivers/odbc/odbc_driver.php | 4 ++-- 5 files changed, 6 insertions(+), 6 deletions(-) diff --git a/system/database/DB_cache.php b/system/database/DB_cache.php index ad1c28d72..3bf065ca5 100644 --- a/system/database/DB_cache.php +++ b/system/database/DB_cache.php @@ -33,7 +33,7 @@ class CI_DB_Cache { * Grabs the CI super object instance so we can access it. * */ - function __construct(&$db) + function CI_DB_Cache(&$db) { // Assign the main CI object to $this->CI // and load the file helper since we use it a lot diff --git a/system/database/DB_driver.php b/system/database/DB_driver.php index d7b63b9dc..237a4fcea 100644 --- a/system/database/DB_driver.php +++ b/system/database/DB_driver.php @@ -78,7 +78,7 @@ class CI_DB_driver { * * @param array */ - function __construct($params) + function CI_DB_driver($params) { if (is_array($params)) { diff --git a/system/database/DB_forge.php b/system/database/DB_forge.php index 6bc40411b..0dd29c238 100644 --- a/system/database/DB_forge.php +++ b/system/database/DB_forge.php @@ -35,7 +35,7 @@ class CI_DB_forge { * Grabs the CI super object instance so we can access it. * */ - function __construct() + function CI_DB_forge() { // Assign the main database object to $this->db $CI =& get_instance(); diff --git a/system/database/DB_utility.php b/system/database/DB_utility.php index 52196b7ce..a5f174f0a 100644 --- a/system/database/DB_utility.php +++ b/system/database/DB_utility.php @@ -33,7 +33,7 @@ class CI_DB_utility extends CI_DB_forge { * Grabs the CI super object instance so we can access it. * */ - function __construct() + function CI_DB_utility() { // Assign the main database object to $this->db $CI =& get_instance(); diff --git a/system/database/drivers/odbc/odbc_driver.php b/system/database/drivers/odbc/odbc_driver.php index bcd7937d9..08cd27b6c 100644 --- a/system/database/drivers/odbc/odbc_driver.php +++ b/system/database/drivers/odbc/odbc_driver.php @@ -48,9 +48,9 @@ class CI_DB_odbc_driver extends CI_DB { var $_random_keyword; - function __construct($params) + function CI_DB_odbc_driver($params) { - parent::__construct($params); + parent::CI_DB_driver($params); $this->_random_keyword = ' RND('.time().')'; // database specific random keyword } -- cgit v1.2.3-24-g4f1b From 3351275b1c80c9d4ec2e6fa551c3cee3a0e47b27 Mon Sep 17 00:00:00 2001 From: Timothy Warren Date: Fri, 7 Oct 2011 09:51:49 -0400 Subject: Revert "Added check for quote mark" This reverts commit 0e762b32a003dd8a9b805fb95ee7aeb3616c41e3. --- system/database/drivers/pdo/pdo_driver.php | 26 +++++++++++++++++++++----- 1 file changed, 21 insertions(+), 5 deletions(-) diff --git a/system/database/drivers/pdo/pdo_driver.php b/system/database/drivers/pdo/pdo_driver.php index 750c02e27..1a84404bb 100644 --- a/system/database/drivers/pdo/pdo_driver.php +++ b/system/database/drivers/pdo/pdo_driver.php @@ -313,10 +313,7 @@ class CI_DB_pdo_driver extends CI_DB { $str = $this->conn_id->quote($str); //If there are duplicated quotes, trim them away - if(strpos($str, "'") === 0) - { - $str = substr($str, 1, -1); - } + $str = substr($str, 1, -1); // escape LIKE condition wildcards if ($like === TRUE) @@ -352,7 +349,25 @@ class CI_DB_pdo_driver extends CI_DB { */ function insert_id($name=NULL) { - return $this->conn_id->lastInsertId($name); + //Convenience method for postgres insertid + if(strpos($this->hostname, 'pgsql') !== FALSE) + { + $v = $this->_version(); + + $table = func_num_args() > 0 ? func_get_arg(0) : NULL; + + if ($table == NULL && $v >= '8.1') + { + $sql='SELECT LASTVAL() as ins_id'; + } + $query = $this->query($sql); + $row = $query->row(); + return $row->ins_id; + } + else + { + return $this->conn_id->lastInsertId($name); + } } // -------------------------------------------------------------------- @@ -403,6 +418,7 @@ class CI_DB_pdo_driver extends CI_DB { if ($prefix_limit !== FALSE AND $this->dbprefix != '') { + //$sql .= " LIKE '".$this->escape_like_str($this->dbprefix)."%' ".sprintf($this->_like_escape_str, $this->_like_escape_chr); return FALSE; // not currently supported } -- cgit v1.2.3-24-g4f1b From ec19332ba3791c933f2221d972ee073684b5ea3b Mon Sep 17 00:00:00 2001 From: Timothy Warren Date: Fri, 7 Oct 2011 09:53:35 -0400 Subject: Added check for quote mark --- system/database/drivers/pdo/pdo_driver.php | 7 +++++-- 1 file changed, 5 insertions(+), 2 deletions(-) diff --git a/system/database/drivers/pdo/pdo_driver.php b/system/database/drivers/pdo/pdo_driver.php index 1a84404bb..24c658a35 100644 --- a/system/database/drivers/pdo/pdo_driver.php +++ b/system/database/drivers/pdo/pdo_driver.php @@ -313,8 +313,11 @@ class CI_DB_pdo_driver extends CI_DB { $str = $this->conn_id->quote($str); //If there are duplicated quotes, trim them away - $str = substr($str, 1, -1); - + if(strpos($str, "'") === 0) + { + $str = substr($str, 1, -1); + } + // escape LIKE condition wildcards if ($like === TRUE) { -- cgit v1.2.3-24-g4f1b From d66915344f1a09c799dda935cf5c56930c044d34 Mon Sep 17 00:00:00 2001 From: Timothy Warren Date: Fri, 7 Oct 2011 10:03:01 -0400 Subject: if statment code style update --- system/database/drivers/pdo/pdo_driver.php | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) diff --git a/system/database/drivers/pdo/pdo_driver.php b/system/database/drivers/pdo/pdo_driver.php index 24c658a35..19e069b06 100644 --- a/system/database/drivers/pdo/pdo_driver.php +++ b/system/database/drivers/pdo/pdo_driver.php @@ -52,12 +52,12 @@ class CI_DB_pdo_driver extends CI_DB { parent::__construct($params); // clause and character used for LIKE escape sequences - if(strpos($this->hostname, 'mysql') !== FALSE) + if (strpos($this->hostname, 'mysql') !== FALSE) { $this->_like_escape_str = ''; $this->_like_escape_chr = ''; } - else if(strpos($this->hostname, 'odbc') !== FALSE) + else if (strpos($this->hostname, 'odbc') !== FALSE) { $this->_like_escape_str = " {escape '%s'} "; $this->_like_escape_chr = '!'; @@ -180,7 +180,7 @@ class CI_DB_pdo_driver extends CI_DB { $sql = $this->_prep_query($sql); $result_id = $this->conn_id->query($sql); - if(is_object($result_id)) + if (is_object($result_id)) { $this->affect_rows = $result_id->rowCount(); } @@ -313,7 +313,7 @@ class CI_DB_pdo_driver extends CI_DB { $str = $this->conn_id->quote($str); //If there are duplicated quotes, trim them away - if(strpos($str, "'") === 0) + if (strpos($str, "'") === 0) { $str = substr($str, 1, -1); } @@ -353,7 +353,7 @@ class CI_DB_pdo_driver extends CI_DB { function insert_id($name=NULL) { //Convenience method for postgres insertid - if(strpos($this->hostname, 'pgsql') !== FALSE) + if (strpos($this->hostname, 'pgsql') !== FALSE) { $v = $this->_version(); @@ -743,7 +743,7 @@ class CI_DB_pdo_driver extends CI_DB { */ function _limit($sql, $limit, $offset) { - if(strpos($this->hostname, 'cubrid') !== FALSE || strpos($this->hostname, 'sqlite') !== FALSE) + if (strpos($this->hostname, 'cubrid') !== FALSE || strpos($this->hostname, 'sqlite') !== FALSE) { if ($offset == 0) { -- cgit v1.2.3-24-g4f1b