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 android.database.sqlite.cts; 18 19 import static org.junit.Assert.assertEquals; 20 import static org.junit.Assert.assertNotNull; 21 import static org.junit.Assert.assertTrue; 22 import static org.junit.Assert.fail; 23 24 import android.content.ContentValues; 25 import android.content.Context; 26 import android.database.Cursor; 27 import android.database.sqlite.SQLiteCursor; 28 import android.database.sqlite.SQLiteCursorDriver; 29 import android.database.sqlite.SQLiteDatabase; 30 import android.database.sqlite.SQLiteQuery; 31 import android.database.sqlite.SQLiteQueryBuilder; 32 import android.os.CancellationSignal; 33 import android.os.OperationCanceledException; 34 35 import androidx.test.InstrumentationRegistry; 36 import androidx.test.runner.AndroidJUnit4; 37 38 import org.junit.After; 39 import org.junit.Before; 40 import org.junit.Test; 41 import org.junit.runner.RunWith; 42 43 import java.util.HashMap; 44 import java.util.Map; 45 import java.util.Objects; 46 import java.util.concurrent.Semaphore; 47 48 @RunWith(AndroidJUnit4.class) 49 public class SQLiteQueryBuilderTest { 50 private SQLiteDatabase mDatabase; 51 private final String TEST_TABLE_NAME = "test"; 52 private final String EMPLOYEE_TABLE_NAME = "employee"; 53 private static final String DATABASE_FILE = "database_test.db"; 54 55 @Before 56 public void setUp() throws Exception { 57 final Context context = InstrumentationRegistry.getTargetContext(); 58 59 context.deleteDatabase(DATABASE_FILE); 60 mDatabase = Objects.requireNonNull( 61 context.openOrCreateDatabase(DATABASE_FILE, Context.MODE_PRIVATE, null)); 62 } 63 64 @After 65 public void tearDown() throws Exception { 66 final Context context = InstrumentationRegistry.getTargetContext(); 67 68 mDatabase.close(); 69 context.deleteDatabase(DATABASE_FILE); 70 } 71 72 @Test 73 public void testConstructor() { 74 new SQLiteQueryBuilder(); 75 } 76 77 @Test 78 public void testSetDistinct() { 79 String expected; 80 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 81 sqliteQueryBuilder.setTables(TEST_TABLE_NAME); 82 sqliteQueryBuilder.setDistinct(false); 83 sqliteQueryBuilder.appendWhere("age=20"); 84 String sql = sqliteQueryBuilder.buildQuery(new String[] { "age", "address" }, 85 null, null, null, null, null); 86 assertEquals(TEST_TABLE_NAME, sqliteQueryBuilder.getTables()); 87 expected = "SELECT age, address FROM " + TEST_TABLE_NAME + " WHERE (age=20)"; 88 assertEquals(expected, sql); 89 90 sqliteQueryBuilder = new SQLiteQueryBuilder(); 91 sqliteQueryBuilder.setTables(EMPLOYEE_TABLE_NAME); 92 sqliteQueryBuilder.setDistinct(true); 93 sqliteQueryBuilder.appendWhere("age>32"); 94 sql = sqliteQueryBuilder.buildQuery(new String[] { "age", "address" }, 95 null, null, null, null, null); 96 assertEquals(EMPLOYEE_TABLE_NAME, sqliteQueryBuilder.getTables()); 97 expected = "SELECT DISTINCT age, address FROM " + EMPLOYEE_TABLE_NAME + " WHERE (age>32)"; 98 assertEquals(expected, sql); 99 100 sqliteQueryBuilder = new SQLiteQueryBuilder(); 101 sqliteQueryBuilder.setTables(EMPLOYEE_TABLE_NAME); 102 sqliteQueryBuilder.setDistinct(true); 103 sqliteQueryBuilder.appendWhereEscapeString("age>32"); 104 sql = sqliteQueryBuilder.buildQuery(new String[] { "age", "address" }, 105 null, null, null, null, null); 106 assertEquals(EMPLOYEE_TABLE_NAME, sqliteQueryBuilder.getTables()); 107 expected = "SELECT DISTINCT age, address FROM " + EMPLOYEE_TABLE_NAME 108 + " WHERE ('age>32')"; 109 assertEquals(expected, sql); 110 } 111 112 @Test 113 public void testSetProjectionMap() { 114 String expected; 115 Map<String, String> projectMap = new HashMap<String, String>(); 116 projectMap.put("EmployeeName", "name"); 117 projectMap.put("EmployeeAge", "age"); 118 projectMap.put("EmployeeAddress", "address"); 119 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 120 sqliteQueryBuilder.setTables(TEST_TABLE_NAME); 121 sqliteQueryBuilder.setDistinct(false); 122 sqliteQueryBuilder.setProjectionMap(projectMap); 123 String sql = sqliteQueryBuilder.buildQuery(new String[] { "EmployeeName", "EmployeeAge" }, 124 null, null, null, null, null); 125 expected = "SELECT name, age FROM " + TEST_TABLE_NAME; 126 assertEquals(expected, sql); 127 128 sql = sqliteQueryBuilder.buildQuery(null, // projectionIn is null 129 null, null, null, null, null); 130 assertTrue(sql.matches("SELECT (age|name|address), (age|name|address), (age|name|address) " 131 + "FROM " + TEST_TABLE_NAME)); 132 assertTrue(sql.contains("age")); 133 assertTrue(sql.contains("name")); 134 assertTrue(sql.contains("address")); 135 136 sqliteQueryBuilder.setProjectionMap(null); 137 sql = sqliteQueryBuilder.buildQuery(new String[] { "name", "address" }, 138 null, null, null, null, null); 139 assertTrue(sql.matches("SELECT (name|address), (name|address) " 140 + "FROM " + TEST_TABLE_NAME)); 141 assertTrue(sql.contains("name")); 142 assertTrue(sql.contains("address")); 143 } 144 145 @Test 146 public void testSetCursorFactory() { 147 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, " + 148 "name TEXT, age INTEGER, address TEXT);"); 149 mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Mike', '20', 'LA');"); 150 mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('jack', '40', 'LA');"); 151 152 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 153 sqliteQueryBuilder.setTables(TEST_TABLE_NAME); 154 Cursor cursor = sqliteQueryBuilder.query(mDatabase, new String[] { "name", "age" }, 155 null, null, null, null, null); 156 assertNotNull(cursor); 157 assertTrue(cursor instanceof SQLiteCursor); 158 159 SQLiteDatabase.CursorFactory factory = new SQLiteDatabase.CursorFactory() { 160 @Override 161 public Cursor newCursor(SQLiteDatabase db, SQLiteCursorDriver masterQuery, 162 String editTable, SQLiteQuery query) { 163 return new MockCursor(masterQuery, editTable, query); 164 } 165 }; 166 167 sqliteQueryBuilder.setCursorFactory(factory); 168 cursor = sqliteQueryBuilder.query(mDatabase, new String[] { "name", "age" }, 169 null, null, null, null, null); 170 assertNotNull(cursor); 171 assertTrue(cursor instanceof MockCursor); 172 } 173 174 private static class MockCursor extends SQLiteCursor { 175 public MockCursor(SQLiteCursorDriver driver, 176 String editTable, SQLiteQuery query) { 177 super(driver, editTable, query); 178 } 179 } 180 181 @Test 182 public void testBuildQueryString() { 183 String expected; 184 final String[] DEFAULT_TEST_PROJECTION = new String [] { "name", "age", "sum(salary)" }; 185 final String DEFAULT_TEST_WHERE = "age > 25"; 186 final String DEFAULT_HAVING = "sum(salary) > 3000"; 187 188 String sql = SQLiteQueryBuilder.buildQueryString(false, "Employee", 189 DEFAULT_TEST_PROJECTION, 190 DEFAULT_TEST_WHERE, "name", DEFAULT_HAVING, "name", "100"); 191 192 expected = "SELECT name, age, sum(salary) FROM Employee WHERE " + DEFAULT_TEST_WHERE + 193 " GROUP BY name " + 194 "HAVING " + DEFAULT_HAVING + " " + 195 "ORDER BY name " + 196 "LIMIT 100"; 197 assertEquals(expected, sql); 198 } 199 200 @Test 201 public void testBuildQuery() { 202 final String[] DEFAULT_TEST_PROJECTION = new String[] { "name", "sum(salary)" }; 203 final String DEFAULT_TEST_WHERE = "age > 25"; 204 final String DEFAULT_HAVING = "sum(salary) > 2000"; 205 206 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 207 sqliteQueryBuilder.setTables(TEST_TABLE_NAME); 208 sqliteQueryBuilder.setDistinct(false); 209 String sql = sqliteQueryBuilder.buildQuery(DEFAULT_TEST_PROJECTION, 210 DEFAULT_TEST_WHERE, "name", DEFAULT_HAVING, "name", "2"); 211 String expected = "SELECT name, sum(salary) FROM " + TEST_TABLE_NAME 212 + " WHERE (" + DEFAULT_TEST_WHERE + ") " + 213 "GROUP BY name HAVING " + DEFAULT_HAVING + " ORDER BY name LIMIT 2"; 214 assertEquals(expected, sql); 215 } 216 217 @Test 218 public void testAppendColumns() { 219 StringBuilder sb = new StringBuilder(); 220 String[] columns = new String[] { "name", "age" }; 221 222 assertEquals("", sb.toString()); 223 SQLiteQueryBuilder.appendColumns(sb, columns); 224 assertEquals("name, age ", sb.toString()); 225 } 226 227 @Test 228 public void testAppendWhereStandalone() { 229 SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); 230 qb.setTables("Employee"); 231 qb.appendWhereStandalone("A"); 232 qb.appendWhereStandalone("B"); 233 qb.appendWhereStandalone("C"); 234 235 final String query = qb.buildQuery(null, null, null, null, null, null); 236 assertTrue(query.contains("(A) AND (B) AND (C)")); 237 } 238 239 @Test 240 public void testQuery() { 241 createEmployeeTable(); 242 243 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 244 sqliteQueryBuilder.setTables("Employee"); 245 Cursor cursor = sqliteQueryBuilder.query(mDatabase, 246 new String[] { "name", "sum(salary)" }, null, null, 247 "name", "sum(salary)>1000", "name"); 248 assertNotNull(cursor); 249 assertEquals(3, cursor.getCount()); 250 251 final int COLUMN_NAME_INDEX = 0; 252 final int COLUMN_SALARY_INDEX = 1; 253 cursor.moveToFirst(); 254 assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX)); 255 assertEquals(4500, cursor.getInt(COLUMN_SALARY_INDEX)); 256 cursor.moveToNext(); 257 assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); 258 assertEquals(4000, cursor.getInt(COLUMN_SALARY_INDEX)); 259 cursor.moveToNext(); 260 assertEquals("jack", cursor.getString(COLUMN_NAME_INDEX)); 261 assertEquals(3500, cursor.getInt(COLUMN_SALARY_INDEX)); 262 263 sqliteQueryBuilder = new SQLiteQueryBuilder(); 264 sqliteQueryBuilder.setTables(EMPLOYEE_TABLE_NAME); 265 cursor = sqliteQueryBuilder.query(mDatabase, 266 new String[] { "name", "sum(salary)" }, null, null, 267 "name", "sum(salary)>1000", "name", "2" // limit is 2 268 ); 269 assertNotNull(cursor); 270 assertEquals(2, cursor.getCount()); 271 cursor.moveToFirst(); 272 assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX)); 273 assertEquals(4500, cursor.getInt(COLUMN_SALARY_INDEX)); 274 cursor.moveToNext(); 275 assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); 276 assertEquals(4000, cursor.getInt(COLUMN_SALARY_INDEX)); 277 } 278 279 @Test 280 public void testUnionQuery() { 281 String expected; 282 String[] innerProjection = new String[] {"name", "age", "location"}; 283 SQLiteQueryBuilder employeeQueryBuilder = new SQLiteQueryBuilder(); 284 SQLiteQueryBuilder peopleQueryBuilder = new SQLiteQueryBuilder(); 285 286 employeeQueryBuilder.setTables("employee"); 287 peopleQueryBuilder.setTables("people"); 288 289 String employeeSubQuery = employeeQueryBuilder.buildUnionSubQuery( 290 "_id", innerProjection, 291 null, 2, "employee", 292 "age=25", 293 null, null); 294 String peopleSubQuery = peopleQueryBuilder.buildUnionSubQuery( 295 "_id", innerProjection, 296 null, 2, "people", 297 "location=LA", 298 null, null); 299 expected = "SELECT name, age, location FROM employee WHERE (age=25)"; 300 assertEquals(expected, employeeSubQuery); 301 expected = "SELECT name, age, location FROM people WHERE (location=LA)"; 302 assertEquals(expected, peopleSubQuery); 303 304 SQLiteQueryBuilder unionQueryBuilder = new SQLiteQueryBuilder(); 305 306 unionQueryBuilder.setDistinct(true); 307 308 String unionQuery = unionQueryBuilder.buildUnionQuery( 309 new String[] { employeeSubQuery, peopleSubQuery }, null, null); 310 expected = "SELECT name, age, location FROM employee WHERE (age=25) " + 311 "UNION SELECT name, age, location FROM people WHERE (location=LA)"; 312 assertEquals(expected, unionQuery); 313 } 314 315 @Test 316 public void testCancelableQuery_WhenNotCanceled_ReturnsResultSet() { 317 createEmployeeTable(); 318 319 CancellationSignal cancellationSignal = new CancellationSignal(); 320 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 321 sqliteQueryBuilder.setTables("Employee"); 322 Cursor cursor = sqliteQueryBuilder.query(mDatabase, 323 new String[] { "name", "sum(salary)" }, null, null, 324 "name", "sum(salary)>1000", "name", null, cancellationSignal); 325 326 assertEquals(3, cursor.getCount()); 327 } 328 329 @Test 330 public void testCancelableQuery_WhenCanceledBeforeQuery_ThrowsImmediately() { 331 createEmployeeTable(); 332 333 CancellationSignal cancellationSignal = new CancellationSignal(); 334 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 335 sqliteQueryBuilder.setTables("Employee"); 336 337 cancellationSignal.cancel(); 338 try { 339 sqliteQueryBuilder.query(mDatabase, 340 new String[] { "name", "sum(salary)" }, null, null, 341 "name", "sum(salary)>1000", "name", null, cancellationSignal); 342 fail("Expected OperationCanceledException"); 343 } catch (OperationCanceledException ex) { 344 // expected 345 } 346 } 347 348 @Test 349 public void testCancelableQuery_WhenCanceledAfterQuery_ThrowsWhenExecuted() { 350 createEmployeeTable(); 351 352 CancellationSignal cancellationSignal = new CancellationSignal(); 353 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 354 sqliteQueryBuilder.setTables("Employee"); 355 356 Cursor cursor = sqliteQueryBuilder.query(mDatabase, 357 new String[] { "name", "sum(salary)" }, null, null, 358 "name", "sum(salary)>1000", "name", null, cancellationSignal); 359 360 cancellationSignal.cancel(); 361 try { 362 cursor.getCount(); // force execution 363 fail("Expected OperationCanceledException"); 364 } catch (OperationCanceledException ex) { 365 // expected 366 } 367 } 368 369 @Test 370 public void testCancelableQuery_WhenCanceledDueToContention_StopsWaitingAndThrows() { 371 createEmployeeTable(); 372 373 for (int i = 0; i < 5; i++) { 374 final CancellationSignal cancellationSignal = new CancellationSignal(); 375 final Semaphore barrier1 = new Semaphore(0); 376 final Semaphore barrier2 = new Semaphore(0); 377 Thread contentionThread = new Thread() { 378 @Override 379 public void run() { 380 mDatabase.beginTransaction(); // acquire the only available connection 381 barrier1.release(); // release query to start running 382 try { 383 barrier2.acquire(); // wait for test to end 384 } catch (InterruptedException e) { 385 } 386 mDatabase.endTransaction(); // release the connection 387 } 388 }; 389 Thread cancellationThread = new Thread() { 390 @Override 391 public void run() { 392 try { 393 Thread.sleep(300); 394 } catch (InterruptedException ex) { 395 } 396 cancellationSignal.cancel(); 397 } 398 }; 399 try { 400 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 401 sqliteQueryBuilder.setTables("Employee"); 402 403 contentionThread.start(); 404 cancellationThread.start(); 405 406 try { 407 barrier1.acquire(); // wait for contention thread to start transaction 408 } catch (InterruptedException e) { 409 } 410 411 final long startTime = System.nanoTime(); 412 try { 413 Cursor cursor = sqliteQueryBuilder.query(mDatabase, 414 new String[] { "name", "sum(salary)" }, null, null, 415 "name", "sum(salary)>1000", "name", null, cancellationSignal); 416 cursor.getCount(); // force execution 417 fail("Expected OperationCanceledException"); 418 } catch (OperationCanceledException ex) { 419 // expected 420 } 421 422 // We want to confirm that the query really was blocked trying to acquire a 423 // connection for a certain amount of time before it was freed by cancel. 424 final long waitTime = System.nanoTime() - startTime; 425 if (waitTime > 150 * 1000000L) { 426 return; // success! 427 } 428 } finally { 429 barrier1.release(); 430 barrier2.release(); 431 try { 432 contentionThread.join(); 433 cancellationThread.join(); 434 } catch (InterruptedException e) { 435 } 436 } 437 } 438 439 // Occasionally we might miss the timing deadline due to factors in the 440 // environment, but if after several trials we still couldn't demonstrate 441 // that the query was blocked, then the test must be broken. 442 fail("Could not prove that the query actually blocked before cancel() was called."); 443 } 444 445 @Test 446 public void testCancelableQuery_WhenCanceledDuringLongRunningQuery_CancelsQueryAndThrows() { 447 // Populate a table with a bunch of integers. 448 mDatabase.execSQL("CREATE TABLE x (v INTEGER);"); 449 for (int i = 0; i < 100; i++) { 450 mDatabase.execSQL("INSERT INTO x VALUES (?)", new Object[] { i }); 451 } 452 453 for (int i = 0; i < 5; i++) { 454 final CancellationSignal cancellationSignal = new CancellationSignal(); 455 Thread cancellationThread = new Thread() { 456 @Override 457 public void run() { 458 try { 459 Thread.sleep(300); 460 } catch (InterruptedException ex) { 461 } 462 cancellationSignal.cancel(); 463 } 464 }; 465 try { 466 // Build an unsatisfiable 5-way cross-product query over 100 values but 467 // produces no output. This should force SQLite to loop for a long time 468 // as it tests 10^10 combinations. 469 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 470 sqliteQueryBuilder.setTables("x AS a, x AS b, x AS c, x AS d, x AS e"); 471 472 cancellationThread.start(); 473 474 final long startTime = System.nanoTime(); 475 try { 476 Cursor cursor = sqliteQueryBuilder.query(mDatabase, null, 477 "a.v + b.v + c.v + d.v + e.v > 1000000", 478 null, null, null, null, null, cancellationSignal); 479 cursor.getCount(); // force execution 480 fail("Expected OperationCanceledException"); 481 } catch (OperationCanceledException ex) { 482 // expected 483 } 484 485 // We want to confirm that the query really was running and then got 486 // canceled midway. 487 final long waitTime = System.nanoTime() - startTime; 488 if (waitTime > 150 * 1000000L && waitTime < 600 * 1000000L) { 489 return; // success! 490 } 491 } finally { 492 try { 493 cancellationThread.join(); 494 } catch (InterruptedException e) { 495 } 496 } 497 } 498 499 // Occasionally we might miss the timing deadline due to factors in the 500 // environment, but if after several trials we still couldn't demonstrate 501 // that the query was canceled, then the test must be broken. 502 fail("Could not prove that the query actually canceled midway during execution."); 503 } 504 505 @Test 506 public void testUpdate() throws Exception { 507 createEmployeeTable(); 508 509 final ContentValues values = new ContentValues(); 510 values.put("name", "Anonymous"); 511 values.put("salary", 0); 512 513 { 514 final SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); 515 qb.setTables("employee"); 516 qb.appendWhere("month=3"); 517 assertEquals(2, qb.update(mDatabase, values, null, null)); 518 } 519 { 520 final SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); 521 qb.setTables("employee"); 522 assertEquals(1, qb.update(mDatabase, values, "month=?", new String[] { "2" })); 523 } 524 } 525 526 @Test 527 public void testDelete() throws Exception { 528 createEmployeeTable(); 529 530 { 531 final SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); 532 qb.setTables("employee"); 533 qb.appendWhere("month=3"); 534 assertEquals(2, qb.delete(mDatabase, null, null)); 535 assertEquals(0, qb.delete(mDatabase, null, null)); 536 } 537 { 538 final SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); 539 qb.setTables("employee"); 540 assertEquals(1, qb.delete(mDatabase, "month=?", new String[] { "2" })); 541 assertEquals(0, qb.delete(mDatabase, "month=?", new String[] { "2" })); 542 } 543 } 544 545 @Test 546 public void testStrictQuery() throws Exception { 547 createEmployeeTable(); 548 549 final SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); 550 qb.setTables("employee"); 551 qb.setStrict(true); 552 qb.appendWhere("month=2"); 553 554 // Should normally only be able to see one row 555 try (Cursor c = qb.query(mDatabase, null, null, null, null, null, null)) { 556 assertEquals(1, c.getCount()); 557 } 558 559 // Trying sneaky queries should fail; even if they somehow succeed, we 560 // shouldn't get to see any other data. 561 try (Cursor c = qb.query(mDatabase, null, "1=1", null, null, null, null)) { 562 assertEquals(1, c.getCount()); 563 } catch (Exception tolerated) { 564 } 565 try (Cursor c = qb.query(mDatabase, null, "1=1 --", null, null, null, null)) { 566 assertEquals(1, c.getCount()); 567 } catch (Exception tolerated) { 568 } 569 try (Cursor c = qb.query(mDatabase, null, "1=1) OR (1=1", null, null, null, null)) { 570 assertEquals(1, c.getCount()); 571 } catch (Exception tolerated) { 572 } 573 try (Cursor c = qb.query(mDatabase, null, "1=1)) OR ((1=1", null, null, null, null)) { 574 assertEquals(1, c.getCount()); 575 } catch (Exception tolerated) { 576 } 577 } 578 579 @Test 580 public void testStrictUpdate() throws Exception { 581 createEmployeeTable(); 582 583 final ContentValues values = new ContentValues(); 584 values.put("name", "Anonymous"); 585 values.put("salary", 0); 586 587 final SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); 588 qb.setTables("employee"); 589 qb.setStrict(true); 590 qb.appendWhere("month=2"); 591 592 // Should normally only be able to update one row 593 assertEquals(1, qb.update(mDatabase, values, null, null)); 594 595 // Trying sneaky queries should fail; even if they somehow succeed, we 596 // shouldn't get to see any other data. 597 try { 598 assertEquals(1, qb.update(mDatabase, values, "1=1", null)); 599 } catch (Exception tolerated) { 600 } 601 try { 602 assertEquals(1, qb.update(mDatabase, values, "1=1 --", null)); 603 } catch (Exception tolerated) { 604 } 605 try { 606 assertEquals(1, qb.update(mDatabase, values, "1=1) OR (1=1", null)); 607 } catch (Exception tolerated) { 608 } 609 try { 610 assertEquals(1, qb.update(mDatabase, values, "1=1)) OR ((1=1", null)); 611 } catch (Exception tolerated) { 612 } 613 } 614 615 @Test 616 public void testStrictDelete() throws Exception { 617 final SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); 618 qb.setTables("employee"); 619 qb.setStrict(true); 620 qb.appendWhere("month=2"); 621 622 // Should normally only be able to update one row 623 createEmployeeTable(); 624 assertEquals(1, qb.delete(mDatabase, null, null)); 625 626 // Trying sneaky queries should fail; even if they somehow succeed, we 627 // shouldn't get to see any other data. 628 try { 629 createEmployeeTable(); 630 assertEquals(1, qb.delete(mDatabase, "1=1", null)); 631 } catch (Exception tolerated) { 632 } 633 try { 634 createEmployeeTable(); 635 assertEquals(1, qb.delete(mDatabase, "1=1 --", null)); 636 } catch (Exception tolerated) { 637 } 638 try { 639 createEmployeeTable(); 640 assertEquals(1, qb.delete(mDatabase, "1=1) OR (1=1", null)); 641 } catch (Exception tolerated) { 642 } 643 try { 644 createEmployeeTable(); 645 assertEquals(1, qb.delete(mDatabase, "1=1)) OR ((1=1", null)); 646 } catch (Exception tolerated) { 647 } 648 } 649 650 private void createEmployeeTable() { 651 mDatabase.execSQL("DROP TABLE IF EXISTS employee;"); 652 mDatabase.execSQL("CREATE TABLE employee (_id INTEGER PRIMARY KEY, " + 653 "name TEXT, month INTEGER, salary INTEGER);"); 654 mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + 655 "VALUES ('Mike', '1', '1000');"); 656 mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + 657 "VALUES ('Mike', '2', '3000');"); 658 mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + 659 "VALUES ('jack', '1', '2000');"); 660 mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + 661 "VALUES ('jack', '3', '1500');"); 662 mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + 663 "VALUES ('Jim', '1', '1000');"); 664 mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + 665 "VALUES ('Jim', '3', '3500');"); 666 } 667 } 668
