sqlsrv.select.test in Drupal driver for SQL Server and SQL Azure 7

Support tests for SQL Server.

File

tests/sqlsrv.select.test

<?php

/**
 * @file
 * Support tests for SQL Server.
 */
class SqlServerSelectTest extends DatabaseTestCase {

  /**
   * {inheritdoc}
   */
  public static function getInfo() {
    return array(
      'name' => 'Select tests',
      'description' => 'Extended tests for SELECT handling.',
      'group' => 'Database (SQL Server)',
    );
  }

  /**
   * Test group by expansion.
   */
  public function testGroupByExpansion() {

    // By ANSI SQL, GROUP BY columns cannot use aliases. Test that the
    // driver expands the aliases properly.
    $query = db_select('test_task', 't');
    $count_field = $query
      ->addExpression('COUNT(task)', 'num');
    $task_field = $query
      ->addExpression('CONCAT(:prefix, t.task)', 'task', array(
      ':prefix' => 'Task: ',
    ));
    $query
      ->orderBy($count_field);
    $query
      ->groupBy($task_field);
    $result = $query
      ->execute();
    $num_records = 0;
    $last_count = 0;
    $records = array();
    foreach ($result as $record) {
      $num_records++;
      $this
        ->assertTrue($record->{$count_field} >= $last_count, t('Results returned in correct order.'));
      $last_count = $record->{$count_field};
      $records[$record->{$task_field}] = $record->{$count_field};
    }
    $correct_results = array(
      'Task: eat' => 1,
      'Task: sleep' => 2,
      'Task: code' => 1,
      'Task: found new band' => 1,
      'Task: perform at superbowl' => 1,
    );
    foreach ($correct_results as $task => $count) {
      $this
        ->assertEqual($records[$task], $count, t("Correct number of '@task' records found.", array(
        '@task' => $task,
      )));
    }
    $this
      ->assertEqual($num_records, 6, t('Returned the correct number of total rows.'));
  }

  /**
   * Test the 2100 parameter limit per query.
   */
  public function testParameterLimit() {
    $values = array();
    for ($x = 0; $x < 2200; $x++) {
      $values[] = uniqid($x, TRUE);
    }
    $query = db_select('test_task', 't');
    $query
      ->addExpression('COUNT(task)', 'num');
    $query
      ->where('t.task IN (:data)', array(
      ':data' => $values,
    ));
    $result = NULL;

    // If > 2100 we can get SQL Exception! The driver must handle that.
    try {
      $result = $query
        ->execute()
        ->fetchField();
    } catch (\Exception $err) {
    }
    $this
      ->assertEqual($result, 0, 'Returned the correct number of total rows.');
  }

  /**
   * Although per official documentation you cannot send
   * duplicate placeholders in same query, this works in mySQL
   * and is present in some queries, even in core, wich have not
   * gotten enough attention.
   */
  public function testDuplicatePlaceholders() {
    $query = db_select('test_task', 't');
    $query
      ->addExpression('COUNT(task)', 'num');
    $query
      ->where('t.task IN (:data0, :data0)', array(
      ':data0' => 'sleep',
    ));
    $result = NULL;

    // If > 2100 we can get SQL Exception! The driver must handle that.
    try {
      $result = $query
        ->execute()
        ->fetchField();
    } catch (\Exception $err) {
    }
    $this
      ->assertEqual($result, 2, 'Returned the correct number of total rows.');
  }

  /**
   * Test for weird key names
   * in array arguments.
   */
  public function testBadKeysInArrayArguments() {
    $params[':nids'] = array(
      'uid1' => -9,
      'What a bad placeholder name, why should we care?' => -6,
    );
    $result = NULL;
    try {

      // The regular expandArguments implementation will fail to
      // properly expand the associative array with weird keys, OH, and actually
      // you can perform some SQL Injection through the array keys.
      $result = db_query('SELECT COUNT(*) FROM USERS WHERE USERS.UID IN (:nids)', $params)
        ->execute()
        ->fetchField();
    } catch (\Exception $err) {

      // Regular drupal will fail with
      // SQLSTATE[IMSSP]: An error occurred substituting the named parameters.
      // https://www.drupal.org/node/2146839
    }

    // User ID's are negative, so this should return 0 matches.
    $this
      ->assertEqual($result, 0, 'Returned the correct number of total rows.');
  }

  /**
   * Test LIKE statement wildcards are properly escaped.
   */
  public function testEscapeLike() {

    // Test expected escaped characters
    $string = 't[e%s]t_\\';
    $expected = 't[[]e[%]s[]]t[_]\\';
    $actual = db_like($string);
    $this
      ->assertEqual($actual, $expected, 'Properly escaped LIKE statement wildcards.');
    db_insert('test_task')
      ->fields(array(
      'task' => 'T\\est',
    ))
      ->execute();
    $query = db_select('test_task', 't');
    $query
      ->fields('t');
    $query
      ->condition('t.task', db_like('T\\est'), 'LIKE');
    $result = $query
      ->execute()
      ->fetchAll();
    $this
      ->assertEqual(count($result), 1, t('db_select returned the correct number of total rows.'));
    db_insert('test_task')
      ->fields(array(
      'task' => 'T\'est',
    ))
      ->execute();
    $query = db_select('test_task', 't');
    $query
      ->fields('t');
    $query
      ->condition('t.task', db_like('T\'est'), 'LIKE');
    $result = $query
      ->execute()
      ->fetchAll();
    $this
      ->assertEqual(count($result), 1, t('db_select returned the correct number of total rows.'));

    // db_select: Test unescaped wildcard.
    $query = db_select('test_task', 't');
    $query
      ->condition('t.task', '[s]leep', 'LIKE');
    $query
      ->fields('t');
    $result = $query
      ->execute()
      ->fetchAll();
    $this
      ->assertEqual(count($result), 2, t('db_select returned the correct number of total rows.'));

    // db_select: Test unescaped wildcard.
    $query = db_select('test_task', 't');
    $query
      ->condition('t.task', '[s]leep', 'LIKE');
    $query
      ->fields('t');
    $result = $query
      ->execute()
      ->fetchAll();
    $this
      ->assertEqual(count($result), 2, t('db_select returned the correct number of total rows.'));

    // db_select: Test escaped wildcard.
    $query = db_select('test_task', 't');
    $query
      ->condition('t.task', db_like('[s]leep'), 'LIKE');
    $query
      ->fields('t');
    $result = $query
      ->execute()
      ->fetchAll();
    $this
      ->assertEqual(count($result), 0, t('db_select returned the correct number of total rows.'));

    // db_select->where: Test unescaped wildcard.
    $query = db_select('test_task', 't');
    $query
      ->where('t.task LIKE :task', array(
      ':task' => '[s]leep',
    ));
    $query
      ->fields('t');
    $result = $query
      ->execute()
      ->fetchAll();
    $this
      ->assertEqual(count($result), 2, t('db_select returned the correct number of total rows.'));

    // db_select->where: Test escaped wildcard.
    $query = db_select('test_task', 't');
    $query
      ->where('t.task LIKE :task', array(
      ':task' => db_like('[s]leep'),
    ));
    $query
      ->fields('t');
    $result = $query
      ->execute()
      ->fetchAll();
    $this
      ->assertEqual(count($result), 0, t('db_select returned the correct number of total rows.'));

    // db_query: Test unescaped wildcard.
    $query = db_query('SELECT COUNT(*) FROM {test_task} WHERE task LIKE :task', array(
      ':task' => '[s]leep',
    ));
    $result = $query
      ->fetchField();
    $this
      ->assertEqual($result, 2, t('db_query returned the correct number of total rows.'));

    // db_query: Test escaped wildcard.
    $query = db_query('SELECT COUNT(*) FROM {test_task} WHERE task LIKE :task', array(
      ':task' => db_like('[s]leep'),
    ));
    $result = $query
      ->fetchField();
    $this
      ->assertEqual($result, 0, t('db_query returned the correct number of total rows.'));
  }

}

Classes

Name	Description
SqlServerSelectTest	@file Support tests for SQL Server.