Let a Search Value Stay a Value

A technician types an asset name into a search box. The application pastes it into a SQL string. A quote in that name can change how the database reads the statement instead of remaining ordinary text.

We will bind values through the database driver, keep the tenant scope supplied by trusted application state, and expose only the fields the interface needs. The SQLite exercise demonstrates a scoped view; a deployed service also needs real database and application permissions.

Filter the record set by trusted tenant scope before matching the supplied asset value.

The query structure says which table and fields are involved and how rows are filtered. The asset name is a value placed into that fixed structure. Driver placeholders preserve that distinction.

Manually adding quotation marks or escaping a few characters is easy to get wrong across dialects and encodings. Use the driver’s binding API. A parameter containing a quote or SQL-looking text is then compared as a value rather than becoming a new predicate.

The exercise uses a local database and harmless fixture strings. It does not connect to any external service.

Binding tenant and asset values prevents them from changing SQL syntax. It does not prove that the caller may choose any tenant. Obtain the tenant from authenticated server-side context and enforce access again at the data boundary appropriate to the service.

Do not place database credentials in a browser worksheet or give a model unrestricted SQL access because its outputs pass a schema check. A model-generated query is still code whose available operations and data access need deliberate limits.

The workshop shows exact record matching. It does not interpret strings as SQL commands.

Python sqlite3 accepts placeholders such as ? and a separate parameter sequence. A single parameter still needs a one-item sequence. fetchall returns a list of row tuples in the requested order.

The programming exercise writes find_jobs(connection, tenant, asset). Its tenant argument represents scope already established by the caller; the function does not implement login or authorisation. The SQL string remains fixed while the parameters change.

Placeholder spelling varies between database drivers. Follow the driver used by the application.

import sqlite3
connection = sqlite3.connect(":memory:")
connection.execute("CREATE TABLE assets(id TEXT)")
connection.execute("INSERT INTO assets VALUES(?)", ("P'7",))
print(connection.execute("SELECT id FROM assets WHERE id = ?", ("P'7",)).fetchall())

[("P'7",)]

A value placeholder does not generally substitute a table name, column name or sort-direction keyword. If the interface offers a sort order, map its allowed choices to known query fragments or use the driver’s supported identifier-composition API.

Do not convert an arbitrary submitted string into a column identifier and assume value binding elsewhere protects it. Keep the list of accessible fields narrow. The safe design depends on which parts are data and which parts would change executable structure.

An allowlist chooses among known structural options. It is not an attempt to sanitise arbitrary SQL.

Our SQL exercise creates a view containing only north’s open jobs and the fields used by the queue. It demonstrates projection and a stable predicate in one named relation. Inspecting the view makes accidental private_note inclusion easy to notice.

SQLite does not provide PostgreSQL-style server users and GRANT privileges. A local caller with direct access to this database can still read the underlying table. In a server database, appropriate roles and grants can restrict access to the view or approved operations; application checks and, where suitable, row-level security add separate controls.

A view is not an authentication system.

CREATE TABLE jobs(id TEXT PRIMARY KEY NOT NULL,tenant TEXT NOT NULL,asset_id TEXT NOT NULL,status TEXT NOT NULL,private_note TEXT);
INSERT INTO jobs VALUES('J1','north','P7','open','private A'),('J2','south','P8','open','private B'),('J3','north','P9','done','private C');

SELECT id,asset_id,status FROM jobs WHERE tenant='north' AND status='open' ORDER BY id;

Use separate credentials or roles for migrations, application writes and read-only reporting where the architecture supports them. A report process rarely needs permission to change schemas. Revoke unnecessary inherited or public grants and inspect effective privileges rather than only the most recent GRANT statement.

PostgreSQL row-level security can constrain visible rows under defined policies, with important ownership and privileged-role exceptions. Treat it as a configured database feature to test, not a promise made by a WHERE clause in one code path.

The browser examples do not configure production roles or claim to demonstrate complete tenant isolation.

A parameterised query can still be expensive or return too much permitted data. Apply pagination, response-size limits and bounded execution appropriate to the service. Avoid logging secrets or unnecessary row content while retaining enough identifiers to investigate access decisions.

Test a literal quote, an SQL-looking asset name, an absent asset, and the same asset ID in another tenant. Those cases check binding and scoping separately. Operational testing must additionally exercise the actual deployed role and every exposed endpoint.

Correct syntax handling is one necessary boundary in a larger access design.