Zagrożenie ze strony ataków SQL Injection spędza chyba sen z powiek większości programistów mających styczność z zapytaniami do baz danych. Znam osobiście zarówno programistów, którzy do tematu podchodzą z lekką ręką jak i takich, którzy ujawniają w tym temacie swoje iście paranoiczne skłonności. Korzystając z okazji pozwolę sobie nadmienić, iż moim zdaniem paranoja jest cechą niezwykle pożądaną w pracy każdego administratora odpowiedzialnego za bezpieczeństwo danych 😉
Chyba najbardziej powszechnym rozwiązaniem jakie miałem okazję obserwować przy zabezpieczaniu się przed tego typu atakami jest przygotowywanie parametryzowanych zapytań oraz dokładne filtrowanie przekazywanych do zapytania wartości. Jakkolwiek nie mam nic do zarzucenia stosowaniu takiego podejścia to jednak moim zdaniem nie zawsze jest ono wystarczające (a przynajmniej tak podpowiada mi moja wewnętrzna skłonność do paranoi 😉 ). Przy takim podejściu idealnie jest jeśli zapytanie daje się ubrać w ścisły szablon, który musimy już tylko nakarmić parametrami o odpowiednich typach. Co jednak w przypadku gdy zapytanie musimy zbudować dynamicznie na podstawie przekazanych wartości? W takich sytuacjach moje paranoje przeważnie wpadają w lekką panikę, a przynajmniej tak było do czasu aż wpadłem na pewien pomysł, którym chciałbym się teraz podzielić.
Zapytanie możemy zbudować na wiele sposobów. Można je przygotować np. w skrypcie PHP ale równie dobrze nadaje się do tego język T‑SQL. Osobiście preferuję przygotowywanie zapytań po stronie bazy danych ponieważ mogę wtedy ukryć implementację np. wewnątrz procedury (powody takiego podejścia mogą się okazać dobrym tematem na inny wpis, więc nie będę się tutaj nad tym rozwodził). Niemniej w przedstawianych przeze mnie rozważaniach miejsce przygotowania zapytania nie ma większego znaczenia.
Dla wygody sprecyzujmy sobie pewien problem do rozwiązania. Dzięki temu będzie nam łatwiej prześledzić proponowane przeze mnie rozwiązanie. Załóżmy sobie, iż posiadamy w bazie danych tabelę z danymi klientów oraz tabelę z zamówieniami. Naszym zadaniem będzie pobrać rekordy z zamówieniami należącymi do wyszukiwanych osób.
Przygotujmy sobie środowisko do „zabawy”. Najpierw utworzymy tabele i zapełnimy je przykładowymi danymi.
create table dbo.klienci
(
klient_id int primary key identity(1,1),
nazwisko nvarchar(50) not null,
imie nvarchar(50) not null
);
create table dbo.zamowienia
(
zamowienie_id int primary key identity(1,1),
klient_id int not null,
data_zamowienia date not null
);
insert into dbo.klienci (nazwisko, imie) values
(N'Kalinowski', N'Antoni'),
(N'Nowicki', N'Franciszek'),
(N'Michalak', N'Marianna'),
(N'Nowacka', N'Marta');
insert into dbo.zamowienia (klient_id, data_zamowienia) values
(1, '20161201'),
(1, '20161210'),
(2, '20161207'),
(3, '20161211'),
(3, '20161217'),
(3, '20161228'),
(4, '20161204'),
(4, '20161214');
Teraz nadszedł czas na zastosowanie kilku „sztuczek”. Pierwszą z nich będzie wprowadzenie zmiennej tabelarycznej, którą zwykle ograniczam wyłącznie do jednego pola zgodnego z typem klucza poszukiwanych danych (najczęściej używam tutaj typu INT). Ponieważ poszukujemy zamówień gdzie kluczem jest wartość typu INT, więc zmienna tabelaryczna może mieć postać
declare @tabela table (id int);
Przygotujemy sobie również zmienną przechowującą wyszukiwaną frazę co będzie stanowić odpowiednik parametru przekazywanego przez użytkownika. Oczywiście wartość tej zmiennej może zostać ustalona w dowolny sposób, my dla wygody przypiszemy po prostu jakąś wartość, którą będziemy wyszukiwać wśród imion i nazwisk klientów.
declare @fraza nvarchar(50) = N'Mar';
Kolejnym elementem układanki jest przygotowywane zapytanie wyszukujące. Jedynym wymogiem jakie stawiam temu zapytaniu jest zwracanie rekordów zawierających wyłącznie jedno pole – identyfikator poszukiwanych rekordów. Nie będziemy się tutaj zanadto gimnastykować i przygotujemy najprostsze zapytanie będące efektem połączenia kilku łańcuchów znaków. Samo zapytanie, jako tekst, zapisane jest w zmiennej jak w następującym przykładzie
declare @query nvarchar(max) = N' select z.zamowienie_id from dbo.zamowienia as z inner join dbo.klienci as k on k.klient_id = z.klient_id where k.imie like ''%' + @fraza + '%'' or k.nazwisko like ''%' + @fraza + '%''';
Jeśli ktoś jeszcze nie domyślił się po co potrzebna nam jest zmienna tabelaryczna i zapytanie zwracające konkretny zbiór danych to wyjaśniam – kolejnym krokiem jest zapisanie wyniku zwracanego przez zapytanie wprost do zmiennej tabelarycznej jak w poniższym kodzie
insert into @tabela exec sp_executesql @query;
Powyższa konstrukcja ma pewną bardzo pożądaną cechę – jeśli zapytanie zwróci cokolwiek innego niż zbiór dający zapisać się w naszej zmiennej tabelarycznej wtedy próba wykonania kodu zakończy się błędem. Udało nam się więc osiągnąć już mały sukces ale nie jest on jeszcze kompletny. Ktoś sprytny mógłby wstrzyknąć nam niepożądany kod do naszego zapytania w taki sposób aby ten wykonał się przed zapytaniem zwracającym „poprawne” według naszego założenia dane. Co takiego złego mogłoby się wydarzyć? Może trochę przekoloryzuję, ale wyobraźcie sobie taką modyfikację zapytania podanego przeze mnie powyżej
declare @query nvarchar(max) = N' drop table dbo.zamowienia; select z.zamowienie_id from dbo.zamowienia as z inner join dbo.klienci as k on k.klient_id = z.klient_id where k.imie like ''%' + @fraza + '%'' or k.nazwisko like ''%' + @fraza + '%''';
Chyba nie muszę pisać, iż w takiej sytuacji nawet wykonanie drugiego zapytania zwracającego poszukiwane klucze nie ma już sensu – szkoda zostanie wyrządzona. Oczywiście zamiast polecenia usuwającego tabelę może tam się znaleźć cokolwiek innego, równie niepożądanego. Czy można się przed tym jakoś zabezpieczyć? Otóż okazuje się, że jest pewien sposób – transakcje. Zmodyfikujmy fragment naszego kodu pobierający dane do zmiennej tabelarycznej w następujący sposób
begin transaction insert into @tabela exec sp_executesql @query; rollback transaction
Na pierwszy rzut oka taka konstrukcja wydaje się nie mieć sensu – po co robić coś aby potem wycofać wszystkie zmiany? Otóż odpowiedź jest bardzo prosta – właśnie po to aby wycofać wszystkie zmiany – szczególnie te niepożądane. Najważniejsze jest jednak to, iż cokolwiek wewnątrz transakcji zostało zapisane w zmiennej tabelarycznej pozostanie tam nawet po wycofaniu transakcji. Jeśli ktoś poczuł się zaskoczony to już wyjaśniam (postaram się prostymi słowami) – dzieje się tak dlatego gdyż transakcja „pilnuje” wyłącznie danych zapisanych w bazie a zmienne zdefiniowane w zakresie batcha do nich nie należą. Mówiąc inaczej – przy takiej konstrukcji po wycofaniu transakcji możemy mieć do czynienia wyłącznie z dwiema sytuacjami (jeśli się mylę to proszę o sprostowanie w komentarzu) – albo pojawi się błąd albo w zmiennej tabelarycznej znajdą się jakieś dane (lub ich tam nie będzie – brak wyniku podczas wyszukiwania w końcu też jest jakimś wynikiem).
Ktoś mógłby zapytać jeszcze – a co się stanie kiedy ktoś spróbuje zatwierdzić transakcję po wykonaniu swojego złośliwego kodu? Jeśli ktoś spróbuje (ponieważ nie zna na pamięć dokumentacji 😉 ) jego oczom ukaże się błąd
Nie można użyć instrukcji COMMIT wewnątrz instrukcji INSERT-EXEC, chyba że najpierw zostanie użyta instrukcja BEGIN TRANSACTION.
Skoro tak to spróbujmy jeszcze zgodnie z sugestią komunikatu otworzyć transakcję jak w poniższym fragmencie kodu
declare @query nvarchar(max) = N' begin transaction; drop table dbo.zamowienia; commit transaction; select z.zamowienie_id from dbo.zamowienia as z inner join dbo.klienci as k on k.klient_id = z.klient_id where k.imie like ''%' + @fraza + '%'' or k.nazwisko like ''%' + @fraza + '%''';
W takiej sytuacji wykonanie zapytania zakończy się błędem ponieważ tabela zamówień nie będzie istnieć w drugiej części generowanego kodu, która się do niej odwołuje. Zresztą nawet gdyby zawrzeć tam inną tabelę, z której zapytanie nie korzysta, to chociaż polecenie wykona się bez zgłaszania błędu to rezultat pozostanie ten sam – cała transakcja, nazwijmy ją zewnętrzną, zostanie wycofana nie przynosząc szkody naszej bazie.
W rezultacie naszych powyższych działań, o ile nie napotkaliśmy błędu, możemy potraktować dane znajdujące się w zmiennej tabelarycznej jako identyfikatory poszukiwanych rekordów, a co za tym idzie możemy je wykorzystać do zwrócenia tych danych jak w kodzie poniżej
select z.* from @tabela as t inner join dbo.zamowienia as z on z.zamowienie_id = t.id;
Jeśli czyjaś paranoja nie została dostatecznie dokarmiona to podpowiadam, iż w powyższym zapytaniu można również uwzględnić np. uprawnienia tak aby osoba wykonująca zapytanie otrzymała wyłącznie dane zawarte w dostępnej dla nich zasobach. Dzięki temu jeśli utworzone dynamicznie zapytanie wprowadziło jakąś manipulację do wyniku to będzie on wciąż zawarty w zbiorze rekordów „dozwolonych” do wyświetlenia w tym miejscu.
Dla ułatwienia samodzielnej zabawy (do której gorąco zachęcam) zamieszczam poniżej cały kod na raz (poza przygotowaniem tabel i ich zawartości)
declare @tabela table (id int); declare @fraza nvarchar(50) = N'Mar'; declare @query nvarchar(max) = N' select z.zamowienie_id from dbo.zamowienia as z inner join dbo.klienci as k on k.klient_id = z.klient_id where k.imie like ''%' + @fraza + '%'' or k.nazwisko like ''%' + @fraza + '%'''; begin transaction insert into @tabela exec sp_executesql @query; rollback transaction select z.* from @tabela as t inner join dbo.zamowienia as z on z.zamowienie_id = t.id;
