konnosDashboard
sql-identifier-quoting.test.ts264 lines · main
1import { parse } from 'libpg-query'
2import { describe, expect, it } from 'vitest'
3
4import { extractIdentifiers, isQuotedInSql, needsQuoting } from './sql-identifier-quoting'
5
6describe('needsQuoting', () => {
7 describe('reserved keywords', () => {
8 it('returns true for reserved keywords (uppercase)', () => {
9 expect(needsQuoting('SELECT')).toBe(true)
10 expect(needsQuoting('FROM')).toBe(true)
11 expect(needsQuoting('TABLE')).toBe(true)
12 expect(needsQuoting('ORDER')).toBe(true)
13 expect(needsQuoting('GROUP')).toBe(true)
14 expect(needsQuoting('CREATE')).toBe(true)
15 expect(needsQuoting('INSERT')).toBe(true)
16 expect(needsQuoting('UPDATE')).toBe(true)
17 expect(needsQuoting('DELETE')).toBe(true)
18 })
19
20 it('returns true for reserved keywords (lowercase)', () => {
21 expect(needsQuoting('select')).toBe(true)
22 expect(needsQuoting('from')).toBe(true)
23 expect(needsQuoting('table')).toBe(true)
24 expect(needsQuoting('order')).toBe(true)
25 })
26
27 it('returns true for reserved keywords (mixed case)', () => {
28 expect(needsQuoting('Select')).toBe(true)
29 expect(needsQuoting('From')).toBe(true)
30 expect(needsQuoting('Table')).toBe(true)
31 })
32 })
33
34 describe('mixed case identifiers', () => {
35 it('returns true for identifiers with uppercase letters', () => {
36 expect(needsQuoting('MyTable')).toBe(true)
37 expect(needsQuoting('UserID')).toBe(true)
38 expect(needsQuoting('camelCase')).toBe(true)
39 expect(needsQuoting('PascalCase')).toBe(true)
40 })
41 })
42
43 describe('special characters', () => {
44 it('returns true for identifiers with dashes', () => {
45 expect(needsQuoting('my-table')).toBe(true)
46 expect(needsQuoting('user-name')).toBe(true)
47 })
48
49 it('returns true for identifiers with dots', () => {
50 expect(needsQuoting('table.name')).toBe(true)
51 expect(needsQuoting('schema.table')).toBe(true)
52 })
53
54 it('returns true for identifiers with spaces', () => {
55 expect(needsQuoting('column with spaces')).toBe(true)
56 expect(needsQuoting('my table')).toBe(true)
57 })
58
59 it('returns true for identifiers starting with numbers', () => {
60 expect(needsQuoting('123table')).toBe(true)
61 expect(needsQuoting('1col')).toBe(true)
62 })
63 })
64
65 describe('valid unquoted identifiers', () => {
66 it('returns false for lowercase identifiers', () => {
67 expect(needsQuoting('users')).toBe(false)
68 expect(needsQuoting('user_id')).toBe(false)
69 expect(needsQuoting('orders')).toBe(false)
70 })
71
72 it('returns false for identifiers starting with underscore', () => {
73 expect(needsQuoting('_private')).toBe(false)
74 expect(needsQuoting('_internal')).toBe(false)
75 })
76
77 it('returns false for identifiers with numbers', () => {
78 expect(needsQuoting('col1')).toBe(false)
79 expect(needsQuoting('user123')).toBe(false)
80 })
81
82 it('returns false for identifiers with dollar sign in middle', () => {
83 expect(needsQuoting('col$name')).toBe(false)
84 })
85
86 it('returns true for identifiers starting with dollar sign', () => {
87 expect(needsQuoting('$variable')).toBe(true)
88 })
89 })
90
91 describe('edge cases', () => {
92 it('handles empty string', () => {
93 expect(needsQuoting('')).toBe(true)
94 })
95
96 it('handles single character identifiers', () => {
97 expect(needsQuoting('a')).toBe(false)
98 expect(needsQuoting('A')).toBe(true)
99 expect(needsQuoting('_')).toBe(false)
100 })
101 })
102})
103
104describe('isQuotedInSql', () => {
105 describe('simple quoted identifiers', () => {
106 it('returns true when identifier is quoted', () => {
107 expect(isQuotedInSql('SELECT * FROM "MyTable"', 'MyTable')).toBe(true)
108 expect(isQuotedInSql('SELECT "MyColumn" FROM users', 'MyColumn')).toBe(true)
109 expect(isQuotedInSql('INSERT INTO "users" VALUES (1)', 'users')).toBe(true)
110 })
111
112 it('returns false when identifier is not quoted', () => {
113 expect(isQuotedInSql('SELECT * FROM users', 'users')).toBe(false)
114 expect(isQuotedInSql('SELECT id FROM orders', 'id')).toBe(false)
115 expect(isQuotedInSql('INSERT INTO users VALUES (1)', 'users')).toBe(false)
116 })
117 })
118
119 describe('case-insensitive matching', () => {
120 it('matches quoted identifier regardless of case', () => {
121 expect(isQuotedInSql('SELECT * FROM "mytable"', 'MyTable')).toBe(true)
122 expect(isQuotedInSql('SELECT * FROM "MYTABLE"', 'mytable')).toBe(true)
123 expect(isQuotedInSql('SELECT * FROM "MyTable"', 'MYTABLE')).toBe(true)
124 })
125 })
126
127 describe('escaped quotes', () => {
128 it('handles identifiers with escaped quotes inside', () => {
129 expect(isQuotedInSql('SELECT * FROM "My""Table"', 'My"Table')).toBe(true)
130 expect(isQuotedInSql('SELECT "col""name" FROM users', 'col"name')).toBe(true)
131 })
132
133 it('handles multiple escaped quotes', () => {
134 expect(isQuotedInSql('SELECT "a""b""c" FROM users', 'a"b"c')).toBe(true)
135 })
136 })
137
138 describe('special regex characters', () => {
139 it('handles identifiers with dots', () => {
140 expect(isQuotedInSql('SELECT * FROM "table.name"', 'table.name')).toBe(true)
141 expect(isQuotedInSql('SELECT * FROM "schema.table"', 'schema.table')).toBe(true)
142 })
143
144 it('handles identifiers with parentheses', () => {
145 expect(isQuotedInSql('SELECT * FROM "col(value)"', 'col(value)')).toBe(true)
146 })
147
148 it('handles identifiers with brackets', () => {
149 expect(isQuotedInSql('SELECT * FROM "table[0]"', 'table[0]')).toBe(true)
150 })
151
152 it('handles identifiers with special regex chars', () => {
153 expect(isQuotedInSql('SELECT * FROM "col*name"', 'col*name')).toBe(true)
154 expect(isQuotedInSql('SELECT * FROM "col+name"', 'col+name')).toBe(true)
155 expect(isQuotedInSql('SELECT * FROM "col?name"', 'col?name')).toBe(true)
156 expect(isQuotedInSql('SELECT * FROM "col^name"', 'col^name')).toBe(true)
157 expect(isQuotedInSql('SELECT * FROM "col$name"', 'col$name')).toBe(true)
158 })
159 })
160
161 describe('multiple occurrences', () => {
162 it('returns true if identifier appears quoted anywhere', () => {
163 expect(isQuotedInSql('SELECT * FROM "MyTable" WHERE id = 1', 'MyTable')).toBe(true)
164 expect(isQuotedInSql('SELECT users.id FROM "users"', 'users')).toBe(true)
165 })
166
167 it('returns true even if also appears unquoted', () => {
168 expect(isQuotedInSql('SELECT * FROM "MyTable" JOIN MyTable ON 1=1', 'MyTable')).toBe(true)
169 })
170 })
171
172 describe('partial matches', () => {
173 it('does not match partial identifiers', () => {
174 expect(isQuotedInSql('SELECT * FROM "MyTableX"', 'MyTable')).toBe(false)
175 expect(isQuotedInSql('SELECT * FROM "XMyTable"', 'MyTable')).toBe(false)
176 })
177
178 it('matches exact identifier only', () => {
179 expect(isQuotedInSql('SELECT * FROM "MyTable"', 'MyTable')).toBe(true)
180 expect(isQuotedInSql('SELECT * FROM "MyTable"', 'Table')).toBe(false)
181 })
182 })
183
184 describe('edge cases', () => {
185 it('handles empty identifier', () => {
186 expect(isQuotedInSql('SELECT * FROM ""', '')).toBe(true)
187 expect(isQuotedInSql('SELECT * FROM users', '')).toBe(false)
188 })
189
190 it('handles SQL with comments', () => {
191 expect(isQuotedInSql('SELECT * FROM "MyTable" -- comment', 'MyTable')).toBe(true)
192 expect(isQuotedInSql('/* comment */ SELECT * FROM "MyTable"', 'MyTable')).toBe(true)
193 })
194
195 it('handles SQL with string literals', () => {
196 expect(isQuotedInSql('SELECT * FROM "MyTable" WHERE name = \'test\'', 'MyTable')).toBe(true)
197 expect(
198 isQuotedInSql('SELECT "MyTable" FROM users WHERE name = "not a table"', 'MyTable')
199 ).toBe(true)
200 })
201 })
202})
203
204describe('extractIdentifiers', () => {
205 it('extracts table and column names from a simple SELECT', async () => {
206 const identifiers = extractIdentifiers(await parse('SELECT id, name FROM users'))
207 expect(identifiers).toEqual(expect.arrayContaining(['users', 'id', 'name']))
208 })
209
210 it('extracts schema-qualified table names', async () => {
211 const identifiers = extractIdentifiers(await parse('SELECT id FROM public.users'))
212 expect(identifiers).toEqual(expect.arrayContaining(['public', 'users', 'id']))
213 })
214
215 it('preserves case for quoted identifiers', async () => {
216 const identifiers = extractIdentifiers(await parse('SELECT "MyColumn" FROM "MyTable"'))
217 expect(identifiers).toEqual(expect.arrayContaining(['MyColumn', 'MyTable']))
218 })
219
220 it('lowercases unquoted identifiers with mixed case', async () => {
221 const identifiers = extractIdentifiers(await parse('SELECT MyColumn FROM MyTable'))
222 expect(identifiers).toEqual(expect.arrayContaining(['mycolumn', 'mytable']))
223 })
224
225 it('extracts identifiers from JOINs', async () => {
226 const identifiers = extractIdentifiers(
227 await parse('SELECT u.id, o.total FROM users u JOIN orders o ON u.id = o.user_id')
228 )
229 expect(identifiers).toEqual(
230 expect.arrayContaining(['users', 'orders', 'id', 'total', 'user_id'])
231 )
232 })
233
234 it('extracts identifiers from subqueries', async () => {
235 const identifiers = extractIdentifiers(
236 await parse('SELECT * FROM (SELECT id FROM inner_table) AS sub')
237 )
238 expect(identifiers).toEqual(expect.arrayContaining(['inner_table', 'id']))
239 })
240
241 it('handles INSERT statements', async () => {
242 const identifiers = extractIdentifiers(
243 await parse('INSERT INTO users (name, email) VALUES ($1, $2)')
244 )
245 expect(identifiers).toEqual(expect.arrayContaining(['users', 'name', 'email']))
246 })
247
248 it('handles UPDATE statements', async () => {
249 const identifiers = extractIdentifiers(await parse('UPDATE users SET name = $1 WHERE id = $2'))
250 expect(identifiers).toEqual(expect.arrayContaining(['users', 'name', 'id']))
251 })
252
253 it('handles CREATE TABLE statements', async () => {
254 const identifiers = extractIdentifiers(
255 await parse('CREATE TABLE "MyTable" ("MyColumn" TEXT, other_col INT)')
256 )
257 expect(identifiers).toEqual(expect.arrayContaining(['MyTable', 'MyColumn', 'other_col']))
258 })
259
260 it('returns empty array when no identifiers exist', async () => {
261 const identifiers = extractIdentifiers(await parse('SELECT 1'))
262 expect(identifiers).toEqual([])
263 })
264})