Główna zawartość

Programowanie

Kurs: Programowanie > Rozdział 3

Lekcja 4: Modyfikacja baz danych w SQL

Spraw, aby twój SQL stał się bezpieczniejszy

SQL może być piękny, ale może też być niebezpieczny. Jeśli za pomocą SQL pracujesz z bazą danych aplikacji używanej przez setki, tysiące, czy nawet miliony użytkowników, musisz uważać - bo możesz przez pomyłkę uszkodzić lub wymazać wszystkie te dane. Istnieją jednak różne sposoby na uczynienie SQL bezpieczniejszym.

Unikanie błędnych aktualizacji/usunięć

Zanim wydasz komendę UPDATE, wykonaj SELECT z tym samym WHERE by upewnić się, że aktualizujesz właściwą kolumnę i rząd.

Na przykład, zanim wykonasz:

UPDATE users SET deleted = true WHERE id = 1;

Możesz wykonać:

SELECT id, deleted FROM users WHERE id = 1;

Kiedy zdecydujesz się dokonać aktualizacji, możesz wykorzystać operator LIMIT by zapewnić, że przez pomyłkę nie zmienisz zbyt wielu rzędów:

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

Lub, w przypadku usuwania:

DELETE users WHERE id = 1 LIMIT 1;

Używanie transakcji

Kiedy wydajemy polecenie SQL, które w jakiś sposób zmienia naszą bazę danych, rozpoczyna ono tzw. "transakcję." Transakcja jest sekwencją operacji traktowanych jako jedno logiczne zadanie (jak transakcja bankowa). W świecie baz danych transakcja musi stosować się do zasad "ACID" by zapewnić jej prawidłowe przetwarzanie.

Kiedy wydajemy polecenie takie jak CREATE, UPDATE, INSERT lub DELETE, automatycznie rozpoczynamy transakcję. Jeśli chcemy jednak, możemy powiązać wiele poleceń w jedną większą transakcję. Może się tak zdarzyć, że chcemy by UPDATE odbył się tylko, jeśli inny UPDATE zakończy się z powodzeniem, więc chcemy umieścić je w tej samej transakcji.

W takim wypadku możemy otoczyć te komendy poleceniami BEGIN TRANSACTION i COMMIT:

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

Jeśli baza danych z jakiegoś powodu nie jest w stanie wykonać obu poleceń UPDATE, wtedy wycofa transakcję i powróci do stanu początkowego.

Transakcji używamy też, kiedy chcemy zapewnić, że wszystkie nasze komendy działają na tym samym widoku danych - kiedy chcemy mieć pewność, że żadne inne transakcje nie będą operować na tych samych danych podczas gdy wykonywana jest nasza sekwencja komend. Kiedy patrzysz na sekwencję komend, którą chcesz wykonać, zastanów się, co stałoby się, gdyby w jej trakcie inny użytkownik wywoływał polecenia. Czy może się zdarzyć, że Twoje dane znajdą się w niepożądanym stanie? W takim wypadku, powinieneś użyć transakcji.

Poniższe polecenia na przykład tworzą rząd oznaczający, że użytkownik zdobył odznakę, a następnie aktualizują ostatnią aktywność użytkownika tak, by to odzwierciedlała:

INSERT INTO user_badges VALUES (1, "Mistrz SQL", "4pm");
UPDATE user SET recent_activity = "Zdobył odznakę Mistrz SQL" WHERE id = 1;

W tym samym czasie, inny użytkownik lub proces może nagradzać tego użytkownika inną odznaką:

INSERT INTO user_badges VALUES (1, "Doskonały Słuchacz", "4:05pm");
UPDATE user SET recent_activity = "Zdobył odznakę Doskonały Słuchacz" WHERE id = 1;

Te polecenia mogą w rzeczywistości zostać wykonane w następującej kolejności:

INSERT INTO user_badges VALUES (1, "Mistrz SQL");
INSERT INTO user_badges VALUES (1, "Doskonały Słuchacz");
UPDATE user SET recent_activity = "Zdobył odznakę Doskonały Słuchacz" WHERE id = 1;
UPDATE user SET recent_activity = "Zdobył odznakę Mistrz SQL" WHERE id = 1;

Ostatnią aktywnością tego użytkownika byłoby teraz "Zdobył odznakę Mistrz SQL" mimo, że ostatnią zdobytą odznaką jest "Doskonały Słuchacz". To nie koniec świata, ale prawdopodobnie też nie to, czego oczekiwaliśmy.

Zamiast tego moglibyśmy wykonywać te polecenia w transakcji, by zapewnić, że żadne inne transakcje nie zostaną wykonane w międzyczasie:

BEGIN TRANSACTION;
INSERT INTO user_badges VALUES (1, "Mistrz SQL");
UPDATE user SET recent_activity = "Zdobył odznakę Mistrz SQL" WHERE id = 1;
COMMIT;

Robienie kopii zapasowych

Powinieneś pamiętać o każdej wskazówce, niestety nawet wtedy możesz napotkać błąd. Z tego powodu firmy tworzą kopie zapasowe baz danych - co godzinę, codziennie, co tydzień, w zależności od rozmiaru bazy i ilości wolnego miejsca. Gdy stanie się coś złego, mogą przywrócić dane z kopii zapasowej bazy danych dla każdej uszkodzonej lub usuniętej tabeli. Dane mogą okazać się nieaktualne, ale nieaktualne dane są lepsze niż brak danych.

Replikacja

Podobnym podejściem jest replikacja - przechowywanie kopii bazy danych w wielu miejscach. Jeśli z jakiegoś powodu określona kopia bazy jest niedostępna (na przykład piorun uderzył w budynek, takie zdarzenie faktycznie miało miejsce!), to zapytanie zostanie wysłane do innej kopii bazy danych, która powinna być dostępna. Jeśli dane są bardzo ważne, to powinny być replikowane, aby zapewnić ciągłą dostępność. Na przykład, jeśli lekarz chce dowiedzieć się na co uczulony jest pacjent, aby wiedzieć co zrobić w sytuacji kryzysowej, to nie może czekać na inżynierów, aby uzyskać dostęp do danych. Musi mieć do nich dostęp natychmiastowo.

Jednakże, replikacja baz danych wymaga o wiele więcej wysiłku i często oznacza mniejszą wydajność, ponieważ operacje zapisu muszą zostać wykonane na wszystkich kopiach, więc firma musi zdecydować, czy zalety replikacji są wymierne do kosztów i znaleźć najbardziej optymalny sposób na zbudowanie środowiska.

Przyznawanie uprawnień

Wiele baz danych zawiera użytkowników i uprawnienia przypisane do nich, ponieważ dane przechowywane na serwerze są dostępne dla wielu użytkowników. Na Khan Academy nie istnieje pojęcie użytkownik/uprawnienia w skryptach SQL, ponieważ SQLite jest przeważnie używany przez jednego użytkownika. Dzięki temu możesz zapisywać dane do bazy jeśli masz dostęp do dysku na którym jest ona przechowana.

Jeśli pewnego dnia będziesz korzystał z bazy danych na współdzielonym serwerze pamiętaj o ustawieniu użytkowników i uprawnień na początku. Powszechnie przyjmuje się, że niewielu użytkowników powinno mieć pełny dostęp do bazy danych (inżynierowie backendu), ponieważ jest to niebezpieczne.

Poniżej przykład jak przypisać pełny dostęp do bazy użytkownikowi:

GRANT FULL ON TABLE users TO super_admin;

A tutaj jak przypisać tylko zapytanie SELECT do innego użytkownika:

GRANT SELECT ON TABLE users TO analyzing_user;

W dużej firmie, często nie pozwala się nawet na wykonywanie zapytania SELECT, ponieważ tabela może zawierać informacje o użytkownikach, na przykład adres e-mail lub imię. Wiele firm ma anonimowe wersje swoich baz danych, na których użytkownicy mogą wykonywać zapytania bez obawy o prywatne informacje.

Bonus: Przeczytaj ten słynny komiks XKCD o bezpiecznym SQL (plus to wyjaśnienie).

Chcesz dołączyć do dyskusji?

Zaloguj się

Sortuj według

Na razie brak głosów w dyskusji

Rozumiesz angielski? Kliknij tutaj, aby zobaczyć więcej dyskusji na angielskiej wersji strony Khan Academy.