konnossql-identifier-quoting.ts97 lines · main
| 1 | import { POSTGRESQL_RESERVED_WORDS } from '@supabase/pg-meta/src/pg-format/reserved' |
| 2 | import type { ColumnDef, ColumnRef, Node, RangeVar, ResTarget } from 'libpg-query' |
| 3 | |
| 4 | /** |
| 5 | * Recursively traverse a libpg-query AST to extract all identifiers. |
| 6 | * Collects table names from RangeVar and column names from ColumnRef/ResTarget. |
| 7 | */ |
| 8 | export function extractIdentifiers(ast: Node | Node[]): string[] { |
| 9 | const identifiers: string[] = [] |
| 10 | |
| 11 | function extractFromRangeVar(rv: RangeVar): void { |
| 12 | if (rv.relname) identifiers.push(rv.relname) |
| 13 | if (rv.schemaname) identifiers.push(rv.schemaname) |
| 14 | } |
| 15 | |
| 16 | function traverse(node: unknown): void { |
| 17 | if (!node || typeof node !== 'object') return |
| 18 | |
| 19 | const obj = node as Record<string, unknown> |
| 20 | |
| 21 | // RangeVar - table references (wrapped form in SELECT) |
| 22 | if ('RangeVar' in obj) { |
| 23 | extractFromRangeVar(obj.RangeVar as RangeVar) |
| 24 | } |
| 25 | |
| 26 | // relation - table references (unwrapped form in INSERT/UPDATE/DELETE) |
| 27 | if ('relation' in obj && obj.relation && typeof obj.relation === 'object') { |
| 28 | extractFromRangeVar(obj.relation as RangeVar) |
| 29 | } |
| 30 | |
| 31 | // ColumnRef - column references in expressions |
| 32 | if ('ColumnRef' in obj) { |
| 33 | const cr = obj.ColumnRef as ColumnRef |
| 34 | for (const field of cr.fields ?? []) { |
| 35 | if ('String' in field) { |
| 36 | const str = field.String as { sval?: string } |
| 37 | if (str.sval) identifiers.push(str.sval) |
| 38 | } |
| 39 | } |
| 40 | } |
| 41 | |
| 42 | // ResTarget - column targets in INSERT/UPDATE |
| 43 | if ('ResTarget' in obj) { |
| 44 | const rt = obj.ResTarget as ResTarget |
| 45 | if (rt.name) identifiers.push(rt.name) |
| 46 | } |
| 47 | |
| 48 | // ColumnDef - column definitions in CREATE TABLE |
| 49 | if ('ColumnDef' in obj) { |
| 50 | const cd = obj.ColumnDef as ColumnDef |
| 51 | if (cd.colname) identifiers.push(cd.colname) |
| 52 | } |
| 53 | |
| 54 | // Recurse into all values |
| 55 | for (const value of Object.values(obj)) { |
| 56 | if (Array.isArray(value)) { |
| 57 | value.forEach(traverse) |
| 58 | } else { |
| 59 | traverse(value) |
| 60 | } |
| 61 | } |
| 62 | } |
| 63 | |
| 64 | traverse(ast) |
| 65 | return identifiers |
| 66 | } |
| 67 | |
| 68 | export function needsQuoting(identifier: string): boolean { |
| 69 | if (POSTGRESQL_RESERVED_WORDS.has(identifier.toUpperCase())) { |
| 70 | return true |
| 71 | } |
| 72 | |
| 73 | // Matches valid unquoted identifiers: starts with underscore or lowercase letter, |
| 74 | // followed by digits, dollar signs, underscores, or lowercase letters |
| 75 | // Examples: "users", "user_id", "_private", "col$name" |
| 76 | const validUnquotedPattern = /^[_a-z][\d$_a-z]*$/ |
| 77 | if (validUnquotedPattern.test(identifier)) { |
| 78 | return false |
| 79 | } |
| 80 | |
| 81 | return true |
| 82 | } |
| 83 | |
| 84 | export function isQuotedInSql(sql: string, identifier: string): boolean { |
| 85 | // Escapes special regex characters so they're treated literally |
| 86 | // Examples: "table.name" -> "table\.name", "col(value)" -> "col\(value\)" |
| 87 | const escapedForRegex = identifier.replace(/[.*+?^${}()|[\]\\]/g, '\\$&') |
| 88 | |
| 89 | // PostgreSQL escapes quotes inside quoted identifiers as "" |
| 90 | const escapedIdentifier = escapedForRegex.replace(/"/g, '""') |
| 91 | |
| 92 | // Matches quoted identifier: "identifier" (case-insensitive) |
| 93 | // Examples: "MyTable", "my""table" (for identifier "my"table") |
| 94 | const quotedPattern = new RegExp(`"${escapedIdentifier}"`, 'i') |
| 95 | |
| 96 | return quotedPattern.test(sql) |
| 97 | } |