1 /* 2 * Copyright (C) 2009 The Android Open Source Project 3 * 4 * Licensed under the Apache License, Version 2.0 (the "License"); 5 * you may not use this file except in compliance with the License. 6 * You may obtain a copy of the License at 7 * 8 * http://www.apache.org/licenses/LICENSE-2.0 9 * 10 * Unless required by applicable law or agreed to in writing, software 11 * distributed under the License is distributed on an "AS IS" BASIS, 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 * See the License for the specific language governing permissions and 14 * limitations under the License. 15 */ 16 17 package com.android.providers.media.util; 18 19 import static com.google.common.truth.Truth.assertThat; 20 21 import static org.junit.Assert.assertEquals; 22 import static org.junit.Assert.assertFalse; 23 import static org.junit.Assert.assertNotNull; 24 import static org.junit.Assert.assertTrue; 25 import static org.junit.Assert.fail; 26 27 import android.content.ContentValues; 28 import android.content.Context; 29 import android.database.Cursor; 30 import android.database.sqlite.SQLiteCursor; 31 import android.database.sqlite.SQLiteCursorDriver; 32 import android.database.sqlite.SQLiteDatabase; 33 import android.database.sqlite.SQLiteQuery; 34 import android.os.Build; 35 import android.os.CancellationSignal; 36 import android.os.OperationCanceledException; 37 import android.provider.MediaStore; 38 39 import androidx.test.InstrumentationRegistry; 40 import androidx.test.runner.AndroidJUnit4; 41 42 import com.google.common.util.concurrent.Uninterruptibles; 43 44 import org.junit.After; 45 import org.junit.Before; 46 import org.junit.Test; 47 import org.junit.runner.RunWith; 48 49 import java.util.Arrays; 50 import java.util.HashMap; 51 import java.util.Map; 52 import java.util.Objects; 53 import java.util.concurrent.Semaphore; 54 55 @RunWith(AndroidJUnit4.class) 56 public class SQLiteQueryBuilderTest { 57 private SQLiteDatabase mDatabase; 58 private SQLiteQueryBuilder mStrictBuilder; 59 60 private final String TEST_TABLE_NAME = "test"; 61 private final String EMPLOYEE_TABLE_NAME = "employee"; 62 private static final String DATABASE_FILE = "database_test.db"; 63 64 @Before setUp()65 public void setUp() throws Exception { 66 final Context context = InstrumentationRegistry.getTargetContext(); 67 68 context.deleteDatabase(DATABASE_FILE); 69 mDatabase = Objects.requireNonNull( 70 context.openOrCreateDatabase(DATABASE_FILE, Context.MODE_PRIVATE, null)); 71 72 createEmployeeTable(); 73 createStrictQueryBuilder(); 74 } 75 76 @After tearDown()77 public void tearDown() throws Exception { 78 final Context context = InstrumentationRegistry.getTargetContext(); 79 80 mDatabase.close(); 81 context.deleteDatabase(DATABASE_FILE); 82 } 83 84 @Test testConstructor()85 public void testConstructor() { 86 new SQLiteQueryBuilder(); 87 } 88 89 @Test testSetDistinct()90 public void testSetDistinct() { 91 String expected; 92 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 93 sqliteQueryBuilder.setTables(TEST_TABLE_NAME); 94 sqliteQueryBuilder.setDistinct(false); 95 sqliteQueryBuilder.appendWhere("age=20"); 96 String sql = sqliteQueryBuilder.buildQuery(new String[] { "age", "address" }, 97 null, null, null, null, null); 98 assertThat(sqliteQueryBuilder.getTables()).isEqualTo(TEST_TABLE_NAME); 99 expected = "SELECT age, address FROM " + TEST_TABLE_NAME + " WHERE (age=20)"; 100 assertThat(sql).isEqualTo(expected); 101 102 sqliteQueryBuilder = new SQLiteQueryBuilder(); 103 sqliteQueryBuilder.setTables(EMPLOYEE_TABLE_NAME); 104 sqliteQueryBuilder.setDistinct(true); 105 sqliteQueryBuilder.appendWhere("age>32"); 106 sql = sqliteQueryBuilder.buildQuery(new String[] { "age", "address" }, 107 null, null, null, null, null); 108 assertThat(sqliteQueryBuilder.getTables()).isEqualTo(EMPLOYEE_TABLE_NAME); 109 expected = "SELECT DISTINCT age, address FROM " + EMPLOYEE_TABLE_NAME + " WHERE (age>32)"; 110 assertThat(sql).isEqualTo(expected); 111 112 sqliteQueryBuilder = new SQLiteQueryBuilder(); 113 sqliteQueryBuilder.setTables(EMPLOYEE_TABLE_NAME); 114 sqliteQueryBuilder.setDistinct(true); 115 sqliteQueryBuilder.appendWhereEscapeString("age>32"); 116 sql = sqliteQueryBuilder.buildQuery(new String[] { "age", "address" }, 117 null, null, null, null, null); 118 assertThat(sqliteQueryBuilder.getTables()).isEqualTo(EMPLOYEE_TABLE_NAME); 119 expected = "SELECT DISTINCT age, address FROM " + EMPLOYEE_TABLE_NAME 120 + " WHERE ('age>32')"; 121 assertThat(sql).isEqualTo(expected); 122 } 123 124 @Test testSetProjectionMap()125 public void testSetProjectionMap() { 126 Map<String, String> projectMap = new HashMap<String, String>(); 127 projectMap.put("EmployeeName", "name"); 128 projectMap.put("EmployeeAge", "age"); 129 projectMap.put("EmployeeAddress", "address"); 130 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 131 sqliteQueryBuilder.setTables(TEST_TABLE_NAME); 132 sqliteQueryBuilder.setDistinct(false); 133 sqliteQueryBuilder.setProjectionMap(projectMap); 134 String sql = sqliteQueryBuilder.buildQuery(new String[] { "EmployeeName", "EmployeeAge" }, 135 null, null, null, null, null); 136 assertThat(sql).isEqualTo("SELECT name, age FROM " + TEST_TABLE_NAME); 137 138 sql = sqliteQueryBuilder.buildQuery(null, // projectionIn is null 139 null, null, null, null, null); 140 assertThat(sql).matches( 141 "SELECT (age|name|address), (age|name|address), (age|name|address) " 142 + "FROM " + TEST_TABLE_NAME); 143 assertThat(sql).contains("age"); 144 assertThat(sql).contains("name"); 145 assertThat(sql).contains("address"); 146 147 sqliteQueryBuilder.setProjectionMap(null); 148 sql = sqliteQueryBuilder.buildQuery(new String[] { "name", "address" }, 149 null, null, null, null, null); 150 assertThat(sql).matches("SELECT (name|address), (name|address) " 151 + "FROM " + TEST_TABLE_NAME); 152 assertThat(sql).contains("name"); 153 assertThat(sql).contains("address"); 154 155 } 156 157 @Test testAllowRowid()158 public void testAllowRowid() { 159 Map<String, String> projectMap = new HashMap<String, String>(); 160 projectMap.put("EmployeeName", "name"); 161 projectMap.put("EmployeeAge", "age"); 162 projectMap.put("EmployeeAddress", "address"); 163 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 164 sqliteQueryBuilder.setTables(TEST_TABLE_NAME); 165 sqliteQueryBuilder.setDistinct(false); 166 sqliteQueryBuilder.setProjectionMap(projectMap); 167 168 sqliteQueryBuilder.allowColumn("rowid"); 169 170 String sql = sqliteQueryBuilder.buildQuery(new String[] { SQLiteQueryBuilder.ROWID_COLUMN }, 171 null, null, null, null, null); 172 assertThat(sql).isEqualTo("SELECT rowid FROM " + TEST_TABLE_NAME); 173 174 sql = sqliteQueryBuilder.buildQuery(null, // projectionIn is null 175 null, null, null, null, null); 176 assertThat(sql).matches( 177 "SELECT (age|name|address|rowid), (age|name|address|rowid), " 178 + "(age|name|address|rowid), (age|name|address|rowid) " 179 + "FROM " + TEST_TABLE_NAME); 180 assertThat(sql).contains("age"); 181 assertThat(sql).contains("name"); 182 assertThat(sql).contains("address"); 183 assertThat(sql).contains("rowid"); 184 } 185 186 private static class MockCursor extends SQLiteCursor { MockCursor(SQLiteCursorDriver driver, String editTable, SQLiteQuery query)187 public MockCursor(SQLiteCursorDriver driver, 188 String editTable, SQLiteQuery query) { 189 super(driver, editTable, query); 190 } 191 } 192 193 @Test testBuildQueryString()194 public void testBuildQueryString() { 195 String expected; 196 final String[] DEFAULT_TEST_PROJECTION = new String [] { "name", "age", "sum(salary)" }; 197 final String DEFAULT_TEST_WHERE = "age > 25"; 198 final String DEFAULT_HAVING = "sum(salary) > 3000"; 199 200 String sql = SQLiteQueryBuilder.buildQueryString(false, "Employee", 201 DEFAULT_TEST_PROJECTION, 202 DEFAULT_TEST_WHERE, "name", DEFAULT_HAVING, "name", "100"); 203 204 expected = "SELECT name, age, sum(salary) FROM Employee WHERE " + DEFAULT_TEST_WHERE + 205 " GROUP BY name " + 206 "HAVING " + DEFAULT_HAVING + " " + 207 "ORDER BY name " + 208 "LIMIT 100"; 209 assertThat(sql).isEqualTo(expected); 210 } 211 212 @Test testBuildQuery()213 public void testBuildQuery() { 214 final String[] DEFAULT_TEST_PROJECTION = new String[] { "name", "sum(salary)" }; 215 final String DEFAULT_TEST_WHERE = "age > 25"; 216 final String DEFAULT_HAVING = "sum(salary) > 2000"; 217 218 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 219 sqliteQueryBuilder.setTables(TEST_TABLE_NAME); 220 sqliteQueryBuilder.setDistinct(false); 221 String sql = sqliteQueryBuilder.buildQuery(DEFAULT_TEST_PROJECTION, 222 DEFAULT_TEST_WHERE, "name", DEFAULT_HAVING, "name", "2"); 223 String expected = "SELECT name, sum(salary) FROM " + TEST_TABLE_NAME 224 + " WHERE (" + DEFAULT_TEST_WHERE + ") " + 225 "GROUP BY name HAVING " + DEFAULT_HAVING + " ORDER BY name LIMIT 2"; 226 assertThat(sql).isEqualTo(expected); 227 } 228 229 @Test testAppendColumns()230 public void testAppendColumns() { 231 StringBuilder sb = new StringBuilder(); 232 String[] columns = new String[] { "name", "age" }; 233 234 assertThat(sb.toString()).isEmpty(); 235 SQLiteQueryBuilder.appendColumns(sb, columns); 236 assertThat(sb.toString()).isEqualTo("name, age "); 237 } 238 239 @Test testAppendWhereStandalone()240 public void testAppendWhereStandalone() { 241 SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); 242 qb.setTables("Employee"); 243 qb.appendWhereStandalone("A"); 244 qb.appendWhereStandalone("B"); 245 qb.appendWhereStandalone("C"); 246 247 final String query = qb.buildQuery(null, null, null, null, null, null); 248 assertThat(query).contains("(A) AND (B) AND (C)"); 249 } 250 251 @Test testQuery()252 public void testQuery() { 253 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 254 sqliteQueryBuilder.setTables("Employee"); 255 Cursor cursor = sqliteQueryBuilder.query(mDatabase, 256 new String[] { "name", "sum(salary)" }, null, null, 257 "name", "sum(salary)>1000", "name", null, null); 258 assertNotNull(cursor); 259 assertEquals(3, cursor.getCount()); 260 261 final int COLUMN_NAME_INDEX = 0; 262 final int COLUMN_SALARY_INDEX = 1; 263 cursor.moveToFirst(); 264 assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX)); 265 assertEquals(4500, cursor.getInt(COLUMN_SALARY_INDEX)); 266 cursor.moveToNext(); 267 assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); 268 assertEquals(4000, cursor.getInt(COLUMN_SALARY_INDEX)); 269 cursor.moveToNext(); 270 assertEquals("jack", cursor.getString(COLUMN_NAME_INDEX)); 271 assertEquals(3500, cursor.getInt(COLUMN_SALARY_INDEX)); 272 273 sqliteQueryBuilder = new SQLiteQueryBuilder(); 274 sqliteQueryBuilder.setTables(EMPLOYEE_TABLE_NAME); 275 cursor = sqliteQueryBuilder.query(mDatabase, 276 new String[] { "name", "sum(salary)" }, null, null, 277 "name", "sum(salary)>1000", "name", "2" // limit is 2 278 , null); 279 assertNotNull(cursor); 280 assertEquals(2, cursor.getCount()); 281 cursor.moveToFirst(); 282 assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX)); 283 assertEquals(4500, cursor.getInt(COLUMN_SALARY_INDEX)); 284 cursor.moveToNext(); 285 assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); 286 assertEquals(4000, cursor.getInt(COLUMN_SALARY_INDEX)); 287 } 288 289 @Test testCancelableQuery_WhenNotCanceled_ReturnsResultSet()290 public void testCancelableQuery_WhenNotCanceled_ReturnsResultSet() { 291 CancellationSignal cancellationSignal = new CancellationSignal(); 292 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 293 sqliteQueryBuilder.setTables("Employee"); 294 Cursor cursor = sqliteQueryBuilder.query(mDatabase, 295 new String[] { "name", "sum(salary)" }, null, null, 296 "name", "sum(salary)>1000", "name", null, cancellationSignal); 297 298 assertEquals(3, cursor.getCount()); 299 } 300 301 @Test testCancelableQuery_WhenCanceledBeforeQuery_ThrowsImmediately()302 public void testCancelableQuery_WhenCanceledBeforeQuery_ThrowsImmediately() { 303 CancellationSignal cancellationSignal = new CancellationSignal(); 304 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 305 sqliteQueryBuilder.setTables("Employee"); 306 307 cancellationSignal.cancel(); 308 try { 309 sqliteQueryBuilder.query(mDatabase, 310 new String[] { "name", "sum(salary)" }, null, null, 311 "name", "sum(salary)>1000", "name", null, cancellationSignal); 312 fail("Expected OperationCanceledException"); 313 } catch (OperationCanceledException ex) { 314 // expected 315 } 316 } 317 318 @Test testCancelableQuery_WhenCanceledAfterQuery_ThrowsWhenExecuted()319 public void testCancelableQuery_WhenCanceledAfterQuery_ThrowsWhenExecuted() { 320 CancellationSignal cancellationSignal = new CancellationSignal(); 321 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 322 sqliteQueryBuilder.setTables("Employee"); 323 324 Cursor cursor = sqliteQueryBuilder.query(mDatabase, 325 new String[] { "name", "sum(salary)" }, null, null, 326 "name", "sum(salary)>1000", "name", null, cancellationSignal); 327 328 cancellationSignal.cancel(); 329 try { 330 cursor.getCount(); // force execution 331 fail("Expected OperationCanceledException"); 332 } catch (OperationCanceledException ex) { 333 // expected 334 } 335 } 336 337 @Test testCancelableQuery_WhenCanceledDueToContention_StopsWaitingAndThrows()338 public void testCancelableQuery_WhenCanceledDueToContention_StopsWaitingAndThrows() { 339 for (int i = 0; i < 5; i++) { 340 final CancellationSignal cancellationSignal = new CancellationSignal(); 341 final Semaphore barrier1 = new Semaphore(0); 342 final Semaphore barrier2 = new Semaphore(0); 343 Thread contentionThread = new Thread() { 344 @Override 345 public void run() { 346 mDatabase.beginTransaction(); // acquire the only available connection 347 barrier1.release(); // release query to start running 348 try { 349 barrier2.acquire(); // wait for test to end 350 } catch (InterruptedException e) { 351 } 352 mDatabase.endTransaction(); // release the connection 353 } 354 }; 355 Thread cancellationThread = new Thread() { 356 @Override 357 public void run() { 358 try { 359 Thread.sleep(300); 360 } catch (InterruptedException ex) { 361 } 362 cancellationSignal.cancel(); 363 } 364 }; 365 try { 366 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 367 sqliteQueryBuilder.setTables("Employee"); 368 369 contentionThread.start(); 370 cancellationThread.start(); 371 372 try { 373 barrier1.acquire(); // wait for contention thread to start transaction 374 } catch (InterruptedException e) { 375 } 376 377 final long startTime = System.nanoTime(); 378 try { 379 Cursor cursor = sqliteQueryBuilder.query(mDatabase, 380 new String[] { "name", "sum(salary)" }, null, null, 381 "name", "sum(salary)>1000", "name", null, cancellationSignal); 382 cursor.getCount(); // force execution 383 fail("Expected OperationCanceledException"); 384 } catch (OperationCanceledException ex) { 385 // expected 386 } 387 388 // We want to confirm that the query really was blocked trying to acquire a 389 // connection for a certain amount of time before it was freed by cancel. 390 final long waitTime = System.nanoTime() - startTime; 391 if (waitTime > 150 * 1000000L) { 392 return; // success! 393 } 394 } finally { 395 barrier1.release(); 396 barrier2.release(); 397 Uninterruptibles.joinUninterruptibly(contentionThread); 398 Uninterruptibles.joinUninterruptibly(cancellationThread); 399 } 400 } 401 402 // Occasionally we might miss the timing deadline due to factors in the 403 // environment, but if after several trials we still couldn't demonstrate 404 // that the query was blocked, then the test must be broken. 405 fail("Could not prove that the query actually blocked before cancel() was called."); 406 } 407 408 @Test testCancelableQuery_WhenCanceledDuringLongRunningQuery_CancelsQueryAndThrows()409 public void testCancelableQuery_WhenCanceledDuringLongRunningQuery_CancelsQueryAndThrows() { 410 // Populate a table with a bunch of integers. 411 mDatabase.execSQL("CREATE TABLE x (v INTEGER);"); 412 for (int i = 0; i < 100; i++) { 413 mDatabase.execSQL("INSERT INTO x VALUES (?)", new Object[] { i }); 414 } 415 416 for (int i = 0; i < 5; i++) { 417 final CancellationSignal cancellationSignal = new CancellationSignal(); 418 Thread cancellationThread = new Thread() { 419 @Override 420 public void run() { 421 try { 422 Thread.sleep(300); 423 } catch (InterruptedException ex) { 424 } 425 cancellationSignal.cancel(); 426 } 427 }; 428 try { 429 // Build an unsatisfiable 5-way cross-product query over 100 values but 430 // produces no output. This should force SQLite to loop for a long time 431 // as it tests 10^10 combinations. 432 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 433 sqliteQueryBuilder.setTables("x AS a, x AS b, x AS c, x AS d, x AS e"); 434 435 cancellationThread.start(); 436 437 final long startTime = System.nanoTime(); 438 try { 439 Cursor cursor = sqliteQueryBuilder.query(mDatabase, null, 440 "a.v + b.v + c.v + d.v + e.v > 1000000", 441 null, null, null, null, null, cancellationSignal); 442 cursor.getCount(); // force execution 443 fail("Expected OperationCanceledException"); 444 } catch (OperationCanceledException ex) { 445 // expected 446 } 447 448 // We want to confirm that the query really was running and then got 449 // canceled midway. 450 final long waitTime = System.nanoTime() - startTime; 451 if (waitTime > 150 * 1000000L && waitTime < 600 * 1000000L) { 452 return; // success! 453 } 454 } finally { 455 Uninterruptibles.joinUninterruptibly(cancellationThread); 456 } 457 } 458 459 // Occasionally we might miss the timing deadline due to factors in the 460 // environment, but if after several trials we still couldn't demonstrate 461 // that the query was canceled, then the test must be broken. 462 fail("Could not prove that the query actually canceled midway during execution."); 463 } 464 465 @Test testUpdate()466 public void testUpdate() throws Exception { 467 final ContentValues values = new ContentValues(); 468 values.put("name", "Anonymous"); 469 values.put("salary", 0); 470 471 { 472 final SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); 473 qb.setTables("employee"); 474 qb.appendWhere("month=3"); 475 assertEquals(2, qb.update(mDatabase, values, null, null)); 476 } 477 { 478 final SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); 479 qb.setTables("employee"); 480 assertEquals(1, qb.update(mDatabase, values, "month=?", new String[] { "2" })); 481 } 482 } 483 484 @Test testDelete()485 public void testDelete() throws Exception { 486 { 487 final SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); 488 qb.setTables("employee"); 489 qb.appendWhere("month=3"); 490 assertEquals(2, qb.delete(mDatabase, null, null)); 491 assertEquals(0, qb.delete(mDatabase, null, null)); 492 } 493 { 494 final SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); 495 qb.setTables("employee"); 496 assertEquals(1, qb.delete(mDatabase, "month=?", new String[] { "2" })); 497 assertEquals(0, qb.delete(mDatabase, "month=?", new String[] { "2" })); 498 } 499 } 500 501 @Test testStrictQuery()502 public void testStrictQuery() throws Exception { 503 final SQLiteQueryBuilder qb = mStrictBuilder; 504 505 // Should normally only be able to see one row 506 try (Cursor c = qb.query(mDatabase, null, null, null, null, null, null, null, null)) { 507 assertEquals(1, c.getCount()); 508 } 509 510 // Trying sneaky queries should fail; even if they somehow succeed, we 511 // shouldn't get to see any other data. 512 try (Cursor c = qb.query(mDatabase, null, "1=1", null, null, null, null, null, null)) { 513 assertEquals(1, c.getCount()); 514 } catch (Exception tolerated) { 515 } 516 try (Cursor c = qb.query(mDatabase, null, "1=1 --", null, null, null, null, null, null)) { 517 assertEquals(1, c.getCount()); 518 } catch (Exception tolerated) { 519 } 520 try (Cursor c = qb.query(mDatabase, null, "1=1) OR (1=1", null, null, null, null, null, null)) { 521 assertEquals(1, c.getCount()); 522 } catch (Exception tolerated) { 523 } 524 try (Cursor c = qb.query(mDatabase, null, "1=1)) OR ((1=1", null, null, null, null, null, null)) { 525 assertEquals(1, c.getCount()); 526 } catch (Exception tolerated) { 527 } 528 } 529 530 @Test testStrictUpdate()531 public void testStrictUpdate() throws Exception { 532 final SQLiteQueryBuilder qb = mStrictBuilder; 533 534 final ContentValues values = new ContentValues(); 535 values.put("name", "Anonymous"); 536 537 // Should normally only be able to update one row 538 assertEquals(1, qb.update(mDatabase, values, null, null)); 539 540 // Trying sneaky queries should fail; even if they somehow succeed, we 541 // shouldn't get to see any other data. 542 try { 543 assertEquals(1, qb.update(mDatabase, values, "1=1", null)); 544 } catch (Exception tolerated) { 545 } 546 try { 547 assertEquals(1, qb.update(mDatabase, values, "1=1 --", null)); 548 } catch (Exception tolerated) { 549 } 550 try { 551 assertEquals(1, qb.update(mDatabase, values, "1=1) OR (1=1", null)); 552 } catch (Exception tolerated) { 553 } 554 try { 555 assertEquals(1, qb.update(mDatabase, values, "1=1)) OR ((1=1", null)); 556 } catch (Exception tolerated) { 557 } 558 } 559 560 @Test testStrictDelete()561 public void testStrictDelete() throws Exception { 562 final SQLiteQueryBuilder qb = mStrictBuilder; 563 564 // Should normally only be able to update one row 565 createEmployeeTable(); 566 assertEquals(1, qb.delete(mDatabase, null, null)); 567 568 // Trying sneaky queries should fail; even if they somehow succeed, we 569 // shouldn't get to see any other data. 570 try { 571 createEmployeeTable(); 572 assertEquals(1, qb.delete(mDatabase, "1=1", null)); 573 } catch (Exception tolerated) { 574 } 575 try { 576 createEmployeeTable(); 577 assertEquals(1, qb.delete(mDatabase, "1=1 --", null)); 578 } catch (Exception tolerated) { 579 } 580 try { 581 createEmployeeTable(); 582 assertEquals(1, qb.delete(mDatabase, "1=1) OR (1=1", null)); 583 } catch (Exception tolerated) { 584 } 585 try { 586 createEmployeeTable(); 587 assertEquals(1, qb.delete(mDatabase, "1=1)) OR ((1=1", null)); 588 } catch (Exception tolerated) { 589 } 590 } 591 592 private static final String[] COLUMNS_VALID = new String[] { 593 "_id", 594 }; 595 596 private static final String[] COLUMNS_INVALID = new String[] { 597 "salary", 598 "MAX(salary)", 599 "undefined", 600 "(secret_column IN secret_table)", 601 "(SELECT secret_column FROM secret_table)", 602 }; 603 604 @Test testStrictQueryProjection()605 public void testStrictQueryProjection() throws Exception { 606 for (String column : COLUMNS_VALID) { 607 assertStrictQueryValid( 608 new String[] { column }, null, null, null, null, null, null); 609 } 610 for (String column : COLUMNS_INVALID) { 611 assertStrictQueryInvalid( 612 new String[] { column }, null, null, null, null, null, null); 613 } 614 } 615 616 @Test testStrictQueryWhere()617 public void testStrictQueryWhere() throws Exception { 618 for (String column : COLUMNS_VALID) { 619 assertStrictQueryValid( 620 null, column + ">0", null, null, null, null, null); 621 assertStrictQueryValid( 622 null, "_id>" + column, null, null, null, null, null); 623 } 624 for (String column : COLUMNS_INVALID) { 625 assertStrictQueryInvalid( 626 null, column + ">0", null, null, null, null, null); 627 assertStrictQueryInvalid( 628 null, "_id>" + column, null, null, null, null, null); 629 } 630 } 631 632 @Test testStrictQueryGroupBy()633 public void testStrictQueryGroupBy() { 634 for (String column : COLUMNS_VALID) { 635 assertStrictQueryValid( 636 null, null, null, column, null, null, null); 637 assertStrictQueryValid( 638 null, null, null, "_id," + column, null, null, null); 639 } 640 for (String column : COLUMNS_INVALID) { 641 assertStrictQueryInvalid( 642 null, null, null, column, null, null, null); 643 assertStrictQueryInvalid( 644 null, null, null, "_id," + column, null, null, null); 645 } 646 } 647 648 @Test testStrictQueryHaving()649 public void testStrictQueryHaving() { 650 for (String column : COLUMNS_VALID) { 651 assertStrictQueryValid( 652 null, null, null, "_id", column, null, null); 653 } 654 for (String column : COLUMNS_INVALID) { 655 assertStrictQueryInvalid( 656 null, null, null, "_id", column, null, null); 657 } 658 } 659 660 @Test testStrictQueryOrderBy()661 public void testStrictQueryOrderBy() { 662 for (String column : COLUMNS_VALID) { 663 assertStrictQueryValid( 664 null, null, null, null, null, column, null); 665 assertStrictQueryValid( 666 null, null, null, null, null, column + " ASC", null); 667 assertStrictQueryValid( 668 null, null, null, null, null, "_id COLLATE NOCASE ASC," + column, null); 669 } 670 for (String column : COLUMNS_INVALID) { 671 assertStrictQueryInvalid( 672 null, null, null, null, null, column, null); 673 assertStrictQueryInvalid( 674 null, null, null, null, null, column + " ASC", null); 675 assertStrictQueryInvalid( 676 null, null, null, null, null, "_id COLLATE NOCASE ASC," + column, null); 677 } 678 } 679 680 @Test testStrictQueryLimit()681 public void testStrictQueryLimit() { 682 assertStrictQueryValid( 683 null, null, null, null, null, null, "32"); 684 assertStrictQueryValid( 685 null, null, null, null, null, null, "0,32"); 686 assertStrictQueryValid( 687 null, null, null, null, null, null, "32 OFFSET 0"); 688 689 for (String column : COLUMNS_VALID) { 690 assertStrictQueryInvalid( 691 null, null, null, null, null, null, column); 692 } 693 for (String column : COLUMNS_INVALID) { 694 assertStrictQueryInvalid( 695 null, null, null, null, null, null, column); 696 } 697 } 698 699 @Test testStrictInsertValues()700 public void testStrictInsertValues() throws Exception { 701 final ContentValues values = new ContentValues(); 702 for (String column : COLUMNS_VALID) { 703 values.clear(); 704 values.put(column, 42); 705 assertStrictInsertValid(values); 706 } 707 for (String column : COLUMNS_INVALID) { 708 values.clear(); 709 values.put(column, 42); 710 assertStrictInsertInvalid(values); 711 } 712 } 713 714 @Test testStrictUpdateValues()715 public void testStrictUpdateValues() throws Exception { 716 final ContentValues values = new ContentValues(); 717 for (String column : COLUMNS_VALID) { 718 values.clear(); 719 values.put(column, 42); 720 assertStrictUpdateValid(values, null, null); 721 } 722 for (String column : COLUMNS_INVALID) { 723 values.clear(); 724 values.put(column, 42); 725 assertStrictUpdateInvalid(values, null, null); 726 } 727 } 728 assertEnforceStrictGrammarRelaxedByTargetSdk(String selection)729 private static void assertEnforceStrictGrammarRelaxedByTargetSdk(String selection) { 730 final SQLiteQueryBuilder builder = new SQLiteQueryBuilder(); 731 final HashMap<String, String> map = new HashMap<>(); 732 map.put("_data", "_data"); 733 map.put("date_added", "date_added"); 734 map.put("date_modified", "date_modified"); 735 map.put("media_type", "media_type"); 736 builder.setProjectionMap(map); 737 { 738 builder.setTargetSdkVersion(Build.VERSION_CODES.Q); 739 builder.enforceStrictGrammar(selection, null, null, null, null); 740 } 741 try { 742 builder.setTargetSdkVersion(Build.VERSION_CODES.R); 743 builder.enforceStrictGrammar(selection, null, null, null, null); 744 fail("Expected to throw"); 745 } catch (IllegalArgumentException expected) { 746 } 747 } 748 749 @Test testStrict_154193772()750 public void testStrict_154193772() { 751 final String selection = "(LOWER(_data) LIKE \"%.wmv\" OR LOWER(_data) LIKE \"%.wm\" OR LOWER(_data) LIKE \"%.wtv\" OR LOWER(_data) LIKE \"%.asf\" OR LOWER(_data) LIKE \"%.hls\" OR LOWER(_data) LIKE \"%.mp4\" OR LOWER(_data) LIKE \"%.m4v\" OR LOWER(_data) LIKE \"%.mov\" OR LOWER(_data) LIKE \"%.mp4v\" OR LOWER(_data) LIKE \"%.3g2\" OR LOWER(_data) LIKE \"%.3gp\" OR LOWER(_data) LIKE \"%.3gp2\" OR LOWER(_data) LIKE \"%.3gpp\" OR LOWER(_data) LIKE \"%.mj2\" OR LOWER(_data) LIKE \"%.qt\" OR LOWER(_data) LIKE \"%.external\" OR LOWER(_data) LIKE \"%.mov\" OR LOWER(_data) LIKE \"%.asf\" OR LOWER(_data) LIKE \"%.avi\" OR LOWER(_data) LIKE \"%.divx\" OR LOWER(_data) LIKE \"%.mpg\" OR LOWER(_data) LIKE \"%.mpeg\" OR LOWER(_data) LIKE \"%.mkv\" OR LOWER(_data) LIKE \"%.webm\" OR LOWER(_data) LIKE \"%.mk3d\" OR LOWER(_data) LIKE \"%.mks\" OR LOWER(_data) LIKE \"%.3gp\" OR LOWER(_data) LIKE \"%.mpegts\" OR LOWER(_data) LIKE \"%.ts\" OR LOWER(_data) LIKE \"%.m2ts\" OR LOWER(_data) LIKE \"%.m2t\") AND (date_added >= ? OR date_modified >=?)"; 752 assertEnforceStrictGrammarRelaxedByTargetSdk(selection); 753 } 754 755 @Test testStrict_156554363()756 public void testStrict_156554363() { 757 final String selection = "date_added>? AND media_type=0 AND (_data LIKE \"%.mov\" OR _data LIKE \"%.MOV\""; 758 assertEnforceStrictGrammarRelaxedByTargetSdk(selection); 759 } 760 761 @Test testStrict_156832140()762 public void testStrict_156832140() { 763 final String selection = "_data LIKE \"%com.gopro.smarty%\""; 764 assertEnforceStrictGrammarRelaxedByTargetSdk(selection); 765 } 766 767 @Test testStrict_156136746()768 public void testStrict_156136746() { 769 // Verify that both keywords column names are allowed to be 770 // case-insensitive, per the SQLite specification 771 assertStrictQueryValid(new String[] { "Name", "Max(Month)" }, 772 "IfNull(Month,-1) Between 1100 And 1900", null, 773 "Month", "Month In (1,2)", null, null); 774 } 775 776 @Test testStrict_158537159()777 public void testStrict_158537159() { 778 final SQLiteQueryBuilder builder = new SQLiteQueryBuilder(); 779 final HashMap<String, String> map = new HashMap<>(); 780 map.put("bucket_id", "bucket_id"); 781 builder.setProjectionMap(map); 782 final String sortOrder = "bucket_id COLLATE LOCALIZED ASC"; 783 { 784 builder.setTargetSdkVersion(Build.VERSION_CODES.Q); 785 builder.enforceStrictGrammar(null, null, null, sortOrder, null); 786 } 787 try { 788 builder.setTargetSdkVersion(Build.VERSION_CODES.R); 789 builder.enforceStrictGrammar(null, null, null, sortOrder, null); 790 fail("Expected to throw"); 791 } catch (IllegalArgumentException expected) { 792 } 793 } 794 795 @Test testStrictCustomCollator()796 public void testStrictCustomCollator() { 797 final SQLiteQueryBuilder builder = new SQLiteQueryBuilder(); 798 final HashMap<String, String> map = new HashMap<>(); 799 map.put("bucket_id", "bucket_id"); 800 builder.setProjectionMap(map); 801 802 final String sortOrder = "bucket_id COLLATE custom_zh ASC"; 803 builder.enforceStrictGrammar(null, null, null, sortOrder, null); 804 } 805 806 @Test testShouldAppendRowId_hasIdInValues_notAppendId()807 public void testShouldAppendRowId_hasIdInValues_notAppendId() { 808 final SQLiteQueryBuilder builder = new SQLiteQueryBuilder(); 809 final ContentValues values = new ContentValues(); 810 values.put(MediaStore.MediaColumns._ID, "1"); 811 values.put(MediaStore.MediaColumns.DATA, "/storage/emulated/0/"); 812 813 assertFalse(builder.shouldAppendRowId(values)); 814 } 815 816 @Test testShouldAppendRowId_noDataInValues_notAppendId()817 public void testShouldAppendRowId_noDataInValues_notAppendId() { 818 final SQLiteQueryBuilder builder = new SQLiteQueryBuilder(); 819 final ContentValues values = new ContentValues(); 820 821 assertFalse(builder.shouldAppendRowId(values)); 822 } 823 824 @Test testShouldAppendRowId_noIdInProjectionMap_notAppendId()825 public void testShouldAppendRowId_noIdInProjectionMap_notAppendId() { 826 final SQLiteQueryBuilder builder = new SQLiteQueryBuilder(); 827 final ContentValues values = new ContentValues(); 828 values.put(MediaStore.MediaColumns.DATA, "/storage/emulated/0/"); 829 830 final HashMap<String, String> map = new HashMap<>(); 831 map.put("_data", "_data"); 832 map.put("date_added", "date_added"); 833 map.put("date_modified", "date_modified"); 834 map.put("media_type", "media_type"); 835 builder.setProjectionMap(map); 836 837 assertFalse(builder.shouldAppendRowId(values)); 838 } 839 840 @Test testShouldAppendRowId_noProjectionMap_notAppendId()841 public void testShouldAppendRowId_noProjectionMap_notAppendId() { 842 final SQLiteQueryBuilder builder = new SQLiteQueryBuilder(); 843 final ContentValues values = new ContentValues(); 844 values.put(MediaStore.MediaColumns.DATA, "/storage/emulated/0/"); 845 846 assertFalse(builder.shouldAppendRowId(values)); 847 } 848 849 @Test testShouldAppendRowId_hasIdInProjectionMap_shouldAppendId()850 public void testShouldAppendRowId_hasIdInProjectionMap_shouldAppendId() { 851 final SQLiteQueryBuilder builder = new SQLiteQueryBuilder(); 852 final ContentValues values = new ContentValues(); 853 values.put(MediaStore.MediaColumns.DATA, "/storage/emulated/0/"); 854 855 final HashMap<String, String> map = new HashMap<>(); 856 map.put(MediaStore.MediaColumns._ID, MediaStore.MediaColumns._ID); 857 map.put("_data", "_data"); 858 map.put("date_added", "date_added"); 859 map.put("date_modified", "date_modified"); 860 map.put("media_type", "media_type"); 861 builder.setProjectionMap(map); 862 863 assertTrue(builder.shouldAppendRowId(values)); 864 } 865 assertStrictInsertValid(ContentValues values)866 private void assertStrictInsertValid(ContentValues values) { 867 mStrictBuilder.insert(mDatabase, values); 868 } 869 assertStrictInsertInvalid(ContentValues values)870 private void assertStrictInsertInvalid(ContentValues values) { 871 try { 872 mStrictBuilder.insert(mDatabase, values); 873 fail(Arrays.asList(values).toString()); 874 } catch (Exception expected) { 875 } 876 } 877 assertStrictUpdateValid(ContentValues values, String selection, String[] selectionArgs)878 private void assertStrictUpdateValid(ContentValues values, String selection, 879 String[] selectionArgs) { 880 mStrictBuilder.update(mDatabase, values, selection, selectionArgs); 881 } 882 assertStrictUpdateInvalid(ContentValues values, String selection, String[] selectionArgs)883 private void assertStrictUpdateInvalid(ContentValues values, String selection, 884 String[] selectionArgs) { 885 try { 886 mStrictBuilder.update(mDatabase, values, selection, selectionArgs); 887 fail(Arrays.asList(values, selection, selectionArgs).toString()); 888 } catch (Exception expected) { 889 } 890 } 891 assertStrictQueryValid(String[] projectionIn, String selection, String[] selectionArgs, String groupBy, String having, String sortOrder, String limit)892 private void assertStrictQueryValid(String[] projectionIn, String selection, 893 String[] selectionArgs, String groupBy, String having, String sortOrder, String limit) { 894 try (Cursor c = mStrictBuilder.query(mDatabase, projectionIn, selection, selectionArgs, 895 groupBy, having, sortOrder, limit, null)) { 896 } 897 } 898 assertStrictQueryInvalid(String[] projectionIn, String selection, String[] selectionArgs, String groupBy, String having, String sortOrder, String limit)899 private void assertStrictQueryInvalid(String[] projectionIn, String selection, 900 String[] selectionArgs, String groupBy, String having, String sortOrder, String limit) { 901 try (Cursor c = mStrictBuilder.query(mDatabase, projectionIn, selection, selectionArgs, 902 groupBy, having, sortOrder, limit, null)) { 903 fail(Arrays.asList(projectionIn, selection, selectionArgs, 904 groupBy, having, sortOrder, limit).toString()); 905 } catch (Exception expected) { 906 } 907 } 908 createEmployeeTable()909 private void createEmployeeTable() { 910 mDatabase.execSQL("DROP TABLE IF EXISTS employee;"); 911 mDatabase.execSQL("CREATE TABLE employee (_id INTEGER PRIMARY KEY, " + 912 "name TEXT, month INTEGER, salary INTEGER);"); 913 mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + 914 "VALUES ('Mike', '1', '1000');"); 915 mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + 916 "VALUES ('Mike', '2', '3000');"); 917 mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + 918 "VALUES ('jack', '1', '2000');"); 919 mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + 920 "VALUES ('jack', '3', '1500');"); 921 mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + 922 "VALUES ('Jim', '1', '1000');"); 923 mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + 924 "VALUES ('Jim', '3', '3500');"); 925 } 926 createStrictQueryBuilder()927 private void createStrictQueryBuilder() { 928 mStrictBuilder = new SQLiteQueryBuilder(); 929 mStrictBuilder.setTables("employee"); 930 mStrictBuilder.setStrict(true); 931 mStrictBuilder.setStrictColumns(true); 932 mStrictBuilder.setStrictGrammar(true); 933 mStrictBuilder.appendWhere("month=2"); 934 935 final Map<String, String> projectionMap = new HashMap<>(); 936 projectionMap.put("_id", "_id"); 937 projectionMap.put("name", "name"); 938 projectionMap.put("month", "month"); 939 mStrictBuilder.setProjectionMap(projectionMap); 940 } 941 } 942