public function DatabaseConnection_sqlsrv::prepareQuery in Drupal driver for SQL Server and SQL Azure 7.2

Same name and namespace in other branches

1. 7.3 sqlsrv/database.inc \DatabaseConnection_sqlsrv::prepareQuery()
2. 7 sqlsrv/database.inc \DatabaseConnection_sqlsrv::prepareQuery()

Temporary override of DatabaseConnection::prepareQuery().

@todo: remove that when DatabaseConnection::prepareQuery() is fixed to call $this->prepare() and not parent::prepare(). @status: tested, temporary

Parameters

mixed $query:

mixed $insecure:

Return value

PDOStatement

Overrides DatabaseConnection::prepareQuery

2 calls to DatabaseConnection_sqlsrv::prepareQuery()

DatabaseConnection_sqlsrv::query in sqlsrv/database.inc

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

DatabaseConnection_sqlsrv::query_direct in sqlsrv/database.inc

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

sqlsrv/database.inc, line 170

Database interface code for Microsoft SQL Server.

Class

DatabaseConnection_sqlsrv

Summary of DatabaseConnection_sqlsrv

Code

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

  // 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->statementCaching,
    'direct_query' => $this->directQuery,
    'prefix_tables' => 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 = array();

  // Set insecure options if requested so. There is no global
  // setting for this, only at the statement level.
  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 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 we are not going to cache prepared statements, always use direct queries!
  // There is no point in preparing the statement if you are not going to cache it.
  if (!$this->statementCaching || $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

  // Call our overriden prepare.
  $stmt = $this
    ->PDOPrepare($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;
}