C, PHP, VB, .NET

Задачата беше дадена в два варианта, като те се различават съвсем малко в едно условие. По-долу е отбелязано като "вариант 1" или съответно "вариант 2". Ето задачите и решението им (което нарочно ще го разгледам много подробно, дори с риск да бъде прекалено):

По време на проектирането на софтуер за управление на многопотребителска система са ви възложили задача да проектирате частта от базата от данни на бъдещото приложение, която ще отговаря за входа в системата и сигурността свързана около него. Знае се, че за всеки потребител ще се пази уникално ID, уникално потребителско име (до 32 символа) и парола (до 32 символа). Именно с комбинацията от потребителско име и съответната му парола потребителите ще се автентикират в системата през въпросната форма за вход.

Допълнително за системата е нужно да се имплементира и система за сигурност срещу “brute force” атаки (компютърни програми, които се опитват да налучкат паролите на потребители от системата чрез многократно въвеждане на комбинации от различни букви и цифри за име и парола). Очакват се следните видове атаки:

1. Бот от един IP адрес прави многократни опити да влезе в един определен акаунт;
2. Бот от един IP адрес прави многократни опити да влезе в различни акаунти;
3. Ботове от различни IP адреси правят многократни опити да влязат в един акаунт;

Защитата, която ще се имплементира за всеки един от споменатите случаи съответно е:

1. При повече от 50 неуспешни опита се забранява достъпа на конкретния IP адрес до цялата система. В базата от данни ще се пази броя на неуспешните влизания;
2. При повече от 50 неуспешни опита към различни акаунти отново ще забраняваме достъпа на дадения IP адрес до цялата система по същия начин както в точка 1;
3. • Вариант 1: При повече от 1000 опита да се влезе в дадения акаунт, то всички IP адреси, които са правили неуспешен опит да влязат в дадения акаунт се блокират;
   • Вариант 2: При повече от 1000 опита да се влезе в дадения акаунт той се блокира перманентно.

Задача 1. Направете ER диаграма на базата данни като прецените внимателно какви данни ще пазите и как точно ще ги пазите.

Задача 2. Създайте базата от данни и попълнете таблиците с примерни данни.

Задача 3. Ако програмата ви подаде IP адрес, потребителско име и парола, то напишете SELECT заявка, която използва тези данни и проверява:
а) Дали IP адреса може да влезе в системата (блокиран ли е или не);
б) Дали потребителското име и парола са верни.

Програмата очаква да ѝ бъде върнат user id (ако името и паролата са верни и IP адреса/акаунта не е блокиран) или empty set (т.е. нищо ако името и/или паролата са грешни или ако IP адреса/акаунта е блокиран).

Решение: Първоначално е задължително да определим какви данни ще съхраняваме в базата от данни. Със сигурност като абсолютен минимум ни трябват:

1. Уникални ID-та на потребителите;
2. Потребителски имена на потребителите;
3. Пароли на потребителите;
4. IP адреси на компютри, които се опитват да влезнат в системата;
5. Брой неуспешни опити за влизане на даден IP адрес в даден акаунт.

При това ясно се вижда, че id, user и pass на потребителите са зависими данни (паролата на даден потребител отговаря на точно определено потребителско име, което от своя страна отговаря на точно определено id и обратно), докато IP адреса на хакера не е зависим от тях - казано е, че може "един IP адрес да прави многократни опити да влезе в различни акаунти" (с други думи това, че се опитваме да влизаме в системата от даден IP адрес не предопределя в кой акаунт ще се опитаме да влезем). По този начин ще изолираме два класа обекти - потребители (users) и IP адреси (ips). Колкото до "брой неуспешни опити за влизане на даден IP адрес в даден акаунт" (ще именоваме това като "attempts") - тези данни са едновременно зависими както от акаунта, така и от IP адреса. Тоест attempts трябва да участват във връзката между тях.

Сега да определим отношението между users и ips. Имаме три определящи условия, да ги припомним:

1. Бот от един IP адрес прави многократни опити да влезе в един определен акаунт;
2. Бот от един IP адрес прави многократни опити да влезе в различни акаунти;
3. Ботове от различни IP адреси правят многократни опити да влязат в един акаунт.

Първото условие определя връзка 1:1, но второто определя връзка 1:M (от users към ips), която е "по-силна" и така да се каже "поглъща" предишната в себе си.  От третото условие пък се вижда, че имаме и "обратна връзка" 1:M (от ips към users). Или финално определяме, че зависимостта между двата класа обекти е връзка от тип M:M. Работната ни ER диаграма е следната:

В нея единствено не сме отбелязали "attempts". По този начин (както е направена) ние можем да записваме всяко едно неуспешно влизане на даден IP адрес в междинната таблица "logs", т.е. ние имаме много повече излишна информация, отколкото трябва да пазим - практически междинната таблица може да се използва за изчисляване на attempts (като броим редовете с даден IP). Или по друг начин казано - в горния вариант таблицата logs няма да има primary key, а само и единствено два foreign keys, в които ще се позволяват повтарящи се редове.

Къде все пак е подходящото място за "attempts"? Вече казахме, че това число ще е зависимо и от двете таблици, следователно подходящото място за него е именно във връзката (таблица logs). Или ER диаграмата ни ще стане следната:

 

След като добавихме attempts в logs, то вече можем да забраним повтарящи се комбинации от uid и IPid в него. Или най-добре направо да направим комбинацията от (uid, IPid) като primary key.

Що се отнася до типовете данни, Users.id ще бъде числова стойност, при това уникална, което я прави идеална за употреба като първичен ключ за таблица users. Users.user трябва да е потребителското име, за което пише, че е "уникално" и "до 32 символа", следователно ще бъде колона от тип varchar(32) unique. За Users.pass пише само, че трябва да е "до 32 символа", следователно ще бъде колона от тип varchar(32).

Logs.attempts очевидно ще бъде целочислен тип данни, например int. Другите две колони в тази таблица (id на потребител и id на IP) ще са външни ключове, като комбинацията от двата ще е подходящ първичен ключ (така няма да позволим два пъти запис за едно и също ip към един и същи акаунт).

Защо сме добавили ново поле "id" в таблица "IPs"? В таблицата IPs колоната "address" можем да изберем като varchar(15) защото IP адресите се записват като - от "0.0.0.0" (7 символа) до "255.255.255.255" (15 символа). Знаем обаче, че използването на текстово поле като първичен ключ не е препоръчително от гледна точка на производителност (performance). Именно заради това добавихме нов атрибут "уникално id на IP адрес" (id), което да използвамe за първичен ключ. Това е валидно решение ако наистина ще пазим IP адреса като текстови низ! От там следва, че външния ключ в таблицата "Logs" ще бъде именно това id. Ние обаче ще потърсим по-добро решение.

Можем да се досетим, че IP адресите се състоят от четири поредни числа разделени с точка и ако приемем числата за винаги трицифрени (например 5 да се представя като 005), то бихме могли да измислим начин, по който IP адреса да се запише като число в системата. Ако се "договорим" с приложението да не работим с низове, а например с числа от типа aaabbbcccddd (например 111222015004 би било адреса 111.222.15.4), то ще можем да запазим IP адресите в числови полета. Друг вариант (още по-добър) е да се използват готовите функции в MySQL INET_ATON и INET_NTOA, които превръщат текстови низ (който трябва да е IP адрес, в противен случай резултата ще е NULL) в число от вида aaa×256³ + bbb×256² + ccc×256 + ddd. Това число се оказва, че е във възможни граници от 0 до 4294967295 или то идеално съвпадащо с тип INT UNSIGNED!

Независимо кой от двата варианта изберем от предишния параграф виждаме, че можем да пазим IP адреса като целочислен тип вместо като текстови низ. Освен всичко друго IP адресите на различните ботове/потребители са различни (unique). Значи в таблица IPs можем спокойно да премахнем "id" на IPто и самият IP адрес да стане първичен ключ, т.е. таблицата ще стане с една единствена колона. От тук пък автоматично следва, че няма никакъв смисъл да пазим IP адресите в отделна таблица, а те могат спокойно да преминат в таблицата Logs. Действително - в Logs в текущия работен вариант ние ще пазим външен ключ към IP адресите, който по същество ще бъдат самите те (всъщност ненужно ще дублираме информация и ще направим разхищение на данни). Така за настоящата база данни, ако приемем да ги пазим в числови полета, се оказва, че е излишно да пазим IP адресите в отделна таблица. Така финално нашата ER диаграма ще бъде:

В нея първичният ключ в таблицата Logs ще бъде комбинацията (uid, ip).

Сега се насочваме към решението на задача 2 - създаване на базата данни:

CREATE DATABASE usersystem;
USE usersystem;

CREATE TABLE users(
	id INT UNSIGNED PRIMARY KEY,
	user VARCHAR(32) NOT NULL UNIQUE,
	pass VARCHAR(32) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE logs(
	uid INT UNSIGNED,
	FOREIGN KEY (uid) REFERENCES users(id),
	ip INT UNSIGNED NOT NULL,
	attempts INT NOT NULL,
	PRIMARY KEY(uid,ip)
) ENGINE=InnoDB;

и запълваме с малко тестови данни, които се стараем да покрият всякакви възможности - потребители с еднакви пароли, IP адреси с повече от 50 опита и такива с по-малко, акаунти с и под 1000 неуспешни влизания:

INSERT INTO users(id, user, pass)
VALUES	(1, "ivan", "password"),
	(2, "petar", "123456"),
	(3, "maria", "password");

INSERT INTO logs(uid, ip, attempts)
VALUES	(1, INET_ATON("1.1.1.1"), 66),
	(2, INET_ATON("1.1.1.1"), 20),
	(1, INET_ATON("2.2.2.2"), 10),
	(3, INET_ATON("2.2.2.2"), 30),
	(2, INET_ATON("3.3.3.3"), 1200);

И преминаваме към задача 3. В нея е казано, че към "програмата очаква да ѝ бъде върнат user id (ако името и паролата са верни и IP адреса/акаунта не е блокиран) или empty set (т.е. нищо ако името и/или паролата са грешни или ако IP адреса/акаунта е блокиран)". Значи заявката ще бъде от вида:

SELECT id
FROM users
WHERE ...

В WHERE ще трябва да напишем ограничения, които ще премахнат от изхода id'та до достигане на желания резултат. Първото очевидно ограничение е, че името и паролата трябва да са подадени верни - ако не са, то няма да има валиден вход в системата, независимо дали IP адреса е блокиран или не. Включваме това в нашата заявка:

SELECT id
FROM users
WHERE user="username" AND pass="password"
AND ...

Тук текстовите низове "username" и "password" са име и парола подадени от приложението към нашата програма. Сега трябва да се погрижим и за "блокирането на ботовете". Нека припомним първите две условия за това:

1. При повече от 50 неуспешни опита се забранява достъпа на конкретния IP адрес до цялата система. В базата от данни ще се пази броя на неуспешните влизания;
2. При повече от 50 неуспешни опита към различни акаунти отново ще забраняваме достъпа на дадения IP адрес до цялата система по същия начин както в точка 1;

Очевидно е, че второто условие "поглъща" първото. Действително - ако един бот е направил повече от 50 опита за влизане в един от акаунтите, то той ще е направил общо над 50 опита към всички акаунти в системата, дори да не е правил опит към друг акаунт. Затова тук е достатъчно да направим ограничението само по точка 2, а то ще включи в себе си и ограничението от точка 1. Първо ни е нужен общия брой опити, които е направил подаден IP адрес към системата. Това ще се изчисли чрез:

SELECT SUM(logs.attempts) FROM logs
WHERE logs.ip=INET_ATON('1.1.1.1');

... където 1.1.1.1 ще е IP адреса подаден от приложението. В търсеното ограничаващо условие искаме да допускаме само IP адреси, които имат под 50 такива опита. Тоест към заявката ще добавим именно това:

SELECT id
FROM users
WHERE user="username" AND pass="password"
AND(
	( SELECT SUM(logs.attempts) FROM logs
	  WHERE logs.ip=INET_ATON('1.1.1.1')
	  HAVING SUM(logs.attempts) > 50
	) IS NULL
)
AND ...

В горната заявка групиране не беше нужно, защото ограничаваме изхода до едно единствено IP. Забележете, че с така дефинираната заявка ние разрешаваме достъп на IP адреси, които въобще не са влизали в системата - тяхната сума ще е NULL. Остана да добавим третото ограничаващо условие за ботовете. Да си го припомним:

• Вариант 1: При повече от 1000 опита да се влезе в дадения акаунт, то всички IP адреси, които са правили неуспешен опит да влязат в дадения акаунт се блокират;

Ще допускаме вход само до тези потребители, които имат под 1000 влизания в системата. Ще направим това по аналогия с предишното условие, но тук ще броим attempts спрямо user.id. Това user.id ще е едно единствено, защото ограничението user="username" е по уникална колона, следователно от него ще излезе едно единствено id. Важно е да се отбележи и условието, че трябва да бъде допуснат IP адрес, който не е отбелязан в таблицата Logs, независимо, че в дадения акаунт има записани над 1000 неуспешни опита (така е по условие във вариант 1). Това ще стане с второ условие разделено с логическо ИЛИ с първото. Така финално заявката за вариант 1 ще бъде:

SELECT id
FROM users
WHERE user="username" AND pass="password"
AND(
	( SELECT SUM(logs.attempts) FROM logs
	  WHERE logs.ip=INET_ATON('1.1.1.1')
	  HAVING SUM(logs.attempts) > 50
	) IS NULL
)
AND(
	( SELECT SUM(logs.attempts) FROM logs
	  WHERE logs.uid = users.id) < 1000
	    OR
	  INET_ATON('1.1.1.1')
	  NOT IN ( SELECT ip FROM logs
		   WHERE users.id = logs.uid )
);

Вече се досещаме, че случаят с вариант 2 ще е по-елементарен:

Вариант 2: При повече от 1000 опита да се влезе в дадения акаунт той се блокира перманентно.

Решението е аналогично на предишните разсъждения, но този път няма да допускаме IP адреси, които не са отчетени в системата (така при повече от 1000 неуспешни опита в акаунт ще се окаже, че всички IP адреси са блокирани). Тоест просто трябва да премахнем второто условие:

SELECT id
FROM users
WHERE user="username" AND pass="password"
AND(
	( SELECT SUM(logs.attempts) FROM logs
	  WHERE logs.ip=INET_ATON('1.1.1.1')
	  HAVING SUM(logs.attempts) > 50
	) IS NULL
)
AND(
	( SELECT SUM(logs.attempts) FROM logs
	  WHERE logs.uid = users.id ) < 1000
);

Допълнителна задача: проверете горните две заявки дали работят правилно с тестовите данни попълнени в системата.

(***) Използването на функцията inet_aton НЕ беше задължително на контролното. Всякакви други начини за записване на IP адреса в базата данни щяха да се приемат.