Sapphire Project Sp. z o.o.
Brilliancy of quality
Praktische SQLMacros-syntaxis voor databasequery's
Documentatie

Praktische SQLMacros-syntaxis voor databasequery's

Sapphire I.C.D.S.

Doel en grenzen van SQLMacros

SQLMacros is een sjabloonlaag voor SQL in Sapphire I.C.D.S. Een gewone query staat in de map SQL/<Module>/<dialect>/, ontvangt een voorbereide tabel met waarden, vormt de uiteindelijke SQL-tekst en geeft die aan het actieve databasestuurprogramma. SQLMacros opent geen verbinding, kiest geen backend, start geen transactie en bepaalt niet welke query mag worden uitgevoerd. Die verantwoordelijkheden blijven bij de module en de DB-laag.

Onderhoud afzonderlijke bestanden voor MySQL en PostgreSQL, ook als de query's op elkaar lijken. Aanhaling van identificatoren, datumfuncties, typeconversies, upsert en aggregaten verschillen. Twee dialecten in één ingewikkeld sjabloon proberen te verbergen is meestal slechter dan twee korte, controleerbare bestanden.

Basissubstitutie met Table.Data

De volgende voorbeelden gebruiken een illustratief schema met de tabellen example_articles en example_article_notes; dit zijn geen echte tabellen van Sapphire I.C.D.S.

De belangrijkste bron van invoergegevens is Table.Data("field"). Voor één rij is de cursor doorgaans al door de aanroepende kant voorbereid. Voorbeeld van een selectie met numerieke parameters:

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") }};

Op deze manier mag u alleen waarden uitvoeren die de module als gehele getallen heeft aangemaakt en op bereik heeft gecontroleerd. De gebruikerstekenreeks "10; DELETE ..." mag niet in een numeriek veld terechtkomen. De macro maakt willekeurige tekst niet tot een veilig getal.

Tekstliteralen en escape

Een tekenreeks komt tussen SQL-aanhalingstekens en de waarde gaat door de actieve DB escape provider:

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

escape(...) en de compatibele variant Escape(...) escapen de inhoud voor het geselecteerde stuurprogramma. De aanhalingstekens rond de tekenreeksliteraal blijven onderdeel van het SQL-sjabloon. Gebruik geen HTML escaping, handmatige vervanging van apostrofs of URL encoding in plaats van DB escape.

Het escapen van een waarde staat niet toe die als identificator te gebruiken. Schrijf nooit ORDER BY {{ Table.Data("sort") }}, FROM {{ ... }} of een dynamische kolomnaam uit een gebruikersaanvraag. Toegestane sorteer- en kolomvarianten worden in de module via een allowlist gekozen, waarna een concreet statisch sjabloon wordt aangeroepen of een vooraf bekend veilig fragment volgens een afzonderlijk contract wordt doorgegeven.

Voorwaarden in het sjabloon

Besturingsconstructies staan tussen {% en %}. Ze zijn bruikbaar voor een klein aantal optionele filters:

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") }};

Zorg dat iedere combinatie van voorwaarden een syntactisch correcte query oplevert. Het is handig om met een verplichte WHERE te beginnen en optionele onderdelen via AND toe te voegen. Als er veel vertakkingen zijn, kunt u beter meerdere logische query's maken: die zijn eenvoudiger te testen en veiliger te onderhouden.

Commentaar van de sjabloonengine heeft de vorm {# toelichting #} en komt niet in de uiteindelijke SQL terecht. Gewoon -- SQL comment blijft wel in de query staan. Bewaar in commentaar geen geheimen, echte persoonsgegevens of tijdelijke omzeilingen van rechten.

INSERT en UPDATE

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

Hier moet article_id een positief getal uit een vertrouwde gestructureerde tabel zijn en note een tekenreeks via escape. Controle van eigenaar, gebruikersrechten, bestaan van het artikel en toegestane notitielengte vindt vóór de query plaats. SQLMacros voegt dit beleid niet automatisch toe.

Neem voor update/delete altijd een precieze entiteitsbeperking op en waar nodig de eigenaar of layout. Een bulkwijziging zonder WHERE wordt niet aanvaardbaar doordat deze in een sjabloon staat. Gebruik voor gelijktijdige bewerking het revision/transaction contract dat in de module geldt, niet een zelfgemaakte tekenreeks.

Pager: gegevensquery en count

Een lijst met paginering heeft meestal twee sjablonen: de hoofdquery met LIMIT/OFFSET en een bestand *.count.sql. De filtervoorwaarden moeten daarin gelijk zijn, anders toont Pager een verkeerd aantal pagina's. Een bruikbare controlevolgorde is:

  1. kopieer alleen het filterblok naar de count-query;
  2. controleer een lege zoekopdracht;
  3. controleer een tekenreeks met een apostrof en procenttekens;
  4. controleer limit 1, de laatste pagina en een offset buiten het resultaat;
  5. vergelijk het aantal rijen van de hoofdquery met count bij dezelfde invoer.

Environment, POST en Cookie

SQLMacros stelt technisch omgevings- en aanvraagwaarden beschikbaar via aanroepen zoals Env.Get(...), Post.Get(...) en Cookie.Get(...). Spreek die niet rechtstreeks aan voor gewone SQL-logica van een toepassing. De module moet de HTTP-invoer lezen, het type controleren, de waarde normaliseren, rechten toepassen en het resultaat in Table plaatsen. Zo blijft SQL onafhankelijk van het transport en geschikt voor CGI, FastCGI, AJAX, een service of een test.

Wat een veilig sjabloon is

  • De SQL-structuur, tabellen, kolommen en operatoren zijn statisch geschreven.
  • Iedere tekenreeks gaat binnen een tekenreeksliteraal door escape(Table.Data(...)).
  • Getallen, vlaggen, limit en offset worden vóór rendering getypeerd en begrensd.
  • Het sjabloon staat in de juiste module- en dialect-map.
  • De keuze van de query en toestemming voor uitvoering blijven bij de module.
  • Tests en diagnose bevestigen dat een geslaagd resultaat geen onverwerkte {{ ... }} of {% ... %} bevat.
  • De huidige runtime kan bij een fout in SQLMacros de oorspronkelijke sjabloontekst teruggeven; de aanroepende DB-omgeving garandeert geen afzonderlijke fail-closed-blokkade. Beschouw rendering niet als security boundary: zulke fouten moeten vóór production met tests en logboeken worden ontdekt.

Controleer SELECT op testgegevens en wijzigingsquery's in een beheerste transactie of geïsoleerde database. Test geen nieuwe DELETE/UPDATE op production. SQLMacros vermindert duplicatie en centraliseert escaping, maar veiligheid ontstaat alleen samen met getypeerde invoer, serverautorisatie en een statische querystructuur.

Diagnose van het renderingresultaat

Leg bij een fout eerst de logische querynaam, het dialect en de set invoervelden vast zonder geheime waarden. Verkrijg vervolgens in een veilige diagnoseomgeving de uiteindelijke SQL en controleer of alle macro's zijn verwerkt, de aanhalingstekens van tekenreeksen in balans zijn en optionele vertakkingen de verwachte tekst opleveren. Los het probleem niet op door willekeurige aanhalingstekens rond getallen toe te voegen: ga terug naar het veldtype en het contract van de aanroepende module.

Bereid voor een nieuwe query grensgevallen voor: een lege tekenreeks, apostrof, Unicode, nul resultaten, maximaal toegestane lengte en minimale en maximale identificatie. De controle moet beide ondersteunde dialectbestanden omvatten. Als één backend lokaal niet wordt gebruikt, mag de query toch niet als gereed gelden vóór een syntactische of integratiecontrole in de bijbehorende omgeving.