kodkod | מבואות קודקוד
Docs
kodkod | מבואות קודקוד
מסדי נתונים ו-MySQL

הרצת Query מתוך Python: execute, פרמטרים, commit ו-fetch

מטרות השיעור
  • לתאר את המסלול Python → Connection → Query → Database → Result → Python
  • להריץ INSERT ו-SELECT מ-Python עם execute ופרמטרים (%s)
  • להסביר מה זה SQL Injection ולמה לעולם לא בונים SQL בשרשור מחרוזות
  • לבצע commit אחרי שינוי, ולקבל את ה-id החדש עם lastrowid

מה נלמד?

עד עכשיו כתבנו SQL “ידני” ב-Database Client. עכשיו מחברים אותו לקוד, לפי המסלול שהסילבוס מדגיש:

Python  →  Connection  →  Query  →  Database  →  Result  →  Python
(values)   (open)         (SQL +    (runs it)     (rows)     (tuples,
                           params)                            ints, strs)
  • execute עם פרמטרים (%s).
  • SQL Injection: הסכנה, ולמה פרמטרים פותרים אותה.
  • commit: שמירת שינויים.
  • lastrowid: ה-id שהמסד נתן לשורה החדשה.
  • fetchone / fetchall על נתונים אמיתיים.

למה צריך את זה?

ב-API, הערכים מגיעים מהמשתמש: שם, גיל ואימייל מתוך Body של POST. הקוד צריך להכניס אותם לשאילתה בבטחה, לשמור, ולהחזיר את מה שנשמר, כולל ה-id החדש (201 Created + הסטודנט). כל זה קורה בשיעור הזה, בקוד אמיתי.

הסבר מהבסיס

execute(sql, params)

cursor.execute(
    "INSERT INTO students (name, age, email) VALUES (%s, %s, %s)",
    ("Dana", 24, "dana@mail.com"),
)
  • ב-SQL כותבים %s במקום כל ערך (Placeholder, “שומר מקום”). ב-MySQL זה תמיד %s — לכל טיפוס, גם למספרים.
  • הערכים עוברים בנפרד, כ-tuple, באותו סדר של ה-Placeholders.
  • ה-Driver (mysql.connector) מכניס אותם למקום בבטחה: מחרוזות כמחרוזות, ומספרים כמספרים. אין צורך בגרש, ואין צורך בהמרה.

SQL Injection — למה לא לבנות SQL בשרשור

נראה “פשוט” לבנות את השאילתה בעצמנו:

name = input("Student name to search: ")
sql = "SELECT id, name FROM students WHERE name = '" + name + "'"

אם המשתמש מקליד Dana, מתקבלת השאילתה ... WHERE name = 'Dana'. בסדר. אבל אם הוא מקליד:

x' OR '1'='1

מתקבלת השאילתה:

SELECT id, name FROM students WHERE name = 'x' OR '1'='1'

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

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

איך בדיוק %s מגן?

בשרשור, ההתקפה עובדת בגלל גרש אחד: הגרש שהמשתמש הקליד אחרי x סוגר את הטקסט שלנו, וכל מה שבא אחריו (OR '1'='1') כבר נקרא כחלק מהפקודה.

עם %s, ה-Driver מכין את הערך לפני ששולח אותו: הוא מוסיף לוכסן הפוך (\) לפני כל גרש בקלט. לוכסן לפני גרש אומר ל-MySQL: “זה גרש רגיל בתוך הטקסט, לא סוף הטקסט”.

שלבמה רואים
המשתמש מקלידx' OR '1'='1
ה-Driver מוסיף \ לפני כל גרשx\' OR \'1\'=\'1
מה ש-MySQL מקבלSELECT name FROM students WHERE name = 'x\' OR \'1\'=\'1'
איך MySQL קוראטקסט אחד: שם ארוך ומוזר — x' OR '1'='1

הגרש של המשתמש כבר לא יכול לסגור את הטקסט, ולכן כל הקלט נשאר בפנים כשם אחד. אין OR שהפך לחלק מהפקודה, ו-MySQL מחפש סטודנט בשם הזה — ולא מוצא ([]).

ולמה לא לעשות את זה לבד? כי קל לפספס מקרה: גרש כפול, לוכסן בקלט, קידוד מיוחד. ה-Driver יודע לטפל בכולם, בכל פעם — לכן תמיד %s.

כלל ברזל

לעולם לא בונים SQL עם + מערכים שמגיעים מבחוץ. תמיד %s ופרמטרים. אין יוצאים מן הכלל.

אנלוגיה: טופס עם תיבות

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

commit — לשמור באמת

פקודות שמשנות נתונים (INSERT, UPDATE, DELETE) נכנסות קודם ל-Transaction (עסקה) פתוחה. השינוי נראה בתוך החיבור הנוכחי, אבל לא נשמר סופית עד שקוראים ל-connection.commit(). אם סוגרים את החיבור בלי commit, השינויים נזרקים.

  • SELECT לא צריך commit, כי הוא לא משנה כלום.
  • CREATE TABLE נשמר ב-MySQL מיד, גם בלי commit.

lastrowid ו-rowcount

אחרי execute, ל-Cursor יש מידע על מה שקרה:

Attributeמה הוא מכילשימוש
cursor.lastrowidה-id שהמסד נתן לשורה האחרונה שהוכנסהלהחזיר את הפריט החדש מ-POST
cursor.rowcountכמה שורות השתנו ב-INSERT/UPDATE/DELETEלגלות ש"לא נמצא" ב-UPDATE/DELETE (בהמשך הנושא)

תחביר

connection = get_connection()
try:
    cursor = connection.cursor()

    # change data: execute + commit
    cursor.execute("INSERT INTO t (a, b) VALUES (%s, %s)", (value_a, value_b))
    connection.commit()
    new_id = cursor.lastrowid

    # read data: execute + fetch
    cursor.execute("SELECT a, b FROM t WHERE b > %s", (10,))
    rows = cursor.fetchall()
    cursor.execute("SELECT a, b FROM t WHERE id = %s", (new_id,))
    row = cursor.fetchone()
finally:
    connection.close()

שימו לב ל-(10,): tuple של איבר אחד חייב פסיק (נושא Python!). (10) הוא סתם המספר 10, ו-execute תיכשל.

דוגמת קוד ראשונה

week-4/school-db/students_demo.py:

import mysql.connector


def get_connection():
    return mysql.connector.connect(
        host="localhost", user="root", password="kodkod", database="school")


connection = get_connection()
try:
    cursor = connection.cursor()
    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS students (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            age INT NOT NULL,
            email VARCHAR(100) NOT NULL UNIQUE
        )
        """
    )

    cursor.execute(
        "INSERT INTO students (name, age, email) VALUES (%s, %s, %s)",
        ("Dana", 24, "dana@mail.com"),
    )
    connection.commit()
    print("Inserted student with id", cursor.lastrowid)

    cursor.execute("SELECT id, name, age, email FROM students")
    print("All rows:", cursor.fetchall())
finally:
    connection.close()

פלט (הרצה ראשונה, על טבלה ריקה):

Inserted student with id 1
All rows: [(1, 'Dana', 24, 'dana@mail.com')]

פירוק הקוד שורה אחר שורה

קודשלב במסלול
get_connection()Connection: פתיחה.
cursor = connection.cursor()ה-Cursor שדרכו נריץ את כל השאילתות.
"""..."""מחרוזת מרובת שורות (שלוש מירכאות). נוחה ל-SQL ארוך.
execute(CREATE TABLE ...)יצירת הטבלה, אם היא לא קיימת.
execute("INSERT ... VALUES (%s, %s, %s)", ("Dana", 24, "dana@mail.com"))Query עם 3 Placeholders ו-3 ערכים. Python → Database.
connection.commit()שמירה. בלעדיה, הסטודנטית הייתה נעלמת בסגירה.
cursor.lastrowidה-id שהמסד נתן: 1.
execute("SELECT ...") + fetchall()Result → Python: רשימה של tuples.
finally: connection.close()סגירה, תמיד.

הריצו שוב:

Traceback (most recent call last):
  ...
mysql.connector.errors.IntegrityError: 1062 (23000): Duplicate entry 'dana@mail.com' for key 'students.email'

בהרצה השנייה, ה-INSERT של דנה נכשל, כי האימייל כבר קיים, והמסד אכף את UNIQUE. (ולמרות השגיאה, החיבור נסגר, בזכות finally.) בהמשך הנושא נטפל בשגיאה הזו כמו שצריך. בינתיים, כדי להתחיל מחדש: ב-Database Client מריצים DROP TABLE students;.

דוגמה נוספת

הוספה של כמה סטודנטים, חיפושים עם פרמטרים, והדגמה של SQL Injection.

import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
try:
    cursor = connection.cursor()
    cursor.execute("DROP TABLE IF EXISTS students")
    cursor.execute(
        "CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, "
        "name VARCHAR(100) NOT NULL, age INT NOT NULL)")
    for name, age in [("Dana", 24), ("Avi", 31), ("Noa", 22)]:
        cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", (name, age))
    connection.commit()

    cursor.execute("SELECT id, name, age FROM students WHERE id = %s", (2,))
    print("By id 2:", cursor.fetchone())

    cursor.execute("SELECT name FROM students WHERE age > %s ORDER BY age", (23,))
    print("Older than 23:", cursor.fetchall())

    cursor.execute("SELECT id, name FROM students WHERE id = %s", (99,))
    print("By id 99:", cursor.fetchone())

    attack = "x' OR '1'='1"

    unsafe_sql = "SELECT name FROM students WHERE name = '" + attack + "'"
    print("UNSAFE query:", unsafe_sql)
    cursor.execute(unsafe_sql)
    print("UNSAFE result:", cursor.fetchall())

    cursor.execute("SELECT name FROM students WHERE name = %s", (attack,))
    print("SAFE result:", cursor.fetchall())
finally:
    connection.close()

פלט:

By id 2: (2, 'Avi', 31)
Older than 23: [('Dana',), ('Avi',)]
By id 99: None
UNSAFE query: SELECT name FROM students WHERE name = 'x' OR '1'='1'
UNSAFE result: [('Dana',), ('Avi',), ('Noa',)]
SAFE result: []

פירוק:

  • DROP TABLE IF EXISTS students — מוחק את הטבלה, אם קיימת, כדי שכל הרצה תתחיל נקייה. (נוח להדגמות. לא עושים את זה בקוד אמיתי!)
  • INSERT בלולאה, ו-commit אחד בסוף — שלוש הכנסות נשמרות יחד.
  • fetchone() על id קיים ‏←‏ tuple. על id שלא קיים ‏←‏ None. זה ה"לא נמצא" שיהפוך ל-404.
  • (2,), (23,) — tuple של ערך אחד, עם פסיק.
  • הגרסה הלא בטוחה החזירה את כל הסטודנטים, כי הקלט שינה את השאילתה עצמה.
  • הגרסה הבטוחה חיפשה סטודנט ששמו בדיוק x' OR '1'='1, ואין כזה, ולכן []. ההתקפה נכשלה.

טעות נפוצה

הטעות 1: לשכוח commit.

cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", ("Tom", 27))
connection.close()

אין שגיאה, וTom לא נשמר. סגירה בלי commit = ביטול השינויים. זו אחת הטעויות המבלבלות ביותר, כי “הכול עבד”.

הטעות 2: פרמטר בודד בלי פסיק.

cursor.execute("SELECT name FROM students WHERE id = %s", (2))
mysql.connector.errors.ProgrammingError: Could not process parameters: int(2), it must be of type list, tuple or dict

(2) הוא int, ולא tuple. → (2,).

הטעות 3: מספר פרמטרים לא תואם.

cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", ("Tom",))
mysql.connector.errors.ProgrammingError: Not enough parameters for the SQL statement

הטעות 4: %s עם גרש.

cursor.execute("SELECT * FROM students WHERE name = '%s'", ("Dana",))
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; ... near 'Dana''' at line 1

ה-Driver כבר מוסיף גרשים בעצמו, ועכשיו יש גרשים כפולים. %s תמיד בלי גרש.

הטעות 5: %s במקום שם של טבלה או עמודה. "SELECT * FROM %s" לא עובד: Placeholders הם רק לערכים — ה-Driver הופך את students ל-'students' (מחרוזת), וזו שגיאת תחביר. שמות של טבלאות ועמודות כותבים בקוד, קבועים.

תרגול

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

תרגיל 1: execute עם פרמטרים

כתבו פונקציה add_student(connection, name, age) שמוסיפה סטודנט עם פרמטרים (%s), שומרת, ומחזירה את ה-id החדש.

cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS students "
               "(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, age INT NOT NULL)")
print(add_student(connection, "Dana", 24))
print(add_student(connection, "Avi", 31))
cursor.execute("SELECT * FROM students")
print(cursor.fetchall())

פלט צפוי:

1
2
[(1, 'Dana', 24), (2, 'Avi', 31)]
הצג פתרון
import mysql.connector


def add_student(connection, name, age):
    cursor = connection.cursor()
    cursor.execute(
        "INSERT INTO students (name, age) VALUES (%s, %s)", (name, age))
    connection.commit()
    return cursor.lastrowid


connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS students "
               "(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, age INT NOT NULL)")
print(add_student(connection, "Dana", 24))
print(add_student(connection, "Avi", 31))
cursor.execute("SELECT * FROM students")
print(cursor.fetchall())
connection.close()
1
2
[(1, 'Dana', 24), (2, 'Avi', 31)]
  • %s הם מקומות שמורים, והערכים עוברים בנפרד כ-tuple. (ב-MySQL כותבים %s לכל טיפוס — גם למספרים.)
  • cursor.lastrowid — ה-id שהמסד נתן לשורה החדשה.

תרגיל 2: SQL Injection

בטבלה יש שני סטודנטים — דנה ואבי. צרו אותה כך:

import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
cursor = connection.cursor()
cursor.execute("DROP TABLE IF EXISTS students")   # start clean, so ids start from 1
cursor.execute("CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100))")
cursor.execute("INSERT INTO students (name) VALUES ('Dana'), ('Avi')")
connection.commit()
connection.close()

קוד שבונה שאילתה מקלט של משתמש:

name = "x' OR '1'='1"
sql = "SELECT name FROM students WHERE name = '" + name + "'"
  1. איך תיראה הפקודה שנוצרת, ומה היא תחזיר?
  2. הראו בקוד את ההבדל: הריצו את השאילתה בשרשור והדפיסו Unsafe: ואת התוצאה, ואז את אותה שאילתה עם %s והדפיסו Safe: ואת התוצאה.

פלט צפוי:

Unsafe: [('Dana',), ('Avi',)]
Safe: []
הצג פתרון
  1. הפקודה היא SELECT name FROM students WHERE name = 'x' OR '1'='1' — והתנאי '1'='1' תמיד נכון, ולכן היא מחזירה את כל הסטודנטים. הקלט של המשתמש הפך לחלק מהפקודה.
import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS students "
               "(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100))")
cursor.execute("INSERT INTO students (name) VALUES ('Dana'), ('Avi')")
connection.commit()

name = "x' OR '1'='1"

sql = "SELECT name FROM students WHERE name = '" + name + "'"
cursor.execute(sql)
print("Unsafe:", cursor.fetchall())

cursor.execute("SELECT name FROM students WHERE name = %s", (name,))
print("Safe:", cursor.fetchall())
connection.close()
Unsafe: [('Dana',), ('Avi',)]
Safe: []

עם %s הערך עובר בנפרד — ה-Driver מתייחס אליו כאל שם מוזר, לעולם לא כאל פקודה. לעולם לא בונים SQL בשרשור של קלט.

תרגיל 3: commit שנשכח

connection = mysql.connector.connect(...)
cursor = connection.cursor()
cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", ("Noa", 22))
connection.close()

התוכנית רצה בלי שגיאה, אבל נועה לא נמצאת במסד. למה?

הראו בקוד: התחילו מטבלת students ריקה (עם name ו-age). הכניסו את נועה בלי commit, סגרו, ובחיבור חדש ספרו כמה שורות יש בטבלה. אחר כך הכניסו אותה שוב עם commit, וספרו שוב.

פלט צפוי:

Without commit: 0 rows
With commit: 1 rows
הצג פתרון

בלי commit() השינוי נשאר זמני, ו-close() מבטל אותו בשקט.

import mysql.connector


def connect():
    return mysql.connector.connect(
        host="localhost", user="root", password="kodkod", database="school")


def count_rows():
    connection = connect()
    try:
        cursor = connection.cursor()
        cursor.execute("SELECT * FROM students")
        return len(cursor.fetchall())
    finally:
        connection.close()


setup = connect()
setup.cursor().execute("CREATE TABLE IF NOT EXISTS students (name VARCHAR(100), age INT)")
setup.close()

connection = connect()
cursor = connection.cursor()
cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", ("Noa", 22))
connection.close()
print("Without commit:", count_rows(), "rows")

connection = connect()
cursor = connection.cursor()
cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", ("Noa", 22))
connection.commit()
connection.close()
print("With commit:", count_rows(), "rows")
Without commit: 0 rows
With commit: 1 rows

CREATE TABLE נשמר מיד, אבל כל INSERT, UPDATE ו-DELETE מחכה ל-commit.

תרגיל 4: חיפוש בטוח לפי id

בטבלה יש סטודנטית אחת — דנה, בת 24. צרו אותה כך:

import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
cursor = connection.cursor()
cursor.execute("DROP TABLE IF EXISTS students")   # start clean, so ids start from 1
cursor.execute("CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), age INT)")
cursor.execute("INSERT INTO students (name, age) VALUES ('Dana', 24)")
connection.commit()
connection.close()

כתבו find_by_id(student_id) שפותחת חיבור, מחפשת סטודנט עם פרמטר, וסוגרת תמיד. היא מחזירה את השורה או None.

print(find_by_id(1))
print(find_by_id(99))

פלט צפוי:

(1, 'Dana', 24)
None
הצג פתרון
import mysql.connector


def get_connection():
    return mysql.connector.connect(
        host="localhost", user="root", password="kodkod", database="school")


def find_by_id(student_id):
    connection = get_connection()
    try:
        cursor = connection.cursor()
        cursor.execute("SELECT * FROM students WHERE id = %s", (student_id,))
        return cursor.fetchone()
    finally:
        connection.close()


setup = get_connection()
cursor = setup.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS students "
               "(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), age INT)")
cursor.execute("INSERT INTO students (name, age) VALUES ('Dana', 24)")
setup.commit()
setup.close()

print(find_by_id(1))
print(find_by_id(99))
(1, 'Dana', 24)
None

שימו לב ל-(student_id,) — tuple עם איבר אחד צריך פסיק. בלי הפסיק זה סתם סוגריים סביב מספר.

תרגיל 5: המסלול של שאילתה

סדרו את השלבים של הרצת שאילתה מתוך Python:

  • (א) connection.commit() — רק אם שינינו נתונים
  • (ב) mysql.connector.connect(...) ואז connection.cursor()
  • (ג) connection.close()
  • (ד) cursor.execute(sql, params)
  • (ה) cursor.fetchone() / fetchall() — רק אם קראנו נתונים
הצג פתרון

הסדר: (ב), (ד), אחר כך (ה) או (א), ובסוף (ג)

  1. connect + cursor — פותחים חיבור ויוצרים Cursor.
  2. execute — מריצים את השאילתה עם הפרמטרים.
  3. אם זו קריאה (SELECT) — fetch את התוצאות. אם זה שינוי (INSERT, UPDATE, DELETE) — commit.
  4. close — סוגרים, בתוך finally.

סיכום

  • המסלול: Python ‏←‏ Connection ‏←‏ Query (עם %s ופרמטרים) ‏←‏ Database ‏←‏ Result ‏←‏ Python.
  • cursor.execute(sql, params) — ערכים רק דרך %s ו-tuple. tuple של ערך אחד: (x,).
  • SQL Injection = קלט שמשנה את השאילתה. לעולם לא לשרשר ערכים מבחוץ לתוך SQL.
  • שינוי נתונים (INSERT/UPDATE/DELETE) → commit(), אחרת השינוי נזרק בסגירה.
  • cursor.lastrowid = ה-id החדש. fetchone() = שורה או None. fetchall() = רשימת tuples.
  • פונקציה לכל פעולה, עם חיבור ו-try/finally משלה.

דוקומנטציה רשמית