|
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 // |
|
15 |
|
16 #include <e32test.h> |
|
17 #include <bautils.h> |
|
18 #include <sqldb.h> |
|
19 |
|
20 /////////////////////////////////////////////////////////////////////////////////////// |
|
21 |
|
22 RTest TheTest(_L("t_sqlattach test")); |
|
23 |
|
24 RSqlDatabase TheDb; |
|
25 RSqlDatabase TheDb2; |
|
26 |
|
27 _LIT(KTestDir, "c:\\test\\"); |
|
28 |
|
29 _LIT(KTestDb1, "c:\\test\\t_sqlattach_1.db"); |
|
30 _LIT(KTestDb2, "c:\\test\\t_sqlattach_2.db"); |
|
31 _LIT(KTestDb3, "c:\\test\\t_sqlattach_3.db"); |
|
32 _LIT(KTestDb4, "c:\\test\\t_sqlattach_4.db"); |
|
33 |
|
34 _LIT(KSecureTestDb1, "c:[21212122]BBDb2.db");//Created outside this test app |
|
35 _LIT(KSecureTestDb2, "c:[21212122]AADb2.db");//Created outside this test app |
|
36 _LIT(KSecureTestDb3, "c:[21212123]t_sqlattach_3.db"); |
|
37 _LIT(KDbNameInjection, "c:\\test\\t_sqlattach_3.db' as db; delete from a;"); |
|
38 |
|
39 //const TUid KSecureUid = {0x21212122};//The UID of the secure test databases: KSecureTestDb1 and KSecureTestDb2 |
|
40 |
|
41 //The test uses two secure databases: KSecureTestDb1 and KSecureTestDb2. |
|
42 // |
|
43 //KSecureTestDb1 schema |
|
44 //TABLE A1(F1 INTEGER , F2 INTEGER, B1 BLOB) |
|
45 // |
|
46 //KSecureTestDb1 security settings |
|
47 //-Security UID = KSecureUid |
|
48 //-Schema policy = ECapabilityTrustedUI |
|
49 //-Read policy = ECapabilityReadDeviceData |
|
50 //-Write policy = ECapabilityWriteDeviceData |
|
51 //The test application can read/write the database tables but cannot modify the database structure |
|
52 // |
|
53 //KSecureTestDb2 schema |
|
54 //TABLE C(A1 INTEGER, B2 BLOB) |
|
55 // |
|
56 //KSecureTestDb2 security settings |
|
57 //-Security UID = KSecureUid |
|
58 //-Schema policy = ECapabilityDiskAdmin |
|
59 //-Read policy = ECapabilityNetworkControl |
|
60 //-Write policy = ECapabilityWriteDeviceData |
|
61 //The test application can write to the database tables but cannot modify the database structure or read from tables |
|
62 |
|
63 /////////////////////////////////////////////////////////////////////////////////////// |
|
64 |
|
65 void DeleteDatabases() |
|
66 { |
|
67 TheDb2.Close(); |
|
68 TheDb.Close(); |
|
69 (void)RSqlDatabase::Delete(KDbNameInjection); |
|
70 (void)RSqlDatabase::Delete(KSecureTestDb3); |
|
71 (void)RSqlDatabase::Delete(KTestDb4); |
|
72 (void)RSqlDatabase::Delete(KTestDb3); |
|
73 (void)RSqlDatabase::Delete(KTestDb2); |
|
74 (void)RSqlDatabase::Delete(KTestDb1); |
|
75 } |
|
76 |
|
77 /////////////////////////////////////////////////////////////////////////////////////// |
|
78 //Test macros and functions |
|
79 void Check(TInt aValue, TInt aLine) |
|
80 { |
|
81 if(!aValue) |
|
82 { |
|
83 DeleteDatabases(); |
|
84 TheTest(EFalse, aLine); |
|
85 } |
|
86 } |
|
87 void Check(TInt aValue, TInt aExpected, TInt aLine) |
|
88 { |
|
89 if(aValue != aExpected) |
|
90 { |
|
91 DeleteDatabases(); |
|
92 RDebug::Print(_L("*** Expected error: %d, got: %d\r\n"), aExpected, aValue); |
|
93 TheTest(EFalse, aLine); |
|
94 } |
|
95 } |
|
96 #define TEST(arg) ::Check((arg), __LINE__) |
|
97 #define TEST2(aValue, aExpected) ::Check(aValue, aExpected, __LINE__) |
|
98 |
|
99 /////////////////////////////////////////////////////////////////////////////////////// |
|
100 |
|
101 void CreateTestDir() |
|
102 { |
|
103 RFs fs; |
|
104 TInt err = fs.Connect(); |
|
105 TEST2(err, KErrNone); |
|
106 |
|
107 err = fs.MkDir(KTestDir); |
|
108 TEST(err == KErrNone || err == KErrAlreadyExists); |
|
109 |
|
110 fs.Close(); |
|
111 } |
|
112 |
|
113 void CreateDatabases() |
|
114 { |
|
115 TBuf<100> sql; |
|
116 |
|
117 TInt err = TheDb.Create(KTestDb1); |
|
118 TEST2(err, KErrNone); |
|
119 sql.Copy(_L("CREATE TABLE A1(F1 INTEGER, F2 INTEGER)")); |
|
120 err = TheDb.Exec(sql); |
|
121 TEST(err >= 0); |
|
122 sql.Copy(_L("CREATE TABLE A2(DDD INTEGER)")); |
|
123 err = TheDb.Exec(sql); |
|
124 TEST(err >= 0); |
|
125 TheDb.Close(); |
|
126 |
|
127 err = TheDb.Create(KTestDb2); |
|
128 TEST2(err, KErrNone); |
|
129 sql.Copy(_L("CREATE TABLE B(A1 INTEGER, A2 INTEGER)")); |
|
130 err = TheDb.Exec(sql); |
|
131 TEST(err >= 0); |
|
132 TheDb.Close(); |
|
133 } |
|
134 |
|
135 /////////////////////////////////////////////////////////////////////////////////////// |
|
136 /////////////////////////////////////////////////////////////////////////////////////// |
|
137 |
|
138 /** |
|
139 @SYMTestCaseID SYSLIB-SQL-CT-1641 |
|
140 @SYMTestCaseDesc Attached database tests. |
|
141 Open non-secure database, attach secure database. |
|
142 The test application's security policy allows read/write operations on the attached |
|
143 database, but database schema modifications are not allowed. The test executes |
|
144 different kind of SQL statements to verify that the test application's security |
|
145 policy is properly asserted by the SQL server. |
|
146 @SYMTestPriority High |
|
147 @SYMTestActions Execution SQL statements on attached database. |
|
148 @SYMTestExpectedResults Test must not fail |
|
149 @SYMREQ REQ5792 |
|
150 REQ5793 |
|
151 */ |
|
152 void Test1() |
|
153 { |
|
154 TInt err = TheDb.Open(KTestDb1); |
|
155 TEST2(err, KErrNone); |
|
156 |
|
157 //Attach a secure database, the logical database name length is 0 |
|
158 _LIT(KAttachDb0, ""); |
|
159 err = TheDb.Attach(KSecureTestDb1, KAttachDb0); |
|
160 TEST2(err, KErrBadName); |
|
161 |
|
162 //Attach a secure database, the logical database name length is > than KMaxFileName |
|
163 TBuf<KMaxFileName + 1> longDbName; |
|
164 longDbName.SetLength(longDbName.MaxLength()); |
|
165 longDbName.Fill(TChar('A')); |
|
166 err = TheDb.Attach(KSecureTestDb1, longDbName); |
|
167 TEST2(err, KErrBadName); |
|
168 |
|
169 //Attach a secure database |
|
170 //The test application can read/write the attached database tables but cannot modify the database structure |
|
171 _LIT(KAttachDb1, "Db1"); |
|
172 err = TheDb.Attach(KSecureTestDb1, KAttachDb1); |
|
173 TEST2(err, KErrNone); |
|
174 |
|
175 //Attempt to read from the attached secure database |
|
176 err = TheDb.Exec(_L("SELECT * FROM db1.a1")); |
|
177 TEST(err >= 0); |
|
178 //Attempt to write to the attached secure database |
|
179 err = TheDb.Exec(_L("INSERT INTO dB1.a1(f1) valUES(10)")); |
|
180 TEST2(err, 1); |
|
181 //Attempt to modify the attached secure database schema |
|
182 err = TheDb.Exec(_L("CREATE TABLE db1.CCC(H REAL)")); |
|
183 TEST2(err, KErrPermissionDenied); |
|
184 err = TheDb.Exec(_L("ALTER TABLE db1.A1 ADD COLUMN a2 integer")); |
|
185 TEST2(err, KErrPermissionDenied); |
|
186 |
|
187 //Attempt to read from the main non-secure database |
|
188 err = TheDb.Exec(_L("SELECT * FROM main.a1")); |
|
189 TEST(err >= 0); |
|
190 //Attempt to write to the main non-secure database |
|
191 err = TheDb.Exec(_L("INSERT INTO a1(f1) valUES(10)")); |
|
192 TEST2(err, 1); |
|
193 //Attempt to modify the main non-secure database schema |
|
194 err = TheDb.Exec(_L("CREATE TABLE a3(H REAL)")); |
|
195 TEST(err >= 0); |
|
196 |
|
197 TheTest.Printf(_L("===Attach second, non-secure database")); |
|
198 //Attach a non-secure database |
|
199 //The test application should be able to do everything with the attached database |
|
200 _LIT(KAttachDb2, "db2"); |
|
201 err = TheDb.Attach(KTestDb2, KAttachDb2); |
|
202 TEST2(err, KErrNone); |
|
203 |
|
204 //Attempt to read from the attached non-secure database |
|
205 err = TheDb.Exec(_L("SELECT * FROM db2.B")); |
|
206 TEST(err >= 0); |
|
207 //Attempt to write to the attached non-secure database |
|
208 err = TheDb.Exec(_L("INSERT INTO dB2.b(a2) ValUES(112)")); |
|
209 TEST2(err, 1); |
|
210 //Attempt to modify the attached non-secure database schema |
|
211 err = TheDb.Exec(_L("ALTER TABLE db2.b ADD COLUMN a3 text")); |
|
212 TEST(err >= 0); |
|
213 |
|
214 TheTest.Printf(_L("===Attach third, non-secure database (the main database)")); |
|
215 //Attach a non-secure database (the main database) |
|
216 //The test application should be able to do everything with the attached database |
|
217 _LIT(KAttachDb3, "db3"); |
|
218 err = TheDb.Attach(KTestDb1, KAttachDb3); |
|
219 TEST2(err, KErrNone); |
|
220 |
|
221 //Attempt to read from the third, non-secure database |
|
222 err = TheDb.Exec(_L("SELECT * FROM db3.a1")); |
|
223 TEST(err >= 0); |
|
224 //Attempt to write to the third, non-secure database |
|
225 err = TheDb.Exec(_L("INSERT INTO db3.a1(f2) values(11)")); |
|
226 TEST2(err, 1); |
|
227 //Attempt to modify the third, non-secure database schema |
|
228 err = TheDb.Exec(_L("CREATE TABLE db3.a4(s blob)")); |
|
229 TEST(err < 0);//Cannot modify the main database from the atatched!? |
|
230 |
|
231 TheTest.Printf(_L("===Attach fourth, secure database")); |
|
232 //Attach a secure database |
|
233 //The test application can only write to the database, but cannot modify the schema or read from the database |
|
234 _LIT(KAttachDb4, "db4"); |
|
235 err = TheDb.Attach(KSecureTestDb2, KAttachDb4); |
|
236 TEST2(err, KErrNone); |
|
237 |
|
238 //Attempt to read from the attached secure database |
|
239 err = TheDb.Exec(_L("SELECT * FROM db4.c")); |
|
240 TEST2(err, KErrPermissionDenied); |
|
241 //Attempt to write to the attached secure database |
|
242 err = TheDb.Exec(_L("INSERT INTO Db4.c(a1) VALUES(1)")); |
|
243 TEST2(err, 1); |
|
244 //Attempt to write to a non-secure database using data from the attached secure database |
|
245 err = TheDb.Exec(_L("INSERT INTO a1(f1) select db4.c.a1 from db4.c")); |
|
246 TEST2(err, KErrPermissionDenied); |
|
247 //Attempt to write to a secure database using data from a non-secure database |
|
248 err = TheDb.Exec(_L("INSERT INTO db4.c(a1) select f1 from a1")); |
|
249 TEST(err >= 0); |
|
250 err = TheDb.Exec(_L("UPDATE db4.C SET a1 = 3 WHERE a1 = 1")); |
|
251 TEST2(err, KErrPermissionDenied);//!?!?!? |
|
252 err = TheDb.Exec(_L("DELETE FROM db4.C")); |
|
253 TEST(err >= 0); |
|
254 //Attempt to modify the attached secure database schema |
|
255 err = TheDb.Exec(_L("CREATE TABLE db4.CCC(z integer)")); |
|
256 TEST2(err, KErrPermissionDenied); |
|
257 err = TheDb.Exec(_L("DROP table db4.C")); |
|
258 TEST2(err, KErrPermissionDenied); |
|
259 |
|
260 err = TheDb.Detach(KAttachDb2); |
|
261 TEST2(err, KErrNone); |
|
262 err = TheDb.Detach(KAttachDb1); |
|
263 TEST2(err, KErrNone); |
|
264 |
|
265 err = TheDb.Detach(KAttachDb4); |
|
266 TEST2(err, KErrNone); |
|
267 err = TheDb.Exec(_L("SELECT * FROM db4.c")); |
|
268 TEST(err < 0); |
|
269 |
|
270 err = TheDb.Detach(KAttachDb2); |
|
271 TEST(err != KErrNone); |
|
272 |
|
273 err = TheDb.Detach(KAttachDb3); |
|
274 TEST2(err, KErrNone); |
|
275 err = TheDb.Exec(_L("INSERT INTO db3.a1(f2) values(11)")); |
|
276 TEST(err < 0); |
|
277 |
|
278 err = TheDb.Detach(KAttachDb4); |
|
279 TEST(err != KErrNone); |
|
280 |
|
281 TheDb.Close(); |
|
282 } |
|
283 |
|
284 /** |
|
285 @SYMTestCaseID SYSLIB-SQL-CT-1642 |
|
286 @SYMTestCaseDesc Attached database tests. |
|
287 Open secure database, attach secure database. |
|
288 The test application's security policy allows read/write operations on the main |
|
289 database, but database schema modifications are not allowed. The test application |
|
290 is allowed to write to the attached database but can't read from or modify the schema. |
|
291 The test executes different kind of SQL statements to verify that the test application's security |
|
292 policy is properly asserted by the SQL server. |
|
293 @SYMTestPriority High |
|
294 @SYMTestActions Execution SQL statements on attached database. |
|
295 @SYMTestExpectedResults Test must not fail |
|
296 @SYMREQ REQ5792 |
|
297 REQ5793 |
|
298 */ |
|
299 void Test2() |
|
300 { |
|
301 //The test application can read/write the database tables but cannot modify the database structure |
|
302 TInt err = TheDb.Open(KSecureTestDb1); |
|
303 TEST2(err, KErrNone); |
|
304 _LIT(KAttachDb2, "Db2"); |
|
305 //The test application can only write to the database, but cannot modify the schema or read from the database |
|
306 err = TheDb.Attach(KSecureTestDb2, KAttachDb2); |
|
307 TEST2(err, KErrNone); |
|
308 |
|
309 //Attempt to read from the main database and write to the attached database |
|
310 err = TheDb.Exec(_L("INSERT INTO db2.c(a1) SELECT f1 FROM a1")); |
|
311 TEST(err >= 0); |
|
312 |
|
313 //Attempt to read from the attached database and write to the main database |
|
314 err = TheDb.Exec(_L("INSERT INTO a1(f2) SELECT a1 FROM db2.c")); |
|
315 TEST2(err, KErrPermissionDenied); |
|
316 |
|
317 //Attempt to detach database using DETACH sql statement directly. |
|
318 //Executed only in release mode because the server will panic in _DEBUG mode |
|
319 #ifndef _DEBUG |
|
320 err = TheDb.Exec(_L("DETACH DATABASE DB2")); |
|
321 TEST2(err, KErrPermissionDenied); |
|
322 #endif |
|
323 |
|
324 err = TheDb.Detach(KAttachDb2); |
|
325 TEST2(err, KErrNone); |
|
326 |
|
327 //Attempt to attach a database using ATTACH sql statement directly. |
|
328 TBuf<100> sql; |
|
329 sql.Format(_L("ATTACH DATABASE '%S' AS Db3"), &KSecureTestDb2); |
|
330 err = TheDb.Exec(sql); |
|
331 TEST2(err, KErrPermissionDenied); |
|
332 |
|
333 TheDb.Close(); |
|
334 } |
|
335 |
|
336 /** |
|
337 @SYMTestCaseID SYSLIB-SQL-CT-1814 |
|
338 @SYMTestCaseDesc Attached database tests. SQL injection. |
|
339 Create the following test databases: |
|
340 1) c:\test\inj.db |
|
341 2) c:\test\inj.db' as db; delete from a; |
|
342 3) c:[21212123]Injected.db |
|
343 Insert some records in database (3). Attach database (2) to database (3). |
|
344 Check the records count of table A. If the count is zero, then it means that the injection has been successful |
|
345 and a security hole exists when attaching/detaching databases. |
|
346 @SYMTestPriority High |
|
347 @SYMTestActions Attached database tests. SQL injection. |
|
348 @SYMTestExpectedResults Test must not fail |
|
349 @SYMREQ REQ5792 |
|
350 REQ5793 |
|
351 */ |
|
352 void SqlInjectionTest() |
|
353 { |
|
354 //Create the database, which name is used for the attack. |
|
355 //This is done just to ensure that the database, which name is used in the SQL injection, exists, |
|
356 //Otherwise the injection attack may fail with KErrNotFound error. |
|
357 TInt err = TheDb2.Create(KTestDb3); |
|
358 TEST2(err, KErrNone); |
|
359 TheDb2.Close(); |
|
360 err = TheDb2.Create(KDbNameInjection); |
|
361 TEST2(err, KErrNone); |
|
362 TheDb2.Close(); |
|
363 //Create a secure database, which will be impacted by the SQL injection |
|
364 TSecurityPolicy policy(TSecurityPolicy::EAlwaysPass); |
|
365 RSqlSecurityPolicy dbPolicy; |
|
366 err = dbPolicy.Create(policy); |
|
367 TEST2(err, KErrNone); |
|
368 err = TheDb.Create(KSecureTestDb3, dbPolicy); |
|
369 TEST2(err, KErrNone); |
|
370 err = TheDb.Exec(_L("CREATE TABLE A(Id Integer)")); |
|
371 TEST(err >= 0); |
|
372 err = TheDb.Exec(_L("INSERT INTO A(Id) VALUES(1)")); |
|
373 TEST(err >= 0); |
|
374 err = TheDb.Exec(_L("INSERT INTO A(Id) VALUES(2)")); |
|
375 TEST(err >= 0); |
|
376 const TInt KInsertedRecCnt = 2; |
|
377 //Cleanup |
|
378 dbPolicy.Close(); |
|
379 TheDb.Close(); |
|
380 //Repopen the secure database and attach the secind database, which file name is actually a SQL injection |
|
381 err = TheDb.Open(KSecureTestDb3); |
|
382 TEST2(err, KErrNone); |
|
383 err = TheDb.Attach(KDbNameInjection, _L("Db2")); |
|
384 TEST2(err, KErrNone); |
|
385 //Check table A contents. If the security hole still exists, table A content is gone. |
|
386 TSqlScalarFullSelectQuery query(TheDb); |
|
387 TInt recCnt = 0; |
|
388 TRAP(err, recCnt = query.SelectIntL(_L("SELECT COUNT(*) FROM A"))); |
|
389 TEST2(err, KErrNone); |
|
390 TEST2(recCnt, KInsertedRecCnt);//if zero records count - successfull SQL injection - the security hole exists! |
|
391 //Try to execute RSqlDatabase::Detach(), where instead of a logical database name, SQL statement is supplied. |
|
392 err = TheDb.Detach(_L("DB; INSERT INTO A(Id) VALUES(3)")); |
|
393 TEST(err != KErrNone); |
|
394 //Check table A contents. If the security hole still exists, table A will have one more record. |
|
395 TRAP(err, recCnt = query.SelectIntL(_L("SELECT COUNT(*) FROM A"))); |
|
396 TEST2(err, KErrNone); |
|
397 TEST2(recCnt, KInsertedRecCnt);//if one more record - successfull SQL injection - the security hole exists! |
|
398 TheDb.Close(); |
|
399 //Cleanup |
|
400 (void)RSqlDatabase::Delete(KDbNameInjection); |
|
401 (void)RSqlDatabase::Delete(KTestDb3); |
|
402 (void)RSqlDatabase::Delete(KSecureTestDb3); |
|
403 } |
|
404 |
|
405 /** |
|
406 @SYMTestCaseID SYSLIB-SQL-UT-3507 |
|
407 @SYMTestCaseDesc Test for DEF109100: SQL, code coverage for TSqlBufRIterator, TSqlAttachDbRefCounter is very low. |
|
408 The test opens two existing databases, and the attaches to them the same secure shared database. |
|
409 @SYMTestPriority High |
|
410 @SYMTestActions Test for DEF109100: SQL, code coverage for TSqlBufRIterator, TSqlAttachDbRefCounter is very low. |
|
411 @SYMTestExpectedResults Test must not fail |
|
412 @SYMDEF DEF109100 |
|
413 */ |
|
414 void TwoConnAttachTest() |
|
415 { |
|
416 //Connection 1 |
|
417 TInt err = TheDb.Open(KTestDb1); |
|
418 TEST2(err, KErrNone); |
|
419 //Connection 2 |
|
420 err = TheDb2.Open(KTestDb2); |
|
421 TEST2(err, KErrNone); |
|
422 //Attach KSecureTestDb1 to connection 1 |
|
423 _LIT(KAttachDb1, "Db1"); |
|
424 err = TheDb.Attach(KSecureTestDb1, KAttachDb1); |
|
425 TEST2(err, KErrNone); |
|
426 //Attach KSecureTestDb1 to connection 2 |
|
427 err = TheDb2.Attach(KSecureTestDb1, KAttachDb1); |
|
428 TEST2(err, KErrNone); |
|
429 //Detach |
|
430 err = TheDb2.Detach(KAttachDb1); |
|
431 TEST2(err, KErrNone); |
|
432 err = TheDb.Detach(KAttachDb1); |
|
433 TEST2(err, KErrNone); |
|
434 //Cleanup |
|
435 TheDb2.Close(); |
|
436 TheDb.Close(); |
|
437 } |
|
438 |
|
439 /** |
|
440 @SYMTestCaseID SYSLIB-SQL-UT-3515 |
|
441 @SYMTestCaseDesc RSqlStatement::DeclaredColumnType() test |
|
442 The test creates 2 tables in two different databases. Then the test opens the first database and |
|
443 attaches the second one. After that a SELECT sql statement is prepared and the statement operates |
|
444 on both tables: from the main database and the attached one. |
|
445 DeclaredColumnType() is called after the statement preparation and column types checked. |
|
446 @SYMTestPriority High |
|
447 @SYMTestActions RSqlStatement::ColumnCount() test |
|
448 @SYMTestExpectedResults Test must not fail |
|
449 @SYMREQ REQ8035 |
|
450 */ |
|
451 void DeclaredColumnTypeTest() |
|
452 { |
|
453 //Preparation |
|
454 TInt err = TheDb.Open(KTestDb1); |
|
455 TEST2(err, KErrNone); |
|
456 err = TheDb.Exec(_L("CREATE TABLE Y(Id INTEGER, Name TEXT)")); |
|
457 TEST(err >= 0); |
|
458 TheDb.Close(); |
|
459 err = TheDb.Open(KTestDb2); |
|
460 TEST2(err, KErrNone); |
|
461 err = TheDb.Exec(_L("CREATE TABLE Z(Id INTEGER, Data BLOB)")); |
|
462 TEST(err >= 0); |
|
463 TheDb.Close(); |
|
464 //Open KTestDb1, attach KTestDb2 |
|
465 err = TheDb.Open(KTestDb1); |
|
466 TEST2(err, KErrNone); |
|
467 _LIT(KAttachDb, "Db2"); |
|
468 err = TheDb.Attach(KTestDb2, KAttachDb); |
|
469 TEST2(err, KErrNone); |
|
470 //SELECT from both db |
|
471 RSqlStatement stmt; |
|
472 err = stmt.Prepare(TheDb, _L("SELECT Y.Id, Y.Name, DB2.Z.Data FROM Y,DB2.Z WHERE Y.Id = DB2.Z.Id")); |
|
473 TEST2(err, KErrNone); |
|
474 TInt colCnt = stmt.ColumnCount(); |
|
475 TEST2(colCnt, 3); |
|
476 TSqlColumnType colType; |
|
477 err = stmt.DeclaredColumnType(0, colType); |
|
478 TEST2(err, KErrNone); |
|
479 TEST2(colType, ESqlInt); |
|
480 err = stmt.DeclaredColumnType(1, colType); |
|
481 TEST2(err, KErrNone); |
|
482 TEST2(colType, ESqlText); |
|
483 err = stmt.DeclaredColumnType(2, colType); |
|
484 TEST2(err, KErrNone); |
|
485 TEST2(colType, ESqlBinary); |
|
486 stmt.Close(); |
|
487 //Cleanup |
|
488 err = TheDb.Detach(KAttachDb); |
|
489 TEST2(err, KErrNone); |
|
490 TheDb.Close(); |
|
491 } |
|
492 |
|
493 /** |
|
494 @SYMTestCaseID SYSLIB-SQL-UT-4016 |
|
495 @SYMTestCaseDesc Test for DEF116713 SQL: No redindexing occurs for an attached database. |
|
496 The test does the following steps: |
|
497 1) Sets the "CollationDllName" column value in the "symbian_settings" stable of the database to be used |
|
498 as an attached database (KTestDb2). The set column value is different than the default collation dll name. |
|
499 2) Opens KTestDb1, attaches KTestDb2. |
|
500 3) When KTestDb2 is attached to KTestDb1, the SQL server should detect that the "CollationDllName" column |
|
501 value is different than the default collation dll name and should reindex the attached database and then |
|
502 store the current collation dll name in the "CollationDllName" column. |
|
503 4) The test checks that after attaching the KTestDb2 database, the "CollationDllName" column value |
|
504 is not the previously used test collation dll name. |
|
505 @SYMTestPriority Low |
|
506 @SYMTestActions Test for DEF116713 SQL: No redindexing occurs for an attached database. |
|
507 @SYMTestExpectedResults Test must not fail |
|
508 @SYMDEF DEF116713 |
|
509 */ |
|
510 void DEF116713() |
|
511 { |
|
512 //Set the "CollationDllName" column value in "symbian_settings" table of the database to be attached - |
|
513 //not to be the default collation dll name. |
|
514 TInt err = TheDb.Open(KTestDb2); |
|
515 TEST2(err, KErrNone); |
|
516 err = TheDb.Exec(_L("UPDATE symbian_settings SET CollationDllName='ddkjrrm'")); |
|
517 TEST2(err, 1); |
|
518 TheDb.Close(); |
|
519 //Open the main database, attach the other one |
|
520 err = TheDb.Open(KTestDb1); |
|
521 TEST2(err, KErrNone); |
|
522 err = TheDb.Attach(KTestDb2, _L("Db2")); |
|
523 TEST2(err, KErrNone); |
|
524 //The expectation is that the attached database is reindexed and the "CollationDllName" column value - set. |
|
525 RSqlStatement stmt; |
|
526 err = stmt.Prepare(TheDb, _L("SELECT CollationDllName FROM Db2.symbian_settings")); |
|
527 TEST2(err, KErrNone); |
|
528 err = stmt.Next(); |
|
529 TEST2(err, KSqlAtRow); |
|
530 TPtrC collationDllName; |
|
531 err = stmt.ColumnText(0, collationDllName); |
|
532 TEST2(err, KErrNone); |
|
533 stmt.Close(); |
|
534 TheDb.Close(); |
|
535 |
|
536 _LIT(KTestCollationDllName, "ddkjrrm");//The same as the used in the "UPDATE symbian_settings" sql. |
|
537 TEST(collationDllName != KTestCollationDllName); |
|
538 } |
|
539 |
|
540 /** |
|
541 @SYMTestCaseID SYSLIB-SQL-UT-4042 |
|
542 @SYMTestCaseDesc RSqlDatabase::Size(TSize&) on attached database - injection test. |
|
543 The test creates a database and attempts to attach another database, |
|
544 passing a DELETE SQL statement in the attached database name. |
|
545 The attach operation is expected to fail, the database content should stay |
|
546 unchanged after the operation. |
|
547 @SYMTestPriority High |
|
548 @SYMTestActions RSqlDatabase::Size(TSize&) on attached database - injection test. |
|
549 @SYMTestExpectedResults Test must not fail |
|
550 @SYMREQ REQ10407 |
|
551 */ |
|
552 void Size2InjectionTest() |
|
553 { |
|
554 TInt err = TheDb.Create(KTestDb4); |
|
555 TEST2(err, KErrNone); |
|
556 err = TheDb.Exec(_L("CREATE TABLE A(I INTEGER)")); |
|
557 TEST(err >= 0); |
|
558 err = TheDb.Exec(_L("INSERT INTO A VALUES(1)")); |
|
559 TEST2(err, 1); |
|
560 _LIT(KAttachDbName, "B"); |
|
561 err = TheDb.Attach(KTestDb4, KAttachDbName); |
|
562 TEST2(err, KErrNone); |
|
563 RSqlDatabase::TSize size; |
|
564 err = TheDb.Size(size, _L("B;DELETE FROM MAIN.A")); |
|
565 TEST2(err, KSqlErrGeneral); |
|
566 TPtrC msg = TheDb.LastErrorMessage(); |
|
567 TheTest.Printf(_L("RSqlDatabase::Size(TSize&) injection, error message: %S\r\n"), &msg); |
|
568 TSqlScalarFullSelectQuery q(TheDb); |
|
569 TInt reccnt = 0; |
|
570 TRAP(err, reccnt = q.SelectIntL(_L("SELECT COUNT(*) FROM MAIN.A"))); |
|
571 TEST2(err, KErrNone); |
|
572 TEST2(reccnt, 1); |
|
573 err = TheDb.Detach(KAttachDbName); |
|
574 TEST2(err, KErrNone); |
|
575 TheDb.Close(); |
|
576 (void)RSqlDatabase::Delete(KTestDb4); |
|
577 } |
|
578 |
|
579 /** |
|
580 @SYMTestCaseID SYSLIB-SQL-UT-4043 |
|
581 @SYMTestCaseDesc RSqlDatabase::Compact() on attached database - injection test. |
|
582 The test creates a database and attaches another database. |
|
583 Then the test attempts to compact the attached database calling |
|
584 RSqlDatabase::Compact() passing DROP TABLE and DELETE statements |
|
585 as name of the attached database. The call is expected to fail, |
|
586 the database content should stay unchanged after the call. |
|
587 @SYMTestPriority High |
|
588 @SYMTestActions RSqlDatabase::Compact() on attached database - injection test. |
|
589 @SYMTestExpectedResults Test must not fail |
|
590 @SYMREQ REQ10405 |
|
591 */ |
|
592 void CompactInjectionTest() |
|
593 { |
|
594 TInt err = TheDb.Create(KTestDb4); |
|
595 TEST2(err, KErrNone); |
|
596 err = TheDb.Exec(_L("CREATE TABLE A(I INTEGER); INSERT INTO A(I) VALUES(1)")); |
|
597 TEST(err >= 0); |
|
598 _LIT(KAttachDbName, "B"); |
|
599 err = TheDb.Attach(KTestDb4, KAttachDbName); |
|
600 TEST2(err, KErrNone); |
|
601 err = TheDb.Compact(RSqlDatabase::EMaxCompaction, _L("B;DROP B.A")); |
|
602 TEST2(err, KSqlErrGeneral); |
|
603 TPtrC msg = TheDb.LastErrorMessage(); |
|
604 TheTest.Printf(_L("RSqlDatabase::Compact() injection, error message: %S\r\n"), &msg); |
|
605 |
|
606 TSqlScalarFullSelectQuery query(TheDb); |
|
607 TInt recCount = 0; |
|
608 TRAP(err, recCount = query.SelectIntL(_L("SELECT COUNT(*) FROM A"))); |
|
609 TEST2(err, KErrNone); |
|
610 TEST2(recCount, 1); |
|
611 |
|
612 err = TheDb.Compact(8192, _L("B;DROP B.A;")); |
|
613 TEST2(err, KSqlErrGeneral); |
|
614 msg.Set(TheDb.LastErrorMessage()); |
|
615 TheTest.Printf(_L("RSqlDatabase::Compact() injection, error message: %S\r\n"), &msg); |
|
616 |
|
617 recCount = 0; |
|
618 TRAP(err, recCount = query.SelectIntL(_L("SELECT COUNT(*) FROM A"))); |
|
619 TEST2(err, KErrNone); |
|
620 TEST2(recCount, 1); |
|
621 |
|
622 TRequestStatus stat; |
|
623 TheDb.Compact(8192, stat, _L("B;DELETE FROM B.A;")); |
|
624 User::WaitForRequest(stat); |
|
625 TEST2(stat.Int(), KSqlErrGeneral); |
|
626 msg.Set(TheDb.LastErrorMessage()); |
|
627 TheTest.Printf(_L("RSqlDatabase::Compact() injection, error message: %S\r\n"), &msg); |
|
628 |
|
629 recCount = 0; |
|
630 TRAP(err, recCount = query.SelectIntL(_L("SELECT COUNT(*) FROM A"))); |
|
631 TEST2(err, KErrNone); |
|
632 TEST2(recCount, 1); |
|
633 |
|
634 err = TheDb.Detach(KAttachDbName); |
|
635 TEST2(err, KErrNone); |
|
636 TheDb.Close(); |
|
637 (void)RSqlDatabase::Delete(KTestDb4); |
|
638 } |
|
639 |
|
640 /** |
|
641 @SYMTestCaseID SYSLIB-SQL-UT-4094 |
|
642 @SYMTestCaseDesc Incremental blob i/o tests on an attached database. |
|
643 Open secure database, attach secure database. |
|
644 The test application's security policy allows incremental blob read & write |
|
645 operations on the main database, but only write operations on the attached database. |
|
646 The test attempts to read and write to a blob in the attached database to verify that |
|
647 the test application's security policy is properly asserted by the Symbian SQL server. |
|
648 @SYMTestPriority High |
|
649 @SYMTestActions Execution of blob read and write operations on the attached database. |
|
650 @SYMTestExpectedResults Test must not fail |
|
651 @SYMREQ REQ5794 |
|
652 */ |
|
653 void BlobAttachedTestL() |
|
654 { |
|
655 // Open the main secure database - the test application can read & write blobs in it |
|
656 // Attach another secure database - the test application can only write blobs in it |
|
657 TInt err = TheDb.Open(KSecureTestDb1); |
|
658 TEST2(err, KErrNone); |
|
659 _LIT(KAttachDb1, "Db1"); |
|
660 err = TheDb.Attach(KSecureTestDb2, KAttachDb1); |
|
661 TEST2(err, KErrNone); |
|
662 |
|
663 // Insert a new record into the attached database - the blob value is "AAAAAAAAAA" |
|
664 err = TheDb.Exec(_L("INSERT INTO Db1.C(A1, B2) VALUES(15, x'41414141414141414141')")); |
|
665 TEST2(err, 1); |
|
666 |
|
667 // Attempt to write to a blob in the attached database |
|
668 RSqlBlobWriteStream wrStrm; |
|
669 CleanupClosePushL(wrStrm); |
|
670 TRAP(err, wrStrm.OpenL(TheDb, _L("C"), _L("B2"), KSqlLastInsertedRowId, KAttachDb1)); |
|
671 TEST2(err, KErrNone); |
|
672 TRAP(err, wrStrm.WriteL(_L8("ZZZ"))); |
|
673 TEST2(err, KErrNone); |
|
674 CleanupStack::PopAndDestroy(&wrStrm); |
|
675 |
|
676 TRAP(err, TSqlBlob::SetL(TheDb, _L("C"), _L("B2"), _L8("YYYYY"), KSqlLastInsertedRowId, KAttachDb1)); |
|
677 TEST2(err, KErrNone); |
|
678 |
|
679 // Attempt to read a blob in the attached database |
|
680 RSqlBlobReadStream rdStrm; |
|
681 CleanupClosePushL(rdStrm); |
|
682 TRAP(err, rdStrm.OpenL(TheDb, _L("C"), _L("B2"), KSqlLastInsertedRowId, KAttachDb1)); |
|
683 TEST2(err, KErrPermissionDenied); |
|
684 CleanupStack::PopAndDestroy(&rdStrm); |
|
685 |
|
686 HBufC8* wholeBuf = NULL; |
|
687 TRAP(err, wholeBuf = TSqlBlob::GetLC(TheDb, _L("C"), _L("B2"), KSqlLastInsertedRowId, KAttachDb1)); |
|
688 TEST2(err, KErrPermissionDenied); |
|
689 |
|
690 HBufC8* buf = HBufC8::NewLC(10); |
|
691 TPtr8 bufPtr(buf->Des()); |
|
692 err = TSqlBlob::Get(TheDb, _L("C"), _L("B2"), bufPtr, KSqlLastInsertedRowId, KAttachDb1); |
|
693 TEST2(err, KErrPermissionDenied); |
|
694 CleanupStack::PopAndDestroy(buf); |
|
695 |
|
696 // SQLite and system tables in the attached database |
|
697 |
|
698 // Attempt to read from and write to the SQLite master table - |
|
699 // reads should be permitted because write capability is enough for this, |
|
700 // writes should not be permitted because schema capability is required for this |
|
701 TBuf8<20> data; |
|
702 CleanupClosePushL(rdStrm); |
|
703 TRAP(err, rdStrm.OpenL(TheDb, _L("sqlite_master"), _L("tbl_name"), 1, KAttachDb1)); // TEXT column |
|
704 TEST2(err, KErrNone); |
|
705 TRAP(err, rdStrm.ReadL(data, 1)); |
|
706 TEST2(err, KErrNone); |
|
707 CleanupStack::PopAndDestroy(&rdStrm); |
|
708 |
|
709 wholeBuf = TSqlBlob::GetLC(TheDb, _L("sqlite_master"), _L("tbl_name"), 1, KAttachDb1); |
|
710 TEST(wholeBuf->Length() > 0); |
|
711 CleanupStack::PopAndDestroy(wholeBuf); |
|
712 |
|
713 buf = HBufC8::NewLC(100); |
|
714 bufPtr.Set(buf->Des()); |
|
715 err = TSqlBlob::Get(TheDb, _L("sqlite_master"), _L("tbl_name"), bufPtr, 1, KAttachDb1); |
|
716 TEST2(err, KErrNone); |
|
717 TEST(bufPtr.Length() > 0); |
|
718 CleanupStack::PopAndDestroy(buf); |
|
719 |
|
720 CleanupClosePushL(wrStrm); |
|
721 TRAP(err, wrStrm.OpenL(TheDb, _L("sqlite_master"), _L("tbl_name"), 1, KAttachDb1)); |
|
722 TEST2(err, KErrPermissionDenied); |
|
723 CleanupStack::PopAndDestroy(&wrStrm); |
|
724 |
|
725 TRAP(err, TSqlBlob::SetL(TheDb, _L("sqlite_master"), _L("tbl_name"), _L8("VVVV"), 1, KAttachDb1)); |
|
726 TEST2(err, KErrPermissionDenied); |
|
727 |
|
728 // Attempt to read from and write to the system tables in the attached database - neither reads nor writes should be permitted |
|
729 CleanupClosePushL(rdStrm); |
|
730 TRAP(err, rdStrm.OpenL(TheDb, _L("symbian_security"), _L("PolicyData"), 1, KAttachDb1)); // BLOB column |
|
731 TEST2(err, KErrPermissionDenied); |
|
732 CleanupStack::PopAndDestroy(&rdStrm); |
|
733 |
|
734 TRAP(err, wholeBuf = TSqlBlob::GetLC(TheDb, _L("symbian_security"), _L("PolicyData"), 1, KAttachDb1)); |
|
735 TEST2(err, KErrPermissionDenied); |
|
736 |
|
737 buf = HBufC8::NewLC(100); |
|
738 bufPtr.Set(buf->Des()); |
|
739 err = TSqlBlob::Get(TheDb, _L("symbian_security"), _L("PolicyData"), bufPtr, 1, KAttachDb1); |
|
740 TEST2(err, KErrPermissionDenied); |
|
741 CleanupStack::PopAndDestroy(buf); |
|
742 |
|
743 CleanupClosePushL(wrStrm); |
|
744 TRAP(err, wrStrm.OpenL(TheDb, _L("symbian_security"), _L("PolicyData"), 1, KAttachDb1)); |
|
745 TEST2(err, KErrPermissionDenied); |
|
746 CleanupStack::PopAndDestroy(&wrStrm); |
|
747 |
|
748 TRAP(err, TSqlBlob::SetL(TheDb, _L("symbian_security"), _L("PolicyData"), _L8("VVVV"), 1, KAttachDb1)); |
|
749 TEST2(err, KErrPermissionDenied); |
|
750 |
|
751 TheDb.Close(); |
|
752 } |
|
753 |
|
754 void DoTestsL() |
|
755 { |
|
756 CreateDatabases(); |
|
757 |
|
758 TheTest.Start(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1641 ===Open non-secure database, attach secure database ")); |
|
759 Test1(); |
|
760 |
|
761 TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1642 ===Open secure database, attach secure database ")); |
|
762 Test2(); |
|
763 |
|
764 TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-CT-1814 SQL injection test ")); |
|
765 SqlInjectionTest(); |
|
766 |
|
767 TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-3507 DEF109100 - SQL, code coverage for TSqlBufRIterator,TSqlAttachDbRefCounter is very low ")); |
|
768 TwoConnAttachTest(); |
|
769 |
|
770 TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-3515 RSqlStatement::DeclaredColumnType() and attached databases test ")); |
|
771 DeclaredColumnTypeTest(); |
|
772 |
|
773 TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-4016 DEF116713 SQL: No redindexing occurs for an attached database ")); |
|
774 DEF116713(); |
|
775 |
|
776 TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-4042 RSqlDatabase::Size(TSize) - attached database, injection test")); |
|
777 Size2InjectionTest(); |
|
778 |
|
779 TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-4043 RSqlDatabase::Compact() - attached database, injection test")); |
|
780 CompactInjectionTest(); |
|
781 |
|
782 TheTest.Next(_L(" @SYMTestCaseID:SYSLIB-SQL-UT-4094 Incremental blob attached test")); |
|
783 BlobAttachedTestL(); |
|
784 } |
|
785 |
|
786 TInt E32Main() |
|
787 { |
|
788 TheTest.Title(); |
|
789 |
|
790 CTrapCleanup* tc = CTrapCleanup::New(); |
|
791 |
|
792 __UHEAP_MARK; |
|
793 |
|
794 CreateTestDir(); |
|
795 DeleteDatabases(); |
|
796 |
|
797 TRAPD(err, DoTestsL()); |
|
798 DeleteDatabases(); |
|
799 TEST2(err, KErrNone); |
|
800 |
|
801 __UHEAP_MARKEND; |
|
802 |
|
803 TheTest.End(); |
|
804 TheTest.Close(); |
|
805 |
|
806 delete tc; |
|
807 |
|
808 User::Heap().Check(); |
|
809 return KErrNone; |
|
810 } |