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;orDELETE FROM incidents;orSELECT * 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:
- Automatically inspects the database's tables and columns (
PRAGMA table_info) to generate a compact Schema Prompt for the LLM.- 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
SELECTorWITH.- 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
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 Output | Security Decision | Why? |
|---|---|---|
SELECT service, AVG(duration_ms) FROM incidents GROUP BY service; | ALLOWED | Single SELECT query, read-only. |
WITH top AS (SELECT * FROM incidents) SELECT * FROM top; | ALLOWED | Starts 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!