How Python SQLAlchemy Raw Query SQL Injection happens and how to fix it

Introduction

In the skills/last30days/scripts/store.py file, a critical data processing script was found to contain a high-severity SQL injection vulnerability where untrusted input was being concatenated directly with raw SQL query strings. This pattern—while seemingly convenient for dynamic query construction—creates a direct pathway for attackers to manipulate database operations, potentially exposing sensitive data or destroying critical information.

The vulnerability stems from a fundamental misunderstanding of how SQLAlchemy's raw query execution works. When developers use text() or execute() with string concatenation, they bypass SQLAlchemy's built-in protections. The database receives a single string where attacker-controlled data is indistinguishable from command syntax. This is exactly what Semgrep's python.sqlalchemy.security.sqlalchemy-execute-raw-query rule detected in this codebase.

The Vulnerability Explained

The Dangerous Pattern

The vulnerable code in skills/last30days/scripts/store.py was constructing SQL queries by directly embedding variables into query strings:

# VULNERABLE PATTERN (conceptual, based on the vulnerability description)
from sqlalchemy import text

# UNSAFE: String concatenation with untrusted input
user_input = get_untrusted_data()  # Could be: "'; DROP TABLE users; --"
query = "SELECT * FROM records WHERE name = '" + user_input + "'"
result = connection.execute(text(query))

Or using f-strings (equally dangerous):

# UNSAFE: f-string formatting
query = f"SELECT * FROM records WHERE category = '{user_category}' AND value > {min_value}"
result = connection.execute(text(query))

Why This Is Exploitable

The text() function in SQLAlchemy creates a TextualSQL object, but it does not escape or parameterize the string you pass to it. It treats the entire string as raw SQL. When you concatenate user input:

1. The database cannot distinguish code from data—the entire concatenated string is parsed as SQL syntax
2. Attackers can inject SQL metacharacters like single quotes ('), semicolons (;), and comments (--) to break out of the intended query structure
3. Multiple statements can be executed if the database driver allows it, enabling data destruction or privilege escalation

Real-World Attack Scenario

Consider the store.py script processing data from an external source. An attacker provides this input as a "category" value:

'; DELETE FROM audit_logs WHERE 1=1; --

The resulting query becomes:

SELECT * FROM records WHERE category = ''; DELETE FROM audit_logs WHERE 1=1; --' AND value > 100

This executes three operations:
1. A harmless empty selection
2. Complete deletion of all audit logs
3. A commented-out remainder that prevents syntax errors

For a script handling last-30-days analytics data, this could mean:
- Data integrity destruction: Corrupted metrics and reports
- Compliance violations: Missing audit trails for regulated data
- Business intelligence poisoning: Strategic decisions based on manipulated data

The Fix

The remediation replaces string concatenation with SQLAlchemy's parameterized query support using named parameters in text():

Before (Vulnerable)

from sqlalchemy import text

# Line X in skills/last30days/scripts/store.py - VULNERABLE
def get_records_by_category(connection, category, min_value):
    # DANGEROUS: Direct string concatenation
    query = f"SELECT * FROM records WHERE category = '{category}' AND value > {min_value}"
    result = connection.execute(text(query))
    return result.fetchall()

After (Secure)

from sqlalchemy import text

# SECURE: Using named parameters with text()
def get_records_by_category(connection, category, min_value):
    # SAFE: Parameters are bound separately from the query structure
    query = text("SELECT * FROM records WHERE category = :category AND value > :min_value")
    result = connection.execute(query, {"category": category, "min_value": min_value})
    return result.fetchall()

Key Security Improvements

Alternative: SQL Expression Language

For more complex query composition, SQLAlchemy's SQL Expression Language provides programmatic query building:

from sqlalchemy import select, and_
from mymodels import records_table

# Fully programmatic, no string SQL needed
stmt = select(records_table).where(
    and_(
        records_table.c.category == category,
        records_table.c.value > min_value
    )
)
result = connection.execute(stmt)

Alternative: ORM Approach

For most applications, the SQLAlchemy ORM provides the safest and most maintainable option:

from mymodels import Record

# ORM handles all parameterization automatically
records = session.query(Record).filter(
    Record.category == category,
    Record.value > min_value
).all()

Prevention & Best Practices

1. Never Concatenate Into SQL Strings

Establish this as a hard rule in your team:

# ❌ NEVER DO THIS
text(f"SELECT * FROM t WHERE x = '{user_input}'")
text("SELECT * FROM t WHERE x = '" + user_input + "'")
text("SELECT * FROM t WHERE x = '%s'" % user_input)

# ✅ ALWAYS DO THIS
text("SELECT * FROM t WHERE x = :param").bindparams(param=user_input)

2. Use Type-Safe Query Builders

Prefer the SQL Expression Language or ORM over raw SQL when possible. These APIs make SQL injection structurally impossible:

# SQL Expression Language - no string SQL to inject into
from sqlalchemy import select, table, column
t = table('records', column('id'), column('name'))
stmt = select(t).where(t.c.name == user_input)  # Safe by construction

3. Static Analysis in CI/CD

Add Semgrep to your pipeline to catch these patterns automatically:

# .github/workflows/security.yml
- name: Run Semgrep
  uses: returntocorp/semgrep-action@v1
  with:
    config: >-
      p/python
      p/owasp-top-ten

The specific rule that found this vulnerability: python.sqlalchemy.security.sqlalchemy-execute-raw-query

4. Defense in Depth: Least Privilege

Even with parameterized queries, database connections should use principle of least privilege:

# Use read-only users for analytics scripts
# Separate credentials for write operations

5. Security Standards Alignment

Key Takeaways

• Never use f-strings, + concatenation, or .format() with sqlalchemy.text()—the text() function does not escape its input
• The skills/last30days/scripts/store.py fix demonstrates that named parameters (:param_name) are the only safe way to include variable data in raw SQLAlchemy queries
• SQLAlchemy ORM and SQL Expression Language provide injection-proof alternatives that should be preferred for new code
• Static analysis tools like Semgrep can detect these patterns before they reach production, but developer education remains essential
• Parameterized queries separate code from data at the database protocol level, making injection attacks impossible by design

How Orbis AppSec Detected This

Source: Untrusted input entering through data ingestion interfaces in the skills/last30days/scripts/store.py processing pipeline

Sink: sqlalchemy.text() execution calls where the SQL string was constructed via concatenation with unvalidated input

Missing control: No use of SQLAlchemy's parameter binding feature; input was interpolated directly into query strings rather than passed as bound parameters

CWE: CWE-89: SQL Injection

Fix: Replaced string concatenation in SQL query construction with SQLAlchemy TextualSQL using named parameters (:param_name), ensuring all user input is treated as data values rather than executable SQL syntax

Orbis AppSec automatically detected this vulnerability and opened a pull request with the fix. Try Orbis AppSec on your repositories to find and fix issues like this automatically.

Conclusion

The SQL injection vulnerability in skills/last30days/scripts/store.py illustrates a persistent and dangerous pattern: developers reaching for string formatting when SQLAlchemy provides safer, more powerful alternatives. The fix—adopting named parameters with text()—eliminates the attack surface while maintaining the flexibility of raw SQL where needed.

For teams building data processing pipelines, this case reinforces that convenience is not worth compromising database security. The few extra characters to write :param_name instead of f'{value}' are the difference between resilient code and a potential breach. Make parameterized queries your default, and treat any raw SQL string construction as a code smell requiring careful review.

References

• CWE-89: SQL Injection
• OWASP SQL Injection Prevention Cheat Sheet
• SQLAlchemy 2.0 Documentation: Using Textual SQL
• SQLAlchemy 2.0 Documentation: SQL Expression Language
• Semgrep Rule: python.sqlalchemy.security.sqlalchemy-execute-raw-query
• GitHub PR: harden: detected non-static command inside command in run.go