Prevent SQL Injection with Python SQLite Prepared Statements

import sqlite3

def get_user_data(user_id):
    """
    Fetches user data from the database using a prepared statement.
    Prevents SQL injection.
    """
    db_path = 'users.db'
    conn = None
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()

        # Vulnerable approach (DO NOT USE IN PRODUCTION)
        # user_input_id = f"'{user_id}' OR 1=1 --"
        # cursor.execute(f"SELECT id, username, email FROM users WHERE id = {user_input_id}")

        # Secure approach: Using parameterized queries (prepared statements)
        # The database driver handles the escaping of the parameter.
        cursor.execute("SELECT id, username, email FROM users WHERE id = ?", (user_id,))
        
        user = cursor.fetchone()
        return user
    except sqlite3.Error as e:
        print(f"Database error: {e}")
        return None
    finally:
        if conn:
            conn.close()

def create_user_table():
    """Helper function to create a dummy users table and insert some data."""
    db_path = 'users.db'
    conn = None
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY,
                username TEXT NOT NULL UNIQUE,
                email TEXT NOT NULL
            )
        ''')
        # Insert some dummy data if table is empty
        cursor.execute("INSERT OR IGNORE INTO users (id, username, email) VALUES (1, 'alice', '[email protected]')")
        cursor.execute("INSERT OR IGNORE INTO users (id, username, email) VALUES (2, 'bob', '[email protected]')")
        conn.commit()
    except sqlite3.Error as e:
        print(f"Error creating/populating table: {e}")
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    create_user_table()

    # Example of secure usage
    print("--- Secure Query ---")
    retrieved_user = get_user_data(1)
    if retrieved_user:
        print(f"Found user: {retrieved_user}")
    else:
        print("User not found or error occurred.")

    # Example of attempting SQL injection (will be safely handled by prepared statement)
    print("
--- Attempted SQL Injection (prevented) ---")
    malicious_id = "1 OR 1=1 --" # This would return all users in a vulnerable query
    retrieved_malicious_user = get_user_data(malicious_id)
    if retrieved_malicious_user:
        print(f"Found user with malicious input: {retrieved_malicious_user}")
    else:
        print("Malicious input safely handled. No user found for that specific string.")

    # A more subtle malicious input that often tricks non-prepared statements
    print("
--- Another SQL Injection Attempt (prevented) ---")
    another_malicious_id = "1; DROP TABLE users; --" # Would drop the table in a vulnerable query
    get_user_data(another_malicious_id) # Just calling it to show it doesn't execute
    
    # Check if table still exists (it should)
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='users';")
    if cursor.fetchone():
        print("Table 'users' still exists after injection attempt (as expected).")
    else:
        print("Table 'users' was dropped! This indicates a vulnerability.")
    conn.close()