Week 3 • Day 7Week 3

Day 7:Mini-Project (Safe Read-Only Text-to-SQL Engine) + Week 3 Final Interview Grill

Battle-tested daily lesson note • 100% runnable code, interview Q&A, test suite & interactive quiz

Week 3 — Day 7: Mini-Project (Safe Read-Only Text-to-SQL Engine) + Week 3 Final Interview Grill

Mentor's Note (10+ YOE Staff AI Engineer POV): Welcome to the Week 3 Mini-Project! One of the most requested AI features in every enterprise company is Text-to-SQL: A manager asks in plain English: "Which service had the highest average incident resolution time?" and the AI translates that into SQL and queries the database.

What is the #1 Security Risk of Text-to-SQL? What if a malicious user (or a hallucinating LLM!) generates: DROP TABLE incidents; or DELETE FROM incidents; or SELECT * FROM incidents; UPDATE users SET role='admin';?

Today we build a Production Text-to-SQL Schema Introspector & Read-Only Guardrail Engine (text_to_sql_engine.py) that:

  1. Automatically inspects the database's tables and columns (PRAGMA table_info) to generate a compact Schema Prompt for the LLM.
  2. Enforces 3 layers of Read-Only Defense:
    • Layer 1: Rejects multi-statement queries (; chaining) and forbidden write keywords (DROP, DELETE, UPDATE, INSERT, ALTER, PRAGMA, ATTACH).
    • Layer 2: Requires the query to start strictly with SELECT or WITH.
    • Layer 3: Enables SQLite's database-level PRAGMA query_only = ON; so even the database engine itself refuses any state mutation!

Running the Week 3 Mini-Project & Test Suite

powershell
python week03_sql_databases_from_zero_to_pro/day07_week3_mini_project_text_to_sql_analytics/src/text_to_sql_engine.py python -m unittest discover -s week03_sql_databases_from_zero_to_pro/day07_week3_mini_project_text_to_sql_analytics/tests -v

Concrete Examples: What Our 3 Security Guards Allow vs Block

User Question / LLM OutputSecurity DecisionWhy?
SELECT service, AVG(duration_ms) FROM incidents GROUP BY service;ALLOWEDSingle SELECT query, read-only.
WITH top AS (SELECT * FROM incidents) SELECT * FROM top;ALLOWEDStarts with CTE WITH, read-only.
SELECT * FROM incidents; DROP TABLE incidents;BLOCKED (Layer 1)Chained semicolon + forbidden DROP command.
UPDATE incidents SET resolved = 1;BLOCKED (Layer 2 & 3)Not a SELECT, plus PRAGMA query_only = ON blocks writes at engine level.

Hands-On Practice: Switch to Tab 2 (Codes) and Tab 4 (Test & Troubleshooting) to run this pure-Python + SQLite engine directly in your browser!