sl@0: // Copyright (c) 2006-2009 Nokia Corporation and/or its subsidiary(-ies). sl@0: // All rights reserved. sl@0: // This component and the accompanying materials are made available sl@0: // under the terms of "Eclipse Public License v1.0" sl@0: // which accompanies this distribution, and is available sl@0: // at the URL "http://www.eclipse.org/legal/epl-v10.html". sl@0: // sl@0: // Initial Contributors: sl@0: // Nokia Corporation - initial contribution. sl@0: // sl@0: // Contributors: sl@0: // sl@0: // Description: sl@0: // t_sqlsecurity4 application has capabilities allowing read/write access to the test database sl@0: // sl@0: // sl@0: sl@0: #include sl@0: #include sl@0: #include sl@0: sl@0: /////////////////////////////////////////////////////////////////////////////////////// sl@0: //The test database has: sl@0: // SCHEMA database policy: ECapabilityReadDeviceData, ECapabilityWriteUserData, ECapabilityReadUserData sl@0: // WRITE database policy: ECapabilityWriteUserData sl@0: // READ database policy: ECapabilityReadUserData sl@0: // sl@0: //Database tables: sl@0: // TABLE A(F1 INTEGER, B1 BLOB) sl@0: // TABLE B(F2 INTEGER, F3 TEXT, B2 BLOB) sl@0: // sl@0: //Database data: sl@0: // TABLE A: {1, x'41414141414141414141'}, {2, x'42424242424242424242'}, {3, x'43434343434343434343'}, {4, x'44444444444444444444'} sl@0: // TABLE B: {2, "ABC", x'45454545454545454545'}, {4, "DEF", x'46464646464646464646'} sl@0: sl@0: /////////////////////////////////////////////////////////////////////////////////////// sl@0: sl@0: #define UNUSED_VAR(a) (a) = (a) sl@0: sl@0: RSqlDatabase TheDb; sl@0: RTest TheTest(_L("t_sqlsecurity4 test")); sl@0: sl@0: _LIT(KTestDbName, "c:[21212125]t_ab.db"); sl@0: _LIT(KTestDbName2, "c:\\test\\t_sqlsecurity4_2.db"); sl@0: sl@0: /////////////////////////////////////////////////////////////////////////////////////// sl@0: //Restore original test database function sl@0: void RestoreOriginalDb() sl@0: { sl@0: TheDb.Close(); sl@0: TheDb.Open(KTestDbName); sl@0: sl@0: // Delete and restore the content of table A (unconditional DELETE, no READ operations) sl@0: TheDb.Exec(_L("DELETE FROM A")); sl@0: TheDb.Exec(_L("INSERT INTO A(F1,B1) VALUES(1,x'41414141414141414141');INSERT INTO A(F1,B1) VALUES(2,x'42424242424242424242');INSERT INTO A(F1,B1) VALUES(3,x'43434343434343434343');INSERT INTO A(F1,B1) VALUES(4,x'44444444444444444444');")); sl@0: sl@0: // Delete and restore the content of table B (unconditional DELETE, no READ operations) sl@0: TheDb.Exec(_L("DELETE FROM B")); sl@0: TheDb.Exec(_L("INSERT INTO B(F2,F3,B2) VALUES(2, 'ABC',x'45454545454545454545');INSERT INTO B(F2,F3,B2) VALUES(4,'DEF',x'46464646464646464646');")); sl@0: sl@0: TheDb.Close(); sl@0: } sl@0: sl@0: /////////////////////////////////////////////////////////////////////////////////////// sl@0: //Test macros and functions sl@0: void Check1(TInt aValue, TInt aLine) sl@0: { sl@0: if(!aValue) sl@0: { sl@0: RestoreOriginalDb(); sl@0: RDebug::Print(_L("*** Line %d\r\n"), aLine); sl@0: TheTest(EFalse, aLine); sl@0: } sl@0: } sl@0: void Check2(TInt aValue, TInt aExpected, TInt aLine) sl@0: { sl@0: if(aValue != aExpected) sl@0: { sl@0: RestoreOriginalDb(); sl@0: RDebug::Print(_L("*** Line %d, Expected error: %d, got: %d\r\n"), aLine, aExpected, aValue); sl@0: TheTest(EFalse, aLine); sl@0: } sl@0: } sl@0: #define TEST(arg) ::Check1((arg), __LINE__) sl@0: #define TEST2(aValue, aExpected) ::Check2(aValue, aExpected, __LINE__) sl@0: sl@0: /////////////////////////////////////////////////////////////////////////////////////// sl@0: sl@0: //This functnion is called while there is an open secure connection. sl@0: //The function will create a new, non-secure connection and check that the non-secure database schema can be modified, sl@0: //while there is another alive, secure database connection. sl@0: void NonSecureDbTest() sl@0: { sl@0: (void)RSqlDatabase::Delete(KTestDbName2); sl@0: RSqlDatabase db; sl@0: TInt err = db.Create(KTestDbName2); sl@0: TEST2(err, KErrNone); sl@0: sl@0: err = db.Exec(_L("CREATE TABLE A(I1 INTEGER, I2 INTEGER)")); sl@0: TEST(err >= 0); sl@0: err = db.Exec(_L("CREATE TEMP TABLE B(I1 INTEGER, I2 INTEGER)")); sl@0: TEST(err >= 0); sl@0: sl@0: //"CREATE VIRTUAL TABLE" statement not supported sl@0: err = db.Exec(_L("CREATE VIRTUAL TABLE V1 USING FTS3(ColOne TEXT, ColTwo DATETIME)")); sl@0: TPtrC msg = db.LastErrorMessage(); sl@0: TheTest.Printf(_L("*** \"CREATE VIRTUAL TABLE\" expected failure, msg=\"%S\", err=%d\r\n"), &msg, err); sl@0: TEST(err != KErrNone); sl@0: sl@0: err = db.Exec(_L("CREATE TRIGGER T1 AFTER INSERT ON A BEGIN INSERT INTO B VALUES(new.I1, new.I2); END;")); sl@0: TEST(err >= 0); sl@0: err = db.Exec(_L("DROP TRIGGER T1")); sl@0: TEST(err >= 0); sl@0: err = db.Exec(_L("CREATE TEMP TRIGGER T2 AFTER UPDATE OF I1 ON A BEGIN UPDATE B SET I1 = new.I1; END;")); sl@0: TEST(err >= 0); sl@0: err = db.Exec(_L("DROP TRIGGER T2")); sl@0: TEST(err >= 0); sl@0: sl@0: err = db.Exec(_L("CREATE VIEW V1 AS SELECT * FROM A")); sl@0: TEST(err >= 0); sl@0: err = db.Exec(_L("DROP VIEW V1")); sl@0: TEST(err >= 0); sl@0: err = db.Exec(_L("CREATE TEMP VIEW V2 AS SELECT * FROM A")); sl@0: TEST(err >= 0); sl@0: err = db.Exec(_L("DROP VIEW V2")); sl@0: TEST(err >= 0); sl@0: sl@0: err = db.Exec(_L("CREATE INDEX Idx1 ON A(I1)")); sl@0: TEST(err >= 0); sl@0: err = db.Exec(_L("ANALYZE A")); sl@0: TEST(err >= 0); sl@0: err = db.Exec(_L("DROP INDEX Idx1")); sl@0: TEST(err >= 0); sl@0: err = db.Exec(_L("CREATE INDEX Idx2 ON B(I1)")); sl@0: TEST(err >= 0); sl@0: err = db.Exec(_L("DROP INDEX Idx2")); sl@0: TEST(err >= 0); sl@0: sl@0: err = db.Exec(_L("DROP TABLE B")); sl@0: TEST(err >= 0); sl@0: err = db.Exec(_L("DROP TABLE A")); sl@0: TEST(err >= 0); sl@0: sl@0: db.Close(); sl@0: (void)RSqlDatabase::Delete(KTestDbName2); sl@0: } sl@0: sl@0: /** sl@0: @SYMTestCaseID SYSLIB-SQL-CT-1646 sl@0: @SYMTestCaseDesc Testing database operations on a secure database. sl@0: The test application's capabilities allow read/write access to the test secure database. sl@0: Verify that any other kind of a database operation will fail with KErrPermissionDenied error. sl@0: @SYMTestPriority High sl@0: @SYMTestActions Testing database operations on a secure database. sl@0: @SYMTestExpectedResults Test must not fail sl@0: @SYMREQ REQ5792 sl@0: REQ5793 sl@0: */ sl@0: void ReadWriteDatabaseTest() sl@0: { sl@0: RSqlDatabase db; sl@0: TInt err = TheDb.Open(KTestDbName); sl@0: TEST2(err, KErrNone); sl@0: sl@0: //Attempt to modify the database schema sl@0: err = TheDb.Exec(_L("CREATE TABLE C(FFF TEXT)")); sl@0: TEST2(err, KErrPermissionDenied); sl@0: err = TheDb.Exec(_L("CREATE TEMP TABLE TBL1(COL1 INTEGER, COL2 INTEGER)")); sl@0: TEST(err >= 0); sl@0: err = TheDb.Exec(_L("CREATE TEMP TRIGGER del1 AFTER DELETE ON TBL1 BEGIN DELETE FROM A; END;")); sl@0: TEST(err >= 0); sl@0: err = TheDb.Exec(_L("DROP TRIGGER del1")); sl@0: TEST(err >= 0); sl@0: err = TheDb.Exec(_L("CREATE TEMP VIEW V1 AS SELECT * FROM TBL1")); sl@0: TEST(err >= 0); sl@0: err = TheDb.Exec(_L("DROP VIEW V1")); sl@0: TEST(err >= 0); sl@0: err = TheDb.Exec(_L("CREATE INDEX I1 ON TBL1(COL2)")); sl@0: TEST(err >= 0); sl@0: err = TheDb.Exec(_L("DROP INDEX I1")); sl@0: TEST(err >= 0); sl@0: err = TheDb.Exec(_L("DROP TABLE TBL1")); sl@0: TEST(err >= 0); sl@0: err = TheDb.Exec(_L("ANALYZE A")); sl@0: TEST2(err, KErrPermissionDenied); sl@0: err = TheDb.Exec(_L("CREATE VIEW V2 AS SELECT * FROM A")); sl@0: TEST2(err, KErrPermissionDenied); sl@0: //Attempt to update the user data (but it includes a READ operation) sl@0: err = TheDb.Exec(_L("UPDATE A SET F1 = 11 WHERE F1 = 1")); sl@0: TEST(err >= 0); sl@0: //Attempt to update the user data (unconditional UPDATE, no READ operations) sl@0: err = TheDb.Exec(_L("UPDATE A SET F1 = 11")); sl@0: TEST(err >= 0); sl@0: //Attempt to delete the user data (but it includes a READ operation) sl@0: err = TheDb.Exec(_L("DELETE FROM B WHERE F2 = 2")); sl@0: TEST(err >= 0); sl@0: //Attempt to delete the user data (unconditional DELETE, no READ operations) sl@0: err = TheDb.Exec(_L("DELETE FROM A")); sl@0: TEST(err >= 0); sl@0: //Restore the deleted table A sl@0: err = TheDb.Exec(_L("INSERT INTO A(F1,B1) VALUES(1,x'41414141414141414141');INSERT INTO A(F1,B1) VALUES(2,x'42424242424242424242');INSERT INTO A(F1,B1) VALUES(3,x'43434343434343434343');INSERT INTO A(F1,B1) VALUES(4,x'44444444444444444444');")); sl@0: TEST(err >= 0); sl@0: //Restore the deleted record in table B sl@0: err = TheDb.Exec(_L("INSERT INTO B(F2, F3, B2) VALUES(2, 'ABC', x'45454545454545454545');")); sl@0: TEST2(err, 1); sl@0: //Attempt to insert new user data sl@0: err = TheDb.Exec(_L("INSERT INTO B(F2, F3, B2) VALUES(6, 'GHI', x'47474747474747474747');")); sl@0: TEST2(err, 1); sl@0: //Attempt to read the user data sl@0: RSqlStatement stmt; sl@0: err = stmt.Prepare(TheDb, _L("SELECT A.F1 FROM B,A WHERE A.F1 = B.F2")); sl@0: TEST2(err, KErrNone); sl@0: //ColumnCount() has no capabilities assigned sl@0: TInt colCnt = stmt.ColumnCount(); sl@0: TEST2(colCnt, 1); sl@0: //DeclaredColumnType() has no capabilities assigned sl@0: TSqlColumnType colType; sl@0: err = stmt.DeclaredColumnType(0, colType); sl@0: TEST2(err, KErrNone); sl@0: TEST2(colType, ESqlInt); sl@0: err = stmt.Next(); sl@0: TEST2(err, KSqlAtRow); sl@0: RDebug::Print(_L("Value=%d\r\n"), stmt.ColumnInt(0)); sl@0: err = stmt.Next(); sl@0: TEST2(err, KSqlAtRow); sl@0: RDebug::Print(_L("Value=%d\r\n"), stmt.ColumnInt(0)); sl@0: stmt.Close(); sl@0: //Attempt to read the system data sl@0: err = stmt.Prepare(TheDb, _L("SELECT * FROM SQLITE_MASTER")); sl@0: TEST2(err, KErrNone); sl@0: err = stmt.Next(); sl@0: TEST2(err, KSqlAtRow); sl@0: TPtrC p; sl@0: err = stmt.ColumnText(0, p); sl@0: TEST2(err, KErrNone); sl@0: RDebug::Print(_L("Value=%S\r\n"), &p); sl@0: stmt.Close(); sl@0: sl@0: NonSecureDbTest(); sl@0: sl@0: TheDb.Close(); sl@0: } sl@0: sl@0: /** sl@0: @SYMTestCaseID SYSLIB-SQL-UT-4097 sl@0: @SYMTestCaseDesc Testing incremental blob reads and writes on a secure database. sl@0: The test application's capabilities allow read and write access to the blobs. sl@0: Verify that both reads and writes are allowed. sl@0: @SYMTestPriority Medium sl@0: @SYMTestActions Testing incremental blob reads and writes on a secure database. sl@0: @SYMTestExpectedResults Test must not fail sl@0: @SYMREQ REQ5794 sl@0: */ sl@0: void ReadWriteBlobTestL() sl@0: { sl@0: // Current database data: sl@0: // TABLE A: {1, x'41414141414141414141'}, {2, x'42424242424242424242'}, {3, x'43434343434343434343'}, {4, x'44444444444444444444'} sl@0: // TABLE B: {4, "DEF", x'46464646464646464646'} <- ROWID = 2, {2, "ABC", x'45454545454545454545'} <- ROWID = 3, {6, "GHI", x'47474747474747474747'} <- ROWID = 4 sl@0: sl@0: RSqlDatabase db; sl@0: TInt err = TheDb.Open(KTestDbName); sl@0: TEST2(err, KErrNone); sl@0: sl@0: // Attempt to read the blobs in tables A and B sl@0: RSqlBlobReadStream rdStrm; sl@0: CleanupClosePushL(rdStrm); sl@0: TBuf8<20> data; sl@0: TRAP(err, rdStrm.OpenL(TheDb, _L("A"), _L("B1"), 1)); sl@0: TEST2(err, KErrNone); sl@0: TRAP(err, rdStrm.ReadL(data, 3)); sl@0: TEST2(err, KErrNone); sl@0: TEST(data.Compare(_L8("AAA")) == 0); sl@0: rdStrm.Close(); sl@0: TRAP(err, rdStrm.OpenL(TheDb, _L("B"), _L("B2"), 3)); sl@0: TEST2(err, KErrNone); sl@0: TRAP(err, rdStrm.ReadL(data, 10)); sl@0: TEST2(err, KErrNone); sl@0: TEST(data.Compare(_L8("EEEEEEEEEE")) == 0); sl@0: CleanupStack::PopAndDestroy(&rdStrm); sl@0: sl@0: HBufC8* wholeBuf = TSqlBlob::GetLC(TheDb, _L("A"), _L("B1"), 4); sl@0: TEST(wholeBuf->Des().Compare(_L8("DDDDDDDDDD")) == 0); sl@0: CleanupStack::PopAndDestroy(wholeBuf); sl@0: wholeBuf = TSqlBlob::GetLC(TheDb, _L("B"), _L("B2"), 2); sl@0: TEST(wholeBuf->Des().Compare(_L8("FFFFFFFFFF")) == 0); sl@0: CleanupStack::PopAndDestroy(wholeBuf); sl@0: sl@0: HBufC8* buf = HBufC8::NewLC(10); sl@0: TPtr8 bufPtr(buf->Des()); sl@0: err = TSqlBlob::Get(TheDb, _L("A"), _L("B1"), bufPtr, 2); sl@0: TEST2(err, KErrNone); sl@0: TEST(bufPtr.Compare(_L8("BBBBBBBBBB")) == 0); sl@0: err = TSqlBlob::Get(TheDb, _L("B"), _L("B2"), bufPtr, 4); sl@0: TEST2(err, KErrNone); sl@0: TEST(bufPtr.Compare(_L8("GGGGGGGGGG")) == 0); sl@0: CleanupStack::PopAndDestroy(buf); sl@0: sl@0: // Attempt to write the blobs in tables A and B sl@0: RSqlBlobWriteStream wrStrm; sl@0: CleanupClosePushL(wrStrm); sl@0: TRAP(err, wrStrm.OpenL(TheDb, _L("A"), _L("B1"), 1)); sl@0: TEST2(err, KErrNone); sl@0: TRAP(err, wrStrm.WriteL(_L8("ZZZ"))); sl@0: TEST2(err, KErrNone); sl@0: wrStrm.Close(); sl@0: TRAP(err, wrStrm.OpenL(TheDb, _L("B"), _L("B2"), 2)); sl@0: TEST2(err, KErrNone); sl@0: TRAP(err, wrStrm.WriteL(_L8("XXXXXXXXX"))); sl@0: TEST2(err, KErrNone); sl@0: CleanupStack::PopAndDestroy(&wrStrm); sl@0: sl@0: TRAP(err, TSqlBlob::SetL(TheDb, _L("A"), _L("B1"), _L8("UUUUUUUU"), 4)); sl@0: TEST2(err, KErrNone); sl@0: TRAP(err, TSqlBlob::SetL(TheDb, _L("B"), _L("B2"), _L8("TT"), 2)); sl@0: TEST2(err, KErrNone); sl@0: sl@0: // SQLite and system tables sl@0: sl@0: // Attempt to read from and write to the SQLite master table - sl@0: // reads should be permitted because read capability is enough for this, sl@0: // writes should not be permitted because schema capability is required for this sl@0: CleanupClosePushL(rdStrm); sl@0: TRAP(err, rdStrm.OpenL(TheDb, _L("sqlite_master"), _L("tbl_name"), 1)); // TEXT column sl@0: TEST2(err, KErrNone); sl@0: TRAP(err, rdStrm.ReadL(data, 1)); sl@0: TEST2(err, KErrNone); sl@0: CleanupStack::PopAndDestroy(&rdStrm); sl@0: sl@0: wholeBuf = TSqlBlob::GetLC(TheDb, _L("sqlite_master"), _L("tbl_name"), 1); sl@0: TEST(wholeBuf->Length() > 0); sl@0: CleanupStack::PopAndDestroy(wholeBuf); sl@0: sl@0: buf = HBufC8::NewLC(100); sl@0: bufPtr.Set(buf->Des()); sl@0: err = TSqlBlob::Get(TheDb, _L("sqlite_master"), _L("tbl_name"), bufPtr, 1); sl@0: TEST2(err, KErrNone); sl@0: TEST(bufPtr.Length() > 0); sl@0: CleanupStack::PopAndDestroy(buf); sl@0: sl@0: CleanupClosePushL(wrStrm); sl@0: TRAP(err, wrStrm.OpenL(TheDb, _L("sqlite_master"), _L("tbl_name"), 1)); sl@0: TEST2(err, KErrPermissionDenied); sl@0: CleanupStack::PopAndDestroy(&wrStrm); sl@0: sl@0: TRAP(err, TSqlBlob::SetL(TheDb, _L("sqlite_master"), _L("tbl_name"), _L8("VVVV"), 1)); sl@0: TEST2(err, KErrPermissionDenied); sl@0: sl@0: // Attempt to read from and write to the system tables - neither reads nor writes should be permitted sl@0: CleanupClosePushL(rdStrm); sl@0: TRAP(err, rdStrm.OpenL(TheDb, _L("symbian_security"), _L("PolicyData"), 1)); // BLOB column sl@0: TEST2(err, KErrPermissionDenied); sl@0: CleanupStack::PopAndDestroy(&rdStrm); sl@0: sl@0: TRAP(err, wholeBuf = TSqlBlob::GetLC(TheDb, _L("symbian_security"), _L("PolicyData"), 1)); sl@0: TEST2(err, KErrPermissionDenied); sl@0: sl@0: buf = HBufC8::NewLC(100); sl@0: bufPtr.Set(buf->Des()); sl@0: err = TSqlBlob::Get(TheDb, _L("symbian_security"), _L("PolicyData"), bufPtr, 1); sl@0: TEST2(err, KErrPermissionDenied); sl@0: CleanupStack::PopAndDestroy(buf); sl@0: sl@0: CleanupClosePushL(wrStrm); sl@0: TRAP(err, wrStrm.OpenL(TheDb, _L("symbian_security"), _L("PolicyData"), 1)); sl@0: TEST2(err, KErrPermissionDenied); sl@0: CleanupStack::PopAndDestroy(&wrStrm); sl@0: sl@0: TRAP(err, TSqlBlob::SetL(TheDb, _L("symbian_security"), _L("PolicyData"), _L8("VVVV"), 1)); sl@0: TEST2(err, KErrPermissionDenied); sl@0: sl@0: TheDb.Close(); sl@0: } sl@0: sl@0: void DoTestsL() sl@0: { sl@0: TheTest.Start(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1646 Read-write database access test ")); sl@0: ReadWriteDatabaseTest(); sl@0: sl@0: TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-4097 Read-write blob access test")); sl@0: ReadWriteBlobTestL(); sl@0: sl@0: RestoreOriginalDb(); // the same db is used by the other t_security test exe's sl@0: } sl@0: sl@0: TInt E32Main() sl@0: { sl@0: TheTest.Title(); sl@0: sl@0: CTrapCleanup* tc = CTrapCleanup::New(); sl@0: sl@0: __UHEAP_MARK; sl@0: sl@0: TRAPD(err, DoTestsL()); sl@0: TEST2(err, KErrNone); sl@0: sl@0: __UHEAP_MARKEND; sl@0: sl@0: TheTest.End(); sl@0: TheTest.Close(); sl@0: sl@0: delete tc; sl@0: sl@0: User::Heap().Check(); sl@0: return KErrNone; sl@0: }