How to Build a Secure Natural Language to SQL CLI Tool Using Gemini Flash and Python

The Problem with Naive Text-to-SQL

Asking an AI model to write database queries is incredibly appealing. It allows product managers, support teams, and busy engineers to ask complex questions of their databases without writing a single line of SQL.

However, naive implementations of this concept are a security disaster. If you simply feed user prompts into an LLM and run the raw resulting string directly against your database, you are begging for SQL injection attacks, accidental schema deletions, or catastrophic data leaks.

To make text-to-SQL viable in a production ecosystem, we must enforce absolute isolation. In this guide, we will build a command-line interface (CLI) tool using Python and Gemini Flash that safely translates English queries into SQL. We will restrict the engine to read-only operations, parse and validate the SQL structure before execution, and run our queries against a sandboxed connection.

Designing the Security Safeguards

To keep our data secure, we will build three layers of defence:

1. The Read-Only Database Connection: We will connect to SQLite using a read-only URI configuration. Even if the LLM tries to run a DELETE or DROP statement, the database engine itself will block it.
2. Schema-Only Context: We only show Gemini the metadata (table names, column names, types) of our schema. We never expose actual user data to the prompt context.
3. Query Sanitisation: We will write a lightweight static parser in Python that validates the generated SQL against an allowlist of commands (e.g., only allowing SELECT) before execution.

Step 1: Setting Up the Database

For this tutorial, we will set up a mock SQLite database containing a clean retail schema. Create a file called setup_db.py to initialise the database:

`python import sqlite3

def init_db(): conn = sqlite3.connect("retail_data.db") cursor = conn.cursor() # Create tables cursor.execute(""" CREATE TABLE IF NOT EXISTS users ( user_id INTEGER PRIMARY KEY, name TEXT, email TEXT, joined_date TEXT )""") cursor.execute(""" CREATE TABLE IF NOT EXISTS purchases ( purchase_id INTEGER PRIMARY KEY, user_id INTEGER, amount REAL, purchase_date TEXT, FOREIGN KEY(user_id) REFERENCES users(user_id) )""") # Insert dummy data cursor.execute("INSERT OR IGNORE INTO users VALUES (1, 'Alice Smith', 'alice@example.com', '2023-01-15')") cursor.execute("INSERT OR IGNORE INTO users VALUES (2, 'Bob Jones', 'bob@example.com', '2023-06-20')") cursor.execute("INSERT OR IGNORE INTO purchases VALUES (101, 1, 150.50, '2023-11-01')") cursor.execute("INSERT OR IGNORE INTO purchases VALUES (102, 1, 45.00, '2023-11-15')") cursor.execute("INSERT OR IGNORE INTO purchases VALUES (103, 2, 320.00, '2023-12-01')") conn.commit() conn.close()

if __name__ == "__main__": init_db() print("Database initialised successfully.") `

Run this script once to create your local database.

Step 2: Instantiating Gemini Flash and the Safe Prompt

We will use google-genai to generate our SQL. Gemini Flash is the perfect model for this utility; it is fast, cheap, and offers highly reliable structured outputs when configured properly.

Ensure you install the correct SDK:

`bash pip install google-genai `

Now, let us build our query engine. We will extract the DB schema dynamically to construct our prompt, ensuring we do not manually copy schemas when changes occur. We can structure our rules using advanced techniques discussed in our guide on prompt construction.

`python import os import sqlite3 from google import genai from google.genai import types

client = genai.Client(api_key=os.environ.get("GEMINI_API_KEY"))

def get_schema_summary() -> str: conn = sqlite3.connect("retail_data.db") cursor = conn.cursor() cursor.execute("SELECT name, sql FROM sqlite_master WHERE type='table';") tables = cursor.fetchall() conn.close() schema_summary = [] for table_name, create_sql in tables: schema_summary.append(f"Table: {table_name}\nStructure:\n{create_sql}\n") return "\n".join(schema_summary)

def generate_sql(user_query: str) -> str: schema = get_schema_summary() prompt = f""" You are a secure database analysis tool. Given the following SQLite schema, convert the user's plain English request into a clean SQL query. DATABASE SCHEMA: {schema} REQUEST: "{user_query}" RULES: - Your output must be a single, raw, valid SELECT statement. - You MUST NOT use commands that modify data: no INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, or REPLACE. - If the request cannot be answered with a SELECT statement, return an empty string. """ response = client.models.generate_content( model='gemini-2.5-flash', contents=prompt, config=types.GenerateContentConfig( temperature=0.1, # Ensure clean, non-markdown outputs response_mime_type="text/plain" ) ) return response.text.strip().replace("`sql", "").replace("`", "").strip() `

Step 3: Implementing Strict Verification and Execution Guards

Even though we told Gemini to only use SELECT statements, we must assume that LLMs can and will bypass instructions under clever phrasing or accidental context injection.

We will write a static Python check that parses the generated query before executing it, and open the SQLite database using a strict read-only query parameter.

`python def is_query_safe(sql_query: str) -> bool: normalized_query = sql_query.strip().upper() # Basic structural validation if not normalized_query.startswith("SELECT"): return False # Block obvious injection or multi-command queries forbidden_keywords = ["INSERT", "UPDATE", "DELETE", "DROP", "ALTER", "CREATE", "REPLACE", ";"] for keyword in forbidden_keywords: # Ensure we do not trigger on words inside strings by looking for boundaries if f" {keyword} " in f" {normalized_query} ": return False return True

def execute_query_safely(sql_query: str): if not is_query_safe(sql_query): raise PermissionError("Unsafe database operation detected and blocked.") # Open connection in read-only mode using a URI # This prevents any writing operations even if the query passes our checks db_uri = "file:retail_data.db?mode=ro" conn = sqlite3.connect(db_uri, uri=True) cursor = conn.cursor() try: cursor.execute(sql_query) results = cursor.fetchall() columns = [description[0] for description in cursor.description] return columns, results finally: conn.close() `

Step 4: Putting It Together Into a CLI

Let us wrap this into a loop so you can interact with your database using natural language safely.

`python import sys

def main(): if not os.environ.get("GEMINI_API_KEY"): print("Error: GEMINI_API_KEY environment variable is not set.") print("Please consult the troubleshooting steps on https://googlegemini-support.com if you face connection issues.") sys.exit(1)

print("=== Secure Text-to-SQL CLI Interface ===") print("Ask questions like: 'Who spent the most money?' or 'How many users joined in 2023?'") print("Type 'exit' to quit.\n") while True: try: user_input = input("Query > ").strip() if user_input.lower() == 'exit': break if not user_input: continue print("[*] Translating query...") sql = generate_sql(user_input) print(f"[*] Generated SQL: {sql}") columns, rows = execute_query_safely(sql) # Print formatted results print(f"\n{' | '.join(columns)}") print("-" * (len(" | ".join(columns)) + 4)) for row in rows: print(" | ".join(str(val) for val in row)) print(f"\n({len(rows)} rows returned)\n") except PermissionError as pe: print(f"[SECURITY BLOCK]: {pe}\n") except Exception as e: print(f"[ERROR]: Could not execute query. Details: {e}\n")

if __name__ == "__main__": main() `

Moving Beyond the CLI

By following this architecture, you eliminate the single point of failure that usually plagues AI-driven database utilities. If Gemini hallucinations generate an unexpected statement, your static validator blocks it. If both Gemini and the static validator fail, SQLite's strict mode=ro connection ensures the database engine blocks any writes anyway.

For troubleshooting API network exceptions or key configuration errors, refer directly to Gemini Support. Implementing layers of programmatic constraints is the only path to adopting rapid AI tools inside enterprise codebases without sacrificing security.