How QoreDB detects and prevents destructive queries

A database client exposes, with a single click, operations that can empty a table, drop a schema, or disable integrity guarantees. When the SQL editor accepts any text that is typed in, the responsibility of filtering what can run without confirmation falls on the client itself.

In QoreDB, this classification does not rely on a keyword search in the edit bar. It is based on a full SQL parser, run on every submission, and on the environment declared for the connection. This article describes how this mechanism works and why it was designed this way.

Why an SQL parser rather than a regex

Detecting a destructive query with a regex is tempting because it is simple to write. Searching for DROP, TRUNCATE or DELETE seems to cover the essentials. In practice, this approach has two flaws.

The first is that it produces false positives. A column named delete_at, a comment containing DROP TABLE, an alias, or a literal is enough to trigger the alarm. The second is more serious: the regex ignores the syntactic context. It cannot distinguish UPDATE users SET name = 'x' (updating every row) from UPDATE users SET name = 'x' WHERE id = 1 (updating a single row) without risking other false positives.

QoreDB therefore uses sqlparser-rs, a Rust parser that returns a typed AST. Classification is done by visiting the tree, not by searching for strings.

• A Statement::Drop, Statement::Truncate or Statement::AlterTable is flagged as dangerous.
• A Statement::Update or Statement::Delete is only flagged as dangerous if its selection field (the WHERE clause) is None.
• A SELECT ... INTO is detected as a mutation thanks to the select.into.is_some() field.

This analysis runs for every query, without depending on any particular mode. The cost of a full parse is not negligible for long queries, so an LRU cache of 256 entries per (driver, sql) pair avoids re-parsing an identical query that is run several times within the session.

A backend-side analysis, not just a UI warning

The UI displays the associated banners and confirmations, but the check cannot live solely in the frontend. The same IPC command is called from several entry points: the SQL editor, the data grid's inline edit, export, and certain plugin hooks. Concentrating the logic at the level of the Rust command ensures that no call path can bypass the classification.

The result of the analysis lives in the SqlSafetyAnalysis { is_mutation, is_dangerous } struct. This struct is consumed by the query pipeline before execution. That is the moment the decision is made: proceed with execution, request a confirmation, or refuse.

The role of the connection environment

A DROP TABLE query does not carry the same weight depending on the target database. On a throwaway local instance, it is harmless. On a production replica, it is an incident. QoreDB attaches a declared environment to each connection: Dev, Staging, or Prod. The classification produced by the parser is then cross-referenced with this environment and the active policy.

Concretely, in a Prod environment and with a query flagged as dangerous, two behaviors are possible, depending on the policy.

• If prod_block_dangerous_sql is enabled, the query is rejected outright, without being run.
• Otherwise, if prod_require_confirmation is enabled and no explicit acceptance has been provided, the query is refused with a confirmation request.

This explicit acceptance is not a simple boolean passed from the frontend. For the most sensitive IPC actions, QoreDB requires a confirmation token: the UI requests it from a dedicated endpoint, the backend issues it with a 60-second TTL, bound to a specific action name, and it is consumed exactly once. A direct call to the Tauri binding therefore cannot forge the acceptance; it must follow exactly the same cycle.

Beyond the classics: engine-specific dangers

DROP and TRUNCATE are not enough to cover every case where a query can widen the attack surface or break the database. Two engines get additional classifiers.

DuckDB exposes commands that can exfiltrate data or bring in code, and that do not appear within the classic scope of destructive queries.

• INSTALL <extension> downloads an extension from a repository.
• LOAD <extension> potentially enables network or disk I/O within the session.
• ATTACH '...' mounts an arbitrary database, including over HTTP.
• COPY ... TO '<path>' writes to a filesystem path that the user may not have intended to authorize.
• PRAGMA enable_external_access reopens external access within the process.

SQLite, for its part, has PRAGMAs that alter the database's guarantees without touching the data:

• ATTACH DATABASE '...' mounts an arbitrary file.
• PRAGMA writable_schema = 1 allows editing sqlite_master directly.
• PRAGMA journal_mode = OFF disables durability.
• PRAGMA foreign_keys = OFF disables referential integrity.

These classifiers rely on inspecting the leading keyword of the query, after stripping comments and whitespace. They are conservative: only explicitly dangerous forms are flagged. A PRAGMA table_info(users) or a COPY t FROM 'file.csv' pass through without trouble, because they do not open any additional capability beyond what the session already has.

ClickHouse is handled separately. Its dialect (ENGINE, ARRAY JOIN, FORMAT, SETTINGS) is not fully covered by sqlparser-rs, and strict parsing would produce errors on valid SQL. QoreDB therefore falls back to a keyword-based classifier for ClickHouse, also conservative: any unrecognized segment is treated as a mutation, which keeps the safeguard active in read-only mode.

The result on the user side

The practical trade-off of this architecture is visible in day-to-day use. In a Dev environment, the mechanism is silent. A DELETE FROM users query goes straight through, as expected. In Staging, the is_mutation and is_dangerous markers feed the interceptor and the plugin hooks without blocking execution. In Prod, the classification triggers either an immediate rejection or a confirmation request, depending on the chosen policy.

The cost for the developer is minimal: two parsing passes for a new query, an LRU cache read for a repeated query. The classification structure (is_mutation and is_dangerous) remains the single entry point for all downstream decisions: read-only mode, prod confirmation, interceptor pipeline, hooks. It is a single place to reason about and a single place to test.

Conclusion

Destructive-query detection in QoreDB rests on three deliberate principles: an AST parser rather than a regex, a backend-side analysis rather than one in the UI, and a decision weighted by the environment declared for the connection. The result is a mechanism that stays discreet in development, reliable in production, and auditable from a single Rust file. This is precisely the trade-off a desktop client for a developer or a small team is looking for.