Lecture 10: DML, Triggers & Python DB-API

-- Single row insert with explicit columns
INSERT INTO student (id, name, dept_name, tot_cred)
VALUES ('98988', 'Tanaka', 'Biology', 0);

-- Multi-row insert
INSERT INTO department (dept_name, building, budget) VALUES
    ('Data Science', 'Alan Turing', 900000),
    ('Robotics', 'Grace Hopper', 750000);

-- Bulk insert from a query
INSERT INTO faculty_archive
SELECT * FROM instructor WHERE salary < 40000;

-- Increase salaries of Comp. Sci. instructors by 5%
UPDATE instructor
SET salary = salary * 1.05
WHERE dept_name = 'Comp. Sci.';

INSERT INTO course_views (course_id, view_count)
VALUES ('CS-101', 1)
ON CONFLICT (course_id) DO UPDATE SET
    view_count = course_views.view_count + 1;

Trigger Event: [ INSERT / UPDATE / DELETE ]
Timing:        [ BEFORE | AFTER | INSTEAD OF ]
Granularity:   [ FOR EACH ROW | FOR EACH STATEMENT ]

-- Example: Audit salary modifications into an audit log
CREATE TRIGGER audit_instructor_salary
AFTER UPDATE OF salary ON instructor
FOR EACH ROW
WHEN (OLD.salary <> NEW.salary)
BEGIN
    INSERT INTO salary_log (instructor_id, old_sal, new_sal, changed_at)
    VALUES (OLD.id, OLD.salary, NEW.salary, DATETIME('now'));
END;

import sqlite3
import sys

# 1. Establish connection
conn = sqlite3.connect("university.db")

# 2. Access columns by name like dictionary keys
conn.row_factory = sqlite3.Row
cur = conn.cursor()

# 3. Always enable foreign keys in SQLite
cur.execute("PRAGMA foreign_keys = ON;")

dept = sys.argv[1] if len(sys.argv) > 1 else "Comp. Sci."

# 4. Safe parameterized query (NEVER use f-strings or string concatenation!)
cur.execute("SELECT name, salary FROM instructor WHERE dept_name = ?", (dept,))

for row in cur.fetchall():
    print(f"{row['name']}: ${row['salary']:,.2f}")

# 5. Commit mutations and close
conn.commit()
conn.close()

# VULNERABLE CODE: NEVER DO THIS!
user_input = "Comp. Sci.' OR '1'='1"
query = f"SELECT * FROM instructor WHERE dept_name = '{user_input}';"
cur.execute(query)

SELECT * FROM instructor WHERE dept_name = 'Comp. Sci.' OR '1'='1';