public function SqlsrvTest::testEscapeLike in Drupal driver for SQL Server and SQL Azure 8.2

Same name and namespace in other branches

1. 4.2.x tests/src/Kernel/SqlsrvTest.php \Drupal\Tests\sqlsrv\Kernel\SqlsrvTest::testEscapeLike()
2. 3.0.x tests/src/Kernel/SqlsrvTest.php \Drupal\Tests\sqlsrv\Kernel\SqlsrvTest::testEscapeLike()
3. 3.1.x tests/src/Kernel/SqlsrvTest.php \Drupal\Tests\sqlsrv\Kernel\SqlsrvTest::testEscapeLike()
4. 4.0.x tests/src/Kernel/SqlsrvTest.php \Drupal\Tests\sqlsrv\Kernel\SqlsrvTest::testEscapeLike()
5. 4.1.x tests/src/Kernel/SqlsrvTest.php \Drupal\Tests\sqlsrv\Kernel\SqlsrvTest::testEscapeLike()

Test LIKE statement wildcards are properly escaped.

File

tests/src/Kernel/SqlsrvTest.php, line 181

Class

SqlsrvTest

Test behavior that is unique to the Sql Server Driver.

Namespace

Drupal\Tests\sqlsrv\Kernel

Code

public function testEscapeLike() {

  // Test expected escaped characters.
  $string = 't[e%s]t_\\';
  $escaped_string = $this->connection
    ->escapeLike($string);
  $this
    ->assertEqual($escaped_string, 't[e\\%s]t\\_\\\\', 'Properly escaped string with backslashes');
  $query = $this->connection
    ->select('test_task', 't');
  $condition = new Condition('AND');
  $condition
    ->condition('task', $escaped_string, 'LIKE');
  $condition
    ->compile($this->connection, $query);
  $arguments = $condition
    ->conditions();
  $argument = $arguments[0];
  $expected = 't[[]e[%]s]t[_]\\';
  $actual = $argument['value'];
  $this
    ->assertEqual($actual, $expected, 'Properly escaped LIKE statement wildcards.');
  $this->connection
    ->insert('test_task')
    ->fields([
    'task' => 'T\\est',
  ])
    ->execute();
  $query = $this->connection
    ->select('test_task', 't');
  $query
    ->fields('t');
  $query
    ->condition('t.task', $this->connection
    ->escapeLike('T\\est'), 'LIKE');
  $result = $query
    ->execute()
    ->fetchAll();
  $this
    ->assertEqual(count($result), 1, t('db_select returned the correct number of total rows.'));
  $this->connection
    ->insert('test_task')
    ->fields([
    'task' => 'T\'est',
  ])
    ->execute();
  $query = $this->connection
    ->select('test_task', 't');
  $query
    ->fields('t');
  $query
    ->condition('t.task', $this->connection
    ->escapeLike('T\'est'), 'LIKE');
  $result = $query
    ->execute()
    ->fetchAll();
  $this
    ->assertEqual(count($result), 1, t('db_select returned the correct number of total rows.'));

  // Using the condition function requires that only % and _ can be used as
  // wildcards.
  // select->condition: Test unescaped wildcard.
  $query = $this->connection
    ->select('test_task', 't');
  $query
    ->condition('t.task', '_leep', 'LIKE');
  $query
    ->fields('t');
  $result = $query
    ->execute()
    ->fetchAll();
  $this
    ->assertEqual(count($result), 2, t('db_select returned the correct number of total rows.'));

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

  // Using the where function requires that database-specific notation be
  // used. This means we can use the SQL Server bracket notation, but these
  // queries will not be valid on other databases.
  // select->where: Test unescaped wildcard.
  $query = $this->connection
    ->select('test_task', 't');
  $query
    ->where('t.task LIKE :task', [
    ':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.'));

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

  // Using a static query also allows us to use database-specific syntax.
  // Again, queries may not run on other databases.
  // query: Test unescaped wildcard.
  $query = $this->connection
    ->query('SELECT COUNT(*) FROM {test_task} WHERE task LIKE :task', [
    ':task' => '[s]leep',
  ]);
  $result = $query
    ->fetchField();
  $this
    ->assertEqual($result, 2, t('db_query returned the correct number of total rows.'));

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