Added MSSQL/SQLSRV field escaping support with QUOTE_IDENTIFIER detection (issue #706)

3 files changed, 73 insertions, 16 deletions

diff --git a/system/database/DB_driver.php b/system/database/DB_driver.php
index 39c19cdf7..f559863a5 100644
--- a/system/database/DB_driver.php
+++ b/system/database/DB_driver.php
@@ -944,24 +944,29 @@ abstract class CI_DB_driver {
 			return $item;
 		}
 
-		foreach ($this->_reserved_identifiers as $id)
+		static $preg_ec = array();
+
+		if (empty($preg_ec))
 		{
-			if (strpos($item, '.'.$id) !== FALSE)
+			if (is_array($this->_escape_char))
 			{
-				$item = str_replace('.', $this->_escape_char.'.', $item);
-
-				// remove duplicates if the user already included the escape
-				return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $this->_escape_char.$item);
+				$preg_ec = array(preg_quote($this->_escape_char[0]), preg_quote($this->_escape_char[1]));
+			}
+			else
+			{
+				$preg_ec[0] = $preg_ec[1] = preg_quote($this->_escape_char);
 			}
 		}
 
-		if (strpos($item, '.') !== FALSE)
+		foreach ($this->_reserved_identifiers as $id)
 		{
-			$item = str_replace('.', $this->_escape_char.'.'.$this->_escape_char, $item);
+			if (strpos($item, '.'.$id) !== FALSE)
+			{
+				return preg_replace('/'.$preg_ec[0].'?([^'.$preg_ec[1].'\.]+)'.$preg_ec[1].'?\./i', $preg_ec[0].'$1'.$preg_ec[1], $item);
+			}
 		}
 
-		// remove duplicates if the user already included the escape
-		return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $this->_escape_char.$item.$this->_escape_char);
+		return preg_replace('/'.$preg_ec[0].'?([^'.$preg_ec[1].'\.]+)'.$preg_ec[1].'?(\.)?/i', $preg_ec[0].'$1'.$preg_ec[1].'$2', $item);
 	}
 
 	// --------------------------------------------------------------------
diff --git a/system/database/drivers/mssql/mssql_driver.php b/system/database/drivers/mssql/mssql_driver.php
index 3eaea2e8e..87094e76e 100644
--- a/system/database/drivers/mssql/mssql_driver.php
+++ b/system/database/drivers/mssql/mssql_driver.php
@@ -43,7 +43,7 @@ class CI_DB_mssql_driver extends CI_DB {
 	public $dbdriver = 'mssql';
 
 	// The character used for escaping
-	protected $_escape_char = '';
+	protected $_escape_char = '"';
 
 	// clause and character used for LIKE escape sequences
 	protected $_like_escape_str = " ESCAPE '%s' ";
@@ -57,6 +57,17 @@ class CI_DB_mssql_driver extends CI_DB {
 	protected $_count_string = 'SELECT COUNT(*) AS ';
 	protected $_random_keyword = ' NEWID()';
 
+	// MSSQL-specific properties
+	protected $_quoted_identifier = TRUE;
+
+	/*
+	 * Constructor
+	 *
+	 * Appends the port number to the hostname, if needed.
+	 *
+	 * @param	array
+	 * @return	void
+	 */
 	public function __construct($params)
 	{
 		parent::__construct($params);
@@ -67,6 +78,8 @@ class CI_DB_mssql_driver extends CI_DB {
 		}
 	}
 
+	// --------------------------------------------------------------------
+
 	/**
 	 * Non-persistent database connection
 	 *
@@ -74,7 +87,7 @@ class CI_DB_mssql_driver extends CI_DB {
 	 */
 	public function db_connect()
 	{
-		return @mssql_connect($this->hostname, $this->username, $this->password);
+		return $this->_mssql_connect();
 	}
 
 	// --------------------------------------------------------------------
@@ -86,7 +99,35 @@ class CI_DB_mssql_driver extends CI_DB {
 	 */
 	public function db_pconnect()
 	{
-		return @mssql_pconnect($this->hostname, $this->username, $this->password);
+		return $this->_mssql_connect(TRUE);
+	}
+
+	// --------------------------------------------------------------------
+
+	/*
+	 * MSSQL Connect
+	 *
+	 * @param	bool
+	 * @return	resource
+	 */
+	protected function _mssql_connect($persistent = FALSE)
+	{
+		$conn_id = ($persistent)
+				? @mssql_pconnect($this->hostname, $this->username, $this->password)
+				: @mssql_connect($this->hostname, $this->username, $this->password);
+
+		if ( ! $conn_id)
+		{
+			return FALSE;
+		}
+
+		// Determine how identifiers are escaped
+		$query = $this->query('SELECT CASE WHEN (@@OPTIONS | 256) = @@OPTIONS THEN 1 ELSE 0 END AS qi');
+		$query = $query->row_array();
+		$this->_quoted_identifier = empty($query) ? FALSE : (bool) $query->qi;
+		$this->_escape_char = ($this->_quoted_identifier) ? '"' : array('[', ']');
+
+		return $conn_id;
 	}
 
 	// --------------------------------------------------------------------
@@ -106,7 +147,7 @@ class CI_DB_mssql_driver extends CI_DB {
 
 		// Note: The brackets are required in the event that the DB name
 		// contains reserved characters
-		if (@mssql_select_db('['.$database.']', $this->conn_id))
+		if (@mssql_select_db($this->escape_identifiers($database), $this->conn_id))
 		{
 			$this->database = $database;
 			return TRUE;
diff --git a/system/database/drivers/sqlsrv/sqlsrv_driver.php b/system/database/drivers/sqlsrv/sqlsrv_driver.php
index 74e11c3af..d2b73aee4 100644
--- a/system/database/drivers/sqlsrv/sqlsrv_driver.php
+++ b/system/database/drivers/sqlsrv/sqlsrv_driver.php
@@ -43,7 +43,7 @@ class CI_DB_sqlsrv_driver extends CI_DB {
 	public $dbdriver = 'sqlsrv';
 
 	// The character used for escaping
-	protected $_escape_char = '';
+	protected $_escape_char = '"';
 
 	// clause and character used for LIKE escape sequences
 	protected $_like_escape_str = " ESCAPE '%s' ";
@@ -57,6 +57,9 @@ class CI_DB_sqlsrv_driver extends CI_DB {
 	protected $_count_string = 'SELECT COUNT(*) AS ';
 	protected $_random_keyword = ' NEWID()';
 
+	// SQLSRV-specific properties
+	protected $_quoted_identifier = TRUE;
+
 	/**
 	 * Non-persistent database connection
 	 *
@@ -83,7 +86,15 @@ class CI_DB_sqlsrv_driver extends CI_DB {
 			unset($connection['UID'], $connection['PWD']);
 		}
 
-		return sqlsrv_connect($this->hostname, $connection);
+		$conn_id = sqlsrv_connect($this->hostname, $connection);
+
+		// Determine how identifiers are escaped
+		$query = $this->query('SELECT CASE WHEN (@@OPTIONS | 256) = @@OPTIONS THEN 1 ELSE 0 END AS qi');
+		$query = $query->row_array();
+		$this->_quoted_identifier = empty($query) ? FALSE : (bool) $query->qi;
+		$this->_escape_char = ($this->_quoted_identifier) ? '"' : array('[', ']');
+
+		return $conn_id;
 	}
 
 	// --------------------------------------------------------------------