import sqlite3

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

def add_user_secure(username, password_hash):
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()
    # GOOD: Using parameterized queries (placeholders ?)
    # The database driver handles the escaping, preventing injection.
    try:
        cursor.execute("INSERT INTO users (username, password_hash) VALUES (?, ?)", (username, password_hash))
        conn.commit()
        print(f"User '{username}' added securely.")
    except sqlite3.IntegrityError:
        print(f"Error: Username '{username}' already exists.")
    finally:
        conn.close()

def get_user_secure(username):
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()
    # GOOD: Using parameterized queries for SELECT statements too
    cursor.execute("SELECT id, username, password_hash FROM users WHERE username = ?", (username,))
    user = cursor.fetchone()
    conn.close()
    return user

def get_user_vulnerable(username_input):
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()
    # BAD: Direct string concatenation - highly vulnerable to SQL injection
    sql_query = f"SELECT id, username FROM users WHERE username = '{username_input}'"
    print(f"Vulnerable Query: {sql_query}")
    try:
        cursor.execute(sql_query)
        users = cursor.fetchall()
        conn.close()
        return users
    except sqlite3.Error as e:
        print(f"SQL Error: {e}")
        return None

if __name__ == "__main__":
    create_database()

    # Add a test user securely
    add_user_secure("alice", "hashed_password_alice")
    add_user_secure("bob", "hashed_password_bob")

    print("
--- Retrieving users securely ---")
    user_alice = get_user_secure("alice")
    if user_alice:
        print(f"Securely retrieved: ID={user_alice[0]}, Username={user_alice[1]}")
    else:
        print("User 'alice' not found.")

    user_charlie = get_user_secure("charlie")
    if user_charlie:
        print(f"Securely retrieved: ID={user_charlie[0]}, Username={user_charlie[1]}")
    else:
        print("User 'charlie' not found.")


    print("
--- Demonstrating SQL Injection (VULNERABLE) ---")

    # An innocent-looking input
    innocent_username = "bob"
    print(f"Attempting to retrieve '{innocent_username}' (vulnerable func)...")
    vulnerable_result_innocent = get_user_vulnerable(innocent_username)
    print(f"Vulnerable func result for '{innocent_username}': {vulnerable_result_innocent}")

    # A malicious input designed to bypass authentication or extract data
    # This input closes the string literal, adds a OR condition, and comments out the rest.
    malicious_username = "' OR 1=1 --"
    print(f"
Attempting to retrieve '{malicious_username}' (vulnerable func)...")
    vulnerable_result_malicious = get_user_vulnerable(malicious_username)
    print(f"Vulnerable func result for '{malicious_username}': {vulnerable_result_malicious}")
    # If the vulnerable function returned ALL users, it would show SQL injection was successful.
    # (sqlite3.OperationalError: near "--": syntax error sometimes with specific inputs,
    # but the principle holds for other DBs/inputs)
    # The key takeaway is the attempt to manipulate the query structure.

    # Another malicious input: ' UNION SELECT 1, 'admin_pass' --
    malicious_union_username = "' UNION SELECT id, username, password_hash FROM users WHERE 1=1 --"
    print(f"
Attempting to retrieve '{malicious_union_username}' (vulnerable func)...")
    # For a full demonstration, `get_user_vulnerable` would need to fetch all columns.
    # This example primarily shows the query modification.
    vulnerable_result_union = get_user_vulnerable(malicious_union_username)
    print(f"Vulnerable func result for '{malicious_union_username}': {vulnerable_result_union}")
    # Note: SQLite3's `execute` on direct string concatenation with multiple statements or
    # specific comments might sometimes throw errors, but the vulnerability is in the *construction*
    # of the query, allowing an attacker to change its meaning.
    # Parameterized queries completely prevent this.