konnosDashboard
pg-format.test.ts314 lines · main
1import { describe, expect, expectTypeOf, test } from 'vitest'
2
3import { ident, keyword, literal, safeSql } from '../src/pg-format'
4
5describe('pg-format', () => {
6 describe('ident', () => {
7 describe('reserved keywords', () => {
8 test('should quote "collation" reserved keyword', () => {
9 expect(ident('collation')).toBe('"collation"')
10 expect(ident('COLLATION')).toBe('"COLLATION"')
11 expect(ident('Collation')).toBe('"Collation"')
12 })
13
14 test('should quote other reserved keywords', () => {
15 expect(ident('select')).toBe('"select"')
16 expect(ident('from')).toBe('"from"')
17 expect(ident('where')).toBe('"where"')
18 expect(ident('order')).toBe('"order"')
19 expect(ident('group')).toBe('"group"')
20 expect(ident('table')).toBe('"table"')
21 expect(ident('column')).toBe('"column"')
22 expect(ident('create')).toBe('"create"')
23 expect(ident('insert')).toBe('"insert"')
24 expect(ident('update')).toBe('"update"')
25 expect(ident('delete')).toBe('"delete"')
26 })
27
28 test('should not quote non-reserved identifiers', () => {
29 expect(ident('normal_column')).toBe('normal_column')
30 expect(ident('my_table')).toBe('my_table')
31 expect(ident('user_id')).toBe('user_id')
32 expect(ident('_private')).toBe('_private')
33 expect(ident('col1')).toBe('col1')
34 })
35
36 test('should quote identifiers with special characters', () => {
37 expect(ident('column with spaces')).toBe('"column with spaces"')
38 expect(ident('column-with-dashes')).toBe('"column-with-dashes"')
39 expect(ident('column.with.dots')).toBe('"column.with.dots"')
40 expect(ident('column$with$dollar')).toBe('column$with$dollar')
41 })
42
43 test('should handle double quotes in identifiers', () => {
44 expect(ident('quoted"column')).toBe('"quoted""column"')
45 expect(ident('"already quoted"')).toBe('"""already quoted"""')
46 })
47
48 test('should handle camelCase identifiers', () => {
49 expect(ident('camelCaseColumn')).toBe('"camelCaseColumn"')
50 expect(ident('PascalCase')).toBe('"PascalCase"')
51 })
52
53 test('should handle identifiers starting with numbers', () => {
54 expect(ident('123column')).toBe('"123column"')
55 expect(ident('1st_column')).toBe('"1st_column"')
56 })
57 })
58
59 describe('edge cases', () => {
60 test('should throw error for null or undefined', () => {
61 expect(() => ident(null)).toThrow('SQL identifier cannot be null or undefined')
62 expect(() => ident(undefined)).toThrow('SQL identifier cannot be null or undefined')
63 })
64
65 test('should handle boolean values', () => {
66 expect(ident(true)).toBe('"t"')
67 expect(ident(false)).toBe('"f"')
68 })
69
70 test('should handle arrays', () => {
71 expect(ident(['col1', 'col2'])).toBe('col1,col2')
72 expect(ident(['collation', 'select'])).toBe('"collation","select"')
73 })
74
75 test('should throw error for nested arrays', () => {
76 expect(() => ident([['col1']])).toThrow(
77 'Nested array to grouped list conversion is not supported for SQL identifier'
78 )
79 })
80
81 test('should throw error for objects', () => {
82 expect(() => ident({ name: 'test' })).toThrow('SQL identifier cannot be an object')
83 })
84 })
85 })
86
87 describe('literal', () => {
88 test('should handle null and undefined', () => {
89 expect(literal(null)).toBe('NULL')
90 expect(literal(undefined)).toBe('NULL')
91 })
92
93 test('should handle strings', () => {
94 expect(literal('simple string')).toBe("'simple string'")
95 expect(literal("string with 'quotes'")).toBe("'string with ''quotes'''")
96 expect(literal('string with "double quotes"')).toBe('\'string with "double quotes"\'')
97 })
98
99 test('should handle numbers', () => {
100 expect(literal(123)).toBe('123')
101 expect(literal(0)).toBe('0')
102 expect(literal(-42)).toBe('-42')
103 expect(literal(3.14)).toBe('3.14')
104 })
105
106 test('should handle booleans', () => {
107 expect(literal(true)).toBe("'t'")
108 expect(literal(false)).toBe("'f'")
109 })
110
111 test('should handle special number values', () => {
112 expect(literal(Number.POSITIVE_INFINITY)).toBe("'Infinity'")
113 expect(literal(Number.NEGATIVE_INFINITY)).toBe("'-Infinity'")
114 expect(literal(Number.NaN)).toBe("'NaN'")
115 })
116
117 test('should handle bigint', () => {
118 expect(literal(BigInt(123))).toBe('123')
119 expect(literal(BigInt('9007199254740991'))).toBe('9007199254740991')
120 })
121
122 test('should handle dates', () => {
123 const date = new Date('2024-01-01T00:00:00Z')
124 expect(literal(date)).toBe("'2024-01-01 00:00:00.000+00'")
125 })
126
127 test('should handle arrays', () => {
128 expect(literal([1, 2, 3])).toBe('1,2,3')
129 expect(literal(['a', 'b', 'c'])).toBe("'a','b','c'")
130 })
131
132 test('should handle objects as JSON', () => {
133 expect(literal({ name: 'test' })).toBe('\'{"name":"test"}\'::jsonb')
134 expect(literal({ id: 1, name: 'test' })).toBe('\'{"id":1,"name":"test"}\'::jsonb')
135 })
136
137 test('should handle strings with backslashes', () => {
138 expect(literal('path\\to\\file')).toBe("E'path\\\\to\\\\file'")
139 expect(literal('C:\\Users\\test')).toBe("E'C:\\\\Users\\\\test'")
140 })
141 })
142
143 describe('keyword', () => {
144 test('accepts single uppercase words', () => {
145 expect(keyword('BEFORE')).toBe('BEFORE')
146 expect(keyword('AFTER')).toBe('AFTER')
147 expect(keyword('ROW')).toBe('ROW')
148 })
149
150 test('accepts allow-listed multi-word keywords', () => {
151 expect(keyword('INSTEAD OF')).toBe('INSTEAD OF')
152 expect(keyword('BY DEFAULT')).toBe('BY DEFAULT')
153 })
154
155 test('multi-word allow-list is case-insensitive', () => {
156 expect(keyword('instead of')).toBe('instead of')
157 expect(keyword('By Default')).toBe('By Default')
158 })
159
160 test('accepts words with underscores and digits', () => {
161 expect(keyword('EACH_ROW')).toBe('EACH_ROW')
162 expect(keyword('col2')).toBe('col2')
163 })
164
165 test('rejects empty string', () => {
166 expect(() => keyword('')).toThrow('Not a valid keyword')
167 })
168
169 test('rejects strings starting with a digit', () => {
170 expect(() => keyword('1BEFORE')).toThrow('Not a valid keyword')
171 })
172
173 test('rejects strings with semicolons', () => {
174 expect(() => keyword('BEFORE;')).toThrow('Not a valid keyword')
175 })
176
177 test('rejects strings with dashes', () => {
178 expect(() => keyword('BE-FORE')).toThrow('Not a valid keyword')
179 })
180
181 test('rejects strings with single quotes', () => {
182 expect(() => keyword("BE'FORE")).toThrow('Not a valid keyword')
183 })
184
185 test('rejects strings with parentheses', () => {
186 expect(() => keyword('fn()')).toThrow('Not a valid keyword')
187 })
188
189 test('rejects arbitrary multi-word phrases not on the allow-list', () => {
190 expect(() => keyword('DROP TABLE')).toThrow('Not a valid keyword')
191 expect(() => keyword('DELETE FROM users')).toThrow('Not a valid keyword')
192 expect(() => keyword('Each Row')).toThrow('Not a valid keyword')
193 })
194 })
195
196 describe('safeSql', () => {
197 test('returns a plain string when there are no interpolations', () => {
198 const result = safeSql`SELECT 1`
199 expect(result).toBe('SELECT 1')
200 })
201
202 test('interpolates ident values', () => {
203 const table = ident('my_table')
204 const result = safeSql`SELECT * FROM ${table}`
205 expect(result).toBe('SELECT * FROM my_table')
206 })
207
208 test('interpolates literal values', () => {
209 const value = literal('hello')
210 const result = safeSql`SELECT ${value}`
211 expect(result).toBe("SELECT 'hello'")
212 })
213
214 test('interpolates multiple values', () => {
215 const table = ident('users')
216 const col = ident('email')
217 const val = literal('test@example.com')
218 const result = safeSql`SELECT ${col} FROM ${table} WHERE ${col} = ${val}`
219 expect(result).toBe(`SELECT email FROM users WHERE email = 'test@example.com'`)
220 })
221
222 test('handles ident with special characters', () => {
223 const table = ident('my "table"')
224 const result = safeSql`SELECT * FROM ${table}`
225 expect(result).toBe('SELECT * FROM "my ""table"""')
226 })
227
228 test('literal: escapes classic quote bypass', () => {
229 const val = literal("' OR '1'='1")
230 const result = safeSql`SELECT * FROM users WHERE password = ${val}`
231 expect(result).toBe("SELECT * FROM users WHERE password = ''' OR ''1''=''1'")
232 })
233
234 test('literal: escapes UNION SELECT attack', () => {
235 const val = literal("x' UNION SELECT username, password FROM admins --")
236 const result = safeSql`SELECT name FROM products WHERE id = ${val}`
237 expect(result).toBe(
238 "SELECT name FROM products WHERE id = 'x'' UNION SELECT username, password FROM admins --'"
239 )
240 })
241
242 test('literal: escapes stacked query injection', () => {
243 const val = literal("1'; DROP TABLE users; --")
244 const result = safeSql`SELECT * FROM users WHERE id = ${val}`
245 expect(result).toBe("SELECT * FROM users WHERE id = '1''; DROP TABLE users; --'")
246 })
247
248 test('literal: escapes comment-based injection', () => {
249 const val = literal("admin'--")
250 const result = safeSql`SELECT * FROM users WHERE username = ${val}`
251 expect(result).toBe("SELECT * FROM users WHERE username = 'admin''--'")
252 })
253
254 test('ident: escapes SQL keyword injection in table name', () => {
255 const table = ident('users WHERE 1=1 --')
256 const result = safeSql`SELECT * FROM ${table}`
257 expect(result).toBe('SELECT * FROM "users WHERE 1=1 --"')
258 })
259
260 test('ident: escapes stacked query injection in column name', () => {
261 const col = ident('id; DROP TABLE users')
262 const result = safeSql`SELECT ${col} FROM users`
263 expect(result).toBe('SELECT "id; DROP TABLE users" FROM users')
264 })
265
266 test('handles literal with null', () => {
267 const val = literal(null)
268 const result = safeSql`SELECT ${val}`
269 expect(result).toBe('SELECT NULL')
270 })
271
272 test('handles literal with numbers', () => {
273 const val = literal(42)
274 const result = safeSql`SELECT ${val}`
275 expect(result).toBe('SELECT 42')
276 })
277
278 test('can be nested', () => {
279 const col = ident('id')
280 const inner = safeSql`SELECT ${col} FROM ${ident('items')}`
281 const outer = safeSql`WITH cte AS (${inner}) SELECT * FROM cte`
282 expect(outer).toBe('WITH cte AS (SELECT id FROM items) SELECT * FROM cte')
283 })
284 })
285
286 describe('safeSql type safety', () => {
287 test('rejects a plain string interpolation', () => {
288 const unsafeValue = 'malicious'
289 // @ts-expect-error plain string is not a SafeSqlFragment
290 safeSql`SELECT * FROM ${unsafeValue}`
291 })
292
293 test('rejects a number interpolation', () => {
294 // @ts-expect-error number is not a SafeSqlFragment
295 safeSql`SELECT * FROM users LIMIT ${10}`
296 })
297
298 test('rejects an object interpolation', () => {
299 const obj = { table: 'users' }
300 // @ts-expect-error object is not a SafeSqlFragment
301 safeSql`SELECT * FROM ${obj}`
302 })
303
304 test('accepts SafeSqlFragment from ident', () => {
305 const result = safeSql`SELECT * FROM ${ident('users')}`
306 expectTypeOf(result).toBeString
307 })
308
309 test('accepts SafeSqlFragment from literal', () => {
310 const result = safeSql`SELECT ${literal('value')}`
311 expectTypeOf(result).toBeString
312 })
313 })
314})