Introduction
A name supplied by a user should remain a value, not become part of a database command. This DeskLite lookup currently builds SQL text directly from a name. Ordinary requests appear to work, but quoted names and specially shaped input reveal the problem.
You will read only a five-line Python function, learn the small amount of SQL it uses, follow a real Semgrep finding, and apply one SQLite parameter-binding pattern. No prior Python or SQL knowledge is required. The runner, database, scanner, and functional check are already prepared.
Run an Ordinary Lookup
In this step, you will establish what the lookup is supposed to return before reading its implementation.
DeskLite stores three fictional tickets in a small SQLite database. SQLite is a database engine that reads and queries a local file. The course has already prepared the connection and data.
cd /home/labex/project/ticket-queries
run_lookup.py is a prepared entry point. As in the dependency lab, python3 runs a supplied program. The last argument is the name to look up:
python3 run_lookup.py Alice
Expect one ticket with ID 1 and title Printer offline. The runner formats database rows into the familiar JSON response. You will edit the small lookup function, not the runner's formatting code.
The intended boundary is simple: the supplied name selects that owner's tickets. It should not change the meaning of the database command or reveal other owners' tickets. All names and records in this exercise are fictional; work only with this prepared local database.
Read the Five-Line Function
In this step, you will follow the supplied name through a small Python function.
cat lookup.py
The file contains:
from fixture import db
def lookup(name):
query = f"SELECT id, title FROM tickets WHERE owner = '{name}'"
return db.execute(query).fetchall()
from fixture import db makes the prepared database connection available under the name db. A function is a named piece of reusable behavior. def lookup(name): defines a function whose input is called name. The indented lines belong to that function; keep their indentation when editing.
A variable names a value. query = ... assigns a text value, called a string, to query. The leading f makes this an f-string: Python inserts the current name value at {name}. If the input is Alice, that text becomes part of the SQL string.
db.execute(query) is a function-like method call that asks the connection to run the query. .fetchall() collects its result rows. return sends those rows back to the caller. The prepared runner passes your terminal argument to lookup and displays those returned rows.
You only need this data path: supplied name → query string → database call → returned rows. Next you will read what that SQL string means.
Read the Intended SQL Condition
In this step, you will connect the SQL condition to the records that should be returned.
SQL is a language for requesting database operations. This query uses only a small part of it:
SELECT id, title FROM tickets WHERE owner = 'Alice'
SELECT id, title chooses the two result fields. FROM tickets chooses the prepared table. WHERE owner = 'Alice' limits results to rows whose owner equals that text value. In SQL, the single quotes mark a text literal. The = inside this SQL string expresses the owner condition. Python's query = ... assignment outside the string gives the completed text a variable name.
Try another ordinary name:
python3 run_lookup.py Bob
Expect only ticket ID 3, titled Spare keyboard. The name changes the intended value of the owner condition, not the selected columns or the table.
Now connect this to the f-string you read. Python inserts the supplied name between the SQL quotes before SQLite sees the query. That combines two different things: the command's structure and input data. A quote inside a real name can interfere with those boundaries.
Do not edit yet. You will first use the fixed scanner to locate the risky construction, then confirm the behavior with a legitimate quoted name and an authorized local test input.
Follow the Static Analysis Finding
In this step, you will read a real Semgrep finding and connect its location to the query construction.
Static application security testing (SAST) examines code without running every possible input. The prepared course-code-scan launcher uses the pinned Semgrep CE 1.145.0 engine and a fixed local rule. There is no cloud account or online ruleset.
course-code-scan lookup.py
Expect a finding at lookup.py:5, with rule ID desklite.sql-parameter-binding and this explanation:
Bind the supplied lookup name as a SQL parameter instead of building SQL text from it.
The line points to use of query in the database call. Follow that value one line upward to see where the f-string was built. This is a short data-flow investigation, not a request to develop a full application.
A finding exits 1; CHECK ERROR exits 2 when the file cannot be analyzed. Neither is a clean result. The local rule tracks this course's supplied name into SQL text. Its scope is limited: a finding still needs code review, and no finding under one rule does not prove that an application has no other risks.
Next you will compare the finding with actual lookup behavior.
Compare Quoted Text with Query-Changing Input
In this step, you will observe why input must remain data, using only the prepared local fixture.
A legitimate owner is named O'Neil. Double quotes keep that whole terminal argument together, including its apostrophe:
python3 run_lookup.py "O'Neil"
Expect a line beginning Lookup failed: with a SQLite syntax error. This failure is intentional evidence of the defect. The name's apostrophe ended a SQL text literal because the program inserted it directly into command text. Rejecting every apostrophe would break a real owner's lookup.
Now try this bounded test input:
python3 run_lookup.py "' OR 1=1 --"
The unsafe version returns all three tickets instead of no matching owner. In this SQL context, the quote closes the literal, OR 1=1 makes the condition true, and -- comments out the remaining text. The input changed command structure rather than staying a name. This is SQL injection.
You are not testing an external service or changing data. The prepared database is read only and contains fictional records. The required repair must satisfy both observations: a legitimate quoted name works, while query-changing text cannot broaden the returned rows.
Bind the Name Separately from SQL Text
In this step, you will repair the lookup using SQLite's parameter-binding interface.
A placeholder marks where a data value belongs. SQLite's ? placeholder is part of fixed SQL structure; the name is supplied separately to execute. The database driver treats the supplied value as data, even if it contains quotes or SQL-looking characters.
nano lookup.py
Replace the function's query and execute lines with this supported pattern, preserving the import, function name, and indentation. Save with Ctrl+O, Enter, Ctrl+X:
from fixture import db
def lookup(name):
query = "SELECT id, title FROM tickets WHERE owner = ?"
return db.execute(query, (name,)).fetchall()
Do not put SQL quotes around ?. The driver handles the value's representation. (name,) is a one-element tuple, an ordered collection of values. The trailing comma makes it a tuple; its one value corresponds to the one placeholder. The second argument to execute carries that tuple separately from the query text.
Run an ordinary lookup:
python3 run_lookup.py Alice
Then retry the legitimate quoted name:
python3 run_lookup.py "O'Neil"
Expect ticket ID 1 for Alice and ticket ID 2, titled Login issue, for O'Neil. Both use the same fixed SQL structure. If Python reports an indentation or syntax error, restore the short function shown above; do not modify the database or runner.
Verify Both Rejection and Legitimate Behavior
In this step, you will confirm that the repair preserves valid names and prevents the supplied text from broadening the lookup.
Repeat the query-changing input:
python3 run_lookup.py "' OR 1=1 --"
Now expect an empty tickets list. SQLite searches for an owner literally named by that text, and none exists. No query structure was added.
Check an ordinary missing owner:
python3 run_lookup.py "Missing owner"
Expect another empty list. A missing value is different from a broken application: ordinary and legitimate quoted names still return their own tickets.
Run the provided functional regression check using the same entry style as the dependency lab:
bash test.sh
Expect:
PASS: ordinary, quoted, missing, and injection-like names.
These checks exercise real SQLite calls and result rows. Removing the function, returning an empty list for every input, or deleting data would not preserve the legitimate results. A character blacklist is also insufficient: a real owner has an apostrophe. The intended defense is the separation of fixed SQL text and bound values.
Rescan the Repaired Source
In this step, you will combine the static result with the actual lookup behavior you just tested.
course-code-scan lookup.py
Expect No findings under the fixed local rule. The supplied name no longer flows into the SQL command string. It is passed as the separate bound value.
Re-run the functional check:
bash test.sh
Expect the same passing result. The scan and behavior test answer different questions: one examines the targeted code pattern; the other checks that real names still work and special input cannot expand the result.
The course verification also observes the actual SQLite arguments, so merely hiding a scanner finding or manually replacing quote characters is not the required repair. Keep the original database, fixture, runner, and function interface. No written investigation file is needed.
This lab taught one small SQLite binding pattern and one local SAST rule. A complete application review would also consider other input sources and vulnerability classes. Here, you have removed the specific input-to-query risk while preserving the intended lookup.
Summary
You traced a supplied name through a short Python lookup and SQL condition, connected a real local Semgrep finding to runtime behavior, and used SQLite parameter binding. Ordinary and quoted names still work, while query-changing input cannot broaden the results. Static and functional evidence support the specific repaired boundary.
