From 082ee2b054b0d61ac02fea3deb704290e8d6021a Mon Sep 17 00:00:00 2001
From: Andrey Andreev <narf@bofh.bg>
Date: Fri, 8 Jun 2012 15:26:34 +0300
Subject: Added MSSQL/SQLSRV field escaping support with QUOTE_IDENTIFIER
 detection (issue #706)

---
 system/database/drivers/mssql/mssql_driver.php   | 49 ++++++++++++++++++++++--
 system/database/drivers/sqlsrv/sqlsrv_driver.php | 15 +++++++-
 2 files changed, 58 insertions(+), 6 deletions(-)

(limited to 'system/database/drivers')

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;
 	}
 
 	// --------------------------------------------------------------------
-- 
cgit v1.2.3-24-g4f1b