class DatabaseConnection_sqlsrv in Drupal driver for SQL Server and SQL Azure 7

Hierarchy

• class \DatabaseConnection
  • class \DatabaseConnection_sqlsrv

File

sqlsrv/database.inc, line 15

Database interface code for Microsoft SQL Server.

class DatabaseConnection_sqlsrv extends DatabaseConnection {
  public $bypassQueryPreprocess = FALSE;

  /**
   * Override of DatabaseConnection::driver().
   *
   * @status tested
   */
  public function driver() {
    return 'sqlsrv';
  }

  /**
   * Override of DatabaseConnection::databaseType().
   *
   * @status tested
   */
  public function databaseType() {
    return 'sqlsrv';
  }

  /**
   * Override of DatabaseConnection::databaseType().
   *
   * @status complete
   */
  public function __construct(array $connection_options = array()) {

    // Store connection options for future reference.
    $this->connectionOptions =& $connection_options;

    // We don't need a specific PDOStatement class here, we simulate it using
    // DatabaseStatement_sqlsrv below.
    $this->statementClass = NULL;

    // This driver defaults to transaction support, except if explicitly passed FALSE.
    $this->transactionSupport = !isset($connection_options['transactions']) || $connection_options['transactions'] !== FALSE;

    // Build the DSN.
    $options = array();
    $options[] = 'Server=' . $connection_options['host'] . (!empty($connection_options['port']) ? ',' . $connection_options['port'] : '');

    // We might not have a database in the
    // connection options, for example, during
    // database creation in Install.
    if (!empty($connection_options['database'])) {
      $options[] = 'Database=' . $connection_options['database'];
    }
    $dsn = 'sqlsrv:' . implode(';', $options);

    // Allow PDO options to be overridden.
    $connection_options['pdo'] = array();

    // This PDO options are INSECURE, but will overcome the following issues:
    // (1) Duplicate placeholders
    // (2) > 2100 parameter limit
    // (3) Using expressions for group by with parameters are not detected as equal.
    // This options are not applied by default, they are just stored in the connection
    // options and applied when needed. See {Statement} class.
    // The security of parameterized queries is not in effect when you use PDO::ATTR_EMULATE_PREPARES => true.
    // Your application should ensure that the data that is bound to the parameter(s) does not contain malicious
    // Transact-SQL code.
    $connection_options['pdo'] += array(
      // We run the statements in "direct mode" because the way PDO prepares
      // statement in non-direct mode cause temporary tables to be destroyed
      // at the end of the statement.
      PDO::SQLSRV_ATTR_DIRECT_QUERY => TRUE,
      // We ask PDO to perform the placeholders replacement itself because
      // SQL Server is not able to detect duplicated placeholders in
      // complex statements.
      // E.g. This query is going to fail because SQL Server cannot
      // detect that length1 and length2 are equals.
      // SELECT SUBSTRING(title, 1, :length1)
      // FROM node
      // GROUP BY SUBSTRING(title, 1, :length2);
      // This is only going to work in PDO 3 but doesn't hurt in PDO 2.
      PDO::ATTR_EMULATE_PREPARES => TRUE,
    );

    // Launch the connection to the server.
    parent::__construct($dsn, $connection_options['username'], $connection_options['password'], $connection_options['pdo']);
    $this
      ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Fetch the name of the user-bound schema. It is the schema that SQL Server
    // will use for non-qualified tables.
    $this
      ->schema()->defaultSchema = $this
      ->schema()
      ->GetDefaultSchema();
  }

  /**
   * Override of PDO::prepare(): prepare a prefetching database statement.
   *
   * @status tested
   */
  public function prepare($query, $options = array()) {
    return new DatabaseStatement_sqlsrv($this, $query, $options);
  }

  /**
   * Temporary override of DatabaseConnection::prepareQuery().
   *
   * @todo: remove that when DatabaseConnection::prepareQuery() is fixed to call
   *   $this->prepare() and not parent::prepare().
   * @status: tested, temporary
   */
  public function prepareQuery($query) {
    $query = $this
      ->prefixTables($query);

    // Call our overriden prepare.
    return $this
      ->prepare($query);
  }

  /**
   * Internal function: prepare a query by calling PDO directly.
   *
   * This function has to be public because it is called by other parts of the
   * database layer, but do not call it directly, as you risk locking down the
   * PHP process.
   */
  public function PDOPrepare($query, array $options = array()) {
    if (!$this->bypassQueryPreprocess) {
      $query = $this
        ->preprocessQuery($query);
    }
    return parent::prepare($query, $options);
  }

  /**
   * This is the original replacement regexp from Microsoft.
   *
   * We could probably simplify it a lot because queries only contain
   * placeholders when we modify them.
   *
   * NOTE: removed 'escape' from the list, because it explodes
   * with LIKE xxx ESCAPE yyy syntax.
   */
  const RESERVED_REGEXP = '/\\G
    # Everything that follows a boundary that is not : or _.
    \\b(?<![:\\[_])(?:
      # Any reserved words, followed by a boundary that is not an opening parenthesis.
      (action|admin|alias|any|are|array|at|begin|boolean|class|commit|contains|current|data|date|day|depth|domain|external|file|full|function|get|go|host|input|language|last|less|local|map|min|module|new|no|object|old|open|operation|parameter|parameters|path|plan|prefix|proc|public|ref|result|returns|role|row|rows|rule|save|search|second|section|session|size|state|statistics|temporary|than|time|timestamp|tran|translate|translation|trim|user|value|variable|view|without)
      (?!\\()
      |
      # Or a normal word.
      ([a-z]+)
    )\\b
    |
    \\b(
      [^a-z\'"\\\\]+
    )\\b
    |
    (?=[\'"])
    (
      "  [^\\\\"] * (?: \\\\. [^\\\\"] *) * "
      |
      \' [^\\\\\']* (?: \\\\. [^\\\\\']*) * \'
    )
  /Six';
  protected function replaceReservedCallback($matches) {
    if ($matches[1] !== '') {

      // Replace reserved words.
      return '[' . $matches[1] . ']';
    }

    // Let other value passthru.
    // by the logic of the regex above, this will always be the last match.
    return end($matches);
  }
  public function quoteIdentifier($identifier) {
    return '[' . $identifier . ']';
  }
  public function escapeField($field) {
    if (strlen($field) > 0) {
      return implode('.', array_map(array(
        $this,
        'quoteIdentifier',
      ), explode('.', preg_replace('/[^A-Za-z0-9_.]+/', '', $field))));
    }
    else {
      return '';
    }
  }
  public function quoteIdentifiers($identifiers) {
    return array_map(array(
      $this,
      'quoteIdentifier',
    ), $identifiers);
  }

  /**
   * Override of DatabaseConnection::escapeLike().
   */
  public function escapeLike($string) {
    return preg_replace('/([\\[\\]%_])/', '[$1]', $string);
  }

  /**
   * Override of DatabaseConnection::queryRange().
   */
  public function queryRange($query, $from, $count, array $args = array(), array $options = array()) {
    $query = $this
      ->addRangeToQuery($query, $from, $count);
    return $this
      ->query($query, $args, $options);
  }

  /**
   * Override of DatabaseConnection::queryTemporary().
   *
   * @status tested
   */
  public function queryTemporary($query, array $args = array(), array $options = array()) {

    // Generate a new temporary table name and protect it from prefixing.
    // SQL Server requires that temporary tables to be non-qualified.
    $tablename = '#' . $this
      ->generateTemporaryTableName();
    $prefixes = $this->prefixes;
    $prefixes[$tablename] = '';
    $this
      ->setPrefix($prefixes);

    // Replace SELECT xxx FROM table by SELECT xxx INTO #table FROM table.
    $query = preg_replace('/^SELECT(.*?)FROM/i', 'SELECT$1 INTO ' . $tablename . ' FROM', $query);
    $this
      ->query($query, $args, $options);
    return $tablename;
  }

  /**
   * {@inheritdoc}
   *
   * This method is overriden to manage the insecure (EMULATE_PREPARE)
   * behaviour to prevent some compatibility issues with SQL Server.
   */
  public function query($query, array $args = array(), $options = array()) {

    // Use default values if not already set.
    $options += $this
      ->defaultOptions();
    try {

      // We allow either a pre-bound statement object or a literal string.
      // In either case, we want to end up with an executed statement object,
      // which we pass to PDOStatement::execute.
      if ($query instanceof DatabaseStatementInterface) {
        $stmt = $query;
        $stmt
          ->execute(NULL, $options);
      }
      else {
        $this
          ->expandArguments($query, $args);
        $stmt = $this
          ->prepareQuery($query);
        $insecure = isset($options['insecure']) ? $options['insecure'] : FALSE;

        // Try to detect duplicate place holders, this check's performance
        // is not a good addition to the driver, but does a good job preventing
        // duplicate placeholder errors.
        $argcount = count($args);
        if ($insecure === TRUE || $argcount >= 2100 || $argcount != substr_count($query, ':')) {
          $stmt
            ->RequireInsecure();
        }
        $stmt
          ->execute($args, $options);
      }

      // Depending on the type of query we may need to return a different value.
      // See DatabaseConnection::defaultOptions() for a description of each
      // value.
      switch ($options['return']) {
        case Database::RETURN_STATEMENT:
          return $stmt;
        case Database::RETURN_AFFECTED:
          return $stmt
            ->rowCount();
        case Database::RETURN_INSERT_ID:
          return $this
            ->lastInsertId();
        case Database::RETURN_NULL:
          return;
        default:
          throw new PDOException('Invalid return directive: ' . $options['return']);
      }
    } catch (PDOException $e) {
      if ($options['throw_exception']) {

        // Add additional debug information.
        if ($query instanceof DatabaseStatementInterface) {
          $e->query_string = $stmt
            ->getQueryString();
        }
        else {
          $e->query_string = $query;
        }
        $e->args = $args;
        throw $e;
      }
      return NULL;
    }
  }

  /**
   * {@inheritdoc}
   *
   * This method is overriden to modify the way placeholder
   * names are generated. This allows to have plain queries
   * have a higher degree of repetitivity, allowing for a possible
   * query manipulation cache.
   * https://www.drupal.org/node/2146839
   */
  protected function expandArguments(&$query, &$args) {
    $modified = FALSE;

    // If the placeholder value to insert is an array, assume that we need
    // to expand it out into a comma-delimited set of placeholders.
    foreach (array_filter($args, 'is_array') as $key => $data) {
      $new_keys = array();
      $pos = 0;
      foreach ($data as $i => $value) {

        // This assumes that there are no other placeholders that use the same
        // name.  For example, if the array placeholder is defined as :example
        // and there is already an :example_2 placeholder, this will generate
        // a duplicate key.  We do not account for that as the calling code
        // is already broken if that happens.
        $new_keys[$key . '_' . $pos] = $value;
        $pos++;
      }

      // Update the query with the new placeholders.
      // preg_replace is necessary to ensure the replacement does not affect
      // placeholders that start with the same exact text. For example, if the
      // query contains the placeholders :foo and :foobar, and :foo has an
      // array of values, using str_replace would affect both placeholders,
      // but using the following preg_replace would only affect :foo because
      // it is followed by a non-word character.
      $query = preg_replace('#' . $key . '\\b#', implode(', ', array_keys($new_keys)), $query);

      // Update the args array with the new placeholders.
      unset($args[$key]);
      $args += $new_keys;
      $modified = TRUE;
    }
    return $modified;
  }

  /**
   * Internal function: massage a query to make it compliant with SQL Server.
   */
  public function preprocessQuery($query) {

    // Force quotes around some SQL Server reserved keywords.
    if (preg_match('/^SELECT/i', $query)) {
      $query = preg_replace_callback(self::RESERVED_REGEXP, array(
        $this,
        'replaceReservedCallback',
      ), $query);
    }

    // Last chance to modify some SQL Server-specific syntax.
    $replacements = array(
      // Normalize SAVEPOINT syntax to the SQL Server one.
      '/^SAVEPOINT (.*)$/' => 'SAVE TRANSACTION $1',
      '/^ROLLBACK TO SAVEPOINT (.*)$/' => 'ROLLBACK TRANSACTION $1',
      // SQL Server doesn't need an explicit RELEASE SAVEPOINT.
      // Run a non-operaiton query to avoid a fatal error
      // when no query is runned.
      '/^RELEASE SAVEPOINT (.*)$/' => 'SELECT 1 /* $0 */',
    );
    $query = preg_replace(array_keys($replacements), $replacements, $query);

    // Add prefixes to Drupal-specific functions.
    $functions = $this
      ->schema()
      ->DrupalSpecificFunctions();
    foreach ($functions as $function) {
      $query = preg_replace('/\\b(?<![:.])(' . preg_quote($function) . ')\\(/i', $this
        ->schema()->defaultSchema . '.$1(', $query);
    }
    $replacements = array(
      'LENGTH' => 'LEN',
      'POW' => 'POWER',
    );
    foreach ($replacements as $function => $replacement) {
      $query = preg_replace('/\\b(?<![:.])(' . preg_quote($function) . ')\\(/i', $replacement . '(', $query);
    }

    // Replace the ANSI concatenation operator with SQL Server poor one.
    $query = preg_replace('/\\|\\|/', '+', $query);
    return $query;
  }

  /**
   * Internal function: add range options to a query.
   *
   * This cannot be set protected because it is used in other parts of the
   * database engine.
   *
   * @status tested
   */
  public function addRangeToQuery($query, $from, $count) {
    if ($from == 0) {

      // Easy case: just use a TOP query if we don't have to skip any rows.
      $query = preg_replace('/^\\s*SELECT(\\s*DISTINCT)?/Dsi', 'SELECT$1 TOP(' . $count . ')', $query);
    }
    else {

      // More complex case: use a TOP query to retrieve $from + $count rows, and
      // filter out the first $from rows using a window function.
      $query = preg_replace('/^\\s*SELECT(\\s*DISTINCT)?/Dsi', 'SELECT$1 TOP(' . ($from + $count) . ') ', $query);
      $query = '
        SELECT * FROM (
          SELECT sub2.*, ROW_NUMBER() OVER(ORDER BY sub2.__line2) AS __line3 FROM (
            SELECT 1 AS __line2, sub1.* FROM (' . $query . ') AS sub1
          ) as sub2
        ) AS sub3
        WHERE __line3 BETWEEN ' . ($from + 1) . ' AND ' . ($from + $count);
    }
    return $query;
  }
  public function mapConditionOperator($operator) {

    // SQL Server doesn't need special escaping for the \ character in a string
    // literal, because it uses '' to escape the single quote, not \'.
    static $specials = array(
      'LIKE' => array(),
      'NOT LIKE' => array(),
    );
    return isset($specials[$operator]) ? $specials[$operator] : NULL;
  }

  /**
   * Override of DatabaseConnection::nextId().
   *
   * @status tested
   */
  public function nextId($existing = 0) {

    // If an exiting value is passed, for its insertion into the sequence table.
    if ($existing > 0) {
      try {
        $this
          ->query('SET IDENTITY_INSERT {sequences} ON; INSERT INTO {sequences} (value) VALUES(:existing); SET IDENTITY_INSERT {sequences} OFF', array(
          ':existing' => $existing,
        ));
      } catch (Exception $e) {

        // Doesn't matter if this fails, it just means that this value is already
        // present in the table.
      }
    }
    return $this
      ->query('INSERT INTO {sequences} DEFAULT VALUES', array(), array(
      'return' => Database::RETURN_INSERT_ID,
    ));
  }

  /**
   * Override DatabaseConnection::escapeTable().
   *
   * @status needswork
   */
  public function escapeTable($table) {

    // Rescue the # prefix from the escaping.
    return ($table[0] == '#' ? '#' : '') . preg_replace('/[^A-Za-z0-9_.]+/', '', $table);
  }

}

Members

Name	Modifiers	Type	Description	Overrides
DatabaseConnection::$connection	protected	property	The actual PDO connection.
DatabaseConnection::$connectionOptions	protected	property	The connection information for this connection object.
DatabaseConnection::$driverClasses	protected	property	Index of what driver-specific class to use for various operations.
DatabaseConnection::$escapedAliases	protected	property	List of escaped aliases names, keyed by unescaped aliases.
DatabaseConnection::$escapedNames	protected	property	List of escaped database, table, and field names, keyed by unescaped names.
DatabaseConnection::$key	protected	property	The key representing this connection.
DatabaseConnection::$logger	protected	property	The current database logging object for this connection.
DatabaseConnection::$prefixes	protected	property	The prefixes used by this database connection.
DatabaseConnection::$prefixReplace	protected	property	List of replacement values for use in prefixTables().
DatabaseConnection::$prefixSearch	protected	property	List of search values for use in prefixTables().
DatabaseConnection::$schema	protected	property	The schema object for this connection.
DatabaseConnection::$statementClass	protected	property	The name of the Statement class for this connection.
DatabaseConnection::$target	protected	property	The database target this connection is for.
DatabaseConnection::$temporaryNameIndex	protected	property	An index used to generate unique temporary table names.
DatabaseConnection::$transactionalDDLSupport	protected	property	Whether this database connection supports transactional DDL.
DatabaseConnection::$transactionLayers	protected	property	Tracks the number of "layers" of transactions currently active.
DatabaseConnection::$transactionSupport	protected	property	Whether this database connection supports transactions.
DatabaseConnection::$unprefixedTablesMap	protected	property	List of un-prefixed table names, keyed by prefixed table names.
DatabaseConnection::commit	public	function	Throws an exception to deny direct access to transaction commits.
DatabaseConnection::defaultOptions	protected	function	Returns the default query options for any given query.
DatabaseConnection::delete	public	function	Prepares and returns a DELETE query object.
DatabaseConnection::destroy	public	function	Destroys this Connection object.
DatabaseConnection::escapeAlias	public	function	Escapes an alias name string.	1
DatabaseConnection::filterComment	protected	function	Sanitize a query comment string.
DatabaseConnection::generateTemporaryTableName	protected	function	Generates a temporary table name.
DatabaseConnection::getConnectionOptions	public	function	Returns the connection information for this connection object.
DatabaseConnection::getDriverClass	public	function	Gets the driver-specific override class if any for the specified class.
DatabaseConnection::getKey	public	function	Returns the key this connection is associated with.
DatabaseConnection::getLogger	public	function	Gets the current logging object for this connection.
DatabaseConnection::getTarget	public	function	Returns the target this connection is associated with.
DatabaseConnection::getUnprefixedTablesMap	public	function	Gets a list of individually prefixed table names.
DatabaseConnection::insert	public	function	Prepares and returns an INSERT query object.
DatabaseConnection::inTransaction	public	function	Determines if there is an active transaction open.
DatabaseConnection::makeComment	public	function	Flatten an array of query comments into a single comment string.
DatabaseConnection::makeSequenceName	public	function	Creates the appropriate sequence name for a given table and serial field.
DatabaseConnection::merge	public	function	Prepares and returns a MERGE query object.
DatabaseConnection::popCommittableTransactions	protected	function	Internal function: commit all the transaction layers that can commit.	1
DatabaseConnection::popTransaction	public	function	Decreases the depth of transaction nesting.	1
DatabaseConnection::prefixTables	public	function	Appends a database prefix to all tables in a query.
DatabaseConnection::pushTransaction	public	function	Increases the depth of transaction nesting.	1
DatabaseConnection::rollback	public	function	Rolls back the transaction entirely or to a named savepoint.	2
DatabaseConnection::schema	public	function	Returns a DatabaseSchema object for manipulating the schema.
DatabaseConnection::select	public	function	Prepares and returns a SELECT query object.
DatabaseConnection::setKey	public	function	Tells this connection object what its key is.
DatabaseConnection::setLogger	public	function	Associates a logging object with this connection.
DatabaseConnection::setPrefix	protected	function	Set the list of prefixes used by this database connection.	1
DatabaseConnection::setTarget	public	function	Tells this connection object what its target value is.
DatabaseConnection::startTransaction	public	function	Returns a new DatabaseTransaction object on this connection.
DatabaseConnection::supportsTransactionalDDL	public	function	Determines if this driver supports transactional DDL.
DatabaseConnection::supportsTransactions	public	function	Determines if this driver supports transactions.
DatabaseConnection::tablePrefix	public	function	Find the prefix for a table.
DatabaseConnection::transactionDepth	public	function	Determines current transaction depth.
DatabaseConnection::truncate	public	function	Prepares and returns a TRUNCATE query object.
DatabaseConnection::update	public	function	Prepares and returns an UPDATE query object.
DatabaseConnection::utf8mb4IsActive	public	function	Checks whether utf8mb4 support is currently active.	3
DatabaseConnection::utf8mb4IsConfigurable	public	function	Checks whether utf8mb4 support is configurable in settings.php.	1
DatabaseConnection::utf8mb4IsSupported	public	function	Checks whether utf8mb4 support is available on the current database system.	3
DatabaseConnection::version	public	function	Returns the version of the database server.
DatabaseConnection::__call	public	function	Proxy possible direct calls to the \PDO methods.
DatabaseConnection_sqlsrv::$bypassQueryPreprocess	public	property
DatabaseConnection_sqlsrv::addRangeToQuery	public	function	Internal function: add range options to a query.
DatabaseConnection_sqlsrv::databaseType	public	function	Override of DatabaseConnection::databaseType(). Overrides DatabaseConnection::databaseType
DatabaseConnection_sqlsrv::driver	public	function	Override of DatabaseConnection::driver(). Overrides DatabaseConnection::driver
DatabaseConnection_sqlsrv::escapeField	public	function	Escapes a field name string. Overrides DatabaseConnection::escapeField
DatabaseConnection_sqlsrv::escapeLike	public	function	Override of DatabaseConnection::escapeLike(). Overrides DatabaseConnection::escapeLike
DatabaseConnection_sqlsrv::escapeTable	public	function	Override DatabaseConnection::escapeTable(). Overrides DatabaseConnection::escapeTable
DatabaseConnection_sqlsrv::expandArguments	protected	function	This method is overriden to modify the way placeholder names are generated. This allows to have plain queries have a higher degree of repetitivity, allowing for a possible query manipulation cache. https://www.drupal.org/node/2146839 Overrides DatabaseConnection::expandArguments
DatabaseConnection_sqlsrv::mapConditionOperator	public	function	Gets any special processing requirements for the condition operator. Overrides DatabaseConnection::mapConditionOperator
DatabaseConnection_sqlsrv::nextId	public	function	Override of DatabaseConnection::nextId(). Overrides DatabaseConnection::nextId
DatabaseConnection_sqlsrv::PDOPrepare	public	function	Internal function: prepare a query by calling PDO directly.
DatabaseConnection_sqlsrv::prepare	public	function	Override of PDO::prepare(): prepare a prefetching database statement.
DatabaseConnection_sqlsrv::prepareQuery	public	function	Temporary override of DatabaseConnection::prepareQuery(). Overrides DatabaseConnection::prepareQuery
DatabaseConnection_sqlsrv::preprocessQuery	public	function	Internal function: massage a query to make it compliant with SQL Server.
DatabaseConnection_sqlsrv::query	public	function	This method is overriden to manage the insecure (EMULATE_PREPARE) behaviour to prevent some compatibility issues with SQL Server. Overrides DatabaseConnection::query
DatabaseConnection_sqlsrv::queryRange	public	function	Override of DatabaseConnection::queryRange(). Overrides DatabaseConnection::queryRange
DatabaseConnection_sqlsrv::queryTemporary	public	function	Override of DatabaseConnection::queryTemporary(). Overrides DatabaseConnection::queryTemporary
DatabaseConnection_sqlsrv::quoteIdentifier	public	function
DatabaseConnection_sqlsrv::quoteIdentifiers	public	function
DatabaseConnection_sqlsrv::replaceReservedCallback	protected	function
DatabaseConnection_sqlsrv::RESERVED_REGEXP		constant	This is the original replacement regexp from Microsoft.
DatabaseConnection_sqlsrv::__construct	public	function	Override of DatabaseConnection::databaseType(). Overrides DatabaseConnection::__construct