Preventing SQL Injection with Parameterized Queries

import sqlite3

def create_user_table(db_name='users.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 register_user_safe(username, email, db_name='users.db'):
    conn = sqlite3.connect(db_name)
    cursor = conn.cursor()
    try:
        # Use a parameterized query to prevent SQL injection
        cursor.execute("INSERT INTO users (username, email) VALUES (?, ?)", (username, email))
        conn.commit()
        print(f"User '{username}' registered successfully.")
    except sqlite3.IntegrityError:
        print(f"Error: Username '{username}' already exists.")
    except Exception as e:
        print(f"An error occurred: {e}")
    finally:
        conn.close()

def get_user_by_username_safe(username, db_name='users.db'):
    conn = sqlite3.connect(db_name)
    cursor = conn.cursor()
    # Use a parameterized query for SELECT statements too
    cursor.execute("SELECT * FROM users WHERE username = ?", (username,))
    user = cursor.fetchone()
    conn.close()
    return user

# --- Example Usage ---
if __name__ == "__main__":
    db_file = 'users.db'
    create_user_table(db_file)

    # Safe registration
    register_user_safe("alice", "[email protected]", db_file)
    register_user_safe("bob", "[email protected]", db_file)
    register_user_safe("alice", "[email protected]", db_file) # Will show error

    # Simulate a malicious input for demonstration (will be safely escaped)
    malicious_username = "charlie'; DROP TABLE users; --"
    register_user_safe(malicious_username, "[email protected]", db_file)

    # Safe retrieval
    user_data = get_user_by_username_safe("alice", db_file)
    if user_data:
        print(f"Found user: {user_data}")
    else:
        print("User not found.")

    # Try to find the malicious username (it's inserted as a literal string)
    malicious_user_data = get_user_by_username_safe(malicious_username, db_file)
    if malicious_user_data:
        print(f"Found malicious user (as literal string): {malicious_user_data}")
    else:
        print("Malicious user not found (which is good if it was meant to execute code).")