security/phase1-emergency-mitigation/03-sql-injection-detector.ts
Rama main · réplica de lectura del repositorio original.
/**
* This is a TEMPORARY security measure to detect and block SQL injection attempts
* while we implement proper parameterized queries in Phase 2-4.
*
* WARNING: This is NOT a replacement for proper input validation and parameterized queries.
* This should be removed once all SQL injection vulnerabilities are fixed.
*/
import { Request, Response, NextFunction } from "express";
/**
* Common SQL injection patterns to detect
*/
const SQL_INJECTION_PATTERNS = [
// UNION-based injection
/(\bUNION\b.*\bSELECT\b)/i,
/(\bUNION\b.*\bALL\b.*\bSELECT\b)/i,
// Boolean-based blind injection
/(\bOR\b\s+[0-9]+\s*=\s*[0-9]+)/i,
/(\bAND\b\s+[0-9]+\s*=\s*[0-9]+)/i,
/(\bOR\b\s+['"][^'"]*['"]\s*=\s*['"][^'"]*['"])/i,
// Time-based blind injection
/(\bSLEEP\s*\()/i,
/(\bPG_SLEEP\s*\()/i,
/(\bWAITFOR\b.*\bDELAY\b)/i,
/(\bBENCHMARK\s*\()/i,
// Stacked queries
/(;\s*DROP\b)/i,
/(;\s*DELETE\b)/i,
/(;\s*UPDATE\b)/i,
/(;\s*INSERT\b)/i,
/(;\s*CREATE\b)/i,
/(;\s*ALTER\b)/i,
/(;\s*TRUNCATE\b)/i,
// Information schema access
/(information_schema)/i,
/(pg_catalog)/i,
/(pg_tables)/i,
/(pg_database)/i,
// Comment-based injection
/(--[^\r\n]*)/,
/(\/\*.*\*\/)/,
/(#[^\r\n]*)/,
// Command execution
/(exec\s*\()/i,
/(execute\s*\()/i,
/(xp_cmdshell)/i,
// Dangerous functions
/(\bDROP\b.*\bTABLE\b)/i,
/(\bDROP\b.*\bDATABASE\b)/i,
/(\bTRUNCATE\b.*\bTABLE\b)/i,
/(\bDELETE\b.*\bFROM\b)/i,
// Encoded attacks
/(0x[0-9a-f]+)/i,
/(CHAR\s*\()/i,
/(CHR\s*\()/i,
/(ASCII\s*\()/i,
// PostgreSQL specific
/(\bCOPY\b.*\bFROM\b)/i,
/(\bCOPY\b.*\bTO\b)/i,
/(pg_read_file)/i,
/(pg_ls_dir)/i,
/(pg_stat_file)/i,
// Multiple single quotes (common in injection)
/('{2,})/,
// Hex encoding
/(\\x[0-9a-f]{2})/i,
];
/**
* Suspicious parameter names that are commonly used in attacks
*/
const SUSPICIOUS_PARAM_NAMES = [
'id[]',
'id[0]',
'union',
'select',
'where',
'from',
'table',
];
/**
* Check if a value contains SQL injection patterns
*/
function containsSqlInjection(value: any): boolean {
if (typeof value !== 'string') {
return false;
}
// Decode URL encoding to catch encoded attacks
let decodedValue = value;
try {
decodedValue = decodeURIComponent(value);
} catch (e) {
// If decoding fails, use original value
}
// Check against all patterns
return SQL_INJECTION_PATTERNS.some(pattern => pattern.test(decodedValue));
}
/**
* Recursively check an object for SQL injection patterns
*/
function checkObjectForInjection(obj: any, path: string = ''): { found: boolean; location: string; value: string } | null {
if (obj === null || obj === undefined) {
return null;
}
if (typeof obj === 'string') {
if (containsSqlInjection(obj)) {
return { found: true, location: path, value: obj };
}
return null;
}
if (Array.isArray(obj)) {
for (let i = 0; i < obj.length; i++) {
const result = checkObjectForInjection(obj[i], `${path}[${i}]`);
if (result) return result;
}
return null;
}
if (typeof obj === 'object') {
for (const key in obj) {
if (obj.hasOwnProperty(key)) {
const result = checkObjectForInjection(obj[key], path ? `${path}.${key}` : key);
if (result) return result;
}
}
return null;
}
return null;
}
/**
* Log security incident
*/
function logSecurityIncident(req: Request, detection: { location: string; value: string }) {
const incident = {
timestamp: new Date().toISOString(),
type: 'SQL_INJECTION_ATTEMPT',
ip: req.ip || req.socket.remoteAddress,
method: req.method,
url: req.originalUrl || req.url,
headers: {
userAgent: req.headers['user-agent'],
referer: req.headers['referer'],
origin: req.headers['origin'],
},
detection: {
location: detection.location,
value: detection.value.substring(0, 200), // Limit logged value length
},
user: (req as any).user?.id || 'anonymous',
};
// Log to console (in production, send to monitoring service)
console.error('[SECURITY ALERT] SQL Injection Attempt Detected:', JSON.stringify(incident, null, 2));
// TODO: Send alert to security team
// TODO: Log to security audit table
// TODO: Send to Azure Monitor / Application Insights
}
/**
* SQL Injection Detection Middleware
*
* This middleware checks all incoming requests for SQL injection patterns
* and blocks suspicious requests.
*/
export const sqlInjectionDetector = (req: Request, res: Response, next: NextFunction) => {
try {
// Skip for certain paths (static files, health checks)
const skipPaths = [
'/health',
'/favicon.ico',
'/static/',
'/public/',
'/_next/',
];
if (skipPaths.some(path => req.path.startsWith(path))) {
return next();
}
// Check query parameters
const queryCheck = checkObjectForInjection(req.query, 'query');
if (queryCheck) {
logSecurityIncident(req, queryCheck);
return res.status(403).json({
done: false,
message: 'Forbidden: Suspicious request detected',
body: null,
});
}
// Check request body
const bodyCheck = checkObjectForInjection(req.body, 'body');
if (bodyCheck) {
logSecurityIncident(req, bodyCheck);
return res.status(403).json({
done: false,
message: 'Forbidden: Suspicious request detected',
body: null,
});
}
// Check URL parameters
const paramsCheck = checkObjectForInjection(req.params, 'params');
if (paramsCheck) {
logSecurityIncident(req, paramsCheck);
return res.status(403).json({
done: false,
message: 'Forbidden: Suspicious request detected',
body: null,
});
}
// Check for suspicious parameter names
const allParams = { ...req.query, ...req.body, ...req.params };
for (const paramName of SUSPICIOUS_PARAM_NAMES) {
if (paramName in allParams) {
logSecurityIncident(req, { location: `param.${paramName}`, value: String(allParams[paramName]) });
return res.status(403).json({
done: false,
message: 'Forbidden: Suspicious parameter detected',
body: null,
});
}
}
next();
} catch (error) {
// Don't let the security middleware crash the app
console.error('[SQL Injection Detector] Error:', error);
next();
}
};
/**
* Rate limiting for detected attacks
* Keep track of IPs that trigger the detector
*/
const attackAttempts = new Map<string, { count: number; firstAttempt: number }>();
// Clean up old entries every hour
setInterval(() => {
const oneHourAgo = Date.now() - 60 * 60 * 1000;
for (const [ip, data] of attackAttempts.entries()) {
if (data.firstAttempt < oneHourAgo) {
attackAttempts.delete(ip);
}
}
}, 60 * 60 * 1000);
/**
* Enhanced detector with IP blocking
*/
export const sqlInjectionDetectorWithBlocking = (req: Request, res: Response, next: NextFunction) => {
const ip = req.ip || req.socket.remoteAddress || 'unknown';
// Check if IP is already blocked
const attempts = attackAttempts.get(ip);
if (attempts && attempts.count >= 5) {
const timeSinceFirst = Date.now() - attempts.firstAttempt;
if (timeSinceFirst < 60 * 60 * 1000) { // Block for 1 hour
return res.status(403).json({
done: false,
message: 'Forbidden: Too many suspicious requests',
body: null,
});
} else {
// Reset after 1 hour
attackAttempts.delete(ip);
}
}
// Run the detector
const originalNext = next;
let blocked = false;
const wrappedNext = (err?: any) => {
if (!blocked) {
originalNext(err);
}
};
const wrappedRes = {
...res,
status: (code: number) => {
if (code === 403) {
blocked = true;
// Increment attack counter
const current = attackAttempts.get(ip) || { count: 0, firstAttempt: Date.now() };
attackAttempts.set(ip, {
count: current.count + 1,
firstAttempt: current.firstAttempt,
});
}
return res.status(code);
},
} as Response;
sqlInjectionDetector(req, wrappedRes, wrappedNext);
};
export default sqlInjectionDetectorWithBlocking;