Preventing SQL Injection with Python Parameterized Queries

import psycopg2
from psycopg2 import sql

def get_user_data(user_id):
    conn = None
    try:
        # Replace with your actual database connection details
        conn = psycopg2.connect(
            dbname="mydatabase",
            user="myuser",
            password="mypassword",
            host="localhost"
        )
        cur = conn.cursor()

        # CORRECT: Using a parameterized query
        # The database driver handles escaping the value securely
        query = sql.SQL("SELECT * FROM users WHERE id = %s;")
        cur.execute(query, (user_id,)) # Pass parameters as a tuple

        user_data = cur.fetchone()
        return user_data
    except psycopg2.Error as e:
        print(f"Database error: {e}")
        return None
    finally:
        if conn:
            cur.close()
            conn.close()

def create_user(username, email, password_hash):
    conn = None
    try:
        conn = psycopg2.connect(
            dbname="mydatabase",
            user="myuser",
            password="mypassword",
            host="localhost"
        )
        cur = conn.cursor()

        # CORRECT: Using a parameterized query for INSERT
        insert_query = sql.SQL("INSERT INTO users (username, email, password_hash) VALUES (%s, %s, %s);")
        cur.execute(insert_query, (username, email, password_hash))
        conn.commit()
        print(f"User {username} created successfully.")
    except psycopg2.Error as e:
        print(f"Database error: {e}")
        conn.rollback() # Rollback in case of error
    finally:
        if conn:
            cur.close()
            conn.close()

# --- NEVER DO THIS (Vulnerable to SQL Injection) ---
def get_user_data_vulnerable(user_id):
    conn = None
    try:
        conn = psycopg2.connect(
            dbname="mydatabase",
            user="myuser",
            password="mypassword",
            host="localhost"
        )
        cur = conn.cursor()
        # FLAWED: Direct string concatenation of user input
        # An attacker could inject malicious SQL here
        query = f"SELECT * FROM users WHERE id = {user_id};"
        cur.execute(query)
        user_data = cur.fetchone()
        return user_data
    except psycopg2.Error as e:
        print(f"Vulnerable query error: {e}")
        return None
    finally:
        if conn:
            cur.close()
            conn.close()

if __name__ == "__main__":
    # Example usage (secure)
    user = get_user_data(1)
    print(f"User data (secure): {user}")

    # Example usage (vulnerable) - DO NOT USE IN PRODUCTION
    # Imagine user_id comes from user input, e.g., request.args.get('id')
    malicious_input = "1 OR 1=1; --" # This could retrieve all users or drop tables
    user_vulnerable = get_user_data_vulnerable(malicious_input)
    print(f"User data (vulnerable): {user_vulnerable}")

    # Example of creating a user securely
    create_user("john_doe", "[email protected]", "hashed_password_abc")