Odpowiedź

Historycznie zadania związane z dynamicznymi raportami o zmiennej liczbie grupowań nie były przewidziane przez standard SQL — jest on zaprojektowany do statycznie opisanych zapytań z wyraźnie określoną schemą wyników. Wraz z pojawieniem się platform BI i interaktywnych pulpitów nawigacyjnych programiści zaczęli szukać sposobów na konstruowanie zapytań SQL "na żywo", aby na żądanie użytkownika tworzyć złożone, wielokrotne wycinki danych.

Problem — SQL nie można programować zwykłą logiką if/else w SELECT — liczba kolumn, same pola i nawet GROUP BY są określane tylko na etapie kompilacji zapytania. Jeśli użytkownik chce uzyskać wycinek z kilku dowolnych pól, trzeba dynamicznie zbierać tekst zapytania i wykonywać go za pomocą EXECUTE/Dynamic SQL.

Rozwiązanie:

Generują SQL w aplikacji z późniejszym wywołaniem przez EXEC/EXECUTE. W niektórych przypadkach dla prostych zadań używają CASE i sztywnych szablonów, ale dla elastyczności zawsze sięga się po dynamiczny SQL:

Przykład kodu (Pseudo-kod):

-- Po stronie aplikacji (na przykład, Python)
groups = ['region', 'channel', 'month']
columns = [f'SUM({col}) AS {col}_sum' for col in selected_metrics]
group_by = ', '.join(groups)
selects = ', '.join(groups + columns)

query = f'SELECT {selects} FROM sales GROUP BY {group_by}'
-- Następnie wysyłanie tego zapytania przez interfejs application/sql

W DBMS z obsługą EXECUTE:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT ' + @selects + ' FROM sales GROUP BY ' + @group_by + ';';
EXEC sp_executesql @sql;

Kluczowe cechy:

Wymaga walidacji zmiennych, aby uniknąć SQL Injection
Zazwyczaj związane z architekturą aplikacji, szablony dla SELECT/GROUP BY są realizowane poza SQL
W przypadku dużej liczby opcji potrzebny jest inteligentny konstruktor zapytań lub szablon

Pytania z pułapką.

Czy można zaimplementować dynamiczną zmianę liczby kolumn (pivot/unpivot) tylko przez standardowy SELECT bez dynamicznego SQL?

Nie — tylko z góry znane wartości można "rozwinąć" za pomocą CASE/DECODE, ale nieznana liczba kolumn osiągana jest tylko dynamicznie.

Czy twoja logika dynamicznego SQL zapewnia ochronę przed iniekcją, jeśli używasz konkatencji ciągów?

Nie, ręczna konkatencja ciągów to stałe ryzyko SQL Injection. Należy koniecznie weryfikować listę pól/grup na białej liście, unikać fragmentów użytkownika bez sprawdzenia, a jeszcze lepiej używać parametrów tam, gdzie to możliwe.

Czy GROUP BY może przyjmować listę kolumn przez zmienną?

Standardowy SQL nie obsługuje przekazywania listy pól w GROUP BY przez zmienną/parametr. Konieczne jest dynamiczne formułowanie tekstu zapytania — po prostu nie można użyć zmiennej w liście GROUP BY.

Typowe błędy i antywzorce

Niezweryfikowane nazwy pól od użytkownika — iniekcje
Sztywny, zakodowany na stałe wykaz pól — brak elastyczności
Nie uwzględnianie możliwych pustych wartości w grupach

Przykład z życia

Negatywny przypadek

Inżynier BI pozwolił użytkownikowi bezpośrednio z interfejsu aplikacji przekazywać nazwy pól do raportu — nie filtrując ich przez białą listę. W wyniku testu iniekcji z „przekroczono” produkcyjną tabelę z powodu wprowadzonego złośliwego kodu w nazwie pola.

Zalety:

Maksymalna elastyczność w budowaniu raportów

Wady:

Bezpieczeństwo cierpi z powodu nieprzygotowanych zmiennych

Pozytywny przypadek

Inżynier wdrożył rygorystyczną walidację nazw — użytkownik mógł wybierać tylko z dozwolonych kolumn (z config/metadata), SQL dynamiczny był zbierany tylko w oparciu o białą listę, nie było drogi do iniekcji.