Dokumentacja

Praktyczna składnia SQLMacros do zarządzania zapytaniami z baz danych

Przeznaczenie i granice SQLMacros

SQLMacros to warstwa szablonująca dla SQL w Sapphire I.C.D.S. Standardowe zapytanie jest przechowywane w katalogu SQL/<Module>/<dialect>/, otrzymuje przygotowaną tabelę wartości, generuje końcowy tekst SQL i przekazuje go aktywnemu sterownikowi bazy danych. SQLMacros nie otwiera połączenia, nie wybiera backendu, nie rozpoczyna transakcji ani nie decyduje o tym, jakie zapytanie można wykonać. Te obowiązki pozostają w module i warstwie DB.

Dla MySQL i PostgreSQL utrzymuj oddzielne pliki, nawet jeśli zapytania są podobne. Podwójne cudzysłowy identyfikatorów, funkcje dat, konwersje typów, upsert i agregaty różnią się. Próba ukrycia dwóch dialektów w jednym złożonym szablonie jest zazwyczaj gorsza niż dwa krótkie, sprawdzalne pliki.

Podstawowe podstawienie Table.Data

W poniższych przykładach użyto ilustracyjnego schematu z tabelami example_articles i example_article_notes; to nie są rzeczywiste tabele Sapphire I.C.D.S.

Główne źródło danych wejściowych — Table.Data("field"). Dla jednego rekordu kursor jest zazwyczaj już przygotowany przez stronę wywołującą. Przykład pobierania z parametrami liczbowymi:

SELECT id, topic, created_at
FROM example_articles
WHERE layout_id = {{ Table.Data("layout_id") }}
  AND language_id = {{ Table.Data("language_id") }}
ORDER BY id DESC
LIMIT {{ Table.Data("limit") }} OFFSET {{ Table.Data("offset") }};

W ten sposób można wyświetlać tylko wartości, które moduł utworzył jako liczby całkowite i sprawdził w zakresie. Użytkowska linia "10; DELETE ..." nie powinna dotrzeć do pola numerycznego. Makro nie zamienia dowolnego tekstu na bezpieczną liczbę.

Liczby ciągowe i escape

Ciąg jest umieszczany w cudzysłowach SQL, a wartość przepuszczana przez aktywny dostawca escape DB:

SELECT id, topic
FROM example_articles
WHERE status = '{{ escape(Table.Data("status")) }}'
  AND LOWER(topic) LIKE CONCAT('%', LOWER('{{ escape(Table.Data("query")) }}'), '%');

escape(...) i kompatybilna wersja Escape(...) ucieka zawartość dla wybranego sterownika. Cudzysłowy wokół literału ciągowego pozostają częścią szablonu SQL. Nie stosuj escapowania HTML, ręcznej zamiany apostrofów ani kodowania URL zamiast escape DB.

Escape wartości nie pozwala na traktowanie jej jako identyfikatora. Nigdy nie pisz ORDER BY {{ Table.Data("sort") }}, FROM {{ ... }} ani dynamicznej nazwy kolumny z zapytania użytkownika. Dopuszczalne opcje sortowania i kolumny są wybierane w module na podstawie listy dozwolonych, po czym wywoływany jest konkretny statyczny szablon lub przekazywany jest z góry znany bezpieczny fragment na podstawie osobnej umowy.

Warunki w szablonie

Konstrukcje sterujące są zapisywane między {% i %}. Są przydatne dla małej liczby opcjonalnych filtrów:

SELECT id, topic, status
FROM example_articles
WHERE layout_id = {{ Table.Data("layout_id") }}
{% if Table.Data("status") %}
  AND status = '{{ escape(Table.Data("status")) }}'
{% endif %}
{% if Table.Data("query") %}
  AND LOWER(topic) LIKE CONCAT('%', LOWER('{{ escape(Table.Data("query")) }}'), '%')
{% endif %}
ORDER BY id DESC
LIMIT {{ Table.Data("limit") }};

Upewnij się, że każdy zestaw warunków pozostawia składniowo poprawny zapytanie. Wygodnie jest zacząć od obowiązkowego WHERE, a części opcjonalne dodawać za pomocą AND. Jeśli gałęzi jest wiele, lepiej stworzyć kilka logicznych zapytań: to łatwiejsze do testowania i bezpieczniejsze w utrzymaniu.

Komentarz szablonizatora ma formę {# wyjaśnienie #} i nie trafia do końcowego SQL. Standardowy -- SQL comment pozostaje w zapytaniu. Nie przechowuj sekretów, rzeczywistych danych osobowych ani tymczasowych obejść uprawnień w komentarzach.

INSERT i UPDATE

INSERT INTO example_article_notes (article_id, note, created_at)
VALUES ({{ Table.Data("article_id") }},
        '{{ escape(Table.Data("note")) }}',
        NOW());

Tutaj article_id musi być dodatnią liczbą z zaufanej strukturyzowanej tabeli, a note — ciągiem po escape. Weryfikacja właściciela, uprawnień użytkownika, istnienia artykułu i dopuszczalnej długości notatki odbywa się przed zapytaniem. SQLMacros nie dodaje tej polityki automatycznie.

Dla update/delete zawsze uwzględniaj dokładne ograniczenie encji i, jeśli jest to wymagane, właściciela lub układu. Masowa zmiana bez WHERE nie staje się akceptowalna z powodu tego, że jest przechowywana w szablonie. Do edycji konkurencyjnej używaj umowy revision/transaction przyjętej w module, a nie ręcznie stworzonego ciągu.

Pager: zapytanie danych i count

Lista z paginacją zazwyczaj ma dwa szablony: główne zapytanie z LIMIT/OFFSET i plik *.count.sql. Warunki filtrowania w nich muszą się zgadzać. W przeciwnym razie Pager wyświetli nieprawidłową liczbę stron. Wygodna kolejność sprawdzania:

skopiuj tylko blok filtrów do zapytania count;
sprawdź pusty wyszukiwanie;
sprawdź ciąg z apostrofem i znakami procenta;
sprawdź limit 1, ostatnią stronę i offset poza zakresem wyniku;
porównaj liczbę wierszy głównego zapytania z count przy tym samym wejściu.

Environment, POST i Cookie

SQLMacros technicznie udostępnia wartości środowiska i żądania poprzez wywołania typu Env.Get(...), Post.Get(...) i Cookie.Get(...). Dla zwykłej logiki aplikacji SQL nie odwołuj się do nich bezpośrednio. Moduł powinien przeczytać dane wejściowe HTTP, sprawdzić typ, znormalizować wartość, zastosować uprawnienia i umieścić wynik w Table. Taka ścieżka czyni SQL niezależnym od transportu i nadającym się do CGI, FastCGI, AJAX, usługi lub testu.

Co uważać za bezpieczny szablon

Struktura SQL, tabele, kolumny i operatory są zapisane statycznie.
Każdy ciąg przechodzi escape(Table.Data(...)) wewnątrz literału ciągu znaków.
Liczby, flagi, limit i offset są typowane i ograniczone do renderowania.
Szablon znajduje się w odpowiednim module i katalogu dialektu.
Wybór zapytania i pozwolenie na wykonanie pozostają w module.
Testy i diagnostyka potwierdzają, że pomyślny wynik nie zawiera niezdeklarowanych {{ ... }} ani {% ... %}.
Obecny runtime przy awarii SQLMacros może zwrócić oryginalny tekst szablonu; wywołujący obwód DB nie gwarantuje oddzielnego blokowania fail-closed. Nie traktuj renderowania jako granicy bezpieczeństwa: takie błędy należy wykrywać testami i logami przed produkcją.

Testuj SELECT na danych testowych, a zapytania modify — wewnątrz kontrolowanej transakcji lub na izolowanej bazie. Nie testuj nowego DELETE/UPDATE na produkcji. SQLMacros zmniejsza duplikację i centralizuje escape, ale bezpieczeństwo pojawia się tylko wraz z typowanym wejściem, autoryzacją serwerową i statyczną strukturą zapytania.

Diagnostyka wyniku renderowania

W przypadku błędu najpierw zarejestruj logiczną nazwę zapytania, dialekt i zestaw pól wejściowych bez wartości sekretnych. Następnie uzyskaj końcowy SQL w bezpiecznym obwodzie diagnostycznym i upewnij się, że wszystkie makra zostały rozwinięte, cudzysłowy ciągowe są zrównoważone, a opcjonalne gałęzie dały oczekiwany tekst. Nie naprawiaj problem dodawaniem przypadkowych cudzysłowów wokół liczb: wróć do typu pola i umowy modułu wywołującego.

Dla nowego zapytania przygotuj przypadki brzegowe: pusty ciąg, apostrof, Unicode, wynik zerowy, maksymalna dopuszczalna długość, minimalny i maksymalny identyfikator. Weryfikacja powinna obejmować oba obsługiwane pliki dialektu. Jeśli jeden backend nie jest używany lokalnie, zapytanie nadal nie powinno być uważane za gotowe przed walidacją składniową lub integracyjną w odpowiednim środowisku.