Направи своя SQL по-сигурен

SQL може да бъде нещо много красиво, но и опасно. Ако използваш SQL, за да достъпиш база данни, която се използва от стотици, хиляди или дори милиони потребители, трябва да внимаваш – защото може неволно да повредиш или да изтриеш всички данни. Има различни начини да направиш своя SQL по-безопасен.

Избягване на лоши актуализации/изтривания

Преди да изпълниш UPDATE, изпълни SELECT със същия WHERE, за да се убедиш, че актуализираш правилните ред и колона.

Например преди да изпълниш:

UPDATE users SET deleted = true WHERE id = 1;

Можеш да изпълниш:

SELECT id, deleted FROM users WHERE id = 1;

След като решиш да изпълниш актуализацията, можеш да използваш оператора LIMIT, за да е сигурно, че няма да актулизираш прекалено много редове:

UPDATE users SET deleted = true WHERE id = 1 LIMIT 1;

Или ако изтриваш:

DELETE users WHERE id = 1 LIMIT 1;

Използване на транзакции

Когато задаваме SQL команда, която променя базата данни по някакъв начин, започва т.нар. "транзакция." Транзакцията е поредица от операции, които се приемат за едно парче логика (като банкова транзакция), а в света на базите данни транзакцията трябва да спазва "ACID" принципите, за да е сигурна, че данните се обработват надеждно.

Винаги когато използваме команда като CREATE, UPDATE, INSERT или DELETE, автоматично стартираме транзакция. Обаче ако желаем, можем да обхванем множество команди в една голяма транзакция. Може би искаме един UPDATE да се изпълни само ако друг UPDATE се изпълни успешно, затова искаме и двата да са в една и съща транзакция.

В този случай можем да поставим командите между BEGIN TRANSACTION и COMMIT:

BEGIN TRANSACTION;
UPDATE people SET husband = "Winston" WHERE user_id = 1;
UPDATE people SET wife = "Winnefer" WHERE user_id = 2;
COMMIT;

Ако базата данни не може да изпълни и двете команди UPDATE по някаква причина, тя ще възстанови транзакцията и ще остави базата данни непроменена.

Използваме транзакциите и когато искаме да сме сигурни, че всичките ни команди ще оперират върху едни и същи данни – когато искаме да сме сигурни, че няма други транзакции, които да оперират върху тези данни, докато се изпълнява нашата заявка. Когато търсиш последователност от команди, които искаш да изпълниш, запитай се какво ще се случи, ако друг потребител изпрати командите в същото време. Дали твоите данни може да станат невалидни? В такъв случай, изпълни транзакция.

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

INSERT INTO user_badges VALUES (1, "SQL отличник", "4pm");
UPDATE user SET recent_activity = "Спечели значка за SQL отличник" WHERE id = 1;

В същото време друг потребител или процес може да дава на потребителя втора значка:

INSERT INTO user_badges VALUES (1, "Страхотен слушател", "4:05pm");
UPDATE user SET recent_activity = "Спечели значка за страхотен слушател" WHERE id = 1;

Тези команди могат всъщност да бъдат изпълнени в този ред:

INSERT INTO user_badges VALUES (1, "SQL отличник");
INSERT INTO user_badges VALUES (1, "Страхотен слушател");
UPDATE user SET recent_activity = "Спечели значка за страхотен слушател" WHERE id = 1;
UPDATE user SET recent_activity = "Спечели значка за SQL отличник" WHERE id = 1;

Последната активност ще бъде "Спечели значка за SQL отличник", въпреки че последата спечелена значка е "Страхотен слушател". Това не е краят на света, но вероятно не е това, което очаквахме.

Вместо това, можеш да ги изпълниш в транзакция, за да е сигурно, че по средата на процеса няма да се случат други транзакции:

BEGIN TRANSACTION;
INSERT INTO user_badges VALUES (1, "SQL отличник");
UPDATE user SET recent_activity = "Спечели значка за SQL отличник" WHERE id = 1;
COMMIT;

Правене на бекъп (архивиране)

Дори когато стриктно следваш тези съвети, се случва да се допуснат и грешки. Ето защо повечето компании правят бекъпи на базите данни – на всеки час, всеки ден или всяка седмица, в зависимост от размера на базата данни и наличното пространство. Когато се случи нещо лошо, могат да импортират данните от стара база данни за която и да е повредена или изгубена таблица. Данните може да са малко остарели, но дори и остарелите данни са по-добри от никакви данни.

Репликация

Подобрен подход е репликацията – винаги запазвай няколко копия на базите данни на различни места. Ако по някаква причина определено копие на базата данни е недостъпно (например светкавица удря сградата, в която се намира копието – точно такова нещо ми се случи!), заявката може да се изпрати към друго копие на базата данни, което се надяваме все още да е достъпно. Ако данните са много важни, ще е добре да се репликират, за да се осигури наличност. Например, ако един лекар се опита да състави списък с алергиите на един пациент, за да определи как да го лекува при спешен случай, не може да си позволи да чака инженерите да извадят данните от бекъп; тези данни му трябват веднага.

Репликирането на базите данни отнема много повече усилия и често означава по-бавно изпълнение, тъй като операциите трябва да се изпълнят върху всички данни, затова компаниите трябва да решават дали ползите от репликирането си струват цената и да потърсят най-добрия подход за своята среда.

Предоставяне на привилегии

Много системи за бази данни имат вградени в себе си потребители и привилегии, защото се съхраняват на сървър и се достъпват от много потребители. В SQL скриптовете на Кан Академия няма понятие за потебител/привилегия, защото SQLite обикновено прави сценарий с един потебител. Ето защо трябва да го напишеш, за да имаш достъп до устройството, на което е съхранено.

Но ако един ден използваш система база данни на споделен сървър, трябва да се увериш, че потребителите и привилегиите са зададени правилно от самото начало. Като общо правило трябва да има само няколко потебители с пълен достъп до базата данни (като бекенд инженерите), тъй като това може да е опасно.

Например ето как можем да дадем пълен достъп на конкретен потребител:

GRANT FULL ON TABLE users TO super_admin;

А така можем да дадем само SELECT достъп на друг потребител:

GRANT SELECT ON TABLE users TO analyzing_user;

В голяма компания често не искаш да даваш достъп на потребителите до SELECT, защото в таблицата може да има лична информация като имейл адрес на потребителя или неговото име. Много компании имат анонимни версии на своите бази данни, към които могат да се отправят заявки без притеснение за достъп до лична информация.

Бонус: Прочети този известен XKCD комикс за по-сигурен SQL (плюс това обяснение).

Искаш ли да се присъединиш към разговора?

Вписване в профила

Сортирай по:

Все още няма публикации.

Разбираш ли английски? Натисни тук, за да видиш още дискусии в английския сайт на Кан Академия.