/**
* Method to fetch Authentication app ID from PluginIDTable giving the plugin ID
* @param aPluginID the ID of the plugin
* @param aAuthAppId [out] ID of the Authentication app
*/
void CSmfCredMgrDbUser::readAuthAppIdInPluginIdTable(const TDesC& aPluginID,
TDes& aAuthAppId)
{
TInt err(KErrNone);
RSqlStatement sqlReadStatement;
TInt paramIndex(KErrNone);
err = sqlReadStatement.Prepare(iDataBase, KSmfDbReadAuthAppIdInPluginTable);
__ASSERT_DEBUG( (err >= KErrNone), User::Invariant());
paramIndex = sqlReadStatement.ParameterIndex(_L(":iID"));
err = sqlReadStatement.BindText(paramIndex, aPluginID);
__ASSERT_DEBUG( (err >= KErrNone), User::Invariant());
while ((err = sqlReadStatement.Next()) == KSqlAtRow)
{
//sometimes sqlStmt.Next returns KSqlAtRow even if no row is present
if (!sqlReadStatement.IsNull(0))
{
sqlReadStatement.ColumnText(0, aAuthAppId);
}
}
sqlReadStatement.Close();
}
/**
* Method to fetch Authentication app ID from RegTokenValidityTable
* giving the reg token
* @param aRegToken The Registration token of the authentication app
* @param aAuthAppId [out] The ID of the Authentication app
*/
void CSmfCredMgrDbUser::readAuthAppIdInRegTokenTable(const TDesC& aRegToken,
TDes& aAuthAppId)
{
TInt err(KErrNone);
RSqlStatement sqlReadStatement;
TInt paramIndex(KErrNone);
TInt64 duration;
TBuf<KMaxBufSize> tokenBuf(aRegToken);
err = sqlReadStatement.Prepare(iDataBase,
KSmfDbReadAuthAppIdInRegTokenTable);
__ASSERT_DEBUG( (err >= KErrNone), User::Invariant());
paramIndex = sqlReadStatement.ParameterIndex(_L(":iID"));
err = sqlReadStatement.BindText(paramIndex, tokenBuf);
__ASSERT_DEBUG( (err >= KErrNone), User::Invariant());
while ((err = sqlReadStatement.Next()) == KSqlAtRow)
{
//sometimes sqlStmt.Next returns KSqlAtRow even if no row is present
if (!sqlReadStatement.IsNull(0))
{
duration = sqlReadStatement.ColumnInt(1);
if (duration) //to be checked with epoch date-time
{
sqlReadStatement.ColumnText(0, aAuthAppId);
}
}
else
{
__ASSERT_DEBUG( 0, User::Invariant());
}
}
sqlReadStatement.Close();
}
/**
* Method to fetch all the URLs associated with the Authentication app
* @param aAuthAppId The ID of the Authentication app
* @param aArray [out] The array to be updated with URLs
*/
void CSmfCredMgrDbUser::readUrlL(const TDesC& aAuthAppId,
RPointerArray<HBufC>& aArray)
{
TInt err(KErrNone);
RSqlStatement sqlReadStatement;
TInt paramIndex(KErrNone);
err = sqlReadStatement.Prepare(iDataBase, KSmfDbReadURL);
__ASSERT_DEBUG( (err >= KErrNone), User::Invariant());
paramIndex = sqlReadStatement.ParameterIndex(_L(":iID"));
err = sqlReadStatement.BindText(paramIndex, aAuthAppId);
__ASSERT_DEBUG( (err >= KErrNone), User::Invariant());
while ((err = sqlReadStatement.Next()) == KSqlAtRow)
{
//sometimes sqlStmt.Next returns KSqlAtRow even if no row is present
if (!sqlReadStatement.IsNull(0))
{
TBuf<KMaxBufSize> urlBuf;
HBufC* buf = HBufC::NewL(KMaxBufSize);
sqlReadStatement.ColumnText(0, urlBuf);
buf->Des().Copy(urlBuf);
aArray.Append(buf);
}
else
{
__ASSERT_DEBUG( 0, User::Invariant());
}
}
sqlReadStatement.Close();
}
TBool CPredictiveSearchSynchronizer::ReadMailAddressesL(CContactItem& aContact)
{
PRINT(_L("CPredictiveSearchSynchronizer::ReadMailAddressesL"));
// SELECT value FROM comm_addr
// WHERE contact_id = [contact id value] AND type = [type value];
_LIT(KSelectMailAddrFormat, "SELECT %S FROM %S WHERE %S = %d AND %S = %d;");
const TInt KContactIdLength = 10;
const TInt KCommAddrTypeLength = 2; // CPplCommAddrTable::EEmailAddress is enum
TInt bufSize = KSelectMailAddrFormat().Length() +
KCommAddrValue().Length() +
KSqlContactCommAddrTableName().Length() +
KCommAddrContactId().Length() +
KContactIdLength +
KCommAddrType().Length() +
KCommAddrTypeLength;
HBufC* sqlStatement = HBufC::NewLC(bufSize);
sqlStatement->Des().AppendFormat(KSelectMailAddrFormat,
&KCommAddrValue,
&KSqlContactCommAddrTableName,
&KCommAddrContactId,
aContact.Id(),
&KCommAddrType,
CPplCommAddrTable::EEmailAddress);
RSqlStatement stmnt;
CleanupClosePushL(stmnt);
PRINT1(_L("prepare SQL statement:%S"), sqlStatement);
stmnt.PrepareL(iDatabase, *sqlStatement);
const TInt KValueIndex = 0;
TBool foundMailAddress(EFalse);
TInt err(KErrNone);
while ((err = stmnt.Next()) == KSqlAtRow)
{
TPtrC value;
if (stmnt.ColumnText(KValueIndex, value) == KErrNone)
{
PRINT2(_L(" id=%d, found mail address=%S"), aContact.Id(), &value);
CContactItemField* field =
CContactItemField::NewLC(KStorageTypeText, KUidContactFieldEMail);
CContactTextField* textfield = field->TextStorage();
textfield->SetTextL(value);
aContact.AddFieldL(*field); // Takes ownership
CleanupStack::Pop(field);
foundMailAddress = ETrue;
}
}
if (err != KSqlAtEnd)
{
PRINT1(_L("CPredictiveSearchSynchronizer::ReadMailAddressesL SQL err=%d"), err);
User::Leave(err);
}
CleanupStack::PopAndDestroy(&stmnt);
CleanupStack::PopAndDestroy(sqlStatement);
PRINT1(_L("CPredictiveSearchSynchronizer::ReadMailAddressesL return %d"), foundMailAddress);
return foundMailAddress;
}
/**
@SYMTestCaseID SYSLIB-SQL-CT-1645
@SYMTestCaseDesc Testing database operations on a secure database.
The test application's capabilities allow write-only 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 WriteOnlyDatabaseTest()
{
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 TRIGGER upd_a_b1 UPDATE OF B1 ON A BEGIN UPDATE B SET F3 = 'AAAA' WHERE F2 = A.F1; END;"));
TEST2(err, KErrPermissionDenied);
err = TheDb.Exec(_L("CREATE TEMP TRIGGER upd_a_b1 UPDATE OF B1 ON A BEGIN UPDATE B SET F3 = 'AAAA' WHERE F2 = A.F1; END;"));
TEST2(err, KErrPermissionDenied);//Temp trigger which attempts to update one of the tables.
err = TheDb.Exec(_L("CREATE VIEW V1 AS SELECT * FROM A"));
TEST2(err, KErrPermissionDenied);
err = TheDb.Exec(_L("CREATE TEMP VIEW V1 AS SELECT * FROM A"));
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"));
TEST2(err, KErrPermissionDenied);
//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"));
TEST2(err, KErrPermissionDenied);
//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);
//Attempt to insert new user data
err = TheDb.Exec(_L("INSERT INTO B(F2, F3, B2) VALUES(22, 'AAA', x'47474747474747474747')"));
TEST2(err, 1);
//Attempt to change the isolation level.
err = TheDb.SetIsolationLevel(RSqlDatabase::ESerializable);
TEST2(err, KErrNone);
err = TheDb.SetIsolationLevel(RSqlDatabase::EReadUncommitted);
TEST2(err, KErrNone);
//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, KErrPermissionDenied);
//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();
}
/**
* Method to Key-Secret pairs of the Authentication app
* @param aAuthAppId The ID of the Authentication app
* @param aArray [out] The array containing the key-secret pair
*/
void CSmfCredMgrDbUser::readAuthTokensL(const TDesC& aAuthAppId, RArray<
TSmfAuthToken>& aArray)
{
TInt err(KErrNone);
RSqlStatement sqlReadStatement;
TInt paramIndex(KErrNone);
err = sqlReadStatement.Prepare(iDataBase, KSmfDbReadAuthTokens);
__ASSERT_DEBUG( (err >= KErrNone), User::Invariant());
paramIndex = sqlReadStatement.ParameterIndex(_L(":iID"));
err = sqlReadStatement.BindText(paramIndex, aAuthAppId);
__ASSERT_DEBUG( (err >= KErrNone), User::Invariant());
while ((err = sqlReadStatement.Next()) == KSqlAtRow)
{
//sometimes sqlStmt.Next returns KSqlAtRow even if no row is present
if (!sqlReadStatement.IsNull(0))
{
TSmfAuthToken Set;
Set.iKey = HBufC::NewL(KMaxAuthTokenLength);
Set.iSecret = HBufC::NewL(KMaxAuthTokenLength);
TBuf<KMaxBufSize> keyBuf;
TBuf<KMaxBufSize> secretBuf;
sqlReadStatement.ColumnText(0, keyBuf);
sqlReadStatement.ColumnText(1, secretBuf);
Set.iKey->Des().Copy(keyBuf);
Set.iSecret->Des().Copy(secretBuf);
//add it to the array
aArray.Append(Set);
}
else
{
__ASSERT_DEBUG( 0, User::Invariant());
}
}
sqlReadStatement.Close();
}
/**
@SYMTestCaseID PDS-SQLITE3SEC-UT-4041
@SYMTestCaseDesc SQL server single-select performance test.
The test selects one randomly chosen record and stores
the execution time for later use (comparison and printing).
@SYMTestPriority High
@SYMTestActions SQL server single-select performance test.
@SYMTestExpectedResults Test must not fail
@SYMREQ REQ11320
*/
static void SqlServerSingleSelectTest(const char aSingleSelectSql[], TInt aSelectRecId)
{
TheTest.Next( _L("@SYMTestCaseID:PDS-SQLITE3SEC-UT-4041"));
(void)KillProcess(KSqlSrvName);
TInt err = TheDb.Open(KTestDbName);
TEST2(err, KErrNone);
TheSqlBuf.Copy(TPtrC8((const TUint8*)aSingleSelectSql));
TheSqlBuf.AppendNum((TInt64)aSelectRecId);
RSqlStatement stmt;
err = stmt.Prepare(TheDb, TheSqlBuf);
TEST2(err, KErrNone);
TInt recCnt = 0;
TUint32 fc = FastCounterValue();
while((err = stmt.Next()) == KSqlAtRow)
{
TInt64 i64 = stmt.ColumnInt64(0);
UNUSED_VAR(i64);
TReal d = stmt.ColumnReal(1);
UNUSED_VAR(d);
TPtrC t;
err = stmt.ColumnText(2, t);
TEST2(err, KErrNone);
UNUSED_PTR(t);
TPtrC8 b;
err = stmt.ColumnBinary(3, b);
TEST2(err, KErrNone);
UNUSED_PTR(b);
++recCnt;
}
StorePerfTestResult(EPerfTestSqlMode, EPerfTestSingleSelect, FastCounterValue() - fc);
TEST2(err, KSqlAtEnd);
TEST2(recCnt, 1);
stmt.Close();
TheDb.Close();
}
/**
@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();
}
void CPredictiveSearchSynchronizer::CreatePredSearchTablesL(TBool aAllTables)
{
PRINT1(_L("CPredictiveSearchSynchronizer::CreatePredSearchTablesL all=%d"), aAllTables);
if (aAllTables)
{
i12keyTable.CreateTableL();
iSettingsTable.CreateTableL();
iSettingsTable.StoreCurrentLanguageL();
}
iQwertyTable.CreateTableL();
_LIT(KSelectAllContactsFormat, "SELECT %S,%S,%S FROM %S;");
TInt bufSize = KSelectAllContactsFormat().Length() +
KContactId().Length() +
KContactFirstName().Length() +
KContactLastName().Length() +
KSqlContactTableName().Length();
HBufC* sqlStatement = HBufC::NewLC(bufSize);
sqlStatement->Des().AppendFormat(KSelectAllContactsFormat,
&KContactId,
&KContactFirstName,
&KContactLastName,
&KSqlContactTableName);
RSqlStatement stmnt;
CleanupClosePushL(stmnt);
PRINT1(_L("CreatePredSearchTablesL prepare SQL statement:%S"), sqlStatement);
stmnt.PrepareL(iDatabase, *sqlStatement);
const TInt KContactIdIndex = 0;
const TInt KFirstNameIndex = 1;
const TInt KLastNameIndex = 2;
TInt err(KErrNone);
while ((err = stmnt.Next()) == KSqlAtRow)
{
PRINT(_L("CreatePredSearchTablesL create CContactItem"));
TInt id = KUidContactCardValue;
TUid uid;
uid.iUid = id;
CContactItem* contact = CContactItem::NewLC(uid);
contact->SetId(stmnt.ColumnInt(KContactIdIndex));
// If first name exists, write it to contact item
TPtrC firstName;
if (stmnt.ColumnText(KFirstNameIndex, firstName) == KErrNone)
{
CContactItemField* field =
CContactItemField::NewLC(KStorageTypeText, KUidContactFieldGivenName);
CContactTextField* textfield = field->TextStorage();
textfield->SetTextL(firstName);
contact->AddFieldL(*field); // Takes ownership
CleanupStack::Pop(field);
}
TPtrC lastName;
if (stmnt.ColumnText(KLastNameIndex, lastName) == KErrNone)
{
CContactItemField* field =
CContactItemField::NewLC(KStorageTypeText, KUidContactFieldFamilyName);
CContactTextField* textfield = field->TextStorage();
textfield->SetTextL(lastName);
contact->AddFieldL(*field); // Takes ownership
CleanupStack::Pop(field);
}
PRINT(_L("CreatePredSearchTablesL create entry to tables"));
if (aAllTables)
{
i12keyTable.CreateInDbL(*contact);
}
if (ReadMailAddressesL(*contact))
{
iQwertyTable.CreateInDbL(*contact);
}
CleanupStack::PopAndDestroy(contact);
}
// Leave if we didn't complete going through the results properly
if (err != KSqlAtEnd)
{
PRINT1(_L("CreatePredSearchTablesL SQL err=%d"), err);
User::Leave(err);
}
CleanupStack::PopAndDestroy(&stmnt);
CleanupStack::PopAndDestroy(sqlStatement);
PRINT(_L("CPredictiveSearchSynchronizer::CreatePredSearchTablesL ends"));
}
