Stop Trusting AI-Generated SQL: Build a Query Validation Gateway in Laravel

byHossein Hezami •
0

The dangerous moment is not when the AI says, “Here is the SQL.”

The dangerous moment is when your application believes it.

A user asks an internal assistant, “Show me overdue invoices from last quarter.” The model returns something plausible:

SELECT *
FROM invoices
WHERE status = 'overdue'
  AND due_at < '2026-03-31';

Maybe it is harmless.

Maybe it is this instead:

SELECT *
FROM invoices
WHERE status = 'overdue';

UPDATE users
SET is_admin = 1
WHERE email = 'attacker@example.com';

Or maybe the problem is subtler:

SELECT *
FROM invoices
WHERE organization_id = 123 OR 1 = 1;

Or:

SELECT id, total_cents, customer_email, internal_notes
FROM invoices
ORDER BY total_cents DESC;

No injection. No destructive statement. Just an unauthorized column, a missing tenant scope, and an unbounded query that locks up a replica.

AI-generated SQL needs a gate.

In a Laravel application, that gate should be a Query Validation Gateway: a service that refuses to treat model output as executable SQL until it has been normalized, validated, authorized, cost-checked, and audited.

This article walks through how to build one.

TL;DR

  • Do not execute raw AI-generated SQL directly.
  • Prefer a structured query intent: table, columns, filters, sort, limit.
  • Allowlist tables and columns per role. Do not expose the whole schema.
  • Use Laravel’s query builder with bindings for values.
  • Validate identifiers separately because bindings do not protect table or column names.
  • Force tenant and ownership scopes yourself.
  • Use a read-only database user and enforce row limits.
  • If raw SQL is unavoidable, treat it as hostile input and still restrict the database user.
  • Audit accepted and rejected query attempts.

📋 Table of Contents

1. Treat AI-generated SQL as untrusted input, not generated code

The first mistake is categorizing AI-generated SQL as “code we generated safely.”

It is not.

It is unstructured output from a probabilistic model, often influenced by user input, retrieved context, tool results, and prompt history. That output may be syntactically valid, semantically wrong, accidentally expensive, or actively malicious.

A Query Validation Gateway is not a wrapper around DB::select(). It is a pipeline.

AI output
  ↓
Normalize into query request
  ↓
Validate structure
  ↓
Validate schema identifiers
  ↓
Authorize user access
  ↓
Apply mandatory scopes
  ↓
Build query safely
  ↓
Apply limits and cost checks
  ↓
Execute on restricted connection
  ↓
Audit result

In Laravel, this can live as a plain service class:

namespace App\Services\QueryGateway;

use App\Models\User;
use Illuminate\Support\Collection;

final class QueryValidationGateway
{
    public function __construct(
        private readonly SchemaCatalog $catalog,
        private readonly SafeQueryBuilder $builder,
        private readonly QueryAuditor $auditor,
    ) {}

    public function run(User $user, QuerySpec $spec): Collection
    {
        try {
            $this->catalog->assertAuthorized($user, $spec);

            $query = $this->builder->build($user, $spec);

            $this->auditor->logAttempt($user, $spec, $query);

            return $query->get();
        } catch (QueryGatewayException $exception) {
            $this->auditor->logRejection($user, $spec, $exception);

            throw $exception;
        }
    }
}

The important part is that execution is the last step, not the first.

The gateway owns the contract. The AI does not.

🔍 Why this matters: SQL injection is only one failure mode. AI-generated queries can also leak tenant data, expose columns the user should not see, perform full-table scans, or hallucinate tables that do not exist.

2. Make the model speak query intent, not raw SQL

The safest AI-generated SQL is the SQL the AI never gets to write.

Instead of asking the model to return SQL, ask it to return a structured query object.

For example:

{
  "table": "invoices",
  "columns": ["id", "customer_id", "status", "total_cents", "due_at"],
  "filters": [
    {
      "column": "status",
      "operator": "=",
      "value": "overdue"
    },
    {
      "column": "due_at",
      "operator": "<",
      "value": "2026-03-31"
    }
  ],
  "order_by": "due_at",
  "direction": "asc",
  "limit": 100,
  "offset": 0
}

Now your Laravel application can validate that shape before it touches the database.

A simple DTO could look like this:

namespace App\Services\QueryGateway;

final class QuerySpec
{
    public function __construct(
        public readonly string $table,
        public readonly array $columns,
        public readonly array $filters,
        public readonly ?string $orderBy,
        public readonly string $direction,
        public readonly int $limit,
        public readonly int $offset,
    ) {}
}

Then validate the AI output before constructing the DTO:

use Illuminate\Support\Facades\Validator;
use Illuminate\Validation\Rule;
use Illuminate\Validation\ValidationException;

final class QuerySpecFactory
{
    public function fromAiOutput(array $aiOutput): QuerySpec
    {
        $data = Validator::make($aiOutput, [
            'table' => ['required', 'string', 'alpha_dash', 'max:64'],
            'columns' => ['required', 'array', 'min:1', 'max:20'],
            'columns.*' => ['required', 'string', 'alpha_dash', 'max:64'],
            'filters' => ['sometimes', 'array', 'max:20'],
            'filters.*.column' => ['required', 'string', 'alpha_dash', 'max:64'],
            'filters.*.operator' => [
                'required',
                Rule::in([
                    '=', '!=', '>', '<', '>=', '<=',
                    'like', 'in', 'between', 'null', 'not_null',
                ]),
            ],
            'filters.*.value' => ['nullable'],
            'order_by' => ['nullable', 'string', 'alpha_dash', 'max:64'],
            'direction' => ['nullable', Rule::in(['asc', 'desc'])],
            'limit' => ['required', 'integer', 'min:1', 'max:200'],
            'offset' => ['sometimes', 'integer', 'min:0', 'max:10000'],
        ])->validate();

        return new QuerySpec(
            table: $data['table'],
            columns: $data['columns'],
            filters: $data['filters'] ?? [],
            orderBy: $data['order_by'] ?? null,
            direction: $data['direction'] ?? 'asc',
            limit: $data['limit'],
            offset: $data['offset'] ?? 0,
        );
    }
}

This does not yet prove the table exists. It does not yet prove the user can access it. It does not yet prevent a bad filter value.

But it changes the problem.

You are no longer parsing arbitrary SQL. You are validating a structured request.

That is a much better place to start.

💡 Practical note: If the model cannot produce valid structured output, do not fall back to raw SQL. Ask the model to retry with a stricter schema, or return a clarification message to the user.

3. Allowlist tables and columns like route parameters

A common mistake is validating the shape of the query but not the identifiers.

The AI may return:

{
  "table": "users",
  "columns": ["email", "password", "api_token"]
}

That is structurally valid. It is also a data leak.

You need a schema catalog.

The catalog should know:

  • Which tables are allowed.
  • Which user roles can access them.
  • Which columns are visible.
  • Which columns are sortable.
  • Which columns are filterable.
  • Which tenant column must be enforced.

A config-driven catalog works well:

// config/query_gateway.php

return [
    'connection' => 'readonly',

    'tables' => [
        'invoices' => [
            'roles' => ['support', 'finance', 'admin'],
            'columns' => [
                'id',
                'organization_id',
                'customer_id',
                'status',
                'total_cents',
                'currency',
                'due_at',
                'issued_at',
            ],
            'filterable' => [
                'status',
                'customer_id',
                'total_cents',
                'due_at',
                'issued_at',
            ],
            'sortable' => [
                'id',
                'status',
                'total_cents',
                'due_at',
                'issued_at',
            ],
            'tenant_column' => 'organization_id',
            'max_limit' => 200,
            'default_sort' => ['due_at', 'asc'],
        ],

        'customers' => [
            'roles' => ['support', 'admin'],
            'columns' => [
                'id',
                'organization_id',
                'name',
                'email',
                'created_at',
            ],
            'filterable' => [
                'name',
                'email',
                'created_at',
            ],
            'sortable' => [
                'id',
                'name',
                'created_at',
            ],
            'tenant_column' => 'organization_id',
            'max_limit' => 100,
            'default_sort' => ['id', 'desc'],
        ],
    ],
];

Then build a catalog service around it:

namespace App\Services\QueryGateway;

use App\Models\User;
use RuntimeException;

final class SchemaCatalog
{
    public function assertAuthorized(User $user, QuerySpec $spec): void
    {
        $this->assertTable($user, $spec->table);

        foreach ($spec->columns as $column) {
            $this->assertColumn($user, $spec->table, $column);
        }

        foreach ($spec->filters as $filter) {
            $this->assertFilterableColumn($user, $spec->table, $filter['column']);
        }

        if ($spec->orderBy !== null) {
            $this->assertSortableColumn($user, $spec->table, $spec->orderBy);
        }
    }

    public function assertTable(User $user, string $table): void
    {
        $config = config("query_gateway.tables.$table");

        if (! is_array($config)) {
            throw new QueryAuthorizationException("Table [$table] is not available.");
        }

        $allowedRoles = $config['roles'] ?? [];

        if (! $user->hasAnyRole($allowedRoles)) {
            throw new QueryAuthorizationException("You do not have access to [$table].");
        }
    }

    public function assertColumn(User $user, string $table, string $column): string
    {
        $this->assertTable($user, $table);

        $columns = config("query_gateway.tables.$table.columns", []);

        if (! in_array($column, $columns, true)) {
            throw new QueryAuthorizationException("Column [$column] is not allowed on [$table].");
        }

        return $column;
    }

    public function assertFilterableColumn(User $user, string $table, string $column): string
    {
        $this->assertColumn($user, $table, $column);

        $filterable = config("query_gateway.tables.$table.filterable", []);

        if (! in_array($column, $filterable, true)) {
            throw new QueryAuthorizationException("Column [$column] cannot be used as a filter.");
        }

        return $column;
    }

    public function assertSortableColumn(User $user, string $table, string $column): string
    {
        $this->assertColumn($user, $table, $column);

        $sortable = config("query_gateway.tables.$table.sortable", []);

        if (! in_array($column, $sortable, true)) {
            throw new QueryAuthorizationException("Column [$column] cannot be used for sorting.");
        }

        return $column;
    }

    public function tableConfig(string $table): array
    {
        $config = config("query_gateway.tables.$table");

        if (! is_array($config)) {
            throw new QueryAuthorizationException("Table [$table] is not available.");
        }

        return $config;
    }
}

This may feel restrictive. Good.

A query gateway should be restrictive.

You can use Schema::getColumnListing() to discover columns dynamically, but I would not make that the primary authorization source. Dynamic schema introspection tells you what exists. It does not tell you what the user is allowed to see.

Use the schema to verify reality. Use the catalog to enforce policy.

⚠️ Gotcha: If you allow the AI to choose from “all tables the user can access,” you have moved the authorization problem into the model’s prompt. That is not authorization. That is hope.

4. Bind values, whitelist identifiers

Laravel’s query builder protects you from many SQL injection problems because bindings separate values from SQL.

This is safe:

DB::table('invoices')
    ->where('status', '=', $status)
    ->get();

The value is bound. The database driver handles escaping.

But this is not safe:

DB::table('invoices')
    ->where($column, '=', $value)
    ->get();

If $column comes from the AI, it may contain:

1 = 1; DROP TABLE invoices --

Bindings do not protect identifiers.

They do not protect:

  • Table names.
  • Column names.
  • ORDER BY columns.
  • GROUP BY columns.
  • SQL keywords.
  • Sort direction.
  • Database or schema names.

Your gateway must validate all identifiers separately.

A safe filter applier might look like this:

namespace App\Services\QueryGateway;

use Illuminate\Database\Query\Builder;
use Illuminate\Validation\ValidationException;

final class SafeQueryBuilder
{
    public function __construct(
        private readonly SchemaCatalog $catalog,
    ) {}

    public function build(User $user, QuerySpec $spec): Builder
    {
        $this->catalog->assertAuthorized($user, $spec);

        $tableConfig = $this->catalog->tableConfig($spec->table);

        $query = DB::connection(config('query_gateway.connection'))
            ->table($spec->table);

        $columns = array_map(
            fn (string $column) => $this->catalog->assertColumn($user, $spec->table, $column),
            $spec->columns
        );

        $query->select($columns);

        $this->applyTenantScope($user, $query, $tableConfig);

        foreach ($spec->filters as $filter) {
            $this->applyFilter($user, $query, $spec->table, $filter);
        }

        $this->applyOrderBy($user, $query, $spec, $tableConfig);

        $maxLimit = $tableConfig['max_limit'] ?? 100;

        $query->limit(min($spec->limit, $maxLimit));
        $query->offset($spec->offset);

        return $query;
    }

    private function applyFilter(
        User $user,
        Builder $query,
        string $table,
        array $filter
    ): void {
        $column = $this->catalog->assertFilterableColumn($user, $table, $filter['column']);

        $operator = $filter['operator'];
        $value = $filter['value'] ?? null;

        match ($operator) {
            '=' => $query->where($column, '=', $value),
            '!=' => $query->where($column, '!=', $value),
            '>' => $query->where($column, '>', $value),
            '<' => $query->where($column, '<', $value),
            '>=' => $query->where($column, '>=', $value),
            '<=' => $query->where($column, '<=', $value),
            'like' => $query->where($column, 'like', $this->normalizeLikeValue($value)),
            'in' => $query->whereIn($column, $this->normalizeInValue($value)),
            'between' => $query->whereBetween($column, $this->normalizeBetweenValue($value)),
            'null' => $query->whereNull($column),
            'not_null' => $query->whereNotNull($column),
            default => throw ValidationException::withMessages([
                'filters' => "Unsupported operator [$operator].",
            ]),
        };
    }

    private function applyOrderBy(
        User $user,
        Builder $query,
        QuerySpec $spec,
        array $tableConfig
    ): void {
        [$defaultColumn, $defaultDirection] = $tableConfig['default_sort'] ?? ['id', 'desc'];

        $orderBy = $spec->orderBy ?? $defaultColumn;
        $direction = strtolower($spec->direction ?: $defaultDirection);

        $column = $this->catalog->assertSortableColumn($user, $spec->table, $orderBy);

        $direction = in_array($direction, ['asc', 'desc'], true)
            ? $direction
            : 'asc';

        $query->orderBy($column, $direction);
    }

    private function applyTenantScope(
        User $user,
        Builder $query,
        array $tableConfig
    ): void {
        $tenantColumn = $tableConfig['tenant_column'] ?? null;

        if ($tenantColumn !== null) {
            $query->where($tenantColumn, '=', $user->organization_id);
        }
    }

    private function normalizeLikeValue(mixed $value): string
    {
        if (! is_string($value)) {
            throw ValidationException::withMessages([
                'filters' => 'LIKE filters require a string value.',
            ]);
        }

        $escaped = str_replace(
            ['\\', '%', '_'],
            ['\\\\', '\%', '\_'],
            $value
        );

        return '%' . $escaped . '%';
    }

    private function normalizeInValue(mixed $value): array
    {
        if (! is_array($value)) {
            throw ValidationException::withMessages([
                'filters' => 'IN filters require an array value.',
            ]);
        }

        if (count($value) === 0) {
            throw ValidationException::withMessages([
                'filters' => 'IN filters require at least one value.',
            ]);
        }

        if (count($value) > 100) {
            throw ValidationException::withMessages([
                'filters' => 'IN filters support a maximum of 100 values.',
            ]);
        }

        foreach ($value as $item) {
            if (! is_scalar($item)) {
                throw ValidationException::withMessages([
                    'filters' => 'IN filter values must be scalar.',
                ]);
            }
        }

        return array_values($value);
    }

    private function normalizeBetweenValue(mixed $value): array
    {
        if (! is_array($value) || count($value) !== 2) {
            throw ValidationException::withMessages([
                'filters' => 'BETWEEN filters require exactly two values.',
            ]);
        }

        foreach ($value as $item) {
            if (! is_scalar($item)) {
                throw ValidationException::withMessages([
                    'filters' => 'BETWEEN filter values must be scalar.',
                ]);
            }
        }

        return [$value[0], $value[1]];
    }
}

This is deliberately boring.

It does not allow raw expressions. It does not allow orderByRaw(). It does not allow whereRaw(). It does not allow joins unless you explicitly model and validate them.

That is the point.

The more expressive your AI query gateway is, the larger your security surface becomes.

5. Inject tenant and ownership scopes after validation

A query can be syntactically perfect and still be unauthorized.

Suppose the AI returns:

{
  "table": "invoices",
  "columns": ["id", "total_cents", "customer_id"],
  "filters": [
    {
      "column": "status",
      "operator": "=",
      "value": "overdue"
    }
  ]
}

If your application is multi-tenant, that query is missing the most important condition:

WHERE organization_id = ?

Do not rely on the model to include it.

Do not rely on the prompt to remember it.

Do not rely on the user to ask only for their own data.

The gateway must apply mandatory scopes itself.

In the previous example, the scope is applied here:

private function applyTenantScope(
    User $user,
    Builder $query,
    array $tableConfig
): void {
    $tenantColumn = $tableConfig['tenant_column'] ?? null;

    if ($tenantColumn !== null) {
        $query->where($tenantColumn, '=', $user->organization_id);
    }
}

This should happen even if the AI did not mention tenants.

It should happen even if the AI tried to filter by another tenant.

For stronger protection, reject attempts to filter on the tenant column directly:

public function assertFilterableColumn(User $user, string $table, string $column): string
{
    $this->assertColumn($user, $table, $column);

    $tenantColumn = config("query_gateway.tables.$table.tenant_column");

    if ($tenantColumn === $column) {
        throw new QueryAuthorizationException(
            "Tenant column [$column] cannot be used as a user-supplied filter."
        );
    }

    $filterable = config("query_gateway.tables.$table.filterable", []);

    if (! in_array($column, $filterable, true)) {
        throw new QueryAuthorizationException("Column [$column] cannot be used as a filter.");
    }

    return $column;
}

That prevents this kind of AI output:

{
  "filters": [
    {
      "column": "organization_id",
      "operator": "=",
      "value": 999
    }
  ]
}

The gateway decides the tenant boundary. The AI can only query inside it.

🧠 The important part: Authorization cannot be delegated to the model. The model can help translate intent, but the application must enforce boundaries.

6. Enforce limits, read-only connections, and cost controls

AI-generated queries are often technically valid but operationally expensive.

Examples:

SELECT *
FROM invoices;

SELECT *
FROM audit_events
ORDER BY created_at DESC
LIMIT 1000000;

SELECT id, payload
FROM webhook_deliveries
WHERE payload LIKE '%error%';

These can saturate a database replica, increase replication lag, or cause timeouts.

Your gateway needs three controls:

  1. Row limits.
  2. Offset limits.
  3. A restricted database connection.

The row limit should be enforced even if the AI requests more:

$maxLimit = $tableConfig['max_limit'] ?? 100;

$query->limit(min($spec->limit, $maxLimit));

The offset limit prevents deep pagination abuse:

'offset' => ['sometimes', 'integer', 'min:0', 'max:10000'],

Deep pagination is often a sign that the AI is trying to crawl the table. If the user needs exports or reporting, use a dedicated export job, not an interactive query gateway.

The database connection should be read-only.

In config/database.php, define a separate connection:

'readonly' => [
    'driver' => env('READONLY_DB_DRIVER', 'mysql'),
    'host' => env('READONLY_DB_HOST', '127.0.0.1'),
    'port' => env('READONLY_DB_PORT', '3306'),
    'database' => env('READONLY_DB_DATABASE', 'app'),
    'username' => env('READONLY_DB_USERNAME', 'app_readonly'),
    'password' => env('READONLY_DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
],

The database user should have only SELECT on the allowed tables.

That gives you defense in depth.

If the gateway has a bug, the database user still cannot execute:

UPDATE users SET is_admin = 1;

or:

DROP TABLE invoices;

A read-only user does not solve data leakage, but it prevents mutation.

For Postgres, you can also configure statement timeouts at the role or session level. For MySQL, consider using a replica with query limits or proxy-level controls. The exact mechanism depends on your database, but the principle is the same: the AI query path should have fewer privileges than the main application.

🚨 Production warning: Do not run AI-generated reporting queries against your primary write database unless you have a very good reason. A bad ORDER BY on a large table can hurt real users.

7. If you must accept raw SQL, guard it like a hostile file upload

Sometimes you cannot avoid raw SQL.

Maybe you are building an internal admin tool. Maybe you have a reporting team that wants full flexibility. Maybe the AI is generating SQL for a legacy database with weird views and stored procedures.

If you must accept raw SQL, do not treat it like normal input. Treat it like a hostile file upload.

At minimum:

  • Allow only SELECT.
  • Reject multiple statements.
  • Reject comments.
  • Reject dangerous keywords.
  • Run as a read-only database user.
  • Require manual approval for anything broad.
  • Log the full query.
  • Apply a timeout.

A basic raw SQL policy might look like this:

namespace App\Services\QueryGateway\Raw;

use App\Services\QueryGateway\QueryAuthorizationException;

final class RawSqlPolicy
{
    public function ensureSafe(string $sql): void
    {
        $sql = trim($sql);

        if ($sql === '') {
            throw new QueryAuthorizationException('SQL query is empty.');
        }

        if (str_contains($sql, ';')) {
            throw new QueryAuthorizationException('Multiple SQL statements are not allowed.');
        }

        if (preg_match('/\/\*|\*\/|--|#/', $sql)) {
            throw new QueryAuthorizationException('SQL comments are not allowed.');
        }

        if (! preg_match('/^select\s/i', $sql)) {
            throw new QueryAuthorizationException('Only SELECT queries are allowed.');
        }

        $forbidden = '/\b(
            insert|update|delete|drop|alter|truncate|create|replace|
            grant|revoke|call|execute|exec|set|load|outfile|dumpfile|
            into|sleep|benchmark
        )\b/ix';

        if (preg_match($forbidden, $sql)) {
            throw new QueryAuthorizationException('Query contains forbidden SQL keyword.');
        }
    }
}

This is not perfect.

Regular expressions cannot fully parse SQL. They can be bypassed by exotic syntax, database-specific functions, encoded identifiers, or clever use of comments if your validation is weak.

But this policy is still useful as one layer in a larger defense.

A stronger raw SQL path would add:

final class RawSqlGateway
{
    public function __construct(
        private readonly RawSqlPolicy $policy,
        private readonly QueryAuditor $auditor,
    ) {}

    public function run(User $user, string $sql): array
    {
        $this->policy->ensureSafe($sql);

        $this->auditor->logRawAttempt($user, $sql);

        return DB::connection('readonly')
            ->select($sql);
    }
}

Even then, I would not expose this directly to end users.

For customer-facing AI features, I would not accept raw SQL at all.

For internal tools, raw SQL can be acceptable if it is behind strong authentication, role checks, audit logging, and a read-only database user.

ApproachFlexibilitySafetyComplexityBest for
Execute raw AI SQL directlyHighLowLowDo not use
Regex-guarded raw SQLMediumLow/MediumLowInternal prototypes only
Structured query specMediumHighMediumMost AI reporting features
Predefined query templatesLowVery highLowCustomer-facing analytics
Query builder with scopesMediumHighMediumApplication-level reporting

8. Dry-run and explain expensive queries before returning data

Sometimes a query is authorized and structurally safe but still expensive.

For example:

SELECT id, total_cents, due_at
FROM invoices
WHERE status LIKE '%overdue%'
ORDER BY due_at;

If status is not indexed and the LIKE pattern starts with a wildcard, the database may scan a large portion of the table.

A useful optional layer is query explanation.

You can use Laravel’s query builder to produce the SQL and bindings, then run EXPLAIN on a read-only connection.

namespace App\Services\QueryGateway;

use Illuminate\Database\Query\Builder;
use Illuminate\Support\Facades\DB;

final class QueryCostAnalyzer
{
    public function analyze(Builder $query): array
    {
        $connection = DB::connection(config('query_gateway.connection'));

        $driver = $connection->getDriverName();

        $sql = $query->toSql();
        $bindings = $query->getBindings();

        $explainSql = match ($driver) {
            'mysql', 'mariadb' => 'EXPLAIN ' . $sql,
            'pgsql' => 'EXPLAIN ' . $sql,
            'sqlite' => 'EXPLAIN QUERY PLAN ' . $sql,
            default => throw new QueryGatewayException(
                "Unsupported driver [$driver] for query explanation."
            ),
        };

        return $connection->select($explainSql, $bindings);
    }
}

For MySQL, you can inspect estimated rows:

final class QueryCostGuard
{
    public function __construct(
        private readonly QueryCostAnalyzer $analyzer,
    ) {}

    public function ensureAffordable(Builder $query): void
    {
        $plan = $this->analyzer->analyze($query);

        $estimatedRows = collect($plan)
            ->map(fn ($row) => (int) ($row->rows ?? 0))
            ->sum();

        if ($estimatedRows > 250_000) {
            throw new QueryCostException(
                'Query appears too expensive to execute interactively.'
            );
        }
    }
}

This is database-specific and approximate. Postgres plans look different. SQLite plans look different. Managed databases may have their own tools.

Still, even a rough guard is useful.

You can also reject queries that request too many columns, use leading wildcard LIKE filters, or sort by non-indexed columns. Those checks can be done before execution.

For example:

private function containsLeadingWildcardLike(array $filters): bool
{
    foreach ($filters as $filter) {
        if (($filter['operator'] ?? null) !== 'like') {
            continue;
        }

        $value = $filter['value'] ?? '';

        if (is_string($value) && str_starts_with($value, '%')) {
            return true;
        }
    }

    return false;
}

In a strict gateway, I would avoid exposing raw LIKE to end users unless the searched column is indexed and the query volume is low. A full-text search index or search service is often better.

💡 Practical note: EXPLAIN helps, but it is not free. Run it on a replica, cache results for repeated query shapes, and do not let the AI trigger unlimited explain loops.

9. Audit every AI query attempt, especially the rejected ones

If you are allowing AI to influence database queries, you need an audit trail.

You need to know:

  • Who asked.
  • What the AI generated.
  • Whether the query was accepted or rejected.
  • Which validation rule failed.
  • What SQL was produced.
  • What bindings were used.
  • How many rows were returned.
  • How long it took.
  • Whether it was executed on a read-only replica.

A simple log table is enough to start:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('ai_query_logs', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->nullable()->constrained();
            $table->string('status', 32);
            $table->string('requested_table', 128)->nullable();
            $table->json('spec')->nullable();
            $table->text('sql')->nullable();
            $table->json('bindings_hash')->nullable();
            $table->json('validation_errors')->nullable();
            $table->unsignedInteger('row_count')->nullable();
            $table->unsignedInteger('duration_ms')->nullable();
            $table->string('connection', 64)->nullable();
            $table->timestamp('created_at')->useCurrent();

            $table->index(['user_id', 'created_at']);
            $table->index(['status', 'created_at']);
            $table->index('requested_table');
        });
    }
};

The auditor can store the query spec and a sanitized version of the generated SQL:

namespace App\Services\QueryGateway;

use App\Models\AiQueryLog;
use App\Models\User;
use Illuminate\Database\Query\Builder;
use Throwable;

final class QueryAuditor
{
    public function logAttempt(User $user, QuerySpec $spec, Builder $query): void
    {
        AiQueryLog::create([
            'user_id' => $user->id,
            'status' => 'executed',
            'requested_table' => $spec->table,
            'spec' => $spec,
            'sql' => $query->toSql(),
            'bindings_hash' => $this->hashBindings($query->getBindings()),
            'connection' => config('query_gateway.connection'),
        ]);
    }

    public function logRejection(User $user, QuerySpec $spec, Throwable $exception): void
    {
        AiQueryLog::create([
            'user_id' => $user->id,
            'status' => 'rejected',
            'requested_table' => $spec->table,
            'spec' => $spec,
            'validation_errors' => [
                'message' => $exception->getMessage(),
                'class' => $exception::class,
            ],
        ]);
    }

    private function hashBindings(array $bindings): array
    {
        return array_map(
            fn ($binding) => hash('sha256', serialize($binding)),
            $bindings
        );
    }
}

Be careful with bindings. They may contain personal data, financial data, or other sensitive values. Hashing them is often better than storing them raw. If you need full debugging detail, store raw bindings only temporarily, encrypt them, or restrict access to the audit table.

You can also use Laravel’s query events to capture execution duration:

use Illuminate\Support\Facades\DB;

DB::listen(function ($query) {
    if ($query->connectionName !== config('query_gateway.connection')) {
        return;
    }

    // Log or emit metrics for query duration, SQL shape, and connection.
});

The audit log is not just for compliance.

It is also how you discover that the model keeps trying to query users.password, or that users keep asking for a column you have not exposed, or that a certain filter shape causes expensive scans.

10. Test the gateway against the queries that will hurt you

A query gateway is security infrastructure. It needs tests.

Do not only test the happy path.

Test the cases that will eventually appear in production.

Here are the ones I would include.

Disallowed table

it('rejects tables that are not allowlisted', function () {
    $user = User::factory()->withRole('support')->create();

    $spec = new QuerySpec(
        table: 'users',
        columns: ['email'],
        filters: [],
        orderBy: null,
        direction: 'asc',
        limit: 10,
        offset: 0,
    );

    expect(fn () => app(QueryValidationGateway::class)->run($user, $spec))
        ->toThrow(QueryAuthorizationException::class);
});

Disallowed column

it('rejects columns that are not exposed', function () {
    $user = User::factory()->withRole('support')->create();

    $spec = new QuerySpec(
        table: 'invoices',
        columns: ['id', 'internal_notes'],
        filters: [],
        orderBy: null,
        direction: 'asc',
        limit: 10,
        offset: 0,
    );

    expect(fn () => app(QueryValidationGateway::class)->run($user, $spec))
        ->toThrow(QueryAuthorizationException::class);
});

Tenant bypass attempt

it('rejects user-supplied filters on tenant columns', function () {
    $user = User::factory()->withRole('support')->create();

    $spec = new QuerySpec(
        table: 'invoices',
        columns: ['id', 'total_cents'],
        filters: [
            [
                'column' => 'organization_id',
                'operator' => '=',
                'value' => 999,
            ],
        ],
        orderBy: null,
        direction: 'asc',
        limit: 10,
        offset: 0,
    );

    expect(fn () => app(QueryValidationGateway::class)->run($user, $spec))
        ->toThrow(QueryAuthorizationException::class);
});

Identifier injection through ORDER BY

it('rejects malicious order-by identifiers', function () {
    $user = User::factory()->withRole('support')->create();

    $spec = new QuerySpec(
        table: 'invoices',
        columns: ['id'],
        filters: [],
        orderBy: 'id; DROP TABLE invoices',
        direction: 'asc',
        limit: 10,
        offset: 0,
    );

    expect(fn () => app(QuerySpecFactory::class)->fromAiOutput([
        'table' => 'invoices',
        'columns' => ['id'],
        'order_by' => 'id; DROP TABLE invoices',
        'direction' => 'asc',
        'limit' => 10,
        'offset' => 0,
    ]))->toThrow(ValidationException::class);
});

Excessive limit

it('caps limits even when the AI asks for more', function () {
    $user = User::factory()->withRole('finance')->create();

    $spec = new QuerySpec(
        table: 'invoices',
        columns: ['id'],
        filters: [],
        orderBy: null,
        direction: 'asc',
        limit: 100000,
        offset: 0,
    );

    $query = app(SafeQueryBuilder::class)->build($user, $spec);

    expect($query->limit)->toBe(200);
});

Stacked raw SQL statements

it('rejects stacked raw SQL statements', function () {
    $sql = "SELECT id FROM invoices; DROP TABLE invoices;";

    expect(fn () => app(RawSqlPolicy::class)->ensureSafe($sql))
        ->toThrow(QueryAuthorizationException::class);
});

Raw SQL comment abuse

it('rejects raw SQL comments', function () {
    $sql = "SELECT id FROM invoices -- comment";

    expect(fn () => app(RawSqlPolicy::class)->ensureSafe($sql))
        ->toThrow(QueryAuthorizationException::class);
});

Wildcard abuse

it('escapes LIKE wildcards from user-controlled values', function () {
    $gateway = app(SafeQueryBuilder::class);

    $user = User::factory()->withRole('finance')->create();

    $spec = new QuerySpec(
        table: 'invoices',
        columns: ['id', 'status'],
        filters: [
            [
                'column' => 'status',
                'operator' => 'like',
                'value' => '100%_done',
            ],
        ],
        orderBy: null,
        direction: 'asc',
        limit: 10,
        offset: 0,
    );

    $query = $gateway->build($user, $spec);

    expect($query->toSql())->toContain('like');
});

These tests are not glamorous, but they define the boundary.

If your AI feature can query the database, the gateway’s test suite is part of the feature.

Choosing the right level of strictness

Not every AI SQL feature needs the same level of paranoia.

The right design depends on who is using it and what it can touch.

Customer-facing analytics

If end users can ask questions over their own data, use the strictest design.

I would use:

  • Structured query intent only.
  • No raw SQL.
  • Allowlisted tables and columns.
  • Forced tenant scopes.
  • Read-only replica.
  • Small row limits.
  • Full audit logging.

If the structured spec cannot express the query, the answer is “I cannot answer that yet,” not “let me try raw SQL.”

Internal support tools

Support teams often need more flexibility, but still not unlimited SQL.

I would use:

  • Structured query spec.
  • Role-based table allowlists.
  • Column-level visibility.
  • Tenant scopes unless the user is explicitly global.
  • Audit logging.
  • Optional export jobs for large results.

Raw SQL may be acceptable for senior support engineers, but it should be separate from the normal AI path and protected by stronger access controls.

Data team or admin reporting

If trained users are querying a warehouse or replica, you can allow more expressiveness.

Even then, I would not execute AI-generated SQL blindly.

I would use:

  • Read-only connection.
  • Statement timeout.
  • Query cost estimation.
  • Manual review for broad queries.
  • Audit logs.
  • Separate credentials.
  • Possibly raw SQL with policy checks.

The difference is that the users are trusted more, but the system still assumes the model can be wrong.

What I would build

If I were adding AI-driven querying to a Laravel application today, I would not start by asking the model to write SQL.

I would start with a query contract.

The AI would produce:

{
  "table": "invoices",
  "columns": ["id", "status", "total_cents", "due_at"],
  "filters": [
    {
      "column": "status",
      "operator": "=",
      "value": "overdue"
    }
  ],
  "order_by": "due_at",
  "direction": "asc",
  "limit": 100,
  "offset": 0
}

Then Laravel would do the boring, important work:

  • Validate the request shape.
  • Check the table against the user’s role.
  • Check every column against the catalog.
  • Reject tenant-column filters.
  • Apply the tenant scope.
  • Build the query with bindings.
  • Cap the limit.
  • Use a read-only connection.
  • Log the attempt.
  • Return the result.

The model gets to translate intent.

The application keeps control over data access.

That is the core lesson: AI-generated SQL is not a query until your system has turned it into one.

A Query Validation Gateway is how you make that transformation explicit, testable, and safe enough for production.

Tags:

Post a Comment

Previous Post Next Post