How can you prevent SQL injection vulnerabilities in your Python code?

Using parameterized queries is the core method to prevent SQL injection in Python, and it is necessary to avoid direct splicing of user input. 1. Always use parameterized queries supported by database drivers, such as the placeholder mechanisms in sqlite3, psycopg2, and mysql-connector-python; 2. It is prohibited to construct SQL statements through string splicing or formatting (such as f-string, .format()); 3. Priority is given to the use of ORM libraries such as SQLAlchemy or Django ORM, which use parameterized queries by default; 4. Verification and filtering of user inputs as supplementary defenses, including type, length, and whitelist verification; 5. When dealing with dynamic table names or column names, the whitelist mechanism must be used to ensure that only predefined identifiers are allowed. As long as you always insist on parameterized queries and avoid directly embedding user input into SQL strings, you can effectively eliminate SQL injection vulnerabilities.

Preventing SQL injection vulnerabilities in Python comes down to one core principle: never trust user input when building SQL queries . The most effective way to protect your application is by avoiding string concatenation or formatting to include user data directly in SQL statements. Here's how to do it right.

Use Parameterized Queries (Prepared Statements)

The best and most reliable defense is to use parameterized queries with placeholders. This ensures that user input is treated strictly as data, not executable code.

Most Python database libraries support parameterized queries. For example, with sqlite3 :

 import sqlite3

conn = sqlite3.connect("example.db")
cursor = conn.cursor()

# ✅ Safe: Using parameterized query
username = input("Enter username: ")
cursor.execute("SELECT * FROM users WHERE username = ?", (username,))

# ❌ Dangerous: Don't do this
# cursor.execute(f"SELECT * FROM users WHERE username = '{username}'")

With psycopg2 (PostgreSQL):

 import psycopg2

cursor.execute("SELECT * FROM users WHERE email = %s", (email,))

With MySQL using mysql-connector-python :

 cursor.execute("SELECT * FROM users WHERE email = %s", (email,))

Always use the database driver's built-in parameterization — not Python string formatting like .format() , f-strings, or % .

Avoid Dynamic Query Building with User Input

Never build SQL queries by concatenating strings that include user-supplied data.

 # ❌ Vulnerable to injection
query = "SELECT * FROM users WHERE username = '" username "'"
cursor.execute(query)

Even if you try to sanitize input yourself, you'll likely miss edge cases. Let the database driver handle it via parameters.

Use ORM Libraries When Possible

Object-Relational Mappers (ORMs) like SQLAlchemy or Django ORM automatically use parameterized queries under the hood, making it much harder to introduce SQL injection.

Example with SQLAlchemy:

 from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()

# ✅ Safe
user = session.query(User).filter(User.username == username).first()

Example with Django:

 User.objects.filter(username=request.GET['username'])

These frameworks abstract away raw SQL and enforce safe practices by default.

Validate and Sanitize Input (Defense in Depth)

While parameterized queries are the main line of defense, you should still validate input types, lengths, formats, and acceptable values where possible.

For example:

• Ensure an ID is numeric.
• Limit string lengths.
• Use allowlists for things like sort directions ( ASC / DESC ) or table/column names if they must be dynamic.

But remember: input validation alone cannot stop SQL injection — it's a supplement, not a replacement for parameterization.

Handle Dynamic Table or Column Names Carefully

Sometimes you need to dynamically include table or column names (which can't be parameterized). In such cases:

• Use an allowlist of permitted identifiers.
• Never pass raw user input directly.

 allowed_columns = {'name', 'email', 'created_at'}
if column not in allowed_columns:
    raise ValueError("Invalid column name")

query = f"SELECT * FROM users ORDER BY {column}"
cursor.execute(query)

This way, only pre-approved values are used.

Basically, stick to parameterized queries for all variable data, use ORMs when you can, and never embedded user input directly into SQL strings. It's not complex — just consistent.

The above is the detailed content of How can you prevent SQL injection vulnerabilities in your Python code?. For more information, please follow other related articles on the PHP Chinese website!