--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/persistentstorage/sql/TEST/t_sqlattach.cpp Fri Jan 22 11:06:30 2010 +0200
@@ -0,0 +1,810 @@
+// 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:
+//
+
+#include <e32test.h>
+#include <bautils.h>
+#include <sqldb.h>
+
+///////////////////////////////////////////////////////////////////////////////////////
+
+RTest TheTest(_L("t_sqlattach test"));
+
+RSqlDatabase TheDb;
+RSqlDatabase TheDb2;
+
+_LIT(KTestDir, "c:\\test\\");
+
+_LIT(KTestDb1, "c:\\test\\t_sqlattach_1.db");
+_LIT(KTestDb2, "c:\\test\\t_sqlattach_2.db");
+_LIT(KTestDb3, "c:\\test\\t_sqlattach_3.db");
+_LIT(KTestDb4, "c:\\test\\t_sqlattach_4.db");
+
+_LIT(KSecureTestDb1, "c:[21212122]BBDb2.db");//Created outside this test app
+_LIT(KSecureTestDb2, "c:[21212122]AADb2.db");//Created outside this test app
+_LIT(KSecureTestDb3, "c:[21212123]t_sqlattach_3.db");
+_LIT(KDbNameInjection, "c:\\test\\t_sqlattach_3.db' as db; delete from a;");
+
+//const TUid KSecureUid = {0x21212122};//The UID of the secure test databases: KSecureTestDb1 and KSecureTestDb2
+
+//The test uses two secure databases: KSecureTestDb1 and KSecureTestDb2.
+//
+//KSecureTestDb1 schema
+//TABLE A1(F1 INTEGER , F2 INTEGER, B1 BLOB)
+//
+//KSecureTestDb1 security settings
+//-Security UID = KSecureUid
+//-Schema policy = ECapabilityTrustedUI
+//-Read policy = ECapabilityReadDeviceData
+//-Write policy = ECapabilityWriteDeviceData
+//The test application can read/write the database tables but cannot modify the database structure
+//
+//KSecureTestDb2 schema
+//TABLE C(A1 INTEGER, B2 BLOB)
+//
+//KSecureTestDb2 security settings
+//-Security UID = KSecureUid
+//-Schema policy = ECapabilityDiskAdmin
+//-Read policy = ECapabilityNetworkControl
+//-Write policy = ECapabilityWriteDeviceData
+//The test application can write to the database tables but cannot modify the database structure or read from tables
+
+///////////////////////////////////////////////////////////////////////////////////////
+
+void DeleteDatabases()
+ {
+ TheDb2.Close();
+ TheDb.Close();
+ (void)RSqlDatabase::Delete(KDbNameInjection);
+ (void)RSqlDatabase::Delete(KSecureTestDb3);
+ (void)RSqlDatabase::Delete(KTestDb4);
+ (void)RSqlDatabase::Delete(KTestDb3);
+ (void)RSqlDatabase::Delete(KTestDb2);
+ (void)RSqlDatabase::Delete(KTestDb1);
+ }
+
+///////////////////////////////////////////////////////////////////////////////////////
+//Test macros and functions
+void Check(TInt aValue, TInt aLine)
+ {
+ if(!aValue)
+ {
+ DeleteDatabases();
+ TheTest(EFalse, aLine);
+ }
+ }
+void Check(TInt aValue, TInt aExpected, TInt aLine)
+ {
+ if(aValue != aExpected)
+ {
+ DeleteDatabases();
+ RDebug::Print(_L("*** Expected error: %d, got: %d\r\n"), aExpected, aValue);
+ TheTest(EFalse, aLine);
+ }
+ }
+#define TEST(arg) ::Check((arg), __LINE__)
+#define TEST2(aValue, aExpected) ::Check(aValue, aExpected, __LINE__)
+
+///////////////////////////////////////////////////////////////////////////////////////
+
+void CreateTestDir()
+ {
+ RFs fs;
+ TInt err = fs.Connect();
+ TEST2(err, KErrNone);
+
+ err = fs.MkDir(KTestDir);
+ TEST(err == KErrNone || err == KErrAlreadyExists);
+
+ fs.Close();
+ }
+
+void CreateDatabases()
+ {
+ TBuf<100> sql;
+
+ TInt err = TheDb.Create(KTestDb1);
+ TEST2(err, KErrNone);
+ sql.Copy(_L("CREATE TABLE A1(F1 INTEGER, F2 INTEGER)"));
+ err = TheDb.Exec(sql);
+ TEST(err >= 0);
+ sql.Copy(_L("CREATE TABLE A2(DDD INTEGER)"));
+ err = TheDb.Exec(sql);
+ TEST(err >= 0);
+ TheDb.Close();
+
+ err = TheDb.Create(KTestDb2);
+ TEST2(err, KErrNone);
+ sql.Copy(_L("CREATE TABLE B(A1 INTEGER, A2 INTEGER)"));
+ err = TheDb.Exec(sql);
+ TEST(err >= 0);
+ TheDb.Close();
+ }
+
+///////////////////////////////////////////////////////////////////////////////////////
+///////////////////////////////////////////////////////////////////////////////////////
+
+/**
+@SYMTestCaseID SYSLIB-SQL-CT-1641
+@SYMTestCaseDesc Attached database tests.
+ Open non-secure database, attach secure database.
+ The test application's security policy allows read/write operations on the attached
+ database, but database schema modifications are not allowed. The test executes
+ different kind of SQL statements to verify that the test application's security
+ policy is properly asserted by the SQL server.
+@SYMTestPriority High
+@SYMTestActions Execution SQL statements on attached database.
+@SYMTestExpectedResults Test must not fail
+@SYMREQ REQ5792
+ REQ5793
+*/
+void Test1()
+ {
+ TInt err = TheDb.Open(KTestDb1);
+ TEST2(err, KErrNone);
+
+ //Attach a secure database, the logical database name length is 0
+ _LIT(KAttachDb0, "");
+ err = TheDb.Attach(KSecureTestDb1, KAttachDb0);
+ TEST2(err, KErrBadName);
+
+ //Attach a secure database, the logical database name length is > than KMaxFileName
+ TBuf<KMaxFileName + 1> longDbName;
+ longDbName.SetLength(longDbName.MaxLength());
+ longDbName.Fill(TChar('A'));
+ err = TheDb.Attach(KSecureTestDb1, longDbName);
+ TEST2(err, KErrBadName);
+
+ //Attach a secure database
+ //The test application can read/write the attached database tables but cannot modify the database structure
+ _LIT(KAttachDb1, "Db1");
+ err = TheDb.Attach(KSecureTestDb1, KAttachDb1);
+ TEST2(err, KErrNone);
+
+ //Attempt to read from the attached secure database
+ err = TheDb.Exec(_L("SELECT * FROM db1.a1"));
+ TEST(err >= 0);
+ //Attempt to write to the attached secure database
+ err = TheDb.Exec(_L("INSERT INTO dB1.a1(f1) valUES(10)"));
+ TEST2(err, 1);
+ //Attempt to modify the attached secure database schema
+ err = TheDb.Exec(_L("CREATE TABLE db1.CCC(H REAL)"));
+ TEST2(err, KErrPermissionDenied);
+ err = TheDb.Exec(_L("ALTER TABLE db1.A1 ADD COLUMN a2 integer"));
+ TEST2(err, KErrPermissionDenied);
+
+ //Attempt to read from the main non-secure database
+ err = TheDb.Exec(_L("SELECT * FROM main.a1"));
+ TEST(err >= 0);
+ //Attempt to write to the main non-secure database
+ err = TheDb.Exec(_L("INSERT INTO a1(f1) valUES(10)"));
+ TEST2(err, 1);
+ //Attempt to modify the main non-secure database schema
+ err = TheDb.Exec(_L("CREATE TABLE a3(H REAL)"));
+ TEST(err >= 0);
+
+ TheTest.Printf(_L("===Attach second, non-secure database"));
+ //Attach a non-secure database
+ //The test application should be able to do everything with the attached database
+ _LIT(KAttachDb2, "db2");
+ err = TheDb.Attach(KTestDb2, KAttachDb2);
+ TEST2(err, KErrNone);
+
+ //Attempt to read from the attached non-secure database
+ err = TheDb.Exec(_L("SELECT * FROM db2.B"));
+ TEST(err >= 0);
+ //Attempt to write to the attached non-secure database
+ err = TheDb.Exec(_L("INSERT INTO dB2.b(a2) ValUES(112)"));
+ TEST2(err, 1);
+ //Attempt to modify the attached non-secure database schema
+ err = TheDb.Exec(_L("ALTER TABLE db2.b ADD COLUMN a3 text"));
+ TEST(err >= 0);
+
+ TheTest.Printf(_L("===Attach third, non-secure database (the main database)"));
+ //Attach a non-secure database (the main database)
+ //The test application should be able to do everything with the attached database
+ _LIT(KAttachDb3, "db3");
+ err = TheDb.Attach(KTestDb1, KAttachDb3);
+ TEST2(err, KErrNone);
+
+ //Attempt to read from the third, non-secure database
+ err = TheDb.Exec(_L("SELECT * FROM db3.a1"));
+ TEST(err >= 0);
+ //Attempt to write to the third, non-secure database
+ err = TheDb.Exec(_L("INSERT INTO db3.a1(f2) values(11)"));
+ TEST2(err, 1);
+ //Attempt to modify the third, non-secure database schema
+ err = TheDb.Exec(_L("CREATE TABLE db3.a4(s blob)"));
+ TEST(err < 0);//Cannot modify the main database from the atatched!?
+
+ TheTest.Printf(_L("===Attach fourth, secure database"));
+ //Attach a secure database
+ //The test application can only write to the database, but cannot modify the schema or read from the database
+ _LIT(KAttachDb4, "db4");
+ err = TheDb.Attach(KSecureTestDb2, KAttachDb4);
+ TEST2(err, KErrNone);
+
+ //Attempt to read from the attached secure database
+ err = TheDb.Exec(_L("SELECT * FROM db4.c"));
+ TEST2(err, KErrPermissionDenied);
+ //Attempt to write to the attached secure database
+ err = TheDb.Exec(_L("INSERT INTO Db4.c(a1) VALUES(1)"));
+ TEST2(err, 1);
+ //Attempt to write to a non-secure database using data from the attached secure database
+ err = TheDb.Exec(_L("INSERT INTO a1(f1) select db4.c.a1 from db4.c"));
+ TEST2(err, KErrPermissionDenied);
+ //Attempt to write to a secure database using data from a non-secure database
+ err = TheDb.Exec(_L("INSERT INTO db4.c(a1) select f1 from a1"));
+ TEST(err >= 0);
+ err = TheDb.Exec(_L("UPDATE db4.C SET a1 = 3 WHERE a1 = 1"));
+ TEST2(err, KErrPermissionDenied);//!?!?!?
+ err = TheDb.Exec(_L("DELETE FROM db4.C"));
+ TEST(err >= 0);
+ //Attempt to modify the attached secure database schema
+ err = TheDb.Exec(_L("CREATE TABLE db4.CCC(z integer)"));
+ TEST2(err, KErrPermissionDenied);
+ err = TheDb.Exec(_L("DROP table db4.C"));
+ TEST2(err, KErrPermissionDenied);
+
+ err = TheDb.Detach(KAttachDb2);
+ TEST2(err, KErrNone);
+ err = TheDb.Detach(KAttachDb1);
+ TEST2(err, KErrNone);
+
+ err = TheDb.Detach(KAttachDb4);
+ TEST2(err, KErrNone);
+ err = TheDb.Exec(_L("SELECT * FROM db4.c"));
+ TEST(err < 0);
+
+ err = TheDb.Detach(KAttachDb2);
+ TEST(err != KErrNone);
+
+ err = TheDb.Detach(KAttachDb3);
+ TEST2(err, KErrNone);
+ err = TheDb.Exec(_L("INSERT INTO db3.a1(f2) values(11)"));
+ TEST(err < 0);
+
+ err = TheDb.Detach(KAttachDb4);
+ TEST(err != KErrNone);
+
+ TheDb.Close();
+ }
+
+/**
+@SYMTestCaseID SYSLIB-SQL-CT-1642
+@SYMTestCaseDesc Attached database tests.
+ Open secure database, attach secure database.
+ The test application's security policy allows read/write operations on the main
+ database, but database schema modifications are not allowed. The test application
+ is allowed to write to the attached database but can't read from or modify the schema.
+ The test executes different kind of SQL statements to verify that the test application's security
+ policy is properly asserted by the SQL server.
+@SYMTestPriority High
+@SYMTestActions Execution SQL statements on attached database.
+@SYMTestExpectedResults Test must not fail
+@SYMREQ REQ5792
+ REQ5793
+*/
+void Test2()
+ {
+ //The test application can read/write the database tables but cannot modify the database structure
+ TInt err = TheDb.Open(KSecureTestDb1);
+ TEST2(err, KErrNone);
+ _LIT(KAttachDb2, "Db2");
+ //The test application can only write to the database, but cannot modify the schema or read from the database
+ err = TheDb.Attach(KSecureTestDb2, KAttachDb2);
+ TEST2(err, KErrNone);
+
+ //Attempt to read from the main database and write to the attached database
+ err = TheDb.Exec(_L("INSERT INTO db2.c(a1) SELECT f1 FROM a1"));
+ TEST(err >= 0);
+
+ //Attempt to read from the attached database and write to the main database
+ err = TheDb.Exec(_L("INSERT INTO a1(f2) SELECT a1 FROM db2.c"));
+ TEST2(err, KErrPermissionDenied);
+
+ //Attempt to detach database using DETACH sql statement directly.
+ //Executed only in release mode because the server will panic in _DEBUG mode
+#ifndef _DEBUG
+ err = TheDb.Exec(_L("DETACH DATABASE DB2"));
+ TEST2(err, KErrPermissionDenied);
+#endif
+
+ err = TheDb.Detach(KAttachDb2);
+ TEST2(err, KErrNone);
+
+ //Attempt to attach a database using ATTACH sql statement directly.
+ TBuf<100> sql;
+ sql.Format(_L("ATTACH DATABASE '%S' AS Db3"), &KSecureTestDb2);
+ err = TheDb.Exec(sql);
+ TEST2(err, KErrPermissionDenied);
+
+ TheDb.Close();
+ }
+
+/**
+@SYMTestCaseID SYSLIB-SQL-CT-1814
+@SYMTestCaseDesc Attached database tests. SQL injection.
+ Create the following test databases:
+ 1) c:\test\inj.db
+ 2) c:\test\inj.db' as db; delete from a;
+ 3) c:[21212123]Injected.db
+ Insert some records in database (3). Attach database (2) to database (3).
+ Check the records count of table A. If the count is zero, then it means that the injection has been successful
+ and a security hole exists when attaching/detaching databases.
+@SYMTestPriority High
+@SYMTestActions Attached database tests. SQL injection.
+@SYMTestExpectedResults Test must not fail
+@SYMREQ REQ5792
+ REQ5793
+*/
+void SqlInjectionTest()
+ {
+ //Create the database, which name is used for the attack.
+ //This is done just to ensure that the database, which name is used in the SQL injection, exists,
+ //Otherwise the injection attack may fail with KErrNotFound error.
+ TInt err = TheDb2.Create(KTestDb3);
+ TEST2(err, KErrNone);
+ TheDb2.Close();
+ err = TheDb2.Create(KDbNameInjection);
+ TEST2(err, KErrNone);
+ TheDb2.Close();
+ //Create a secure database, which will be impacted by the SQL injection
+ TSecurityPolicy policy(TSecurityPolicy::EAlwaysPass);
+ RSqlSecurityPolicy dbPolicy;
+ err = dbPolicy.Create(policy);
+ TEST2(err, KErrNone);
+ err = TheDb.Create(KSecureTestDb3, dbPolicy);
+ TEST2(err, KErrNone);
+ err = TheDb.Exec(_L("CREATE TABLE A(Id Integer)"));
+ TEST(err >= 0);
+ err = TheDb.Exec(_L("INSERT INTO A(Id) VALUES(1)"));
+ TEST(err >= 0);
+ err = TheDb.Exec(_L("INSERT INTO A(Id) VALUES(2)"));
+ TEST(err >= 0);
+ const TInt KInsertedRecCnt = 2;
+ //Cleanup
+ dbPolicy.Close();
+ TheDb.Close();
+ //Repopen the secure database and attach the secind database, which file name is actually a SQL injection
+ err = TheDb.Open(KSecureTestDb3);
+ TEST2(err, KErrNone);
+ err = TheDb.Attach(KDbNameInjection, _L("Db2"));
+ TEST2(err, KErrNone);
+ //Check table A contents. If the security hole still exists, table A content is gone.
+ TSqlScalarFullSelectQuery query(TheDb);
+ TInt recCnt = 0;
+ TRAP(err, recCnt = query.SelectIntL(_L("SELECT COUNT(*) FROM A")));
+ TEST2(err, KErrNone);
+ TEST2(recCnt, KInsertedRecCnt);//if zero records count - successfull SQL injection - the security hole exists!
+ //Try to execute RSqlDatabase::Detach(), where instead of a logical database name, SQL statement is supplied.
+ err = TheDb.Detach(_L("DB; INSERT INTO A(Id) VALUES(3)"));
+ TEST(err != KErrNone);
+ //Check table A contents. If the security hole still exists, table A will have one more record.
+ TRAP(err, recCnt = query.SelectIntL(_L("SELECT COUNT(*) FROM A")));
+ TEST2(err, KErrNone);
+ TEST2(recCnt, KInsertedRecCnt);//if one more record - successfull SQL injection - the security hole exists!
+ TheDb.Close();
+ //Cleanup
+ (void)RSqlDatabase::Delete(KDbNameInjection);
+ (void)RSqlDatabase::Delete(KTestDb3);
+ (void)RSqlDatabase::Delete(KSecureTestDb3);
+ }
+
+/**
+@SYMTestCaseID SYSLIB-SQL-UT-3507
+@SYMTestCaseDesc Test for DEF109100: SQL, code coverage for TSqlBufRIterator, TSqlAttachDbRefCounter is very low.
+ The test opens two existing databases, and the attaches to them the same secure shared database.
+@SYMTestPriority High
+@SYMTestActions Test for DEF109100: SQL, code coverage for TSqlBufRIterator, TSqlAttachDbRefCounter is very low.
+@SYMTestExpectedResults Test must not fail
+@SYMDEF DEF109100
+*/
+void TwoConnAttachTest()
+ {
+ //Connection 1
+ TInt err = TheDb.Open(KTestDb1);
+ TEST2(err, KErrNone);
+ //Connection 2
+ err = TheDb2.Open(KTestDb2);
+ TEST2(err, KErrNone);
+ //Attach KSecureTestDb1 to connection 1
+ _LIT(KAttachDb1, "Db1");
+ err = TheDb.Attach(KSecureTestDb1, KAttachDb1);
+ TEST2(err, KErrNone);
+ //Attach KSecureTestDb1 to connection 2
+ err = TheDb2.Attach(KSecureTestDb1, KAttachDb1);
+ TEST2(err, KErrNone);
+ //Detach
+ err = TheDb2.Detach(KAttachDb1);
+ TEST2(err, KErrNone);
+ err = TheDb.Detach(KAttachDb1);
+ TEST2(err, KErrNone);
+ //Cleanup
+ TheDb2.Close();
+ TheDb.Close();
+ }
+
+/**
+@SYMTestCaseID SYSLIB-SQL-UT-3515
+@SYMTestCaseDesc RSqlStatement::DeclaredColumnType() test
+ The test creates 2 tables in two different databases. Then the test opens the first database and
+ attaches the second one. After that a SELECT sql statement is prepared and the statement operates
+ on both tables: from the main database and the attached one.
+ DeclaredColumnType() is called after the statement preparation and column types checked.
+@SYMTestPriority High
+@SYMTestActions RSqlStatement::ColumnCount() test
+@SYMTestExpectedResults Test must not fail
+@SYMREQ REQ8035
+*/
+void DeclaredColumnTypeTest()
+ {
+ //Preparation
+ TInt err = TheDb.Open(KTestDb1);
+ TEST2(err, KErrNone);
+ err = TheDb.Exec(_L("CREATE TABLE Y(Id INTEGER, Name TEXT)"));
+ TEST(err >= 0);
+ TheDb.Close();
+ err = TheDb.Open(KTestDb2);
+ TEST2(err, KErrNone);
+ err = TheDb.Exec(_L("CREATE TABLE Z(Id INTEGER, Data BLOB)"));
+ TEST(err >= 0);
+ TheDb.Close();
+ //Open KTestDb1, attach KTestDb2
+ err = TheDb.Open(KTestDb1);
+ TEST2(err, KErrNone);
+ _LIT(KAttachDb, "Db2");
+ err = TheDb.Attach(KTestDb2, KAttachDb);
+ TEST2(err, KErrNone);
+ //SELECT from both db
+ RSqlStatement stmt;
+ err = stmt.Prepare(TheDb, _L("SELECT Y.Id, Y.Name, DB2.Z.Data FROM Y,DB2.Z WHERE Y.Id = DB2.Z.Id"));
+ TEST2(err, KErrNone);
+ TInt colCnt = stmt.ColumnCount();
+ TEST2(colCnt, 3);
+ TSqlColumnType colType;
+ err = stmt.DeclaredColumnType(0, colType);
+ TEST2(err, KErrNone);
+ TEST2(colType, ESqlInt);
+ err = stmt.DeclaredColumnType(1, colType);
+ TEST2(err, KErrNone);
+ TEST2(colType, ESqlText);
+ err = stmt.DeclaredColumnType(2, colType);
+ TEST2(err, KErrNone);
+ TEST2(colType, ESqlBinary);
+ stmt.Close();
+ //Cleanup
+ err = TheDb.Detach(KAttachDb);
+ TEST2(err, KErrNone);
+ TheDb.Close();
+ }
+
+/**
+@SYMTestCaseID SYSLIB-SQL-UT-4016
+@SYMTestCaseDesc Test for DEF116713 SQL: No redindexing occurs for an attached database.
+ The test does the following steps:
+ 1) Sets the "CollationDllName" column value in the "symbian_settings" stable of the database to be used
+ as an attached database (KTestDb2). The set column value is different than the default collation dll name.
+ 2) Opens KTestDb1, attaches KTestDb2.
+ 3) When KTestDb2 is attached to KTestDb1, the SQL server should detect that the "CollationDllName" column
+ value is different than the default collation dll name and should reindex the attached database and then
+ store the current collation dll name in the "CollationDllName" column.
+ 4) The test checks that after attaching the KTestDb2 database, the "CollationDllName" column value
+ is not the previously used test collation dll name.
+@SYMTestPriority Low
+@SYMTestActions Test for DEF116713 SQL: No redindexing occurs for an attached database.
+@SYMTestExpectedResults Test must not fail
+@SYMDEF DEF116713
+*/
+void DEF116713()
+ {
+ //Set the "CollationDllName" column value in "symbian_settings" table of the database to be attached -
+ //not to be the default collation dll name.
+ TInt err = TheDb.Open(KTestDb2);
+ TEST2(err, KErrNone);
+ err = TheDb.Exec(_L("UPDATE symbian_settings SET CollationDllName='ddkjrrm'"));
+ TEST2(err, 1);
+ TheDb.Close();
+ //Open the main database, attach the other one
+ err = TheDb.Open(KTestDb1);
+ TEST2(err, KErrNone);
+ err = TheDb.Attach(KTestDb2, _L("Db2"));
+ TEST2(err, KErrNone);
+ //The expectation is that the attached database is reindexed and the "CollationDllName" column value - set.
+ RSqlStatement stmt;
+ err = stmt.Prepare(TheDb, _L("SELECT CollationDllName FROM Db2.symbian_settings"));
+ TEST2(err, KErrNone);
+ err = stmt.Next();
+ TEST2(err, KSqlAtRow);
+ TPtrC collationDllName;
+ err = stmt.ColumnText(0, collationDllName);
+ TEST2(err, KErrNone);
+ stmt.Close();
+ TheDb.Close();
+
+ _LIT(KTestCollationDllName, "ddkjrrm");//The same as the used in the "UPDATE symbian_settings" sql.
+ TEST(collationDllName != KTestCollationDllName);
+ }
+
+/**
+@SYMTestCaseID SYSLIB-SQL-UT-4042
+@SYMTestCaseDesc RSqlDatabase::Size(TSize&) on attached database - injection test.
+ The test creates a database and attempts to attach another database,
+ passing a DELETE SQL statement in the attached database name.
+ The attach operation is expected to fail, the database content should stay
+ unchanged after the operation.
+@SYMTestPriority High
+@SYMTestActions RSqlDatabase::Size(TSize&) on attached database - injection test.
+@SYMTestExpectedResults Test must not fail
+@SYMREQ REQ10407
+*/
+void Size2InjectionTest()
+ {
+ TInt err = TheDb.Create(KTestDb4);
+ TEST2(err, KErrNone);
+ err = TheDb.Exec(_L("CREATE TABLE A(I INTEGER)"));
+ TEST(err >= 0);
+ err = TheDb.Exec(_L("INSERT INTO A VALUES(1)"));
+ TEST2(err, 1);
+ _LIT(KAttachDbName, "B");
+ err = TheDb.Attach(KTestDb4, KAttachDbName);
+ TEST2(err, KErrNone);
+ RSqlDatabase::TSize size;
+ err = TheDb.Size(size, _L("B;DELETE FROM MAIN.A"));
+ TEST2(err, KSqlErrGeneral);
+ TPtrC msg = TheDb.LastErrorMessage();
+ TheTest.Printf(_L("RSqlDatabase::Size(TSize&) injection, error message: %S\r\n"), &msg);
+ TSqlScalarFullSelectQuery q(TheDb);
+ TInt reccnt = 0;
+ TRAP(err, reccnt = q.SelectIntL(_L("SELECT COUNT(*) FROM MAIN.A")));
+ TEST2(err, KErrNone);
+ TEST2(reccnt, 1);
+ err = TheDb.Detach(KAttachDbName);
+ TEST2(err, KErrNone);
+ TheDb.Close();
+ (void)RSqlDatabase::Delete(KTestDb4);
+ }
+
+/**
+@SYMTestCaseID SYSLIB-SQL-UT-4043
+@SYMTestCaseDesc RSqlDatabase::Compact() on attached database - injection test.
+ The test creates a database and attaches another database.
+ Then the test attempts to compact the attached database calling
+ RSqlDatabase::Compact() passing DROP TABLE and DELETE statements
+ as name of the attached database. The call is expected to fail,
+ the database content should stay unchanged after the call.
+@SYMTestPriority High
+@SYMTestActions RSqlDatabase::Compact() on attached database - injection test.
+@SYMTestExpectedResults Test must not fail
+@SYMREQ REQ10405
+*/
+void CompactInjectionTest()
+ {
+ TInt err = TheDb.Create(KTestDb4);
+ TEST2(err, KErrNone);
+ err = TheDb.Exec(_L("CREATE TABLE A(I INTEGER); INSERT INTO A(I) VALUES(1)"));
+ TEST(err >= 0);
+ _LIT(KAttachDbName, "B");
+ err = TheDb.Attach(KTestDb4, KAttachDbName);
+ TEST2(err, KErrNone);
+ err = TheDb.Compact(RSqlDatabase::EMaxCompaction, _L("B;DROP B.A"));
+ TEST2(err, KSqlErrGeneral);
+ TPtrC msg = TheDb.LastErrorMessage();
+ TheTest.Printf(_L("RSqlDatabase::Compact() injection, error message: %S\r\n"), &msg);
+
+ TSqlScalarFullSelectQuery query(TheDb);
+ TInt recCount = 0;
+ TRAP(err, recCount = query.SelectIntL(_L("SELECT COUNT(*) FROM A")));
+ TEST2(err, KErrNone);
+ TEST2(recCount, 1);
+
+ err = TheDb.Compact(8192, _L("B;DROP B.A;"));
+ TEST2(err, KSqlErrGeneral);
+ msg.Set(TheDb.LastErrorMessage());
+ TheTest.Printf(_L("RSqlDatabase::Compact() injection, error message: %S\r\n"), &msg);
+
+ recCount = 0;
+ TRAP(err, recCount = query.SelectIntL(_L("SELECT COUNT(*) FROM A")));
+ TEST2(err, KErrNone);
+ TEST2(recCount, 1);
+
+ TRequestStatus stat;
+ TheDb.Compact(8192, stat, _L("B;DELETE FROM B.A;"));
+ User::WaitForRequest(stat);
+ TEST2(stat.Int(), KSqlErrGeneral);
+ msg.Set(TheDb.LastErrorMessage());
+ TheTest.Printf(_L("RSqlDatabase::Compact() injection, error message: %S\r\n"), &msg);
+
+ recCount = 0;
+ TRAP(err, recCount = query.SelectIntL(_L("SELECT COUNT(*) FROM A")));
+ TEST2(err, KErrNone);
+ TEST2(recCount, 1);
+
+ err = TheDb.Detach(KAttachDbName);
+ TEST2(err, KErrNone);
+ TheDb.Close();
+ (void)RSqlDatabase::Delete(KTestDb4);
+ }
+
+/**
+@SYMTestCaseID SYSLIB-SQL-UT-4094
+@SYMTestCaseDesc Incremental blob i/o tests on an attached database.
+ Open secure database, attach secure database.
+ The test application's security policy allows incremental blob read & write
+ operations on the main database, but only write operations on the attached database.
+ The test attempts to read and write to a blob in the attached database to verify that
+ the test application's security policy is properly asserted by the Symbian SQL server.
+@SYMTestPriority High
+@SYMTestActions Execution of blob read and write operations on the attached database.
+@SYMTestExpectedResults Test must not fail
+@SYMREQ REQ5794
+*/
+void BlobAttachedTestL()
+ {
+ // Open the main secure database - the test application can read & write blobs in it
+ // Attach another secure database - the test application can only write blobs in it
+ TInt err = TheDb.Open(KSecureTestDb1);
+ TEST2(err, KErrNone);
+ _LIT(KAttachDb1, "Db1");
+ err = TheDb.Attach(KSecureTestDb2, KAttachDb1);
+ TEST2(err, KErrNone);
+
+ // Insert a new record into the attached database - the blob value is "AAAAAAAAAA"
+ err = TheDb.Exec(_L("INSERT INTO Db1.C(A1, B2) VALUES(15, x'41414141414141414141')"));
+ TEST2(err, 1);
+
+ // Attempt to write to a blob in the attached database
+ RSqlBlobWriteStream wrStrm;
+ CleanupClosePushL(wrStrm);
+ TRAP(err, wrStrm.OpenL(TheDb, _L("C"), _L("B2"), KSqlLastInsertedRowId, KAttachDb1));
+ TEST2(err, KErrNone);
+ TRAP(err, wrStrm.WriteL(_L8("ZZZ")));
+ TEST2(err, KErrNone);
+ CleanupStack::PopAndDestroy(&wrStrm);
+
+ TRAP(err, TSqlBlob::SetL(TheDb, _L("C"), _L("B2"), _L8("YYYYY"), KSqlLastInsertedRowId, KAttachDb1));
+ TEST2(err, KErrNone);
+
+ // Attempt to read a blob in the attached database
+ RSqlBlobReadStream rdStrm;
+ CleanupClosePushL(rdStrm);
+ TRAP(err, rdStrm.OpenL(TheDb, _L("C"), _L("B2"), KSqlLastInsertedRowId, KAttachDb1));
+ TEST2(err, KErrPermissionDenied);
+ CleanupStack::PopAndDestroy(&rdStrm);
+
+ HBufC8* wholeBuf = NULL;
+ TRAP(err, wholeBuf = TSqlBlob::GetLC(TheDb, _L("C"), _L("B2"), KSqlLastInsertedRowId, KAttachDb1));
+ TEST2(err, KErrPermissionDenied);
+
+ HBufC8* buf = HBufC8::NewLC(10);
+ TPtr8 bufPtr(buf->Des());
+ err = TSqlBlob::Get(TheDb, _L("C"), _L("B2"), bufPtr, KSqlLastInsertedRowId, KAttachDb1);
+ TEST2(err, KErrPermissionDenied);
+ CleanupStack::PopAndDestroy(buf);
+
+ // SQLite and system tables in the attached database
+
+ // Attempt to read from and write to the SQLite master table -
+ // reads should be permitted because write capability is enough for this,
+ // writes should not be permitted because schema capability is required for this
+ TBuf8<20> data;
+ CleanupClosePushL(rdStrm);
+ TRAP(err, rdStrm.OpenL(TheDb, _L("sqlite_master"), _L("tbl_name"), 1, KAttachDb1)); // 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, KAttachDb1);
+ 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, KAttachDb1);
+ TEST2(err, KErrNone);
+ TEST(bufPtr.Length() > 0);
+ CleanupStack::PopAndDestroy(buf);
+
+ CleanupClosePushL(wrStrm);
+ TRAP(err, wrStrm.OpenL(TheDb, _L("sqlite_master"), _L("tbl_name"), 1, KAttachDb1));
+ TEST2(err, KErrPermissionDenied);
+ CleanupStack::PopAndDestroy(&wrStrm);
+
+ TRAP(err, TSqlBlob::SetL(TheDb, _L("sqlite_master"), _L("tbl_name"), _L8("VVVV"), 1, KAttachDb1));
+ TEST2(err, KErrPermissionDenied);
+
+ // Attempt to read from and write to the system tables in the attached database - neither reads nor writes should be permitted
+ CleanupClosePushL(rdStrm);
+ TRAP(err, rdStrm.OpenL(TheDb, _L("symbian_security"), _L("PolicyData"), 1, KAttachDb1)); // BLOB column
+ TEST2(err, KErrPermissionDenied);
+ CleanupStack::PopAndDestroy(&rdStrm);
+
+ TRAP(err, wholeBuf = TSqlBlob::GetLC(TheDb, _L("symbian_security"), _L("PolicyData"), 1, KAttachDb1));
+ TEST2(err, KErrPermissionDenied);
+
+ buf = HBufC8::NewLC(100);
+ bufPtr.Set(buf->Des());
+ err = TSqlBlob::Get(TheDb, _L("symbian_security"), _L("PolicyData"), bufPtr, 1, KAttachDb1);
+ TEST2(err, KErrPermissionDenied);
+ CleanupStack::PopAndDestroy(buf);
+
+ CleanupClosePushL(wrStrm);
+ TRAP(err, wrStrm.OpenL(TheDb, _L("symbian_security"), _L("PolicyData"), 1, KAttachDb1));
+ TEST2(err, KErrPermissionDenied);
+ CleanupStack::PopAndDestroy(&wrStrm);
+
+ TRAP(err, TSqlBlob::SetL(TheDb, _L("symbian_security"), _L("PolicyData"), _L8("VVVV"), 1, KAttachDb1));
+ TEST2(err, KErrPermissionDenied);
+
+ TheDb.Close();
+ }
+
+void DoTestsL()
+ {
+ CreateDatabases();
+
+ TheTest.Start(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1641 ===Open non-secure database, attach secure database "));
+ Test1();
+
+ TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1642 ===Open secure database, attach secure database "));
+ Test2();
+
+ TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1814 SQL injection test "));
+ SqlInjectionTest();
+
+ TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-3507 DEF109100 - SQL, code coverage for TSqlBufRIterator,TSqlAttachDbRefCounter is very low "));
+ TwoConnAttachTest();
+
+ TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-3515 RSqlStatement::DeclaredColumnType() and attached databases test "));
+ DeclaredColumnTypeTest();
+
+ TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-4016 DEF116713 SQL: No redindexing occurs for an attached database "));
+ DEF116713();
+
+ TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-4042 RSqlDatabase::Size(TSize) - attached database, injection test"));
+ Size2InjectionTest();
+
+ TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-4043 RSqlDatabase::Compact() - attached database, injection test"));
+ CompactInjectionTest();
+
+ TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-4094 Incremental blob attached test"));
+ BlobAttachedTestL();
+ }
+
+TInt E32Main()
+ {
+ TheTest.Title();
+
+ CTrapCleanup* tc = CTrapCleanup::New();
+
+ __UHEAP_MARK;
+
+ CreateTestDir();
+ DeleteDatabases();
+
+ TRAPD(err, DoTestsL());
+ DeleteDatabases();
+ TEST2(err, KErrNone);
+
+ __UHEAP_MARKEND;
+
+ TheTest.End();
+ TheTest.Close();
+
+ delete tc;
+
+ User::Heap().Check();
+ return KErrNone;
+ }