Dokumentatsioon

SQLMacros praktiline süntaks andmebaasipäringute juhtimiseks

SQLMacrosi otstarve ja piirid

SQLMacros on Sapphire I.C.D.S.-i SQL-i mallikiht. Tavalist päringut hoitakse kataloogis SQL/<Module>/<dialect>/, see saab ettevalmistatud väärtustabeli, koostab lõpliku SQL-teksti ja edastab selle aktiivsele andmebaasidraiverile. SQLMacros ei ava ühendust, ei vali backend'i, ei alusta tehingut ega otsusta, millist päringut tohib käivitada. Need kohustused jäävad moodulile ja DB-kihile.

Hoidke MySQL-i ja PostgreSQL-i jaoks eraldi faile isegi siis, kui päringud sarnanevad. Identifikaatorite jutumärgid, kuupäevafunktsioonid, tüübiteisendused, upsert ja agregaadid erinevad. Katse peita kaks dialekti ühte keerukasse malli on tavaliselt halvem kui kaks lühikest kontrollitavat faili.

Table.Data põhiline asendus

Järgmistes näidetes kasutatakse illustratiivset skeemi tabelitega example_articles ja example_article_notes; need ei ole Sapphire I.C.D.S.-i päris tabelid.

Peamine sisendandmete allikas on Table.Data("field"). Ühe rea jaoks on kursor tavaliselt juba kutsuva poole poolt ette valmistatud. Näide arvparameetritega valikust:

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

Nii võib väljastada ainult väärtusi, mille moodul on loonud täisarvudena ja mille vahemikku ta on kontrollinud. Kasutaja string "10; DELETE ..." ei tohi arvuväljale jõuda. Makro ei muuda suvalist teksti turvaliseks arvuks.

Stringiliteraalid ja escape

String asetatakse SQL-i jutumärkidesse ning väärtus lastakse läbi aktiivse DB escape provider'i:

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

escape(...) ja ühilduv variant Escape(...) põgenevad sisu valitud draiveri jaoks. Stringiliteraali ümber olevad jutumärgid jäävad SQL-malli osaks. Ärge kasutage DB escape'i asemel HTML escaping'ut, apostroofide käsitsi asendamist ega URL encoding'ut.

Väärtuse põgenemine ei luba seda identifikaatorina kasutada. Ärge kunagi kirjutage kasutajapäringust ORDER BY {{ Table.Data("sort") }}, FROM {{ ... }} ega dünaamilist veerunime. Lubatud sortimis- ja veeruvariandid valitakse moodulis allowlist'i järgi, mille järel kutsutakse konkreetne staatiline mall või edastatakse eraldi lepingu alusel ette teada turvaline fragment.

Tingimused mallis

Juhtkonstruktsioonid kirjutatakse märkide {% ja %} vahele. Need sobivad väikese arvu valikuliste filtrite jaoks:

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

Veenduge, et iga tingimuste kombinatsioon jätab päringu süntaktiliselt korrektseks. Mugav on alustada kohustuslikust WHERE-osast ja lisada valikulised osad AND-iga. Kui harusid on palju, on parem luua mitu loogilist päringut: neid on lihtsam testida ja turvalisem hooldada.

Mallimootori kommentaari kuju on {# selgitus #} ning see ei jõua lõplikku SQL-i. Tavaline -- SQL comment jääb päringusse. Ärge hoidke kommentaarides saladusi, tegelikke isikuandmeid ega ajutisi õiguste eiramise võtteid.

INSERT ja UPDATE

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

Siin peab article_id olema usaldatud struktureeritud tabelist pärinev positiivne arv ning note escape'i läbinud string. Omaniku, kasutajaõiguste, artikli olemasolu ja märkme lubatud pikkuse kontrollimine toimub enne päringut. SQLMacros ei lisa seda poliitikat automaatselt.

Update/delete-toimingute puhul lisage alati täpne olemi piirang ja vajaduse korral omanik või layout. Massmuudatus ilma WHERE-tingimuseta ei muutu vastuvõetavaks seetõttu, et see on mallis. Konkureeriva redigeerimise jaoks kasutage moodulis kehtivat revision/transaction lepingut, mitte omatehtud stringi.

Pager: andmepäring ja count

Lehekülgjaotusega loendil on tavaliselt kaks malli: põhipäring LIMIT/OFFSET-iga ja fail *.count.sql. Filtritingimused peavad neis kattuma, muidu näitab Pager vale lehekülgede arvu. Sobiv kontrollijärjekord on:

kopeerige count-päringusse ainult filtriplokk;
kontrollige tühja otsingut;
kontrollige apostroofi ja protsendimärkidega stringi;
kontrollige limit 1, viimast lehekülge ja tulemusevälise offset'i;
võrrelge põhipäringu ridade arvu count-väärtusega sama sisendi korral.

Environment, POST ja Cookie

SQLMacros võimaldab tehniliselt kasutada keskkonna ja päringu väärtusi selliste kutsete kaudu nagu Env.Get(...), Post.Get(...) ja Cookie.Get(...). Tavalises rakenduse SQL-loogikas ärge pöörduge nende poole otse. Moodul peab lugema HTTP-sisendi, kontrollima tüüpi, normaliseerima väärtuse, rakendama õigusi ja panema tulemuse Table-isse. Nii on SQL transpordist sõltumatu ja sobib CGI, FastCGI, AJAX-i, teenuse või testi jaoks.

Milline mall on turvaline

SQL-i struktuur, tabelid, veerud ja operaatorid on kirjas staatiliselt.
Iga string läbib stringiliteraali sees escape(Table.Data(...)).
Arvud, lipud, limit ja offset tüübistatakse ning piiratakse enne renderdamist.
Mall asub õige mooduli ja dialect'i kataloogis.
Päringu valik ja käivitamise luba jäävad moodulile.
Testid ja diagnostika kinnitavad, et edukas tulemus ei sisalda avaldamata {{ ... }} ega {% ... %} avaldisi.
Praegune runtime võib SQLMacrosi vea korral tagastada malli algse teksti; kutsuv DB-keskkond ei taga eraldi fail-closed blokeeringut. Ärge käsitage renderdamist security boundary'na: sellised vead tuleb testide ja logidega enne production'it avastada.

Kontrollige SELECT-päringuid testandmetega ja muutmispäringuid kontrollitud tehingus või isoleeritud andmebaasis. Ärge testige uut DELETE/UPDATE-päringut production'is. SQLMacros vähendab dubleerimist ja tsentraliseerib põgenemise, kuid turvalisus tekib ainult koos tüübitud sisendi, serveripoolse autoriseerimise ja staatilise päringustruktuuriga.

Renderdustulemuse diagnostika

Vea korral fikseerige esmalt päringu loogiline nimi, dialect ja sisendväljade kogum ilma salajaste väärtusteta. Seejärel hankige turvalises diagnostikakeskkonnas lõplik SQL ning veenduge, et kõik makrod avaldusid, stringide jutumärgid on tasakaalus ja valikulised harud andsid oodatud teksti. Ärge parandage probleemi juhuslike jutumärkide lisamisega arvude ümber: pöörduge tagasi välja tüübi ja kutsuva mooduli lepingu juurde.

Valmistage uue päringu jaoks ette piirjuhud: tühi string, apostroof, Unicode, null tulemust, suurim lubatud pikkus ning minimaalne ja maksimaalne identifikaator. Kontroll peab hõlmama mõlemat toetatud dialect-faili. Kui üht backend'i kohapeal ei kasutata, ei tohi päringut siiski valmis lugeda enne süntaktilist või integratsioonikontrolli vastavas keskkonnas.