konnosDashboard
sql-event-parser.test.ts572 lines · main
1import { TABLE_EVENT_ACTIONS } from 'common/telemetry-constants'
2import { describe, expect, it } from 'vitest'
3
4import { sqlEventParser } from './sql-event-parser'
5
6describe('SQL Event Parser', () => {
7 describe('CREATE TABLE detection', () => {
8 it('detects basic CREATE TABLE', () => {
9 const results = sqlEventParser.getTableEvents('CREATE TABLE users (id INT PRIMARY KEY)')
10 expect(results).toHaveLength(1)
11 expect(results[0]).toEqual({
12 type: TABLE_EVENT_ACTIONS.TableCreated,
13 schema: undefined,
14 tableName: 'users',
15 })
16 })
17
18 it('detects CREATE TABLE with schema', () => {
19 const results = sqlEventParser.getTableEvents('CREATE TABLE public.users (id INT)')
20 expect(results).toHaveLength(1)
21 expect(results[0]).toEqual({
22 type: TABLE_EVENT_ACTIONS.TableCreated,
23 schema: 'public',
24 tableName: 'users',
25 })
26 })
27
28 it('detects CREATE TABLE IF NOT EXISTS', () => {
29 const results = sqlEventParser.getTableEvents('CREATE TABLE IF NOT EXISTS users (id INT)')
30 expect(results).toHaveLength(1)
31 expect(results[0]).toEqual({
32 type: TABLE_EVENT_ACTIONS.TableCreated,
33 schema: undefined,
34 tableName: 'users',
35 })
36 })
37
38 it('handles quoted identifiers', () => {
39 const results = sqlEventParser.getTableEvents('CREATE TABLE "public"."user_table" (id INT)')
40 expect(results).toHaveLength(1)
41 expect(results[0]).toEqual({
42 type: TABLE_EVENT_ACTIONS.TableCreated,
43 schema: 'public',
44 tableName: 'user_table',
45 })
46 })
47
48 it('returns empty array for non-matching SQL', () => {
49 const results = sqlEventParser.getTableEvents('SELECT * FROM users')
50 expect(results).toHaveLength(0)
51 })
52
53 it('detects CREATE TEMPORARY TABLE', () => {
54 const results = sqlEventParser.getTableEvents('CREATE TEMPORARY TABLE temp_users (id INT)')
55 expect(results).toHaveLength(1)
56 expect(results[0]).toEqual({
57 type: TABLE_EVENT_ACTIONS.TableCreated,
58 schema: undefined,
59 tableName: 'temp_users',
60 })
61 })
62
63 it('detects CREATE TEMP TABLE', () => {
64 const results = sqlEventParser.getTableEvents('CREATE TEMP TABLE temp_users (id INT)')
65 expect(results).toHaveLength(1)
66 expect(results[0]).toEqual({
67 type: TABLE_EVENT_ACTIONS.TableCreated,
68 schema: undefined,
69 tableName: 'temp_users',
70 })
71 })
72
73 it('detects CREATE UNLOGGED TABLE', () => {
74 const results = sqlEventParser.getTableEvents('CREATE UNLOGGED TABLE fast_table (id INT)')
75 expect(results).toHaveLength(1)
76 expect(results[0]).toEqual({
77 type: TABLE_EVENT_ACTIONS.TableCreated,
78 schema: undefined,
79 tableName: 'fast_table',
80 })
81 })
82
83 it('detects CREATE TEMP TABLE IF NOT EXISTS', () => {
84 const results = sqlEventParser.getTableEvents(
85 'CREATE TEMP TABLE IF NOT EXISTS temp_users (id INT)'
86 )
87 expect(results).toHaveLength(1)
88 expect(results[0]).toEqual({
89 type: TABLE_EVENT_ACTIONS.TableCreated,
90 schema: undefined,
91 tableName: 'temp_users',
92 })
93 })
94 })
95
96 describe('INSERT detection', () => {
97 it('detects basic INSERT INTO', () => {
98 const results = sqlEventParser.getTableEvents("INSERT INTO users (name) VALUES ('John')")
99 expect(results).toHaveLength(1)
100 expect(results[0]).toEqual({
101 type: TABLE_EVENT_ACTIONS.TableDataAdded,
102 schema: undefined,
103 tableName: 'users',
104 })
105 })
106
107 it('detects INSERT with schema', () => {
108 const results = sqlEventParser.getTableEvents(
109 "INSERT INTO public.users (name) VALUES ('John')"
110 )
111 expect(results).toHaveLength(1)
112 expect(results[0]).toEqual({
113 type: TABLE_EVENT_ACTIONS.TableDataAdded,
114 schema: 'public',
115 tableName: 'users',
116 })
117 })
118
119 it('handles quoted identifiers', () => {
120 const results = sqlEventParser.getTableEvents('INSERT INTO "auth"."users" (id) VALUES (1)')
121 expect(results).toHaveLength(1)
122 expect(results[0]).toEqual({
123 type: TABLE_EVENT_ACTIONS.TableDataAdded,
124 schema: 'auth',
125 tableName: 'users',
126 })
127 })
128
129 it('returns empty array for non-matching SQL', () => {
130 const results = sqlEventParser.getTableEvents('UPDATE users SET name = "John"')
131 expect(results).toHaveLength(0)
132 })
133 })
134
135 describe('COPY detection', () => {
136 it('detects basic COPY FROM', () => {
137 const results = sqlEventParser.getTableEvents("COPY users FROM '/tmp/users.csv'")
138 expect(results).toHaveLength(1)
139 expect(results[0]).toEqual({
140 type: TABLE_EVENT_ACTIONS.TableDataAdded,
141 schema: undefined,
142 tableName: 'users',
143 })
144 })
145
146 it('detects COPY with schema', () => {
147 const results = sqlEventParser.getTableEvents(
148 "COPY public.users FROM '/tmp/users.csv' WITH CSV HEADER"
149 )
150 expect(results).toHaveLength(1)
151 expect(results[0]).toEqual({
152 type: TABLE_EVENT_ACTIONS.TableDataAdded,
153 schema: 'public',
154 tableName: 'users',
155 })
156 })
157
158 it('handles quoted identifiers', () => {
159 const results = sqlEventParser.getTableEvents('COPY "auth"."users" FROM STDIN')
160 expect(results).toHaveLength(1)
161 expect(results[0]).toEqual({
162 type: TABLE_EVENT_ACTIONS.TableDataAdded,
163 schema: 'auth',
164 tableName: 'users',
165 })
166 })
167
168 it('returns empty array for COPY TO', () => {
169 const results = sqlEventParser.getTableEvents("COPY users TO '/tmp/users.csv'")
170 expect(results).toHaveLength(0)
171 })
172
173 it('returns empty array for non-matching SQL', () => {
174 const results = sqlEventParser.getTableEvents('SELECT * FROM users')
175 expect(results).toHaveLength(0)
176 })
177 })
178
179 describe('SELECT INTO detection', () => {
180 it('detects SELECT INTO', () => {
181 const results = sqlEventParser.getTableEvents('SELECT * INTO new_users FROM users')
182 expect(results).toHaveLength(1)
183 expect(results[0]).toEqual({
184 type: TABLE_EVENT_ACTIONS.TableCreated,
185 schema: undefined,
186 tableName: 'new_users',
187 })
188 })
189
190 it('detects SELECT INTO with schema', () => {
191 const results = sqlEventParser.getTableEvents(
192 'SELECT id, name INTO public.new_users FROM users'
193 )
194 expect(results).toHaveLength(1)
195 expect(results[0]).toEqual({
196 type: TABLE_EVENT_ACTIONS.TableCreated,
197 schema: 'public',
198 tableName: 'new_users',
199 })
200 })
201
202 it('detects CREATE TABLE AS SELECT', () => {
203 const results = sqlEventParser.getTableEvents('CREATE TABLE new_users AS SELECT * FROM users')
204 expect(results).toHaveLength(1)
205 expect(results[0]).toEqual({
206 type: TABLE_EVENT_ACTIONS.TableCreated,
207 schema: undefined,
208 tableName: 'new_users',
209 })
210 })
211
212 it('detects CREATE TABLE IF NOT EXISTS AS SELECT', () => {
213 const results = sqlEventParser.getTableEvents(
214 'CREATE TABLE IF NOT EXISTS new_users AS SELECT * FROM users WHERE active = true'
215 )
216 expect(results).toHaveLength(1)
217 expect(results[0]).toEqual({
218 type: TABLE_EVENT_ACTIONS.TableCreated,
219 schema: undefined,
220 tableName: 'new_users',
221 })
222 })
223
224 it('handles quoted identifiers', () => {
225 const results = sqlEventParser.getTableEvents(
226 'SELECT * INTO "backup"."users_2024" FROM users'
227 )
228 expect(results).toHaveLength(1)
229 expect(results[0]).toEqual({
230 type: TABLE_EVENT_ACTIONS.TableCreated,
231 schema: 'backup',
232 tableName: 'users_2024',
233 })
234 })
235
236 it('returns empty array for regular SELECT', () => {
237 const results = sqlEventParser.getTableEvents('SELECT * FROM users')
238 expect(results).toHaveLength(0)
239 })
240 })
241
242 describe('RLS detection', () => {
243 it('detects ALTER TABLE ENABLE ROW LEVEL SECURITY', () => {
244 const results = sqlEventParser.getTableEvents('ALTER TABLE users ENABLE ROW LEVEL SECURITY')
245 expect(results).toHaveLength(1)
246 expect(results[0]).toEqual({
247 type: TABLE_EVENT_ACTIONS.TableRLSEnabled,
248 schema: undefined,
249 tableName: 'users',
250 })
251 })
252
253 it('detects short form ENABLE RLS', () => {
254 const results = sqlEventParser.getTableEvents('ALTER TABLE users ENABLE RLS')
255 expect(results).toHaveLength(1)
256 expect(results[0]).toEqual({
257 type: TABLE_EVENT_ACTIONS.TableRLSEnabled,
258 schema: undefined,
259 tableName: 'users',
260 })
261 })
262
263 it('detects with schema', () => {
264 const results = sqlEventParser.getTableEvents(
265 'ALTER TABLE public.users ENABLE ROW LEVEL SECURITY'
266 )
267 expect(results).toHaveLength(1)
268 expect(results[0]).toEqual({
269 type: TABLE_EVENT_ACTIONS.TableRLSEnabled,
270 schema: 'public',
271 tableName: 'users',
272 })
273 })
274
275 it('handles other ALTER TABLE statements in between', () => {
276 const results = sqlEventParser.getTableEvents(
277 'ALTER TABLE users ADD COLUMN test INT, ENABLE ROW LEVEL SECURITY'
278 )
279 expect(results).toHaveLength(1)
280 expect(results[0]).toEqual({
281 type: TABLE_EVENT_ACTIONS.TableRLSEnabled,
282 schema: undefined,
283 tableName: 'users',
284 })
285 })
286
287 it('returns empty array for disabling RLS', () => {
288 const results = sqlEventParser.getTableEvents('ALTER TABLE users DISABLE ROW LEVEL SECURITY')
289 expect(results).toHaveLength(0)
290 })
291
292 it('detects ALTER TABLE IF EXISTS ENABLE ROW LEVEL SECURITY', () => {
293 const results = sqlEventParser.getTableEvents(
294 'ALTER TABLE IF EXISTS public."Conversations" ENABLE ROW LEVEL SECURITY'
295 )
296 expect(results).toHaveLength(1)
297 expect(results[0]).toEqual({
298 type: TABLE_EVENT_ACTIONS.TableRLSEnabled,
299 schema: 'public',
300 tableName: 'Conversations',
301 })
302 })
303
304 it('detects ALTER TABLE ONLY ENABLE ROW LEVEL SECURITY', () => {
305 const results = sqlEventParser.getTableEvents(
306 'ALTER TABLE ONLY public.users ENABLE ROW LEVEL SECURITY'
307 )
308 expect(results).toHaveLength(1)
309 expect(results[0]).toEqual({
310 type: TABLE_EVENT_ACTIONS.TableRLSEnabled,
311 schema: 'public',
312 tableName: 'users',
313 })
314 })
315
316 it('detects ALTER TABLE IF EXISTS ONLY ENABLE ROW LEVEL SECURITY', () => {
317 const results = sqlEventParser.getTableEvents(
318 'ALTER TABLE IF EXISTS ONLY public.users ENABLE ROW LEVEL SECURITY'
319 )
320 expect(results).toHaveLength(1)
321 expect(results[0]).toEqual({
322 type: TABLE_EVENT_ACTIONS.TableRLSEnabled,
323 schema: 'public',
324 tableName: 'users',
325 })
326 })
327 })
328
329 describe('ReDoS protection', () => {
330 it('handles extremely long identifier names efficiently', () => {
331 const longIdentifier = 'a'.repeat(10000)
332 const sql = `CREATE TABLE ${longIdentifier} (id INT)`
333
334 const startTime = Date.now()
335 const results = sqlEventParser.getTableEvents(sql)
336 const duration = Date.now() - startTime
337
338 expect(duration).toBeLessThan(100)
339 expect(results).toHaveLength(1)
340 expect(results[0]).toEqual({
341 type: TABLE_EVENT_ACTIONS.TableCreated,
342 schema: undefined,
343 tableName: longIdentifier,
344 })
345 })
346
347 it('handles nested dots in schema names without catastrophic backtracking', () => {
348 const maliciousInput = 'a.'.repeat(1000) + 'table'
349 const sql = `CREATE TABLE ${maliciousInput} (id INT)`
350
351 const startTime = Date.now()
352 const results = sqlEventParser.getTableEvents(sql)
353 const duration = Date.now() - startTime
354
355 expect(duration).toBeLessThan(100)
356 expect(results.length).toBeGreaterThan(0)
357 })
358
359 it('handles pathological SELECT INTO patterns', () => {
360 const maliciousSQL = 'SELECT ' + 'a '.repeat(1000) + 'INTO table FROM users'
361
362 const startTime = Date.now()
363 const results = sqlEventParser.getTableEvents(maliciousSQL)
364 const duration = Date.now() - startTime
365
366 expect(duration).toBeLessThan(100)
367 expect(results).toHaveLength(1)
368 expect(results[0]).toEqual({
369 type: TABLE_EVENT_ACTIONS.TableCreated,
370 schema: undefined,
371 tableName: 'table',
372 })
373 })
374
375 it('handles ALTER TABLE with many operations between', () => {
376 const manyOperations = 'ADD COLUMN test INT, '.repeat(100)
377 const sql = `ALTER TABLE users ${manyOperations} ENABLE ROW LEVEL SECURITY`
378
379 const startTime = Date.now()
380 const results = sqlEventParser.getTableEvents(sql)
381 const duration = Date.now() - startTime
382
383 expect(duration).toBeLessThan(100)
384 expect(results).toHaveLength(1)
385 expect(results[0]).toEqual({
386 type: TABLE_EVENT_ACTIONS.TableRLSEnabled,
387 schema: undefined,
388 tableName: 'users',
389 })
390 })
391
392 it('handles mixed quotes and backticks efficiently', () => {
393 const mixedQuotes = '`"`.'.repeat(100) + 'tablename'
394 const sql = `CREATE TABLE ${mixedQuotes} (id INT)`
395
396 const startTime = Date.now()
397 sqlEventParser.getTableEvents(sql)
398 const duration = Date.now() - startTime
399
400 expect(duration).toBeLessThan(100)
401 })
402 })
403
404 describe('Edge cases and special characters', () => {
405 it('handles Unicode identifiers', () => {
406 const sql = 'CREATE TABLE 用户表 (id INT)'
407 const results = sqlEventParser.getTableEvents(sql)
408 expect(results).toHaveLength(0)
409 })
410
411 it('handles identifiers with numbers', () => {
412 const sql = 'CREATE TABLE table123 (id INT)'
413 const results = sqlEventParser.getTableEvents(sql)
414 expect(results).toHaveLength(1)
415 expect(results[0]).toEqual({
416 type: TABLE_EVENT_ACTIONS.TableCreated,
417 schema: undefined,
418 tableName: 'table123',
419 })
420 })
421
422 it('handles identifiers with underscores', () => {
423 const sql = 'CREATE TABLE user_accounts (id INT)'
424 const results = sqlEventParser.getTableEvents(sql)
425 expect(results).toHaveLength(1)
426 expect(results[0]).toEqual({
427 type: TABLE_EVENT_ACTIONS.TableCreated,
428 schema: undefined,
429 tableName: 'user_accounts',
430 })
431 })
432
433 it('handles escaped quotes in identifiers', () => {
434 const sql = 'CREATE TABLE "user""table" (id INT)'
435 const results = sqlEventParser.getTableEvents(sql)
436 expect(results).toHaveLength(1)
437 expect(results[0]).toEqual({
438 type: TABLE_EVENT_ACTIONS.TableCreated,
439 schema: undefined,
440 tableName: 'usertable',
441 })
442 })
443
444 it('does not scan inside dollar-quoted string literals', () => {
445 // $$...$$ is a string literal in Postgres — its contents must not be
446 // parsed as DDL, otherwise a user inserting SQL-shaped text into a log
447 // table would trigger false-positive table-created events.
448 const sql = `
449 CREATE TABLE users (id INT);
450 INSERT INTO logs VALUES ($$CREATE TABLE fake$$);
451 INSERT INTO users VALUES (1);
452 `
453 const results = sqlEventParser.getTableEvents(sql)
454 expect(results).toHaveLength(3)
455 expect(results[0]).toMatchObject({
456 type: TABLE_EVENT_ACTIONS.TableCreated,
457 tableName: 'users',
458 })
459 expect(results[1]).toMatchObject({
460 type: TABLE_EVENT_ACTIONS.TableDataAdded,
461 tableName: 'logs',
462 })
463 expect(results[2]).toMatchObject({
464 type: TABLE_EVENT_ACTIONS.TableDataAdded,
465 tableName: 'users',
466 })
467 })
468
469 it('does not treat SELECT..INTO inside a plpgsql body as table creation', () => {
470 // Regression for the RLS warning modal false-positive: variable
471 // assignment inside a function body must not be reported as a new table.
472 const sql = `
473 create or replace function schema_checks()
474 returns jsonb
475 language plpgsql
476 as $$
477 declare
478 ret jsonb;
479 begin
480 select jsonb_build_object('value', 'ok') into ret;
481 return ret;
482 end;
483 $$;
484 `
485 const results = sqlEventParser.getTableEvents(sql)
486 expect(results).toEqual([])
487 })
488
489 it('does not leak nested dollar-quoted dynamic SQL through statement splitting', () => {
490 // Regression: an inner $sql$...$sql$ tag used inside an outer $fn$...$fn$
491 // body was pairing with the outer opening tag (the splitStatements regex
492 // doesn't enforce matching tags), which caused the inner semicolon to
493 // split the statement and exposed `create table fake` to the detectors.
494 // The fix is that stripDollarQuoteBodies runs before splitStatements and
495 // uses a backreference to require matching tags.
496 const sql = `
497 create function f()
498 returns void
499 language plpgsql
500 as $fn$
501 begin
502 execute $sql$create table fake(id int);$sql$;
503 end;
504 $fn$;
505 `
506 const results = sqlEventParser.getTableEvents(sql)
507 expect(results).toEqual([])
508 })
509
510 it('still detects a real top-level CREATE TABLE next to a function with nested dollar tags', () => {
511 const sql = `
512 create table public.real_table(id int);
513 create function f()
514 returns void
515 language plpgsql
516 as $fn$
517 begin
518 execute $sql$create table fake(id int);$sql$;
519 end;
520 $fn$;
521 `
522 const results = sqlEventParser.getTableEvents(sql)
523 expect(results).toEqual([
524 {
525 type: TABLE_EVENT_ACTIONS.TableCreated,
526 schema: 'public',
527 tableName: 'real_table',
528 },
529 ])
530 })
531
532 it('handles SQL injection attempts safely', () => {
533 const sql = "CREATE TABLE users'; DROP TABLE users; -- (id INT)"
534 const results = sqlEventParser.getTableEvents(sql)
535 expect(results).toHaveLength(1)
536 expect(results[0]).toEqual({
537 type: TABLE_EVENT_ACTIONS.TableCreated,
538 schema: undefined,
539 tableName: 'users',
540 })
541 })
542 })
543
544 describe('getTableEvents', () => {
545 it('filters only table-related events', () => {
546 const sql = `
547 CREATE TABLE users (id INT);
548 CREATE FUNCTION test() RETURNS INT AS $$ BEGIN RETURN 1; END; $$ LANGUAGE plpgsql;
549 INSERT INTO users (id) VALUES (1);
550 ALTER TABLE users ENABLE RLS;
551 CREATE VIEW user_view AS SELECT * FROM users;
552 `
553 const results = sqlEventParser.getTableEvents(sql)
554 expect(results).toHaveLength(3)
555 expect(results.map((r) => r.type)).toEqual([
556 TABLE_EVENT_ACTIONS.TableCreated,
557 TABLE_EVENT_ACTIONS.TableDataAdded,
558 TABLE_EVENT_ACTIONS.TableRLSEnabled,
559 ])
560 })
561
562 it('returns empty array for non-table SQL', () => {
563 const sql = `
564 CREATE FUNCTION test() RETURNS INT AS $$ BEGIN RETURN 1; END; $$ LANGUAGE plpgsql;
565 CREATE VIEW user_view AS SELECT * FROM users;
566 SELECT * FROM users;
567 `
568 const results = sqlEventParser.getTableEvents(sql)
569 expect(results).toHaveLength(0)
570 })
571 })
572})