// 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_sqlsecurity5 application has capabilities allowing schema access to the test database
//
//
#include <e32test.h>
#include <bautils.h>
#include <sqldb.h>
///////////////////////////////////////////////////////////////////////////////////////
//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_sqlsecurity5 test"));
_LIT(KTestDbName, "c:[21212125]t_ab.db");
_LIT(KTestDbName2, "c:\\test\\t_sqlsecurity5_2.db");
///////////////////////////////////////////////////////////////////////////////////////
void DeleteTestDb2()
{
TheDb.Close();
(void)RSqlDatabase::Delete(KTestDbName2);
}
///////////////////////////////////////////////////////////////////////////////////////
//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)
{
DeleteTestDb2();
RestoreOriginalDb();
RDebug::Print(_L("*** Line %d\r\n"), aLine);
TheTest(EFalse, aLine);
}
}
void Check2(TInt aValue, TInt aExpected, TInt aLine)
{
if(aValue != aExpected)
{
DeleteTestDb2();
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__)
///////////////////////////////////////////////////////////////////////////////////////
/**
@SYMTestCaseID SYSLIB-SQL-CT-1647
@SYMTestCaseDesc Testing database operations on a secure database.
The test application's capabilities allow schema access to the test secure database.
Verify that any other kind of a database operation will pass.
@SYMTestPriority High
@SYMTestActions Testing database operations on a secure database.
@SYMTestExpectedResults Test must not fail
@SYMREQ REQ5792
REQ5793
*/
void SchemaSecurityTest()
{
TInt err = TheDb.Open(KTestDbName);
TEST2(err, KErrNone);
//Attempt to modify the database schema
err = TheDb.Exec(_L("CREATE TABLE IF NOT EXISTS C(FFF TEXT)"));
TEST(err >= 0);
//Index operations
err = TheDb.Exec(_L("CREATE INDEX Cidx ON C(FFF)"));
TEST(err >= 0);
err = TheDb.Exec(_L("ANALYZE C"));
TEST(err >= 0);
err = TheDb.Exec(_L("DROP INDEX Cidx"));
TEST(err >= 0);
//Trigger operations
err = TheDb.Exec(_L("CREATE TRIGGER T1 AFTER INSERT ON C BEGIN INSERT INTO B VALUES(1, 2); END;"));
TEST(err >= 0);
err = TheDb.Exec(_L("DROP TRIGGER T1"));
TEST(err >= 0);
//View operations
err = TheDb.Exec(_L("CREATE VIEW V1 AS SELECT * FROM C"));
TEST(err >= 0);
err = TheDb.Exec(_L("DROP VIEW V1"));
TEST(err >= 0);
//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);
//
stmt.Close();
TheDb.Close();
}
/**
@SYMTestCaseID SYSLIB-SQL-UT-4037
@SYMTestCaseDesc RSqlStatement::DeclaredColumnType() - security test.
The test calls RSqlStatement::DeclaredColumnType() on a secure database.
It should be possible to retrieve the declared column type without problems.
@SYMTestPriority High
@SYMTestActions RSqlStatement::DeclaredColumnType() - security test.
@SYMTestExpectedResults Test must not fail
@SYMREQ REQ5794
*/
void DeclaredColumnTypeTest()
{
TInt err = TheDb.Open(KTestDbName);
TEST2(err, KErrNone);
RSqlStatement stmt;
err = stmt.Prepare(TheDb, _L("SELECT A.F1 FROM B,A WHERE A.F1 = B.F2"));
TEST2(err, KErrNone);
//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();
TheDb.Close();
}
/**
@SYMTestCaseID SYSLIB-SQL-UT-4046
@SYMTestCaseDesc RSqlDatabase::Size(TSize&), platsec test.
The test verifies that RSqlDatabase::Size(TSize&) can be called
on the main or on an attached database no matter what the client capabilities are.
@SYMTestPriority Medium
@SYMTestActions RSqlDatabase::Size(TSize&), platsec test.
@SYMTestExpectedResults Test must not fail
@SYMREQ REQ10407
*/
void Size2Test()
{
TInt err = TheDb.Open(KTestDbName);
TEST2(err, KErrNone);
//Size(TSize&) has no capabilities assigned
RSqlDatabase::TSize size;
err = TheDb.Size(size);
TEST2(err, KErrNone);
//Attach and repeat the test again
_LIT(KAttachDbName, "Db");
err = TheDb.Attach(KTestDbName, KAttachDbName);
TEST2(err, KErrNone);
TEST(size.iSize > 0);
TEST(size.iFree >= 0);
err = TheDb.Size(size, KAttachDbName);
TEST2(err, KErrNone);
TEST(size.iSize > 0);
TEST(size.iFree >= 0);
err = TheDb.Detach(KAttachDbName);
TEST2(err, KErrNone);
TheDb.Close();
}
/**
@SYMTestCaseID SYSLIB-SQL-UT-4047
@SYMTestCaseDesc RSqlDatabase::Compact(), platsec test.
The test verifies that RSqlDatabase::Compact() can be called
on the main or on an attached database no matter what the client capabilities are.
@SYMTestPriority Medium
@SYMTestActions RSqlDatabase::Compact(), platsec test.
@SYMTestExpectedResults Test must not fail
@SYMREQ REQ10405
*/
void CompactTest()
{
TInt err = TheDb.Open(KTestDbName);
TEST2(err, KErrNone);
err = TheDb.Compact(RSqlDatabase::EMaxCompaction);
TEST(err >= 0);
TRequestStatus stat;
TheDb.Compact(RSqlDatabase::EMaxCompaction, stat);
User::WaitForRequest(stat);
TEST(stat.Int() >= 0);
TheDb.Close();
err = TheDb.Create(KTestDbName2);
TEST2(err, KErrNone);
_LIT(KDbName, "Db");
err = TheDb.Attach(KTestDbName, KDbName);
TEST2(err, KErrNone);
err = TheDb.Compact(RSqlDatabase::EMaxCompaction, KDbName);
TEST(err >= 0);
TheDb.Compact(RSqlDatabase::EMaxCompaction, stat, KDbName);
User::WaitForRequest(stat);
TEST(stat.Int() >= 0);
err = TheDb.Detach(KDbName);
TheDb.Close();
(void)RSqlDatabase::Delete(KTestDbName2);
}
/**
@SYMTestCaseID SYSLIB-SQL-UT-4098
@SYMTestCaseDesc Testing incremental blob reads and writes on a secure database.
The test application's schema capabilities allow read and write access to the blobs.
Verify that both reads and writes are allowed and also that database operations
that require schema capability are allowed.
@SYMTestPriority Medium
@SYMTestActions Testing incremental blob reads and writes and schema operations on a secure database.
@SYMTestExpectedResults Test must not fail
@SYMREQ REQ5794
*/
void SchemaBlobTestL()
{
// 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 = db.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(db, _L("A"), _L("B1"), 2));
TEST2(err, KErrNone);
TRAP(err, rdStrm.ReadL(data, 7));
TEST2(err, KErrNone);
TEST(data.Compare(_L8("BBBBBBB")) == 0);
rdStrm.Close();
TRAP(err, rdStrm.OpenL(db, _L("B"), _L("B2"), 2));
TEST2(err, KErrNone);
TRAP(err, rdStrm.ReadL(data, 9));
TEST2(err, KErrNone);
TEST(data.Compare(_L8("FFFFFFFFF")) == 0);
CleanupStack::PopAndDestroy(&rdStrm);
HBufC8* wholeBuf = TSqlBlob::GetLC(db, _L("A"), _L("B1"), 1);
TEST(wholeBuf->Des().Compare(_L8("AAAAAAAAAA")) == 0);
CleanupStack::PopAndDestroy(wholeBuf);
wholeBuf = TSqlBlob::GetLC(db, _L("B"), _L("B2"), 4);
TEST(wholeBuf->Des().Compare(_L8("GGGGGGGGGG")) == 0);
CleanupStack::PopAndDestroy(wholeBuf);
HBufC8* buf = HBufC8::NewLC(10);
TPtr8 bufPtr(buf->Des());
err = TSqlBlob::Get(db, _L("A"), _L("B1"), bufPtr, 3);
TEST2(err, KErrNone);
TEST(bufPtr.Compare(_L8("CCCCCCCCCC")) == 0);
err = TSqlBlob::Get(db, _L("B"), _L("B2"), bufPtr, 2);
TEST2(err, KErrNone);
TEST(bufPtr.Compare(_L8("FFFFFFFFFF")) == 0);
CleanupStack::PopAndDestroy(buf);
// Attempt to write the blobs in tables A and B
RSqlBlobWriteStream wrStrm;
CleanupClosePushL(wrStrm);
TRAP(err, wrStrm.OpenL(db, _L("A"), _L("B1"), 1));
TEST2(err, KErrNone);
TRAP(err, wrStrm.WriteL(_L8("ZZZ")));
TEST2(err, KErrNone);
wrStrm.Close();
TRAP(err, wrStrm.OpenL(db, _L("B"), _L("B2"), 3));
TEST2(err, KErrNone);
TRAP(err, wrStrm.WriteL(_L8("WWWWWWWWWW")));
TEST2(err, KErrNone);
CleanupStack::PopAndDestroy(&wrStrm);
TRAP(err, TSqlBlob::SetL(db, _L("A"), _L("B1"), _L8("UUUUUUUU"), 4));
TEST2(err, KErrNone);
TRAP(err, TSqlBlob::SetL(db, _L("B"), _L("B2"), _L8("SSS"), 4));
TEST2(err, KErrNone);
// SQLite and system tables
// Attempt to read from and write to the SQLite master table -
// reads should be permitted because schema capability is enough for this,
// writes should be permitted because schema capability is enough for this
CleanupClosePushL(rdStrm);
TRAP(err, rdStrm.OpenL(db, _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(db, _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(db, _L("sqlite_master"), _L("tbl_name"), bufPtr, 1);
TEST2(err, KErrNone);
TEST(bufPtr.Length() > 0);
CleanupStack::PopAndDestroy(buf);
CleanupClosePushL(wrStrm);
TRAP(err, wrStrm.OpenL(db, _L("sqlite_master"), _L("tbl_name"), 1));
TEST2(err, KErrNone);
TRAP(err, wrStrm.WriteL(_L8("myTableName")));
TEST2(err, KErrNone);
CleanupStack::PopAndDestroy(&wrStrm);
TRAP(err, TSqlBlob::SetL(db, _L("sqlite_master"), _L("tbl_name"), _L8("myTableName"), 1));
TEST2(err, KErrNone);
// Attempt to read from and write to the system tables - neither reads nor writes should be permitted
CleanupClosePushL(rdStrm);
TRAP(err, rdStrm.OpenL(db, _L("symbian_security"), _L("PolicyData"), 1)); // BLOB column
TEST2(err, KErrPermissionDenied);
CleanupStack::PopAndDestroy(&rdStrm);
TRAP(err, wholeBuf = TSqlBlob::GetLC(db, _L("symbian_security"), _L("PolicyData"), 1));
TEST2(err, KErrPermissionDenied);
buf = HBufC8::NewLC(100);
bufPtr.Set(buf->Des());
err = TSqlBlob::Get(db, _L("symbian_security"), _L("PolicyData"), bufPtr, 1);
TEST2(err, KErrPermissionDenied);
CleanupStack::PopAndDestroy(buf);
CleanupClosePushL(wrStrm);
TRAP(err, wrStrm.OpenL(db, _L("symbian_security"), _L("PolicyData"), 1));
TEST2(err, KErrPermissionDenied);
CleanupStack::PopAndDestroy(&wrStrm);
TRAP(err, TSqlBlob::SetL(db, _L("symbian_security"), _L("PolicyData"), _L8("VVVV"), 1));
TEST2(err, KErrPermissionDenied);
db.Close();
}
void DoTestsL()
{
TheTest.Start(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1647 Schema database access test "));
SchemaSecurityTest();
TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-4037 Declared column type test"));
DeclaredColumnTypeTest();
TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-4046 Size(TSize&) test"));
Size2Test();
TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-4047 Compact() test"));
CompactTest();
TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-4098 Schema blob access test"));
SchemaBlobTestL();
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;
}