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

4 files changed, 83 insertions, 22 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;
 	}
 
 	// --------------------------------------------------------------------
diff --git a/user_guide_src/source/changelog.rst b/user_guide_src/source/changelog.rst
index 256de9548..7a6f8fa37 100644
--- a/user_guide_src/source/changelog.rst
+++ b/user_guide_src/source/changelog.rst
@@ -88,10 +88,16 @@ Release Date: Not Released
    -  Removed protect_identifiers() and renamed internal method _protect_identifiers() to it instead - it was just an alias.
    -  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 DSN string support for CUBRID.
-   -  Added persistent connections support for CUBRID.
-   -  Added random ordering support for MSSQL, SQLSRV.
    -  Added support for SQLite3 database driver.
+   -  Improved support of the CUBRID driver, including:
+	 - Added DSN string support.
+	 - Added persistent connections support.
+	 - Improved list_databases() in :doc:`Database Utility <database/utilities>` (until now only the currently used database was returned).
+   -  Improved support of the MSSQL and SQLSRV drivers, including:
+	 - Added random ordering support.
+	 - Added support for optimize_table() in :doc:`Database Utility <database/utilities>`.
+	 - Added escaping with QUOTE_IDENTIFIER setting detection.
+	 - Added port handling support for UNIX-based systems (MSSQL driver).
    -  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>`.
@@ -104,10 +110,7 @@ Release Date: Not Released
    -  Added SQLite 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 MSSQL, SQLSRV support for optimize_table() in :doc:`Database Utility <database/utilities>`.
-   -  Improved CUBRID support for list_databases() in :doc:`Database Utility <database/utilities>` (until now only the currently used database was returned).
    -  Added unbuffered_row() method for getting a row without prefetching whole result (consume less memory).
-   -  Added port handling support for MSSQL on UNIX-based systems.
 
 -  Libraries
 
@@ -238,6 +241,7 @@ Bug fixes for 3.0
 -  Fixed a bug where the magic_quotes_runtime setting wasn't turned off for PHP 5.3 (where it is indeed deprecated, but not non-existent).
 -  Fixed a bug (#666) - :doc:`Output library <libraries/output>`'s set_content_type() method didn't set the document charset.
 -  Fixed a bug (#784, #861) - :doc:`Database Forge <database/forge>` method ``create_table()`` used to accept constraints for MSSQL/SQLSRV integer-type columns.
+-  Fixed a bug (#706) - SQLSRV/MSSSQL didn't escape field names.
 
 Version 2.1.1
 =============