· 6 years ago · Oct 15, 2019, 02:38 PM
1#GRANT ALL PRIVILEGES ON login.* TO 'onlineshop'@'localhost';
2DROP SCHEMA IF EXISTS login;
3CREATE SCHEMA IF NOT EXISTS `login` DEFAULT CHARACTER SET utf8;
4USE login;
5-- place your solutions here
6
7
8-- Erstellen der ersten Tabelle
9DROP TABLE IF EXISTS `user`;
10CREATE TABLE IF NOT EXISTS `user` (
11 `iduser` bigint unsigned NOT NULL AUTO_INCREMENT,
12 `email` varchar(100) NOT NULL,
13 `password` char(128) NOT NULL,
14 `active` char(128) NOT NULL,
15 PRIMARY KEY (`iduser`)
16) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
17
18
19-- Erste Insert Variante
20INSERT INTO `user` (`email`, `password`, `active`) VALUES
21('shopuser1@onlineshop.at', PASSWORD('password1'),PASSWORD(20)),
22('shopuser2@onlineshop.at', 'password2',15);
23
24-- Zweite Insert Variante
25INSERT into user set
26 email = 'shopuser3@onlineshop.at',
27 password = 'password3',
28 active = 35;
29
30-- Dritte Insert Variante
31insert into user
32(email, password, active)
33values
34('shopuser4@onlineshop.at', 'password4', 45);
35
36insert into user
37(email, password, active)
38values
39('shopuser5@onlineshop.at', 'password5', 56);
40
41-- Erstellen der zweiten Tabelle, diese Tabelle besitzt einen foreign-key der auf die id der ersten Tabelle user verweist
42DROP TABLE IF EXISTS `data`;
43CREATE TABLE IF NOT EXISTS `data` (
44 `iddata` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
45 `user_iduser` bigint unsigned DEFAULT NULL,
46 `email` text(100) NOT NULL,
47 `password` varchar(128) NOT NULL,
48 `date` date NOT NULL,
49 `number` decimal(60) NOT NULL,
50 PRIMARY KEY (`iddata`),
51 KEY `fk_user` (`user_iduser`)
52) ENGINE=InnoDB DEFAULT CHARSET=utf8;
53
54-- Alter table statment ist notwendig damit foreign-key funktioniert
55ALTER TABLE `data`
56 ADD CONSTRAINT `fk_user` FOREIGN KEY (`user_iduser`) REFERENCES `user` (`iduser`);
57
58-- Erste insert Variante
59INSERT INTO `data` (`email`, `password`,`date`,`number`) VALUES
60('shopuser1@onlineshop.at', 'password1','1989-05-06','13.45'),
61('shopuser4@onlineshop.at', 'password4','1769-09-08','45.45'),
62('shopuser2@onlineshop.at', 'password2','1999-12-09','45.23');
63
64-- Zweite insert Variante
65INSERT into data set
66 email = 'shopuser5@onlineshop.at',
67 password = 'password5',
68 date = '1987-08-08',
69 number = '13.45';
70
71-- Dritte insert Variante
72insert into data
73(email, password, date, number)
74values
75('shopuser3@onlineshop.at', 'password3', '2000-09-09', '23.23');
76
77-- löschen der Daten, Reihenfolge ist wichtig, da data mit einen foreign key auf user verweist, deswegen diese zuerst löschen
78delete from data;
79delete from user;
80
81-- löschen der Tabelle, rollback möglich
82drop table data;
83-- löschen der Tabelle, kein rollback möglich da keine logfiles geschrieben werden
84truncate table user;
85
86-- erstellen der Tabelle mit dynamic columns
87DROP TABLE IF EXISTS `dyna`;
88CREATE TABLE IF NOT EXISTS `dyna`(
89 `prim` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
90 dynamic_cols blob,
91 PRIMARY KEY (`prim`)
92);
93-- einfügen in dynamic columns
94INSERT INTO dyna VALUES
95(0, COLUMN_CREATE('firstname','lastname','email', 'gender'));
96
97INSERT INTO dyna VALUES
98(0, COLUMN_CREATE('firstname','lastname','email','nationality'));
99
100-- end solution
101
102DROP SCHEMA login;
103
104#REVOKE ALL PRIVILEGES ON login.* FROM 'onlineshop'@'localhost';
105
106/*
107 Frage 5: Verhindern von Cross-Site-Scripting. Hacker greift auf die eine Datenbank zu und erhält zum Beispiel Username + Email, aber keine Passwörter,
108 weil die auf noch einer anderen Datenbank liegen.
109
110 Frage 7: mittel Passwort oder Encode. Encode ist nicht zuverlässig für Passwörter, es sollte ein Verschlüsselungsalgorithmus wie SHA2() verwendet werden.
111 */