Skip to content

The Postgres development platform. Supabase gives you a dedicated Postgres database to build your web, mobile, and AI applications.

11.9 KB
Raw Blame
import { POSTGRESQL_RESERVED_WORDS } from './reserved'

interface PgFormatConfigPattern {
  ident: string
  literal: string
  string: string
}

export interface PgFormatConfig {
  pattern: PgFormatConfigPattern
}

/**
 * A branded string type representing a SQL fragment that is safe to compose
 * into queries.
 * Values of this type are either:
 * - Static strings in source code (no interpolation)
 * - Outputs of `ident()`, `literal()`, or `keyword()` (properly escaped or
 *   validated)
 * - Compositions via `safeSql` template tag (enforces SafeSqlFragment-only
 *   interpolations)
 * - Compositions via `joinFragments` (enforces SafeSqlFragment inputs)
 *
 * Never cast arbitrary strings to this type — use ident/literal/keyword/safeSql
 * instead.
 */
export type SafeSqlFragment = string & { readonly __safeSqlFragmentBrand: never }

/**
 * A branded string type representing SQL that may have been influenced by a
 * third party (URL params, AI output, external content). Safe to display;
 * must never be auto-executed or persisted as user-authored content.
 * Promote to SafeSqlFragment via acceptUntrustedSql() — only inside an
 * explicit user-action event handler.
 */
export type UntrustedSqlFragment = string & { readonly __untrustedSqlFragmentBrand: never }

/** Either brand — for read-only display surfaces that accept both. */
export type DisplayableSqlFragment = SafeSqlFragment | UntrustedSqlFragment

export type SqlFragmentSeparator =
  | ','
  | ', '
  | ';\n'
  | ' and '
  | ' AND '
  | ' or '
  | ' OR '
  | ' union all '
  | ' union '
  | ' UNION ALL '
  | ' UNION '
  | '\n'
  | '\n\n'
  | ' '

const FMT_PATTERN_CONFIG: PgFormatConfigPattern = {
  ident: 'I',
  literal: 'L',
  string: 's',
}

// convert to Postgres default ISO 8601 format
function formatDate(date: SafeSqlFragment): SafeSqlFragment {
  return date.replace('T', ' ').replace('Z', '+00') as SafeSqlFragment
}

function isReserved(value: string): boolean {
  if (POSTGRESQL_RESERVED_WORDS.has(value.toUpperCase())) {
    return true
  }
  return false
}

function arrayToList<ElementType = unknown>(
  useSpace: boolean,
  array: ElementType[],
  formatter: (value: ElementType) => SafeSqlFragment
): SafeSqlFragment {
  let sql = safeSql``

  sql = useSpace ? safeSql`${sql} (` : safeSql`${sql} (`
  for (const [index, element] of array.entries()) {
    sql = safeSql`${sql}${index === 0 ? safeSql`` : safeSql`, `}${formatter(element)}`
  }
  sql = safeSql`${sql})`

  return sql
}

// Ported from PostgreSQL 9.2.4 source code in src/interfaces/libpq/fe-exec.c
// eslint-disable-next-line radar/cognitive-complexity
export function ident(value?: unknown): SafeSqlFragment {
  if (value === undefined || value === null) {
    throw new Error('SQL identifier cannot be null or undefined')
  } else if (value === false) {
    return '"f"' as SafeSqlFragment
  } else if (value === true) {
    return '"t"' as SafeSqlFragment
  } else if (value instanceof Date) {
    return safeSql`"${formatDate(value.toISOString() as SafeSqlFragment)}"`
  } else if (Array.isArray(value)) {
    const temporary: string[] = []
    for (const element of value) {
      if (Array.isArray(element) === true) {
        throw new TypeError(
          'Nested array to grouped list conversion is not supported for SQL identifier'
        )
      } else {
        temporary.push(ident(element))
      }
    }
    return temporary.toString() as SafeSqlFragment
  } else if (value === Object(value)) {
    throw new Error('SQL identifier cannot be an object')
  }

  const tident = String(value).slice(0) // create copy

  // do not quote a valid, unquoted identifier
  if (/^[_a-z][\d$_a-z]*$/.test(tident) === true && isReserved(tident) === false) {
    return tident as SafeSqlFragment
  }

  let quoted = '"'

  for (const c of tident) {
    quoted += c === '"' ? c + c : c
  }

  quoted += '"'

  return quoted as SafeSqlFragment
}

// Ported from PostgreSQL 9.2.4 source code in src/interfaces/libpq/fe-exec.c
// eslint-disable-next-line radar/cognitive-complexity
export function literal(value?: unknown): SafeSqlFragment {
  let tliteral = ''
  let explicitCast: string | undefined

  if (value === undefined || value === null) {
    return 'NULL' as SafeSqlFragment
  }
  if (typeof value === 'bigint') {
    return BigInt(value).toString() as SafeSqlFragment
  }
  if (value === Number.POSITIVE_INFINITY) {
    return "'Infinity'" as SafeSqlFragment
  }
  if (value === Number.NEGATIVE_INFINITY) {
    return "'-Infinity'" as SafeSqlFragment
  }
  if (Number.isNaN(value)) {
    return "'NaN'" as SafeSqlFragment
  }
  if (typeof value === 'number') {
    // Test must be AFTER other special case number tests
    return Number(value).toString() as SafeSqlFragment
  }
  if (value === false) {
    return "'f'" as SafeSqlFragment
  }
  if (value === true) {
    return "'t'" as SafeSqlFragment
  }
  if (value instanceof Date) {
    return safeSql`'${formatDate(value.toISOString() as SafeSqlFragment)}'`
  }
  if (Array.isArray(value)) {
    const temporary: string[] = []
    for (const [index, element] of value.entries()) {
      if (Array.isArray(element) === true) {
        temporary.push(arrayToList(index !== 0, element, literal))
      } else {
        temporary.push(literal(element))
      }
    }
    return temporary.toString() as SafeSqlFragment
  }
  if (value === Object(value)) {
    explicitCast = 'jsonb'
    tliteral = JSON.stringify(value)
  } else {
    tliteral = String(value).slice(0) // create copy
  }

  let hasBackslash = false
  let quoted = "'"

  for (const c of tliteral) {
    if (c === "'") {
      quoted += c + c
    } else if (c === '\\') {
      quoted += c + c
      hasBackslash = true
    } else {
      quoted += c
    }
  }

  quoted += "'"

  if (hasBackslash === true) {
    quoted = `E${quoted}`
  }

  if (explicitCast) {
    quoted += `::${explicitCast}`
  }

  return quoted as SafeSqlFragment
}

// Multi-word SQL keyword phrases callers are allowed to pass to `keyword()`.
// Compared case-insensitively. Any phrase not listed here must be expressed as
// separate single-word `keyword()` calls composed via `safeSql`, so that
// arbitrary control phrases (e.g. "DROP TABLE") can't slip through.
const ALLOWED_MULTI_WORD_KEYWORDS = new Set(['INSTEAD OF', 'BY DEFAULT'])

/**
 * Marks SQL keywords (e.g., 'BEFORE', 'INSTEAD OF') as safe for interpolation.
 * Accepts either a single word matching [A-Za-z][A-Za-z0-9_]*, or a
 * multi-word phrase from `ALLOWED_MULTI_WORD_KEYWORDS`. Any other input —
 * including arbitrary space-separated tokens like "DROP TABLE" — is rejected.
 */
export function keyword(value: string): SafeSqlFragment {
  if (/^[A-Za-z][A-Za-z0-9_]*$/.test(value)) {
    return value as SafeSqlFragment
  }
  if (ALLOWED_MULTI_WORD_KEYWORDS.has(value.toUpperCase())) {
    return value as SafeSqlFragment
  }
  throw new Error(
    `Not a valid keyword: "${value}". Must be a single word matching [A-Za-z][A-Za-z0-9_]*, or one of: ${[...ALLOWED_MULTI_WORD_KEYWORDS].join(', ')}.`
  )
}

type Stringifyable =
  | SafeSqlFragment
  | number
  | boolean
  | Date
  | null
  | undefined
  | Record<string | number | symbol, unknown>
  | Stringifyable[]

// eslint-disable-next-line radar/cognitive-complexity
export function string(value?: Stringifyable): SafeSqlFragment {
  if (value === undefined || value === null) {
    return safeSql``
  }
  if (value === false) {
    return safeSql`f`
  }
  if (value === true) {
    return safeSql`t`
  }
  if (value instanceof Date) {
    return formatDate(value.toISOString() as SafeSqlFragment)
  }
  if (Array.isArray(value)) {
    const temporary: SafeSqlFragment[] = []
    for (const [index, element] of value.entries()) {
      if (element !== null && element !== undefined) {
        if (Array.isArray(element) === true) {
          temporary.push(arrayToList(index !== 0, element, string))
        } else {
          temporary.push(string(element))
        }
      }
    }
    return temporary.toString() as SafeSqlFragment
  }
  if (!!value && typeof value === 'object') {
    return JSON.stringify(value) as SafeSqlFragment
  }

  // value is number or SafeSqlFragment
  return String(value).toString().slice(0) as SafeSqlFragment // return copy
}

export function config(cfg: PgFormatConfig): void {
  // default
  FMT_PATTERN_CONFIG.ident = 'I'
  FMT_PATTERN_CONFIG.literal = 'L'
  FMT_PATTERN_CONFIG.string = 's'

  if (cfg && cfg.pattern) {
    if (cfg.pattern.ident) {
      FMT_PATTERN_CONFIG.ident = cfg.pattern.ident
    }
    if (cfg.pattern.literal) {
      FMT_PATTERN_CONFIG.literal = cfg.pattern.literal
    }
    if (cfg.pattern.string) {
      FMT_PATTERN_CONFIG.string = cfg.pattern.string
    }
  }
}

export function withArray(fmt: SafeSqlFragment, parameters: SafeSqlFragment[]): SafeSqlFragment {
  let index = 0

  let reText = '%(%|(\\d+\\$)?['
  reText += FMT_PATTERN_CONFIG.ident
  reText += FMT_PATTERN_CONFIG.literal
  reText += FMT_PATTERN_CONFIG.string
  reText += '])'
  const re = new RegExp(reText, 'g')

  // eslint-disable-next-line @typescript-eslint/ban-ts-comment
  return fmt.replace(re, (_, type: string): SafeSqlFragment => {
    if (type === '%') {
      return safeSql`%`
    }

    let position = index
    const tokens = type.split('$')

    if (tokens.length > 1) {
      position = Number.parseInt(tokens[0], 10) - 1
      // eslint-disable-next-line no-param-reassign, prefer-destructuring
      type = tokens[1]
    }

    if (position < 0) {
      throw new Error('specified argument 0 but arguments start at 1')
    } else if (position > parameters.length - 1) {
      throw new Error('too few arguments')
    }

    index = position + 1

    if (type === FMT_PATTERN_CONFIG.ident) {
      return ident(parameters[position])
    }
    if (type === FMT_PATTERN_CONFIG.literal) {
      return literal(parameters[position])
    }
    if (type === FMT_PATTERN_CONFIG.string) {
      return string(parameters[position])
    }

    throw new Error(`unsupported format type: ${type}`)
  }) as SafeSqlFragment
}

export function format(fmt: SafeSqlFragment, ...arguments_: SafeSqlFragment[]): SafeSqlFragment {
  return withArray(fmt, arguments_)
}

/**
 * Tagged template literal for composing SQL fragments safely.
 * Only accepts SafeSqlFragment interpolations — plain strings are rejected at
 * compile time.
 */
export function safeSql(
  strings: TemplateStringsArray,
  ...interpolated: Array<SafeSqlFragment>
): SafeSqlFragment {
  return strings.reduce(
    (result, string, i) => result + string + (interpolated[i] ?? ''),
    ''
  ) as SafeSqlFragment
}

/**
 * Marks a user-provided SQL string as a SafeSqlFragment for execution.
 * Only use this when the user has explicitly typed or authored the SQL
 * (e.g. a SQL editor, RLS tester). Never use for arbitrary data.
 */
export function rawSql(sql: string): SafeSqlFragment {
  return sql as SafeSqlFragment
}

/**
 * Marks SQL that may have been influenced by a third party (URL params, AI
 * output, external content) as UntrustedSqlFragment. Safe to display; must
 * never be auto-executed or persisted as user-authored.
 */
export function untrustedSql(sql: string): UntrustedSqlFragment {
  return sql as UntrustedSqlFragment
}

/**
 * Promote SQL to executable after explicit user acknowledgment.
 * Accepts DisplayableSqlFragment (SafeSqlFragment | UntrustedSqlFragment) because
 * a Run action approves whatever is currently in the editor, whether the user typed
 * it themselves or loaded it from an external source.
 * ONLY call from an event handler tied to a deliberate user action (onClick,
 * keydown on Run shortcut). Never call from useEffect, render, or any path
 * that runs without a user gesture.
 */
export function acceptUntrustedSql(sql: DisplayableSqlFragment): SafeSqlFragment {
  return sql as unknown as SafeSqlFragment
}

/**
 * Joins an array of already-safe SQL fragments with a fixed structural
 * separator.
 */
export function joinSqlFragments(
  fragments: Array<SafeSqlFragment>,
  separator: SqlFragmentSeparator
): SafeSqlFragment {
  return fragments.join(separator) as SafeSqlFragment
}