What is SQL injection & how to prevent it in Python

SQL injection (SQLi) is still one of the quickest ways for attackers to exfiltrate data or wreck a database schema. This article explains how the flaw arises, shows real‑world attack strings, and walks through the simple but effective counter‑measures every Python developer should adopt.

 

What is SQL Injection

SQL injection is a security vulnerability that lets an attacker insert or alter SQL commands inside a database query by exploiting unvalidated user input.

An attacker can then:

• Read confidential data (password hashes, personal info).
• Modify or delete records or entire tables.
• Execute additional malicious SQL on the server.

 

How Does SQLi End Up in Our Code?

It usually slips in when a query string is built by concatenating, formatting or f‑string‑ing user‑supplied values:

query = f"SELECT * FROM users WHERE username = '{username}'"

Deleting Tables or Data

Input:

'; DELETE FROM users; --

Resulting statement:

SELECT * FROM users WHERE username = '';
DELETE FROM users; --'

All rows are wiped. A similar payload—

'; DROP TABLE users; --

—drops the entire table.

 

Authentication bypass

Input:

' OR '1'='1

Query produced:

SELECT * FROM users WHERE username = '' OR '1'='1'

The condition is always true, so login succeeds for every account.

 

Extracting Hidden Data

Input:

' UNION SELECT name, password FROM admins --

Query produced:

SELECT * FROM users WHERE username = ''
UNION
SELECT name, password FROM admins --'

The attacker merges rows from admins into the result set.

 

Dodging Table Filters

Suppose we page through a huge logs table:

SELECT * FROM logs
WHERE user = '{user}' AND timestamp >= '{date}'

Malicious user input:

' OR 1=1 -- 

Query becomes:

SELECT * FROM logs
WHERE user = '' OR 1=1 -- ' AND timestamp >= '2024-01-01'

Both filters are neutralised; the query now scans the entire table, hammering performance.

 

How to Defend Against SQL Injection

1. Use Parameterised Queries (Placeholders)

If your driver supports it (most do), always parameterise:

# ⚠️ Vulnerable
cursor.execute(
    f"SELECT * FROM logs WHERE user = '{user}' AND timestamp >= '{date}'"
)

# ✅ Safe
cursor.execute(
    "SELECT * FROM logs WHERE user = ? AND timestamp >= ?",  # or %s / :name
    (user, date)
)

The SQL engine treats the placeholders as data, not code, automatically escaping dangerous characters.

 

2. Validate Types Explicitly

If you must interpolate, at least check types:

def validate_int(value: str) -> bool:
    try:
        int(value)
        return True
    except ValueError:
        return False

from datetime import datetime
def validate_date(date_str: str) -> bool:
    try:
        datetime.strptime(date_str, "%Y-%m-%d")
        return True
    except ValueError:
        return False

 

3. Whitelist Safe Characters for Strings

import re
def is_safe_string(text: str) -> bool:
    """Allow only letters, digits and underscores."""
    return re.fullmatch(r"[a-zA-Z0-9_]+", text) is not None

4. Grant the Minimum Privileges Needed

Give your application account read‑only access if it only ever performs SELECT; separate writer accounts for INSERT/UPDATE; never let the web tier own DROP rights.

 

Conclusion

SQL injection is widespread, easy to exploit and catastrophic when ignored. To keep your Python apps safe:

• Parameterise every query.
• Validate that inputs are of the expected type or pattern.
• Limit privileges—the database user should do no more than necessary.

Need a security audit or help refactoring legacy queries? Contact Crow Tech’s experts—we’ll lock the doors for you.