public function Connection::prepareQuery in Drupal driver for SQL Server and SQL Azure 8.2

Same name and namespace in other branches

1. 8 drivers/lib/Drupal/Driver/Database/sqlsrv/Connection.php \Drupal\Driver\Database\sqlsrv\Connection::prepareQuery()
2. 3.0.x drivers/lib/Drupal/Driver/Database/sqlsrv/Connection.php \Drupal\Driver\Database\sqlsrv\Connection::prepareQuery()

Internal prepare a query.

Overrides Connection::prepareQuery

2 calls to Connection::prepareQuery()

Connection::query in drivers/lib/Drupal/Driver/Database/sqlsrv/Connection.php

This method is overriden to manage the insecure (EMULATE_PREPARE) behaviour to prevent some compatibility issues with SQL Server.

Connection::query_direct in drivers/lib/Drupal/Driver/Database/sqlsrv/Connection.php

Like query but with no insecure detection or query preprocessing. The caller is sure that his query is MS SQL compatible! Used internally from the schema class, but could be called from anywhere.

File

drivers/lib/Drupal/Driver/Database/sqlsrv/Connection.php, line 178

Definition of Drupal\Driver\Database\sqlsrv\Connection

Class

Connection

Temporary tables: temporary table support is done by means of global temporary tables (#) to avoid the use of DIRECT QUERIES. You can enable and disable the use of direct queries with $this->driver_settings->defaultDirectQuery =…

Namespace

Drupal\Driver\Database\sqlsrv

Code

public function prepareQuery($query, array $options = []) {

  // Preprocess the query.
  $bypass = isset($options['bypass_query_preprocess']) && $options['bypass_query_preprocess'] == true ? true : false;
  if (!$bypass) {
    $query = $this
      ->preprocessQuery($query);
  }

  // Merge default statement options. These options are
  // only specific for this preparation and will only override
  // the global configuration if set to different than NULL.
  $options = array_merge(array(
    'insecure' => false,
    'statement_caching' => $this->driver_settings
      ->GetStatementCachingMode(),
    'direct_query' => $this->driver_settings
      ->GetDefaultDirectQueries(),
    'prefix_tables' => true,
    'integrityretry' => false,
    'resilientretry' => true,
  ), $options);

  // Prefix tables. There is no global setting for this.
  if ($options['prefix_tables'] !== false) {
    $query = $this
      ->prefixTables($query);
  }

  // The statement caching settings only affect the storage
  // in the cache, but if a statement is already available
  // why not reuse it!
  if (isset($this->statement_cache[$query])) {
    return $this->statement_cache[$query];
  }

  #region PDO Options
  $pdo_options = [];

  // Set insecure options if requested so.
  if ($options['insecure'] === true) {

    // We have to log this, prepared statements are a security RISK.
    // watchdog('SQL Server Driver', 'An insecure query has been executed against the database. This is not critical, but worth looking into: %query', array('%query' => $query));
    // These are defined in class Connection.
    // 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.
    // 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.
    // 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.
    // Never use this when you need special column binding.
    // THIS ONLY WORKS IF SET AT THE STATEMENT LEVEL.
    $pdo_options[PDO::ATTR_EMULATE_PREPARES] = true;
  }

  // We need this behaviour to make UPSERT and MERGE more robust.
  if ($options['integrityretry'] == true) {
    $pdo_options[\Drupal\Driver\Database\sqlsrv\PDO\Connection::PDO_RETRYONINTEGRITYVIOLATION] = true;
  }
  if ($options['resilientretry'] == true) {
    $pdo_options[\Drupal\Driver\Database\sqlsrv\PDO\Connection::PDO_RESILIENTRETRY] = true;
  }

  // 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.
  // If you are using the PDO_SQLSRV driver and you want to execute a query that
  // changes a database setting (e.g. SET NOCOUNT ON), use the PDO::query method with
  // the PDO::SQLSRV_ATTR_DIRECT_QUERY attribute.
  // http://blogs.iis.net/bswan/archive/2010/12/09/how-to-change-database-settings-with-the-pdo-sqlsrv-driver.aspx
  // If a query requires the context that was set in a previous query,
  // you should execute your queries with PDO::SQLSRV_ATTR_DIRECT_QUERY set to True.
  // For example, if you use temporary tables in your queries, PDO::SQLSRV_ATTR_DIRECT_QUERY must be set
  // to True.
  if ($this->driver_settings
    ->GetStatementCachingMode() != 'always' || $options['direct_query'] == true) {
    $pdo_options[PDO::SQLSRV_ATTR_DIRECT_QUERY] = true;
  }

  // It creates a cursor for the query, which allows you to iterate over the result set
  // without fetching the whole result at once. A scrollable cursor, specifically, is one that allows
  // iterating backwards.
  // https://msdn.microsoft.com/en-us/library/hh487158%28v=sql.105%29.aspx
  $pdo_options[PDO::ATTR_CURSOR] = PDO::CURSOR_SCROLL;

  // Lets you access rows in any order. Creates a client-side cursor query.
  $pdo_options[PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE] = PDO::SQLSRV_CURSOR_BUFFERED;

  #endregion
  if ($this->driver_settings
    ->GetAppendCallstackComment()) {
    $query = $this
      ->addDebugInfoToQuery($query);
  }

  // Call our overriden prepare.
  $stmt = $this->connection
    ->prepare($query, $pdo_options);

  // If statement caching is enabled, store current statement for reuse.
  if ($options['statement_caching'] === true) {
    $this->statement_cache[$query] = $stmt;
  }
  return $stmt;
}