PYTHON
Prevent SQL Injection with Python Prepared Statements
Secure your Python database interactions against SQL injection by using prepared statements and parameterized queries with psycopg2 for PostgreSQL.
import psycopg2
from psycopg2 import Error
def get_user_data_secure(user_id):
"""
Fetches user data securely using prepared statements.
"""
conn = None
try:
# Establish a connection to the database
# Replace with your actual database credentials
conn = psycopg2.connect(
user="your_db_user",
password="your_db_password",
host="127.0.0.1",
port="5432",
database="your_database"
)
cursor = conn.cursor()
# NEVER do this (SQL Injection Vulnerable):
# query = f"SELECT * FROM users WHERE id = {user_id};"
# cursor.execute(query)
# ALWAYS use parameterized queries / prepared statements (secure):
query = "SELECT id, username, email FROM users WHERE id = %s;"
cursor.execute(query, (user_id,)) # Pass parameters as a tuple
user_data = cursor.fetchone()
if user_data:
print(f"Securely fetched user: ID={user_data[0]}, Username={user_data[1]}, Email={user_data[2]}")
return user_data
else:
print(f"User with ID {user_id} not found.")
return None
except (Exception, Error) as error:
print(f"Error while connecting to PostgreSQL or executing query: {error}")
finally:
if conn:
cursor.close()
conn.close()
print("PostgreSQL connection closed.")
# Example usage:
# Assuming you have a 'users' table with columns 'id', 'username', 'email'
get_user_data_secure(1)
# Try a malicious input (will be treated as a literal string by the database)
get_user_data_secure("1 OR 1=1")
How it works: This Python snippet demonstrates how to prevent SQL injection vulnerabilities by using prepared statements with the `psycopg2` library for PostgreSQL. Instead of embedding user input directly into the SQL query string, parameters are passed separately to `cursor.execute()`. The database then safely handles the user input, treating it as data rather than executable code, thereby neutralizing injection attempts.