Design a read-only SQL tool for an analyst assistant

A read-only SQL tool needs enforced data and operation boundaries. Checking whether a generated string starts with SELECT is insufficient: queries can access unintended data, call expensive functions or produce excessive results without updating a business table.

The local tool below exposes four columns from a synthetic orders table and rejects operations outside a narrow policy. It demonstrates layered controls in SQLite; it is not a production database gateway or an operating-system sandbox.

Minimize the database surface

The tool implementation creates a fresh in-memory database containing order ID, order timestamp, status and amount. Customer identifiers and email addresses are never loaded into that database.

The tool then enables query-only mode and installs a default-deny authorizer. It permits reads of the exposed table and columns, selection, and a short function list including SUM and COUNT. It rejects attachment, arbitrary pragmas, writes, schema reads and unapproved functions.

SQLite documents the authorizer callback and query-only pragma. These controls serve different purposes and are combined here with additional limits.

Execute a permitted query and rejected actions

from readonly_sql import query,QueryRejected

result = query(
    'SELECT COUNT(*) AS orders,SUM(order_total_paise) AS amount '
    'FROM orders WHERE status=? AND ordered_at>=? AND ordered_at<?',
    ('completed','2026-01-01T00:00:00','2026-02-01T00:00:00'))
assert result['rows']==[[8,104000]]
for sql in (
    'UPDATE orders SET order_total_paise=0',
    'PRAGMA query_only=OFF',
    'SELECT customer_id FROM orders',
    'SELECT randomblob(100)',
):
    try:
        query(sql)
    except QueryRejected:
        pass
    else:
        raise AssertionError('Expected rejection: '+sql)
assert query('SELECT COUNT(*) FROM orders WHERE status=?',
             ("completed' OR 1=1 --",))['rows']==[[0]]
print({'allowed_result':result['rows'],'rejection_cases':4,
       'parameter_text_remained_data':True})

Bound parameters protect values in a known query structure. They do not authorize the SQL structure itself. The authorizer and exposed schema address that separate concern.

Bound work and output

The implementation limits statement length, expression depth, columns, bound parameters and compound selections. A progress handler interrupts excessive SQLite virtual-machine work. Result handling rejects more than the configured row count, oversized cells or an oversized serialized-value estimate.

These settings belong to trusted application configuration. An assistant must not be able to increase its own limits by supplying extra tool arguments.

A VM budget is not a universal wall-clock or memory guarantee. The trusted CSV-loading step happens before that budget, and this small fixture does not exercise production-scale concurrency or every resource-exhaustion pattern. A real service also needs deployment-level isolation, timeouts and capacity controls suited to its database.

Keep permission and correctness separate

SELECT SUM(order_total_paise) FROM orders is permitted but answers a different question from the completed-order metric. It includes cancelled and pending orders. A successful read-only execution therefore does not establish that the result meets the business contract.

Compare returned values and row eligibility with an independent reference. Preserve period boundaries and units in the answer. The source calculator provides that separate reference for this fixture.

One implementation detail is tested explicitly: local SQLite 3.42 reports an empty column and no database name for a COUNT(*) table read. The policy permits that narrow case on the sole trusted table while denying arbitrary schema access. Inspect actual engine behavior instead of weakening all read checks after a surprising failure.

Reproduce the boundary tests

Run python verify_extensions.py in the lab directory. The suite includes allowed aggregates, rejected writes and attachment, unexposed columns, multiple statements, recursive operations, row limits, a work-budget interruption and parameter text containing SQL-looking content.

Passing these tests establishes behavior for the tested cases. It is not a security certification, a tenant-isolation design or evidence that generated SQL is semantically correct.

Exercise: submit a permitted query that returns the wrong business total, then explain why tool authorization should allow the query while metric verification rejects the resulting answer.

NeuraPath's Data Analytics with Generative AI course connects SQL execution with responsible AI tool use. The analyst should understand both what a tool may do and whether its result answers the intended question.

Continue learning

This article is part of the Generative AI for verified analyst work sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Separate retrieval facts from calculated numbers.
• Continue with Compare AI assistants on the same analyst task fairly.