SC010: SQLInjection

Overview

Property Value
ID SC010
Name SQLInjection
Group security
Severity ERROR

Description

Detects potential SQL injection vulnerabilities by scanning for string-formatted SQL queries.

SQL injection is one of the most dangerous security vulnerabilities:

  • Data theft: Attackers can extract sensitive database contents
  • Data manipulation: Unauthorized modification or deletion of data
  • Authentication bypass: Attackers can bypass login checks
  • Command execution: Some databases allow OS command execution

What it checks

The check scans all Python files (excluding test files, .venv/, and __pycache__/) for SQL queries constructed using string formatting:

  • f-strings with SQL keywords: f"SELECT * FROM users WHERE id = {user_id}"
  • .format() on SQL strings: "SELECT * FROM users WHERE id = {}".format(user_id)
  • % formatting on SQL strings: "SELECT * FROM users WHERE id = %s" % user_id

SQL keywords detected

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • DROP

Result states

  • PASSED: No SQL injection patterns found
  • FAILED: Potential SQL injection vulnerabilities detected

Safe patterns (not flagged)

Parameterized queries using proper placeholders are not flagged:

# Safe - using ? placeholders (sqlite)
cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))

# Safe - using %s placeholders (psycopg2)
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))

# Safe - using named placeholders (SQLAlchemy)
session.execute("SELECT * FROM users WHERE id = :id", {"id": user_id})

How to fix

Use parameterized queries

# DANGEROUS - SQL injection vulnerability
user_input = get_user_input()
query = f"SELECT * FROM users WHERE username = '{user_input}'"
cursor.execute(query)

# SAFE - parameterized query
user_input = get_user_input()
cursor.execute(
    "SELECT * FROM users WHERE username = ?",
    (user_input,)
)

Use ORM query builders

# DANGEROUS - string interpolation
from sqlalchemy import text
username = get_user_input()
query = text(f"SELECT * FROM users WHERE username = '{username}'")

# SAFE - SQLAlchemy ORM
from sqlalchemy.orm import Session
from models import User

def get_user(session: Session, username: str):
    return session.query(User).filter(User.username == username).first()

# SAFE - SQLAlchemy Core with bound parameters
from sqlalchemy import select, bindparam
stmt = select(users).where(users.c.username == bindparam("username"))
result = connection.execute(stmt, {"username": username})

Use Django ORM

# DANGEROUS - raw SQL with interpolation
username = request.GET.get("username")
User.objects.raw(f"SELECT * FROM users WHERE username = '{username}'")

# SAFE - Django ORM querysets
User.objects.filter(username=username)

# SAFE - Django raw with params
User.objects.raw(
    "SELECT * FROM users WHERE username = %s",
    [username]
)

Use psycopg2 properly

import psycopg2

# DANGEROUS
user_id = get_user_input()
cursor.execute(f"SELECT * FROM users WHERE id = {user_id}")

# SAFE - using %s placeholder (NOT string formatting!)
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))

# SAFE - using named placeholders
cursor.execute(
    "SELECT * FROM users WHERE id = %(user_id)s",
    {"user_id": user_id}
)

Use sqlite3 properly

import sqlite3

# DANGEROUS
user_id = get_user_input()
cursor.execute(f"SELECT * FROM users WHERE id = {user_id}")

# SAFE - using ? placeholder
cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))

# SAFE - using named placeholders
cursor.execute(
    "SELECT * FROM users WHERE id = :id",
    {"id": user_id}
)

Understanding the attack

# Vulnerable code
username = get_user_input()  # User enters: admin' OR '1'='1
query = f"SELECT * FROM users WHERE username = '{username}'"
# Resulting query:
# SELECT * FROM users WHERE username = 'admin' OR '1'='1'
# This returns ALL users!

# Even worse - user enters: '; DROP TABLE users; --
query = f"SELECT * FROM users WHERE username = '{username}'"
# Resulting query:
# SELECT * FROM users WHERE username = ''; DROP TABLE users; --'
# This deletes the entire users table!

Why ERROR severity?

This check is an ERROR because:

  • SQL injection is a critical security vulnerability
  • It can lead to complete database compromise
  • It’s consistently in the OWASP Top 10
  • The fix is straightforward (use parameterized queries)

Configuration

Skip this check

[tool.pycmdcheck]
skip = ["SC010"]

CLI

pycmdcheck --skip SC010

Limitations

This check uses heuristics and may:

  • False positives: Flag safe string building that doesn’t reach the database
  • False negatives: Miss SQL injection through indirect string construction

For comprehensive security testing, use specialized tools like:

References