DEV Community: yuan ming

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

yuan ming — Sat, 12 Sep 2026 12:42:32 +0000

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?

I Stopped a Raft Leader in a 3-Node Go KV Store. Here Is the Evidence.

yuan ming — Sat, 12 Sep 2026 12:35:26 +0000

Most Raft projects look healthy when every node is still running.

The useful question is narrower: after a leader stops, what exactly can the
remaining nodes prove?

I built RaftKV, a teaching-grade three-node Raft sharded key-value store in Go.
For this test I did not want another architecture diagram. I wanted a recorded
flow with a committed key, a stopped node, a failover, a read, and a new write.

This post walks through that flow and separates the evidence from the claims the
test does not support.

The cluster

RaftKV starts three independent node processes:

node-a, node-b, and node-c
two shards: shard-0 and shard-1
one Raft group per shard
three replicas in each Raft group
real HTTP RPCs for RequestVote and AppendEntries
client requests accepted by any node, then forwarded to the shard leader

The important part is that the nodes are separate processes. A leader election
is not a method call inside one test binary.

Before the fault, the health endpoint returned:

{
  "cluster": "RaftKV Trial 2026-09-06",
  "leaders": 2,
  "maintenance": false,
  "shards": 2,
  "status": "ok",
  "version": "raft-kv-1.0.2"
}

Two shards, two leaders, three live nodes.

Step 1: commit a key

I wrote a key through the public HTTP API:

trial:user:1001 = RaftKV-ok

The key was routed to shard-1. The write had to go through the shard leader,
reach a Raft majority, commit, and then apply to the state machine.

This distinction matters. A value stored in memory on one node is not the same
as a committed Raft entry replicated to a majority.

Step 2: stop node-a

While the cluster was healthy, I stopped node-a, which had node_id 0.

I did not stop a request handler or change a flag in a test. The process
disappeared.

Step 3: ask the surviving nodes

After waiting for a new election, both surviving nodes reported:

{
  "cluster": "RaftKV Trial 2026-09-06",
  "leaders": 2,
  "maintenance": false,
  "node_id": 2,
  "shards": 2,
  "status": "ok",
  "uptime_s": 81,
  "version": "raft-kv-1.0.2"
}

The affected shard had a new leader. The old leader was no longer part of the
live majority.

Step 4: read the committed key

The previously committed key was still available:

{
  "found": true,
  "key": "trial:user:1001",
  "shard": "shard-1",
  "value": "RaftKV-ok"
}

This is the part that connects Raft theory to an operational result. The key
survived because it had already been committed by a majority, not because one
process happened to keep a copy in memory.

Step 5: write again

A cluster that can only read after failover is not enough. I wrote a new key:

failover:after:node0 = still-writable

The new value was read back successfully.

The recorded evidence summary is:

RaftKV fault evidence: stopped node-a (node_id 0), then queried node-b and node-c.
health post: {"cluster":"RaftKV Trial 2026-09-06","leaders":2,"maintenance":false,"node_id":2,"shards":2,"status":"ok","uptime_s":81,"version":"raft-kv-1.0.2"}
read post: RaftKV-ok
write post: still-writable

What this test proves

In this implementation and this recorded environment, a three-node Raft group
continued to serve committed data after one node stopped. A new leader was
elected, the old committed value was readable, and the cluster accepted a new
write.

The same public evidence repository also records:

three-node HTTP Raft read and write tests
follower restart and catch-up coverage
restart recovery
dynamic shard add and migration recovery
snapshot restore
concurrent write visibility

Raw files include:

evidence/trial-health-pre.json
evidence/trial-status-pre.json
evidence/trial-read-post.json
evidence/trial-write-post.json
evidence/fault-post-summary.txt
evidence/go-test-output.txt

What it does not prove

I do not treat this experiment as proof that RaftKV is production-ready.

It does not prove:

correct behavior under every network partition
disk-failure or fsync-failure correctness
cross-data-center replication
production-grade consistency guarantees
performance under production traffic

RaftKV is teaching-grade and portfolio-grade software. It is not a replacement
for Redis, etcd, or TiKV.

That boundary is not a disclaimer added at the end. It changes how the project
should be reviewed. The goal is to make the implementation and failure behavior
inspectable, not to pretend a small system has covered every production failure
mode.

Why this is a better project story

A project description that says "implemented Raft, sharding, and failover" is
difficult to verify.

A stronger story separates four things:

The design choice.
The failure that was injected.
The observed result.
The remaining uncertainty.

For a backend interview, that structure also creates better follow-up
questions. Why did the committed key survive? What happens to an uncommitted
entry? What happens when the old leader restarts? What still breaks under a
network partition?

Those questions are more useful than another list of features.

Reproduce it

The public evidence repository is here:

https://github.com/yuan1521913/raft-kv

The full Go source, tests, Docker Compose setup, management dashboard, and
teaching materials are part of the licensed source package.

If you want to run the Windows trial first:

https://5552463341538.gumroad.com/l/raftkv-trial?utm_source=devto&utm_medium=article&utm_campaign=raftkv_launch

If you build distributed systems, I would be interested in the failure test you
consider mandatory before trusting a Raft implementation.

An AI-generated login endpoint "works" - I still found SQL concatenation before launch

yuan ming — Thu, 10 Sep 2026 09:47:45 +0000

Functional tests passing is not the same as code being safe to ship. Here is a reproducible case: an AI-generated login endpoint that behaves correctly, the scan finding I checked before launch, and the fix that made the pattern disappear.

The code runs, but I would not ship it like this

This is a local demo equivalent of a login endpoint, not live production code and not a client project:

username = request.form.get("username", "")
password = request.form.get("password", "")

sql = f"SELECT id FROM users WHERE username = '{username}' AND password = '{password}'"
cursor.execute(sql)
row = cursor.fetchone()

if row:
    return {"ok": True}
return {"ok": False}, 401

It works: correct credentials return success. My concern is not whether it works today. It is that username and password come from an HTTP request and are placed directly inside an SQL string.

A first-pass scan finds the pattern to check

I did not read every line first. I ran a local quick scan:

code-audit app --format html --output report.html

One result was:

Severity	Rule	Risk	Location
High	`sql-concat`	SQL assembled from strings, user input can reach the query	`app/login.py:12`

A high finding is not an automatic conclusion. I confirm it in four steps.

1. Where does the input come from?

username and password come from request.form. That means a client can submit arbitrary values. Input from HTTP requests is untrusted by default.

2. Where does it go?

The values skip length checks, type checks, escaping and parameterization, then enter the SQL template:

sql = f"SELECT id FROM users WHERE username = '{username}' AND password = '{password}'"
cursor.execute(sql)

The source is a request parameter. The sink is SQL execution. There is no boundary between them.

3. Can it be exploited?

I do not attack my own project. I reason through SQL syntax:

If username contains:

' OR '1'='1

the resulting SQL can become:

SELECT id FROM users WHERE username = '' OR '1'='1' AND password = '{password}'

I cannot prove that every login is bypassable. I can confirm there is a suspicious path that does not require advanced exploitation. For a login endpoint, fixing it before launch is cheaper than investigating it after launch.

4. Use a parameterized query

The fix is not filtering single quotes. It is keeping user input out of the SQL string:

cursor.execute(
    "SELECT id, password_hash FROM users WHERE username = %s",
    (username,),
)
row = cursor.fetchone()

if row and verify_password(password, row["password_hash"]):
    return {"ok": True}
return {"ok": False}, 401

This also changes the login flow to fetch the password hash by username first and verify the password separately, instead of putting the plaintext password into SQL.

After the fix, the sql-concat High finding no longer appears.

A clean scan is not "absolutely safe"

A clean scan only means this round did not find that rule pattern. It does not mean:

There are no other vulnerabilities.
The login flow meets every production security requirement.
You can skip rate limiting, lockout policy, audit logging and access checks.

Pre-launch checking is a process, not a verdict: scan for suspicious locations, confirm with a human, fix what can be fixed, and list what still needs review.

Evidence you can inspect

The rules, sample output and boundaries are public on GitHub: https://github.com/yuan1521913/code-audit-cli

The full source package is a separate licensed delivery. If you want a local scanner that produces Markdown, JSON, HTML or SARIF reports and can act as a CI gate, the English source package is here: https://5552463341538.gumroad.com/l/code-audit-cli-source

The scanner runs locally and does not upload your code. Automatic results still need human confirmation.