Procedura z dynamicznym SQL

Procedura z dynamicznym SQL buduje i wykonuje polecenie jako tekst, co wymaga szczególnej ochrony przed wstrzyknięciem kodu.

Na czym polega procedura z dynamicznym SQL

Procedura z dynamicznym SQL tworzy instrukcję SQL w czasie działania, zwykle przez połączenie stałego szablonu z wartościami ustalonymi na podstawie parametrów wejściowych, konfiguracji albo wyników wcześniejszych zapytań. W odróżnieniu od procedury wykonującej z góry zapisaną instrukcję, taki kod może zmieniać fragmenty polecenia, na przykład warunek filtrowania, nazwę tabeli, kolejność sortowania lub zakres wybieranych kolumn. Utworzony tekst jest następnie przekazywany do mechanizmu wykonującego SQL danego silnika bazy danych.

Dynamiczne SQL bywa potrzebne wtedy, gdy struktura polecenia nie jest znana podczas projektowania procedury. Przykładem może być raport, w którym użytkownik wybiera jeden z wielu dozwolonych wariantów sortowania, albo operacja obejmująca tabelę wskazaną przez konfigurację aplikacji. Sam fakt budowania polecenia jako tekstu nie oznacza jeszcze błędu. Ryzyko pojawia się wtedy, gdy dane pochodzące z zewnątrz są bezpośrednio dopisywane do tekstu instrukcji bez odpowiedniej walidacji i bez rozdzielenia danych od składni SQL.

Różnica między wartością parametru a fragmentem składni

Najważniejszą zasadą ochrony jest parametryzacja wartości. Wartość wyszukiwana, identyfikator użytkownika, data albo liczba powinny być przekazane jako parametr wykonania, a nie wklejone do tekstu instrukcji. Silnik otrzymuje wówczas oddzielnie strukturę zapytania i oddzielnie dane, dzięki czemu zawartość parametru nie powinna zostać potraktowana jako operator, dodatkowe wyrażenie ani nowa instrukcja. Parametryzacja ogranicza także problemy z apostrofami, kodowaniem znaków i konwersją typów.

Parametr nie zastępuje jednak walidacji fragmentów, których silnik nie pozwala przekazywać jako zwykłych wartości. Nazwa tabeli, nazwa kolumny, kierunek sortowania oraz niektóre elementy składni są częścią struktury polecenia. Jeżeli muszą być wybierane dynamicznie, bezpieczniejszym rozwiązaniem jest zamknięta lista dozwolonych wartości, mapowanie wartości zewnętrznych na z góry ustalone identyfikatory oraz odrzucanie wszystkiego, czego nie przewidziano w projekcie. Samo usuwanie pojedynczych znaków, takich jak apostrof lub średnik, nie jest pełną metodą zabezpieczenia, ponieważ nie uwzględnia całej składni konkretnego silnika.

Walidacja, uprawnienia i przewidywalne wykonanie

Procedura wykorzystująca dynamiczne SQL powinna jasno określać, które dane mogą wpływać na tekst polecenia, a które są wyłącznie wartościami parametrów. Walidacja powinna sprawdzać typ, zakres i dozwolony format danych przed rozpoczęciem budowania instrukcji. Dla wyboru kolumn lub sortowania warto stosować mapę aplikacyjną, w której zewnętrzny kod, na przykład nazwa opcji interfejsu, odpowiada konkretnemu, zapisanemu wcześniej fragmentowi SQL. Takie mapowanie jest łatwiejsze do przejrzenia i przetestowania niż swobodne przyjmowanie tekstu od wywołującego.

Znaczenie ma także konto, w którego kontekście wykonywana jest procedura. Uprawnienia powinny być ograniczone do operacji koniecznych dla danego zadania, ponieważ błąd w konstrukcji zapytania albo wstrzyknięcie składni może wtedy objąć mniejszy zakres danych i operacji. Należy kontrolować, czy procedura może odczytywać, zmieniać lub usuwać dane, oraz czy dynamiczny tekst nie pozwala odwołać się do obiektów spoza zamierzonego obszaru. Weryfikacji wymagają również błędy wykonania, obsługa pustych wartości, nieprawidłowych typów oraz sytuacje, w których obiekt wskazany przez konfigurację nie istnieje.

Jak rozpoznać problem w działaniu procedury

Diagnostyka procedury z dynamicznym SQL powinna obejmować źródło każdego fragmentu tworzonej instrukcji, sposób przekazania parametrów i końcowy plan obsługi błędu. Warto sprawdzić, czy logika nie łączy danych użytkownika ze składnią, czy wartości są bindowane jako parametry oraz czy lista dynamicznych identyfikatorów jest zamknięta. Analizuje się również komunikaty błędów, nietypowe czasy wykonania, nieoczekiwane odczyty lub modyfikacje danych oraz rozbieżność między parametrami zapisanymi w logu a rzeczywistym przebiegiem procedury. Logowanie powinno pomagać odtworzyć zdarzenie, ale nie powinno bez potrzeby ujawniać poufnej zawartości danych.

Testy powinny obejmować zwykłe wartości, znaki specjalne, puste ciągi, wartości graniczne, nieoczekiwane typy oraz próby wprowadzenia dodatkowej składni. Osobno trzeba sprawdzić wybór niedozwolonej tabeli, kolumny lub kierunku sortowania, ponieważ te elementy są obsługiwane inaczej niż parametry danych. Po zmianie procedury należy porównać rezultaty z wersją statyczną, zweryfikować liczbę i zakres modyfikowanych rekordów oraz sprawdzić wpływ na wydajność. Problemy z planem wykonania mogą wynikać zarówno z konstrukcji dynamicznego SQL, jak i z niejednorodnych wartości parametrów, dlatego diagnostyka powinna łączyć analizę kodu, danych wejściowych i zachowania silnika bazy danych.