Separate SQL structure from input data
What happens when search text is concatenated into a SQL statement? Create only two fictional documents in an in-memory SQLite database and compare input treated as data with input interpreted as part of an expression. No real data, external database, or existing service is involved. The database disappears when the experiment finishes. Begin with the Web security overview and its question of where input becomes executable structure.
The goal is not to remove a few suspicious characters. Keep statement structure under program control and pass values separately through the driver's binding facility. Python 3.12 sqlite3 uses question-mark placeholders in this example. Another driver may require a different convention. These results verify this SQLite experiment; they do not verify PostgreSQL behavior or connectivity for another database tutorial.
Predict three result sets
Document one belongs to A and is titled alpha; document two belongs to B and is titled beta. The deliberately concatenated query accepts input that closes a quote, adds OR 1=1, and comments out the remainder. Predict that leaked contains both documents because the input changes the predicate's meaning. This unsafe construction exists only to demonstrate the boundary in the disposable fixture.
Bind the identical text as a placeholder value and it searches for a title literally equal to that string. No such title exists, so the result is empty. Bind the ordinary title alpha and only document one returns. Record the three predictions before running: two rows for concatenation, zero for bound hostile-looking input, and one for normal input. Rejecting every search would not satisfy the intended behavior.
Run the complete fixture and preserve versions
Save the program as query_boundary.py and run python3.12 query_boundary.py. No additional library is needed. It includes schema creation and fixture insertion. Preserve the version output, result sets, and final success message. All assertions passed with Python 3.12.14 and SQLite 3.53.1: concatenation returned [(1,), (2,)] and binding returned [].
Even one bound value must be supplied using the driver's expected parameter container. The comma in (owner,) creates a one-element tuple. Formatting a value into the statement before execute does not become binding merely because execute is eventually called. Identify the precise API call where SQL text and parameter values are passed separately. Pair that structural observation with the results rather than relying only on a success message.
import sqlite3
with sqlite3.connect(':memory:') as db:
db.execute('CREATE TABLE documents (id INTEGER PRIMARY KEY, owner TEXT, title TEXT)')
db.executemany('INSERT INTO documents VALUES (?, ?, ?)', [(1, 'A', 'alpha'), (2, 'B', 'beta')])
value = "' OR 1=1 --"
unsafe = "SELECT id FROM documents WHERE owner = 'A' AND title = '" + value + "'"
leaked = db.execute(unsafe).fetchall()
assert leaked == [(1,), (2,)]
bound = db.execute('SELECT id FROM documents WHERE owner = ? AND title = ?', ('A', value)).fetchall()
assert bound == []
assert db.execute('SELECT id FROM documents WHERE owner = ? AND title = ?', ('A', 'alpha')).fetchall() == [(1,)]
assert db.execute('SELECT id FROM documents WHERE owner = ? AND title = ?', ('B', 'beta')).fetchall() == [(2,)]
# Binding a caller-supplied owner does not authenticate or authorize that owner.
order_columns = {'title': 'title COLLATE BINARY', 'id': 'id'}
def list_for_owner(owner, order):
if order not in order_columns:
raise ValueError('unknown sort key')
return db.execute('SELECT id FROM documents WHERE owner = ? ORDER BY ' + order_columns[order], (owner,)).fetchall()
assert list_for_owner('A', 'title') == [(1,)]
try:
list_for_owner('A', 'id; DROP TABLE documents')
except ValueError:
pass
else:
raise AssertionError('unknown identifier accepted')
assert db.execute('SELECT count(*) FROM documents').fetchone()[0] == 2
print('SQLite', sqlite3.sqlite_version)
print('concatenated:', leaked, 'bound:', bound)
print('value binding and identifier allowlist passed')Identifiers need a separate rule
A sort selector introduces another boundary. Passing the string title into a value placeholder is not a general facility for choosing a column identifier. The program accepts only the external keys title and id, then maps them to fixed SQL fragments authored inside the program. It selects from an allowlist instead of passing an arbitrary column string through.
An unknown key raises ValueError before query execution, and a later assertion confirms that the table still contains two rows. If a real feature also exposes direction or aggregation choices, give those finite allowed alternatives too. An explicit mapping constrains which structures can be produced more clearly than a loose check for suspicious characters. It also makes additions to the set of selectable columns visible during review.
Binding does not authenticate or authorize an owner
Another assertion binds owner B and title beta and successfully retrieves document two. It demonstrates a limit: parameter binding does not establish the caller's identity or permissions. An API that trusts a caller-supplied owner may expose another person's document without any alteration of SQL structure. The server must derive the permitted scope from an authenticated principal and an authorization policy.
Binding also does not make sensitive values suitable for unrestricted logging. Search terms, tokens, and personal information require a separate decision about diagnostics and monitoring. Transaction boundaries, result limits, and timeouts remain additional responsibilities. The experiment establishes a specific distinction between statement structure and values. It does not certify an entire data-access layer or erase the need to test its other security and operational properties.
Keep a positive identifier-selection case
The fixture tests a permitted title sort as well as an unknown key. An empty allowlist that rejects every selection would not satisfy the feature requirement. To extend the test, add several documents for the same owner with different titles, predict their order, and compare the actual ordered identifiers. That checks whether the allowed key really selects the intended expression rather than merely avoiding an error. Keep owner scope unchanged during this comparison so that ordering and authorization are not changed in the same test.
Exercise and worked boundary explanation
Add a normal title containing an apostrophe and retrieve it through value binding. Pass the original title unchanged; do not remove or manually double the quote. Because it is not inserted into statement structure, it remains ordinary data. Check that the row's owner also belongs to the intended search scope.
Next consider adding a created sort key. Identify the schema change, fixed SQL fragment, and expected test result rather than opening a path for arbitrary caller SQL. Finally ask whether these binding tests prove that an A user cannot submit owner=B and read another person's document. They do not. That requires requests through the authentication and authorization boundary. Include a concrete untested responsibility in the experiment record alongside its successful results, keeping both the verified mechanism and its limits reviewable.