/safe-sql-execution

name: safe-sql-execution
description: >-
  Use whenever code will build, return, fetch, or execute SQL that runs against
  a user's real Postgres database — even when the request reads like an ordinary
  feature or bug fix and never says "security," "injection," or
  "SafeSqlFragment." This covers: writing or editing any pg-meta function, query
  builder, or endpoint that builds/returns SQL for database objects (tables,
  views, functions, DB triggers, indexes, RLS policies); interpolating a
  schema/table/column/search/route-param value into SQL text; storing, fetching,
  or re-running SQL that round-trips from the database (a policy's definition, a
  function/view definition, a snippet's saved content); and any
  "Run"/"Apply"/"Execute" action that sends SQL to a project's database (SQL
  editor run-selection, policy editor apply, snippet runner). Load this BEFORE
  writing such code, not only when reviewing a finished diff. Skip only for
  changes that never touch SQL text or execution — styling, unrelated data
  hooks, non-SQL form validation, or UI layout work.

Safe SQL execution

Supabase Studio executes SQL statements directly against the user's database. Because this is the authenticated user's own database, our security model is different from most frontend applications: a user should be able to execute any SQL statement, as long as it is proven that they themselves authored it. What we SHOULD NOT ALLOW is execution of SQL statements that can be influenced by an attacker, such as through URL parameters.

Security model

The security model for SQL execution in Supabase Studio is based on the principle of "proven authorship". This means that a user should only be able to execute SQL statements that they have explicitly authored, and not statements that can be influenced by external input.

There are three classes of SQL fragments:

1. Hardcoded within the application code. These are safe to execute because they cannot be influenced by an attacker. They can be marked with the `safeSql` utility with `pg-meta`:

    import { safeSql } from '@supabase/pg-meta'

    const sql = safeSql`
      SELECT *
      FROM users
      WHERE id = 1
    `

`safeSql` automatically creates a string of the branded type `SafeSqlFragment`. (See Provenance Tracking below.)

2. Third-party influenceable. These are SQL fragments that can be influenced by an attacker, such as through URL parameters or LLM output. These should be marked with the `untrustedSql` utility with `pg-meta`:

    import { untrustedSql } from '@supabase/pg-meta'

    const unsafeQuery = searchParams.get('query')
    const querySql = untrustedSql(unsafeQuery)

`untrustedSql` creates a string of the branded type `UntrustedSqlFragment`. (See Provenance Tracking below.)

3. User-authored. These are SQL fragments that are authored by the user themselves within the UI, for example in a text input field. Because the user is the author, these should be considered safe to execute.

However, there is a caveat, where third-party and user-authored code can mix, contaminating the user-authored code (for example, if an input is prefilled from an unsanitized URL parameter). Provenance tracking helps us track these cases.

For example, a safe input component could be implemented as follows by requiring that its placeholder and controlled value are of type `SafeSqlFragment`. In this case we can use its onChange to promote the user input to `SafeSqlFragment` type, because we know that the user is the author of the input. An implementation of this is in @apps/studio/components/ui/SafeSqlInput.tsx:

    import { rawSql, type SafeSqlFragment } from '@supabase/pg-meta'
    import type { ChangeEvent, ComponentProps } from 'react'
    import { Input } from 'ui-patterns/DataInputs/Input'

    type InputProps = ComponentProps<typeof Input>

    export type SafeSqlInputProps = Omit<
      InputProps, 'placeholder' | 'value' | 'onChange'
    > & {
      placeholder?: SafeSqlFragment
      value: SafeSqlFragment
      onChange?:
        (event: ChangeEvent<HTMLInputElement>, value: SafeSqlFragment) => void
    }

    export const SafeSqlInput = ({ onChange, ...props }: SafeSqlInputProps) => (
      <Input
        {...props}
        onChange={(event) => onChange?.(event, rawSql(event.target.value))}
      />
    )

This is pretty much the ONLY VALID USE CASE of the rawSql export from pg-meta, and it should be used with caution.

Provenance tracking

Branded types are used to track the provenance of SQL fragments. The types, exported from `pg-meta`, are:

• `SafeSqlFragment`: represents SQL fragments that are safe to execute, because

they are either hardcoded in the application or authored by the user themselves.

• `UntrustedSqlFragment`: represents SQL fragments that can be influenced by an

attacker, such as through URL parameters or LLM output.

These are valid ways to generate a `SafeSqlFragment`:

• Using the `safeSql` utility from `pg-meta` to create hardcoded SQL fragments.
• Using the sanitization utilities from `pg-meta` to sanitize untrusted input

and promote it to a `SafeSqlFragment`:

• `ident`
• `literal`
• `keyword`
• Using the safe SQL manipulation utilities:
• `joinSqlFragments`
• `trimSafeSqlFragment`

`UntrustedSqlFragments` can be generated from raw strings using `untrustedSql()`.

There is also a union type, `DisplayableSqlFragment`, which represents SQL fragments that can be safely displayed in the UI, but not necessarily executed. This includes both `SafeSqlFragment` and `UntrustedSqlFragment`.

Security of SQL round-tripped from the user's database

SQL derived directly from catalog tables (e.g., function definitions, RLS expressions, etc.) is considered safe, and it is promoted AT THE POINT OF BEING QUERIED from the database. In most cases, this is in an apps/studio/data/\*_/_.t