Dokumentation

Praktische Syntax von SQLMacros zur Verwaltung von Datenbankabfragen

Zweck und Grenzen von SQLMacros

SQLMacros ist eine Template-Schicht für SQL in Sapphire I.C.D.S. Eine normale Abfrage wird im Katalog gespeichert SQL/<Module>/<dialect>/, erhält eine vorbereitete Tabellenkombination, generiert den endgültigen SQL-Text und übergibt diesen an den aktiven Datenbanktreiber. SQLMacros öffnet keine Verbindung, wählt keinen Backend aus, startet keine Transaktion und entscheidet nicht, welche Abfrage ausgeführt werden darf. Diese Verantwortlichkeiten verbleiben beim Modul und der DB-Schicht.

Für MySQL und PostgreSQL verwenden Sie separate Dateien, auch wenn die Abfragen ähnlich sind. Anführungszeichen für Identifikatoren, Datumsfunktionen, Typumwandlungen, Upsert und Aggregationen unterscheiden sich. Der Versuch, zwei Dialekte in ein einziges komplexes Template zu zwängen, ist meist schlechter als zwei kurze überprüfbare Dateien.

Grundlegende Substitution von Table.Data

In den folgenden Beispielen wird ein illustratives Schema mit Tabellen verwendet example_articles und example_article_notes; dies sind keine echten Sapphire I.C.D.S.-Tabellen.

Die Hauptquelle der Eingabedaten ist Table.Data("field"). Für eine Zeile ist der Cursor normalerweise bereits von der aufrufenden Seite vorbereitet. Beispiel für die Abfrage mit numerischen Parametern:

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

Auf diese Weise können nur Werte ausgegeben werden, die das Modul als Ganzzahlen erstellt und im Bereich überprüft hat. Eine benutzerdefinierte Zeichenkette "10; DELETE ..." darf nicht bis zum numerischen Feld gelangen. Das Makro wandelt keinen beliebigen Text in eine sichere Zahl um.

String-Literale und Escape

Die Zeichenkette wird in SQL-Anführungszeichen gesetzt, und der Wert durch einen aktiven DB escape provider geleitet:

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

escape(...) und eine kompatible Variante Escape(...) maskiert den Inhalt für den ausgewählten Treiber. Die Anführungszeichen um das String-Literal bleiben Teil des SQL-Templates. Verwenden Sie kein HTML Escaping, keine manuelle Apostroph-Ersetzung oder URL Encoding anstelle von DB Escape.

Das Maskieren eines Wertes erlaubt es nicht, ihn zu einem Identifikator zu machen. Schreiben Sie niemals ORDER BY {{ Table.Data("sort") }}, FROM {{ ... }} oder einen dynamischen Spaltennamen aus der Benutzerabfrage. Die zulässigen Sortier- und Spaltenoptionen werden im Modul über eine Whitelist ausgewählt, woraufhin ein spezifisches statisches Template aufgerufen oder ein vordefinierter sicherer Fragment nach einem separaten Vertrag übergeben wird.

Bedingungen im Template

Steuerungsstrukturen werden zwischen {% und %}. Sie sind nützlich für eine kleine Anzahl optionaler Filter:

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

Achten Sie darauf, dass jeder Satz von Bedingungen eine syntaktisch korrekte Abfrage ergibt. Es ist praktisch, mit einer obligatorischen WHERE, und optionalen Teilen über AND. Wenn es viele Verzweigungen gibt, ist es besser, mehrere logische Abfragen zu erstellen: das ist einfacher zu testen und sicherer zu warten.

Der Kommentar des Templatisierers hat die Form {# Erklärung #} und gelangt nicht in das finale SQL. Ein normaler -- SQL comment bleibt in der Abfrage. Speichern Sie keine Geheimnisse, echte personenbezogene Daten oder temporären Rechte-Workarounds in Kommentaren.

INSERT und UPDATE

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

Hier article_id muss eine positive Zahl aus einer vertrauenswürdigen strukturierten Tabelle sein, und note — ein String mittels Escape. Die Überprüfung des Besitzers, der Benutzerrechte, der Artikelexistenz und der zulässigen Notizlänge erfolgt vor der Abfrage. SQLMacros fügt diese Richtlinie nicht automatisch hinzu.

Für Update/Delete schließen Sie immer eine exakte Entitätseinschränkung ein und bei Bedarf den Besitzer oder das Layout. Eine Massenänderung ohne WHERE wird nicht akzeptabel, da sie im Template gespeichert ist. Für die kollaborative Bearbeitung verwenden Sie den im Modul festgelegten Revision/Transaktionsvertrag und nicht eine selbst erstellte Zeichenkette.

Pager: Datenabfrage und Count

Eine Liste mit Paginierung hat normalerweise zwei Templates: die Hauptabfrage mit LIMIT/OFFSET und die Datei *.count.sql. Die Filterbedingungen müssen darin übereinstimmen. Andernfalls zeigt der Pager eine falsche Seitenzahl an. Der einfache Prüfablauf:

kopieren Sie nur den Filterblock in die Count-Abfrage;
prüfen Sie die leere Suche;
prüfen Sie die Zeichenkette mit Apostroph und Prozentzeichen;
prüfen Sie limit 1, die letzte Seite und offset außerhalb des Ergebnisses;
vergleichen Sie die Anzahl der Zeilen der Hauptabfrage mit dem Count bei gleichen Eingaben.

Environment, POST und Cookie

SQLMacros stellt technisch Werte der Umgebung und der Anfrage über Aufrufe wie Env.Get(...), Post.Get(...) und Cookie.Get(...). Für normale anwendungsseitige SQL-Logik greifen Sie nicht direkt darauf zu. Das Modul muss die HTTP-Eingabe lesen, den Typ überprüfen, den Wert normalisieren, Berechtigungen anwenden und das Ergebnis in Table. Dieser Weg macht SQL unabhängig vom Transport und geeignet für CGI, FastCGI, AJAX, Services oder Tests.

Was als sicheres Template gilt

Die Struktur von SQL, Tabellen, Spalten und Operatoren sind statisch geschrieben.
Jede Zeichenkette durchläuft escape(Table.Data(...)) innerhalb eines String-Literals.
Zahlen, Flags, limit und offset sind typisiert und begrenzt bis zum Rendern.
Das Template liegt im richtigen Modul und Dialektkatalog.
Die Auswahl der Abfrage und die Berechtigung zur Ausführung verbleiben beim Modul.
Tests und Diagnosen bestätigen, dass ein erfolgreiches Ergebnis keine unaufgelösten {{ ... }} oder {% ... %}.
Der aktuelle Laufzeitzustand bei einem Fehler von SQLMacros kann den ursprünglichen Text des Templates zurückgeben; der aufrufende DB-Kontext garantiert keine separate fail-closed Sperrung. Betrachten Sie das Rendern nicht als Security Boundary: solche Fehler müssen durch Tests und Logs vor der Produktion gefunden werden.

Testen Sie SELECT mit Testdaten, und modify-Abfragen innerhalb einer kontrollierten Transaktion oder auf einer isolierten Datenbank. Testen Sie kein neues DELETE/UPDATE in der Produktion. SQLMacros reduziert die Duplizierung und zentralisiert das Escaping, aber die Sicherheit entsteht nur zusammen mit dem typisierten Input, der serverseitigen Autorisierung und der statischen Struktur der Abfrage.

Diagnose des Render-Ergebnisses

Bei einem Fehler zuerst das logische Namen der Abfrage, den Dialekt und die Menge der Eingabefelder ohne geheime Werte protokollieren. Anschließend erhalten Sie das finale SQL in einem sicheren Diagnosekontext und stellen sicher, dass alle Makros aufgelöst sind, die String-Anführungszeichen ausgeglichen sind und optionale Verzweigungen den erwarteten Text geliefert haben. Korrigieren Sie das Problem nicht durch Hinzufügen zufälliger Anführungszeichen um Zahlen: Kehren Sie zum Feldtyp und dem Vertrag des aufrufenden Moduls zurück.

Für eine neue Abfrage vorbereiten Sie Grenzfälle: leere Zeichenkette, Apostroph, Unicode, null Ergebnis, maximale zulässige Länge, minimaler und maximaler Identifikator. Die Überprüfung muss beide unterstützten Dialekt-Dateien abdecken. Wenn ein Backend lokal nicht verwendet wird, sollte die Abfrage trotzdem nicht als fertig betrachtet werden, bevor sie in der entsprechenden Umgebung syntaktisch oder integriert geprüft wurde.