PYTHON
Prevent SQL Injection with Parameterized Queries (Python)
Learn to prevent SQL injection vulnerabilities in your Python applications using parameterized queries, a critical security practice for database interactions.
import psycopg2
def get_user_data_securely(user_id):
conn = None
try:
# Establish a database connection (replace with your actual connection string)
conn = psycopg2.connect("dbname=yourdb user=youruser password=yourpassword")
cur = conn.cursor()
# NEVER use f-strings or string concatenation for SQL queries with user input!
# This is INSECURE: query = f"SELECT * FROM users WHERE id = {user_id};"
# CORRECT: Use parameterized queries to prevent SQL injection
query = "SELECT id, username, email FROM users WHERE id = %s;"
cur.execute(query, (user_id,)) # Pass parameters as a tuple/list
user_data = cur.fetchone()
return user_data
except Exception as e:
print(f"Database error: {e}")
return None
finally:
if conn:
cur.close()
conn.close()
# Example Usage:
# user = get_user_data_securely(123)
# if user:
# print(f"Found user: {user}")
# else:
# print("User not found or an error occurred.")
# Trying to inject (will fail safely):
# malicious_input = "123 OR 1=1 --"
# user = get_user_data_securely(malicious_input)
# if user:
# print(f"Malicious input result: {user}") # This will likely return None or an error for non-integer ID
How it works: This snippet demonstrates how to prevent SQL injection attacks using parameterized queries with `psycopg2` for PostgreSQL in Python. Instead of concatenating user input directly into SQL strings, parameters are passed separately to the `execute` method. The database driver then safely handles the escaping and insertion of values, ensuring that malicious input is treated as data rather than executable SQL code, thus safeguarding your database from unauthorized access or data manipulation.