/**
* Query validation utilities to prevent injection attacks
* Supports validation for Kusto (KQL), SQL, Cosmos DB, and other query languages
*/
export interface QueryValidationResult {
isValid: boolean;
sanitized?: string;
error?: string;
warnings: string[];
riskLevel: 'low' | 'medium' | 'high';
}
/**
* Dangerous SQL/KQL patterns that indicate potential injection
*/
const DANGEROUS_PATTERNS = [
{ pattern: /;\s*drop\s+/i, description: 'DROP statement detected', risk: 'high' as const },
{ pattern: /;\s*delete\s+/i, description: 'DELETE statement detected', risk: 'high' as const },
{ pattern: /;\s*truncate\s+/i, description: 'TRUNCATE statement detected', risk: 'high' as const },
{ pattern: /;\s*alter\s+/i, description: 'ALTER statement detected', risk: 'high' as const },
{ pattern: /;\s*create\s+/i, description: 'CREATE statement detected', risk: 'high' as const },
{ pattern: /;\s*exec(?:ute)?\s+/i, description: 'EXEC statement detected', risk: 'high' as const },
{ pattern: /xp_cmdshell/i, description: 'xp_cmdshell detected', risk: 'high' as const },
{ pattern: /sp_executesql/i, description: 'sp_executesql detected', risk: 'high' as const },
{ pattern: /into\s+outfile/i, description: 'INTO OUTFILE detected', risk: 'high' as const },
{ pattern: /load_file/i, description: 'LOAD_FILE detected', risk: 'high' as const },
{ pattern: /--[^\n]*$/m, description: 'SQL comment detected', risk: 'medium' as const },
{ pattern: /\/\*.*?\*\//s, description: 'Block comment detected', risk: 'medium' as const },
{ pattern: /union\s+select/i, description: 'UNION SELECT detected', risk: 'high' as const },
{ pattern: /'\s*or\s*'[^']*'\s*=/i, description: 'SQL injection pattern detected', risk: 'high' as const },
];
/**
* Validates a Kusto Query Language (KQL) query
* KQL is used by Azure Data Explorer, Log Analytics, and Application Insights
*
* @param query - The KQL query to validate
* @param options - Validation options
* @returns Validation result
*
* @example
* ```typescript
* const result = validateKustoQuery('TableName | where TimeGenerated > ago(1h)');
* if (!result.isValid) {
* throw new Error(result.error);
* }
* ```
*/
export function validateKustoQuery(
query: string,
options: {
maxLength?: number;
allowMultipleStatements?: boolean;
allowedTables?: string[];
} = {}
): QueryValidationResult {
const {
maxLength = 10000,
allowMultipleStatements = false,
allowedTables = []
} = options;
const warnings: string[] = [];
let riskLevel: 'low' | 'medium' | 'high' = 'low';
// Basic validation
if (!query || !query.trim()) {
return {
isValid: false,
error: 'Query cannot be empty',
warnings,
riskLevel: 'low'
};
}
const trimmed = query.trim();
// Length check
if (trimmed.length > maxLength) {
return {
isValid: false,
error: `Query exceeds maximum length of ${maxLength} characters`,
warnings,
riskLevel: 'low'
};
}
// Check for multiple statements
if (!allowMultipleStatements && /;\s*\w+/.test(trimmed)) {
return {
isValid: false,
error: 'Multiple statements not allowed',
warnings,
riskLevel: 'high'
};
}
// Check for dangerous patterns
for (const { pattern, description, risk } of DANGEROUS_PATTERNS) {
if (pattern.test(trimmed)) {
return {
isValid: false,
error: `Dangerous pattern detected: ${description}`,
warnings,
riskLevel: 'high'
};
}
}
// Validate KQL syntax basics
const kqlOperators = [
'where', 'project', 'extend', 'summarize', 'join', 'union',
'take', 'limit', 'top', 'sort', 'order', 'count', 'distinct'
];
const lowerQuery = trimmed.toLowerCase();
const hasValidOperator = kqlOperators.some(op => lowerQuery.includes(`| ${op}`)) ||
/^[a-z_][a-z0-9_]*\s*\|/i.test(trimmed); // Starts with table name
if (!hasValidOperator && !trimmed.includes('|')) {
warnings.push('Query may not be valid KQL - no pipe operators detected');
riskLevel = 'medium';
}
// Check if table name is allowed (if allowlist provided)
if (allowedTables.length > 0) {
const tableMatch = trimmed.match(/^([a-z_][a-z0-9_]*)/i);
if (tableMatch) {
const tableName = tableMatch[1];
if (!allowedTables.includes(tableName)) {
return {
isValid: false,
error: `Table '${tableName}' not in allowed list`,
warnings,
riskLevel: 'high'
};
}
}
}
// Warn about expensive operations
if (/\|\s*join\s+/i.test(trimmed)) {
warnings.push('Query contains JOIN operation (may be expensive)');
if (riskLevel === 'low') riskLevel = 'medium';
}
return {
isValid: true,
sanitized: trimmed,
warnings,
riskLevel
};
}
/**
* Validates a SQL query for Azure SQL, PostgreSQL, etc.
*
* @param query - The SQL query to validate
* @param options - Validation options
* @returns Validation result
*/
export function validateSQLQuery(
query: string,
options: {
maxLength?: number;
allowedOperations?: ('SELECT' | 'INSERT' | 'UPDATE' | 'DELETE')[];
} = {}
): QueryValidationResult {
const {
maxLength = 5000,
allowedOperations = ['SELECT'] // Default: read-only
} = options;
const warnings: string[] = [];
let riskLevel: 'low' | 'medium' | 'high' = 'low';
if (!query || !query.trim()) {
return {
isValid: false,
error: 'Query cannot be empty',
warnings,
riskLevel: 'low'
};
}
const trimmed = query.trim();
if (trimmed.length > maxLength) {
return {
isValid: false,
error: `Query exceeds maximum length of ${maxLength} characters`,
warnings,
riskLevel: 'low'
};
}
// Check for dangerous patterns
for (const { pattern, description, risk } of DANGEROUS_PATTERNS) {
if (pattern.test(trimmed)) {
return {
isValid: false,
error: `Dangerous pattern detected: ${description}`,
warnings,
riskLevel: 'high'
};
}
}
// Check operation type
const operation = trimmed.match(/^\s*(SELECT|INSERT|UPDATE|DELETE|CREATE|DROP|ALTER)/i)?.[1]?.toUpperCase();
if (!operation) {
return {
isValid: false,
error: 'Could not determine query operation type',
warnings,
riskLevel: 'medium'
};
}
if (!allowedOperations.includes(operation as any)) {
return {
isValid: false,
error: `Operation '${operation}' not allowed. Allowed: ${allowedOperations.join(', ')}`,
warnings,
riskLevel: 'high'
};
}
// Warn about potentially expensive queries
if (!/\bwhere\b/i.test(trimmed) && operation === 'SELECT') {
warnings.push('SELECT without WHERE clause (may be expensive)');
riskLevel = 'medium';
}
if (/\bselect\s+\*/i.test(trimmed)) {
warnings.push('SELECT * detected (consider specifying columns)');
}
return {
isValid: true,
sanitized: trimmed,
warnings,
riskLevel
};
}
/**
* Validates a Cosmos DB SQL query
*
* @param query - The Cosmos DB query to validate
* @param options - Validation options
* @returns Validation result
*/
export function validateCosmosQuery(
query: string,
options: {
maxLength?: number;
} = {}
): QueryValidationResult {
const { maxLength = 5000 } = options;
const warnings: string[] = [];
let riskLevel: 'low' | 'medium' | 'high' = 'low';
if (!query || !query.trim()) {
return {
isValid: false,
error: 'Query cannot be empty',
warnings,
riskLevel: 'low'
};
}
const trimmed = query.trim();
if (trimmed.length > maxLength) {
return {
isValid: false,
error: `Query exceeds maximum length of ${maxLength} characters`,
warnings,
riskLevel: 'low'
};
}
// Cosmos DB uses SQL-like syntax but only supports SELECT
if (!/^\s*select\s+/i.test(trimmed)) {
return {
isValid: false,
error: 'Cosmos DB queries must start with SELECT',
warnings,
riskLevel: 'medium'
};
}
// Check for dangerous patterns (less strict for Cosmos which has limited capabilities)
const dangerousForCosmos = DANGEROUS_PATTERNS.filter(p => p.risk === 'high');
for (const { pattern, description } of dangerousForCosmos) {
if (pattern.test(trimmed)) {
warnings.push(`Suspicious pattern detected: ${description}`);
riskLevel = 'high';
}
}
// Warn about expensive operations
if (!/\bwhere\b/i.test(trimmed)) {
warnings.push('Query without WHERE clause (will scan all documents)');
riskLevel = 'medium';
}
if (/\bselect\s+\*/i.test(trimmed)) {
warnings.push('SELECT * detected (consider specifying properties)');
}
return {
isValid: true,
sanitized: trimmed,
warnings,
riskLevel
};
}
/**
* Validates an Azure Monitor Log Analytics query
* Uses same validation as KQL since Log Analytics uses KQL
*/
export const validateLogAnalyticsQuery = validateKustoQuery;
/**
* Generic query validator that attempts to detect query type
* and apply appropriate validation
*
* @param query - The query to validate
* @param hint - Optional hint about query type
* @returns Validation result
*/
export function validateQuery(
query: string,
hint?: 'kql' | 'sql' | 'cosmos' | 'logs'
): QueryValidationResult {
if (hint === 'kql' || hint === 'logs') {
return validateKustoQuery(query);
}
if (hint === 'sql') {
return validateSQLQuery(query);
}
if (hint === 'cosmos') {
return validateCosmosQuery(query);
}
// Auto-detect
const trimmed = query.trim();
// Cosmos DB pattern
if (/^\s*select\s+.*\s+from\s+c\b/i.test(trimmed)) {
return validateCosmosQuery(query);
}
// KQL pattern (table name followed by pipe)
if (/^[a-z_][a-z0-9_]*\s*\|/i.test(trimmed)) {
return validateKustoQuery(query);
}
// SQL pattern
if (/^\s*(select|insert|update|delete)\s+/i.test(trimmed)) {
return validateSQLQuery(query);
}
return {
isValid: false,
error: 'Could not determine query type. Please specify hint parameter.',
warnings: [],
riskLevel: 'medium'
};
}
