0
# Authority and Security
1

2
Table and column access analysis with whitelist validation for security and authorization checking. This functionality helps enforce access controls and analyze SQL queries for security compliance.
3

4
## Capabilities
5

6
### Table List Extraction
7

8
Extract all tables accessed by a SQL query with operation type information.
9

10
```javascript { .api }
11
/**
12
 * Extract list of tables from SQL query
13
 * @param sql - SQL string to analyze
14
 * @param opt - Optional configuration object
15
 * @returns Array of table names with authority format
16
 */
17
tableList(sql: string, opt?: Option): string[];
18
```
19

20
**Authority Format**: `{type}::{dbName}::{tableName}`
21

22
Where:
23
- `type` is the operation type: `select`, `update`, `delete`, `insert`
24
- `dbName` is the database name (or `null` if not specified)
25
- `tableName` is the table name
26

27
**Usage Examples:**
28

29
```javascript
30
const { Parser } = require('node-sql-parser');
31
const parser = new Parser();
32

33
// Simple table access
34
const tables1 = parser.tableList('SELECT * FROM users');
35
console.log(tables1); // ["select::null::users"]
36

37
// Multiple tables with joins
38
const tables2 = parser.tableList(`
39
  SELECT u.name, p.title 
40
  FROM users u 
41
  JOIN posts p ON u.id = p.user_id
42
`);
43
console.log(tables2); // ["select::null::users", "select::null::posts"]
44

45
// Database-qualified table names
46
const tables3 = parser.tableList('SELECT * FROM mydb.users');
47
console.log(tables3); // ["select::mydb::users"]
48

49
// Mixed operations
50
const tables4 = parser.tableList(`
51
  UPDATE orders SET status = 'shipped' 
52
  WHERE user_id IN (SELECT id FROM users WHERE active = 1)
53
`);
54
console.log(tables4); // ["update::null::orders", "select::null::users"]
55

56
// Schema-qualified names (e.g., PostgreSQL)
57
const tables5 = parser.tableList('SELECT * FROM public.users', { database: 'PostgreSQL' });
58
console.log(tables5); // ["select::public::users"]
59

60
// Server.database.schema.table (TransactSQL)
61
const tables6 = parser.tableList(
62
  'SELECT * FROM server1.mydb.dbo.users', 
63
  { database: 'TransactSQL' }
64
);
65
console.log(tables6); // ["select::server1.mydb.dbo::users"]
66
```
67

68
### Column List Extraction
69

70
Extract all columns accessed by a SQL query with operation and table context.
71

72
```javascript { .api }
73
/**
74
 * Extract list of columns from SQL query
75
 * @param sql - SQL string to analyze  
76
 * @param opt - Optional configuration object
77
 * @returns Array of column names with authority format
78
 */
79
columnList(sql: string, opt?: Option): string[];
80
```
81

82
**Authority Format**: `{type}::{tableName}::{columnName}`
83

84
Where:
85
- `type` is the operation type: `select`, `update`, `delete`, `insert`
86
- `tableName` is the table name (or `null` if not determinable)
87
- `columnName` is the column name
88

89
**Special Cases:**
90
- For `SELECT *`, `DELETE`, and `INSERT INTO table VALUES()` without specified columns, the `.*` regex pattern is used
91

92
**Usage Examples:**
93

94
```javascript
95
// Specific column access
96
const columns1 = parser.columnList('SELECT name, email FROM users');
97
console.log(columns1); // ["select::users::name", "select::users::email"]
98

99
// Qualified column names
100
const columns2 = parser.columnList('SELECT u.name, p.title FROM users u JOIN posts p ON u.id = p.user_id');
101
console.log(columns2); 
102
// ["select::u::name", "select::p::title", "select::u::id", "select::p::user_id"]
103

104
// WHERE clause columns
105
const columns3 = parser.columnList('SELECT name FROM users WHERE age > 18 AND status = "active"');
106
console.log(columns3); 
107
// ["select::users::name", "select::users::age", "select::users::status"]
108

109
// UPDATE operations
110
const columns4 = parser.columnList('UPDATE users SET email = "new@email.com" WHERE id = 1');
111
console.log(columns4); // ["update::users::email", "update::users::id"]
112

113
// Wildcard selections (require .* authority)
114
const columns5 = parser.columnList('SELECT * FROM users');
115
console.log(columns5); // ["select::users::.*"] 
116

117
// Complex expressions
118
const columns6 = parser.columnList('SELECT COUNT(id), MAX(created_at) FROM orders GROUP BY user_id');
119
console.log(columns6); 
120
// ["select::orders::id", "select::orders::created_at", "select::orders::user_id"]
121
```
122

123
### Whitelist Authorization
124

125
Check SQL queries against whitelist patterns to enforce access control policies.
126

127
```javascript { .api }
128
/**
129
 * Check SQL against whitelist authority patterns
130
 * @param sql - SQL string to check
131
 * @param whiteList - Array of allowed patterns (regex strings)
132
 * @param opt - Optional configuration object
133
 * @returns Error if check fails, undefined if passes
134
 * @throws Error with detailed message if unauthorized access detected
135
 */
136
whiteListCheck(sql: string, whiteList: string[], opt?: Option): Error | undefined;
137

138
type WhilteListCheckMode = "table" | "column";
139

140
interface Option {
141
  database?: string;
142
  type?: WhilteListCheckMode; // Default: "table"
143
}
144
```
145

146
**Usage Examples:**
147

148
```javascript
149
// Table whitelist checking (default mode)
150
const tableWhitelist = [
151
  'select::(.*)::(users|posts)',     // Allow SELECT on users and posts tables
152
  'update::(.*)::users',             // Allow UPDATE on users table
153
  'insert::(.*)::posts'              // Allow INSERT on posts table
154
];
155

156
try {
157
  // This passes - SELECT on users is allowed
158
  parser.whiteListCheck(
159
    'SELECT * FROM users', 
160
    tableWhitelist
161
  );
162
  
163
  // This fails - DELETE is not in whitelist
164
  parser.whiteListCheck(
165
    'DELETE FROM users WHERE id = 1', 
166
    tableWhitelist
167
  );
168
} catch (error) {
169
  console.error('Access denied:', error.message);
170
  // "authority = 'delete::null::users' is required in table whiteList to execute SQL = 'DELETE FROM users WHERE id = 1'"
171
}
172

173
// Column whitelist checking
174
const columnWhitelist = [
175
  'select::users::(name|email)',     // Allow SELECT on name and email columns
176
  'select::users::.*',               // Allow wildcard SELECT on users
177
  'update::users::email'             // Allow UPDATE on email column only
178
];
179

180
try {
181
  // This passes - accessing allowed columns
182
  parser.whiteListCheck(
183
    'SELECT name, email FROM users',
184
    columnWhitelist,
185
    { type: 'column' }
186
  );
187
  
188
  // This fails - password column not allowed
189
  parser.whiteListCheck(
190
    'SELECT name, password FROM users',
191
    columnWhitelist,
192
    { type: 'column' }
193
  );
194
} catch (error) {
195
  console.error('Column access denied:', error.message);
196
}
197

198
// Database-specific whitelist
199
const pgWhitelist = ['select::public::(users|posts)'];
200

201
parser.whiteListCheck(
202
  'SELECT * FROM public.users',
203
  pgWhitelist,
204
  { database: 'PostgreSQL', type: 'table' }
205
);
206

207
// Complex whitelist patterns
208
const complexWhitelist = [
209
  '^(select|insert)::(.*)::(user_.*|post_.*)$',  // Allow select/insert on tables starting with user_ or post_
210
  '^update::(.*)::(user_profile|user_settings)$' // Allow update only on specific user tables
211
];
212
```
213

214
### Authority Pattern Matching
215

216
Whitelist patterns use regular expressions for flexible access control:
217

218
```javascript
219
// Exact match
220
'select::null::users'              // Exact table match
221

222
// Wildcard database
223
'select::(.*)::users'              // Any database, users table
224

225
// Multiple tables
226
'select::(.*)::(users|posts|comments)'  // Multiple specific tables
227

228
// Table pattern matching
229
'^(select|insert)::(.*)::(user_.*)$'    // Tables starting with user_
230

231
// Column patterns
232
'select::users::(name|email|created_at)' // Specific columns only
233
'select::users::.*'                      // All columns (wildcard)
234
'^select::(.*)::(?!password).*$'         // All columns except password
235

236
// Mixed patterns
237
'(select|update)::(.*)::(users|profiles)' // Multiple operations and tables
238
```
239

240
**Common Whitelist Scenarios:**
241

242
```javascript
243
// Read-only access to specific tables
244
const readOnlyWhitelist = [
245
  'select::(.*)::(users|posts|comments)',
246
  'select::(.*)::.*'  // Allow column wildcards
247
];
248

249
// Limited user management
250
const userMgmtWhitelist = [
251
  'select::(.*)::(users|user_profiles)',
252
  'update::(.*)::user_profiles',
253
  'insert::(.*)::user_profiles'
254
];
255

256
// Reporting access (read-only, specific columns)
257
const reportingWhitelist = [
258
  'select::users::(id|name|email|created_at)',
259
  'select::orders::(id|user_id|total|status|created_at)',
260
  'select::products::(id|name|price|category)'
261
];
262

263
// Admin access (most operations allowed)
264
const adminWhitelist = [
265
  '(select|insert|update)::(.*)::((?!sensitive_data).)*',  // All except sensitive_data table
266
  'select::(.*)::((?!password|ssn|credit_card).)*'         // All columns except sensitive ones
267
];
268
```
269

270
## Security Best Practices
271

272
### Input Validation
273

274
Always validate SQL queries before processing:
275

276
```javascript
277
function validateAndExecute(sql, whitelist) {
278
  try {
279
    // First check syntax by parsing
280
    const ast = parser.astify(sql);
281
    
282
    // Then check authorization
283
    parser.whiteListCheck(sql, whitelist);
284
    
285
    // Safe to execute
286
    return executeQuery(sql);
287
  } catch (error) {
288
    if (error.message.includes('authority')) {
289
      throw new Error('Access denied: ' + error.message);
290
    } else {
291
      throw new Error('Invalid SQL: ' + error.message);
292
    }
293
  }
294
}
295
```
296

297
### Comprehensive Authority Analysis
298

299
Analyze all aspects of a query:
300

301
```javascript
302
function analyzeQuery(sql) {
303
  const tables = parser.tableList(sql);
304
  const columns = parser.columnList(sql);
305
  const ast = parser.astify(sql);
306
  
307
  return {
308
    tables,
309
    columns,
310
    operations: [...new Set(tables.map(t => t.split('::')[0]))],
311
    databases: [...new Set(tables.map(t => t.split('::')[1]).filter(d => d !== 'null'))],
312
    ast
313
  };
314
}
315

316
const analysis = analyzeQuery('UPDATE users SET email = "new@email.com" WHERE id = 1');
317
console.log(analysis);
318
// {
319
//   tables: ["update::null::users"],
320
//   columns: ["update::users::email", "update::users::id"],
321
//   operations: ["update"],
322
//   databases: [],
323
//   ast: { ... }
324
// }
325
```
326

327
### Multi-Level Authorization
328

329
Implement layered security checks:
330

331
```javascript
332
function checkMultiLevelAuth(sql, userRole) {
333
  const baseWhitelist = ['select::(.*)::(users|posts)']; // Basic read access
334
  const adminWhitelist = [...baseWhitelist, 'update::(.*)::(.*)', 'delete::(.*)::(.*)']; // Admin access
335
  
336
  const whitelist = userRole === 'admin' ? adminWhitelist : baseWhitelist;
337
  
338
  // Check table access
339
  parser.whiteListCheck(sql, whitelist, { type: 'table' });
340
  
341
  // Additional column-level check for sensitive operations
342
  if (sql.toLowerCase().includes('update') || sql.toLowerCase().includes('insert')) {
343
    const columnWhitelist = userRole === 'admin' 
344
      ? ['(update|insert)::(.*)::((?!password|ssn).)*'] 
345
      : ['insert::(.*)::(name|email)'];
346
      
347
    parser.whiteListCheck(sql, columnWhitelist, { type: 'column' });
348
  }
349
}