konnosDashboard
studio.test.ts98 lines · main
1import { describe, expect, test } from 'bun:test';
2
3import { ValidationError } from '@briven/shared';
4
5import { buildFilterClauses, FILTER_OPS } from './studio.js';
6
7// NOTE (sprint plan S0.4): this file previously asserted MySQL output
8// (backtick quoting + `?` placeholders) while the shipped code emits
9// Postgres (`"col"` + `$n`). A test in the wrong dialect can never catch a
10// real DoltGres break — it shows green regardless. Rewritten to match the
11// actual Postgres/DoltGres SQL `buildFilterClauses` produces.
12
13const COLS = new Set(['id', 'name', 'created_at', 'score']);
14
15describe('buildFilterClauses — Postgres/DoltGres', () => {
16 test('eq renders parameterised equality with double-quote identifier + $n', () => {
17 const { clauses, params } = buildFilterClauses(
18 [{ column: 'name', op: 'eq', value: 'alice' }],
19 COLS,
20 'users',
21 );
22 expect(clauses).toEqual(['"name" = $1']);
23 expect(params).toEqual(['alice']);
24 });
25
26 test('contains renders case-insensitive substring match — no client pattern smuggling', () => {
27 const { clauses, params } = buildFilterClauses(
28 [{ column: 'name', op: 'contains', value: '%admin%' }],
29 COLS,
30 'users',
31 );
32 // The `%` are glued in SQL around the parameter, so a caller writing
33 // `%admin%` cannot smuggle pattern characters — the literal value is bound.
34 // DoltGres has no ILIKE, so we use lower()+LIKE for case-insensitive match.
35 expect(clauses).toEqual([`lower("name"::text) LIKE '%' || lower($1) || '%'`]);
36 expect(params).toEqual(['%admin%']);
37 });
38
39 test.each(FILTER_OPS.filter((op) => op !== 'eq' && op !== 'contains'))(
40 'comparison op %s renders the right operator',
41 (op) => {
42 const { clauses, params } = buildFilterClauses(
43 [{ column: 'score', op, value: 42 }],
44 COLS,
45 'leaderboard',
46 );
47 const symbol = { gt: '>', lt: '<', gte: '>=', lte: '<=' }[op as 'gt' | 'lt' | 'gte' | 'lte'];
48 expect(clauses).toEqual([`"score" ${symbol} $1`]);
49 expect(params).toEqual([42]);
50 },
51 );
52
53 test('multiple filters share a params array with incrementing $n placeholders', () => {
54 const { clauses, params } = buildFilterClauses(
55 [
56 { column: 'name', op: 'eq', value: 'alice' },
57 { column: 'score', op: 'gt', value: 100 },
58 ],
59 COLS,
60 'users',
61 );
62 expect(clauses).toEqual(['"name" = $1', '"score" > $2']);
63 expect(params).toEqual(['alice', 100]);
64 });
65
66 test('unknown column → ValidationError (not 500)', () => {
67 expect(() =>
68 buildFilterClauses([{ column: 'nonexistent', op: 'eq', value: 'x' }], COLS, 'users'),
69 ).toThrow(ValidationError);
70 });
71
72 test('invalid column name (SQL injection attempt) → ValidationError', () => {
73 expect(() =>
74 buildFilterClauses(
75 [{ column: 'name; DROP TABLE users', op: 'eq', value: 'x' }],
76 COLS,
77 'users',
78 ),
79 ).toThrow(ValidationError);
80 });
81
82 test('invalid operator → ValidationError', () => {
83 expect(() =>
84 buildFilterClauses([{ column: 'name', op: 'like' as never, value: 'x' }], COLS, 'users'),
85 ).toThrow(ValidationError);
86 });
87
88 test('SQL injection value does NOT appear in the clause text', () => {
89 const { clauses, params } = buildFilterClauses(
90 [{ column: 'name', op: 'eq', value: "'; DROP TABLE users; --" }],
91 COLS,
92 'users',
93 );
94 expect(clauses[0]).toBe('"name" = $1');
95 expect(clauses[0]).not.toContain('DROP');
96 expect(params).toEqual(["'; DROP TABLE users; --"]);
97 });
98});