konnosDashboard
index.ts409 lines · main
1import { POSTGRESQL_RESERVED_WORDS } from './reserved'
2
3interface PgFormatConfigPattern {
4 ident: string
5 literal: string
6 string: string
7}
8
9export interface PgFormatConfig {
10 pattern: PgFormatConfigPattern
11}
12
13/**
14 * A branded string type representing a SQL fragment that is safe to compose
15 * into queries.
16 * Values of this type are either:
17 * - Static strings in source code (no interpolation)
18 * - Outputs of `ident()`, `literal()`, or `keyword()` (properly escaped or
19 * validated)
20 * - Compositions via `safeSql` template tag (enforces SafeSqlFragment-only
21 * interpolations)
22 * - Compositions via `joinFragments` (enforces SafeSqlFragment inputs)
23 *
24 * Never cast arbitrary strings to this type — use ident/literal/keyword/safeSql
25 * instead.
26 */
27export type SafeSqlFragment = string & { readonly __safeSqlFragmentBrand: never }
28
29/**
30 * A branded string type representing SQL that may have been influenced by a
31 * third party (URL params, AI output, external content). Safe to display;
32 * must never be auto-executed or persisted as user-authored content.
33 * Promote to SafeSqlFragment via acceptUntrustedSql() — only inside an
34 * explicit user-action event handler.
35 */
36export type UntrustedSqlFragment = string & { readonly __untrustedSqlFragmentBrand: never }
37
38/** Either brand — for read-only display surfaces that accept both. */
39export type DisplayableSqlFragment = SafeSqlFragment | UntrustedSqlFragment
40
41export type SqlFragmentSeparator =
42 | ','
43 | ', '
44 | ';\n'
45 | ' and '
46 | ' AND '
47 | ' or '
48 | ' OR '
49 | ' union all '
50 | ' union '
51 | ' UNION ALL '
52 | ' UNION '
53 | '\n'
54 | '\n\n'
55 | ' '
56
57const FMT_PATTERN_CONFIG: PgFormatConfigPattern = {
58 ident: 'I',
59 literal: 'L',
60 string: 's',
61}
62
63// convert to Postgres default ISO 8601 format
64function formatDate(date: SafeSqlFragment): SafeSqlFragment {
65 return date.replace('T', ' ').replace('Z', '+00') as SafeSqlFragment
66}
67
68function isReserved(value: string): boolean {
69 if (POSTGRESQL_RESERVED_WORDS.has(value.toUpperCase())) {
70 return true
71 }
72 return false
73}
74
75function arrayToList<ElementType = unknown>(
76 useSpace: boolean,
77 array: ElementType[],
78 formatter: (value: ElementType) => SafeSqlFragment
79): SafeSqlFragment {
80 let sql = safeSql``
81
82 sql = useSpace ? safeSql`${sql} (` : safeSql`${sql} (`
83 for (const [index, element] of array.entries()) {
84 sql = safeSql`${sql}${index === 0 ? safeSql`` : safeSql`, `}${formatter(element)}`
85 }
86 sql = safeSql`${sql})`
87
88 return sql
89}
90
91// Ported from PostgreSQL 9.2.4 source code in src/interfaces/libpq/fe-exec.c
92// eslint-disable-next-line radar/cognitive-complexity
93export function ident(value?: unknown): SafeSqlFragment {
94 if (value === undefined || value === null) {
95 throw new Error('SQL identifier cannot be null or undefined')
96 } else if (value === false) {
97 return '"f"' as SafeSqlFragment
98 } else if (value === true) {
99 return '"t"' as SafeSqlFragment
100 } else if (value instanceof Date) {
101 return safeSql`"${formatDate(value.toISOString() as SafeSqlFragment)}"`
102 } else if (Array.isArray(value)) {
103 const temporary: string[] = []
104 for (const element of value) {
105 if (Array.isArray(element) === true) {
106 throw new TypeError(
107 'Nested array to grouped list conversion is not supported for SQL identifier'
108 )
109 } else {
110 temporary.push(ident(element))
111 }
112 }
113 return temporary.toString() as SafeSqlFragment
114 } else if (value === Object(value)) {
115 throw new Error('SQL identifier cannot be an object')
116 }
117
118 const tident = String(value).slice(0) // create copy
119
120 // do not quote a valid, unquoted identifier
121 if (/^[_a-z][\d$_a-z]*$/.test(tident) === true && isReserved(tident) === false) {
122 return tident as SafeSqlFragment
123 }
124
125 let quoted = '"'
126
127 for (const c of tident) {
128 quoted += c === '"' ? c + c : c
129 }
130
131 quoted += '"'
132
133 return quoted as SafeSqlFragment
134}
135
136// Ported from PostgreSQL 9.2.4 source code in src/interfaces/libpq/fe-exec.c
137// eslint-disable-next-line radar/cognitive-complexity
138export function literal(value?: unknown): SafeSqlFragment {
139 let tliteral = ''
140 let explicitCast: string | undefined
141
142 if (value === undefined || value === null) {
143 return 'NULL' as SafeSqlFragment
144 }
145 if (typeof value === 'bigint') {
146 return BigInt(value).toString() as SafeSqlFragment
147 }
148 if (value === Number.POSITIVE_INFINITY) {
149 return "'Infinity'" as SafeSqlFragment
150 }
151 if (value === Number.NEGATIVE_INFINITY) {
152 return "'-Infinity'" as SafeSqlFragment
153 }
154 if (Number.isNaN(value)) {
155 return "'NaN'" as SafeSqlFragment
156 }
157 if (typeof value === 'number') {
158 // Test must be AFTER other special case number tests
159 return Number(value).toString() as SafeSqlFragment
160 }
161 if (value === false) {
162 return "'f'" as SafeSqlFragment
163 }
164 if (value === true) {
165 return "'t'" as SafeSqlFragment
166 }
167 if (value instanceof Date) {
168 return safeSql`'${formatDate(value.toISOString() as SafeSqlFragment)}'`
169 }
170 if (Array.isArray(value)) {
171 const temporary: string[] = []
172 for (const [index, element] of value.entries()) {
173 if (Array.isArray(element) === true) {
174 temporary.push(arrayToList(index !== 0, element, literal))
175 } else {
176 temporary.push(literal(element))
177 }
178 }
179 return temporary.toString() as SafeSqlFragment
180 }
181 if (value === Object(value)) {
182 explicitCast = 'jsonb'
183 tliteral = JSON.stringify(value)
184 } else {
185 tliteral = String(value).slice(0) // create copy
186 }
187
188 let hasBackslash = false
189 let quoted = "'"
190
191 for (const c of tliteral) {
192 if (c === "'") {
193 quoted += c + c
194 } else if (c === '\\') {
195 quoted += c + c
196 hasBackslash = true
197 } else {
198 quoted += c
199 }
200 }
201
202 quoted += "'"
203
204 if (hasBackslash === true) {
205 quoted = `E${quoted}`
206 }
207
208 if (explicitCast) {
209 quoted += `::${explicitCast}`
210 }
211
212 return quoted as SafeSqlFragment
213}
214
215// Multi-word SQL keyword phrases callers are allowed to pass to `keyword()`.
216// Compared case-insensitively. Any phrase not listed here must be expressed as
217// separate single-word `keyword()` calls composed via `safeSql`, so that
218// arbitrary control phrases (e.g. "DROP TABLE") can't slip through.
219const ALLOWED_MULTI_WORD_KEYWORDS = new Set(['INSTEAD OF', 'BY DEFAULT'])
220
221/**
222 * Marks SQL keywords (e.g., 'BEFORE', 'INSTEAD OF') as safe for interpolation.
223 * Accepts either a single word matching [A-Za-z][A-Za-z0-9_]*, or a
224 * multi-word phrase from `ALLOWED_MULTI_WORD_KEYWORDS`. Any other input —
225 * including arbitrary space-separated tokens like "DROP TABLE" — is rejected.
226 */
227export function keyword(value: string): SafeSqlFragment {
228 if (/^[A-Za-z][A-Za-z0-9_]*$/.test(value)) {
229 return value as SafeSqlFragment
230 }
231 if (ALLOWED_MULTI_WORD_KEYWORDS.has(value.toUpperCase())) {
232 return value as SafeSqlFragment
233 }
234 throw new Error(
235 `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(', ')}.`
236 )
237}
238
239type Stringifyable =
240 | SafeSqlFragment
241 | number
242 | boolean
243 | Date
244 | null
245 | undefined
246 | Record<string | number | symbol, unknown>
247 | Stringifyable[]
248
249// eslint-disable-next-line radar/cognitive-complexity
250export function string(value?: Stringifyable): SafeSqlFragment {
251 if (value === undefined || value === null) {
252 return safeSql``
253 }
254 if (value === false) {
255 return safeSql`f`
256 }
257 if (value === true) {
258 return safeSql`t`
259 }
260 if (value instanceof Date) {
261 return formatDate(value.toISOString() as SafeSqlFragment)
262 }
263 if (Array.isArray(value)) {
264 const temporary: SafeSqlFragment[] = []
265 for (const [index, element] of value.entries()) {
266 if (element !== null && element !== undefined) {
267 if (Array.isArray(element) === true) {
268 temporary.push(arrayToList(index !== 0, element, string))
269 } else {
270 temporary.push(string(element))
271 }
272 }
273 }
274 return temporary.toString() as SafeSqlFragment
275 }
276 if (!!value && typeof value === 'object') {
277 return JSON.stringify(value) as SafeSqlFragment
278 }
279
280 // value is number or SafeSqlFragment
281 return String(value).toString().slice(0) as SafeSqlFragment // return copy
282}
283
284export function config(cfg: PgFormatConfig): void {
285 // default
286 FMT_PATTERN_CONFIG.ident = 'I'
287 FMT_PATTERN_CONFIG.literal = 'L'
288 FMT_PATTERN_CONFIG.string = 's'
289
290 if (cfg && cfg.pattern) {
291 if (cfg.pattern.ident) {
292 FMT_PATTERN_CONFIG.ident = cfg.pattern.ident
293 }
294 if (cfg.pattern.literal) {
295 FMT_PATTERN_CONFIG.literal = cfg.pattern.literal
296 }
297 if (cfg.pattern.string) {
298 FMT_PATTERN_CONFIG.string = cfg.pattern.string
299 }
300 }
301}
302
303export function withArray(fmt: SafeSqlFragment, parameters: SafeSqlFragment[]): SafeSqlFragment {
304 let index = 0
305
306 let reText = '%(%|(\\d+\\$)?['
307 reText += FMT_PATTERN_CONFIG.ident
308 reText += FMT_PATTERN_CONFIG.literal
309 reText += FMT_PATTERN_CONFIG.string
310 reText += '])'
311 const re = new RegExp(reText, 'g')
312
313 // eslint-disable-next-line @typescript-eslint/ban-ts-comment
314 return fmt.replace(re, (_, type: string): SafeSqlFragment => {
315 if (type === '%') {
316 return safeSql`%`
317 }
318
319 let position = index
320 const tokens = type.split('$')
321
322 if (tokens.length > 1) {
323 position = Number.parseInt(tokens[0], 10) - 1
324 // eslint-disable-next-line no-param-reassign, prefer-destructuring
325 type = tokens[1]
326 }
327
328 if (position < 0) {
329 throw new Error('specified argument 0 but arguments start at 1')
330 } else if (position > parameters.length - 1) {
331 throw new Error('too few arguments')
332 }
333
334 index = position + 1
335
336 if (type === FMT_PATTERN_CONFIG.ident) {
337 return ident(parameters[position])
338 }
339 if (type === FMT_PATTERN_CONFIG.literal) {
340 return literal(parameters[position])
341 }
342 if (type === FMT_PATTERN_CONFIG.string) {
343 return string(parameters[position])
344 }
345
346 throw new Error(`unsupported format type: ${type}`)
347 }) as SafeSqlFragment
348}
349
350export function format(fmt: SafeSqlFragment, ...arguments_: SafeSqlFragment[]): SafeSqlFragment {
351 return withArray(fmt, arguments_)
352}
353
354/**
355 * Tagged template literal for composing SQL fragments safely.
356 * Only accepts SafeSqlFragment interpolations — plain strings are rejected at
357 * compile time.
358 */
359export function safeSql(
360 strings: TemplateStringsArray,
361 ...interpolated: Array<SafeSqlFragment>
362): SafeSqlFragment {
363 return strings.reduce(
364 (result, string, i) => result + string + (interpolated[i] ?? ''),
365 ''
366 ) as SafeSqlFragment
367}
368
369/**
370 * Marks a user-provided SQL string as a SafeSqlFragment for execution.
371 * Only use this when the user has explicitly typed or authored the SQL
372 * (e.g. a SQL editor, RLS tester). Never use for arbitrary data.
373 */
374export function rawSql(sql: string): SafeSqlFragment {
375 return sql as SafeSqlFragment
376}
377
378/**
379 * Marks SQL that may have been influenced by a third party (URL params, AI
380 * output, external content) as UntrustedSqlFragment. Safe to display; must
381 * never be auto-executed or persisted as user-authored.
382 */
383export function untrustedSql(sql: string): UntrustedSqlFragment {
384 return sql as UntrustedSqlFragment
385}
386
387/**
388 * Promote SQL to executable after explicit user acknowledgment.
389 * Accepts DisplayableSqlFragment (SafeSqlFragment | UntrustedSqlFragment) because
390 * a Run action approves whatever is currently in the editor, whether the user typed
391 * it themselves or loaded it from an external source.
392 * ONLY call from an event handler tied to a deliberate user action (onClick,
393 * keydown on Run shortcut). Never call from useEffect, render, or any path
394 * that runs without a user gesture.
395 */
396export function acceptUntrustedSql(sql: DisplayableSqlFragment): SafeSqlFragment {
397 return sql as unknown as SafeSqlFragment
398}
399
400/**
401 * Joins an array of already-safe SQL fragments with a fixed structural
402 * separator.
403 */
404export function joinSqlFragments(
405 fragments: Array<SafeSqlFragment>,
406 separator: SqlFragmentSeparator
407): SafeSqlFragment {
408 return fragments.join(separator) as SafeSqlFragment
409}