PYTHON
Prevent SQL Injection Using Prepared Statements in Python
Protect your database from SQL injection attacks by implementing parameterized queries (prepared statements) in Python, ensuring user input is safely handled.
import sqlite3
def get_user_data(username):
conn = None
try:
conn = sqlite3.connect('database.db')
cursor = conn.cursor()
# BAD EXAMPLE: Vulnerable to SQL Injection
# query = f"SELECT * FROM users WHERE username = '{username}'"
# cursor.execute(query)
# GOOD EXAMPLE: Using a parameterized query (prepared statement)
query = "SELECT * FROM users WHERE username = ?"
cursor.execute(query, (username,))
user = cursor.fetchone()
return user
except sqlite3.Error as e:
print(f"Database error: {e}")
return None
finally:
if conn:
conn.close()
# Example usage:
# Malicious input attempt:
malicious_username = "' OR '1'='1"
print(f"Attempting to query with malicious input: {malicious_username}")
malicious_user = get_user_data(malicious_username)
print(f"Result with malicious input (should be None if user doesn't exist): {malicious_user}")
# Legitimate input:
legit_username = "alice"
print(f"
Attempting to query with legitimate input: {legit_username}")
legit_user = get_user_data(legit_username)
print(f"Result with legitimate input: {legit_user}")
# To run this, you might need a dummy database first:
# conn = sqlite3.connect('database.db')
# cursor = conn.cursor()
# cursor.execute('''CREATE TABLE IF NOT EXISTS users
# (id INTEGER PRIMARY KEY, username TEXT, email TEXT)''')
# cursor.execute("INSERT OR IGNORE INTO users (username, email) VALUES ('alice', '[email protected]')")
# conn.commit()
# conn.close()
How it works: This Python snippet demonstrates how to prevent SQL Injection attacks using parameterized queries, also known as prepared statements. Instead of directly embedding user input into the SQL query string, placeholders (like `?` for `sqlite3`) are used. The database driver then separates the query structure from the user-provided data, ensuring that the input is treated purely as data and not as executable SQL code. This effectively neutralizes injection attempts, preventing attackers from altering query logic or accessing unauthorized data.