Symaptic: os/persistentdata/persistentstorage/sql/TEST/t

     1 // Copyright (c) 2006-2009 Nokia Corporation and/or its subsidiary(-ies).

     2 // All rights reserved.

     3 // This component and the accompanying materials are made available

     4 // under the terms of "Eclipse Public License v1.0"

     5 // which accompanies this distribution, and is available

     6 // at the URL "http://www.eclipse.org/legal/epl-v10.html".

     7 //

     8 // Initial Contributors:

     9 // Nokia Corporation - initial contribution.

    10 //

    11 // Contributors:

    12 //

    13 // Description:

    14 // t_sqlsecurity5 application has capabilities allowing schema access to the test database

    15 //

    16 //

    18 #include <e32test.h>

    19 #include <bautils.h>

    20 #include <sqldb.h>

    22 ///////////////////////////////////////////////////////////////////////////////////////

    23 //The test database has:

    24 //  SCHEMA database policy: ECapabilityReadDeviceData, ECapabilityWriteUserData, ECapabilityReadUserData

    25 //  WRITE database policy:  ECapabilityWriteUserData

    26 //  READ database policy:   ECapabilityReadUserData

    27 //

    28 //Database tables:

    29 //  TABLE A(F1 INTEGER, B1 BLOB)

    30 //  TABLE B(F2 INTEGER, F3 TEXT, B2 BLOB)

    31 //

    32 //Database data:

    33 //  TABLE A: {1, x'41414141414141414141'}, {2, x'42424242424242424242'}, {3, x'43434343434343434343'}, {4, x'44444444444444444444'}

    34 //  TABLE B: {2, "ABC", x'45454545454545454545'}, {4, "DEF", x'46464646464646464646'}

    36 ///////////////////////////////////////////////////////////////////////////////////////

    38 #define UNUSED_VAR(a) (a) = (a)

    40 RSqlDatabase TheDb;

    41 RTest TheTest(_L("t_sqlsecurity5 test"));

    43 _LIT(KTestDbName, "c:[21212125]t_ab.db");

    44 _LIT(KTestDbName2, "c:\\test\\t_sqlsecurity5_2.db");

    46 ///////////////////////////////////////////////////////////////////////////////////////

    48 void DeleteTestDb2()

    49 	{

    50 	TheDb.Close();

    51 	(void)RSqlDatabase::Delete(KTestDbName2);

    52 	}

    54 ///////////////////////////////////////////////////////////////////////////////////////

    55 //Restore original test database function

    56 void RestoreOriginalDb()

    57 	{

    58 	TheDb.Close();

    59 	TheDb.Open(KTestDbName);

    61 	// Delete and restore the content of table A (unconditional DELETE, no READ operations)

    62 	TheDb.Exec(_L("DELETE FROM A"));

    63 	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');"));

    65 	// Delete and restore the content of table B (unconditional DELETE, no READ operations)

    66 	TheDb.Exec(_L("DELETE FROM B"));

    67 	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');"));

    69 	TheDb.Close();

    70 	}

    72 ///////////////////////////////////////////////////////////////////////////////////////

    73 //Test macros and functions

    74 void Check1(TInt aValue, TInt aLine)

    75 	{

    76 	if(!aValue)

    77 		{

    78 		DeleteTestDb2();

    79 		RestoreOriginalDb();

    80 		RDebug::Print(_L("*** Line %d\r\n"), aLine);

    81 		TheTest(EFalse, aLine);

    82 		}

    83 	}

    84 void Check2(TInt aValue, TInt aExpected, TInt aLine)

    85 	{

    86 	if(aValue != aExpected)

    87 		{

    88 		DeleteTestDb2();

    89 		RestoreOriginalDb();

    90 		RDebug::Print(_L("*** Line %d, Expected error: %d, got: %d\r\n"), aLine, aExpected, aValue);

    91 		TheTest(EFalse, aLine);

    92 		}

    93 	}

    94 #define TEST(arg) ::Check1((arg), __LINE__)

    95 #define TEST2(aValue, aExpected) ::Check2(aValue, aExpected, __LINE__)

    97 ///////////////////////////////////////////////////////////////////////////////////////

    99 /**

   100 @SYMTestCaseID			SYSLIB-SQL-CT-1647

   101 @SYMTestCaseDesc		Testing database operations on a secure database.

   102 						The test application's capabilities allow schema access to the test secure database.

   103 						Verify that any other kind of a database operation will pass.

   104 @SYMTestPriority		High

   105 @SYMTestActions			Testing database operations on a secure database.

   106 @SYMTestExpectedResults Test must not fail

   107 @SYMREQ					REQ5792

   108                         REQ5793

   109 */

   110 void SchemaSecurityTest()

   111 	{

   112 	TInt err = TheDb.Open(KTestDbName);

   113 	TEST2(err, KErrNone);

   115 	//Attempt to modify the database schema

   116 	err = TheDb.Exec(_L("CREATE TABLE IF NOT EXISTS C(FFF TEXT)"));

   117 	TEST(err >= 0);

   118 	//Index operations

   119     err = TheDb.Exec(_L("CREATE INDEX Cidx ON C(FFF)"));

   120     TEST(err >= 0);

   121     err = TheDb.Exec(_L("ANALYZE C"));

   122     TEST(err >= 0);

   123     err = TheDb.Exec(_L("DROP INDEX Cidx"));

   124     TEST(err >= 0);

   125     //Trigger operations

   126     err = TheDb.Exec(_L("CREATE TRIGGER T1 AFTER INSERT ON C BEGIN INSERT INTO B VALUES(1, 2); END;"));

   127     TEST(err >= 0);

   128     err = TheDb.Exec(_L("DROP TRIGGER T1"));

   129     TEST(err >= 0);

   130     //View operations

   131     err = TheDb.Exec(_L("CREATE VIEW V1 AS SELECT * FROM C"));

   132     TEST(err >= 0);

   133     err = TheDb.Exec(_L("DROP VIEW V1"));

   134     TEST(err >= 0);

   135 	//Attempt to update the user data (but it includes a READ operation)

   136 	err = TheDb.Exec(_L("UPDATE A SET F1 = 11 WHERE F1 = 1"));

   137 	TEST(err >= 0);

   138 	//Attempt to update the user data (unconditional UPDATE, no READ operations)

   139 	err = TheDb.Exec(_L("UPDATE A SET F1 = 11"));

   140 	TEST(err >= 0);

   141 	//Attempt to delete the user data (but it includes a READ operation)

   142 	err = TheDb.Exec(_L("DELETE FROM B WHERE F2 = 2"));

   143 	TEST(err >= 0);

   144 	//Attempt to delete the user data (unconditional DELETE, no READ operations)

   145 	err = TheDb.Exec(_L("DELETE FROM A"));

   146 	TEST(err >= 0);

   147 	//Restore the deleted table A

   148 	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');"));

   149 	TEST(err >= 0);

   150 	//Restore the deleted record in table B

   151 	err = TheDb.Exec(_L("INSERT INTO B(F2, F3, B2) VALUES(2, 'ABC', x'45454545454545454545');"));

   152 	TEST2(err, 1);

   153 	//Attempt to insert new user data

   154 	err = TheDb.Exec(_L("INSERT INTO B(F2, F3, B2) VALUES(6, 'GHI', x'47474747474747474747');"));

   155 	TEST2(err, 1);

   156 	//Attempt to read the user data

   157 	RSqlStatement stmt;

   158 	err = stmt.Prepare(TheDb, _L("SELECT A.F1 FROM B,A WHERE A.F1 = B.F2"));

   159 	TEST2(err, KErrNone);

   160 	//ColumnCount() has no capabilities assigned

   161 	TInt colCnt = stmt.ColumnCount();

   162 	TEST2(colCnt, 1);

   163 	//

   164 	stmt.Close();

   165 	TheDb.Close();

   166 	}

   168 /**

   169 @SYMTestCaseID			SYSLIB-SQL-UT-4037

   170 @SYMTestCaseDesc		RSqlStatement::DeclaredColumnType() - security test.

   171 						The test calls RSqlStatement::DeclaredColumnType() on a secure database.

   172 						It should be possible to retrieve the declared column type without problems.

   173 @SYMTestPriority		High

   174 @SYMTestActions			RSqlStatement::DeclaredColumnType() - security test.

   175 @SYMTestExpectedResults Test must not fail

   176 @SYMREQ					REQ5794

   177 */

   178 void DeclaredColumnTypeTest()

   179 	{

   180 	TInt err = TheDb.Open(KTestDbName);

   181 	TEST2(err, KErrNone);

   182 	RSqlStatement stmt;

   183 	err = stmt.Prepare(TheDb, _L("SELECT A.F1 FROM B,A WHERE A.F1 = B.F2"));

   184 	TEST2(err, KErrNone);

   185 	//DeclaredColumnType() has no capabilities assigned

   186 	TSqlColumnType colType;

   187 	err = stmt.DeclaredColumnType(0, colType);

   188 	TEST2(err, KErrNone);

   189 	TEST2(colType, ESqlInt);

   190 	err = stmt.Next();

   191 	TEST2(err, KSqlAtRow);

   192 	RDebug::Print(_L("Value=%d\r\n"), stmt.ColumnInt(0));

   193 	err = stmt.Next();

   194 	TEST2(err, KSqlAtRow);

   195 	RDebug::Print(_L("Value=%d\r\n"), stmt.ColumnInt(0));

   196 	stmt.Close();

   197 	//Attempt to read the system data

   198 	err = stmt.Prepare(TheDb, _L("SELECT * FROM SQLITE_MASTER"));

   199 	TEST2(err, KErrNone);

   200 	err = stmt.Next();

   201 	TEST2(err, KSqlAtRow);

   202 	TPtrC p;

   203 	err = stmt.ColumnText(0, p);

   204 	TEST2(err, KErrNone);

   205 	RDebug::Print(_L("Value=%S\r\n"), &p);

   206 	//

   207 	stmt.Close();

   208 	TheDb.Close();

   209 	}

   211 /**

   212 @SYMTestCaseID			SYSLIB-SQL-UT-4046

   213 @SYMTestCaseDesc		RSqlDatabase::Size(TSize&), platsec test.

   214 						The test verifies that RSqlDatabase::Size(TSize&) can be called

   215 						on the main or on an attached database no matter what the client capabilities are.

   216 @SYMTestPriority		Medium

   217 @SYMTestActions			RSqlDatabase::Size(TSize&), platsec test.

   218 @SYMTestExpectedResults Test must not fail

   219 @SYMREQ					REQ10407

   220 */

   221 void Size2Test()

   222 	{

   223 	TInt err = TheDb.Open(KTestDbName);

   224 	TEST2(err, KErrNone);

   225 	//Size(TSize&) has no capabilities assigned

   226 	RSqlDatabase::TSize size;

   227 	err = TheDb.Size(size);

   228 	TEST2(err, KErrNone);

   229 	//Attach and repeat the test again

   230 	_LIT(KAttachDbName, "Db");

   231 	err = TheDb.Attach(KTestDbName, KAttachDbName);

   232 	TEST2(err, KErrNone);

   233 	TEST(size.iSize > 0);

   234 	TEST(size.iFree >= 0);

   235 	err = TheDb.Size(size, KAttachDbName);

   236 	TEST2(err, KErrNone);

   237 	TEST(size.iSize > 0);

   238 	TEST(size.iFree >= 0);

   239 	err = TheDb.Detach(KAttachDbName);

   240 	TEST2(err, KErrNone);

   241 	TheDb.Close();

   242 	}

   244 /**

   245 @SYMTestCaseID			SYSLIB-SQL-UT-4047

   246 @SYMTestCaseDesc		RSqlDatabase::Compact(), platsec test.

   247 						The test verifies that RSqlDatabase::Compact() can be called

   248 						on the main or on an attached database no matter what the client capabilities are.

   249 @SYMTestPriority		Medium

   250 @SYMTestActions			RSqlDatabase::Compact(), platsec test.

   251 @SYMTestExpectedResults Test must not fail

   252 @SYMREQ					REQ10405

   253 */

   254 void CompactTest()

   255 	{

   256 	TInt err = TheDb.Open(KTestDbName);

   257 	TEST2(err, KErrNone);

   259 	err = TheDb.Compact(RSqlDatabase::EMaxCompaction);

   260 	TEST(err >= 0);

   262 	TRequestStatus stat;

   263 	TheDb.Compact(RSqlDatabase::EMaxCompaction, stat);

   264 	User::WaitForRequest(stat);

   265 	TEST(stat.Int() >= 0);

   267 	TheDb.Close();

   269 	err = TheDb.Create(KTestDbName2);

   270 	TEST2(err, KErrNone);

   271 	_LIT(KDbName, "Db");

   272 	err = TheDb.Attach(KTestDbName, KDbName);

   273 	TEST2(err, KErrNone);

   275 	err = TheDb.Compact(RSqlDatabase::EMaxCompaction, KDbName);

   276 	TEST(err >= 0);

   278 	TheDb.Compact(RSqlDatabase::EMaxCompaction, stat, KDbName);

   279 	User::WaitForRequest(stat);

   280 	TEST(stat.Int() >= 0);

   282 	err = TheDb.Detach(KDbName);

   283 	TheDb.Close();

   284 	(void)RSqlDatabase::Delete(KTestDbName2);

   285 	}

   287 /**

   288 @SYMTestCaseID			SYSLIB-SQL-UT-4098

   289 @SYMTestCaseDesc		Testing incremental blob reads and writes on a secure database.

   290 						The test application's schema capabilities allow read and write access to the blobs.

   291 						Verify that both reads and writes are allowed and also that database operations

   292 						that require schema capability are allowed.

   293 @SYMTestPriority		Medium

   294 @SYMTestActions			Testing incremental blob reads and writes and schema operations on a secure database.

   295 @SYMTestExpectedResults Test must not fail

   296 @SYMREQ					REQ5794

   297 */

   298 void SchemaBlobTestL()

   299 	{

   300 	// Current database data:

   301 	// TABLE A: {1, x'41414141414141414141'}, {2, x'42424242424242424242'}, {3, x'43434343434343434343'}, {4, x'44444444444444444444'}

   302 	// TABLE B: {4, "DEF", x'46464646464646464646'} <- ROWID = 2, {2, "ABC", x'45454545454545454545'} <- ROWID = 3, {6, "GHI", x'47474747474747474747'} <- ROWID = 4

   304 	RSqlDatabase db;

   305 	TInt err = db.Open(KTestDbName);

   306 	TEST2(err, KErrNone);

   308 	// Attempt to read the blobs in tables A and B

   309 	RSqlBlobReadStream rdStrm;

   310 	CleanupClosePushL(rdStrm);

   311 	TBuf8<20> data;

   312 	TRAP(err, rdStrm.OpenL(db, _L("A"), _L("B1"), 2));

   313 	TEST2(err, KErrNone);

   314 	TRAP(err, rdStrm.ReadL(data, 7));

   315 	TEST2(err, KErrNone);

   316 	TEST(data.Compare(_L8("BBBBBBB")) == 0);

   317 	rdStrm.Close();

   318 	TRAP(err, rdStrm.OpenL(db, _L("B"), _L("B2"), 2));

   319 	TEST2(err, KErrNone);

   320 	TRAP(err, rdStrm.ReadL(data, 9));

   321 	TEST2(err, KErrNone);

   322 	TEST(data.Compare(_L8("FFFFFFFFF")) == 0);

   323 	CleanupStack::PopAndDestroy(&rdStrm);

   325 	HBufC8* wholeBuf = TSqlBlob::GetLC(db, _L("A"), _L("B1"), 1);

   326 	TEST(wholeBuf->Des().Compare(_L8("AAAAAAAAAA")) == 0);

   327 	CleanupStack::PopAndDestroy(wholeBuf);

   328 	wholeBuf = TSqlBlob::GetLC(db, _L("B"), _L("B2"), 4);

   329 	TEST(wholeBuf->Des().Compare(_L8("GGGGGGGGGG")) == 0);

   330 	CleanupStack::PopAndDestroy(wholeBuf);

   332 	HBufC8* buf = HBufC8::NewLC(10);

   333 	TPtr8 bufPtr(buf->Des());

   334 	err = TSqlBlob::Get(db, _L("A"), _L("B1"), bufPtr, 3);

   335 	TEST2(err, KErrNone);

   336 	TEST(bufPtr.Compare(_L8("CCCCCCCCCC")) == 0);

   337 	err = TSqlBlob::Get(db, _L("B"), _L("B2"), bufPtr, 2);

   338 	TEST2(err, KErrNone);

   339 	TEST(bufPtr.Compare(_L8("FFFFFFFFFF")) == 0);

   340 	CleanupStack::PopAndDestroy(buf);

   342 	// Attempt to write the blobs in tables A and B

   343 	RSqlBlobWriteStream wrStrm;

   344 	CleanupClosePushL(wrStrm);

   345 	TRAP(err, wrStrm.OpenL(db, _L("A"), _L("B1"), 1));

   346 	TEST2(err, KErrNone);

   347 	TRAP(err, wrStrm.WriteL(_L8("ZZZ")));

   348 	TEST2(err, KErrNone);

   349 	wrStrm.Close();

   350 	TRAP(err, wrStrm.OpenL(db, _L("B"), _L("B2"), 3));

   351 	TEST2(err, KErrNone);

   352 	TRAP(err, wrStrm.WriteL(_L8("WWWWWWWWWW")));

   353 	TEST2(err, KErrNone);

   354 	CleanupStack::PopAndDestroy(&wrStrm);

   356 	TRAP(err, TSqlBlob::SetL(db, _L("A"), _L("B1"), _L8("UUUUUUUU"), 4));

   357 	TEST2(err, KErrNone);

   358 	TRAP(err, TSqlBlob::SetL(db, _L("B"), _L("B2"), _L8("SSS"), 4));

   359 	TEST2(err, KErrNone);

   361 	// SQLite and system tables

   363 	// Attempt to read from and write to the SQLite master table -

   364 	// reads should be permitted because schema capability is enough for this,

   365 	// writes should be permitted because schema capability is enough for this

   366 	CleanupClosePushL(rdStrm);

   367 	TRAP(err, rdStrm.OpenL(db, _L("sqlite_master"), _L("tbl_name"), 1)); // TEXT column

   368 	TEST2(err, KErrNone);

   369 	TRAP(err, rdStrm.ReadL(data, 1));

   370 	TEST2(err, KErrNone);

   371 	CleanupStack::PopAndDestroy(&rdStrm);

   373 	wholeBuf = TSqlBlob::GetLC(db, _L("sqlite_master"), _L("tbl_name"), 1);

   374 	TEST(wholeBuf->Length() > 0);

   375 	CleanupStack::PopAndDestroy(wholeBuf);

   377 	buf = HBufC8::NewLC(100);

   378 	bufPtr.Set(buf->Des());

   379 	err = TSqlBlob::Get(db, _L("sqlite_master"), _L("tbl_name"), bufPtr, 1);

   380 	TEST2(err, KErrNone);

   381 	TEST(bufPtr.Length() > 0);

   382 	CleanupStack::PopAndDestroy(buf);

   384 	CleanupClosePushL(wrStrm);

   385 	TRAP(err, wrStrm.OpenL(db, _L("sqlite_master"), _L("tbl_name"), 1));

   386 	TEST2(err, KErrNone);

   387 	TRAP(err, wrStrm.WriteL(_L8("myTableName")));

   388 	TEST2(err, KErrNone);

   389 	CleanupStack::PopAndDestroy(&wrStrm);

   391 	TRAP(err, TSqlBlob::SetL(db, _L("sqlite_master"), _L("tbl_name"), _L8("myTableName"), 1));

   392 	TEST2(err, KErrNone);

   394 	// Attempt to read from and write to the system tables - neither reads nor writes should be permitted

   395 	CleanupClosePushL(rdStrm);

   396 	TRAP(err, rdStrm.OpenL(db, _L("symbian_security"), _L("PolicyData"), 1)); // BLOB column

   397 	TEST2(err, KErrPermissionDenied);

   398 	CleanupStack::PopAndDestroy(&rdStrm);

   400 	TRAP(err, wholeBuf = TSqlBlob::GetLC(db, _L("symbian_security"), _L("PolicyData"), 1));

   401 	TEST2(err, KErrPermissionDenied);

   403 	buf = HBufC8::NewLC(100);

   404 	bufPtr.Set(buf->Des());

   405 	err = TSqlBlob::Get(db, _L("symbian_security"), _L("PolicyData"), bufPtr, 1);

   406 	TEST2(err, KErrPermissionDenied);

   407 	CleanupStack::PopAndDestroy(buf);

   409 	CleanupClosePushL(wrStrm);

   410 	TRAP(err, wrStrm.OpenL(db, _L("symbian_security"), _L("PolicyData"), 1));

   411 	TEST2(err, KErrPermissionDenied);

   412 	CleanupStack::PopAndDestroy(&wrStrm);

   414 	TRAP(err, TSqlBlob::SetL(db, _L("symbian_security"), _L("PolicyData"), _L8("VVVV"), 1));

   415 	TEST2(err, KErrPermissionDenied);

   417 	db.Close();

   418 	}

   420 void DoTestsL()

   421 	{

   422 	TheTest.Start(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1647 Schema database access test "));

   423 	SchemaSecurityTest();

   425 	TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-4037 Declared column type test"));

   426 	DeclaredColumnTypeTest();

   428 	TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-4046 Size(TSize&) test"));

   429 	Size2Test();

   431 	TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-4047 Compact() test"));

   432 	CompactTest();

   434 	TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-4098 Schema blob access test"));

   435 	SchemaBlobTestL();

   437 	RestoreOriginalDb(); // the same db is used by the other t_security test exe's

   438 	}

   440 TInt E32Main()

   441 	{

   442 	TheTest.Title();

   444 	CTrapCleanup* tc = CTrapCleanup::New();

   446 	__UHEAP_MARK;

   448 	TRAPD(err, DoTestsL());

   449 	TEST2(err, KErrNone);

   451 	__UHEAP_MARKEND;

   453 	TheTest.End();

   454 	TheTest.Close();

   456 	delete tc;

   458 	User::Heap().Check();

   459 	return KErrNone;

   460 	}

author	sl
	Tue, 10 Jun 2014 14:32:02 +0200
changeset 1	260cb5ec6c19
permissions	-rw-r--r--