קשירת פרמטרים ב-SQLite: ?, :name וערכים בטוחים

קשירה היא הדרך שבה ערכים נכנסים ל-Prepared Statement

prepared statement הוא SQL עם חורים. קשירה היא הפעולה של מילוי החורים האלה בערכים: בבטחה, אחד אחד, דרך ה-API של הדרייבר, ולא על ידי הדבקת מחרוזות זו לזו.

המבנה תמיד נראה אותו דבר: כותבים את ה-SQL עם מצייני מקום, ואז מעבירים את הערכים בנפרד.

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

-- ה-SQL שמסד הנתונים רואה:
INSERT INTO users (name, email) VALUES (?, ?);

ב-CLI אי אפשר באמת להדגים קשירה (ל-shell אין קוד אפליקציה מחובר), אבל ה-SQL שלמעלה הוא בדיוק מה שהאפליקציה שלכם שולחת. סימני ה-? הם מצייני מקום. הדרייבר שלכם, sqlite3 ב-Python, better-sqlite3 ב-Node, rusqlite ב-Rust, ממלא אותם דרך קריאת bind נפרדת.

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

מצייני מקום לפי מיקום: ?

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

INSERT INTO users (name, email) VALUES (?, ?);

ב-Python זה:

cursor.execute(
    "INSERT INTO users (name, email) VALUES (?, ?)",
    ("Rosa", "rosa@example.com"),
)

ה-? הראשון מקבל את "Rosa", והשני מקבל את "rosa@example.com". העברתם מעט מדי ערכים או יותר מדי? הדרייבר יזרוק שגיאה לפני שהפקודה רצה.

אפשר גם למספר אותם במפורש עם ?1, ?2, ?3, וזה שימושי כשאותו ערך מופיע יותר מפעם אחת:

SELECT ?1 AS greeting, ?1 AS still_the_same;

?1 משתמש שוב בערך הקשור הראשון. בלי מספור, הייתם צריכים לקשור את אותו ערך פעמיים.

מצייני מקום עם שם: :name

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

INSERT INTO users (name, email)
VALUES (:name, :email);

ב-Python:

cursor.execute(
    "INSERT INTO users (name, email) VALUES (:name, :email)",
    {"name": "Boris", "email": "boris@example.com"},
)

סדר המפתחות במילון לא משנה: רק השמות משנים. SQLite מקבלת גם @name ו-$name כקידומות חלופיות; כולן מתנהגות אותו דבר. :name היא ללא ספק הנפוצה ביותר.

פרמטרים עם שם משתלמים ברגע שיש לכם UPDATE עם חמש עמודות, או שאילתה שמשתמשת באותו ערך ב-WHERE וב-RETURNING.

קשירת NULL

הדרך הנכונה להכניס NULL היא להעביר את ערך ה-null של השפה שלכם דרך ה-API לקשירה. הדרייבר מטפל בתרגום:

INSERT INTO users (name, email) VALUES (?, ?);
-- Bind: ("Cyrus", None)   ב-Python
-- Bind: ["Cyrus", null]   ב-Node

SELECT id, name, email FROM users;

None, null, nil, איך שהשפה שלכם לא קוראת לזה: הדרייבר הופך את זה ל-NULL אמיתי של SQL. אל תקשרו את המחרוזת "NULL"; זה שומר את הטקסט "NULL" בן ארבעת התווים. ואל תשתלו את המילה NULL בטקסט ה-SQL: זה מבטל את הקשירה לגמרי.

אותו כלל חל על מספרים, blobs ותאריכים: העבירו את הערך המקורי, ותנו לדרייבר לקשור אותו.

שימוש חוזר בפקודה עם ערכים שונים

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

INSERT INTO users (name, email) VALUES (?, ?);
-- קשירת ("Ada",   "ada@example.com")    -> הרצה
-- קשירת ("Boris", "boris@example.com")  -> הרצה
-- קשירת ("Cyrus", NULL)                 -> הרצה

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

רוב הדרייברים עוטפים את זה ב-executemany (Python) או בלולאת .run() (Node). כך או כך, מה שאתם חוסכים הוא עלות הפענוח: קטנה לכל פקודה, אבל אמיתית כשאתם מכניסים אלפי שורות.

אל תשלבו סגנונות בפקודה אחת

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

-- חוקי, אבל מלכודת:
INSERT INTO users (name, email) VALUES (?, :email);

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

מלכודת נפוצה: קשירה היא לא עיצוב מחרוזות

כל הרעיון של קשירה הוא שהערכים לא עוברים דרך פענוח ה-SQL. השוו בין שתי שורות ה-Python האלה:

# שגוי: עיצוב מחרוזות:
cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")

# נכון: קשירת פרמטרים:
cursor.execute("SELECT * FROM users WHERE name = ?", (name,))

השורה הראשונה בונה SQL בשרשור. אם name הוא "'; DROP TABLE users; --", מסד הנתונים יפענח ויריץ בשמחה את הפקודה שהוזרקה. השורה השנייה שולחת את ה-SQL ואת הערך בערוצים נפרדים: הערך נקשר כמחרוזת, נקודה, לא משנה אילו תווים יש בו. זו הסיבה שכל מדריך אומר לכם לקשור: זה לא עניין של סגנון, זה עניין של מה שהמפענח רואה.

נעמיק בצד של ההזרקה בעמוד הבא.

עוד מלכודת: אי אפשר לקשור מזהים

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

-- זה לא עושה את מה שאתם רוצים:
SELECT * FROM ? WHERE id = ?;
-- ה-? הראשון נקשר כמחרוזת מילולית, לא כשם טבלה.

אם אתם באמת צריכים שם טבלה או עמודה דינמי (נדיר בקוד של אפליקציה), בדקו אותו מול רשימת ערכים מותרים ושרשרו אותו ל-SQL בעצמכם, אף פעם לא ישירות מקלט של המשתמש. לכל השאר, קשרו.

דוגמה מלאה

כשמחברים את כל החלקים, הנה טבלת users קטנה שכותבים אליה וקוראים ממנה רק דרך קשירות:

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

-- האפליקציה הייתה קושרת את הערכים האלה; כאן הם מוצגים כערכים מילוליים:
INSERT INTO users (name, email)        VALUES ('Ada',   'ada@example.com');
INSERT INTO users (name, email)        VALUES ('Boris', 'boris@example.com');
INSERT INTO users (name, email, active) VALUES ('Cyrus', NULL, 0);

-- שאילתה אמיתית שדרייבר היה מריץ, עם :name ו-:active קשורים:
SELECT id, name, email
FROM users
WHERE active = :active AND name LIKE :name
ORDER BY id;

בקוד אמיתי, פקודות ה-INSERT וה-SELECT היו כולן משתמשות במצייני מקום. ל-CLI פשוט אין אפליקציה לקשור ממנה, ולכן הערכים המילוליים מחליפים את מה שהקשירה מפיקה.

הבא בתור: מניעת SQL Injection

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