Bound values and safe query boundaries

A bound parameter supplies a value without making that value part of the SQL grammar. The worked query uses two dollar-numbered placeholders for country and active status. The lab binds UA and one through the driver, producing identities 1 and 2.

The displayed SQL is a parameterized statement, not a standalone console command with invented values. The companion runner supplies the declared binding arrays. In an application, use the actual driver's parameter API rather than replacing placeholder text manually.

The SQL structure remains fixed while the driver transmits the country as data. An input such as UA followed by a quote and an OR expression must stay a value, not become a new predicate. The lab separately tests that adversarial bound string and expects no matching country.

Placeholders bind values, not arbitrary table names, column names or ordering directions. Dynamic identifiers need a small allowlist and the driver's appropriate identifier mechanism. Validate a country code for product meaning as well; injection resistance does not by itself enforce a business input contract.

The counterexample illustrates an already-interpolated OR expression in the SQL text. Its predicate broadens the output instead of matching only the requested country. Escaping one example string by hand is not a robust replacement for the driver's value-binding boundary.

Do not put a placeholder inside SQL string quotes and expect it to bind. That creates a literal placeholder-looking value. Also distinguish safe binding from LIKE wildcard escaping: a bound percent can still deliberately act as a pattern wildcard under a separate matching policy.

Execute the same parameterized statement with US and one, without changing its SQL text. Predict identities 3 and 4. The independent task is deliberately small: preserve the structure and replace only the binding values in the local runner's practice case.

Then inspect the adversarial country binding as an optional extension. Do not insert it into a live query or real account system. The local fixture has only four known country codes and contains no private data or network database connection.

Step 1: Preserve both placeholders and the fixed predicate. Step 2: Supply US and 1 through the local binding case.

The solution uses the identical two-placeholder SQL text with a different declared binding array. It returns the two active US role identities. The genuine zero Canadian role remains excluded by country, and the unknown US salary does not matter because salary is not part of this filter.

The acceptance runner verifies both ordinary binding cases and a quote-containing adversarial value. It also uses array row mode for PostgreSQL so repeated output labels elsewhere cannot overwrite positional values during verification. Driver representation is part of reliable evidence.

In interviews, explain which boundary binding protects and which choices remain application responsibilities. Values, identifiers, authorization and wildcard meaning are separate concerns. A prepared statement does not grant permission to read another account's data, and validation alone is not a substitute for binding.

The next lesson inspects execution plans on the same synthetic population. Correct results and safe inputs come first; an efficient plan for the wrong or unsafe query is not a satisfactory implementation.

Optional stretch — after the core task works: Bind a quote-containing country value and verify it returns no matches rather than changing query grammar.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.