Views, parameters & SQL security

SQL correctness includes security. A perfectly written SELECT can still be dangerous if an application builds it by concatenating untrusted strings.

Views as stored query interfaces

A view presents the result of a query as a named relation. It can hide joins, centralize stable derivations, and expose a narrower interface to consumers.

CREATE VIEW paid_order_summary AS
SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(total) AS total_value
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;

Run the query to see the result.

Views do not automatically mean materialized data. Ordinary views normally execute their underlying query when referenced; materialized views are a separate feature in databases such as PostgreSQL.

SQL injection: the unsafe pattern

Do not construct SQL like this in application code:

SELECT id, email
FROM users
WHERE email = '" + userInput + "';

Run the query to see the result.

If userInput contains SQL syntax, string concatenation can change the query structure. Escaping alone is fragile and database-specific.

Parameterized queries / prepared statements

OWASP's primary recommendation is to use prepared statements with parameter binding so SQL structure and data remain separate.

Conceptually:

SELECT id, email, status
FROM users
WHERE email = :email;

Run the query to see the result.

The site's runner provides safe sample values for a small set of named parameters, so the query above can be executed without concatenating input.

Dynamic identifiers are different

Parameters represent data values, not table names, column names, or sort directions in most database APIs. If an application truly needs dynamic identifiers, map user choices to a fixed allow-list of known identifiers rather than injecting raw input into SQL.

Least privilege

Application database accounts should have only the permissions they need. Read-only reporting code should not be able to drop tables; a service that only touches one schema should not be a database superuser. PostgreSQL exposes GRANT/REVOKE and role management; exact mechanisms differ by engine.

Security testing ideas

Test quotation characters, comment markers, boolean expressions, encoded payloads, unexpected types, very long values, and alternate input paths—but verify that the application still uses parameter binding rather than judging safety from a small payload list.

Practice: identify which parts of a “sort by user-selected column” feature can be parameters and which require allow-listing.