CtrlK
BlogDocsLog inGet started
Tessl Logo

tessl/npm-node-sql-parser

Simple SQL parser library that converts SQL statements to AST and back to SQL with support for multiple database engines

—

Pending
Quality

Pending

Does it follow best practices?

Impact

Pending

No eval scenarios have been run

SecuritybySnyk

Pending

The risk profile of this skill

Overview
Eval results
Files

security.mddocs/

Authority and Security

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.

Capabilities

Table List Extraction

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

/**
 * Extract list of tables from SQL query
 * @param sql - SQL string to analyze
 * @param opt - Optional configuration object
 * @returns Array of table names with authority format
 */
tableList(sql: string, opt?: Option): string[];

Authority Format: {type}::{dbName}::{tableName}

Where:

  • type is the operation type: select, update, delete, insert
  • dbName is the database name (or null if not specified)
  • tableName is the table name

Usage Examples:

const { Parser } = require('node-sql-parser');
const parser = new Parser();

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

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

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

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

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

// Server.database.schema.table (TransactSQL)
const tables6 = parser.tableList(
  'SELECT * FROM server1.mydb.dbo.users', 
  { database: 'TransactSQL' }
);
console.log(tables6); // ["select::server1.mydb.dbo::users"]

Column List Extraction

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

/**
 * Extract list of columns from SQL query
 * @param sql - SQL string to analyze  
 * @param opt - Optional configuration object
 * @returns Array of column names with authority format
 */
columnList(sql: string, opt?: Option): string[];

Authority Format: {type}::{tableName}::{columnName}

Where:

  • type is the operation type: select, update, delete, insert
  • tableName is the table name (or null if not determinable)
  • columnName is the column name

Special Cases:

  • For SELECT *, DELETE, and INSERT INTO table VALUES() without specified columns, the .* regex pattern is used

Usage Examples:

// Specific column access
const columns1 = parser.columnList('SELECT name, email FROM users');
console.log(columns1); // ["select::users::name", "select::users::email"]

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

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

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

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

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

Whitelist Authorization

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

/**
 * Check SQL against whitelist authority patterns
 * @param sql - SQL string to check
 * @param whiteList - Array of allowed patterns (regex strings)
 * @param opt - Optional configuration object
 * @returns Error if check fails, undefined if passes
 * @throws Error with detailed message if unauthorized access detected
 */
whiteListCheck(sql: string, whiteList: string[], opt?: Option): Error | undefined;

type WhilteListCheckMode = "table" | "column";

interface Option {
  database?: string;
  type?: WhilteListCheckMode; // Default: "table"
}

Usage Examples:

// Table whitelist checking (default mode)
const tableWhitelist = [
  'select::(.*)::(users|posts)',     // Allow SELECT on users and posts tables
  'update::(.*)::users',             // Allow UPDATE on users table
  'insert::(.*)::posts'              // Allow INSERT on posts table
];

try {
  // This passes - SELECT on users is allowed
  parser.whiteListCheck(
    'SELECT * FROM users', 
    tableWhitelist
  );
  
  // This fails - DELETE is not in whitelist
  parser.whiteListCheck(
    'DELETE FROM users WHERE id = 1', 
    tableWhitelist
  );
} catch (error) {
  console.error('Access denied:', error.message);
  // "authority = 'delete::null::users' is required in table whiteList to execute SQL = 'DELETE FROM users WHERE id = 1'"
}

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

try {
  // This passes - accessing allowed columns
  parser.whiteListCheck(
    'SELECT name, email FROM users',
    columnWhitelist,
    { type: 'column' }
  );
  
  // This fails - password column not allowed
  parser.whiteListCheck(
    'SELECT name, password FROM users',
    columnWhitelist,
    { type: 'column' }
  );
} catch (error) {
  console.error('Column access denied:', error.message);
}

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

parser.whiteListCheck(
  'SELECT * FROM public.users',
  pgWhitelist,
  { database: 'PostgreSQL', type: 'table' }
);

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

Authority Pattern Matching

Whitelist patterns use regular expressions for flexible access control:

// Exact match
'select::null::users'              // Exact table match

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

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

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

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

// Mixed patterns
'(select|update)::(.*)::(users|profiles)' // Multiple operations and tables

Common Whitelist Scenarios:

// Read-only access to specific tables
const readOnlyWhitelist = [
  'select::(.*)::(users|posts|comments)',
  'select::(.*)::.*'  // Allow column wildcards
];

// Limited user management
const userMgmtWhitelist = [
  'select::(.*)::(users|user_profiles)',
  'update::(.*)::user_profiles',
  'insert::(.*)::user_profiles'
];

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

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

Security Best Practices

Input Validation

Always validate SQL queries before processing:

function validateAndExecute(sql, whitelist) {
  try {
    // First check syntax by parsing
    const ast = parser.astify(sql);
    
    // Then check authorization
    parser.whiteListCheck(sql, whitelist);
    
    // Safe to execute
    return executeQuery(sql);
  } catch (error) {
    if (error.message.includes('authority')) {
      throw new Error('Access denied: ' + error.message);
    } else {
      throw new Error('Invalid SQL: ' + error.message);
    }
  }
}

Comprehensive Authority Analysis

Analyze all aspects of a query:

function analyzeQuery(sql) {
  const tables = parser.tableList(sql);
  const columns = parser.columnList(sql);
  const ast = parser.astify(sql);
  
  return {
    tables,
    columns,
    operations: [...new Set(tables.map(t => t.split('::')[0]))],
    databases: [...new Set(tables.map(t => t.split('::')[1]).filter(d => d !== 'null'))],
    ast
  };
}

const analysis = analyzeQuery('UPDATE users SET email = "new@email.com" WHERE id = 1');
console.log(analysis);
// {
//   tables: ["update::null::users"],
//   columns: ["update::users::email", "update::users::id"],
//   operations: ["update"],
//   databases: [],
//   ast: { ... }
// }

Multi-Level Authorization

Implement layered security checks:

function checkMultiLevelAuth(sql, userRole) {
  const baseWhitelist = ['select::(.*)::(users|posts)']; // Basic read access
  const adminWhitelist = [...baseWhitelist, 'update::(.*)::(.*)', 'delete::(.*)::(.*)']; // Admin access
  
  const whitelist = userRole === 'admin' ? adminWhitelist : baseWhitelist;
  
  // Check table access
  parser.whiteListCheck(sql, whitelist, { type: 'table' });
  
  // Additional column-level check for sensitive operations
  if (sql.toLowerCase().includes('update') || sql.toLowerCase().includes('insert')) {
    const columnWhitelist = userRole === 'admin' 
      ? ['(update|insert)::(.*)::((?!password|ssn).)*'] 
      : ['insert::(.*)::(name|email)'];
      
    parser.whiteListCheck(sql, columnWhitelist, { type: 'column' });
  }
}

docs

expressions.md

index.md

security.md

sql-parsing.md

utilities.md

tile.json