תיעוד

תחביר מעשי של SQLMacros לניהול שאילתות מסד נתונים

המטרה והגבולות של SQLMacros

SQLMacros הוא שכבת תבניות עבור SQL ב-Sapphire I.C.D.S. שאילתה רגילה נשמרת בתיקייה SQL/<Module>/<dialect>/, מקבלת טבלת ערכים מוכנה, יוצרת את טקסט ה-SQL הסופי ומעבירה אותו למנהל ההתקן הפעיל של מסד הנתונים. SQLMacros אינו פותח חיבור, אינו בוחר backend, אינו מתחיל עסקה ואינו מחליט איזו שאילתה מותר להריץ. האחריות הזאת נשארת בידי המודול ושכבת ה-DB.

שמרו קבצים נפרדים עבור MySQL ועבור PostgreSQL גם כאשר השאילתות דומות. מירכאות למזהים, פונקציות תאריך, המרות טיפוסים, upsert ופעולות צבירה שונים ביניהם. ניסיון להסתיר שני ניבים בתבנית מורכבת אחת בדרך כלל גרוע יותר משני קבצים קצרים שאפשר לבדוק.

הצבה בסיסית באמצעות Table.Data

הדוגמאות הבאות משתמשות בסכמה להמחשה עם הטבלאות example_articles ו-example_article_notes; אלה אינן טבלאות אמיתיות של Sapphire I.C.D.S.

מקור נתוני הקלט העיקרי הוא Table.Data("field"). עבור שורה אחת, הסמן בדרך כלל כבר הוכן בצד הקורא. דוגמה לבחירה עם פרמטרים מספריים:

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

כך מותר להציג רק ערכים שהמודול יצר כמספרים שלמים ובדק את הטווח שלהם. מחרוזת משתמש "10; DELETE ..." אינה אמורה להגיע לשדה מספרי. המאקרו אינו הופך טקסט שרירותי למספר בטוח.

ליטרלים של מחרוזות ו-escape

מחרוזת מוכנסת למירכאות SQL והערך עובר דרך ה-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(...) והגרסה התואמת Escape(...) מבצעים escaping לתוכן עבור מנהל ההתקן שנבחר. המירכאות סביב ליטרל המחרוזת נשארות חלק מתבנית ה-SQL. אל תשתמשו ב-HTML escaping, בהחלפה ידנית של גרשים או ב-URL encoding במקום DB escape.

escaping של ערך אינו מתיר להשתמש בו כמזהה. לעולם אל תכתבו ORDER BY {{ Table.Data("sort") }}, FROM {{ ... }} או שם עמודה דינמי מתוך בקשת משתמש. אפשרויות המיון והעמודות המותרות נבחרות במודול לפי allowlist, ולאחר מכן נקראת תבנית סטטית מסוימת או מועבר מקטע בטוח וידוע מראש לפי חוזה נפרד.

תנאים בתבנית

מבני בקרה נכתבים בין {% לבין %}. הם שימושיים עבור מספר קטן של מסננים אופציונליים:

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

ודאו שכל שילוב תנאים משאיר שאילתה תקינה מבחינה תחבירית. נוח להתחיל ב-WHERE חובה ולהוסיף חלקים אופציונליים באמצעות AND. אם יש ענפים רבים, עדיף ליצור כמה שאילתות לוגיות: קל יותר לבדוק אותן ובטוח יותר לתחזק אותן.

הערת מנוע התבנית נכתבת בצורה {# הסבר #} ואינה נכנסת ל-SQL הסופי. הערת -- SQL comment רגילה נשארת בשאילתה. אל תשמרו בהערות סודות, נתונים אישיים אמיתיים או מעקפים זמניים של הרשאות.

INSERT ו-UPDATE

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

כאן article_id חייב להיות מספר חיובי מטבלה מובנית ומהימנה, ו-note חייב להיות מחרוזת שעברה escape. בדיקת הבעלים, הרשאות המשתמש, קיום המאמר והאורך המותר של ההערה מתבצעת לפני השאילתה. SQLMacros אינו מוסיף את המדיניות הזאת אוטומטית.

עבור update/delete כללו תמיד הגבלה מדויקת של הישות, ובמקום הנדרש גם בעלים או layout. שינוי המוני ללא WHERE אינו הופך לתקין מפני שהוא נשמר בתבנית. לעריכה מקבילית השתמשו ב-revision/transaction contract המקובל במודול ולא במחרוזת מאולתרת.

Pager: שאילתת נתונים ו-count

לרשימה עם עימוד יש בדרך כלל שתי תבניות: השאילתה הראשית עם LIMIT/OFFSET וקובץ *.count.sql. תנאי הסינון חייבים להיות זהים בשניהם, אחרת Pager יציג מספר עמודים שגוי. סדר בדיקה נוח הוא:

העתיקו לשאילתת count רק את מקטע המסננים;
בדקו חיפוש ריק;
בדקו מחרוזת עם גרש וסימני אחוז;
בדקו limit 1, את העמוד האחרון ו-offset מחוץ לתוצאה;
השוו את מספר השורות בשאילתה הראשית ל-count עבור אותו קלט.

Environment, POST ו-Cookie

SQLMacros מספק מבחינה טכנית ערכי סביבה ובקשה באמצעות קריאות כגון Env.Get(...), Post.Get(...) ו-Cookie.Get(...). בלוגיקת SQL יישומית רגילה אין לפנות אליהם ישירות. המודול צריך לקרוא את קלט ה-HTTP, לבדוק את הטיפוס, לנרמל את הערך, להחיל הרשאות ולהכניס את התוצאה ל-Table. כך ה-SQL אינו תלוי בתעבורה ומתאים ל-CGI, ‏FastCGI, ‏AJAX, שירות או בדיקה.

מהי תבנית בטוחה

מבנה ה-SQL, הטבלאות, העמודות והאופרטורים כתובים באופן סטטי.
כל מחרוזת עוברת דרך escape(Table.Data(...)) בתוך ליטרל מחרוזת.
מספרים, דגלים, limit ו-offset מקבלים טיפוס ומוגבלים לפני הרינדור.
התבנית נמצאת בתיקיית המודול וה-dialect הנכונים.
בחירת השאילתה וההרשאה להריץ אותה נשארות בידי המודול.
בדיקות ואבחון מאשרים שתוצאה מוצלחת אינה מכילה ביטויי {{ ... }} או {% ... %} שלא נפתחו.
במקרה של כשל ב-SQLMacros, סביבת הריצה הנוכחית עשויה להחזיר את הטקסט המקורי של התבנית; סביבת ה-DB הקוראת אינה מבטיחה חסימת fail-closed נפרדת. אין לראות ברינדור security boundary: יש לאתר שגיאות כאלה באמצעות בדיקות ויומנים לפני production.

בדקו SELECT על נתוני בדיקה, ושאילתות שינוי בתוך עסקה מבוקרת או במסד נתונים מבודד. אל תבדקו DELETE/UPDATE חדש ב-production. SQLMacros מצמצם כפילות ומרכז escaping, אך בטיחות נוצרת רק בשילוב קלט בעל טיפוסים, הרשאה בצד השרת ומבנה שאילתה סטטי.

אבחון תוצאת הרינדור

בעת שגיאה, תעדו תחילה את השם הלוגי של השאילתה, את ה-dialect ואת קבוצת שדות הקלט בלי ערכים סודיים. לאחר מכן קבלו את ה-SQL הסופי בסביבת אבחון בטוחה וודאו שכל המאקרואים נפתחו, שמירכאות המחרוזות מאוזנות ושהענפים האופציונליים יצרו את הטקסט הצפוי. אל תפתרו את הבעיה באמצעות הוספת מירכאות אקראיות סביב מספרים: חזרו לטיפוס השדה ולחוזה של המודול הקורא.

הכינו מקרי גבול עבור שאילתה חדשה: מחרוזת ריקה, גרש, Unicode, אפס תוצאות, האורך המרבי המותר והמזהה המזערי והמרבי. הבדיקה צריכה לכסות את שני קובצי ה-dialect הנתמכים. גם אם backend אחד אינו בשימוש מקומי, אין לראות בשאילתה שאילתה מוכנה לפני בדיקת תחביר או אינטגרציה בסביבה המתאימה.