Sapphire Project Sp. z o.o.
Brilliancy of quality
Sintassi pratica di SQLMacros per gestire le query del database
Documentazione

Sintassi pratica di SQLMacros per gestire le query del database

Sapphire I.C.D.S.

Scopo e limiti di SQLMacros

SQLMacros è il livello di template per SQL in Sapphire I.C.D.S. Una normale query viene conservata nella directory SQL/<Module>/<dialect>/, riceve una tabella di valori preparata, genera il testo SQL finale e lo passa al driver del database attivo. SQLMacros non apre una connessione, non sceglie il backend, non avvia una transazione e non decide quale query sia consentito eseguire. Queste responsabilità restano nel modulo e nel livello DB.

Mantenete file separati per MySQL e PostgreSQL anche quando le query sono simili. Le virgolette degli identificatori, le funzioni di data, le conversioni dei tipi, gli upsert e gli aggregati sono differenti. Tentare di nascondere due dialetti in un unico template complesso è in genere peggiore di due file brevi e verificabili.

Sostituzione di base con Table.Data

Gli esempi seguenti utilizzano uno schema illustrativo con le tabelle example_articles e example_article_notes; non sono tabelle reali di Sapphire I.C.D.S.

La fonte principale dei dati di input è Table.Data("field"). Per una singola riga, il chiamante ha normalmente già preparato il cursore. Esempio di selezione con parametri numerici:

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

In questo modo si possono inserire solo valori che il modulo ha creato come interi e verificato rispetto all'intervallo. Una stringa utente come "10; DELETE ..." non deve raggiungere un campo numerico. La macro non trasforma un testo arbitrario in un numero sicuro.

Letterali stringa ed escape

Una stringa viene racchiusa tra virgolette SQL e il valore passa attraverso il DB escape provider attivo:

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

escape(...) e la variante compatibile Escape(...) applicano l'escape al contenuto per il driver selezionato. Le virgolette intorno al letterale stringa rimangono parte del template SQL. Non usate HTML escaping, sostituzione manuale degli apostrofi o URL encoding al posto dell'escape DB.

L'escape di un valore non consente di usarlo come identificatore. Non scrivete mai ORDER BY {{ Table.Data("sort") }}, FROM {{ ... }} o un nome dinamico di colonna proveniente dalla richiesta di un utente. Il modulo sceglie da una allowlist le opzioni consentite di ordinamento e colonna, quindi richiama uno specifico template statico oppure passa un frammento sicuro noto in anticipo secondo un contratto separato.

Condizioni nel template

I costrutti di controllo vengono scritti tra {% e %}. Sono utili per un numero limitato di filtri facoltativi:

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

Assicuratevi che qualsiasi combinazione di condizioni produca una query sintatticamente corretta. È pratico iniziare con un WHERE obbligatorio e aggiungere le parti facoltative tramite AND. Se le diramazioni sono numerose, è preferibile creare più query logiche: sono più facili da testare e da mantenere in sicurezza.

Un commento del motore di template ha la forma {# spiegazione #} e non compare nel SQL finale. Un normale -- SQL comment rimane nella query. Non conservate nei commenti segreti, dati personali reali o espedienti temporanei per aggirare i diritti.

INSERT e UPDATE

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

Qui article_id deve essere un numero positivo proveniente da una tabella strutturata affidabile, mentre note deve essere una stringa elaborata con escape. Proprietario, diritti dell'utente, esistenza dell'articolo e lunghezza consentita della nota vengono verificati prima della query. SQLMacros non aggiunge automaticamente questa politica.

Per update/delete, includete sempre un vincolo preciso sull'entità e, quando necessario, sul proprietario o sul layout. Una modifica massiva senza WHERE non diventa accettabile perché è conservata in un template. Per la modifica concorrente usate il revision/transaction contract adottato dal modulo, non una stringa improvvisata.

Pager: query dei dati e count

Un elenco paginato utilizza normalmente due template: la query principale con LIMIT/OFFSET e un file *.count.sql. Le condizioni di filtro devono coincidere; in caso contrario, Pager mostrerà un numero errato di pagine. Un ordine pratico per la verifica è:

  1. copiare nella query count solo il blocco dei filtri;
  2. verificare una ricerca vuota;
  3. verificare una stringa con un apostrofo e caratteri percentuale;
  4. verificare limit 1, l'ultima pagina e un offset oltre i risultati;
  5. confrontare il numero di righe della query principale con count usando lo stesso input.

Environment, POST e Cookie

SQLMacros rende tecnicamente disponibili i valori di ambiente e della richiesta tramite chiamate come Env.Get(...), Post.Get(...) e Cookie.Get(...). Non accedetevi direttamente per la normale logica SQL dell'applicazione. Il modulo deve leggere l'input HTTP, verificarne il tipo, normalizzare il valore, applicare i diritti e inserire il risultato in Table. Questo percorso rende SQL indipendente dal trasporto e adatto a CGI, FastCGI, AJAX, un servizio o un test.

Cosa considerare un template sicuro

  • La struttura SQL, le tabelle, le colonne e gli operatori sono scritti staticamente.
  • Ogni stringa passa attraverso escape(Table.Data(...)) all'interno di un letterale stringa.
  • Numeri, flag, limit e offset sono tipizzati e limitati prima del rendering.
  • Il template si trova nel modulo e nella directory del dialetto corretti.
  • La scelta della query e l'autorizzazione a eseguirla restano nel modulo.
  • Test e diagnostica confermano che un risultato riuscito non contiene {{ ... }} o {% ... %} non risolti.
  • In caso di errore di SQLMacros, il runtime attuale può restituire il testo originale del template; il perimetro DB chiamante non garantisce un blocco fail-closed separato. Non considerate il rendering una security boundary: questi errori devono essere rilevati da test e log prima della production.

Verificate i SELECT con dati di test e le query di modifica all'interno di una transazione controllata o su un database isolato. Non testate un nuovo DELETE/UPDATE in production. SQLMacros riduce la duplicazione e centralizza l'escape, ma la sicurezza esiste solo insieme a input tipizzato, autorizzazione lato server e struttura statica della query.

Diagnostica del risultato del rendering

Quando si verifica un errore, registrate innanzitutto il nome logico della query, il dialetto e l'insieme dei campi di input senza valori segreti. Ottenete poi il SQL finale in un perimetro diagnostico sicuro e verificate che tutte le macro siano state risolte, che le virgolette delle stringhe siano bilanciate e che i rami facoltativi abbiano prodotto il testo previsto. Non correggete il problema aggiungendo virgolette casuali intorno ai numeri: tornate al tipo del campo e al contratto del modulo chiamante.

Per una nuova query, preparate casi limite: stringa vuota, apostrofo, Unicode, zero risultati, lunghezza massima consentita e identificatori minimo e massimo. La verifica deve coprire entrambi i file di dialetto supportati. Anche se un backend non viene usato localmente, la query non deve essere considerata pronta prima di una verifica sintattica o di integrazione nell'ambiente corrispondente.