Szablon wiadomości z polami z tabeli i bezpieczne podstawianie w T-SQL

Autor: Adam Siemiątkowski, SoftwareStudio Group Sp. z o.o.

Podstawianie pól w szablonie wiadomości T-SQL pozwala redaktorowi wskazać, które dane z bazy trafią do e-maila lub SMS-a, bez oddawania mu dostępu do całej bazy. Szablon zapisany w tabeli zawiera tokeny, a procedura zamienia je na wartości. Każda nazwa obiektu z tokenu trafia jednak do tekstu zapytania, więc wymaga osobnej ochrony.

Procedura rozbiera token na trzy części (tabela, kolumna, klucz), sprawdza je na liście dozwolonych pól i przez COL_LENGTH upewnia się, że kolumny istnieją. Nazwy otacza QUOTENAME przy budowie zapytania, a wartość klucza przekazuje parametrem do sp_executesql. Odczytana wartość wraca parametrem wyjściowym i nie trafia do tekstu zapytania.

Po co szablon wiadomości z polami w tabeli i gdzie jest w nim ryzyko

W przykładzie token ma postać [@POLE:tabela;kolumna;klucz], na przykład [@POLE:zgloszenia;numer_rma;id]. Wartość klucza podaje procedura jako parametr @id typu bigint, więc w przykładzie klucz jest liczbą. To wzorzec autorski, napisany od nowa z nazwami przykładowymi.

Dla porównania, w produkcie StudioSystem szablony e-mail i SMS mają własny token [@FIELD:tab#tabela;kol#kolumna;id#klucz], który składa treść wiadomości w momencie przygotowania do wysyłki. Pętla przetwarza najwyżej 99 tokenów, a brak wiersza daje w treści spację. Wzorzec z tego artykułu jest uproszczoną wersją z własną listą dozwolonych pól. Wiadomość wysyła pracownik, a procedura tylko składa jej treść. Od strony użytkownika opisuje to strona o wiadomości do klienta z szablonu, a to, co administrator zmienia sam, wyjaśnia strona o module reklamacji.

Ryzyko wynika z jednej cechy języka. Wartość klucza można przekazać do sp_executesql jako parametr, ale nazwy tabeli i kolumny nie, bo nie są wartościami, tylko częścią tekstu zapytania. Skoro redaktor szablonu wpisuje te nazwy w tokenie, procedura musi je wstawić do tekstu, a to wymaga dwóch zabezpieczeń naraz.

Pierwszym jest lista dozwolonych pól sprawdzana przed zapytaniem. Drugim jest QUOTENAME przy budowie tekstu. Samo QUOTENAME nie ogranicza dostępu, a nazwy doklejone bez niego łamią składnię, co pokazują dwie następne sekcje.

Token i jego rozbiór w T-SQL

Na początku tworzymy dane przykładowe i listę dozwolonych pól. Tabela zgloszenia ma kolumnę uwagi_wewnetrzne, której redaktor nie powinien mieć szansy wstawić do wiadomości. Drugie zgłoszenie ma w polu klienta tekst przypominający token, bo pozwoli to sprawdzić, że dane nie są rozwijane.

CREATE TABLE dbo.zgloszenia (
    id bigint NOT NULL PRIMARY KEY, klient nvarchar(100) NOT NULL, numer_rma varchar(20) NOT NULL,
    data_zgloszenia datetime NOT NULL, status nvarchar(30) NULL, uwagi_wewnetrzne nvarchar(200) NULL);
INSERT dbo.zgloszenia VALUES
 (1, N'Klient Testowy', '7C3A-1E94-B20F', '2026-10-12T14:30:00', N'Przyjęta', N'tylko dla serwisu'),
 (2, N'Klient [@POLE:abc]', '5D2B-8F01-C46A', '2026-10-13T09:00:00', NULL, N'tylko dla serwisu');

CREATE TABLE dbo.szablon_pola (
    tabela sysname NOT NULL, kolumna sysname NOT NULL, klucz sysname NOT NULL,
    PRIMARY KEY (tabela, kolumna));
INSERT dbo.szablon_pola VALUES
 (N'zgloszenia', N'klient', N'id'), (N'zgloszenia', N'numer_rma', N'id'),
 (N'zgloszenia', N'data_zgloszenia', N'id'), (N'zgloszenia', N'status', N'id');

Przykład ilustracyjny, dane wymyślone. Dane przykładowe i lista dozwolonych pól z kluczem głównym tabela plus kolumna.

Procedura składa się z jednej pętli. Pierwszy fragment znajduje token funkcją CHARINDEX i wycina jego treść między [@POLE: a ]. Potem dzieli ją po średnikach na trzy części. Jeśli brakuje zamknięcia nawiasu albo token nie ma dokładnie trzech części, procedura zgłasza THROW z numerem 50011 i komunikatem, że token ma mieć postać [@POLE:tabela;kolumna;klucz]. Wywołujący dostaje więc wyjątek, a nie pustą wiadomość. O tym, jak zachowuje się THROW po ROLLBACK w procedurze zapisu, pisze osobny artykuł.

Token z dwiema częściami, na przykład [@POLE:zgloszenia;klient], i token bez zamykającego ] kończą się w teście błędem 50011. Procedura powstaje przez CREATE OR ALTER, a THROW jest dostępny od SQL Server 2012.

CREATE OR ALTER PROCEDURE dbo.wypelnij_szablon
    @szablon nvarchar(max), @id bigint, @wynik nvarchar(max) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @poz int = 1, @p int, @k int, @s1 int, @s2 int, @tok nvarchar(max),
            @tab nvarchar(128), @kol nvarchar(128), @klucz nvarchar(128), @sql nvarchar(max), @w nvarchar(4000);
    WHILE CHARINDEX(N'[@POLE:', @szablon, @poz) > 0
    BEGIN
        SET @p = CHARINDEX(N'[@POLE:', @szablon, @poz);
        SET @k = CHARINDEX(N']', @szablon, @p);
        IF @k = 0 THROW 50011, N'Token ma mieć postać [@POLE:tabela;kolumna;klucz].', 1;
        SET @tok = SUBSTRING(@szablon, @p + 7, @k - @p - 7);
        SET @s1 = CHARINDEX(N';', @tok);
        SET @s2 = CHARINDEX(N';', @tok, @s1 + 1);
        IF @s1 = 0 OR @s2 = 0 OR CHARINDEX(N';', @tok, @s2 + 1) > 0
            THROW 50011, N'Token ma mieć postać [@POLE:tabela;kolumna;klucz].', 1;
        SELECT @tab = LEFT(@tok, @s1 - 1), @kol = SUBSTRING(@tok, @s1 + 1, @s2 - @s1 - 1),
               @klucz = SUBSTRING(@tok, @s2 + 1, 4000);
        IF NOT EXISTS (SELECT 1 FROM dbo.szablon_pola
                       WHERE tabela = @tab AND kolumna = @kol AND klucz = @klucz
                         AND COL_LENGTH(N'dbo.' + QUOTENAME(tabela), kolumna) IS NOT NULL
                         AND COL_LENGTH(N'dbo.' + QUOTENAME(tabela), klucz) IS NOT NULL)
            THROW 50012, N'Pole spoza listy dozwolonych.', 1;

Przykład ilustracyjny, dane wymyślone. Pierwsza część procedury: rozbiór tokenu i sprawdzenie listy dozwolonych pól.

Lista dozwolonych pól zamiast zaufania do tekstu szablonu

Sprawdzenie listy ma dwie części. Procedura szuka w tabeli szablon_pola wiersza zgodnego z trzema częściami tokenu, a przez COL_LENGTH upewnia się, że obie kolumny nadal istnieją. Klucz główny tej tabeli to tabela plus kolumna, więc dla jednej kolumny istnieje jeden klucz odczytu.

QUOTENAME chroni składnię, ale nie ogranicza dostępu: każda istniejąca kolumna dałaby się odczytać z szablonu. Dlatego lista jest osobnym zabezpieczeniem. W teście kolumna uwagi_wewnetrzne istnieje w tabeli zgloszenia, ale nie ma jej na liście. Token [@POLE:zgloszenia;uwagi_wewnetrzne;id] kończy się błędem 50012 z komunikatem, że pole jest spoza listy dozwolonych. To samo dzieje się dla nazwy kolumny z apostrofem i dla tokenu wskazującego widok systemowy.

Dopisanie wiersza do szablon_pola jest decyzją osoby odpowiedzialnej za dane, a nie edycją szablonu. Filtrowanie wierszy według tożsamości sesji to osobny wzorzec z artykułu o funkcji tożsamości w SQL Server.

Lista starzeje się po zmianie schematu. Po ALTER TABLE zgloszenia DROP COLUMN status zapytanie kontrolne zwraca jeden wiersz (zgloszenia, status, id), czyli wpis, który wskazuje usuniętą kolumnę.

SELECT p.tabela, p.kolumna, p.klucz
FROM dbo.szablon_pola AS p
WHERE COL_LENGTH(N'dbo.' + QUOTENAME(p.tabela), p.kolumna) IS NULL
   OR COL_LENGTH(N'dbo.' + QUOTENAME(p.tabela), p.klucz) IS NULL;

Przykład ilustracyjny. Zapytanie kontrolne pokazuje wpisy listy wskazujące kolumny, których już nie ma.

Zapytanie z QUOTENAME i parametrem klucza

Druga część procedury buduje tekst zapytania i wykonuje je. Wszystkie trzy nazwy z tokenu przechodzą przez QUOTENAME, który otacza nazwę nawiasami kwadratowymi i podwaja znak ]. Wartość klucza idzie parametrem @id, a odczytana wartość wraca parametrem wyjściowym @w typu nvarchar(4000).

        SET @sql = N'SELECT @w = CONVERT(nvarchar(4000), ' + QUOTENAME(@kol) + N', 23) FROM dbo.'
                 + QUOTENAME(@tab) + N' WHERE ' + QUOTENAME(@klucz) + N' = @id;';
        SET @w = NULL;
        EXEC sys.sp_executesql @sql, N'@id bigint, @w nvarchar(4000) OUTPUT', @id = @id, @w = @w OUTPUT;
        SET @w = ISNULL(@w, N'');
        SET @szablon = STUFF(@szablon, @p, @k - @p + 1, @w);
        SET @poz = @p + LEN(@w + N'.') - 1;
    END
    SET @wynik = @szablon;
END

Przykład ilustracyjny, dane wymyślone. Ciąg dalszy tej samej procedury: zapytanie z QUOTENAME, sp_executesql i zamiana tokenu w tekście.

Obie części tworzą jedną procedurę, a druga zaczyna się wewnątrz tej samej pętli. Nazwy z listy są już sprawdzone, więc QUOTENAME chroni tylko składnię.

Parametrem można przekazać wartość klucza, ale nie nazwę kolumny, więc nazwy trzeba najpierw sprawdzić na liście, a dopiero potem otoczyć QUOTENAME.

Co daje samo QUOTENAME, pokazuje test z nazwą kolumny numer'rma. Doklejona do zapytania bez QUOTENAME daje błąd składni, Msg 105: Unclosed quotation mark after the character string 'rma FROM dbo.zgloszenia'. Z QUOTENAME zapytanie jest poprawne składniowo i kończy się Msg 207: Invalid column name 'numer'rma'. Dowodem jest więc wyjątek składni albo nazwy, a nie zmieniony wynik.

Data w kolumnie datetime jest zamieniana przez CONVERT ze stylem 23 i daje postać 2026-10-12, czyli samą datę w formacie ISO. Bez stylu wynik dla datetime zależałby od ustawień sesji, dlatego przykład podaje go jawnie.

Wartość NULL w kolumnie daje pusty tekst: w teście pusty status zostawia w wiadomości zdanie Status: .. Brak wiersza o podanym kluczu, na przykład dla id = 999, też daje pusty tekst i zdanie Numer: .. Procedura nie rzuca w tych dwóch przypadkach błędu. To decyzja wzorca, którą łatwo zmienić na THROW, jeśli pusta wartość ma zatrzymywać wysyłkę.

Test na dziewięciu przypadkach

Test obejmuje dziewięć przypadków. Pierwszy to komplet pól dla zgłoszenia 1, a kolejne osiem sytuacji zestawia tabela poniżej. Wywołanie pojedynczego przypadku wygląda tak jak poniżej.

DECLARE @w nvarchar(max);
EXEC dbo.wypelnij_szablon
     N'Zgłoszenie [@POLE:zgloszenia;numer_rma;id] ma status: [@POLE:zgloszenia;status;id].', 1, @w OUTPUT;
SELECT @w AS wiadomosc;

Przykład ilustracyjny, dane wymyślone. Wywołanie skróconego szablonu o dwóch tokenach dla zgłoszenia 1 zwraca Zgłoszenie 7C3A-1E94-B20F ma status: Przyjęta. Pełny szablon z czterema tokenami sprawdza przypadek 1.

Wyniki testu na danych przykładowych. Dziewięć przypadków, w tym pięć błędów.

Przypadek 2 pokazuje, że tekst przypominający token w danych nie jest rozwijany: wynik to Klient: Klient [@POLE:abc]. Zapewnia to przesuwanie pozycji szukania za wstawioną wartość, czyli instrukcja @poz = @p + długość wartości. Pętla, która po każdej zamianie szukałaby tokenu od początku tekstu, rozwijałaby także tokeny znajdujące się we wstawionych wartościach, więc sama lista dozwolonych pól by nie wystarczyła.

Do długości wstawionej wartości procedura dodaje kropkę: LEN(@w + N'.') - 1. Funkcja LEN ignoruje końcowe spacje, więc bez tego wartość z końcową spacją przesunęłaby pozycję o jeden znak za mało.

Granice wzorca

Każdy token to osobne wywołanie sp_executesql. Wzorzec nie grupuje odczytów z jednego wiersza, więc szablon z wieloma tokenami dla jednego zgłoszenia wykonuje tyle zapytań, ile ma tokenów. Wartość dłuższa niż 4000 znaków zostałaby obcięta przez nvarchar(4000) parametru wyjściowego.

Przykład uruchomiliśmy na silniku SQL Server 2022 w jednorazowej bazie z poziomem zgodności 130, a nie na silniku 2016. Kod nie używa funkcji nowszych niż SQL Server 2016 SP1. Lista dozwolonych pól starzeje się po zmianie schematu, dlatego zapytanie kontrolne ma swoje miejsce w utrzymaniu bazy.

Przykład: administrator dopisuje do szablonu potwierdzenia token [@POLE:zgloszenia;numer_rma;id] i zapisuje szablon w tabeli. Gdy pracownik wysyła wiadomość dla zgłoszenia 1 (Klient Testowy, numer RMA 7C3A-1E94-B20F), procedura odczytuje numer parametrem @id i wstawia go do tekstu. Następnego dnia administrator, próbując dodać do szablonu [@POLE:zgloszenia;uwagi_wewnetrzne;id], dostaje błąd 50012, bo kolumny nie ma na liście dozwolonych pól.

Szczegóły wysyłki opisuje strona o wysyłaniu e-maili z programu. Walidację parametrów pokazuje artykuł o API na procedurach SQL i JSON, a sp_executesql w innym zastosowaniu artykuł o QUOTED_IDENTIFIER OFF w procedurze. Zmianę treści zapisanych w tabeli konfiguracji opisuje artykuł o zmianie zapytań z kontrolą skrótu.

Cztery pytania przed budową własnych szablonów z polami

Zanim zbudujesz podobny mechanizm u siebie, odpowiedz na cztery pytania.

• Redaktor: kto w organizacji redaguje szablony wiadomości i czy może wskazywać dowolne kolumny, czy tylko zatwierdzoną listę.
• Zakaz: które kolumny nie mogą trafić do wiadomości do klienta pod żadnym warunkiem.
• Puste wartości: czy brak wiersza lub pusta wartość mają dać pusty tekst, czy zatrzymać wysyłkę błędem.
• Klucz odczytu: czy jest liczbą, czy tekstem, i czy jedna tabela wystarczy do wszystkich pól szablonu.