Your Login Tests Are Green. What Did cursor.execute Actually Receive?

A Blind Spot in Many Test Suites

A reader on my earlier Dev.to post made the useful point directly: Assert that the query is parameterized before it reaches cursor.execute. That sounds simple. It also exposes a blind spot in many test suites. A response assertion can prove that login behaves correctly. It cannot prove what SQL and parameters reached the database executor.

The Experiment

So I built a small experiment with two login implementations. Both passed the same two behavior tests. Only one passed the parameterization contract test.

The Two Implementations

The First Implementation

Builds SQL with an f-string:

def login(db, username: str, password: str):
    sql = ("SELECT id FROM users " f"WHERE username = '{username}' AND password = '{password}'")
    db.execute(sql)
    row = db.fetchone()
    if row:
        return {"ok": True}, 200
    return {"ok": False}, 401

The Second Implementation

Keeps user values outside the SQL string:

def login(db, username: str, password: str):
    db.execute("SELECT id FROM users WHERE username = ? AND password = ?", (username, password))
    row = db.fetchone()
    if row:
        return {"ok": True}, 200
    return {"ok": False}, 401

Behavior Tests

The behavior tests are still useful. I ran the same two behavior cases against each implementation:

• unsafe_login: valid credentials -> 200, {"ok": True} PASS
• unsafe_login: invalid credentials -> 401, {"ok": False} PASS
• safe_login: valid credentials -> 200, {"ok": True} PASS
• safe_login: invalid credentials -> 401, {"ok": False} PASS

functional_total=4/4

The Problem

The problem is that SQL safety belongs to another boundary: request parameters -> SQL string and parameters -> cursor.execute. A behavior test can stay green while the second path is unsafe.

Capture What cursor.execute Received

The experiment uses a small fake database object. It records the SQL and parameters instead of executing real SQL:

class FakeDb:
    def __init__(self, valid):
        self.valid = valid
        self.sql = None
        self.params = None

    def execute(self, sql, params=None):
        self.sql = sql
        self.params = params

    def fetchone(self):
        return (1,) if self.valid else None

The Contract Test

Then checks the execution boundary:

def assert_parameterized(db, username, password):
    sql = db.sql or ""
    assert "?" in sql
    assert db.params == (username, password)
    assert username not in sql
    assert password not in sql

The Results

For the controlled values in this experiment, the unsafe implementation fails because both values are embedded in the SQL string and params is None. The parameterized implementation passes because the SQL contains placeholders and the values remain in the parameter tuple.

The Exact Syntax Depends on the Database Driver

• SQLite and many supported drivers use ?.
• psycopg commonly uses %s.
• asyncpg commonly uses $1.

Keep the Same Principle

Assert the SQL shape and the parameter payload before execution. For a production test, make the assertion as strict as the implementation allows. An approved query constant plus the expected parameter tuple is usually stronger than checking for one character in the SQL string.

Use a Scanner to Find the Review Candidate

The contract test proves one boundary that I already know how to exercise. A scanner helps find dangerous patterns earlier, before someone writes that test.

I Ran code-audit-cli Against Both Files

The finding points to the f-string SQL construction for human review. It does not prove that every possible exploit succeeds, and it does not replace checking the input source, execution path, authorization behavior, or surrounding login logic.

The Three Tools Have Different Jobs

• Behavior tests protect the response.
• Contract tests protect the execution boundary.
• Scanners locate candidate code paths for human review. None of them makes the other two redundant.

What This Experiment Does Not Prove

This is a controlled demonstration, not a complete login-security review. It does not cover password hashing, account enumeration, rate limiting, account lockout, audit logging, session handling, or authorization. It also does not mean that green tests are useless. It means the test suite should state which contract it is testing. A response contract and an SQL execution contract are not the same contract.

The Practical Check is Short

When login runs, what exact SQL and parameter tuple reaches cursor.execute? If the test cannot answer that, the response assertion may be hiding the most important part of the path.

The Public Rules and Sample Output Are Here

https://github.com/yuan1521913/code-audit-cli

The Scanner Runs Locally and Does Not Upload the Project

If you want to run the same local scanner and inspect its complete source and rules, the licensed source package is here: