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

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.

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 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

The difference is not visible in a normal successful response. Both implementations can return 200 for valid credentials and 401 for invalid credentials.

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

Across the two implementations, all four behavior test executions passed.

Those tests should stay. They protect the response contract:

request parameters -> response body and status

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

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.

PARAMETERIZATION CONTRACT TEST
unsafe_login: FAIL
params=None
sql="SELECT id FROM users WHERE username = 'alice' AND password = 'correct-password'"

safe_login: PASS
params=('alice', 'correct-password')
sql='SELECT id FROM users WHERE username = ? AND password = ?'

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:

CODE-AUDIT-CLI SCAN
unsafe_login: findings=1
  severity=high pattern=sql-concat line=6

safe_login: findings=0

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:

code-audit-cli source package

What does your test suite capture at cursor.execute?