* fix(io): run the sql filters on a DuckDB connection without external access
`…ExportMixin._filter_by_sql` ran the caller's WHERE clause as
`duckdb.sql(f"FROM df WHERE {sql}")`, on DuckDB's default in-process
connection. That connection has file system and extension access, so a
clause could call `read_csv`, `read_text`, `glob` or a bare file path in
a subquery and read or list any file the wetterdienst process can. The
REST API (`sql`, `sql_values` on stations, values, interpolate and
summarize) and the MCP server pass the clause on unchanged, so any client
of a deployed server could read its files by comparing a column against
such a subquery and watching which rows came back.
The filter now opens a connection of its own with
`enable_external_access=false` and `lock_configuration=true`, registers
the frame on it as `df`, runs the clause there and closes it. DuckDB then
refuses file and network access, `INSTALL`, `LOAD`, `ATTACH` and
`COPY ... TO` with `duckdb.PermissionException`, refuses to turn external
access back on, and with the configuration locked refuses a `SET` of any
other setting. Checked on duckdb 1.0.0, the declared floor, and on
1.5.5, so the floor stays.
On main a refused clause reaches the REST API's catch-alls, which answer
it as a client error: 400 from stations and values, 404 from interpolate
and summarize. The renamed-column hint on `BinderException` is unchanged.
Refs GHSA-rpwr-qmm5-m9wp
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* test(io): match the refusal of INSTALL and of a URL on its wording
Both cases matched an empty pattern, which pytest warns always passes.
"disabled" is in DuckDB's message for each on 1.0.0 ("Installing
extensions is disabled", "Scanning read_csv files is disabled") and on
1.5.5 ("file system operations are disabled"), so it pins the refusal
without pinning one release's wording.
Refs GHSA-rpwr-qmm5-m9wp
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* fix(io): take the sql filter clause as a single condition
The clause was pasted into `FROM df WHERE {sql}` and run as a script, so
anything after a `;` ran too and its result came back in place of the
filtered frame: `true; SELECT * FROM duckdb_settings()` returned DuckDB's
settings, among them `secret_directory`, which names the server's home
directory, and `true; CREATE TABLE t AS SELECT 1` returned no relation,
which surfaced as an `AttributeError` on `None.pl()`.
The filter now applies the clause with `con.table("df").filter(sql)`,
which parses it as one expression: a second statement, `ORDER BY` or
`LIMIT` raises `duckdb.ParserException`. Subqueries and a trailing
semicolon are still accepted, and the binder's messages for a missing
column are the same, so the renamed-column hint, qualified `df.height`
included, keeps working. Checked on duckdb 1.0.0 and 1.5.5.
The single condition also takes `SET`, `INSTALL` and `LOAD` out of a
clause's reach, so the tests for those now sit under the parser refusal.
The existing test of DuckDB's own error for a column missing only where
the query looks built its case with `UNION ALL`; it now uses a subquery,
where DuckDB names the table it looked in.
Refs GHSA-rpwr-qmm5-m9wp
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* fix(io): run the sql filter on one thread within 1 GB and no disk
The filter's connection took DuckDB's defaults: a thread per core and a
memory limit of 80% of the host's RAM, spilling to a temp directory past
that. A clause comes from any REST or MCP client, and one subquery such
as `(SELECT list(i) FROM range(1e9) t(i))` could take every core and most
of the memory of the server for as long as it ran.
The connection now sets `threads=1`, `memory_limit=1GB` and
`max_temp_directory_size=0B` in the same locked config, so a clause gets
one core and fails with `duckdb.OutOfMemoryException` when its query goes
past 1 GB, instead of spilling. The frame is registered as an Arrow
scan, which does not count against the limit; measured on duckdb 1.5.5,
a 20-million-row (658 MB) values frame filters with
`value IN (SELECT value FROM df WHERE value % 3 = 0)` in 1.6 s and with
`value > (SELECT avg(value) FROM df)` in 0.5 s under these settings, so
1 GB leaves room for what a filter ordinarily builds. All three settings
exist on duckdb 1.0.0 with the same effect, so the floor stays.
What this does not bound: CPU time on that one core (DuckDB has no
statement timeout to set here), and allocations DuckDB makes outside its
buffer manager, such as one very long string from `repeat()`.
Refs GHSA-rpwr-qmm5-m9wp
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* fix(io): give the sql filter's connection no temp directory
With external access off, DuckDB still lets its own temp directory
through: `allowed_directories` holds `<working directory>/.tmp/`, so a
clause could read and list files there with `read_text('.tmp/...')` or
`glob('.tmp/*')`. Setting `temp_directory` to empty empties that list,
and both are refused with `duckdb.PermissionException`, on duckdb 1.5.5;
1.0.0 has no `allowed_directories` and refused both already. Spilling
was off through `max_temp_directory_size=0B` before, so nothing else
changes.
The memory limit covers DuckDB's buffers, not every allocation: one
long string from `repeat()` grows past it without an error, and nothing
bounds how long a clause runs on its one core. The CHANGELOG entry, the
docstring and the comment said a query over 1 GB is refused and a client
can no longer tie up the server; they now say what the limit covers.
The settings test no longer pins how DuckDB words the memory limit
("953.6 MiB"), which the out-of-memory refusal checks already.
Refs GHSA-rpwr-qmm5-m9wp
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* fix(io): turn external access off after the sql filter's other settings
The connection took every setting in one config dict, but DuckDB does not
apply a config dict in the order it is written, and from 1.2 on it
refuses an empty `temp_directory` once external access is off
("Modifying the temp_directory has been disabled by configuration").
Which of the two it applied first depended on the other keys present:
the committed set happened to work, and dropping `max_temp_directory_size`
from it made every filter call fail.
The connection now opens with the temp directory, threads and memory
settings, then runs `SET enable_external_access = false` and
`SET lock_configuration = true`, in that order. Checked on duckdb 1.0.0,
1.2.2 and 1.5.5: the settings read back as configured, files under
`.tmp/`, absolute paths, bare paths and URLs are refused, INSTALL and
LOAD are refused, a list past 1 GB raises OutOfMemoryException, and a
`SET` afterwards is refused as locked.
Refs GHSA-rpwr-qmm5-m9wp
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* fix(io): refuse anything after the sql filter's condition, size the limit by the frame
Two defects in the previous commits, found by review.
`con.table("df").filter(sql)` takes a filter expression, but DuckDB
drops what follows one without a word: a trailing `FROM` or `AS` on
1.5.5, and on 1.0 to 1.4 a trailing `ORDER BY`, `LIMIT` or `WHERE` too,
so `elevation > 1 WHERE false` returned every row there, and the
docstring, CHANGELOG and tests claimed a refusal that only 1.5 gave. The
clause is now parenthesised, `({clause}\n)`, after trailing semicolons
are stripped: each of those raises `ParserException` on 1.0.0 and 1.5.5,
a trailing `--` comment is still taken (the newline keeps it from
commenting out the closing parenthesis), and so is `region = 'Bayern';`.
The 1 GB limit applied to every caller and did not grow with the frame,
but a subquery over the frame builds in proportion to it: on a
40-million-row, 1.3 GB values frame, `station_id IN (SELECT station_id
FROM df WHERE value > 10)` raised OutOfMemoryException at 1 GB where it
had worked before. The limit is now 1 GB plus the frame's estimated
size; that filter then runs in 4.5 s. The frame is already in the
process's memory, so the bound stays proportional to what the request
fetched.
The CHANGELOG entry now says what to do instead of `ORDER BY` or
`LIMIT`. The first case of the test of DuckDB's own error for a column
the frame has, missing only where the query looks, is gone: a subquery
in a single condition falls back to the frame's column, so DuckDB no
longer reports such a column missing, and the rewritten case never
reached the hint code. The REST test's out-of-memory case runs under a
64 MB limit, so it no longer fills a gigabyte.
Refs GHSA-rpwr-qmm5-m9wp
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* docs(changelog): mark the sql filter entry breaking, name the default connection
The entry removes what the old `FROM df WHERE {sql}` accepted: an
`ORDER BY`, `LIMIT` or appended statement, and tables a library caller
created on DuckDB's default connection, which the filter's own
connection does not hold. It now carries the **Breaking** marker and
names the default connection along with files, network and extensions.
Refs GHSA-rpwr-qmm5-m9wp
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* fix(io): drop every trailing semicolon from the sql filter clause, say what it raises
`sql.rstrip().rstrip(";")` left `value > 1 ; ;` with a semicolon in
front of the closing parenthesis, which DuckDB refused, although the
docstring said trailing semicolons are dropped. Trailing semicolons and
whitespace are now dropped together, `re.sub(r"[\s;]+$", "", sql)`.
Prose that did not match the code, found by review:
- A table of DuckDB's default connection raises `CatalogException` and
an extension DuckDB would autoload raises the base `duckdb.Error`, not
`PermissionException`; the CHANGELOG and docstring now say a
`duckdb.Error`, and the CHANGELOG says what to do instead (join or
filter the returned frame with polars).
- A clause that closes the parentheses around it itself has a trailing
`FROM` ignored by DuckDB rather than refused; the docstring says so.
- The memory limit is 2**30 bytes plus the frame, so 1 GiB, not 1 GB,
and DuckDB words it "1.0 GiB", not "953.6 MiB" as a test comment said.
Refs GHSA-rpwr-qmm5-m9wp
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* fix(io): strip the sql filter clause with rstrip, not a backtracking regex
`re.sub(r"[\s;]+$", "", sql)` from the previous commit backtracks
quadratically over a long run of whitespace or semicolons that is
followed by another character: review measured 4 s at 40,000 characters
and about 25 s at 100,000. The clause is the caller's, neither the REST
nor the MCP field caps its length, and `re` holds the GIL, so one request
froze every thread before DuckDB's limits applied. `str.rstrip` with
whitespace and `;` strips the same characters in linear time.
The docstring paragraph that commit rewrapped by hand is reflowed.
Refs GHSA-rpwr-qmm5-m9wp
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* fix(io): strip Unicode whitespace from the end of the sql filter clause too
The previous commit replaced `re.sub(r"[\s;]+$", ...)` with
`sql.rstrip(string.whitespace + ";")` and said it stripped the same
characters. It does not: `\s` matches Unicode whitespace, while
`string.whitespace` is ASCII only, so `region = 'Bayern';` followed by
U+3000 or a no-break space, as a clause pasted from a web page ends
with, raised `ParserException` again.
The clause is now stripped in one pass from the end over characters that
are `isspace()` or `;`, which is what the regex matched, in linear time.
Alternating `rstrip()` and `rstrip(";")` would copy the string once per
`; ` pair and be quadratic again. The test now also covers a 100,000
character alternating tail, a mixed ASCII tail, and U+3000, U+00A0 and
U+2028.
Refs GHSA-rpwr-qmm5-m9wp
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* feat(restapi)!: refuse sql and sql_values unless the server enables them
The hardened filter still leaves a clause from any client able to read
DuckDB's settings (which name paths on the server), run for as long as
it likes on its one thread, and allocate memory DuckDB's limit does not
count, and its limits hold per request, not for the server. Whether to
take that from anyone who can reach the API is the operator's call, so
the REST API now refuses `sql` (stations, values) and `sql_values`
(values, interpolate, summarize) unless the new setting `restapi_sql` is
on (`WD_RESTAPI_SQL=true`). The default is off.
The check runs first in each of the four endpoints, before the provider
is looked up, so a refused request fetches nothing. It answers 403 with
a message naming the parameters and the setting. 403 rather than 400:
the request is well formed and the deployment declines it, as the
missing BUFR reader answers 501 rather than blaming the caller; a 400
would tell the caller their request is wrong. The MCP tools are
generated from these routes and go through them, so they are gated with
them. The library and the CLI, whose caller is the host's own user, are
not gated.
The app does not send either parameter: its query panel runs SQL in
DuckDB-wasm in the browser.
The OpenAPI descriptions of both fields, the stations docstring (the
MCP tool description), the settings table and the REST API docs say so;
the REST API docs gain a section on what an enabled clause can and
cannot do, and their values example passed `sql` where it meant
`sql_values`.
Refs GHSA-rpwr-qmm5-m9wp
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* docs(python-api): name the sql filter's table df and say what the clause may hold
The SQL section said the data is a virtual table called `data`; the
filter registers it as `df`, so a subquery written from the docs failed
with a CatalogException. It now says `df`, that the filter takes a
single condition (no second statement, ORDER BY or LIMIT; sort or slice
the returned frame instead), and that the condition runs on a DuckDB
connection of its own without file, network or extension access.
Refs GHSA-rpwr-qmm5-m9wp
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* test: hold the SQL gate's route list to the OpenAPI schema, pin its default
The gate is a call in each of the four endpoints, and its tests list the
five route and parameter pairs by hand, so a route that later offered
`sql` or `sql_values` without the call would be open and the tests
green. A new test collects every route parameter named `sql` or
`sql_values` from the app's OpenAPI schema and requires the list to
match it. `test_default_settings` now also asserts `restapi_sql` is off.
Refs GHSA-rpwr-qmm5-m9wp
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* test(settings): clear WD_RESTAPI_SQL before asserting its default
The new assertion read the process environment, so a developer who had
exported WD_RESTAPI_SQL=true to try a server with SQL saw the default
test fail. It is cleared alongside WD_CACHE_DIR now.
Refs GHSA-rpwr-qmm5-m9wp
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* fix(restapi): count the sql filter's refusals as the caller's
#2271 answers a failure of values, interpolate or summarize with a 500
unless `_is_caller_refusal` names it the caller's; a DuckDB
`ProgrammingError` or `DataError` already counts. The filter's own
connection refuses three more things a clause can ask for, and each is
the clause's to rephrase: a file, URL or extension it may not touch
(`PermissionException`), more memory than the limit sized to the frame
(`OutOfMemoryException`), and an extension DuckDB would install on the
fly, which DuckDB refuses with the bare `duckdb.Error`. Through
`/api/values` all three were 500s.
Kept narrow in two ways. The bare `duckdb.Error` is the base of every
DuckDB error, so only that exact class counts: an `InternalException`
or `IOException` stays a 500. And the three count only where the
request carries `sql` or `sql_values`, so DuckDB running out of memory
for a request without a clause stays the server's failure, as #2271's
own test has it. `_is_caller_refusal` takes the request for that.
The REST tests assert the exact status: 400 from stations (its own
catch-all) and from values for a refused file read, an appended
statement, a clause over the memory limit and an extension autoload,
all with SQL enabled; 500 for an `InternalException` or `IOException`
with a clause.
Refs GHSA-rpwr-qmm5-m9wp
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* fix(restapi): count a construct DuckDB does not implement as the clause's too
Review found a clause DuckDB parses but does not implement, an outer
join on a correlated column, raised as NotImplementedException (a
NotSupportedError), which _is_caller_refusal left a 500. It now counts
as the caller's where the request carries a clause, like the other
refusals of the filter's connection.
The extension case of the REST test could pass through another branch
on a machine with the extension installed, so a stub raising the bare
duckdb.Error now pins that branch, with a clause (400) and without
(500). #2271's out-of-memory test docstring says it holds for a request
without a clause, which is what it sends.
Refs GHSA-rpwr-qmm5-m9wp
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
---------
Co-authored-by: Claude Opus 5.5 <noreply@anthropic.com>