PYTHON
Preventing SQL Injection with Parameterized Queries (Python/SQLAlchemy)
Secure your Python web applications against SQL injection by utilizing parameterized queries with SQLAlchemy, a best practice for database interactions.
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
# For demonstration, use an in-memory SQLite database
# In production, this would be a real database like PostgreSQL, MySQL, etc.
engine = create_engine('sqlite:///:memory:')
# Create a simple table
with engine.connect() as connection:
connection.execute(text(
"CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, email TEXT)"
))
connection.commit()
Session = sessionmaker(bind=engine)
session = Session()
# --- SECURE WAY: Using parameterized queries ---
def get_user_by_username_secure(username_input):
# Using text() with .bind_param() for named parameters
# or simply passing parameters as a dictionary to .execute()
stmt = text("SELECT id, username, email FROM users WHERE username = :username_param")
result = session.execute(stmt, {"username_param": username_input}).fetchone()
return result
# Add some sample data
session.execute(text("INSERT INTO users (username, email) VALUES (:user, :email)"),
{"user": "alice", "email": "[email protected]"})
session.execute(text("INSERT INTO users (username, email) VALUES (:user, :email)"),
{"user": "bob", "email": "[email protected]"})
session.commit()
# Simulate user input
user_input = "alice"
secure_user = get_user_by_username_secure(user_input)
print(f"Secure query result for '{user_input}': {secure_user}")
# Simulate malicious input (SQL injection attempt)
malicious_input = "bob' OR 1=1; --"
# This will NOT work with parameterized queries; it will search for a username literally matching "bob' OR 1=1; --"
secure_malicious_attempt = get_user_by_username_secure(malicious_input)
print(f"Secure query result for malicious input: {secure_malicious_attempt}")
session.close()
How it works: This Python snippet demonstrates how to prevent SQL injection attacks using parameterized queries with SQLAlchemy. Instead of concatenating user input directly into SQL strings, parameterized queries separate the SQL logic from the data. The database driver then handles escaping the parameters safely, ensuring that malicious input like `' OR 1=1; --` is treated as a literal string value rather than executable SQL code, thus protecting your database from unauthorized access and manipulation.