Lekcja 32. Dynamiczny SQL

Dynamic SQL określa technikę użycia kodu T-SQL do generowania i potencjalnego wykonywania innego kodu T-SQL. W praktyce często pojawia się potrzeba użycia kodu T-SQL który wymaga użycia wartości dynamicznie wyliczanych w czasie wykonania kodu. Niestety istnieje wiele przypadków w których zmienne nie mogą być podstawione do literałów kodu T-SQL. Przykładowo załóżmy, że chcemy policzyć wiersze w tabeli Production.Products za pomoc kodu:

USE TSQL2012;
GO
SELECT COUNT(*) AS ProductRowCount FROM [Production].[Products];

A teraz załóżmy, że chcemy użyć zmiennej w której przekazane będą schemat i nazwa tabeli dzięki czemu będziemy mogli wykonywać to zapytanie na wielu różnych tabelach. Niestety użycie zmiennej nie powiedzie się:

USE TSQL2012;
GO
DECLARE @tablename AS NVARCHAR(261) = N'[Production].[Products]';
SELECT COUNT(*) FROM @tablename;

Lecz łącząc instrukcję ze zmienną możemy wydrukować odpowiednie polecenie:

USE TSQL2012;
GO
DECLARE @tablename AS NVARCHAR(261) = N'[Production].[Products]';
PRINT N'SELECT COUNT(*) FROM ' + @tablename;

Takie zapytanie możemy skopiować i wykonać poprawnie w nowym oknie zapytania. Istnieją również komendy EXECUTE lub sp_executesql które pozwalają na wykonanie kodu znajdującego się w zmiennej.

DECLARE @tablename AS NVARCHAR(261) = N'[Production].[Products]';
EXECUTE(N'SELECT COUNT(*) AS TableRowCount FROM ' + @tablename);

Dynamiczny SQL obejmuje tworzenie dynamicznego kody T-SQL oraz jego wykonanie.

Użycie dynamicznego SQL

Dynamiczny SQL jest użyteczny ponieważ T-SQL nie pozwala na bezpośrednią zamianę wielu części komend poprzez zmienne. Te części to:

Nazwa bazy danych w wyrażeniu USE.
Nazwa schematu i tabeli w klauzuli FROM.
Nazwy kolumn w klauzulach SELECT, WHERE, GROUP BY i HAVING (oprócz ORDER BY).
Zawartości list w klauzulach IN i PIVOT.

Aby użyć zmiennych dla tych części komend trzeba użyć dynamicznego SQL. Przykładowe scenariusze użycia dynamicznego SQL to:

Generowanie kodu do automatyzacji zadań administracyjnych.
Wykonywanie zadań na wszystkich bazach danych na serwerze lub wszystkich obiektach określonego typu w bazie danych.
Budowanie procedur składowanych z wieloma parametrami na podstwie których budowane są różne zapytania w treści procedury.
Konstruowanie sparametryzowanych zapytań ad hoc które potrafią użyć zapisanych w pamięci planów zapytania.

Generowanie literałów T-SQL

Kiedy generujemy wyrażenia T-SQL to pracujemy z ciągami znaków i musimy zwrócić szczególną uwagę na sposób w jaki wyznaczamy te ciągi. W SQL Serwerze 2012 domyślnie używamy pojedynczego apostrofu na początku i na końcu ciągu. Wynika to z ustawienia QUOTED_IDENTIFIER. Kiedy QUOTED_IDENTIFIER jest ustawione na ON (domyślnie) to używamy pojedynczego znaku apostrofa do wyznaczania granic ciągów znaków oraz znaku podwójnego apostrofa lub nawiasu kwadratowego do wyznaczania identyfikatorów T-SQL. Jeżeli QUOTED_IDENTIFIER jest wyłączone to można używać znaków ' i " do ograniczania ciągów znaków i tylko [] do ograniczania identyfikatorów T-SQL. Użycie pojedynczego znaku apostrofa jako ogranicznika ciągu znaków może prowadzić do problemu. Przykładowo jak wyszukać pojedynczy znak apostrofu w tekście lub ciągu zawierającego apostrof:

USE TSQL2012;
GO
SELECT custid, companyname, contactname, contacttitle, addressFROM [Sales].[Customers]
WHERE address = N'5678 rue de l'Abbaye';

Wynik:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '.'. Msg 105, Level 15, State 1, Line 2 Unclosed quotation mark after the character string '; '.

SQL Serwer traktuje wyszukiwany ciąg jako "5678 rue de l" ponieważ występuje znak apostrofu. Pozostała część po znaku apostrofu traktowana jest jako błąd składni. W celu wyszukania ciągu z apostrofem należy zamienić pojedynczy apostrof na dwa pojedyncze apostrofy:

SELECT custid, companyname, contactname, contacttitle, addressFROM [Sales].[Customers]
WHERE address = N'5678 rue de l''Abbaye';

Niestety użycie dwóch pojedynczych znaków apostrofu jest trudne do czytania i zrozumienia dynamicznego kodu T-SQL. Przykładowo wydrukowania poprzedniej komendy za pomocą komendy PRINT:

PRINT N'SELECT custid, companyname, contactname, contacttitle, address
FROM [Sales].[Customers]
WHERE address = N''5678 rue de l''''Abbaye'';';

Alternatywnym rozwiązaniem jest użycie funkcji QUOTENAME która potrafi schować kompleksowość zagnieżdżonych znaków apostrofu np:

PRINT QUOTENAME(N'5678 rue de l''Abbaye', '''');

Wynik:

'5678 rue de l''Abbaye'

Komenda EXECUTE

Najprostszą metodą wykonującą dynamiczny kod SQL jest komenda EXECUTE która może być również zapisana w skrócie jako EXEC. Ma ona kilka zastosowań, tylko jedno dotyczy dynamicznego SQL'a:

Wykonywanie procedur składowanych.
Wykonywanie zapytań na serwerach połączonych.
Personifikowanie.
Wykonywanie dynamicznego kodu SQL.

Choć EXEC ie jest funkcją to akceptuje wejściowy ciąg znaków w nawiasach. Ciąg wejściowy musi być pojedynczą porcją wsadową T-SQL. Może zawierać wiele komend T-SQL ale nie może zawierać komendy GO. Można używać w nim literałów, zmiennych lub połączenia obu. Zmienne mogą mieć każdy typ akceptowany przez SQL Serwer (również o maksymalnej długości MAX). Poniżej przedstawiony jest przykład ilustrujący wykorzystanie komendy EXEC z parametrami:

USE TSQL2012;
GO
DECLARE @SQLString AS NVARCHAR(4000)
,@tablename AS NVARCHAR(261) = '[Production].[Products]';
SET @SQLString = 'SELECT COUNT(*) AS TableRowCount FROM ' + @tablename;
EXEC (@SQLString);

Komenda EXEC może akceptować również więcej niż jedną zmienną:

USE TSQL2012;
GO
DECLARE @SQLString AS NVARCHAR(MAX)
, @tablename AS NVARCHAR(261) = '[Production].[Products]';
SET @SQLString = 'SELECT COUNT(*) AS TableRowCount FROM '
EXEC(@SQLString + @tablename);

Wstrzykiwanie kodu SQL

Użycie dynamicznego kodu SQL w aplikacjach które wysyłają dane wejściowe do bazy danych może być podmiotem prowadzącym do wstrzykiwania kodu SQL. Jest to bardzo rozległy temat. Jako programista T-SQL musisz chronić kod T-SQL przed możliwością wstrzykiwania kodu SQL. Hakerzy nauczyli się, że wprowadzenie pojedynczego znaku cudzysłowu może być powodem błędu aplikacji co wskazuje na to, że polecenie zostało wykonane za pomocą dynamicznego kodu SQL i może być hakowane. Po wprowadzenie pojedynczego znaku cudzysłowu SQL Serwer generuje błąd:

Msg 105, Level 15, State 1, Line 1 Unclosed quotation mark after the character string '''. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '''.

Taka informacja daje znak hakerowi, że może on wykonać dodatkowy kod w danym formularzu wejściowym. Hakerzy używają również dodatkowego komentarza aby mieć pewność, że SQL Serwer zignoruje kończący cudzysłów. Jeżeli taki kod zadziała to haker wie, że może wstrzyknąć kod T-SQL jak na poniższym przykładzie:

' SELECT TOP 1 name FROM sys.tables --

Oczywiście pole w formularzu musi zezwalać na wstawienie wystarczającej ilości znaków aby można było wstrzyknąć kod. Bardziej niebezpiecznym kodem niż SELECT jest DELETE lub DROP. Istnieje wiele metod zapobiegających wstrzykiwaniu kodu SQL. Jedną z najważniejszych zaimplementowanych w SQL Serwerze jest używanie parametrycznego dynamicznego kodu SQL wykonywanego za pomocą sp_executesql.

Użycie sp_executesql

Systemowa procedura sp_executesql jest alternatywą dla komendy EXEC służącej do wykonywania dynamicznego kodu SQL. Procedura sp_executesql używa parametrów (także wyjściowych). Parametry muszą być wstawione jako znaki UNICODE. Użycie parametrów czyni tę metodę bardziej bezpieczną i pozwala zapobiegać pewnym typom wstrzykiwania kodu SQL. Parametry sp_executesql nie mogą być używane do zastąpienia wymaganych literałów jak nazwy tabeli czy kolumny. Składnia wygląda następująco:

sp_executesql [ @statement = ] statement
[ { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }
{ , [ @param1 = ] 'value1' [ ,...n ] }]

Parametr @statement jest domyślnie typu NVARCHAR(MAX). Wstawiamy go jako łańcuch znaków UNICODE i zagnieżdżamy w nim parametry jakich chcemy użyć. Wymieniamy nazwy tych parametrów oraz ich typy w parametrze @params. W liście "@param1 = value, ..." umieszczamy ich wartości. Poniżej znajduje się przykład kodu używającego sp_executesql:

USE TSQL2012;
GO
DECLARE @SQLString AS NVARCHAR(4000), @address AS NVARCHAR(60);
SET @SQLString = N'
SELECT custid, companyname, contactname, contacttitle, address
FROM [Sales].[Customers]
WHERE address = @address';
SET @address = N'5678 rue de l''Abbaye';
EXEC sp_executesql
@statement = @SQLString
, @params = N'@address NVARCHAR(60)'
, @address = @address;

Procedura sp_executesql może czasami być bardziej wydajna niż komenda EXEC ponieważ parametryzacja pozwala na ponowne użycie zapisanych w pamięci planów zapytań.

Ćwiczenia

Generowanie dynamicznego kodu T-SQL i użycie QUOTENAME

Użyj kodu przedstawionego poniżej demonstrującego generowanie dynamicznego kodu T-SQL z użyciem zmiennej.

USE TSQL2012;
GO
DECLARE @address AS NVARCHAR(60) = '5678 rue de l''Abbaye';
PRINT N'SELECT *
FROM [Sales].[Customers]
WHERE address = '+ @address;

Użyj kodu poniżej demonstrującego użycie funkcji QUOTENAME.

USE TSQL2012;
GO
DECLARE @address AS NVARCHAR(60) = '5678 rue de l''Abbaye';
PRINT N'SELECT *
FROM [Sales].[Customers]
WHERE address = '+ QUOTENAME(@address, '''') + ';';

Zapobieganie wstrzykiwaniu kodu SQL

Utwórz procedurę składowaną używającą dynamicznego kodu SQL do zwracania listy klientów na podstawie adresu:

USE TSQL2012;
GO
IF OBJECT_ID('Sales.ListCustomersByAddress') IS NOT NULL
DROP PROCEDURE Sales.ListCustomersByAddress;
GO
CREATE PROCEDURE Sales.ListCustomersByAddress
@address NVARCHAR(60)
AS
DECLARE @SQLString AS NVARCHAR(4000);
SET @SQLString = '
SELECT companyname, contactname
FROM Sales.Customers WHERE address = ''' + @address + '''';
-- PRINT @SQLString;
EXEC(@SQLString);
RETURN;
GO

Wykonaj procedurę z normalnym adresem '8901 Tsawassen Blvd.' jako parametr @address:

USE TSQL2012;
GO
EXEC Sales.ListCustomersByAddress @address = '8901 Tsawassen Blvd.';

Wykonaj procedurę z dwoma pojedynczymi cudzysłowami jako parametr @address:
```
USE TSQL2012;
GO
EXEC Sales.ListCustomersByAddress @address = '''';
```
Wynik:
Msg 105, Level 15, State 1, Line 3 Unclosed quotation mark after the character string '''. Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '''.
Wykonaj procedurę z dwoma pojedynczymi cudzysłowami i znakami komentarza jako parametr @address
```
USE TSQL2012;
GO
EXEC Sales.ListCustomersByAddress @address = ''' -- ';
```
Zauważ brak błędu w wyniku!
Aby zademonstrować wstrzykiwanie kodu wykonaj procedurę z dwoma pojedynczymi cudzysłowami i zapytaniem "SELECT 1--". Zamiast tego zapytania haker może użyć dowolnej komendy.
```
USE TSQL2012;
GO
EXEC Sales.ListCustomersByAddress @address = ''' SELECT 1 -- ';
```

Zmień procedurę tak aby używała sp_execute:

USE TSQL2012;
GO
IF OBJECT_ID('Sales.ListCustomersByAddress') IS NOT NULL
DROP PROCEDURE Sales.ListCustomersByAddress;
GO
CREATE PROCEDURE Sales.ListCustomersByAddress
@address AS NVARCHAR(60)
AS
DECLARE @SQLString AS NVARCHAR(4000);
SET @SQLString = '
SELECT companyname, contactname
FROM Sales.Customers WHERE address = @address';
EXEC sp_executesql
@statment = @SQLString
, @params = N'@address NVARCHAR(60)'
, @address = @address;
RETURN;
GO

Spróbuj wykonać procedurę ze wstrzykiwaniem kodu SQL:

USE TSQL2012;
GO
EXEC Sales.ListCustomersByAddress @address = '''';
EXEC Sales.ListCustomersByAddress @address = ''' -- ';
EXEC Sales.ListCustomersByAddress @address = ''' SELECT 1 -- ';

Użycie parametru OUTPUT z procedurą sp_executesql

Użyj poniższego kodu przedstawiającego użycie sp_executesql z parametrem OUTPUT dzięki któremu można zapisać zwracane wartości w parametrze wyjściowym.

USE TSQL2012;
GO
DECLARE @SQLString AS NVARCHAR(4000)
, @outercount AS int;
SET @SQLString = N'SET @innercount = (SELECT COUNT(*) FROM Production.Products)';
EXEC sp_executesql
@statment = @SQLString
, @params = N'@innercount AS int OUTPUT'
, @innercount = @outercount OUTPUT;
SELECT @outercount AS 'RowCount';

Podsumowanie

Dynamiczny kod SQL może być używany do generowania i wykonywania kodu T-SQL w przypadkach kiedy musi on być konstruowany w czasie wykonania.
Wstrzykiwanie kodu SQL jest potencjalnym zagrożeniem używania dynamicznego kodu SQL.
Procedura sp_executesql może być używana do przeciwdziałania wstrzykiwaniu kodu ponieważ wymaga ona parametryzowania kodu SQL.