3 namespace Drupal\KernelTests\Core\Database;
4 use Drupal\Core\Database\InvalidQueryException;
5 use Drupal\Core\Database\Database;
8 * Tests the Select query builder.
12 class SelectTest extends DatabaseTestBase {
15 * Tests rudimentary SELECT statements.
17 public function testSimpleSelect() {
18 $query = db_select('test');
19 $query->addField('test', 'name');
20 $query->addField('test', 'age', 'age');
21 $num_records = $query->countQuery()->execute()->fetchField();
23 $this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
27 * Tests rudimentary SELECT statement with a COMMENT.
29 public function testSimpleComment() {
30 $query = db_select('test')->comment('Testing query comments');
31 $query->addField('test', 'name');
32 $query->addField('test', 'age', 'age');
33 $result = $query->execute();
35 $records = $result->fetchAll();
37 $query = (string) $query;
38 $expected = "/* Testing query comments */";
40 $this->assertEqual(count($records), 4, 'Returned the correct number of rows.');
41 $this->assertNotIdentical(FALSE, strpos($query, $expected), 'The flattened query contains the comment string.');
45 * Tests query COMMENT system against vulnerabilities.
47 public function testVulnerableComment() {
48 $query = db_select('test')->comment('Testing query comments */ SELECT nid FROM {node}; --');
49 $query->addField('test', 'name');
50 $query->addField('test', 'age', 'age');
51 $result = $query->execute();
53 $records = $result->fetchAll();
55 $query = (string) $query;
56 $expected = "/* Testing query comments * / SELECT nid FROM {node}. -- */ SELECT test.name AS name, test.age AS age\nFROM \n{test} test";
58 $this->assertEqual(count($records), 4, 'Returned the correct number of rows.');
59 $this->assertNotIdentical(FALSE, strpos($query, $expected), 'The flattened query contains the sanitised comment string.');
61 $connection = Database::getConnection();
62 foreach ($this->makeCommentsProvider() as $test_set) {
63 list($expected, $comments) = $test_set;
64 $this->assertEqual($expected, $connection->makeComment($comments));
69 * Provides expected and input values for testVulnerableComment().
71 public function makeCommentsProvider() {
77 // Try and close the comment early.
79 '/* Exploit * / DROP TABLE node. -- */ ',
80 ['Exploit */ DROP TABLE node; --'],
82 // Variations on comment closing.
84 '/* Exploit * / * / DROP TABLE node. -- */ ',
85 ['Exploit */*/ DROP TABLE node; --'],
88 '/* Exploit * * // DROP TABLE node. -- */ ',
89 ['Exploit **// DROP TABLE node; --'],
91 // Try closing the comment in the second string which is appended.
93 '/* Exploit * / DROP TABLE node. --. Another try * / DROP TABLE node. -- */ ',
94 ['Exploit */ DROP TABLE node; --', 'Another try */ DROP TABLE node; --'],
100 * Tests basic conditionals on SELECT statements.
102 public function testSimpleSelectConditional() {
103 $query = db_select('test');
104 $name_field = $query->addField('test', 'name');
105 $age_field = $query->addField('test', 'age', 'age');
106 $query->condition('age', 27);
107 $result = $query->execute();
109 // Check that the aliases are being created the way we want.
110 $this->assertEqual($name_field, 'name', 'Name field alias is correct.');
111 $this->assertEqual($age_field, 'age', 'Age field alias is correct.');
113 // Ensure that we got the right record.
114 $record = $result->fetch();
115 $this->assertEqual($record->$name_field, 'George', 'Fetched name is correct.');
116 $this->assertEqual($record->$age_field, 27, 'Fetched age is correct.');
120 * Tests SELECT statements with expressions.
122 public function testSimpleSelectExpression() {
123 $query = db_select('test');
124 $name_field = $query->addField('test', 'name');
125 $age_field = $query->addExpression("age*2", 'double_age');
126 $query->condition('age', 27);
127 $result = $query->execute();
129 // Check that the aliases are being created the way we want.
130 $this->assertEqual($name_field, 'name', 'Name field alias is correct.');
131 $this->assertEqual($age_field, 'double_age', 'Age field alias is correct.');
133 // Ensure that we got the right record.
134 $record = $result->fetch();
135 $this->assertEqual($record->$name_field, 'George', 'Fetched name is correct.');
136 $this->assertEqual($record->$age_field, 27 * 2, 'Fetched age expression is correct.');
140 * Tests SELECT statements with multiple expressions.
142 public function testSimpleSelectExpressionMultiple() {
143 $query = db_select('test');
144 $name_field = $query->addField('test', 'name');
145 $age_double_field = $query->addExpression("age*2");
146 $age_triple_field = $query->addExpression("age*3");
147 $query->condition('age', 27);
148 $result = $query->execute();
150 // Check that the aliases are being created the way we want.
151 $this->assertEqual($age_double_field, 'expression', 'Double age field alias is correct.');
152 $this->assertEqual($age_triple_field, 'expression_2', 'Triple age field alias is correct.');
154 // Ensure that we got the right record.
155 $record = $result->fetch();
156 $this->assertEqual($record->$name_field, 'George', 'Fetched name is correct.');
157 $this->assertEqual($record->$age_double_field, 27 * 2, 'Fetched double age expression is correct.');
158 $this->assertEqual($record->$age_triple_field, 27 * 3, 'Fetched triple age expression is correct.');
162 * Tests adding multiple fields to a SELECT statement at the same time.
164 public function testSimpleSelectMultipleFields() {
165 $record = db_select('test')
166 ->fields('test', ['id', 'name', 'age', 'job'])
167 ->condition('age', 27)
168 ->execute()->fetchObject();
170 // Check that all fields we asked for are present.
171 $this->assertNotNull($record->id, 'ID field is present.');
172 $this->assertNotNull($record->name, 'Name field is present.');
173 $this->assertNotNull($record->age, 'Age field is present.');
174 $this->assertNotNull($record->job, 'Job field is present.');
176 // Ensure that we got the right record.
177 // Check that all fields we asked for are present.
178 $this->assertEqual($record->id, 2, 'ID field has the correct value.');
179 $this->assertEqual($record->name, 'George', 'Name field has the correct value.');
180 $this->assertEqual($record->age, 27, 'Age field has the correct value.');
181 $this->assertEqual($record->job, 'Singer', 'Job field has the correct value.');
185 * Tests adding all fields from a given table to a SELECT statement.
187 public function testSimpleSelectAllFields() {
188 $record = db_select('test')
190 ->condition('age', 27)
191 ->execute()->fetchObject();
193 // Check that all fields we asked for are present.
194 $this->assertNotNull($record->id, 'ID field is present.');
195 $this->assertNotNull($record->name, 'Name field is present.');
196 $this->assertNotNull($record->age, 'Age field is present.');
197 $this->assertNotNull($record->job, 'Job field is present.');
199 // Ensure that we got the right record.
200 // Check that all fields we asked for are present.
201 $this->assertEqual($record->id, 2, 'ID field has the correct value.');
202 $this->assertEqual($record->name, 'George', 'Name field has the correct value.');
203 $this->assertEqual($record->age, 27, 'Age field has the correct value.');
204 $this->assertEqual($record->job, 'Singer', 'Job field has the correct value.');
208 * Tests that a comparison with NULL is always FALSE.
210 public function testNullCondition() {
211 $this->ensureSampleDataNull();
213 $names = db_select('test_null', 'tn')
214 ->fields('tn', ['name'])
215 ->condition('age', NULL)
216 ->execute()->fetchCol();
218 $this->assertEqual(count($names), 0, 'No records found when comparing to NULL.');
222 * Tests that we can find a record with a NULL value.
224 public function testIsNullCondition() {
225 $this->ensureSampleDataNull();
227 $names = db_select('test_null', 'tn')
228 ->fields('tn', ['name'])
230 ->execute()->fetchCol();
232 $this->assertEqual(count($names), 1, 'Correct number of records found with NULL age.');
233 $this->assertEqual($names[0], 'Fozzie', 'Correct record returned for NULL age.');
237 * Tests that we can find a record without a NULL value.
239 public function testIsNotNullCondition() {
240 $this->ensureSampleDataNull();
242 $names = db_select('test_null', 'tn')
243 ->fields('tn', ['name'])
244 ->isNotNull('tn.age')
246 ->execute()->fetchCol();
248 $this->assertEqual(count($names), 2, 'Correct number of records found withNOT NULL age.');
249 $this->assertEqual($names[0], 'Gonzo', 'Correct record returned for NOT NULL age.');
250 $this->assertEqual($names[1], 'Kermit', 'Correct record returned for NOT NULL age.');
254 * Tests that we can UNION multiple Select queries together.
256 * This is semantically equal to UNION DISTINCT, so we don't explicitly test
259 public function testUnion() {
260 $query_1 = db_select('test', 't')
261 ->fields('t', ['name'])
262 ->condition('age', [27, 28], 'IN');
264 $query_2 = db_select('test', 't')
265 ->fields('t', ['name'])
266 ->condition('age', 28);
268 $query_1->union($query_2);
270 $names = $query_1->execute()->fetchCol();
272 // Ensure we only get 2 records.
273 $this->assertEqual(count($names), 2, 'UNION correctly discarded duplicates.');
275 $this->assertEqual($names[0], 'George', 'First query returned correct name.');
276 $this->assertEqual($names[1], 'Ringo', 'Second query returned correct name.');
280 * Tests that we can UNION ALL multiple SELECT queries together.
282 public function testUnionAll() {
283 $query_1 = db_select('test', 't')
284 ->fields('t', ['name'])
285 ->condition('age', [27, 28], 'IN');
287 $query_2 = db_select('test', 't')
288 ->fields('t', ['name'])
289 ->condition('age', 28);
291 $query_1->union($query_2, 'ALL');
293 $names = $query_1->execute()->fetchCol();
295 // Ensure we get all 3 records.
296 $this->assertEqual(count($names), 3, 'UNION ALL correctly preserved duplicates.');
298 $this->assertEqual($names[0], 'George', 'First query returned correct first name.');
299 $this->assertEqual($names[1], 'Ringo', 'Second query returned correct second name.');
300 $this->assertEqual($names[2], 'Ringo', 'Third query returned correct name.');
304 * Tests that we can get a count query for a UNION Select query.
306 public function testUnionCount() {
307 $query_1 = db_select('test', 't')
308 ->fields('t', ['name', 'age'])
309 ->condition('age', [27, 28], 'IN');
311 $query_2 = db_select('test', 't')
312 ->fields('t', ['name', 'age'])
313 ->condition('age', 28);
315 $query_1->union($query_2, 'ALL');
316 $names = $query_1->execute()->fetchCol();
318 $query_3 = $query_1->countQuery();
319 $count = $query_3->execute()->fetchField();
321 // Ensure the counts match.
322 $this->assertEqual(count($names), $count, "The count query's result matched the number of rows in the UNION query.");
326 * Tests that we can UNION multiple Select queries together and set the ORDER.
328 public function testUnionOrder() {
329 // This gives George and Ringo.
330 $query_1 = db_select('test', 't')
331 ->fields('t', ['name'])
332 ->condition('age', [27, 28], 'IN');
335 $query_2 = db_select('test', 't')
336 ->fields('t', ['name'])
337 ->condition('age', 26);
339 $query_1->union($query_2);
340 $query_1->orderBy('name', 'DESC');
342 $names = $query_1->execute()->fetchCol();
344 // Ensure we get all 3 records.
345 $this->assertEqual(count($names), 3, 'UNION returned rows from both queries.');
347 // Ensure that the names are in the correct reverse alphabetical order,
348 // regardless of which query they came from.
349 $this->assertEqual($names[0], 'Ringo', 'First query returned correct name.');
350 $this->assertEqual($names[1], 'Paul', 'Second query returned correct name.');
351 $this->assertEqual($names[2], 'George', 'Third query returned correct name.');
355 * Tests that we can UNION multiple Select queries together with and a LIMIT.
357 public function testUnionOrderLimit() {
358 // This gives George and Ringo.
359 $query_1 = db_select('test', 't')
360 ->fields('t', ['name'])
361 ->condition('age', [27, 28], 'IN');
364 $query_2 = db_select('test', 't')
365 ->fields('t', ['name'])
366 ->condition('age', 26);
368 $query_1->union($query_2);
369 $query_1->orderBy('name', 'DESC');
370 $query_1->range(0, 2);
372 $names = $query_1->execute()->fetchCol();
374 // Ensure we get all only 2 of the 3 records.
375 $this->assertEqual(count($names), 2, 'UNION with a limit returned rows from both queries.');
377 // Ensure that the names are in the correct reverse alphabetical order,
378 // regardless of which query they came from.
379 $this->assertEqual($names[0], 'Ringo', 'First query returned correct name.');
380 $this->assertEqual($names[1], 'Paul', 'Second query returned correct name.');
384 * Tests that random ordering of queries works.
386 * We take the approach of testing the Drupal layer only, rather than trying
387 * to test that the database's random number generator actually produces
388 * random queries (which is very difficult to do without an unacceptable risk
389 * of the test failing by accident).
391 * Therefore, in this test we simply run the same query twice and assert that
392 * the two results are reordered versions of each other (as well as of the
393 * same query without the random ordering). It is reasonable to assume that
394 * if we run the same select query twice and the results are in a different
395 * order each time, the only way this could happen is if we have successfully
396 * triggered the database's random ordering functionality.
398 public function testRandomOrder() {
399 // Use 52 items, so the chance that this test fails by accident will be the
400 // same as the chance that a deck of cards will come out in the same order
401 // after shuffling it (in other words, nearly impossible).
402 $number_of_items = 52;
403 while (db_query("SELECT MAX(id) FROM {test}")->fetchField() < $number_of_items) {
404 db_insert('test')->fields(['name' => $this->randomMachineName()])->execute();
407 // First select the items in order and make sure we get an ordered list.
408 $expected_ids = range(1, $number_of_items);
409 $ordered_ids = db_select('test', 't')
410 ->fields('t', ['id'])
411 ->range(0, $number_of_items)
415 $this->assertEqual($ordered_ids, $expected_ids, 'A query without random ordering returns IDs in the correct order.');
417 // Now perform the same query, but instead choose a random ordering. We
418 // expect this to contain a differently ordered version of the original
420 $randomized_ids = db_select('test', 't')
421 ->fields('t', ['id'])
422 ->range(0, $number_of_items)
426 $this->assertNotEqual($randomized_ids, $ordered_ids, 'A query with random ordering returns an unordered set of IDs.');
427 $sorted_ids = $randomized_ids;
429 $this->assertEqual($sorted_ids, $ordered_ids, 'After sorting the random list, the result matches the original query.');
431 // Now perform the exact same query again, and make sure the order is
433 $randomized_ids_second_set = db_select('test', 't')
434 ->fields('t', ['id'])
435 ->range(0, $number_of_items)
439 $this->assertNotEqual($randomized_ids_second_set, $randomized_ids, 'Performing the query with random ordering a second time returns IDs in a different order.');
440 $sorted_ids_second_set = $randomized_ids_second_set;
441 sort($sorted_ids_second_set);
442 $this->assertEqual($sorted_ids_second_set, $sorted_ids, 'After sorting the second random list, the result matches the sorted version of the first random list.');
446 * Tests that filter by a regular expression works as expected.
448 public function testRegexCondition() {
463 'regex' => 'Ringo|George',
470 $database = $this->container->get('database');
471 foreach ($test_groups as $test_group) {
472 $query = $database->select('test', 't');
473 $query->addField('t', 'name');
474 $query->condition('t.name', $test_group['regex'], 'REGEXP');
475 $result = $query->execute()->fetchCol();
477 $this->assertEqual(count($result), count($test_group['expected']), 'Returns the expected number of rows.');
478 $this->assertEqual(sort($result), sort($test_group['expected']), 'Returns the expected rows.');
481 // Ensure that filter by "#" still works due to the quoting.
482 $database->insert('test')
492 'regex' => '#Drummer',
498 'regex' => '#Singer',
503 foreach ($test_groups as $test_group) {
504 $query = $database->select('test', 't');
505 $query->addField('t', 'name');
506 $query->condition('t.job', $test_group['regex'], 'REGEXP');
507 $result = $query->execute()->fetchCol();
509 $this->assertEqual(count($result), count($test_group['expected']), 'Returns the expected number of rows.');
510 $this->assertEqual(sort($result), sort($test_group['expected']), 'Returns the expected rows.');
513 // Ensure that REGEXP filter still works with no-string type field.
514 $query = $database->select('test', 't');
515 $query->addField('t', 'age');
516 $query->condition('t.age', '2[6]', 'REGEXP');
517 $result = $query->execute()->fetchField();
518 $this->assertEquals($result, '26', 'Regexp with number type.');
522 * Tests that aliases are renamed when they are duplicates.
524 public function testSelectDuplicateAlias() {
525 $query = db_select('test', 't');
526 $alias1 = $query->addField('t', 'name', 'the_alias');
527 $alias2 = $query->addField('t', 'age', 'the_alias');
528 $this->assertNotIdentical($alias1, $alias2, 'Duplicate aliases are renamed.');
532 * Tests that an invalid merge query throws an exception.
534 public function testInvalidSelectCount() {
536 // This query will fail because the table does not exist.
537 // Normally it would throw an exception but we are suppressing
538 // it with the throw_exception option.
539 $options['throw_exception'] = FALSE;
540 db_select('some_table_that_doesnt_exist', 't', $options)
545 $this->pass('$options[\'throw_exception\'] is FALSE, no Exception thrown.');
547 catch (\Exception $e) {
548 $this->fail('$options[\'throw_exception\'] is FALSE, but Exception thrown for invalid query.');
553 // This query will fail because the table does not exist.
554 db_select('some_table_that_doesnt_exist', 't')
559 catch (\Exception $e) {
560 $this->pass('Exception thrown for invalid query.');
563 $this->fail('No Exception thrown.');
567 * Tests thrown exception for IN query conditions with an empty array.
569 public function testEmptyInCondition() {
571 db_select('test', 't')
573 ->condition('age', [], 'IN')
576 $this->fail('Expected exception not thrown');
578 catch (InvalidQueryException $e) {
579 $this->assertEqual("Query condition 'age IN ()' cannot be empty.", $e->getMessage());
583 db_select('test', 't')
585 ->condition('age', [], 'NOT IN')
588 $this->fail('Expected exception not thrown');
590 catch (InvalidQueryException $e) {
591 $this->assertEqual("Query condition 'age NOT IN ()' cannot be empty.", $e->getMessage());