Prevent SQL Injection with Parameterized Queries in Python

import sqlite3

def create_table(db_name="example.db"):
    conn = sqlite3.connect(db_name)
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY,
            username TEXT NOT NULL UNIQUE,
            email TEXT NOT NULL
        )
    ''')
    conn.commit()
    conn.close()

def insert_user_secure(username, email, db_name="example.db"):
    conn = sqlite3.connect(db_name)
    cursor = conn.cursor()
    # Using placeholders (?) for parameters
    cursor.execute("INSERT INTO users (username, email) VALUES (?, ?)", (username, email))
    conn.commit()
    conn.close()
    print(f"User '{username}' inserted securely.")

def get_user_by_username_secure(username, db_name="example.db"):
    conn = sqlite3.connect(db_name)
    cursor = conn.cursor()
    # Using placeholders (?) for parameters in SELECT
    cursor.execute("SELECT id, username, email FROM users WHERE username = ?", (username,))
    user = cursor.fetchone()
    conn.close()
    return user

# --- Example Usage --- 
create_table()

# Secure insertion
insert_user_secure("alice", "[email protected]")
insert_user_secure("bob", "[email protected]")

# Attempting a malicious insertion (will be treated as literal string)
malicious_username = "charlie', '[email protected]'); DROP TABLE users; --"
insert_user_secure(malicious_username, "[email protected]")

# Secure retrieval
found_user = get_user_by_username_secure("alice")
print(f"Found User (alice): {found_user}")

# Trying to retrieve the malicious user (will correctly match by username)
found_malicious_user = get_user_by_username_secure(malicious_username)
print(f"Found Malicious User (charlie): {found_malicious_user}")

# What if a user inputs something like "admin' OR '1'='1" for username?
# With parameterized queries, it will search for a username literally matching "admin' OR '1'='1"
# rather than executing it as SQL code.
# This is the core prevention mechanism.