# PYTHON-LAMBDA-SEC-012: Lambda SQL Injection via pymssql cursor.execute()

> **Severity:** CRITICAL | **CWE:** CWE-89 | **OWASP:** A03:2021

- **Language:** Python
- **Category:** AWS Lambda
- **URL:** https://codepathfinder.dev/registry/python/aws_lambda/PYTHON-LAMBDA-SEC-012
- **Detection:** `pathfinder scan --ruleset python/PYTHON-LAMBDA-SEC-012 --project .`

## Description

This rule detects SQL injection vulnerabilities in AWS Lambda functions where
untrusted event data flows into pymssql cursor.execute() calls without proper
parameterization, enabling SQL injection against RDS for SQL Server backends.

Lambda functions connecting to RDS for SQL Server (Microsoft SQL Server) via pymssql
receive event data from API Gateway, SQS, SNS, S3, and other triggers. Fields like
event.get("body"), event.get("queryStringParameters"), and event["Records"] are
attacker-controllable in public-facing deployments. pymssql uses %s (or %d, %f for
typed parameters) as placeholders; when these placeholders are bypassed by embedding
event data directly in the SQL string via concatenation or f-strings, injection
becomes possible.

SQL Server injection is particularly dangerous due to xp_cmdshell, a stored procedure
that executes OS commands. While RDS restricts xp_cmdshell, unrestricted SQL Server
instances allow full OS command execution through SQL injection. Even on RDS, data
exfiltration, authentication bypass, and data manipulation remain fully exploitable.
Lambda functions lack the ORM safety layer that web frameworks provide, making raw
pymssql calls a common injection vector in serverless SQL Server integrations.


## Vulnerable Code

```python
import pymssql

def handler(event, context):
    user_id = event.get('user_id')
    conn = pymssql.connect(server='db', user='sa', password='pass', database='app')
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users WHERE id = '" + user_id + "'")
    return cursor.fetchall()
```

## Secure Code

```python
import json
import pymssql

def get_db_connection():
    return pymssql.connect(
        server='rds-sqlserver.us-east-1.rds.amazonaws.com',
        user='lambda_user',
        password='password',
        database='appdb'
    )

def lambda_handler(event, context):
    body = json.loads(event.get('body', '{}'))
    order_id = body.get('order_id', '')

    # SECURE: Validate type before query
    try:
        order_id = int(order_id)
    except (ValueError, TypeError):
        return {'statusCode': 400, 'body': 'Invalid order ID'}

    conn = get_db_connection()
    cursor = conn.cursor(as_dict=True)

    # SECURE: Use %d placeholder and pass value as a tuple — never in the SQL string
    cursor.execute(
        "SELECT id, product, amount FROM orders WHERE id = %d",
        (order_id,)
    )
    row = cursor.fetchone()
    conn.close()
    return {'statusCode': 200, 'body': json.dumps(row)}

```

## Detection Rule (Python SDK)

```python
from rules.python_decorators import python_rule
from codepathfinder import calls, flows, QueryType
from codepathfinder.presets import PropagationPresets

class DBCursor(QueryType):
    fqns = ["sqlite3.Cursor", "mysql.connector.cursor.MySQLCursor",
            "psycopg2.extensions.cursor", "pymysql.cursors.Cursor"]
    patterns = ["*Cursor"]
    match_subclasses = True

_LAMBDA_SOURCES = [
    calls("event.get"),
    calls("event.items"),
    calls("event.values"),
    calls("*.get"),
]


@python_rule(
    id="PYTHON-LAMBDA-SEC-012",
    name="Lambda SQL Injection via pymssql Cursor",
    severity="CRITICAL",
    category="aws_lambda",
    cwe="CWE-89",
    tags="python,aws,lambda,sql-injection,pymssql,OWASP-A03,CWE-89",
    message="Lambda event data flows to pymssql cursor.execute(). Use parameterized queries.",
    owasp="A03:2021",
)
def detect_lambda_pymssql_sqli():
    """Detects Lambda event data flowing to pymssql cursor."""
    return flows(
        from_sources=_LAMBDA_SOURCES,
        to_sinks=[
            DBCursor.method("execute").tracks(0),
            calls("cursor.execute"),
        ],
        sanitized_by=[
            calls("escape"),
        ],
        propagates_through=PropagationPresets.standard(),
        scope="global",
    )
```

## How to Fix

- Always pass Lambda event data as the second argument to cursor.execute() as a tuple, never by concatenating it into the SQL string.
- Use pymssql's typed placeholders (%s for strings, %d for integers, %f for floats) appropriately to benefit from driver-level type enforcement.
- Grant the Lambda's SQL Server database login the minimum necessary permissions (SELECT on specific tables) and avoid sysadmin or db_owner role membership.
- Store SQL Server credentials in AWS Secrets Manager with automatic rotation rather than in Lambda environment variables.
- Enable RDS SQL Server audit logging to detect unusual query patterns that may indicate active exploitation.

## Security Implications

- **Full Database Exfiltration:** An attacker who controls any SQL fragment can use UNION SELECT or error-based
extraction techniques to read from any table accessible to the Lambda's SQL Server
database user, including user credentials, financial records, session tokens,
and other sensitive data.

- **xp_cmdshell OS Command Execution (Unrestricted SQL Server):** On unrestricted SQL Server instances (not RDS), an attacker with sufficient
database permissions gained through injection can enable and execute xp_cmdshell
to run arbitrary OS commands on the database server. RDS for SQL Server disables
xp_cmdshell, but this risk applies to self-managed SQL Server instances accessible
from Lambda.

- **Linked Server Pivoting:** SQL Server supports linked servers that allow cross-database queries. SQL injection
through a Lambda can exploit linked server configurations to pivot to other database
servers accessible from the SQL Server instance, potentially accessing systems
outside the intended Lambda attack surface.

- **Authentication Bypass:** Lambda functions that verify credentials or check authorization via SQL queries
are vulnerable to ' OR '1'='1'-- style bypasses. This can grant attackers
administrative access by causing the query to return rows regardless of the
actual credential values.


## FAQ

**Q: What placeholder syntax does pymssql use and how does it differ from other drivers?**

pymssql supports typed placeholders: %s for strings/general values, %d for integers,
%f for floats, and %b for binary data. Like other DB-API drivers, these placeholders
must be used by passing values as the second tuple argument to cursor.execute(),
not by using Python's built-in string formatting. Passing values via Python %
string formatting ("%s" % value) performs string substitution at the Python level
before pymssql sees the query, bypassing the driver's parameterization entirely.


**Q: Is RDS for SQL Server vulnerable to xp_cmdshell exploitation via injection?**

Amazon RDS for SQL Server disables xp_cmdshell and prevents it from being enabled.
However, the other impacts of SQL injection (data exfiltration, authentication bypass,
data modification) remain fully applicable on RDS. If your Lambda connects to a
self-managed SQL Server instance (e.g., on EC2), xp_cmdshell exploitation is possible
if the database user has sufficient permissions.


**Q: Should I use pymssql or a different driver for SQL Server in Lambda?**

Both pymssql and pyodbc (with Microsoft ODBC Driver for SQL Server) are commonly
used. Both support parameterized queries via DB-API 2.0. The security principle is
identical for both: pass event data as parameters, never in the SQL string. Consider
SQLAlchemy as an abstraction layer over either driver, which encourages ORM usage
and provides a uniform parameterization interface.


**Q: How do I handle SQL Server-specific syntax like TOP, WITH (NOLOCK), or OFFSET FETCH?**

Static SQL clauses like TOP n, WITH (NOLOCK), or OFFSET n ROWS FETCH NEXT m ROWS
ONLY are fine in the SQL string when n and m are validated integers from event data
passed as parameters (for the actual values). For clauses where only the value
varies, use a %d placeholder. For clauses where the structure varies based on event
input (e.g., dynamic ORDER BY), use an allowlist of permitted values and select
from a pre-built dictionary of safe SQL fragments.


**Q: Can this rule detect injection through Lambda functions that build queries in helper modules?**

Yes. The rule performs inter-procedural analysis and follows taint from the
Lambda event dictionary through utility functions, query builders, and return
values across file and module boundaries. If event data flows through a
build_filter_clause() helper in a separate module to cursor.execute(), the
finding is still reported.


## References

- [CWE-89: SQL Injection](https://cwe.mitre.org/data/definitions/89.html)
- [OWASP SQL Injection Prevention Cheat Sheet](https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html)
- [pymssql documentation](https://www.pymssql.org/pymssql_documentation.html)
- [AWS RDS for SQL Server](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html)
- [OWASP SQL Injection](https://owasp.org/www-community/attacks/SQL_Injection)
- [Python DB-API 2.0 Parameterized Queries](https://peps.python.org/pep-0249/#paramstyle)

---

Source: https://codepathfinder.dev/registry/python/aws_lambda/PYTHON-LAMBDA-SEC-012
Code Pathfinder — Open source, type-aware SAST with cross-file dataflow analysis