konnosDashboard
SQLEditor.utils.ts263 lines · main
1import { untrustedSql, type SafeSqlFragment } from '@supabase/pg-meta'
2import { TABLE_EVENT_ACTIONS } from 'common/telemetry-constants'
3
4import {
5 alterDatabasePreventConnectionStatements,
6 destructiveSqlRegex,
7 NEW_SQL_SNIPPET_SKELETON,
8 sqlAiDisclaimerComment,
9 updateWithoutWhereRegex,
10} from './SQLEditor.constants'
11import { ContentDiff } from './SQLEditor.types'
12import type { DatabaseEventTrigger } from '@/data/database-event-triggers/database-event-triggers-query'
13import { generateUuid } from '@/lib/api/snippets.browser'
14import { removeCommentsFromSql } from '@/lib/helpers'
15import { sqlEventParser } from '@/lib/sql-event-parser'
16import type { SnippetWithContent } from '@/state/sql-editor-v2'
17
18export type CreateTableWithoutRLS = {
19 schema?: string
20 tableName: string
21}
22
23// The ensure_rls event trigger only auto-enables RLS on tables in the public
24// schema (see AUTO_ENABLE_RLS_EVENT_TRIGGER_SQL).
25const ENSURE_RLS_TRIGGER_SCHEMAS = new Set(['public'])
26
27export function hasActiveEnsureRLSTrigger(triggers: DatabaseEventTrigger[] | undefined) {
28 return (
29 triggers?.some(
30 (t) =>
31 (t.name === 'ensure_rls' || t.function_name === 'rls_auto_enable') &&
32 t.enabled_mode !== 'DISABLED'
33 ) ?? false
34 )
35}
36
37/**
38 * Filters out CREATE TABLE entries that will be covered by the project's
39 * ensure_rls event trigger (which only handles tables in the public schema).
40 * Tables in any other schema are returned unchanged so the user is still warned.
41 */
42export function filterTablesCoveredByEnsureRLSTrigger(
43 tables: CreateTableWithoutRLS[],
44 hasTrigger: boolean
45): CreateTableWithoutRLS[] {
46 if (!hasTrigger) return tables
47 return tables.filter((t) => !ENSURE_RLS_TRIGGER_SCHEMAS.has((t.schema ?? 'public').toLowerCase()))
48}
49
50export const createSqlSnippetSkeletonV2 = ({
51 name,
52 sql,
53 owner_id,
54 project_id,
55 folder_id,
56 idOverride,
57}: {
58 name: string
59 sql: string
60 owner_id: number
61 project_id: number
62 folder_id?: string
63 /**
64 * Optionally, provide a specific snippetId to use for the snippet. This is used to ensure the snippet is created
65 * with a known id, such as to prevent flicker in the SQL editor when adding new unsaved snippets.
66 */
67 idOverride?: string
68}): SnippetWithContent => {
69 const id = idOverride ?? generateUuid([folder_id, `${name}.sql`])
70
71 return {
72 ...NEW_SQL_SNIPPET_SKELETON,
73 id,
74 owner_id,
75 project_id,
76 name,
77 folder_id,
78 favorite: false,
79 inserted_at: new Date().toISOString(),
80 updated_at: new Date().toISOString(),
81 content: {
82 ...NEW_SQL_SNIPPET_SKELETON.content,
83 content_id: id ?? '',
84 unchecked_sql: untrustedSql(sql ?? ''),
85 } as any,
86 isNotSavedInDatabaseYet: true,
87 }
88}
89
90export function checkDestructiveQuery(sql: string) {
91 const cleanedSql = removeCommentsFromSql(sql)
92 return destructiveSqlRegex.some((regex) => regex.test(cleanedSql))
93}
94
95// Replace the contents of single-quoted string literals and double-quoted
96// identifiers with empty quotes, so a downstream `where` scan can't be fooled
97// by tokens like `UPDATE "where table" SET ...` or `SET name = 'where x'`.
98// Postgres uses doubled quotes to escape, so `''` and `""` are matched as
99// part of the same span rather than terminating it.
100const stripQuotedSpans = (sql: string) =>
101 sql.replace(/'(?:''|[^'])*'/g, "''").replace(/"(?:""|[^"])*"/g, '""')
102
103// Function to check for UPDATE queries without WHERE clause
104export function isUpdateWithoutWhere(sql: string): boolean {
105 const updateStatements = sql
106 .split(';')
107 .filter((statement) => statement.trim().toLowerCase().startsWith('update'))
108 return updateStatements.some(
109 (statement) =>
110 updateWithoutWhereRegex.test(statement) && !/where\s/i.test(stripQuotedSpans(statement))
111 )
112}
113
114/**
115 * Returns CREATE TABLE statements in `sql` that do not have a matching
116 * ALTER TABLE ... ENABLE ROW LEVEL SECURITY in the same SQL submission.
117 *
118 * Operates on the SQL passed in (which is the user's selection if any, or the
119 * full editor contents otherwise) so partial-execution selects work naturally.
120 */
121export function getCreateTablesMissingRLS(sql: string): CreateTableWithoutRLS[] {
122 const events = sqlEventParser.getTableEvents(sql)
123
124 // Match case-sensitively. Lowercasing would let quoted identifiers like
125 // "MyTable" and "mytable" — which are different tables in Postgres — collide
126 // and silently suppress the warning. The trade-off is rare false positives
127 // when users mix case for *unquoted* identifiers (Postgres would have folded
128 // them anyway), which is annoying but safe.
129 const key = (e: { schema?: string; tableName?: string }) => `${e.schema ?? ''}.${e.tableName}`
130
131 const rlsEnabled = new Set(
132 events.filter((e) => e.type === TABLE_EVENT_ACTIONS.TableRLSEnabled && e.tableName).map(key)
133 )
134
135 return events
136 .filter((e) => e.type === TABLE_EVENT_ACTIONS.TableCreated && e.tableName)
137 .filter((e) => !rlsEnabled.has(key(e)))
138 .map((e) => ({ schema: e.schema, tableName: e.tableName as string }))
139}
140
141/**
142 * Appends `ALTER TABLE ... ENABLE ROW LEVEL SECURITY` statements to `sql`
143 * for each provided table.
144 */
145export function appendEnableRLSStatements(sql: string, tables: CreateTableWithoutRLS[]) {
146 if (tables.length === 0) return sql
147
148 // Postgres folds unquoted identifiers to lowercase, so any identifier that
149 // isn't strictly lowercase-safe (e.g. "MyTable", "user table") must be quoted
150 // to refer back to the original table.
151 const quote = (identifier: string) =>
152 /^[a-z_][a-z0-9_]*$/.test(identifier) ? identifier : `"${identifier.replace(/"/g, '""')}"`
153
154 const additions = tables
155 .map(({ schema, tableName }) => {
156 const target = schema ? `${quote(schema)}.${quote(tableName)}` : quote(tableName)
157 return `ALTER TABLE ${target} ENABLE ROW LEVEL SECURITY;`
158 })
159 .join('\n')
160
161 const trimmed = sql.replace(/\s+$/, '')
162 // If the SQL ends with a line comment, the appended ';' would be swallowed,
163 // so put the terminator on its own line.
164 const endsWithLineComment = /--[^\r\n]*$/.test(trimmed)
165 const separator = trimmed.endsWith(';') ? '\n\n' : endsWithLineComment ? '\n;\n\n' : ';\n\n'
166
167 return `${trimmed}${separator}-- Added by Briven: enable Row Level Security on newly created tables\n${additions}\n`
168}
169
170export function checkAlterDatabaseConnection(sql: string): boolean {
171 const cleanedSql = removeCommentsFromSql(sql)
172 const statements = cleanedSql
173 .split(';')
174 .filter((statement) => statement.trim().toLowerCase().startsWith('alter database'))
175 return statements.some((statement) =>
176 alterDatabasePreventConnectionStatements.some((x) => statement.toLowerCase().includes(x))
177 )
178}
179
180export const generateMigrationCliCommand = (id: string, name: string, isNpx = false) =>
181 `
182${isNpx ? 'npx ' : ''}briven snippets download ${id} |
183${isNpx ? 'npx ' : ''}briven migration new ${name}
184`.trim()
185
186export const generateSeedCliCommand = (id: string, isNpx = false) =>
187 `
188${isNpx ? 'npx ' : ''}briven snippets download ${id} >> \\
189 briven/seed.sql
190`.trim()
191
192export const generateFileCliCommand = (id: string, name: string, isNpx = false) =>
193 `
194${isNpx ? 'npx ' : ''}briven snippets download ${id} > \\
195 ${name}.sql
196`.trim()
197
198export const compareAsModification = (sqlDiff: ContentDiff) => {
199 const formattedModified = sqlDiff.modified.replace(sqlAiDisclaimerComment, '').trim()
200
201 return {
202 original: sqlDiff.original,
203 modified: `${formattedModified}`,
204 }
205}
206
207export const compareAsAddition = (sqlDiff: ContentDiff) => {
208 const formattedOriginal = sqlDiff.original.replace(sqlAiDisclaimerComment, '').trim()
209 const formattedModified = sqlDiff.modified.replace(sqlAiDisclaimerComment, '').trim()
210 const newModified = (formattedOriginal ? formattedOriginal + '\n\n' : '') + formattedModified
211
212 return {
213 original: sqlDiff.original,
214 modified: newModified,
215 }
216}
217
218export const compareAsNewSnippet = (sqlDiff: ContentDiff) => {
219 return {
220 original: '',
221 modified: sqlDiff.modified,
222 }
223}
224
225// [Joshen] Just FYI as well the checks here on whether to append limit is quite restricted
226// This is to prevent dashboard from accidentally appending limit to the end of a query
227// thats not supposed to have any, since there's too many cases to cover.
228// We can however look into making this logic better in the future
229// i.e It's harder to append the limit param, than just leaving the query as it is
230// Otherwise we'd need a full on parser to do this properly
231export const checkIfAppendLimitRequired = (sql: string, limit: number = 0) => {
232 // Remove lines and whitespaces to use for checking
233 const cleanedSql = sql.trim().replaceAll('\n', ' ').replaceAll(/\s+/g, ' ')
234
235 // Check how many queries
236 const regMatch = cleanedSql.matchAll(/[a-zA-Z]*[0-9]*[;]+/g)
237 const queries = new Array(...regMatch)
238 const indexSemiColon = cleanedSql.lastIndexOf(';')
239 const hasComments = cleanedSql.includes('--')
240 const hasMultipleQueries =
241 queries.length > 1 || (indexSemiColon > 0 && indexSemiColon !== cleanedSql.length - 1)
242
243 // Check if need to auto limit rows
244 const appendAutoLimit =
245 limit > 0 &&
246 !hasComments &&
247 !hasMultipleQueries &&
248 cleanedSql.toLowerCase().startsWith('select') &&
249 !cleanedSql.toLowerCase().match(/fetch\s+first/i) &&
250 !cleanedSql.match(/limit$/i) &&
251 !cleanedSql.match(/limit;$/i) &&
252 !cleanedSql.match(/limit [0-9]* offset [0-9]*[;]?$/i) &&
253 !cleanedSql.match(/limit [0-9]*[;]?$/i)
254 return { cleanedSql, appendAutoLimit }
255}
256
257export const suffixWithLimit = (sql: SafeSqlFragment, limit: number = 0): SafeSqlFragment => {
258 const { cleanedSql, appendAutoLimit } = checkIfAppendLimitRequired(sql, limit)
259 if (!appendAutoLimit) return sql
260 return (
261 cleanedSql.endsWith(';') ? sql.replace(/[;]+$/, ` limit ${limit};`) : `${sql} limit ${limit};`
262 ) as SafeSqlFragment
263}