Prepared Statements ב-SQLite: prepare, bind, step, finalize

מה זה בעצם prepared statement

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

Prepared statement הוא מה שמקבלים כשעוצרים בשלב "הודר ל-bytecode" ושומרים את התוצאה. לתוכנית המהודרת יש חריצים, מצייני מקום, שבהם הערכים האמיתיים ימולאו מאוחר יותר. אפשר להריץ את אותה תוכנית מהודרת פעמים רבות עם ערכים שונים, ואפשר להריץ אותה בבטחה עם ערכים שהגיעו מקלט לא מהימן.

חשבו על זה כמו ההבדל בין לתת למישהו מתכון לקרוא בקול בכל פעם שהוא מבשל, לבין ללמד אותו את המתכון פעם אחת ורק לומר לו את המצרכים ביום הבישול.

מחזור החיים: prepare, bind, step, finalize

כל דרייבר של SQLite בכל שפה עוטף את אותן ארבע קריאות C API. כדאי להכיר את השמות גם אם לעולם לא תכתבו C, כי הודעות שגיאה ותיעוד משתמשים במונחים האלה:

1. sqlite3_prepare_v2: מהדרת מחרוזת SQL ל-handle של statement.
2. sqlite3_bind_*: ממלאת את הערכים של מצייני המקום (פונקציה אחת לכל טיפוס).
3. sqlite3_step: מריצה את התוכנית. עבור SELECT קוראים לה שוב ושוב כדי לעבור על השורות. עבור INSERT/UPDATE/DELETE קריאה אחת עושה את העבודה.
4. sqlite3_finalize: משחררת את התוכנית המהודרת כשמסיימים.

בין הרצות, sqlite3_reset מחזירה statement שהסתיים להתחלה, כך שאפשר לקשור מחדש ולהריץ שוב בלי להכין מחדש.

מצייני מקום ב-SQL

בתוך מחרוזת ה-SQL מסמנים כל מקום של ערך במציין מקום, במקום לשבץ את הערך. SQLite תומכת בכמה צורות:

-- אנונימי, לפי מיקום:
INSERT INTO users (name, email) VALUES (?, ?);

-- ממוספר:
INSERT INTO users (name, email) VALUES (?1, ?2);

-- עם שם:
INSERT INTO users (name, email) VALUES (:name, :email);
INSERT INTO users (name, email) VALUES (@name, @email);
INSERT INTO users (name, email) VALUES ($name, $email);

? הוא הנפוץ ביותר בקוד ברמת הדרייבר. מצייני מקום עם שם (:name) קריאים יותר כשיש כמה פרמטרים או כשאותו ערך מופיע יותר מפעם אחת. בחרו סגנון אחד לכל פרויקט והיצמדו אליו.

הדבר שאסור לעשות הוא לבנות את השאילתה בשרשור מחרוזות:

-- אל תעשו את זה:
"INSERT INTO users (name) VALUES ('" + user_input + "')"

זו הדרך ל-SQL injection, והיא גם מבטלת את השימוש החוזר ב-bytecode שתקראו עליו עוד רגע.

דוגמה מעשית ב-SQL

כדי לראות את המנגנון בלי שפה מארחת, הנה המקבילה ל-prepare/bind/step עם תכונות SQL בלבד ש-SQLite נותנת. צרו טבלה והכניסו שורה עם מציין מקום בסגנון פרמטר שמולא בערך מילולי:

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

INSERT INTO users (name, email) VALUES ('Ada', 'ada@example.com');
INSERT INTO users (name, email) VALUES ('Boris', 'boris@example.com');

SELECT id, name, email FROM users ORDER BY id;

באפליקציה אמיתית לא הייתם כותבים את הערכים בתוך השאילתה: הייתם עושים prepare ל-INSERT פעם אחת עם מצייני המקום ?, ?, ואז bind לזוג השם והאימייל של כל משתמש ו-step. ה-bytecode המהודר זהה בכל קריאה, ורק הערכים הקשורים משתנים.

שימוש חוזר ב-statement (הרווח בביצועים)

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

-- הוכן פעם אחת:
INSERT INTO users (name, email) VALUES (?, ?);

-- ואז, בלולאה:
--   bind(1, name)
--   bind(2, email)
--   step()
--   reset()

ההכנה מנתחת ומהדרת את ה-SQL פעם אחת. כל איטרציה רק מריצה bytecode ומעתיקה ערכים לחריצים. בהכנסות בכמויות גדולות (חשבו על ייבוא של 100,000 שורות) זה מהיר בהרבה מהרצה של 100,000 פקודות שכל אחת נותחה בנפרד, לעתים קרובות בסדר גודל, במיוחד כשהכול עטוף בטרנזקציה אחת.

מלכודת נפוצה: אנשים כותבים לולאה וקוראים ל-prepare בתוך הלולאה. זה זורק את כל התועלת. הכינו מחוץ ללולאה, וקשרו והריצו בתוכה.

למה זו הדרך הבטוחה

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

השוו:

-- פגיע. אם user_input הוא:  '); DROP TABLE users;--
-- השאילתה הופכת להרסנית.
"SELECT * FROM users WHERE name = '" + user_input + "'"

-- בטוח. user_input נקשר כערך TEXT ותמיד
-- מושווה כמחרוזת בלבד, לא משנה מה הוא מכיל.
SELECT * FROM users WHERE name = ?;

הצורה השנייה בטוחה גם אם user_input הוא '); DROP TABLE users;--. SQLite תחפש בצייתנות משתמש ששמו הוא בדיוק המחרוזת (המוזרה) הזו, לא תמצא אף אחד, ותחזיר אפס שורות. שום דבר במבנה השאילתה לא יכול לזוז בגלל הערך.

נעמיק ב-injection בעמוד מאוחר יותר, אבל השורה התחתונה: prepared statements הם לא סתם אחת ההגנות מפני SQL injection, הם ההגנה.

פקודות שמחזירות שורות

עבור SELECT, step מחזירה שורה אחת בכל פעם. הדרייבר בדרך כלל מריץ לולאה עד שהיא מחזירה "done":

CREATE TABLE products (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products (name, price) VALUES
    ('Notebook', 4.50),
    ('Pen',      1.20),
    ('Eraser',   0.80);

SELECT name, price FROM products WHERE price < 2.00 ORDER BY price;

בקוד אפליקציה, הדרייבר היה עושה prepare ל-SELECT הזה עם ? במקום 2.00, קושר את ערך הסף, וקורא ל-step בלולאה, שורה אחת בכל קריאה. אחרי השורה האחרונה step מדווחת על סיום, והדרייבר עושה reset ל-statement (כדי להריץ אותו שוב עם סף חדש) או finalize.

אל תשכחו לעשות finalize

Prepared statement הוא הקצאה קטנה בתוך SQLite. דליפה שלהם אוכלת זיכרון, וחשוב מזה, מחזיקה נעילה פנימית על מסד הנתונים שיכולה לחסום כותבים אחרים. כל דרייבר נותן דרך לנקות אוטומטית, context managers ב-Python, בלוקי using ב-C#, RAII ב-C++, וכדאי להשתמש בהם:

• sqlite3 של Python עושה finalize כשה-cursor נאסף על ידי ה-garbage collector, אבל cursor.close() מפורש נקי יותר.
• better-sqlite3 (Node) עושה finalize כשה-Statement נאסף על ידי ה-garbage collector, ו-prepared statements ארוכי חיים הם בסדר.
• ב-C גולמי אתם קוראים ל-sqlite3_finalize בעצמכם. לשכוח את זה הוא באג אמיתי.

כלל האצבע: אם הכנתם אותו, משהו צריך לעשות לו finalize.

מתי אולי לא תצטרכו כזה בעצמכם

רק לעתים רחוקות תקראו ל-sqlite3_prepare_v2 ישירות. דרייברים ברמה גבוהה הופכים את connection.execute("SELECT ... WHERE id = ?", (42,)) ל-prepare/bind/step/finalize בשבילכם. הסיבה להבין את מחזור החיים היא:

• תזהו מה קורה כשתראו שגיאות כמו "statement is busy" או "cannot operate on a finalized statement".
• תדעו לשמור במטמון prepared statements ארוכי חיים כשאתם מכניסים נתונים בלולאה הדוקה.
• תכתבו שאילתות עם פרמטרים באופן אינסטינקטיבי, גם כששרשור מחרוזות נראה מפתה.

ORM ובוני שאילתות לוקחים את זה עוד יותר רחוק. הם בונים את ה-SQL, מנהלים את ה-prepared statements ומחזירים לכם תוצאות מוקלדות. מתחת לפני השטח, אלה אותן ארבע קריאות.

הבא: קשירת פרמטרים

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