* Parameterize SQLite user-DB driver queries (eliminate SQL injection class)
The SQLite driver built every statement with snprintf, interpolating
caller-supplied values directly into the SQL text before sqlite3_prepare.
That left every value-carrying query injectable (the admin-panel delete
paths fixed in GHSA-v8hj-2xx7-xmp5 were the reachable instances; this
removes the underlying class for this backend).
Add a sqlite_prepare_bind() helper that prepares a statement using '?'
placeholders and binds the values as text parameters, and route all
value-carrying statements through it: users, secrets, origins, realm
options, oauth keys, permission IPs, and admin users. Values never enter
the SQL text again. The only remaining interpolation is the peer-ip
table name (allowed/denied), which cannot be a bound parameter and is
already validated against that whitelist by the caller; its realm/ip
values are now bound. Static, parameter-free statements are unchanged.
oauth timestamp/lifetime were previously rendered as numeric literals;
they are now formatted to decimal text and bound. The oauth_key columns
have integer affinity, so SQLite stores the bound text as the same
integer and reads it back identically (verified).
Add tests/test_sqlite_dbd.c: an interface test that drives the driver's
public vtable against a throwaway database, covering every converted
entry point plus a SQL-injection regression test. Run against the old
string-interpolated driver the seven behavioral tests pass unchanged
(parity) while the injection test fails (a boolean payload in del_user
deletes a whole realm's users); against the new driver all eight pass.
The driver is compiled in isolation via a small support/stub layer
(tests/test_sqlite_support.*) so the suite needs no running server.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
* Parameterize PostgreSQL user-DB driver queries (eliminate SQL injection class)
Like the SQLite driver, dbd_pgsql.c built every statement with snprintf,
interpolating caller-supplied values into the SQL text and running it via
PQexec -- which on PostgreSQL also permits stacked queries, the highest-
impact instance of GHSA-v8hj-2xx7-xmp5.
Add a pq_exec_params() helper around PQexecParams and route all
value-carrying statements through it with $1,$2,... placeholders and the
values passed as out-of-band text parameters: users, secrets, origins,
realm options, oauth keys, permission IPs, and admin users. Values never
enter the SQL text. The only remaining interpolation is the peer-ip table
name (allowed/denied), which cannot be a bound parameter and is already
whitelisted by the caller; its realm/ip values are now bound. Static,
parameter-free SELECTs keep using PQexec.
oauth timestamp/lifetime and the realm-option value were numeric literals;
they are now formatted to decimal text and bound (PostgreSQL casts them to
the column's integer type, preserving behavior).
Add tests/test_pgsql_dbd.c with a link-seam libpq mock (test_pgsql_stub.c)
that captures each emitted command, whether it was parameterized, and the
bound parameter values -- so the driver is unit-tested with no server. The
tests assert every operation emits a parameterized query with caller values
bound out-of-band, and test_sql_injection_neutralized asserts a boolean
payload travels as a bound value, never as SQL text. Run against the old
driver all of these fail (it calls PQexec with values interpolated and binds
nothing); against the new driver all pass. Relay-side stubs are reused from
test_sqlite_support.c.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
* Parameterize MySQL user-DB driver with prepared statements (eliminate SQL injection class)
dbd_mysql.c built every statement with snprintf and ran it via mysql_query(),
interpolating caller-supplied values into the SQL text -- the same SQL injection
class fixed for SQLite and PostgreSQL, now closed for the MySQL backend.
Convert the whole driver to the prepared-statement API. Two helpers carry the
risk:
- my_exec() prepares an INSERT/UPDATE/DELETE, binds text params, executes;
- my_query_rows() prepares a SELECT, binds text params and text result
columns, and invokes a per-row callback (replacing mysql_query +
mysql_store_result + mysql_fetch_row).
Every value-carrying statement now uses '?' placeholders with values bound
out-of-band; the read paths get small row callbacks. The only remaining
interpolation is the peer-ip table name (allowed/denied), which cannot be a
bound parameter and is already whitelisted by the caller; its realm/ip values
are bound. oauth timestamp/lifetime and the realm-option value are formatted to
decimal text and bound (MySQL casts to the column's integer type).
Add tests/test_mysql_dbd.c with a link-seam libmysqlclient mock
(test_mysql_stub.c) that captures the prepared SQL and bound parameters, and can
feed one canned result row so the read paths' bind-result/fetch handling is
exercised (get_user_key, get_oauth_key, get_admin_user, get_auth_secrets all
round-trip). The mock also implements the classic mysql_query/store_result API
so the suite links and runs against the old driver: there every test fails ("got
mysql_query()") while all pass against the new one, and
test_sql_injection_neutralized asserts a boolean payload travels as a bound
value. Relay-side stubs are reused from test_sqlite_support.c (with
ur_string_map_free added there).
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
---------
Co-authored-by: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Pavel Punsky
bfacd81627