מניעת SQL Injection ב-SQLite: שאילתות עם פרמטרים

SQL injection הוא באג של בניית מחרוזות

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

הנה דפוס השגיאה הקלאסי, בפסאודו-קוד שכל שפה יכולה לייצר:

-- אל תעשו את זה
query = "SELECT * FROM users WHERE name = '" + user_input + "'"

אם user_input הוא Ada, מקבלים חיפוש רגיל. אם user_input הוא ' OR 1=1 --, מקבלים:

SELECT * FROM users WHERE name = '' OR 1=1 --'

ה--- הופך את המרכאה שבסוף להערה, OR 1=1 מתאים לכל שורה, והתוקף הרגע שאב את כל טבלת המשתמשים שלכם. גרסאות גרועות יותר משרשרות ; ופקודה שנייה כדי למחוק טבלאות, להוציא נתונים או להכניס חשבון מנהל חדש.

הפגיעות לא נמצאת ב-SQLite. היא בקוד שבנה את המחרוזת.

שאילתות עם פרמטרים: הפתרון האמיתי

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

הריצו חיפוש שנראה פגיע בדרך הבטוחה:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, role TEXT);
INSERT INTO users (name, role) VALUES ('Ada', 'admin'), ('Boris', 'user'), ('Rosa', 'user');

-- דמיינו שהערך אחרי = הגיע ממשתמש. כשהוא קשור, הוא רק טקסט.
SELECT * FROM users WHERE name = 'Ada';

ב-shell של SQLite אתם ממש מקלידים את הערך, אבל בקוד האפליקציה המקבילה נראית כך (הדרייבר sqlite3 של Python):

# Python: עם פרמטרים, בטוח
cursor.execute("SELECT * FROM users WHERE name = ?", (user_input,))

מעבירים את ה-SQL ואת ה-tuple של הערכים כשני ארגומנטים נפרדים. הדרייבר שולח אותם ל-SQLite בנפרד. גם אם user_input הוא ' OR 1=1 --, SQLite מחפשת משתמש ששמו הוא ממש ' OR 1=1 -- ולא מוצאת אף אחד.

מה "בטוח" אומר כאן בפועל

הבטיחות היא לא התאמת תבניות ולא escape. היא מבנית. SQLite מהדרת את ה-statement לצורה פנימית עוד לפני שהיא רואה את הערך שלכם:

-- ל-statement המהודר יש חריץ, לא מחרוזת.
SELECT * FROM users WHERE name = ?
                                 ^
                                 חריץ למציין מקום

כשקושרים ערך, הוא נכנס לחריץ הזה כנתון מוקלד: TEXT, INTEGER, BLOB, מה שלא יהיה. SQLite אף פעם לא מנתחת אותו מחדש כ-SQL. אין תחביר שהתוקף יכול להזריק, כי המנתח כבר סיים את העבודה שלו.

זו הסיבה ששאילתות עם פרמטרים אמינות באופן ש-escape אף פעם לא יהיה. escape מנסה לנקות תווים מסוכנים ממחרוזת. קשירה אף פעם לא בונה את המחרוזת המסוכנת מלכתחילה.

אל תשתמשו בעיצוב מחרוזות

בכל שפה יש קיצור דרך מפתה, f-strings ב-Python, template literals ב-JavaScript, String.format ב-Java, וכל אחד מהם הוא מלכודת כשמדובר ב-SQL.

# לא: ה-f-string משבץ את הערך בתוך טקסט ה-SQL
cursor.execute(f"SELECT * FROM users WHERE name = '{user_input}'")

# לא: אותה בעיה, עיצוב עם %
cursor.execute("SELECT * FROM users WHERE name = '%s'" % user_input)

# כן: מציין מקום + ארגומנט של ערכים
cursor.execute("SELECT * FROM users WHERE name = ?", (user_input,))

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

הכלל הזה מכני: אם אתם מוצאים את עצמכם בונים מחרוזת SQL עם +, f-strings, format או template literals במקום שבו אמור להיות ערך, עצרו והשתמשו במציין מקום במקום זה.

כמה פרמטרים ומצייני מקום עם שם

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

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, total REAL, status TEXT);
INSERT INTO orders (customer, total, status) VALUES
  ('Ada', 120.0, 'paid'),
  ('Boris', 45.0, 'pending'),
  ('Rosa', 89.5, 'paid');

-- לפי מיקום: הסדר חשוב
SELECT * FROM orders WHERE customer = 'Ada' AND status = 'paid';

-- עם שם: הסדר לא חשוב
SELECT * FROM orders WHERE total > 50 AND status = 'paid';

בקוד אפליקציה הן מתורגמות ל:

# לפי מיקום
cursor.execute(
    "SELECT * FROM orders WHERE customer = ? AND status = ?",
    ("Ada", "paid"),
)

# עם שם: ברור יותר כשיש כמה פרמטרים
cursor.execute(
    "SELECT * FROM orders WHERE total > :min_total AND status = :status",
    {"min_total": 50, "status": "paid"},
)

פרמטרים עם שם מתאימים יותר לגדילה. אחרי שלושה או ארבעה ערכים, ?, ?, ?, ? הופך למשחק ניחושים, ואילו :customer, :total, :status, :created_at מתעד את עצמו.

מזהים דורשים גישה אחרת

פרמטרים קשורים עובדים רק ל_ערכים_: הדברים שבצד הימני של =, בתוך IN (...), ב-VALUES (...). הם לא עובדים לשמות טבלאות, לשמות עמודות או למילות מפתח של SQL כמו ASC/DESC.

-- זה לא עובד. מציין מקום לא יכול להחליף שם של עמודה.
SELECT * FROM users ORDER BY ? ASC

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

# גישת רשימת היתרים
ALLOWED_SORT_COLUMNS = {"name", "created_at", "role"}

if sort_column not in ALLOWED_SORT_COLUMNS:
    raise ValueError(f"עמודת מיון לא חוקית: {sort_column}")

query = f"SELECT * FROM users ORDER BY {sort_column} ASC"
cursor.execute(query)

המחרוזת שהמשתמש סיפק נבדקת מול קבוצה קבועה של ערכים שידוע שהם בטוחים לפני שהיא מתקרבת ל-SQL. ה-f-string מקובל כאן רק כי sort_column כבר לא יכול להיות שום דבר מלבד אחד משלושה שמות שכתובים בקוד.

ניסיון injection קונקרטי, מנוטרל

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

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
INSERT INTO users (name, email) VALUES
  ('Ada', 'ada@example.com'),
  ('Boris', 'boris@example.com'),
  ('Rosa', 'rosa@example.com');

-- התוקף שולח את זה בתור ה"שם" שלו:
--   ' OR 1=1 --
-- אפליקציה פגיעה הייתה בונה:
SELECT * FROM users WHERE name = '' OR 1=1 --';

-- אפליקציה עם פרמטרים הייתה שולחת:
SELECT * FROM users WHERE name = ''' OR 1=1 --';

הצורה הפגיעה מחזירה כל משתמש. הצורה עם הפרמטרים מחפשת משתמש ששמו הוא ממש ' OR 1=1 -- ולא מחזירה כלום. אותו קלט, תוצאה שונה לגמרי, כי במקרה השני הערך אף פעם לא הפך ל-SQL.

רשימת בדיקה קצרה

• השתמשו במצייני המקום ? או :name עבור כל ערך שמגיע מחוץ לקוד שלכם: קלט משתמש, גוף בקשות, משתני סביבה, כל דבר שלא כתבתם בתוך הקוד.
• לעולם אל תבנו SQL עם +, f-strings או format במקום שבו אמור להיות ערך.
• לשמות טבלאות או עמודות דינמיים, אמתו מול רשימת היתרים קבועה לפני השיבוץ בשאילתה.
• סמכו על הדרייבר. אל תכתבו פונקציית escape משלכם למרכאות. מנגנון הפרמטרים הקשורים ותיק יותר, נבדק יותר בשטח, ונכון.
• בדקו בסקירת קוד את השאילתות של הצוות עם שאלה אחת: האם קלט משתמש כלשהו משורשר לטקסט SQL? אם כן, תקנו.

הכניסו את ההרגל הזה לאצבעות, ו-SQL injection יפסיק להיות סוג של באג שצריך לחשוב עליו.

הבא: התחברות מאפליקציות

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