Weak SQL Queries That Hurt Security and Performance

A query can be syntactically valid and still waste resources, expose private records, corrupt a business operation, or block other users. This practical guide turns 54 query/application anti-patterns and seven database conditions into before-and-after examples.

Every numbered section includes a weak example, a corrected or controlled alternative, expected output for each, and separate performance and security impacts. Some examples improve correctness or access control without improving speed. Some intentionally change the result to match a stated business requirement.

Dialect, assumptions, and how to use this blog

• PostgreSQL-oriented: SQL syntax, roles, row-level security, indexes, and maintenance examples target PostgreSQL. Python snippets are application illustrations; %s binding and psycopg.sql.Identifier refer to Psycopg conventions. Other driver APIs differ.
• Independent examples: Begin each section from a fresh copy of the sample schema. An “after” block reuses its corresponding “before” setup only when the section explicitly says so. Section 38 reuses section 37’s RLS setup. Do not concatenate all 122 snippets into one script.
• Dedicated lab: Use an isolated practice database/schema. Before examples include deliberate unsafe construction, destructive writes, nontermination, and excessive grants for teaching. They are not recommended actions. Statements are shown as text; this document does not execute them on your server.
• Output honesty: Tables and counts describe the sample data. Plan choices, runtimes, lock waits, log contents, role behavior, and concurrency outcomes are labeled as expected, conditional, or illustrative. There are no fabricated before/after latency benchmarks and no live PostgreSQL execution claim.
• Access setup: Creating roles, extensions, policies, or privileged functions needs the appropriate owner/administrator rights. Test access as a non-owner runtime role, not just the setup administrator. In fresh grant examples assume no extra inherited privileges, PUBLIC table grants, or ownership.
• Authorization: A literal tenant ID is shorthand for an authorized server-derived tenant. Binding a client-supplied tenant ID prevents injection but does not establish permission.
• Measurement: Evaluate realistic data and concurrency. Use EXPLAIN for plan-only review; EXPLAIN ANALYZE actually executes the statement. An index or rewrite is never a universal speed guarantee.

Shared practice database

Create the schema once in a fresh lab. Tables are intentionally tiny so result rows are easy to check; performance impacts describe what can happen at scale. The password-hash strings are placeholders and must never be used as actual credentials.

CREATE SCHEMA weak_sql_blog;
SET search_path = weak_sql_blog, public;
CREATE TABLE employees (
  emp_id INTEGER PRIMARY KEY, emp_name VARCHAR(40) NOT NULL,
  tenant_id INTEGER NOT NULL, dept_id INTEGER,
  salary INTEGER NOT NULL CHECK (salary >= 0),
  email VARCHAR(100) NOT NULL UNIQUE, hired_on DATE NOT NULL,
  manager_id INTEGER REFERENCES employees(emp_id), password_hash TEXT
);
INSERT INTO employees VALUES
(1,'Asha',1,10,90000,'asha@example.com','2025-01-10',NULL,'demo-hash-1'),
(2,'Ravi',1,10,70000,'ravi@example.com','2025-03-15',1,'demo-hash-2'),
(3,'Neha',2,20,70000,'neha@example.com','2024-07-20',1,'demo-hash-3'),
(4,'Imran',2,20,50000,'imran@example.com','2025-06-01',3,'demo-hash-4');
CREATE TABLE customers (
  tenant_id INTEGER, customer_id INTEGER, customer_name VARCHAR(40),
  PRIMARY KEY (tenant_id, customer_id)
);
INSERT INTO customers VALUES (1,1,'Anita'),(1,2,'Bharat'),(2,1,'Charu');
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY, tenant_id INTEGER NOT NULL,
  customer_id INTEGER NOT NULL, amount INTEGER NOT NULL,
  status VARCHAR(12) NOT NULL, urgent BOOLEAN NOT NULL,
  FOREIGN KEY (tenant_id, customer_id)
    REFERENCES customers(tenant_id, customer_id)
);
INSERT INTO orders VALUES
(101,1,1,100,'open',FALSE),(102,1,2,200,'closed',FALSE),
(103,2,1,300,'open',TRUE),(104,1,1,50,'open',TRUE);
CREATE TABLE products (
  product_id INTEGER PRIMARY KEY, product_name VARCHAR(40),
  stock INTEGER NOT NULL CHECK (stock >= 0), status VARCHAR(12)
);
INSERT INTO products VALUES (1,'Bike helmet',1,'active'),(2,'Gloves',5,'inactive');
CREATE TABLE accounts (account_id INTEGER PRIMARY KEY, balance INTEGER NOT NULL);
INSERT INTO accounts VALUES (1,1000),(2,500);
CREATE TABLE payments (
  payment_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  request_key TEXT NOT NULL UNIQUE, amount INTEGER NOT NULL CHECK (amount > 0)
);
CREATE TABLE nodes (id INTEGER PRIMARY KEY, parent_id INTEGER);
INSERT INTO nodes VALUES (1,NULL),(2,1),(3,2),(4,4);

Setup result: four employees, three customers, four orders, two products, two accounts, an empty payments table, and four hierarchy nodes. Node 4 deliberately points to itself for the recursion example.

For role-related examples only, an authorized administrator can also initialize these isolated NOLOGIN group roles:

CREATE ROLE weak_blog_reader NOLOGIN;
CREATE ROLE weak_blog_app NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOBYPASSRLS;
CREATE ROLE weak_blog_tenant1 NOLOGIN NOBYPASSRLS;
GRANT USAGE ON SCHEMA weak_sql_blog
TO weak_blog_reader, weak_blog_app, weak_blog_tenant1;

Expected role setup: three roles created and schema USAGE granted. Recreate the role permissions for independent security examples. These roles are not production login accounts. Qualify table names in application code; the search path above is only a lab convenience.

Sample employees

Sample orders

Boolean values may display as t/f, true/false, or 1/0 depending on the client. In the orders table, urgent is false for 101/102 and true for 103/104.

Contents

Performance-related patterns (1–25)

1. Fetching unnecessary columns
2. Returning unlimited rows
3. Missing a useful lookup index
4. Functions on indexed columns
5. Extracting a year instead of using a date range
6. Casting a stored key unnecessarily
7. Leading-wildcard substring search
8. Deep OFFSET pagination
9. Sorting a larger input than necessary
10. Random sorting for sampling
11. Using DISTINCT to hide join mistakes
12. UNION when duplicates are intentional
13. Accidental Cartesian products
14. Joining on only part of a composite key
15. Joining full detail when a summary is needed
16. N+1 queries from application loops
17. Repeated correlated calculations
18. Counting all matches to answer yes/no
19. Large literal IN lists
20. One commit per inserted row
21. Updating rows whose value is already correct
22. Huge deletes in one transaction
23. Waiting for user input inside a transaction
24. Unnecessary FOR UPDATE on read-only requests
25. Recursive queries without cycle protection

Security-related patterns (26–43)

1. SQL injection through string concatenation
2. Unvalidated dynamic ORDER BY identifiers
3. Unsafe dynamic SQL inside a stored function
4. Manual quote removal instead of parameter binding
5. Missing ownership or tenant authorization
6. Trusting the tenant ID supplied by the browser
7. Selecting sensitive columns for an ordinary directory
8. Running the application with administrative privileges
9. GRANT ALL for a read-only consumer
10. Sensitive data granted to PUBLIC
11. A view that leaves direct table access open
12. Testing row security only as the table owner
13. Using BYPASSRLS for a tenant role
14. Privileged functions with unsafe object resolution
15. Storing plaintext passwords
16. Logging SQL parameter secrets
17. Returning raw database errors to API clients
18. Public endpoints accepting arbitrary SQL

Correctness and availability (44–54)

1. Missing WHERE on a destructive mutation
2. Incorrect AND/OR grouping leaks rows
3. Check-then-write stock races
4. Dependent writes without a transaction
5. Missing uniqueness for a business identifier
6. Comparing a value to NULL with equals
7. NOT IN against a nullable subquery
8. LIMIT without deterministic ordering
9. EXPLAIN ANALYZE mistaken for a read-only inspection
10. Unbounded statement execution and lock waits
11. Blindly retrying a non-idempotent payment insert

Database and workload conditions (55–61)

1. Stale planner statistics
2. Redundant indexes that increase write work
3. An index that does not match the main filter
4. Disabled routine vacuuming
5. Missing an index on referencing foreign-key columns
6. Wrong data types and repeated conversions
7. Uncontrolled query concurrency

1. Fetching unnecessary columns

Before — weak pattern

SELECT * FROM employees WHERE emp_id = 2;

Before — expected output or observed effect

After — corrected or controlled alternative

SELECT emp_id, emp_name FROM employees WHERE emp_id = 2;

After — expected output or observed effect

2. Returning unlimited rows

Before — weak pattern

SELECT emp_id, emp_name FROM employees ORDER BY emp_id;

Before — expected output or observed effect

After — corrected or controlled alternative

SELECT emp_id, emp_name FROM employees ORDER BY emp_id LIMIT 2;

After — expected output or observed effect

Important context: This deliberately changes the result to a page. Provide continuation for the remaining rows.

3. Missing a useful lookup index

Before — weak pattern

SELECT order_id FROM orders WHERE tenant_id = 1 AND customer_id = 1 ORDER BY order_id;

Before — expected output or observed effect

After — corrected or controlled alternative

CREATE INDEX idx_orders_customer ON orders(tenant_id, customer_id);
SELECT order_id FROM orders WHERE tenant_id = 1 AND customer_id = 1 ORDER BY order_id;

After — expected output or observed effect

4. Functions on indexed columns

Before — weak pattern

SELECT emp_id FROM employees WHERE LOWER(email) = 'ravi@example.com';

Before — expected output or observed effect

After — corrected or controlled alternative

CREATE INDEX idx_employee_lower_email ON employees(LOWER(email));
SELECT emp_id FROM employees WHERE LOWER(email) = 'ravi@example.com';

After — expected output or observed effect

Important context: Preserves case-insensitive matching. Removing LOWER would change semantics.

5. Extracting a year instead of using a date range

Before — weak pattern

SELECT emp_name FROM employees WHERE EXTRACT(YEAR FROM hired_on) = 2025 ORDER BY emp_id;

Before — expected output or observed effect

Asha, Ravi, Imran.

After — corrected or controlled alternative

SELECT emp_name FROM employees WHERE hired_on >= DATE '2025-01-01' AND hired_on < DATE '2026-01-01' ORDER BY emp_id;

After — expected output or observed effect

Asha, Ravi, Imran.

Important context: For timestamp data, define time-zone boundaries explicitly.

6. Casting a stored key unnecessarily

Before — weak pattern

SELECT emp_name FROM employees WHERE CAST(emp_id AS TEXT) = '2';

Before — expected output or observed effect

After — corrected or controlled alternative

SELECT emp_name FROM employees WHERE emp_id = 2;

After — expected output or observed effect

7. Leading-wildcard substring search

Before — weak pattern

SELECT emp_id, emp_name FROM employees WHERE emp_name LIKE '%avi%' ORDER BY emp_id;

Before — expected output or observed effect

After — corrected or controlled alternative

-- Requires pg_trgm installed by an authorized administrator.
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_employee_name_trgm ON employees USING gin (emp_name gin_trgm_ops);
SELECT emp_id, emp_name FROM employees WHERE emp_name LIKE '%avi%' ORDER BY emp_id;

After — expected output or observed effect

8. Deep OFFSET pagination

Before — weak pattern

SELECT emp_id, emp_name FROM employees ORDER BY emp_id LIMIT 2 OFFSET 2;

Before — expected output or observed effect

After — corrected or controlled alternative

SELECT emp_id, emp_name FROM employees WHERE emp_id > 2 ORDER BY emp_id LIMIT 2;

After — expected output or observed effect

Important context: This tiny offset illustrates the pattern; keyset pagination changes navigation and needs a valid cursor.

9. Sorting a larger input than necessary

Before — weak pattern

SELECT emp_name, salary FROM employees ORDER BY salary DESC, emp_id;

Before — expected output or observed effect

After — corrected or controlled alternative

SELECT emp_name, salary FROM employees WHERE tenant_id = 1 ORDER BY salary DESC, emp_id LIMIT 2;

After — expected output or observed effect

Important context: Results intentionally differ because the endpoint needs tenant 1 only. Do not remove required business rows just to improve speed.

10. Random sorting for sampling

Before — weak pattern

SELECT emp_id FROM employees ORDER BY RANDOM() LIMIT 2;

Before — expected output or observed effect

Two nondeterministically chosen employee IDs.

After — corrected or controlled alternative

SELECT emp_id FROM employees TABLESAMPLE SYSTEM (50) REPEATABLE (7) LIMIT 2;

After — expected output or observed effect

Zero to two sampled IDs; exact rows depend on physical pages. A small table may yield zero rows.

Important context: Not equivalent sampling: SYSTEM is page-based, does not guarantee two rows, and may be biased for clustered data. Use only if this approximation is acceptable.

11. Using DISTINCT to hide join mistakes

Before — weak pattern

SELECT DISTINCT c.customer_name FROM customers c JOIN orders o ON o.customer_id = c.customer_id ORDER BY c.customer_name;

Before — expected output or observed effect

After — corrected or controlled alternative

SELECT c.customer_name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.tenant_id = c.tenant_id AND o.customer_id = c.customer_id) ORDER BY c.customer_name;

After — expected output or observed effect

12. UNION when duplicates are intentional

Before — weak pattern

SELECT salary FROM employees WHERE dept_id = 10 UNION SELECT salary FROM employees WHERE dept_id = 20 ORDER BY salary;

Before — expected output or observed effect

After — corrected or controlled alternative

SELECT salary FROM employees WHERE dept_id = 10 UNION ALL SELECT salary FROM employees WHERE dept_id = 20 ORDER BY salary;

After — expected output or observed effect

Important context: Changes semantics: use UNION ALL only when duplicate salary records should remain.

13. Accidental Cartesian products

Before — weak pattern

SELECT COUNT(*) AS combinations FROM customers CROSS JOIN orders;

Before — expected output or observed effect

After — corrected or controlled alternative

SELECT COUNT(*) AS combinations FROM customers c JOIN orders o ON o.tenant_id = c.tenant_id AND o.customer_id = c.customer_id;

After — expected output or observed effect

14. Joining on only part of a composite key

Before — weak pattern

SELECT o.order_id, c.customer_name FROM orders o JOIN customers c ON c.customer_id = o.customer_id WHERE o.order_id = 101 ORDER BY c.customer_name;

Before — expected output or observed effect

After — corrected or controlled alternative

SELECT o.order_id, c.customer_name FROM orders o JOIN customers c ON c.tenant_id = o.tenant_id AND c.customer_id = o.customer_id WHERE o.order_id = 101 ORDER BY c.customer_name;

After — expected output or observed effect

15. Joining full detail when a summary is needed

Before — weak pattern

SELECT c.tenant_id, c.customer_id, SUM(o.amount) AS total FROM customers c JOIN orders o ON o.tenant_id=c.tenant_id AND o.customer_id=c.customer_id GROUP BY c.tenant_id,c.customer_id ORDER BY c.tenant_id,c.customer_id;

Before — expected output or observed effect

After — corrected or controlled alternative

WITH totals AS (SELECT tenant_id, customer_id, SUM(amount) AS total FROM orders GROUP BY tenant_id,customer_id) SELECT c.tenant_id,c.customer_id,t.total FROM customers c JOIN totals t ON t.tenant_id=c.tenant_id AND t.customer_id=c.customer_id ORDER BY c.tenant_id,c.customer_id;

After — expected output or observed effect

Important context: Both examples intentionally include customers with orders only.

16. N+1 queries from application loops

Before — weak pattern

orders = db.query("SELECT order_id, tenant_id, customer_id FROM orders ORDER BY order_id")
for o in orders:
    db.query("SELECT customer_name FROM customers WHERE tenant_id=%s AND customer_id=%s",
             (o.tenant_id, o.customer_id))

Before — expected output or observed effect

Same names: 101 Anita; 102 Bharat; 103 Charu; 104 Anita. Five database requests: one list plus four lookups.

After — corrected or controlled alternative

SELECT o.order_id, c.customer_name FROM orders o
JOIN customers c ON c.tenant_id=o.tenant_id AND c.customer_id=o.customer_id
ORDER BY o.order_id;

After — expected output or observed effect

17. Repeated correlated calculations

Before — weak pattern

SELECT e.emp_id,e.emp_name FROM employees e WHERE e.salary > (SELECT AVG(x.salary) FROM employees x WHERE x.dept_id=e.dept_id) ORDER BY e.emp_id;

Before — expected output or observed effect

After — corrected or controlled alternative

WITH department_avg AS (SELECT dept_id,AVG(salary) AS avg_salary FROM employees GROUP BY dept_id) SELECT e.emp_id,e.emp_name FROM employees e JOIN department_avg d ON d.dept_id=e.dept_id WHERE e.salary>d.avg_salary ORDER BY e.emp_id;

After — expected output or observed effect

18. Counting all matches to answer yes/no

Before — weak pattern

SELECT COUNT(*) AS matches FROM orders WHERE tenant_id=1 AND status='open';

Before — expected output or observed effect

After — corrected or controlled alternative

SELECT EXISTS (SELECT 1 FROM orders WHERE tenant_id=1 AND status='open') AS has_open_orders;

After — expected output or observed effect

has_open_orders = true.

Important context: Changes output type because the business question is existence, not the number of matches.

19. Large literal IN lists

Before — weak pattern

SELECT emp_id FROM employees WHERE emp_id IN (1,2,3) ORDER BY emp_id;

Before — expected output or observed effect

After — corrected or controlled alternative

CREATE TEMP TABLE wanted_ids (emp_id INTEGER PRIMARY KEY);
INSERT INTO wanted_ids VALUES (1),(2),(3);
SELECT e.emp_id FROM employees e JOIN wanted_ids w ON w.emp_id=e.emp_id ORDER BY e.emp_id;

After — expected output or observed effect

Important context: Temporary-table lifetime and connection pooling must be managed.

20. One commit per inserted row

Before — weak pattern

BEGIN; INSERT INTO orders VALUES (105,1,1,20,'open',FALSE); COMMIT;
BEGIN; INSERT INTO orders VALUES (106,1,2,30,'open',FALSE); COMMIT;

Before — expected output or observed effect

Two rows inserted through two transactions; order count becomes 6.

After — corrected or controlled alternative

BEGIN;
INSERT INTO orders VALUES (105,1,1,20,'open',FALSE),(106,1,2,30,'open',FALSE);
COMMIT;

After — expected output or observed effect

Two rows inserted through one transaction; order count becomes 6.

21. Updating rows whose value is already correct

Before — weak pattern

BEGIN; UPDATE products SET status='active' WHERE product_id=1 RETURNING product_id; ROLLBACK;

Before — expected output or observed effect

Returns product_id 1; PostgreSQL reports one updated row even though the value is unchanged.

After — corrected or controlled alternative

BEGIN; UPDATE products SET status='active' WHERE product_id=1 AND status IS DISTINCT FROM 'active' RETURNING product_id; ROLLBACK;

After — expected output or observed effect

No rows returned; UPDATE 0.

Important context: Not equivalent if a business-required trigger must run even for unchanged values.

22. Huge deletes in one transaction

Before — weak pattern

BEGIN; DELETE FROM orders WHERE status='closed' RETURNING order_id; COMMIT;

Before — expected output or observed effect

Deletes order 102 in this dataset; a production run may remove millions of rows at once.

After — corrected or controlled alternative

BEGIN;
WITH batch AS (SELECT order_id FROM orders WHERE status='closed' ORDER BY order_id LIMIT 1000 FOR UPDATE SKIP LOCKED)
DELETE FROM orders o USING batch b WHERE o.order_id=b.order_id RETURNING o.order_id;
COMMIT;

After — expected output or observed effect

Deletes order 102 here; at most 1000 selected rows per batch.

Important context: Repeat batches deliberately. SKIP LOCKED can skip busy rows; revisit them. Batching changes all-or-nothing semantics.

23. Waiting for user input inside a transaction

Before — weak pattern

db.execute("BEGIN")
db.execute("UPDATE products SET status='active' WHERE product_id=2")
wait_for_user_confirmation()  # transaction and lock remain open
db.execute("COMMIT")

Before — expected output or observed effect

Product 2 changes when committed; its lock is held throughout the user wait.

After — corrected or controlled alternative

confirmation = obtain_confirmation_before_transaction()
with db.transaction():
    db.execute("UPDATE products SET status='active' WHERE product_id=%s", (2,))

After — expected output or observed effect

Same committed status; the database transaction begins after confirmation.

Important context: Application pseudocode; transaction context-manager APIs vary by driver.

24. Unnecessary FOR UPDATE on read-only requests

Before — weak pattern

SELECT emp_id,emp_name FROM employees WHERE emp_id=2 FOR UPDATE;

Before — expected output or observed effect

2, Ravi; additionally locks the selected row until transaction end.

After — corrected or controlled alternative

SELECT emp_id,emp_name FROM employees WHERE emp_id=2;

After — expected output or observed effect

Important context: Keep locking when needed for a correct read-modify-write operation. Removing it blindly can introduce races.

25. Recursive queries without cycle protection

Before — weak pattern

WITH RECURSIVE walk AS (SELECT id,parent_id FROM nodes WHERE id=4 UNION ALL SELECT n.id,n.parent_id FROM nodes n JOIN walk w ON n.id=w.parent_id) SELECT id FROM walk;

Before — expected output or observed effect

Do not execute unbounded: node 4 points to itself, so recursion keeps producing 4 until canceled or a resource limit is reached.

After — corrected or controlled alternative

WITH RECURSIVE walk(id,parent_id,path) AS (SELECT id,parent_id,ARRAY[id] FROM nodes WHERE id=4 UNION ALL SELECT n.id,n.parent_id,w.path||n.id FROM nodes n JOIN walk w ON n.id=w.parent_id WHERE NOT n.id=ANY(w.path)) SELECT id FROM walk;

After — expected output or observed effect

One row: id = 4.

26. SQL injection through string concatenation

Before — weak pattern

email = "ravi@example.com"
cursor.execute("SELECT emp_id,emp_name FROM employees WHERE email='" + email + "'")

Before — expected output or observed effect

For this benign input: 2, Ravi. Crafted input could change the query structure.

After — corrected or controlled alternative

email = "ravi@example.com"
cursor.execute("SELECT emp_id,emp_name FROM employees WHERE email=%s", (email,))

After — expected output or observed effect

2, Ravi. Supplied email remains a value, including any quote characters.

Important context: Python examples use Psycopg-style %s placeholders. Do not interpolate with Python’s % operator.

27. Unvalidated dynamic ORDER BY identifiers

Before — weak pattern

sort_column = request["sort"]
cursor.execute("SELECT emp_id,emp_name FROM employees ORDER BY " + sort_column)

Before — expected output or observed effect

For a submitted sort value emp_name: Asha, Imran, Neha, Ravi. Untrusted text can alter SQL.

After — corrected or controlled alternative

from psycopg import sql
allowed = {"name": "emp_name", "id": "emp_id"}
column = allowed[request["sort"]]  # reject unknown keys
cursor.execute(sql.SQL("SELECT emp_id,emp_name FROM employees ORDER BY {},emp_id")
               .format(sql.Identifier(column)))

After — expected output or observed effect

For sort=name: Asha, Imran, Neha, Ravi. Unknown sort keys are rejected.

Important context: Before assumes the submitted sort is emp_name; after exposes the public alias name.

28. Unsafe dynamic SQL inside a stored function

Before — weak pattern

CREATE FUNCTION weak_lookup(p_email TEXT) RETURNS SETOF employees LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY EXECUTE
    'SELECT * FROM weak_sql_blog.employees WHERE email=''' || p_email || '''';
END; $$;

Before — expected output or observed effect

For a benign ravi@example.com: the complete Ravi row. Quotes in input are incorporated into executable SQL rather than bound as a value.

After — corrected or controlled alternative

CREATE FUNCTION safe_lookup(p_email TEXT) RETURNS TABLE(emp_id INTEGER,emp_name VARCHAR) LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY EXECUTE
    'SELECT e.emp_id,e.emp_name FROM weak_sql_blog.employees e WHERE e.email=$1'
    USING p_email;
END; $$;
SELECT * FROM safe_lookup('ravi@example.com');

After — expected output or observed effect

Two columns: emp_id 2, emp_name Ravi.

Important context: This before example uses quoting correctly for one literal, unlike raw concatenation. A truly unsafe variant is shown below.

29. Manual quote removal instead of parameter binding

Before — weak pattern

email = user_input.replace("'", "")
cursor.execute("SELECT emp_id FROM employees WHERE email='" + email + "'")

Before — expected output or observed effect

For an address containing an apostrophe, quote removal changes the searched address and may produce an incorrect result.

After — corrected or controlled alternative

cursor.execute("SELECT emp_id FROM employees WHERE email=%s", (user_input,))

After — expected output or observed effect

Searches the exact supplied address. A nonmatching address returns no rows.

30. Missing ownership or tenant authorization

Before — weak pattern

SELECT order_id,amount FROM orders WHERE order_id=103;

Before — expected output or observed effect

After — corrected or controlled alternative

SELECT order_id,amount FROM orders WHERE order_id=103 AND tenant_id=1;

After — expected output or observed effect

No rows returned.

Important context: Tenant 1 must come from verified server-side identity; a supplied ID alone is not proof of access.

31. Trusting the tenant ID supplied by the browser

Before — weak pattern

cursor.execute("SELECT order_id FROM orders WHERE tenant_id=%s ORDER BY order_id",
               (request["tenant_id"],))  # attacker submits 2

Before — expected output or observed effect

Returns order 103 when the client supplies tenant 2.

After — corrected or controlled alternative

tenant_id = authenticated_session.authorized_tenant_id  # verified server-side
cursor.execute("SELECT order_id FROM orders WHERE tenant_id=%s ORDER BY order_id",
               (tenant_id,))  # session authorizes tenant 1

After — expected output or observed effect

Returns orders 101, 102, 104 for the verified tenant 1.

32. Selecting sensitive columns for an ordinary directory

Before — weak pattern

SELECT emp_id,emp_name,email,salary,password_hash FROM employees WHERE emp_id=2;

Before — expected output or observed effect

After — corrected or controlled alternative

SELECT emp_id,emp_name FROM employees WHERE emp_id=2;

After — expected output or observed effect

Important context: The setup hashes are demonstration strings, not usable password hashes.

33. Running the application with administrative privileges

Before — weak pattern

# Application deployment configuration (illustrative)
connection_role = "database_superuser"
# An injected or compromised operation inherits that role's authority.

Before — expected output or observed effect

Assumption: the configured connection is a superuser and can bypass normal database access controls.

After — corrected or controlled alternative

-- Run role management as an authorized administrator.
ALTER ROLE weak_blog_app NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS;
GRANT USAGE ON SCHEMA weak_sql_blog TO weak_blog_app;
GRANT SELECT (emp_id,emp_name) ON weak_sql_blog.employees TO weak_blog_app;
SELECT rolname,rolsuper,rolbypassrls FROM pg_roles WHERE rolname='weak_blog_app';

After — expected output or observed effect

weak_blog_app | rolsuper=false | rolbypassrls=false.

Important context: The lab role is NOLOGIN; a production login or pool must be assigned the reviewed privileges through a suitable deployment design.

34. GRANT ALL for a read-only consumer

Before — weak pattern

GRANT ALL PRIVILEGES ON weak_sql_blog.employees TO weak_blog_reader;
SELECT has_table_privilege('weak_blog_reader','weak_sql_blog.employees','DELETE') AS can_delete;

Before — expected output or observed effect

can_delete = true.

After — corrected or controlled alternative

REVOKE ALL PRIVILEGES ON weak_sql_blog.employees FROM weak_blog_reader;
GRANT SELECT (emp_id,emp_name) ON weak_sql_blog.employees TO weak_blog_reader;
SELECT has_table_privilege('weak_blog_reader','weak_sql_blog.employees','DELETE') AS can_delete;

After — expected output or observed effect

can_delete = false in the isolated lab with no other grants or memberships.

35. Sensitive data granted to PUBLIC

Before — weak pattern

GRANT SELECT ON weak_sql_blog.employees TO PUBLIC;
SELECT has_table_privilege('weak_blog_reader','weak_sql_blog.employees','SELECT') AS can_read_all;

Before — expected output or observed effect

can_read_all = true.

After — corrected or controlled alternative

REVOKE SELECT ON weak_sql_blog.employees FROM PUBLIC;
SELECT has_table_privilege('weak_blog_reader','weak_sql_blog.employees','SELECT') AS can_read_all;

After — expected output or observed effect

can_read_all = false, assuming this role has no other table-level SELECT grant.

36. A view that leaves direct table access open

Before — weak pattern

CREATE VIEW weak_sql_blog.directory AS SELECT emp_id,emp_name FROM weak_sql_blog.employees;
GRANT SELECT ON weak_sql_blog.directory,weak_sql_blog.employees TO weak_blog_reader;
SELECT has_column_privilege('weak_blog_reader','weak_sql_blog.employees','salary','SELECT') AS can_read_salary;

Before — expected output or observed effect

can_read_salary = true.

After — corrected or controlled alternative

REVOKE ALL PRIVILEGES ON weak_sql_blog.employees FROM weak_blog_reader;
GRANT SELECT ON weak_sql_blog.directory TO weak_blog_reader;
SELECT has_column_privilege('weak_blog_reader','weak_sql_blog.employees','salary','SELECT') AS can_read_salary;

After — expected output or observed effect

can_read_salary = false, assuming no inherited or column-level grants; directory remains accessible.

Important context: Run the after example following its before setup within this one section.

37. Testing row security only as the table owner

Before — weak pattern

ALTER TABLE weak_sql_blog.employees ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_one ON weak_sql_blog.employees FOR SELECT TO weak_blog_tenant1 USING (tenant_id=1);
GRANT SELECT ON weak_sql_blog.employees TO weak_blog_tenant1;
-- Run as table owner (normal owner bypass):
SELECT emp_id FROM weak_sql_blog.employees ORDER BY emp_id;

Before — expected output or observed effect

Owner normally sees IDs 1, 2, 3, 4.

After — corrected or controlled alternative

SET ROLE weak_blog_tenant1;
SELECT emp_id FROM weak_sql_blog.employees ORDER BY emp_id;
RESET ROLE;

After — expected output or observed effect

Restricted runtime role sees IDs 1, 2.

Important context: After depends on this section’s policy setup. A policy for SELECT does not authorize INSERT/UPDATE/DELETE.

38. Using BYPASSRLS for a tenant role

Before — weak pattern

ALTER ROLE weak_blog_tenant1 BYPASSRLS;
SET ROLE weak_blog_tenant1;
SELECT emp_id FROM weak_sql_blog.employees ORDER BY emp_id;
RESET ROLE;

Before — expected output or observed effect

With RLS/policy from section 37 installed, BYPASSRLS sees IDs 1, 2, 3, 4.

After — corrected or controlled alternative

ALTER ROLE weak_blog_tenant1 NOBYPASSRLS;
SET ROLE weak_blog_tenant1;
SELECT emp_id FROM weak_sql_blog.employees ORDER BY emp_id;
RESET ROLE;

After — expected output or observed effect

With the same policy, NOBYPASSRLS sees IDs 1, 2.

Important context: This section explicitly reuses section 37’s policy setup. Only an authorized administrator should manage these flags.

39. Privileged functions with unsafe object resolution

Before — weak pattern

CREATE FUNCTION weak_sql_blog.employee_total() RETURNS BIGINT
LANGUAGE sql SECURITY DEFINER AS $$ SELECT COUNT(*) FROM employees; $$;
GRANT EXECUTE ON FUNCTION weak_sql_blog.employee_total() TO PUBLIC;

Before — expected output or observed effect

Under the intended search path, returns 4; unqualified resolution and broad execution are weaknesses.

After — corrected or controlled alternative

BEGIN;
CREATE FUNCTION weak_sql_blog.safe_employee_total() RETURNS BIGINT
LANGUAGE sql SECURITY DEFINER SET search_path=pg_catalog,pg_temp
AS $$ SELECT COUNT(*) FROM weak_sql_blog.employees; $$;
REVOKE ALL ON FUNCTION weak_sql_blog.safe_employee_total() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION weak_sql_blog.safe_employee_total() TO weak_blog_reader;
COMMIT;
SELECT weak_sql_blog.safe_employee_total();

After — expected output or observed effect

Returns 4 using the qualified table; only granted roles can execute, subject to ownership/inheritance.

Important context: Use SECURITY INVOKER instead if elevated execution is unnecessary. Secure creation/grants should occur atomically to avoid a PUBLIC-execution window.

40. Storing plaintext passwords

Before — weak pattern

cursor.execute("INSERT INTO credentials(user_id,password_value) VALUES (%s,%s)",
               (1, "example-password"))

Before — expected output or observed effect

Stored value is directly readable as example-password.

After — corrected or controlled alternative

# credentials is an application table, separate from the shared lab.
from argon2 import PasswordHasher
password_hash = PasswordHasher().hash(user_supplied_password)
cursor.execute("INSERT INTO credentials(user_id,password_hash) VALUES (%s,%s)",
               (1, password_hash))

After — expected output or observed effect

Stores a salted encoded password hash, typically starting with $argon2id$; exact bytes vary. No plaintext password is stored by this insert.

Important context: Application illustration: create a suitable credentials schema and use a maintained implementation. Never use the demonstration setup strings as real hashes.

41. Logging SQL parameter secrets

Before — weak pattern

logger.info("SQL=%s parameters=%r", query, parameters)
cursor.execute(query, parameters)

Before — expected output or observed effect

Application log may include an email, password, or access token from parameters.

After — corrected or controlled alternative

logger.info("operation=employee_lookup request_id=%s", request_id)
cursor.execute(query, parameters)  # do not log the supplied values

After — expected output or observed effect

Log contains the operation name and request identifier, without those parameter values.

42. Returning raw database errors to API clients

Before — weak pattern

try:
    cursor.execute(query, parameters)
except DatabaseError as error:
    return {"error": str(error)}, 500

Before — expected output or observed effect

Response may expose SQL, table names, constraint names, or sensitive values.

After — corrected or controlled alternative

try:
    cursor.execute(query, parameters)
except DatabaseError:
    rollback_if_needed()
    record_redacted_diagnostic(request_id)
    return {"error": "Request could not be completed", "request_id": request_id}, 500

After — expected output or observed effect

Response contains a generic failure and request ID; redacted details remain in protected diagnostics.

43. Public endpoints accepting arbitrary SQL

Before — weak pattern

cursor.execute(request["sql"])
return cursor.fetchall()

Before — expected output or observed effect

Client can request arbitrary statements allowed by the connection role; output depends on submitted SQL.

After — corrected or controlled alternative

if request["operation"] != "my_orders":
    reject_request()
tenant = authenticated_session.authorized_tenant_id
cursor.execute("SELECT order_id,amount FROM orders WHERE tenant_id=%s ORDER BY order_id LIMIT 50",
               (tenant,))
return cursor.fetchall()

After — expected output or observed effect

For authorized tenant 1: (101,100), (102,200), (104,50); unsupported operations rejected.

44. Missing WHERE on a destructive mutation

Before — weak pattern

BEGIN; DELETE FROM orders RETURNING order_id; ROLLBACK;

Before — expected output or observed effect

Before rollback: returns IDs 101,102,103,104; all four rows were deleted in the transaction.

After — corrected or controlled alternative

BEGIN; DELETE FROM orders WHERE order_id=102 AND tenant_id=1 RETURNING order_id; ROLLBACK;

After — expected output or observed effect

Before rollback: returns only 102; the other three rows remain.

45. Incorrect AND/OR grouping leaks rows

Before — weak pattern

SELECT order_id,tenant_id FROM orders WHERE tenant_id=1 AND status='open' OR urgent=TRUE ORDER BY order_id;

Before — expected output or observed effect

After — corrected or controlled alternative

SELECT order_id,tenant_id FROM orders WHERE tenant_id=1 AND (status='open' OR urgent=TRUE) ORDER BY order_id;

After — expected output or observed effect

46. Check-then-write stock races

Before — weak pattern

# Two sessions both read stock=1 before either commits.
stock = query_one("SELECT stock FROM products WHERE product_id=1")
if stock >= 1:
    execute("UPDATE products SET stock=%s WHERE product_id=1", (stock-1,))
    confirm_order()

Before — expected output or observed effect

Both sessions may confirm an order while final stock is 0: two sales for one unit (lost update).

After — corrected or controlled alternative

UPDATE products SET stock=stock-1
WHERE product_id=1 AND stock>=1
RETURNING product_id,stock;

After — expected output or observed effect

At PostgreSQL READ COMMITTED, one committed competing update returns (1,0); the other returns no rows after rechecking stock. Confirm only the successful reservation.

47. Dependent writes without a transaction

Before — weak pattern

-- Autocommit enabled; simulate an application failure between statements.
UPDATE accounts SET balance=balance-100 WHERE account_id=1;
-- Application stops here; credit never executes.
-- UPDATE accounts SET balance=balance+100 WHERE account_id=2;

Before — expected output or observed effect

Account 1: 900; account 2: 500. Total incorrectly falls from 1500 to 1400.

After — corrected or controlled alternative

BEGIN;
UPDATE accounts SET balance=balance-100 WHERE account_id=1;
UPDATE accounts SET balance=balance+100 WHERE account_id=2;
COMMIT;
SELECT account_id,balance FROM accounts ORDER BY account_id;

After — expected output or observed effect

Successful transfer: account 1=900; account 2=600; total=1500. If a failure prevents completion, roll back both changes.

48. Missing uniqueness for a business identifier

Before — weak pattern

CREATE TEMP TABLE signup_weak (id INTEGER, email TEXT);
INSERT INTO signup_weak VALUES (1,'same@example.com'),(2,'same@example.com');
SELECT COUNT(*) AS accounts_for_email FROM signup_weak WHERE email='same@example.com';

Before — expected output or observed effect

accounts_for_email = 2.

After — corrected or controlled alternative

CREATE TEMP TABLE signup_safe (id INTEGER PRIMARY KEY,email TEXT NOT NULL UNIQUE);
INSERT INTO signup_safe VALUES (1,'same@example.com');
INSERT INTO signup_safe VALUES (2,'same@example.com') ON CONFLICT (email) DO NOTHING;
SELECT COUNT(*) AS accounts_for_email FROM signup_safe WHERE email='same@example.com';

After — expected output or observed effect

accounts_for_email = 1; second insert reports INSERT 0 0.

49. Comparing a value to NULL with equals

Before — weak pattern

SELECT emp_id,emp_name FROM employees WHERE manager_id=NULL ORDER BY emp_id;

Before — expected output or observed effect

No rows returned.

After — corrected or controlled alternative

SELECT emp_id,emp_name FROM employees WHERE manager_id IS NULL ORDER BY emp_id;

After — expected output or observed effect

50. NOT IN against a nullable subquery

Before — weak pattern

SELECT e.emp_id FROM employees e WHERE e.emp_id NOT IN (SELECT manager_id FROM employees) ORDER BY e.emp_id;

Before — expected output or observed effect

No rows returned.

After — corrected or controlled alternative

SELECT e.emp_id FROM employees e WHERE NOT EXISTS (SELECT 1 FROM employees x WHERE x.manager_id=e.emp_id) ORDER BY e.emp_id;

After — expected output or observed effect

Important context: After finds employees who manage nobody: IDs 2 and 4. This is not a rule to replace every NOT IN mechanically.

51. LIMIT without deterministic ordering

Before — weak pattern

SELECT emp_id,emp_name FROM employees LIMIT 2;

Before — expected output or observed effect

An unspecified subset of two employees; even if Asha and Ravi appear today, that is not guaranteed.

After — corrected or controlled alternative

SELECT emp_id,emp_name FROM employees ORDER BY emp_id LIMIT 2;

After — expected output or observed effect

52. EXPLAIN ANALYZE mistaken for a read-only inspection

Before — weak pattern

BEGIN; EXPLAIN ANALYZE DELETE FROM orders WHERE order_id=102; SELECT COUNT(*) AS remaining FROM orders; ROLLBACK;

Before — expected output or observed effect

EXPLAIN ANALYZE returns a runtime plan and actually deletes 102 inside the transaction; remaining=3 before rollback.

After — corrected or controlled alternative

EXPLAIN DELETE FROM orders WHERE order_id=102;
SELECT COUNT(*) AS remaining FROM orders;

After — expected output or observed effect

EXPLAIN returns an estimated plan without executing DELETE; remaining=4.

53. Unbounded statement execution and lock waits

Before — weak pattern

SELECT pg_sleep(3);

Before — expected output or observed effect

Returns one void-valued row after approximately three seconds, assuming no pre-existing timeout.

After — corrected or controlled alternative

BEGIN;
SET LOCAL statement_timeout='1s';
SET LOCAL lock_timeout='500ms';
SELECT pg_sleep(3);
ROLLBACK;

After — expected output or observed effect

Expected statement-timeout cancellation at approximately one second; then ROLLBACK is needed to clear the aborted transaction.

54. Blindly retrying a non-idempotent payment insert

Before — weak pattern

INSERT INTO payments(request_key,amount) VALUES ('attempt-1',100);
-- Retry incorrectly uses a different key:
INSERT INTO payments(request_key,amount) VALUES ('attempt-2',100);
SELECT COUNT(*) AS payments,SUM(amount) AS total FROM payments;

Before — expected output or observed effect

payments=2, total=200.

After — corrected or controlled alternative

INSERT INTO payments(request_key,amount) VALUES ('checkout-123',100) ON CONFLICT(request_key) DO NOTHING;
-- Retry uses the SAME stable business request key:
INSERT INTO payments(request_key,amount) VALUES ('checkout-123',100) ON CONFLICT(request_key) DO NOTHING;
SELECT COUNT(*) AS payments,SUM(amount) AS total FROM payments;

After — expected output or observed effect

payments=1, total=100; retry inserts no row.

55. Stale planner statistics

Before — weak pattern

-- After a large import, statistics may still describe the old distribution.
EXPLAIN SELECT order_id FROM orders WHERE tenant_id=1 AND status='open';

Before — expected output or observed effect

An estimated execution plan. With stale statistics, estimated row counts can differ substantially from reality; no fixed plan is guaranteed.

After — corrected or controlled alternative

ANALYZE weak_sql_blog.orders;
EXPLAIN SELECT order_id FROM orders WHERE tenant_id=1 AND status='open';

After — expected output or observed effect

ANALYZE completes, then an estimated plan using refreshed sampled statistics. Actual values and chosen plan vary.

56. Redundant indexes that increase write work

Before — weak pattern

CREATE INDEX idx_salary_a ON weak_sql_blog.employees(salary);
CREATE INDEX idx_salary_b ON weak_sql_blog.employees(salary);
SELECT indexname FROM pg_indexes WHERE schemaname='weak_sql_blog'
AND indexname IN ('idx_salary_a','idx_salary_b') ORDER BY indexname;

Before — expected output or observed effect

Two rows: idx_salary_a, idx_salary_b.

After — corrected or controlled alternative

DROP INDEX weak_sql_blog.idx_salary_b;
SELECT indexname FROM pg_indexes WHERE schemaname='weak_sql_blog'
AND indexname IN ('idx_salary_a','idx_salary_b') ORDER BY indexname;

After — expected output or observed effect

One row: idx_salary_a.

Important context: After follows before in this section. Check constraints/dependencies and real workload before dropping indexes. Production may need CONCURRENTLY, which has transaction restrictions.

57. An index that does not match the main filter

Before — weak pattern

CREATE INDEX idx_unhelpful_name ON weak_sql_blog.employees(emp_name);
SELECT emp_id,salary FROM weak_sql_blog.employees
WHERE dept_id=10 ORDER BY salary DESC,emp_id;

Before — expected output or observed effect

Rows: (1,90000), (2,70000). A name index does not directly support the department filter or salary ordering.

After — corrected or controlled alternative

CREATE INDEX idx_matching_dept_salary ON weak_sql_blog.employees(dept_id,salary DESC,emp_id);
SELECT emp_id,salary FROM weak_sql_blog.employees
WHERE dept_id=10 ORDER BY salary DESC,emp_id;

After — expected output or observed effect

Same rows: (1,90000), (2,70000). A matching index access path is now available.

58. Disabled routine vacuuming

Before — weak pattern

ALTER TABLE weak_sql_blog.employees SET (autovacuum_enabled=false);
SELECT reloptions FROM pg_class WHERE oid='weak_sql_blog.employees'::regclass;

Before — expected output or observed effect

reloptions contains autovacuum_enabled=false.

After — corrected or controlled alternative

ALTER TABLE weak_sql_blog.employees RESET (autovacuum_enabled);
VACUUM (ANALYZE) weak_sql_blog.employees;
SELECT reloptions FROM pg_class WHERE oid='weak_sql_blog.employees'::regclass;

After — expected output or observed effect

VACUUM completes; reloptions is NULL if no other table options are set. Table uses inherited autovacuum settings.

Important context: Run VACUUM outside a transaction block. Ordinary VACUUM mainly enables internal space reuse; do not promise a smaller table file.

59. Missing an index on referencing foreign-key columns

Before — weak pattern

-- The foreign key already enforces integrity; it does not create this child index.
EXPLAIN SELECT order_id FROM weak_sql_blog.orders WHERE tenant_id=1 AND customer_id=1;

Before — expected output or observed effect

An estimated plan for matching orders; without a supporting index, a large child table may need a scan.

After — corrected or controlled alternative

CREATE INDEX idx_orders_fk ON weak_sql_blog.orders(tenant_id,customer_id);
SELECT order_id FROM weak_sql_blog.orders WHERE tenant_id=1 AND customer_id=1 ORDER BY order_id;

After — expected output or observed effect

Order IDs 101, 104; the new index can also support child-key checks for parent changes.

60. Wrong data types and repeated conversions

Before — weak pattern

CREATE TEMP TABLE weak_amounts(amount TEXT);
INSERT INTO weak_amounts VALUES ('20'),('100');
SELECT amount FROM weak_amounts ORDER BY amount;

Before — expected output or observed effect

Text order: 100, then 20 under ordinary digit ordering.

After — corrected or controlled alternative

CREATE TEMP TABLE safe_amounts(amount NUMERIC(12,2));
INSERT INTO safe_amounts VALUES (20),(100);
SELECT amount FROM safe_amounts ORDER BY amount;

After — expected output or observed effect

Numeric order: 20.00, then 100.00; decimal display formatting may vary.

Important context: These are separate tables. A real migration must validate existing text values, precision, scale, and operational locking.

61. Uncontrolled query concurrency

Before — weak pattern

# Illustrative application design: each request opens a connection.
for request in incoming_requests:
    spawn_worker(open_new_connection, request)  # no concurrency bound

Before — expected output or observed effect

Connection/query count grows with request bursts; latency and resource use can rise sharply. Exact behavior is workload-dependent.

After — corrected or controlled alternative

# Application pseudocode; pool API depends on the driver/framework.
pool = ConnectionPool(max_size=20, acquisition_timeout_seconds=2)
with pool.acquire() as connection:
    run_authorized_parameterized_query(connection)

After — expected output or observed effect

At most 20 checked-out connections per pool instance; excess requests wait or fail after the configured acquisition timeout.

Important context: Do not copy 20 as a universal sizing recommendation. It is an illustrative budget, not a benchmark result.

How to verify a real before-and-after improvement

1. State the required result and authorization boundary first. If a rewrite changes rows, ordering, duplicates, NULL handling, or transaction behavior, document why that change is intended.
2. Use representative data and parameter distributions. Four rows cannot demonstrate the value of an index.
3. Inspect estimated plans with EXPLAIN. For safe SELECT workloads, EXPLAIN (ANALYZE, BUFFERS) can measure actual execution. Avoid treating mutation analysis as read-only.
4. Compare actual/estimated row counts, rows removed by filters, loops, buffer activity, sort spills, and end-to-end latency. Account for caching and realistic concurrency.
5. Check added costs: extra indexes slow writes and consume space; batching changes atomicity; sampling changes result guarantees; caching changes freshness.
6. Test unauthorized cases using the real runtime role. Include another tenant’s record, rejected sort keys, SQL metacharacters as bound values, and sensitive response fields.
7. Verify failures: affected-row counts, rollback, timeout recovery, pool cleanup, concurrent stock changes, and repeated business requests.

Interview revision table

Official references

These are further-reading sources, not evidence of a benchmark on this document’s dataset.

• PostgreSQL performance tips
• PostgreSQL EXPLAIN
• PostgreSQL expression indexes
• PostgreSQL index types
• PostgreSQL LIMIT and OFFSET
• PostgreSQL explicit locking
• PostgreSQL row-security policies
• PostgreSQL CREATE FUNCTION and security considerations
• PostgreSQL routine vacuuming
• OWASP SQL injection prevention
• OWASP password storage

Colorful architecture: reviewing a weak SQL request

flowchart TD
    INPUT["Application request"]:::blue --> VALUES["Bind values and allowlist identifiers"]:::purple
    VALUES --> AUTH["Verify identity, tenant, and privileges"]:::purple
    AUTH --> QUERY["Correct predicates, joins, and bounded results"]:::green
    QUERY --> PLAN["Inspect plans and measure representative data"]:::yellow
    INDEX["Suitable indexes and planner statistics"]:::orange --> PLAN
    PLAN --> RUN["Execute within transaction and resource budgets"]:::green
    RUN --> RESULT["Return only authorized fields and rows"]:::blue
    RUN --> FAIL["Rollback, redact errors, and retry safely"]:::pink
    FAIL --> OPS["Protected logs, cleanup, and concurrency controls"]:::pink
    classDef blue fill:#dbeafe,stroke:#2563eb,color:#172554,stroke-width:2px;
    classDef purple fill:#f3e8ff,stroke:#9333ea,color:#581c87,stroke-width:2px;
    classDef green fill:#dcfce7,stroke:#16a34a,color:#14532d,stroke-width:2px;
    classDef yellow fill:#fef9c3,stroke:#ca8a04,color:#713f12,stroke-width:2px;
    classDef orange fill:#ffedd5,stroke:#ea580c,color:#7c2d12,stroke-width:2px;
    classDef pink fill:#fce7f3,stroke:#db2777,color:#831843,stroke-width:2px;

This diagram summarizes review responsibilities, not PostgreSQL’s internal execution order. Mermaid requires support from the Markdown publisher.