# How DBX Performs Query Execution and SQL Risk Analysis: A Deep Dive

> Learn how DBX executes queries and analyzes SQL risks with its layered architecture. Discover AST parsing and keyword detection for security before database dispatch.

- Repository: [skyler/dbx](https://github.com/t8y2/dbx)
- Tags: deep-dive
- Published: 2026-07-05

---

**DBX separates query execution from security enforcement through a layered architecture that uses AST parsing and keyword-based fallback detection to classify SQL risks before database dispatch.**

The `t8y2/dbx` repository implements a sophisticated database proxy that handles **DBX query execution** and **SQL risk analysis** through distinct architectural layers. By parsing SQL with `sqlparser` and maintaining strict read-only guards, DBX ensures that data-modifying statements never reach protected connections. This implementation combines protocol-agnostic RPC parameter building with dialect-aware risk classification to provide defense-in-depth for database operations.

## Query Execution Architecture

DBX routes SQL queries through a three-tier pipeline: web API reception, core parameter construction, and driver-specific dispatch. This separation allows the system to intercept and analyze queries before they reach the database engine.

### HTTP Endpoint and Request Handling

The execution flow begins at the web layer in [`crates/dbx-web/src/routes/query.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-web/src/routes/query.rs). The `execute_query` handler processes POST requests to `/query/execute`, extracting the SQL payload and optional dialect specifications from the JSON body.

```rust
// crates/dbx-web/src/routes/query.rs
pub async fn execute_query(...) { 
    // Extracts SQL and forwards to core execution logic
}

```

This handler immediately delegates to the core entry point, ensuring that all subsequent processing—including **SQL risk analysis**—occurs within the `dbx-core` crate before any database driver is invoked.

### Core Query Builder and RPC Parameters

Inside [`crates/dbx-core/src/query.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/query.rs), the `agent_execute_query_params` function (lines 671–696) constructs a `QueryExecutionOptions` struct that standardizes the request for cross-process communication. This function packages the SQL string, target database name, schema context, and execution constraints into protocol-agnostic RPC parameters.

```rust
// crates/dbx-core/src/query.rs
pub fn agent_execute_query_params(sql, db, schema, opts) -> QueryExecutionOptions { 
    // Builds transportable execution parameters
}

```

The resulting structure ensures that every database driver—whether PostgreSQL, MySQL, or SQLite—receives uniformly formatted input, decoupling the web API from specific database implementations.

### Driver Dispatch and Execution

The [`transfer.rs`](https://github.com/t8y2/dbx/blob/main/transfer.rs) module (lines 2373–2415) handles the final dispatch to concrete database drivers. A match on `DatabaseType` selects the appropriate driver implementation, calling uniform trait methods like `execute_query_with_max_rows`.

```rust
// crates/dbx-core/src/transfer.rs
db::postgres::execute_query_with_max_rows(&pool, sql, max_rows).await

```

Each driver returns a standardized `QueryResult` containing row data, column metadata, and error information. This uniformity allows the web layer to encode responses as JSON without needing driver-specific logic.

## SQL Risk Analysis System

Before any query reaches the driver layer, DBX subjects it to **SQL risk analysis** using a two-stage classification system. This architecture guarantees that read-only connections remain protected even when facing non-standard or vendor-specific SQL syntax.

### AST-Based Classification with sqlparser

The primary risk detection occurs in [`crates/dbx-core/src/sql_risk.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/sql_risk.rs) (lines 59–113). The `classify_sql_risk` function parses the complete SQL string using the `sqlparser` crate with dialect-aware configuration. Each `Statement` node is inspected by `classify_statement`, which maps AST variants to one of four `SqlRisk` enum values:

- **ReadOnly**: SELECT, SHOW, EXPLAIN, and other non-mutating operations
- **Write**: INSERT, UPDATE, DELETE, and data-modifying statements  
- **Ddl**: CREATE, DROP, ALTER, and schema-modifying statements
- **Transaction**: BEGIN, COMMIT, ROLLBACK, and transaction control

For multi-statement queries, the classifier returns the **highest** risk level present among all statements, ensuring that a single write operation in a batch triggers appropriate security measures.

### Keyword-Based Fallback Detection

When `sqlparser` encounters unsupported syntax or vendor-specific extensions, DBX falls back to the keyword scanner in [`crates/dbx-core/src/query_execution_sql.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/query_execution_sql.rs). The `is_write_sql` function strips comments and string literals, then checks the first keyword against a whitelist of read-safe operations (`SELECT`, `WITH`, `SHOW`, etc.) while scanning for dangerous keywords (`DROP`, `INSERT`, `DELETE`, etc.).

This fallback also detects unsafe `PRAGMA` forms and stored procedure calls (`CALL`/`EXEC`), ensuring that parsing failures never result in security bypasses.

### Risk Classification Enum and Logic

The `SqlRisk` enum provides a strict ordering where `Write` and `Ddl` outrank `ReadOnly`. When classifying batches, DBX aggregates risks using the maximum severity principle:

```rust
use dbx_core::sql_risk::{classify_sql_risk, SqlRisk};

let risk = classify_sql_risk("INSERT INTO t (c) VALUES (1)", "postgres")?;
assert_eq!(risk, SqlRisk::Write);

```

Plugins and external tools can import this classifier to enforce custom policies without accessing the underlying database connections.

## Read-Only Protection and Security Enforcement

DBX implements a final enforcement barrier immediately before driver dispatch, creating a defense-in-depth strategy that validates risk classifications against connection permissions.

### Pre-Execution Risk Validation

The `check_read_only` function in [`crates/dbx-core/src/query_execution_sql.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/query_execution_sql.rs) (lines 54–78) serves as the gatekeeper for read-only connections. This function receives the SQL string and connection name, returning an error before any network call to the database:

```rust
// crates/dbx-core/src/query_execution_sql.rs
pub fn check_read_only(sql: &str, connection_name: &str) -> Result<(), String> {
    if is_write_sql(sql) {
        Err(format!(
            "Read‑only mode: connection '{}' has read‑only protection enabled. Write operation (including stored procedure calls) blocked.",
            connection_name
        ))
    } else {
        Ok(())
    }
}

```

When a connection is configured as read-only, DBX calls this validator after the AST classification but before [`transfer.rs`](https://github.com/t8y2/dbx/blob/main/transfer.rs) dispatches to the driver. If either the parser or the keyword scanner detects a write operation, the system returns an HTTP 400 error with a descriptive message naming the protected connection.

### Defense-in-Depth Strategy

The **DBX query execution** pipeline implements layered security through sequential validation:

1. **AST Analysis**: Full parsing with `sqlparser` for standard SQL
2. **Keyword Fallback**: Pattern matching for non-standard syntax  
3. **Permission Check**: Connection-level read-only validation via `check_read_only`
4. **Driver Execution**: Final database interaction only after all validations pass

This architecture ensures that evenzero-day parsing vulnerabilities in `sqlparser` are mitigated by the keyword-based `is_write_sql` guard, while the connection-level check prevents configuration errors from exposing write capabilities to read-only agents.

## Practical Implementation Examples

### Executing a Read-Only Query

The following example demonstrates a successful **DBX query execution** flow for a SELECT statement:

```rust
use dbx_core::query::execute_query;
use dbx_core::models::connection::DatabaseType;

let sql = "SELECT name FROM users WHERE active = true";
let opts = ExecuteQueryOptions {
    database_type: Some(DatabaseType::Postgres),
    sql: sql.to_string(),
    // …other options…
};

let result = execute_query(opts).await?;
println!("Rows: {}", result.rows.len());

```

In this flow, `classify_sql_risk` parses the statement as `ReadOnly`, `check_read_only` passes without error, and the PostgreSQL driver in [`transfer.rs`](https://github.com/t8y2/dbx/blob/main/transfer.rs) executes the query and returns the result set.

### Blocking Write Operations in Read-Only Mode

When attempting a write operation against a protected connection, DBX intercepts the request before database contact:

```rust
let sql = "DELETE FROM users WHERE id = 42";
let res = execute_query(opts).await;
assert!(res.is_err()); // -> "Read‑only mode: connection 'prod' …"

```

Here, `classify_sql_risk` tags the statement as `Write`, triggering `check_read_only` to return an error immediately after parameter construction in [`query.rs`](https://github.com/t8y2/dbx/blob/main/query.rs) but before the driver dispatch in [`transfer.rs`](https://github.com/t8y2/dbx/blob/main/transfer.rs).

### Direct Risk Classification Access

Developers can use the risk classifier independently of the execution pipeline:

```rust
use dbx_core::sql_risk::{classify_sql_risk, SqlRisk};

let risk = classify_sql_risk(
    "CREATE TABLE test (id INT)", 
    "mysql"
)?;
assert_eq!(risk, SqlRisk::Ddl);

```

This allows external tools to audit SQL batches or implement custom routing logic based on the `SqlRisk` classification.

## Summary

- **DBX query execution** flows through three layers: web API ([`query.rs`](https://github.com/t8y2/dbx/blob/main/query.rs)), core parameter builder ([`query.rs`](https://github.com/t8y2/dbx/blob/main/query.rs)), and driver dispatch ([`transfer.rs`](https://github.com/t8y2/dbx/blob/main/transfer.rs)).
- **SQL risk analysis** uses `sqlparser` for AST-based classification into `ReadOnly`, `Write`, `Ddl`, and `Transaction` categories.
- A keyword-based fallback in [`query_execution_sql.rs`](https://github.com/t8y2/dbx/blob/main/query_execution_sql.rs) provides security when parsing fails, detecting dangerous operations via pattern matching.
- The `check_read_only` function enforces connection-level permissions immediately before driver execution, preventing write operations on protected connections.
- All risk analysis occurs in [`crates/dbx-core/src/sql_risk.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/sql_risk.rs) and [`crates/dbx-core/src/query_execution_sql.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/query_execution_sql.rs), while execution logic resides in [`crates/dbx-core/src/transfer.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/transfer.rs).

## Frequently Asked Questions

### How does DBX classify SQL statements for risk analysis?

DBX uses the `classify_sql_risk` function in [`crates/dbx-core/src/sql_risk.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/sql_risk.rs) to parse SQL with the `sqlparser` crate. It inspects each AST node and maps statements to the `SqlRisk` enum variants: `ReadOnly`, `Write`, `Ddl`, or `Transaction`. For multi-statement queries, it returns the highest risk level found among all statements.

### What happens if sqlparser fails to parse a SQL statement in DBX?

When the AST parser encounters unsupported syntax, DBX falls back to the `is_write_sql` function in [`crates/dbx-core/src/query_execution_sql.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/query_execution_sql.rs). This keyword-based scanner strips comments and literals, then checks for write operations by comparing against whitelisted read-only keywords and blacklisted dangerous commands, ensuring security even when parsing fails.

### How does DBX enforce read-only mode at the code level?

DBX calls `check_read_only` in [`crates/dbx-core/src/query_execution_sql.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/query_execution_sql.rs) immediately before dispatching to database drivers in [`transfer.rs`](https://github.com/t8y2/dbx/blob/main/transfer.rs). This function uses the risk classification (or keyword fallback) to verify the connection's write permissions, returning a descriptive error that blocks execution if a write operation is detected on a read-only connection.

### Can DBX handle multiple SQL statements in a single query execution?

Yes, DBX supports multi-statement queries by classifying each statement individually and returning the highest risk classification found. The `classify_sql_risk` function evaluates all statements in the batch, and if any statement qualifies as `Write`, `Ddl`, or `Transaction`, the entire batch is treated according to that risk level for permission checking.