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