· 7 years ago · Apr 23, 2018, 07:46 PM
1Subject : Cyber and Information Security.
2PRACTICAL NO. 1
3Aim : Demonstrate techniques for file and data integrity.
4âž” Data Integrity :
5User1.java
6import java.net.*;
7import java.io.*;
8import java.util.*;
9import java.io.UnsupportedEncodingException;
10import java.security.MessageDigest;
11import java.security.NoSuchAlgorithmException;
12import java.util.Arrays;
13import java.util.Base64;
14import javax.crypto.Cipher;
15import javax.crypto.spec.SecretKeySpec;
16class User1
17{
18 DatagramSocket d;
19 DatagramPacket p,p1;
20 int i=0;
21 private static SecretKeySpec secretKey;
22 private static byte[] key;
23 final String Keys = "ssshhhhhhhhhhh!!!!";
24 User1()
25 {
26 try
27 {
28 d=new DatagramSocket(1000);
29 for(i=0;i<20;i++)
30 {
31
32 System.out.println("Enter a msg");
33 Scanner sc=new Scanner(System.in);
34 String msg=sc.next();
35
36 String encryptedString = User1.encrypt(msg,Keys) ;
37 p=new DatagramPacket(encryptedString.getBytes(),encryptedString.length(),InetAddress.getLocalHost(),2000);
38 d.send(p);
39 System.out.println("Encrypted Msg "+encryptedString);
40 System.out.println("msg sent");
41 receive();
42 }
43 }
44 catch(Exception e)
45 {
46 e.printStackTrace();
47 }
48
49 }
50 public void receive()
51 {
52 try
53 {
54 byte b[]=new byte[1024];
55 p1=new DatagramPacket(b,b.length);
56 d.receive(p1);
57 String s=new String(p1.getData());
58 String decryptedString = User1.decrypt(s.trim(),Keys) ;
59
60 System.out.println(decryptedString);
61 System.out.println(s.trim());
62 }
63 catch(Exception ex)
64 {
65 ex.printStackTrace();
66 }
67 }
68
69 public static void setKey(String myKey)
70 {
71 MessageDigest sha = null;
72 try {
73 key = myKey.getBytes("UTF-8");
74 sha = MessageDigest.getInstance("SHA-1");
75 key = sha.digest(key);
76 key = Arrays.copyOf(key, 16);
77 secretKey = new SecretKeySpec(key, "AES");
78 }
79 catch (NoSuchAlgorithmException e) {
80 e.printStackTrace();
81 }
82 catch (UnsupportedEncodingException e) {
83 e.printStackTrace();
84 }
85 }
86
87 public static String encrypt(String strToEncrypt, String secret)
88 {
89 try
90 {
91 setKey(secret);
92 Cipher cipher = Cipher.getInstance("AES/ECB/PKCS5Padding");
93 cipher.init(Cipher.ENCRYPT_MODE, secretKey);
94 return Base64.getEncoder().withoutPadding().encodeToString(cipher.doFinal(strToEncrypt.getBytes("UTF-8")));
95 }
96 catch (Exception e)
97 {
98 System.out.println("Error while encrypting: " + e.toString());
99 }
100 return null;
101 }
102
103 public static String decrypt(String strToDecrypt, String secret)
104 {
105 try
106 {
107 setKey(secret);
108 Cipher cipher = Cipher.getInstance("AES/ECB/PKCS5PADDING");
109 cipher.init(Cipher.DECRYPT_MODE, secretKey);
110 return new String(cipher.doFinal(Base64.getDecoder().decode(strToDecrypt)));
111 }
112 catch (Exception e)
113 {
114 System.out.println("Error while decrypting: " + e.toString());
115 }
116 return null;
117 }
118 public static void main(String s[])
119 {
120 new User1();
121 }
122}
123
124User2.java
125import java.net.*;
126import java.io.*;
127import java.util.*;
128import java.net.*;
129import java.io.*;
130import java.util.*;
131import java.io.UnsupportedEncodingException;
132import java.security.MessageDigest;
133import java.security.NoSuchAlgorithmException;
134import java.util.Arrays;
135import java.util.Base64;
136import javax.crypto.Cipher;
137import javax.crypto.spec.SecretKeySpec;class User2
138{
139 DatagramSocket d;
140 DatagramPacket p,p1;
141 int i=0;
142 private static SecretKeySpec secretKey;
143 private static byte[] key;
144 final String Keys = "ssshhhhhhhhhhh!!!!";
145
146 User2()
147 {
148 try
149 {
150 d=new DatagramSocket(2000);
151 for(i=0;i<20;i++)
152 {
153 byte b[]=new byte[1024];
154 p1=new DatagramPacket(b,b.length);
155 d.receive(p1);
156 String s=new String(p1.getData());
157 //byte[]text=s.getBytes();
158
159 System.out.println(s.trim());
160 String decryptedString = User2.decrypt(s.trim(),Keys) ;
161
162 System.out.println(decryptedString);
163
164 send();
165 }
166 }
167 catch(Exception e)
168 {
169 e.printStackTrace();
170 }
171
172 }
173 public void send()
174 {
175 try
176 {
177 System.out.println("Enter a msg");
178 Scanner sc=new Scanner(System.in);
179 String msg=sc.next();
180 String encryptedString = User2.encrypt(msg,Keys) ;
181 p=new DatagramPacket(encryptedString.getBytes(),encryptedString.length(),InetAddress.getLocalHost(),2000);
182 d.send(p);
183 System.out.println("Encrypted Msg "+encryptedString);
184 System.out.println("msg sent");
185 }
186 catch(Exception ex)
187 {
188 ex.printStackTrace();
189 }
190 }
191 public static void setKey(String myKey)
192 {
193 MessageDigest sha = null;
194 try {
195 key = myKey.getBytes("UTF-8");
196 sha = MessageDigest.getInstance("SHA-1");
197 key = sha.digest(key);
198 key = Arrays.copyOf(key, 16);
199
200 secretKey = new SecretKeySpec(key, "AES");
201 }
202 catch (NoSuchAlgorithmException e) {
203 e.printStackTrace();
204 }
205 catch (UnsupportedEncodingException e) {
206 e.printStackTrace();
207 }
208 }
209
210 public static String encrypt(String strToEncrypt, String secret)
211 {
212 try
213 {
214 setKey(secret);
215 Cipher cipher = Cipher.getInstance("AES/ECB/PKCS5Padding");
216 cipher.init(Cipher.ENCRYPT_MODE, secretKey);
217 return Base64.getEncoder().withoutPadding().encodeToString(cipher.doFinal(strToEncrypt.getBytes("UTF-8")));
218 }
219 catch (Exception e)
220 {
221 System.out.println("Error while encrypting: " + e.toString());
222 }
223 return null;
224 }
225 public static String decrypt(String strToDecrypt, String secret)
226 {
227 try
228 {
229 setKey(secret);
230 Cipher cipher = Cipher.getInstance("AES/ECB/PKCS5PADDING");
231 cipher.init(Cipher.DECRYPT_MODE, secretKey);
232 return new String(cipher.doFinal(Base64.getMimeDecoder().decode(strToDecrypt)));
233 }
234 catch (Exception e)
235 {
236 System.out.println("Error while decrypting: " + e.toString());
237 }
238 return null;
239 }
240 public static void main(String s[])
241 {
242 new User2();
243 }
244}
245
246Output :
247
248
249
250
251
252âž” File Integrity :
253Server.java
254import java.util.*;
255import javax.swing.*;
256import java.awt.*;
257import javax.swing.*;
258import java.awt.*;
259import java.awt.event.*;
260import java.net.*;
261import java.io.*;
262import java.util.*;
263import java.security.*;
264
265class Server extends JFrame implements ActionListener
266{
267 JLabel l1;
268 JTextField t1;
269 JTextArea ta;
270 JButton b1,b2,b3;
271 Socket s;
272 ServerSocket ss;
273 String z,x,y,hash;
274 String m[];
275 JScrollPane scroll;
276 Server()
277 {
278 super("Server");
279 try
280 {
281 ss=new ServerSocket(1000);
282 s=ss.accept();
283 l1=new JLabel("File Name:- ");
284 t1=new JTextField(20);
285 ta=new JTextArea(20,30);
286 scroll = new JScrollPane(ta);
287 b2=new JButton("Open");
288 b3=new JButton("Check Integrity");
289 setLayout(new FlowLayout());
290 add(l1);
291 add(t1);
292 add(b2);
293 add(b3);
294 add(scroll);
295 setSize(600,600);
296 setVisible(true);
297 b2.addActionListener(this);
298 b3.addActionListener(this);
299 InputStream in=s.getInputStream();
300 InputStream in2=s.getInputStream();
301 byte b[]=new byte[4096];
302 byte b2[]=new byte[1024];
303 in.read(b);
304 in2.read(b2);
305 String msg=new String(b);
306 String h=new String(b2);
307 m=msg.trim().split(" ");
308 System.out.println(m[0]);
309 System.out.println(h);
310 t1.setText(m[0]);
311 hash=h.trim();
312 System.out.println("Generated Hash:-" + hash);
313 setDefaultCloseOperation(WindowConstants.EXIT_ON_CLOSE);
314 }
315 catch(Exception e)
316 {
317 e.printStackTrace();
318 }
319
320 }
321 public static void main(String args[])
322 {
323 new Server();
324 }
325 public void actionPerformed(ActionEvent e)
326 {
327 Object o=e.getSource();
328 if(b2==o)
329 {
330 for(int i=1;i<m.length;i++)
331 ta.append(m[i] + " ");
332 }
333 if(b3==o)
334 {
335 try
336 {
337 MessageDigest digest = MessageDigest.getInstance("MD5");
338 byte[] inputBytes =ta.getText().trim().getBytes();
339 digest.update(inputBytes);
340 byte[] hashBytes = digest.digest();
341 System.out.println("Calculated HashCode: - " + new String(hashBytes));
342 if(hash.equals(new String(hashBytes)))
343 {
344 System.out.println("File Integrity is maintained");
345 }
346 else
347 {
348 System.out.println("File Integrity is not maintained");
349 }
350 }
351 catch(Exception e1)
352 {
353 e1.printStackTrace();
354 }
355 }
356 }
357}
358Client.java
359import java.util.*;
360import javax.swing.*;
361import java.awt.*;
362import javax.swing.*;
363import java.awt.*;
364import java.awt.event.*;
365import java.net.*;
366import java.io.*;
367import java.util.*;
368import java.security.*;
369
370class Client extends JFrame implements ActionListener
371{
372 JLabel l1;
373 JTextField t1;
374 JTextArea ta;
375 JButton b1,b2;
376 Socket s;
377 String z,x,y,hash;
378 byte b[];
379
380 Client()
381 {
382 super("Client");
383 try
384 {
385 s=new Socket(InetAddress.getByName("localhost"),1000);
386 b1=new JButton("browse");
387 t1=new JTextField(20);
388 b2=new JButton("send");
389 setLayout(new FlowLayout());
390 add(b1);
391 add(t1);
392 add(b2);
393 setSize(400,400);
394 setVisible(true);
395 b1.addActionListener(this);
396 b2.addActionListener(this);
397 }
398 catch(Exception e)
399 {
400 e.printStackTrace();
401
402 }
403 }
404 public static void main(String args[])
405 {
406 new Client();
407 }
408 public void actionPerformed(ActionEvent e)
409 {
410 Object o=e.getSource();
411 if(b1==o)
412 {
413 try
414 {
415 JFileChooser f=new JFileChooser();
416 f.showOpenDialog(null);
417 File d=f.getSelectedFile();
418 t1.setText(d.getName());
419 x=t1.getText();
420 InputStream in=new FileInputStream(d);
421 b=new byte[4096];
422 in.read(b);
423 y=new String(b).trim();
424 z=x + " " + y;
425 setDefaultCloseOperation(WindowConstants.EXIT_ON_CLOSE);
426 }
427 catch(Exception ae1)
428 {
429 ae1.printStackTrace();
430 }
431 }
432 if(b2==o)
433 {
434 try
435 {
436 MessageDigest digest = MessageDigest.getInstance("MD5");
437 byte[] inputBytes =y.getBytes();
438 digest.update(inputBytes);
439 byte[] hashBytes = digest.digest();
440 hash=new String(hashBytes);
441
442 System.out.println("Generated HashCode: - " + new String(hashBytes));
443 OutputStream out=s.getOutputStream();
444 out.write(z.trim().getBytes());
445 out.write(hashBytes);
446
447 System.out.println("File Sent");
448 }
449 catch(Exception ae)
450 {
451 ae.printStackTrace();
452 }
453 }
454 }
455}
456
457Output :
458
459 
460PRACTICAL NO. 2
461Aim : Demonstrate techniques to create multi-level access control in databases.
462Method 1 : Using Separation
463Step 1 - Create a sample SQL Server table
464create table cust
465(
466 CustId int Primary key not null,
467 Name varchar(50) not null,
468 AccNo varchar(10) not null,
469 TypeOfAcc varchar(10) not null
470);
471
472Command(s) completed successfully.
473
474
475insert into cust values(1001,'Hina','S1001','Saving');
476(1 row(s) affected)
477
478
479insert into cust values(1002,'Siddhi','C2001','Current');
480(1 row(s) affected)
481
482insert into cust values(1003,'Shreyash','C2004','Current');
483(1 row(s) affected)
484
485insert into cust values(1004,'Shameena','C2007','Current');
486(1 row(s) affected)
487
488insert into cust values(1005,'Sanesh','S1004','Saving');
489(1 row(s) affected)
490
491Step 2 - Create a different views for different uses.
492
493create view saving_view
494as
495select CustId,Name from cust where TypeOfAcc='Saving';
496Command(s) completed successfully.
497
498
499create view current_view
500as
501select CustId,Name from cust where TypeOfAcc='Current';
502Command(s) completed successfully.
503
504
505
506
507select * from saving_view;
508
509
510
511select * from current_view;
512
513
514
515Method 2 : Encryption
516Step 1 - Create a sample SQL Server table
517create table cust
518(
519 CustId int Primary key not null,
520 Name varchar(50) not null,
521 AccNo varchar(10) not null,
522 TypeOfAcc varchar(10) not null
523);
524
525Command(s) completed successfully.
526
527
528insert into cust values(1001,'Hina','S1001','Saving');
529(1 row(s) affected)
530
531
532insert into cust values(1002,'Siddhi','C2001','Current');
533(1 row(s) affected)
534
535insert into cust values(1003,'Shreyash','C2004','Current');
536(1 row(s) affected)
537
538insert into cust values(1004,'Shameena','C2007','Current');
539(1 row(s) affected)
540
541insert into cust values(1005,'Sanesh','S1004','Saving');
542(1 row(s) affected)
543Step 2 - SQL Server Service Master Key
544
545USE master;
546GO
547SELECT *
548FROM sys.symmetric_keys
549WHERE name = '##MS_ServiceMasterKey##';
550GO
551
552
553
554Step 3 - SQL Server Database Master Key
555use hina;
556go
557create master key encryption by password='password123';
558go
559
560Command(s) completed successfully.
561
562Step 4 - Create a Self Signed SQL Server Certificate:
563
564use hina;
565go
566create certificate certificate1
567with subject='Protect Data';
568go
569
570Command(s) completed successfully.
571
572
573Step 5 - SQL Server Symmetric Key
574use hina;
575go
576create symmetric key symmetricKey1
577with algorithm=AES_128
578encryption by certificate certificate1;
579go
580
581Command(s) completed successfully.
582
583
584
585Step 6 - Schema changes
586use hina;
587go
588alter table cust
589add AccEncrypt varbinary(MAX) NULL;
590go
591
592Command(s) completed successfully.
593
594Step 7 - Encrypting the newly created column
595use hina;
596go
597OPEN SYMMETRIC KEY SymmetricKey1
598DECRYPTION BY CERTIFICATE Certificate1;
599GO
600UPDATE cust
601SET AccEncrypt = EncryptByKey (Key_GUID('SymmetricKey1'),AccNo)
602FROM cust;
603GO
604CLOSE SYMMETRIC KEY SymmetricKey1;
605GO
606
607
608(5 row(s) affected)
609
610
611Step 8 - Remove old column
612alter table cust
613drop column AccNo;
614
615Command(s) completed successfully.
616
617
618
619Step 9 - Reading the SQL Server Encrypted Data
620open symmetric key symmetricKey1
621decryption by certificate certificate1;
622
623select CustId,AccEncrypt as 'Encrypted Account No',
624 convert(varchar,DECRYPTBYKEY(AccEncrypt)) as 'Decrypted Account No'
625 from cust;
626
627 close symmetric key symmetrickey1;
628
629
630
631Step 10 - Adding Records to the Table
632open symmetric key symmetricKey1
633decryption by certificate certificate1;
634
635insert into cust values (1006,'Tejas','Saving',ENCRYPTBYKEY(KEY_GUID('symmetricKey1'), CONVERT(varchar,'S1007')));
636
637(1 row(s) affected)r
638
639Step 11 - Accessing the Encrypted Data
640
641CREATE USER test1 WITHOUT LOGIN
642WITH DEFAULT_SCHEMA =dbo;
643
644grant select to test1;
645
646Command(s) completed successfully.
647
648execute as user='test1'
649select CustId,AccEncrypt as 'Encrypted Account No',
650 convert(varchar,DECRYPTBYKEY(AccEncrypt)) as 'Decrypted Account No'
651 from cust;
652
653Step 12 - Grant Permissions to the Encrypted Data
654GRANT VIEW DEFINITION ON SYMMETRIC KEY::SymmetricKey1 TO test;
655GRANT VIEW DEFINITION ON Certificate::Certificate1 TO test;
656
657Command(s) completed successfully.
658
659Method 3 : CHECKSUM
660
661create table tblUser
662(
663UserID INT IDENTITY(1,1) NOT NULL,
664LoginName NVARCHAR(40) NOT NULL,
665PasswordHash BINARY(64) NOT NULL,
666FirstName NVARCHAR(40) NULL,
667LastName NVARCHAR(40) NULL,
668CONSTRAINT [PK_User_UserID] PRIMARY KEY CLUSTERED (UserID ASC)
669)
670
671Command(s) completed successfully.
672
673
674CREATE PROCEDURE uspAddUser
675 @pLogin NVARCHAR(50),
676 @pPassword NVARCHAR(50),
677 @pFirstName NVARCHAR(40) = NULL,
678 @pLastName NVARCHAR(40) = NULL,
679 @responseMessage NVARCHAR(250) OUTPUT
680AS
681BEGIN
682 SET NOCOUNT ON
683 BEGIN TRY
684 INSERT INTO tblUser (LoginName, PasswordHash, FirstName, LastName)
685 VALUES (@pLogin, HASHBYTES('MD2',@pPassword), @pFirstName, @pLastName)
686
687 SET @responseMessage='Success'
688 END TRY
689
690 BEGIN CATCH
691 SET @responseMessage=ERROR_MESSAGE()
692 END CATCH
693END
694
695Command(s) completed successfully.
696
697
698DECLARE @responseMessage NVARCHAR(250)
699EXEC uspAddUser
700 @pLogin = N'Admin',
701 @pPassword = N'123',
702 @pFirstName = N'Admin',
703 @pLastName = N'Administrator',
704 @responseMessage=@responseMessage
705OUTPUT
706Select * from tblUser;
707
708
709
710
711ALTER TABLE tblUser ADD Salt UNIQUEIDENTIFIER
712
713
714Command(s) completed successfully.
715
716
717ALTER PROCEDURE uspAddUser
718 @pLogin NVARCHAR(50),
719 @pPassword NVARCHAR(50),
720 @pFirstName NVARCHAR(40) = NULL,
721 @pLastName NVARCHAR(40) = NULL,
722 @responseMessage NVARCHAR(250) OUTPUT
723AS
724BEGIN
725 SET NOCOUNT ON
726
727 DECLARE @salt UNIQUEIDENTIFIER
728 SET @salt = NEWID()
729 BEGIN TRY
730 INSERT INTO tblUser (LoginName, PasswordHash, Salt, FirstName, LastName)
731 VALUES(@pLogin, HASHBYTES('MD2', @pPassword+CAST(@salt AS NVARCHAR(36))), @salt, @pFirstName, @pLastName)
732
733 SET @responseMessage='Success'
734 END TRY
735
736 BEGIN CATCH
737 SET @responseMessage=ERROR_MESSAGE()
738 END CATCH
739END
740
741Command(s) completed successfully.
742
743
744TRUNCATE TABLE tblUser
745
746Command(s) completed successfully.
747
748DECLARE @responseMessage NVARCHAR(250)
749EXEC uspAddUser
750 @pLogin = N'Admin',
751 @pPassword = N'123',
752 @pFirstName = N'Admin',
753 @pLastName = N'Administrator',
754 @responseMessage=@responseMessage
755OUTPUT
756SELECT UserID, LoginName, PasswordHash, Salt, FirstName, LastName from tblUser
757
758
759
760
761CREATE PROCEDURE uspLogin
762 @pLoginName NVARCHAR(254),
763 @pPassword NVARCHAR(50),
764 @responseMessage NVARCHAR(250)='' OUTPUT
765AS
766BEGIN
767 SET NOCOUNT ON
768 DECLARE @userID INT
769
770 IF EXISTS (SELECT TOP 1 UserID FROM tblUser where LoginName=@pLoginName)
771 BEGIN
772 SET @userID = (SELECT UserID FROM tblUser WHERE LoginName = @pLoginName AND PasswordHash = HASHBYTES('MD2', @pPassword+CAST(Salt AS NVARCHAR(36))))
773
774 IF(@UserID IS NULL)
775 SET @responseMessage='Incorrect password'
776 ELSE
777 SET @responseMessage='User successfully logged in'
778 END
779 ELSE
780 SET @responseMessage='Invalid login'
781END
782
783Command(s) completed successfully.
784
785
786DECLARE @responseMessage NVARCHAR(250)
787EXEC uspLogin
788 @pLoginName = N'Admin',
789 @pPassword = N'123',
790 @responseMessage = @responseMessage
791OUTPUT
792SELECT @responseMessage as N'responseMessage'
793
794
795
796DECLARE @responseMessage NVARCHAR(250)
797EXEC uspLogin
798 @pLoginName = N'Admin1',
799 @pPassword = N'123',
800 @responseMessage = @responseMessage
801OUTPUT
802SELECT @responseMessage as N'responseMessage'
803
804
805
806DECLARE @responseMessage NVARCHAR(250)
807EXEC uspLogin
808 @pLoginName = N'Admin',
809 @pPassword = N'12322',
810 @responseMessage = @responseMessage
811OUTPUT
812SELECT @responseMessage as N'responseMessage'
813
814
815
816
817
818Method 4 : Single Cell Encryption
819Step 1 - Create a sample SQL Server table
820create table cust
821(
822 CustId int Primary key not null,
823 Name varchar(50) not null,
824 AccNo varchar(10) not null,
825 TypeOfAcc varchar(10) not null
826);
827
828Command(s) completed successfully.
829
830
831insert into cust values(1001,'Hina','S1001','Saving');
832(1 row(s) affected)
833
834
835insert into cust values(1002,'Siddhi','C2001','Current');
836(1 row(s) affected)
837
838insert into cust values(1003,'Shreyash','C2004','Current');
839(1 row(s) affected)
840
841insert into cust values(1004,'Shameena','C2007','Current');
842(1 row(s) affected)
843
844insert into cust values(1005,'Sanesh','S1004','Saving');
845(1 row(s) affected)
846Step 2 - SQL Server Service Master Key
847
848USE master;
849GO
850SELECT *
851FROM sys.symmetric_keys
852WHERE name = '##MS_ServiceMasterKey##';
853GO
854
855
856
857Step 3 - SQL Server Database Master Key
858use hina;
859go
860create master key encryption by password='password123';
861go
862
863Command(s) completed successfully.
864
865Step 4 - Create a Self Signed SQL Server Certificate:
866
867use hina;
868go
869create certificate certificate1
870with subject='Protect Data';
871go
872
873Command(s) completed successfully.
874
875
876Step 5 - SQL Server Symmetric Key
877use hina;
878go
879create symmetric key symmetricKey1
880with algorithm=AES_128
881encryption by certificate certificate1;
882go
883
884Command(s) completed successfully.
885
886Step 6 - Schema changes
887use hina;
888go
889alter table cust
890add AccNoEncrypt nvarchar(MAX) NULL;
891go
892
893Command(s) completed successfully.
894
895UPDATE cust
896SET AccNoEncrypt = EncryptByKey AccNo
897FROM cust;
898
899Command(s) completed successfully.
900
901
902Step 7 - Remove old column
903alter table cust
904drop column AccNo;
905
906
907insert into cust values (1006,'Tejas','Saving', 'S1007');
908Step 8 - Encrypting one cell
909open symmetric key symmetricKey1
910decryption by certificate certificate1;
911go
912update [db].[dbo].[cust]
913set AccNoEncrypt=ENCRYPTBYKEY(KEY_GUID('symmetricKey1'),'S1007')
914where CustId=1006;
915
916(1 row(s) affected)
917
918SELECT * FROM [db].[dbo].[cust]
919
920
921Method 5 : Row level Encryption
922Step 1 - Create a sample SQL Server table
923create table demo
924(
925 Name nvarchar(MAX),
926 AccNo nvarchar(MAX)
927);
928
929Command(s) completed successfully.
930
931insert into demo values('Hina','S1001');
932
933insert into demo values('Siddhi','C2001');
934
935insert into demo values('Shreyash','C2004');
936
937insert into demo values('Shameena','C2007');
938
939insert into demo values('Sanesh','S1004');
940
941Step 2 - SQL Server Service Master Key
942
943USE master;
944GO
945SELECT *
946FROM sys.symmetric_keys
947WHERE name = '##MS_ServiceMasterKey##';
948GO
949
950
951
952Step 3 - SQL Server Database Master Key
953use hina;
954go
955create master key encryption by password='password123';
956go
957
958Command(s) completed successfully.
959
960Step 4 - Create a Self Signed SQL Server Certificate:
961
962use hina;
963go
964create certificate certificate1
965with subject='Protect Data';
966go
967
968Command(s) completed successfully.
969
970
971Step 5 - SQL Server Symmetric Key
972use hina;
973go
974create symmetric key symmetricKey1
975with algorithm=AES_128
976encryption by certificate certificate1;
977go
978
979Command(s) completed successfully.
980
981Step 6 - Inserting Encrypted Values
982open symmetric key symmetricKey1
983decryption by certificate certificate1;
984
985insert into demo values (ENCRYPTBYKEY(KEY_GUID('symmetricKey1'),'Tejas'),ENCRYPTBYKEY(KEY_GUID('symmetricKey1'),'S1007'));
986
987
988
989(1 row(s) affected)
990
991select * from demo
992
993 
994PRACTICAL NO. 3
995Aim : Create a honeypot and demonstrate the following –
9961) Description:- Ping Flood Attack
997How to Perform:-
998 Step 1:-
999 Detect Target Address through Zenmap using starting adress of IP adress and mask adress
1000 e.g. Here I am targeting IP address 192.168.2.190
1001
1002
1003Step 2:-
1004 Ping to target machine as follow:-
1005
1006
1007
1008Detection:-
1009Step 1:-
1010Search for ICMP
1011
1012
1013Step 2:-
1014Go to Statistic==>Endpoints
1015 Find length of Packet
1016
1017
1018Step 3:-
1019Get location from LAN IP address as follows:-
1020
1021
1022
1023
10242)Brute force attack
1025Software used: Cain and able
1026Step: 1) Open cain and able. Click on Start/Stop sniffer. Click on + sign and add the ip of the network.
1027
1028
1029
1030Step: 2) Click on the APR tab at the bottom of the screen. Click on + sign. Select the ip and add. Click the APR symbol on the top left of the screen and Poisoning will start.
1031
1032
1033Step: 3) Open a browser and visit any Not Secured website. Enter the user id and password and submit.
1034
1035
1036Step: 4) Click on the passwords tab at the bottom of the screen. Click on HttP and the passwords will be displayed.
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
10503) ARP Flooding:
1051Software Used: Colasoft
1052Open Colasoft.
1053Check the network adapter for connection.
1054
1055
1056Click on add in the top left corner of the screen.
1057Select the ARP Packet. Let time be default.
1058
1059
1060Click Ok and proceed.
1061Now enter the mac and ip address of the source and destination where necessary.
1062
1063
1064Now select the packet in the Packet list. Right Click on the packet and click on send selected packets.
1065
1066
1067Select the adapter and insert the values. Then Click start.
1068
1069
1070ARP packets will start broadcasting.
1071
1072Now Trace the network with wireshark on the destination machine.
1073
1074The network is flooded with ARP packets originating from the source machine.
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086PRACTICAL NO. 4
1087Aim : Configure and implement SSL/TSL for communication.
1088EchoServer.java
1089import javax.net.ssl.*;
1090import java.io.*;
1091public class EchoServer
1092{
1093 public static void main(String[] arstring)
1094 {
1095 try
1096 {
1097 SSLServerSocketFactory sslServerSocketFactory = (SSLServerSocketFactory) SSLServerSocketFactory.getDefault();
1098 SSLServerSocket sslServerSocket = (SSLServerSocket) sslServerSocketFactory.createServerSocket(9999);
1099 SSLSocket sslSocket = (SSLSocket) sslServerSocket.accept();
1100 InputStream is = sslSocket.getInputStream();
1101 InputStreamReader isReader = new InputStreamReader(is);
1102 BufferedReader br = new BufferedReader(isReader);
1103 String string = null;
1104 while ((string = br.readLine()) != null)
1105 {
1106 System.out.println(string);
1107 System.out.flush();
1108 }
1109 }
1110 catch (Exception e)
1111 {
1112 e.printStackTrace();
1113 }
1114 }
1115}
1116
1117EchoClient.java
1118import javax.net.ssl.*;
1119import java.io.*;
1120public class EchoClient
1121{
1122 public static void main(String[] arstring)
1123 {
1124 try
1125 {
1126 SSLSocketFactory sslSocketFactory = (SSLSocketFactory) SSLSocketFactory.getDefault();
1127 SSLSocket sslSocket = (SSLSocket) sslSocketFactory.createSocket("localhost", 9999);
1128 InputStream is = System.in;
1129 InputStreamReader isReader = new InputStreamReader(is);
1130 BufferedReader br = new BufferedReader(isReader);
1131 OutputStream os = sslSocket.getOutputStream();
1132 OutputStreamWriter osWriter = new OutputStreamWriter(os);
1133 BufferedWriter bw = new BufferedWriter(osWriter);
1134 String string = null;
1135 while ((string = br.readLine()) != null)
1136 {
1137 bw.write(string + '\n');
1138 bw.flush();
1139 }
1140 }
1141 catch (Exception e)
1142 {
1143 e.printStackTrace();
1144 }
1145 }
1146}
1147Output :
1148
1149To generaste key :
1150Keytool -genkey -keyStore StoreName -keyalg RSA
1151To run :
1152Java -Djavax.net.ssl.keyStore=StoreName -Djavax.net.ssl.keyStorePassword=pass EchoServer
1153
1154
1155
1156 
1157PRACTICAL NO. 5
1158Aim : Write a program to send an encrypted Email which allows the user to choose the type of encryption. Implement any 3 techniques.
1159
1160Code: -
1161package encryptmail;
1162import com.sun.mail.util.BASE64EncoderStream;
1163import java.security.KeyPair;
1164import java.security.KeyPairGenerator;
1165import java.security.PrivateKey;
1166import java.security.PublicKey;
1167import java.util.Properties;
1168import java.util.Scanner;
1169import javax.crypto.*;
1170import javax.mail.*;
1171import javax.mail.internet.*;
1172import sun.misc.BASE64Encoder;
1173
1174public class EncryptMail
1175{
1176 public static void main(String[] args)
1177 {
1178 Scanner sc=new Scanner(System.in);
1179System.out.println("Choose the Algorithm for email encryption\n1.AES\n2.DES\n3.RSA");
1180intalgoN=sc.nextInt();
1181 String algo="null";
1182 if(algoN==1)
1183 {
1184algo="AES";
1185 }
1186 if(algoN==2)
1187 {
1188algo="DES";
1189 }
1190 if(algoN==3)
1191 {
1192algo="RSA";
1193 }
1194 String to="abc@gmail.com";
1195 Properties props = new Properties();
1196props.put("mail.smtp.starttls.enable","true");
1197props.put("mail.smtp.host", "smtp.gmail.com");
1198props.put("mail.smtp.ssl.trust","smtp.gmail.com");
1199props.put("mail.smtp.socketFactory.port", "465");
1200 props.put("mail.smtp.socketFactory.class","javax.net.ssl.SSLSocketFactory");
1201props.put("mail.smtp.auth", "true");
1202props.put("mail.smtp.port", "465");
1203
1204 Session session = Session.getDefaultInstance(props,newjavax.mail.Authenticator()
1205 {
1206 protected PasswordAuthenticationgetPasswordAuthentication()
1207 {
1208 return new PasswordAuthentication("abc@gmail.com","123");
1209 }
1210 });
1211 try
1212 {
1213
1214 String cipherText="",decryptedText;
1215 String msg="hello";
1216
1217 if(algo.equals("AES"))
1218 {
1219KeyGeneratorkeyGen=KeyGenerator.getInstance(algo);
1220keyGen.init(128);
1221SecretKeysecretKey=keyGen.generateKey();
1222 Cipher aesCipher=Cipher.getInstance(algo);
1223aesCipher.init(Cipher.ENCRYPT_MODE, secretKey);
1224byte[] byteDataToEncrypt=msg.getBytes();
1225byte[] byteCipherText=aesCipher.doFinal(byteDataToEncrypt);
1226cipherText=new BASE64Encoder().encode(byteCipherText);
1227 }
1228 if(algo.equals("RSA"))
1229 {
1230 final intkeySize = 2048;
1231KeyPairGeneratorkeyPairGenerator = KeyPairGenerator.getInstance("RSA");
1232keyPairGenerator.initialize(keySize);
1233KeyPairkeyPair = keyPairGenerator.genKeyPair();
1234PublicKeypubKey = keyPair.getPublic();
1235
1236PrivateKeyprivateKey = keyPair.getPrivate();
1237 Cipher cipher = Cipher.getInstance("RSA");
1238cipher.init(Cipher.ENCRYPT_MODE, privateKey);
1239 byte [] encrypted = cipher.doFinal(msg.getBytes());
1240cipherText=new String(encrypted);
1241 }
1242 if(algo.equals("DES"))
1243 {
1244SecretKey key;
1245 Cipher ecipher;
1246 key = KeyGenerator.getInstance("DES").generateKey();
1247ecipher = Cipher.getInstance("DES");
1248ecipher.init(Cipher.ENCRYPT_MODE, key);
1249byte[] utf8 = msg.getBytes("UTF8");
1250byte[] enc = ecipher.doFinal(utf8);
1251enc = BASE64EncoderStream.encode(enc);
1252cipherText= new String(enc);
1253 }
1254
1255MimeMessage message = new MimeMessage(session);
1256message.setFrom(new InternetAddress("abc
1257@gmail.com"));
1258message.addRecipient(Message.RecipientType.TO,newInternetAddress(to));
1259message.setSubject("Hello");
1260message.setText(cipherText);
1261Transport.send(message);
1262System.out.println("message sent successfully");
1263 }
1264 catch (Exception e)
1265 {
1266e.printStackTrace();
1267 }
1268 }
1269}
1270
1271
1272
1273
1274
1275
1276Output: -
1277Choose the Algorithm for email encryption
12781.AES
12792.DES
12803.RSA
12811
1282message sent successfully.
1283 
1284PRACTICAL NO. 6
1285Aim : Implement uploading secure file in cloud.
1286
1287Prerequisites
1288To run this quickstart, you'll need:
1289â— Python 2.6 or greater.
1290â— The pip package management tool.
1291â— A Google account with Google Drive enabled.
1292Step 1: Turn on the Drive API
1293a. Use this wizard to create or select a project in the Google Developers Console and automatically turn on the API. Click Continue, then Go to credentials.
1294b. On the Add credentials to your project page, click the Cancel button.
1295c. At the top of the page, select the OAuth consent screen tab. Select an Email address, enter a Product name if not already set, and click the Save button.
1296d. Select the Credentials tab, click the Create credentials button and select OAuth client ID.
1297e. Select the application type Other, enter the name "Drive API Quickstart", and click the Create button.
1298f. Click OK to dismiss the resulting dialog.
1299g. Click the file_download (Download JSON) button to the right of the client ID.
1300h. Move this file to your working directory and rename it client_secret.json.
1301
1302Step 2: Install the Google Client Library
1303Run the following command to install the library using pip:
1304pip install --upgrade google-api-python-client
1305See the library's installation page for the alternative installation options.
1306
1307Step 3: Copy paste the bellow code in another file and execute the file using :
1308python filename
1309
1310Code:
1311from __future__ import print_function
1312import httplib2
1313import os
1314import random
1315
1316from apiclient import discovery
1317from apiclient.http import MediaFileUpload
1318from oauth2client import client
1319from oauth2client import tools
1320from oauth2client.file import Storage
1321
1322from Crypto.Cipher import AES
1323from Crypto.Hash import SHA256
1324
1325try:
1326 import argparse
1327 flags = argparse.ArgumentParser(parents=[tools.argparser]).parse_args()
1328except ImportError:
1329 flags = None
1330
1331SCOPES = 'https://www.googleapis.com/auth/drive'
1332CLIENT_SECRET_FILE = 'client_secret.json'
1333APPLICATION_NAME = 'Drive API Python Quickstart'
1334
1335def get_credentials():
1336 # checks if credentials are present. if not creates new dir and stores credentials in it.
1337 # creates dir if dir is not present.
1338credential_dir = os.path.join(os.getcwd(), '.credentials')
1339 if not os.path.exists(credential_dir):
1340os.makedirs(credential_dir)
1341credential_path = os.path.join(credential_dir, 'drive-python-quickstart.json')
1342
1343 # gets credentials
1344 store = Storage(credential_path)
1345 credentials = store.get()
1346
1347 # if credentials not found, creates credentials by receiving authorization from user using authentication flow and stores in cwd
1348 if not credentials or credentials.invalid:
1349 flow = client.flow_from_clientsecrets(CLIENT_SECRET_FILE, SCOPES)
1350flow.user_agent = APPLICATION_NAME
1351 if flags:
1352 credentials = tools.run_flow(flow, store, flags)
1353 else: # Needed only for compatibility with Python 2.6
1354 credentials = tools.run(flow, store)
1355print('Storing credentials to ' + credential_path)
1356 return credentials
1357
1358def get_service():
1359 credentials = get_credentials()
1360 http = credentials.authorize(httplib2.Http())
1361drive_service = discovery.build('drive', 'v3', http=http)
1362 return drive_service
1363
1364def get_key(password):
1365 # creates SHA256 hash of the password
1366 password = password.encode('ascii')
1367 hasher = SHA256.new(password)
1368 return hasher.digest()
1369
1370def encrypt_file(file_name, key):
1371 # encrypts the file with the provided key using AES-128
1372chuncksize = 64 * 1024
1373outputfilename = file_name + ".enc"
1374filesize = str(os.path.getsize(file_name)).zfill(16)
1375 IV = ''
1376
1377 for i in range(16):
1378 IV += chr(random.randint(97, 123))
1379 IV = IV.encode('ascii')
1380encryptor = AES.new(key, AES.MODE_CBC, IV)
1381
1382 with open(file_name, 'rb') as infile:
1383 with open(outputfilename, 'wb') as outfile:
1384outfile.write(filesize.encode('ascii'))
1385outfile.write(IV)
1386
1387 while True:
1388chunck = infile.read(chuncksize)
1389
1390 if(len(chunck) == 0):
1391 break
1392 if(len(chunck) % 16 != 0):
1393chunck += (' ' * (16 - len(chunck) % 16)).encode('ascii')
1394outfile.write(encryptor.encrypt(chunck))
1395 return outputfilename
1396
1397
1398def upload_file(drive_service, filename, filepath):
1399file_metadata = {'name': filename}
1400 media = MediaFileUpload(filepath)
1401 file = drive_service.files().create(body=file_metadata,
1402media_body=media, fields='id').execute()
1403print('File ID: %s' % file.get('id'))
1404
1405
1406def main():
1407drive_service = get_service()
1408
1409file_name = input("Name of the file you want to encrypt and upload: ")
1410 password = input("Enter the password to encrypt the file: ")
1411print("Encrypting file...")
1412encrypted_file = encrypt_file(file_name, get_key(password))
1413print("Uploading encrypted file...")
1414upload_file(drive_service, encrypted_file, encrypted_file)
1415print("File uploaded succesfully.")
1416
1417
1418if __name__ == '__main__':
1419main()
1420
1421
1422
1423 
1424PRACTICAL NO. 7
1425Write a program to generate DSA SSH key.
1426Export.java
1427import java.io.*;
1428import java.security.*;
1429import java.security.spec.DSAPrivateKeySpec;
1430public class Export
1431{
1432 public static void main(String args[])
1433 {
1434 try
1435 {
1436 KeyPairGenerator kpg = KeyPairGenerator.getInstance("DSA");
1437 SecureRandom rnd = SecureRandom.getInstance("SHA1PRNG","SUN");
1438 kpg.initialize(1024,rnd);
1439 KeyPair kp = kpg.generateKeyPair();
1440
1441 Class spec = Class.forName("java.security.spec.DSAPrivateKeySpec");
1442 KeyFactory kf = KeyFactory.getInstance("DSA");
1443 DSAPrivateKeySpec ks = (DSAPrivateKeySpec)kf.getKeySpec(kp.getPrivate(), spec);
1444
1445 FileOutputStream fos = new FileOutputStream("ExportedKey.txt");
1446 ObjectOutputStream oos = new ObjectOutputStream(fos);
1447
1448 oos.writeObject(ks.getX());
1449 oos.writeObject(ks.getP());
1450 oos.writeObject(ks.getQ());
1451 oos.writeObject(ks.getG());
1452
1453 System.out.println("Private Key Exported");
1454 }
1455 catch(Exception e)
1456 {
1457 e.printStackTrace();
1458 }
1459 }
1460}
1461
1462Output :
1463E:\MScCS\CIS Programs>javac Export.java
1464Note: Export.java uses unchecked or unsafe operations.
1465Note: Recompile with -Xlint:unchecked for details.
1466
1467E:\MScCS\CIS Programs>java Export
1468Private Key Exported
1469
1470Import.java
1471import java.io.*;
1472import java.math.BigInteger;
1473import java.security.*;
1474import java.security.spec.DSAPrivateKeySpec;
1475public class Import
1476{
1477 public static void main(String args[])
1478 {
1479 try
1480 {
1481 FileInputStream fis = new FileInputStream("exportedKey.txt");
1482 ObjectInputStream ois = new ObjectInputStream(fis);
1483
1484 DSAPrivateKeySpec ks = new DSAPrivateKeySpec((BigInteger)ois.readObject(),(BigInteger)ois.readObject(),(BigInteger)ois.readObject(),(BigInteger)ois.readObject());
1485
1486 KeyFactory kf = KeyFactory.getInstance("DSA");
1487 PrivateKey pk = kf.generatePrivate(ks);
1488
1489 System.out.println("Got private key.");
1490 }
1491 catch(FileNotFoundException e)
1492 {
1493 System.out.println("Key not found.");
1494 }
1495 catch(Exception e)
1496 {
1497 System.out.println("Key is corrupted.");
1498 }
1499 }
1500}
1501
1502Output :
1503E:\MScCS\CIS Programs>javac Import.java
1504
1505E:\MScCS\CIS Programs>java Import
1506Got private key.
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521PRACTICAL NO. 8
1522Aim : Demonstrate and implement Bluetooth security.
1523Requirements
15241. pc with bluetooth of course
15252. python lightblue package
15263. python version 2.7
15274. target device needs to be paired with the host device
1528
1529
1530Command to install lightblue package
1531sudo apt-get install python-lightblue --(internet required)
1532
1533Code :
1534import bluetooth
1535import lightblue
1536import os
1537import random
1538
1539from Crypto.Cipher import AES
1540from Crypto.Hash import SHA256
1541
1542def get_nearby_devices():
1543 #searches for nearby devices
1544 print "searching for nearby devices..."
1545 nearby_devices = bluetooth.discover_devices()
1546 return nearby_devices
1547
1548def is_target_on(nearby_devices,target_name):
1549 #checks if target is on
1550 for bdaddr in nearby_devices:
1551 print bluetooth.lookup_name(bdaddr)
1552 if target_name == bluetooth.lookup_name(bdaddr):
1553 print "found the target device!"
1554 target_address = bdaddr
1555 print"Target Address: " + target_address
1556 return target_address
1557 return None
1558
1559
1560def get_services(target_address):
1561 #gets the list of all services the target provides over bluetooth
1562 print "searching for the object push service..."
1563 services = lightblue.findservices(target_address)
1564 print services
1565
1566def get_key(password):
1567 # creates SHA256 hash of the password
1568 hasher = SHA256.new(password)
1569 return hasher.digest()
1570
1571def encrypt_file(file_name, key):
1572 # encrypts the file with the provided key using AES-128
1573 chuncksize = 64*1024
1574 outputfile = file_name +".enc"
1575 filesize = str(os.path.getsize(file_name)).zfill(16)
1576 IV = ''
1577
1578 for i in range(16):
1579 IV += chr(random.randint(0,0xFF))
1580 encryptor = AES.new(key, AES.MODE_CBC, IV)
1581
1582 with open(file_name, 'rb') as infile:
1583 with open(outputfile, 'wb') as outfile:
1584 outfile.write(filesize)
1585 outfile.write(IV)
1586
1587 while True:
1588 chunck = infile.read(chuncksize)
1589
1590 if(len(chunck) == 0):
1591 break
1592 if(len(chunck) % 16 != 0):
1593 chunck += ' ' * (16 - len(chunck)%16)
1594 outfile.write(encryptor.encrypt(chunck))
1595 outfile.close()
1596 return outputfile
1597
1598
1599
1600if __name__ == '__main__':
1601 # we should know
1602 file_name = raw_input("Enter the name of the file you want to send: ")
1603 password = raw_input("Enter password to encrypt file: ")
1604 file_to_send = encrypt_file(file_name, get_key(password))
1605
1606 # we don't know yet
1607 obex_port = None
1608 target_address = None
1609
1610 nearby_devices = get_nearby_devices()
1611
1612 devices = []
1613 for mac in nearby_devices:
1614 print bluetooth.lookup_name(mac)
1615 devices.append(mac)
1616
1617 target_index = int(raw_input("Enter the index of device: "))
1618 target_address = devices[target_index]
1619 # target_address = is_target_on(nearby_devices,target_name)
1620 get_services(target_address)
1621 obex_port = int(raw_input("Enter the obex port: "))
1622 print "sending a file..."
1623 lightblue.obex.sendfile(target_address, obex_port, file_to_send)
1624 print "File sent."
1625
1626
1627
1628 
1629PRACTICAL NO. 9
1630Aim : Demonstrate and implement Zigbee.
1631Requirements :
1632â— Two Zigbee
1633â— Two Ardino
1634â— XCTU software
1635â— Ardino Console
1636â— Connecting wires
1637â— Breadboard
1638
1639Steps :
1640• Open Xctu application
1641• Click on the discover devices.
1642
1643• Next the discoverable window will be start.
1644• Select the device connected to the the xctu.
1645• Then Click next.
1646
1647
1648• Do not change the parameters in the next window.
1649• Click on finish button.
1650
1651
1652Let the application load the module
1653After it detects the module press add selected device.
1654
1655
1656
1657
1658• After clicking on the add selected this the main window will start with the device name and the amc address.
1659• Double click on it to open the properties of it in the right pane of the application.
1660
1661
1662• Change the pan id according to your need.
1663• Write the changes by clicking the write button.
1664
1665
1666• After write the changes click on the console button on the application bar to open the console window.
1667• Press the open button to create a connection with the other zigbee.
1668
1669
1670• Type any message in the console and it display it on the console of other zigbee.
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680Sending Code
1681void setup() {
1682 // put your setup code here, to run once:
1683Serial.begin(9600);
1684}
1685
1686void loop() {
1687 // put your main code here, to run repeatedly:
1688 Serial.print("Hello World");
1689 delay(5000);
1690}
1691
1692
1693Receive Code
1694void setup() {
1695 // put your setup code here, to run once:
1696Serial.begin(9600);
1697}
1698
1699void loop() {
1700 // put your main code here, to run repeatedly:
1701 if(Serial.available()>0)
1702 {
1703 Serial.write(Serial.read());
1704 }
1705}
1706
1707
1708//Bluetooth
1709from __future__ import print_function
1710import httplib2
1711import os
1712import random
1713
1714from apiclient import discovery
1715from apiclient.http import MediaFileUpload
1716from oauth2client import client
1717from oauth2client import tools
1718from oauth2client.file import Storage
1719
1720from Crypto.Cipher import AES
1721from Crypto.Hash import SHA256
1722
1723try:
1724 import argparse
1725 flags = argparse.ArgumentParser(parents=[tools.argparser]).parse_args()
1726except ImportError:
1727 flags = None
1728
1729SCOPES = 'https://www.googleapis.com/auth/drive'
1730CLIENT_SECRET_FILE = 'client_secret.json'
1731APPLICATION_NAME = 'Drive API Python Quickstart'
1732
1733
1734def get_credentials():
1735 # checks if credentials are present. if not creates new dir and stores credentials in it.
1736 # creates dir if dir is not present.
1737 credential_dir = os.path.join(os.getcwd(), '.credentials')
1738 if not os.path.exists(credential_dir):
1739 os.makedirs(credential_dir)
1740 credential_path = os.path.join(credential_dir, 'drive-python-quickstart.json')
1741
1742 # gets credentials
1743 store = Storage(credential_path)
1744 credentials = store.get()
1745
1746 # if credentials not found, creates credentials by receiving authorization from user using authentication flow and stores in cwd
1747 if not credentials or credentials.invalid:
1748 flow = client.flow_from_clientsecrets(CLIENT_SECRET_FILE, SCOPES)
1749 flow.user_agent = APPLICATION_NAME
1750 if flags:
1751 credentials = tools.run_flow(flow, store, flags)
1752 else: # Needed only for compatibility with Python 2.6
1753 credentials = tools.run(flow, store)
1754 print('Storing credentials to ' + credential_path)
1755 return credentials
1756
1757
1758def get_service():
1759 credentials = get_credentials()
1760 http = credentials.authorize(httplib2.Http())
1761 drive_service = discovery.build('drive', 'v3', http=http)
1762 return drive_service
1763
1764
1765def get_key(password):
1766 # creates SHA256 hash of the password
1767 password = password.encode('ascii')
1768 hasher = SHA256.new(password)
1769 return hasher.digest()
1770
1771
1772def encrypt_file(file_name, key):
1773 # encrypts the file with the provided key using AES-128
1774 chuncksize = 64 * 1024
1775 outputfilename = file_name + ".enc"
1776 filesize = str(os.path.getsize(file_name)).zfill(16)
1777 IV = ''
1778
1779 for i in range(16):
1780 IV += chr(random.randint(97, 123))
1781 IV = IV.encode('ascii')
1782 encryptor = AES.new(key, AES.MODE_CBC, IV)
1783
1784 with open(file_name, 'rb') as infile:
1785 with open(outputfilename, 'wb') as outfile:
1786 outfile.write(filesize.encode('ascii'))
1787 outfile.write(IV)
1788
1789 while True:
1790 chunck = infile.read(chuncksize)
1791
1792 if(len(chunck) == 0):
1793 break
1794 if(len(chunck) % 16 != 0):
1795 chunck += (' ' * (16 - len(chunck) % 16)).encode('ascii')
1796 outfile.write(encryptor.encrypt(chunck))
1797 return outputfilename
1798
1799
1800def upload_file(drive_service, filename, filepath):
1801 file_metadata = {'name': filename}
1802 media = MediaFileUpload(filepath)
1803 file = drive_service.files().create(body=file_metadata,
1804 media_body=media, fields='id').execute()
1805 print('File ID: %s' % file.get('id'))
1806
1807
1808def main():
1809 drive_service = get_service()
1810
1811 file_name = input("Name of the file you want to encrypt and upload: ")
1812 password = input("Enter the password to encrypt the file: ")
1813 print("Encrypting file...")
1814 encrypted_file = encrypt_file(file_name, get_key(password))
1815 print("Uploading encrypted file...")
1816 upload_file(drive_service, encrypted_file, encrypted_file)
1817 print("File uploaded succesfully.")
1818
1819
1820if __name__ == '__main__':
1821 main()
1822
1823
1824BID ------------------
1825//upload.jsp
1826
1827
1828<%@page import="java.io.FileInputStream"%>
1829<%@page import="java.io.InputStream"%>
1830<%@page import="java.io.IOException"%>
1831<a href="upload.jsp"></a>
1832<%@page import="java.io.File"%>
1833 <%@page import="java.sql.*"%>
1834<%@page contentType="text/html" pageEncoding="UTF-8"%>
1835<!DOCTYPE html>
1836<html>
1837 <head>
1838 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
1839 <title>JSP Page</title>
1840 </head>
1841 <body>
1842 <%
1843
1844 String fname=request.getParameter("name");
1845 String lname=request.getParameter("phoneno");
1846 String email=request.getParameter("emailid");
1847
1848 String about=request.getParameter("aboutme");
1849
1850 File theFile = new File(request.getParameter("image"));
1851 //FileInputStream input = new FileInputStream(theFile);
1852 out.println("FullPathname" + " " + theFile.getAbsolutePath());
1853 // out.println(fname);
1854
1855
1856
1857
1858 try {
1859
1860 Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
1861
1862 //net.unanaaccess.
1863
1864 //String f1="C:/fakepath/img-807080652-0001.jpg";
1865 String msAccDB = "E:/BI/Student.accdb";
1866 String dbURL = "jdbc:ucanaccess://" + msAccDB;
1867
1868 // Step 2.A: Create and get connection using DriverManager class
1869 Connection c = DriverManager.getConnection(dbURL);
1870
1871 out.println("connected");
1872 // Step 2.B: Creating JDBC Statement
1873 Statement s = c.createStatement();
1874 String f="insert into student values('"+fname+"','"+lname+"','"+email+"','"+about+"','"+theFile.getAbsolutePath()+"')";
1875 s.executeUpdate(f);
1876 out.println("inserted");
1877
1878 }
1879
1880 catch(Exception e)
1881 {
1882
1883 }
1884
1885 %>
1886 <a href="display.jsp">show records</a>
1887 </body>
1888</html>
1889
1890
1891
1892
1893
1894 Business Intelligence and Big Data Analytics
1895
1896
1897Database Creation
1898
1899Table: Employee
1900
1901
1902Column Name Data type
1903Employee_ID (PK) AutoNumber
1904Employee_Name Text
1905Manager_ID Number
1906
1907Table: Product
1908
1909Column Name Data type
1910Product_ID (PK) Number
1911Brand_Name Text
1912Product_Name Text
1913Price Currency
1914Subcategory_ID Number
1915
1916
1917
1918Table: Product Category
1919
1920Column Name Data type
1921Category_ID (PK) AutoNumber
1922Category Text
1923
1924
1925Table: Product Subcategory
1926
1927Column Name Data type
1928SubCategory_ID (PK) AutoNumber
1929Subcategory Text
1930Category_Manager Text
1931Category_ID Number
1932
1933
1934Table: SalesFact
1935
1936Column Name Data type
1937Month (PK) Date/Time
1938State_ID (PK) Number
1939Product_ID (PK) Number
1940Employee_ID (PK) Number
1941Sales_Dollars Currency
1942Sales_Units Number
1943
1944Table: State
1945
1946Column Name Data type
1947State_ID (PK) Number
1948State_Name Text
1949Region Text
1950Country Text
1951
1952
1953Note: Insert at least 10 records in each table.
1954
1955
1956Practical No: 01
1957
1958Create a database application that takes
1959• Structure data
1960• Unstructured data
1961• Semi-Structured data
1962
1963Index.html
1964<!DOCTYPE html>
1965<!--
1966To change this license header, choose License Headers in Project Properties.
1967To change this template file, choose Tools | Templates
1968and open the template in the editor.
1969-->
1970<html>
1971 <head>
1972 <title>TODO supply a title</title>
1973 <meta charset="UTF-8">
1974 <meta name="viewport" content="width=device-width, initial-scale=1.0">
1975 <script type="text/javascript">
1976 function showFileName() {
1977 var fil = document.getElementById("myFile");
1978 alert(fil.value);
1979 }
1980 </script>
1981 </head>
1982 <body>
1983 <form name="form1" method="post" action="upload.jsp" enctype="multipart/form-data">
1984 Name <input type="text" name="name" value="" required/>
1985 <br><br><br>
1986 Phone Number <input type="text" name="phoneno" value="" required />
1987 <br><br><br>
1988 Email id <input type="email" name="emailid" value="" required="" />
1989 <br><br><br>
1990
1991 About you <textarea name="aboutme" rows="4" cols="20" required>
1992
1993 </textarea>
1994 <br><br><br>
1995
1996 Upload Image<input type="file" id="myFile" name="image" ACCEPT="image/gif,image/jpeg,image/jpg,image/tiff,image/tif" required/>
1997 <a href="#" onclick="showFileName()">Show Name</a> <br><br><br>
1998 <input type="submit" value="Submit" name="submit" />
1999</form>
2000 </body>
2001</html>
2002Upload.jsp
2003<%--
2004 Document : upload
2005 Created on : 4 Apr, 2018, 4:07:31 PM
2006 Author : shreyash
2007--%>
2008
2009<%@page import="java.io.FileInputStream"%>
2010<%@page import="java.io.InputStream"%>
2011<%@page import="org.apache.commons.codec.binary.Base64"%>
2012<%@page import="java.io.IOException"%>
2013<a href="upload.jsp"></a>
2014<%@page import="java.io.File"%>
2015 <%@page import="java.sql.*"%>
2016<%@page contentType="text/html" pageEncoding="UTF-8"%>
2017<!DOCTYPE html>
2018<html>
2019 <head>
2020 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
2021 <title>JSP Page</title>
2022 </head>
2023 <body>
2024 <%
2025
2026 String fname=request.getParameter("name");
2027 String lname=request.getParameter("phoneno");
2028 String email=request.getParameter("emailid");
2029
2030 String about=request.getParameter("aboutme");
2031
2032 File theFile = new File(request.getParameter("image"));
2033 FileInputStream input = new FileInputStream(theFile);
2034 try {
2035
2036 Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
2037 String msAccDB = "F:/mongodb/Student.accdb";
2038 String dbURL = "jdbc:ucanaccess://" + msAccDB;
2039
2040 // Step 2.A: Create and get connection using DriverManager class
2041 Connection c = DriverManager.getConnection(dbURL);
2042
2043 out.println("connected");
2044 // Step 2.B: Creating JDBC Statement
2045 Statement s = c.createStatement();
2046 String f="insert into student values('"+fname+"','"+lname+"','"+email+"','"+about+"','"+theFile.getAbsolutePath()+"')";
2047 s.executeUpdate(f);
2048 out.println("inserted");
2049
2050 }
2051
2052 catch(Exception e)
2053 {
2054
2055 }
2056
2057 %>
2058 <a href="display.jsp">show records</a>
2059 </body>
2060</html>
2061Display.jsp
2062<%--
2063 Document : display.jsp
2064 Created on : 22 Apr, 2018, 10:35:22 AM
2065 Author : shreyash
2066--%>
2067
2068<%@page import="java.sql.ResultSet"%>
2069<%@page import="java.sql.DriverManager"%>
2070<%@page import="java.sql.Connection"%>
2071<%@page import="java.sql.*"%>
2072<%@page contentType="text/html" pageEncoding="UTF-8"%>
2073<!DOCTYPE html>
2074<html>
2075 <head>
2076 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
2077 <title>JSP Page</title>
2078 </head>
2079 <body>
2080 <%
2081 try {
2082 Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
2083 String msAccDB = "F:/mongodb/Student.accdb";
2084 String dbURL = "jdbc:ucanaccess://" + msAccDB;
2085
2086 // Step 2.A: Create and get connection using DriverManager class
2087 Connection c = DriverManager.getConnection(dbURL);
2088
2089 out.println("connected");
2090 // Step 2.B: Creating JDBC Statement
2091 out.println("<table border=1>");
2092 out.println("<tr><th>ename</th><th>phone no</th><th>emailid</th><th>aboutme</th><th>path</th></tr>");
2093
2094 Statement s = c.createStatement();
2095
2096 String f="select * from student";
2097 ResultSet rs=s.executeQuery(f);
2098
2099 while(rs.next()) {
2100 out.println("<tr><td>"+rs.getString(1)+"</td><td>"+rs.getString(2)+"</td><td>"+rs.getString(3)+"</td><td>"+rs.getString(4)+"</td>"+"</td><td>"+rs.getString(5)+"</td></tr>");
2101
2102
2103 }
2104 out.println("</table>");
2105 }
2106
2107 catch(Exception e)
2108 {
2109
2110 }
2111
2112 %>
2113 </body>
2114</html>
2115
2116
2117
2118
2119
2120
2121
2122
2123Practical No: 02
2124
2125Title: Creating a Data Warehouse
2126
2127Problem Statement: Create and schedule a DTS Package using Data Transformation services (DTS) tool. Fire at least 5 queries on the database.
2128
2129Description:
2130
2131Creating a DTS Package with the DTS Import/Export Wizard
2132
2133The Data Transformation Services (DTS) Import/Export Wizard offers the simplest method of building a DTS package, interactively guiding you through the process of copying and transforming data. Following are the basic steps for creating a package with the DTS Import/Export Wizard:
2134
21351. Open SQL Server Enterprise Manager, expand the server, and click the Databases folder.
2136
21372. On the Tools menu, point to Data Transformation Services, and then click Import Data.
2138
21393. In the Choose a Data Source dialog box, select Microsoft Access as the Data Source, and then type the path and file name of your Access database (.mdb), or use the browser to browse for the file.
2140
2141
21424. In the Choose a Destination dialog box, select Microsoft OLE DB Provider for SQL Server, and then select the database server and the required authentication mode. In the Database dialog box, select the target database on the server.
21435. In the Specify Table Copy or Query dialog box, click Copy tables and views from the source database. If you copy the data or the results of a query, you can customize the data being copied to the destination. You can:
2144• Select which source or destination columns to copy.
2145• Select which source or destination columns to ignore.
2146• Change the data type where valid.
2147• Define how the data is to be converted between source and destination.
2148
2149
2150
21516. In the Select Source Tables and Views dialog box, select the tables and queries to import.
21527. In the Save, Schedule, and Replicate Package dialog box, select Run Immediately.
21538. Click Finish.
2154
2155Cube Creation
2156
2157Create a new OLAP database
2158
21591. Open Programs ïƒ Microsoft SQL Server ïƒ Analysis Services ïƒ Analysis Manager.
2160
21612. Right click the server in the console tree, and click New Database to display the Database dialog Box.
2162
21633. Type Sales as the database name, and type Market Database as the description.
2164
21654. Click OK to close the dialog box and to create the database.
2166
2167Specify a data source
2168
21691. Expand the Sales database folder. Right-click the Data Sources folder, and on the shortcut menu, click New Data Source.
2170
21712. Select Microsoft Jet 4.0 OLE DB Provider, and click Next.
2172
21733. On the connection Tab of the Data Link Properties dialog box, click the ellipsis button(…), navigate to the folder containing the Sales.mdb
2174
21754. Click Test Connection to make sure the data source definition is correct. Then click OK to close the dialog box. Expand the Data Sources folder to see the New Data Source.
2176
21775. Right-click the New Data Source, and click Copy on the shortcut menu. Right-click the Sales database, and click Paste on the shortcut menu. When asked for a new name, click Delete on the shortcut menu, and click Yes to confirm.
2178
2179
2180Designing a Cube by Using the Cube Wizard
2181
2182Select the Fact Tables and the Measures:
2183
21841. Right Click the Cube folder under the Sales database, point to New Cube, and then click Wizard to start the Cube Wizard. On the Welcome screen, click Next.
2185
2186
2187
21882. Select SalesFact from the list of tables.
2189
2190
2191
21923. Click Browse Data to review the records in the table. Close the Browse Data window.
2193
21944. After selecting the fact table, click Next to move to the next step of the wizard.
2195
21965. Add the Sales_Dollars and Sales_Units columns as measures by double-clicking each in turn. You can select a measure and click the right arrow button(>).
2197
2198
2199
22006. After adding both measures, click Next.
2201
2202Create a dimension from a Star Schema table:
2203
22041. Click a New Dimension in the Wizard to start the Dimension Wizard. Select the option to skip the welcome screen, and then click on Next.
2205
2206
2207
22082. Click the Option Star Schema: A Single Dimension Table, and the click Next.
2209
2210
2211
22123. In the Select “The Dimension Table Screenâ€, select State from the Available Tables list.
2213
2214
2215
22164. After selecting the dimension table, click Next.
2217
22185. Double-click Country, Region, and State_Name in the order. You select the Levels from the most summarized to the most detailed.
2219
2220
2221
2222
22236. Click Next three times. Type State as the name of the dimension. Before closing the wizard you can preview the dimension hierarchy.
2224
2225
2226Create a dimension from Snowflake schema tables:
2227
22281. Click Next Dimension in the Cube wizard to again launch the Dimension Wizard.
2229
22302. Click the option “Snowflake Schema: Multiple, Related Dimension Tablesâ€, and Click Next.
2231
22323. Double-click the dimension tables in the following order: Product, Product Subcategory, and Product Category.
2233
2234
2235
22364. Click Next.
2237
22385. Review the joins between tables. If necessary add join between the two Subcategory_ID columns and the two Category_ID columns, and click Next.
2239
2240
2241
22426. Double-click Category, Product_Name, and Subcategory intentionally reversing the order of the last two levels. The Dimension Wizard displays a message suggesting that you can rearrange the levels.
2243
2244
2245
22467. Click Yes to put the Product_Name level below the Subcategory level.
2247
22488. Click Next three times.
2249
22509. Type Product as the name of the dimension, expand items in the Preview window as desired, and click Finish.
2251
2252
2253
2254Create a dimension from Parent-Child tables:
2255
22561. Click Next Dimension in the Cube wizard to again launch the Dimension Wizard.
2257
22582. Click Parent-Child: Two Related Columns In a Single Dimension Table, and click Next.
2259
22603. Select Employee as the dimension table, and click Next.
2261
2262
2263
2264
22654. In the Member Key drop-down list, select Employee_ID. This is the column that is joined to the fact table. In the Parent Key drop-down list, select Manager_ID. In the Member Name drop-down list, select Employee_Name.
2266
2267
2268
22695. Click Next twice: once after defining the parent-child hierarchy, and once to bypass the advanced options step.
2270
22716. Type Employee as the name of the dimension.
2272
22737. Click Finish.
2274
2275
2276Create a “Time†dimension from a date column:
2277
22781. Click Next Dimension in the Cube wizard to again launch the Dimension Wizard.
2279
22802. Click the Option Star Schema: A Single Dimension Table, and the click Next.
2281
22823. In the Select “The Dimension Table Screenâ€, select SaleFact from the Available Tables list. Because the SaleFact table contains a Date/Time column, the Wizard displays a screen offering to create a Time dimension based on that column.
2283
2284
2285
22864. Select the dimension type, and then click the Time Dimension.
2287
22885. The Date column drop-down list allows you to choose which Date column to use if there is more than one in the table. Select Month and click Next.
2289
2290
2291
22926. On the screen that asks you to create the Time dimension levels, you can select the type of Time dimension hierarchy you want. You can even specify the Day and Month for the Year to start – useful for dealing with the fiscal years. In the select time levels drop-down list, click “Year,Quarter,Month†and then click Next.
2293
2294
2295
22967. When you use the Wizard to generate the date related levels you do not select columns to use for the levels. Click Next to bypass the “Advanced Option Screenâ€.
2297
22988. In the final screen on the Dimension Wizard, type “Time†as the dimension name.
2299
2300Name, Save and Process a Cube:
2301
23021. In the Cube Wizard click Next, click Yes when asked if you want to count the fact table rows.
2303
2304
2305
23062. Type “SalesCube†as the name of the Cube and click Finish.
2307
2308
23093. Click Exit on the File menu to close the Cube Editor. Click No when asked if you want to set storage option.
2310
2311
2312
23134. Expand the Cubes folder of the Sales database. Right-click the newly created SalesCube and click Process.
2314
23155. Click OK to begin processing.
2316
2317
2318Working with Measures in the Cube
2319
2320(To create a derived measure)
2321
23221. Right-click on SalesCube and select Edit.
2323
23242. In the SalesCube cube, right-click the Measures folder and click New Measure. Select the Sales_Dollars column, and click OK. The measure is added with the name Sales_Dollars1.
2325
2326
2327
23283. Change the Source Column property to “SalesFactâ€.â€Sales_Dollarsâ€*0.15, and type Commission for the Name property.
2329
2330
2331
23324. On the Tools menu, click Process Cube, agree to save the cube, decline design aggregates, and click OK when asked to specify the processing method. Close the Process log window, and browse the data.
2333
23345. Scroll the grid as needed to see the new Commission measure.
2335
2336
2337
2338(To specify the aggregation function for a measure)
2339
23401. In the Cube Editor, Click the Insert Measure toolbar button, and click double-click the Sales_Units column. This creates a measure named Sales_Units1. Type Bad Price for the measure’s Name property, and change the value of the Source Column property to “SalesFactâ€.â€Sales_Dollars†/ â€SalesFactâ€.â€Sales_Unitsâ€. This is a derived measure. It uses the default aggregation function: Sum.
2341
23422. Process the Cube – that is, click the Process Cube button, save the cube, don’t design aggregates, and choose the full process method. Close the Process log window, and browse the data.
2343
23443. Change the name of the Bad Price to Min Price, and change the value of the Aggregate function to Min.
2345
23464. On the Tools menu, click Process Cube.
2347
2348
2349
2350(To create a calculated measure)
2351
23521. In the Cube tree, right-click the Calculated Members folder, and click New Calculated Member. In the Calculated Member dialog box, type Net Price as the new member name.
2353
23542. In the data tree, fully expand the Measures dimension and double-click the Sales Dollars measure to make the member name appear in the Value Expression box. Click the slash ( / ) on the right side of the dialog box, and then double-click Sales Units in the Data tree.
2355
2356
2357
23583. Click OK.
2359
2360
2361
2362Member Property Creation and new Dimension Creation
2363
2364To create Member Properties:
2365
23661. In the Analysis Manager tree pane, Right Click on the Shared Dimensions folder.
2367
23682. Select Editor.
2369
23703. Select the Product dimension.
2371
2372
2373
23744. Click OK.
2375
2376
2377
23785. Right Click on the field “Brand_Name†in the Product Dimension and select Insert As Level.
2379
23806. Right Click on the field “Product_Name†in the Product Dimension and select Insert As Level.
2381
23827. Right Click on Product_Name level in the left-pane and select New Member Property.
2383
2384
2385
23868. In the Insert Member Property dialog box select Price.
2387
23889. Click OK.
2389
239010. Click on Fileïƒ Save. Give new dimension name as Product_M.
2391
239211. Click on Fileïƒ Exit.
2393
239412. Right Click on SalesCube. Select Edit.
2395
239613. In the left-pane of the Cube Editor Right-Click on Dimensions folder. Select Existing Dimensions. Select Product_M and press the button (>). Click OK.
2397
2398
2399
240014. Click on Save button. Click on Process button. Click No. Select Full Process. Click on OK.
2401
240215. Click Fileïƒ Exit.
2403
2404Firing queries on the Cube by using MDX Sample Application
2405
24061. Launch the MDX Sample Application, which is on the Analysis Services menu along with Analysis Manager. Click OK to connect to the server. Click the New Query File toolbar button to create a new query file. A query file can contain multiple queries.
2407
24082. In the toolbar, select Sales as the database name. Then, in the Cube drop-down lost box, select SalesCube as the cube-name.
2409
2410
2411
2412Note: MDX Sample application is divided into three vertical panes: the top pane is the query pane, where one builds an MDX query statement. The middle pane is the metadata pane, where one inspects information in a selected cube. The bottom pane is the results pane, which shows the output of an MDX query.
2413
2414
2415
2416
2417
2418
2419
2420
2421
2422
2423
2424
2425
2426
2427
24283. Replace the contents of the query pane with SELECT FROM SalesCube.
2429
2430
2431
24324. Click the Run Query button on the toolbar.
2433
24345. Similarly, the following queries can be tried out:
2435
2436
2437
2438Query1:
2439
2440 SELECT [Product].[Category].Members ON COLUMNS FROM SalesCube
2441
2442
2443
2444
2445Query2:
2446
2447 SELECT {[Sales Dollars],[Sales Units]} ON COLUMNS FROM SalesCube
2448
2449
2450
2451
2452Query3:
2453
2454SELECT Country.Members ON COLUMNS, Category.Members ON ROWS from SalesCube
2455
2456
2457
2458
2459Query4:
2460
2461SELECT Crossjoin(Country.Members,Category.Members) ON COLUMNS From SalesCube
2462
2463
2464
2465
2466Query5:
2467
2468SELECT [Measures].Members ON COLUMNS, [Product].[Subcategory].Members ON ROWS From [SalesCube]