SQL dynamiczny w T-SQL

Dynamiczny SQL jest to sposób na konstruowanie ciągów znaków zawierających wyrażenia SQL w aplikacji działającej na serwerze (albo nawet po stronie klienta) i wykonywanie ich w locie. W praktyce jest on wykorzystywany do pisania złożonych zapytań w trakcie wykonywania, co w niektorych przypadkach może zwiększyć wydajność jak i pomóć w wykonaniu zadań teoretycznie niemożliwych do wykonania.

Przy dynamicznym SQL nalezy jednak pamiętać, że w przypadku niepoprawnego jego wykorzystania narażamy się na otwarcie luk w systemie bepieczeństwa (tzw. SQL injection).

EXECUTE (EXEC)

Za pomocą wyrażenia EXECUTE (lub jego skrótu EXEC) możemy w najprostszy sposób skorzystać w funkcjonalności jaką jest dynamiczne tworzenie zapytania lub innej instrukcji. Wystarczy jako łańcuch znaków podać treść takiego wyrażenia. EXECUTE przyjmuje stałe, zmienne typów char/nchar, varchar/nvarchar albo ciągi poleceń zawierające poprawne wyrażenia języka T-SQL.

Powyższy przykład to jedynie pogląd jak skorzytać z SQL'a dynamicznego, bo przecież wystarczyłby sam SELECT i otrzymalibyśmy identyczny wynik. Jednakże prawdziwa moc dynamicznego SQL polega na tym, że możemy dynamicznie budować całe polecenie jakie mamy w planach uruchomić.

Poniżej przykład takiego dynamicznego zapytania, jakie zostało uruchomione.

Oczywiście złączenie moglibyśmy zrobić wcześniej niż w klauzuli EXECUTE, np wynik łączenia można by było przypisać do dodatkowej zmiennej.

Jednakże korzystanie z funkcjonalności jaką daje SQL dynaimczny, niesie za sobą ryzyko ataków typu SQL Injection, czyli wstrzyknięcie obcego kodu do wykonania.

Zmienna @vSQL ma w sobie zapytanie jakie ma zostać uruchomione, a co jeśli przed wykonaniem tego zapytania ktoś podmieni w nim instrukcje jakie mają być wykonane i np. wstrzyknie tam jakiegoś dropa?

Polecenie się powiedzie, a nam zniknie z produkcji być może dosyć istotna tabela.

Przyjrzyjmy się jescze jednemu przykładowi, gdzie wartość zmiennej @vNazwisko pochodziła z interfejcu użytkownika, np ze strony internetowej.

Na pierwszy rzut oka nie wygląda to groźnie, ale co gdyby ktoś do zmiennej @vNazwisko przypisał taki łańcuch:

Wtedy również z naszej produkcji zniknęłaby tabelka. Cały łańcuch zostałby natomiast odczytany w ten sposób:

Za pomocą dynamicznego SQL, ktoś nieupoważniony mógłby pobrać dowolne dane, wykonać dowolne wyrażenia INSERT, UPDATE, DELETE, DROP TABLE, TRUNCATE TABLE czy inne, wstawiające/modyfikujące/usuwające dane lub otwierające możliwość wejścia do systemu poprzez np przydzielenie sobie specjalnych uprawnień.

Jeśli jednak zdecydujemy się już na wykorzystanie korzyści jakie daje dynamiczny SQL, z łańcuchów znaków przekazywanych z interfejsu użytkownika to powinniśmy podjąć wszelkie znane znam środki ostrorożności, takie jak np:

-sprawdzanie pobieranych danych, jeśli oczekujemy jedynie liczb lub jedynie liter to powinniśmy sprawdzić czy dany łańcuch rzeczywiście tylko takie dane zawiera

-nie powinniśmy pozwalać na korzystanie z apostrofów, średników, nawiasów, komentarzy jednowierszowych (dwa minusy), komentarzy wielowierszowych (kombinacji /* */)

-powinniśmy sprawdzać zgodność danych wejściowych XML ze schematem XML, jeśli to tylko możliwe

-powinniśmy zachować szczególną ostrożność, jeśli dane wejściowe zawierają xp_ lub sp_, ponieważ może to oznaczać próbę uruchomienia procedur na lub XP serwerze.

SP_EXECUTESQL

Dynamiczny SQL można również uruchomić za pomocą procedury składowanej SP_EXECUTESQL. Procedura ta również przyjmuje łańcuch znaków jako polecenie do wykonania. Sposób ten wspiera parametry wejściowe jak i wyjściowe, pozwala parametryzować kod z minimalnym ryzykiem ataków typu SQL Injection. Często również plan wykonania zapytania może być lepszy niż przy samym uruchomieniu dynamicznego kodu za pomocą EXECUTE (bardziej optymalny).

Poniżej przykład wykorzystania procedury składowanej SP_EXECUTESQL

Szkolenie Kompleksowe SQL w Microsoft SQL Server →

To szkolenie może być dofinansowane dla Ciebie z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5