← back to Watches
security/sql-injection-prevention.js
368 lines
/**
* SQL INJECTION PREVENTION
* Advanced protection against SQL injection attacks
*
* Features:
* - Pattern detection
* - Parameterized query enforcement
* - Input sanitization
* - Whitelist validation
* - Query complexity analysis
*/
class SQLInjectionPrevention {
constructor() {
this.sqlPatterns = this.initializeSQLPatterns();
this.blockedKeywords = this.initializeBlockedKeywords();
this.allowedTableNames = new Set();
this.allowedColumnNames = new Set();
}
/**
* Initialize SQL injection patterns
*/
initializeSQLPatterns() {
return [
// Union-based injection
/(\bunion\b.*\bselect\b)/i,
/(\bunion\b.*\ball\b.*\bselect\b)/i,
// Stacked queries
/;.*\b(select|insert|update|delete|drop|create|alter)\b/i,
// Comment-based injection
/(--|#|\/\*|\*\/)/,
// Classic SQL injection
/(\bor\b.*=.*)/i,
/(\band\b.*=.*)/i,
/'.*\bor\b.*'/i,
/'.*\band\b.*'/i,
// Boolean-based injection
/(\b1=1\b|\b1='1'\b)/i,
/(\b0=0\b|\b0='0'\b)/i,
// Time-based injection
/\b(sleep|benchmark|waitfor|delay)\b/i,
// Information schema
/\b(information_schema|sys|mysql|pg_catalog)\b/i,
// Dangerous functions
/\b(exec|execute|sp_executesql|xp_cmdshell)\b/i,
// File operations
/\b(load_file|into\s+outfile|into\s+dumpfile)\b/i,
// String manipulation
/\b(concat|chr|char|ascii|substring)\b.*\(/i,
// Encoding bypass
/(%27|%23|%2d%2d|%2f%2a|%2a%2f)/i,
// Hex encoding
/(0x[0-9a-f]+)/i
];
}
/**
* Initialize blocked SQL keywords
*/
initializeBlockedKeywords() {
return new Set([
'DROP', 'DELETE', 'TRUNCATE', 'ALTER', 'CREATE',
'EXEC', 'EXECUTE', 'SCRIPT', 'JAVASCRIPT',
'UNION', 'INSERT', 'UPDATE', 'GRANT', 'REVOKE',
'SHUTDOWN', 'BACKUP', 'RESTORE'
]);
}
/**
* Sanitize input for SQL safety
*/
sanitizeInput(input) {
if (typeof input !== 'string') {
return input;
}
// Remove null bytes
let sanitized = input.replace(/\0/g, '');
// Escape single quotes
sanitized = sanitized.replace(/'/g, "''");
// Remove SQL comments
sanitized = sanitized.replace(/--/g, '');
sanitized = sanitized.replace(/\/\*/g, '');
sanitized = sanitized.replace(/\*\//g, '');
sanitized = sanitized.replace(/#/g, '');
// Remove semicolons (prevents query stacking)
sanitized = sanitized.replace(/;/g, '');
return sanitized;
}
/**
* Validate input against SQL injection patterns
*/
validateInput(input) {
if (typeof input !== 'string') {
return { valid: true, sanitized: input };
}
const threats = [];
// Check against patterns
this.sqlPatterns.forEach((pattern, index) => {
if (pattern.test(input)) {
threats.push({
type: 'PATTERN_MATCH',
pattern: pattern.toString(),
index: index
});
}
});
// Check for blocked keywords
const upperInput = input.toUpperCase();
this.blockedKeywords.forEach(keyword => {
if (upperInput.includes(keyword)) {
threats.push({
type: 'BLOCKED_KEYWORD',
keyword: keyword
});
}
});
// Check for suspicious character sequences
if (this.hasSuspiciousSequences(input)) {
threats.push({
type: 'SUSPICIOUS_SEQUENCE'
});
}
const isValid = threats.length === 0;
return {
valid: isValid,
threats: threats,
sanitized: isValid ? input : this.sanitizeInput(input),
originalLength: input.length,
sanitizedLength: this.sanitizeInput(input).length
};
}
/**
* Check for suspicious character sequences
*/
hasSuspiciousSequences(input) {
const suspicious = [
/'{2,}/, // Multiple single quotes
/\s+(or|and)\s+/i, // OR/AND with spaces
/['"]\s*=\s*['"]/, // Quote equals quote
/\d+\s*=\s*\d+/, // Number equals number
/\)\s*;/, // Closing parenthesis with semicolon
/\bselect\b.*\bfrom\b/i // SELECT FROM pattern
];
return suspicious.some(pattern => pattern.test(input));
}
/**
* Create parameterized query helper
*/
createParameterizedQuery(query, params) {
// Ensure query uses placeholders
const placeholderPattern = /\$\d+|\?/g;
if (!placeholderPattern.test(query)) {
throw new Error('Query must use parameterized placeholders ($1, $2, etc. or ?)');
}
// Validate all parameters
const validatedParams = params.map(param => {
if (typeof param === 'string') {
const validation = this.validateInput(param);
if (!validation.valid) {
throw new Error(`Parameter validation failed: ${JSON.stringify(validation.threats)}`);
}
return validation.sanitized;
}
return param;
});
return {
query: query,
params: validatedParams
};
}
/**
* Whitelist table name
*/
allowTable(tableName) {
this.allowedTableNames.add(tableName.toLowerCase());
}
/**
* Whitelist column name
*/
allowColumn(columnName) {
this.allowedColumnNames.add(columnName.toLowerCase());
}
/**
* Validate table name against whitelist
*/
isValidTableName(tableName) {
if (this.allowedTableNames.size === 0) {
// If no whitelist, do pattern validation
return /^[a-z][a-z0-9_]*$/i.test(tableName);
}
return this.allowedTableNames.has(tableName.toLowerCase());
}
/**
* Validate column name against whitelist
*/
isValidColumnName(columnName) {
if (this.allowedColumnNames.size === 0) {
// If no whitelist, do pattern validation
return /^[a-z][a-z0-9_]*$/i.test(columnName);
}
return this.allowedColumnNames.has(columnName.toLowerCase());
}
/**
* Build safe WHERE clause
*/
buildWhereClause(conditions) {
const validConditions = [];
const params = [];
let paramIndex = 1;
for (const [column, value] of Object.entries(conditions)) {
// Validate column name
if (!this.isValidColumnName(column)) {
throw new Error(`Invalid column name: ${column}`);
}
// Validate value
const validation = this.validateInput(value);
if (!validation.valid) {
throw new Error(`Invalid value for column ${column}`);
}
validConditions.push(`${column} = $${paramIndex}`);
params.push(validation.sanitized);
paramIndex++;
}
return {
clause: validConditions.length > 0 ? 'WHERE ' + validConditions.join(' AND ') : '',
params: params
};
}
/**
* Validate and sanitize ORDER BY clause
*/
sanitizeOrderBy(column, direction = 'ASC') {
// Validate column
if (!this.isValidColumnName(column)) {
throw new Error(`Invalid ORDER BY column: ${column}`);
}
// Validate direction
const validDirection = direction.toUpperCase();
if (!['ASC', 'DESC'].includes(validDirection)) {
throw new Error(`Invalid ORDER BY direction: ${direction}`);
}
return `ORDER BY ${column} ${validDirection}`;
}
/**
* Validate and sanitize LIMIT clause
*/
sanitizeLimit(limit, offset = 0) {
const validLimit = parseInt(limit, 10);
const validOffset = parseInt(offset, 10);
if (isNaN(validLimit) || validLimit < 0 || validLimit > 1000) {
throw new Error('Invalid LIMIT value');
}
if (isNaN(validOffset) || validOffset < 0) {
throw new Error('Invalid OFFSET value');
}
return `LIMIT ${validLimit} OFFSET ${validOffset}`;
}
}
/**
* Express middleware for SQL injection prevention
*/
export const sqlInjectionMiddleware = (sqlPrevention) => {
return (req, res, next) => {
// Validate all string inputs
const validateObject = (obj, path = '') => {
const violations = [];
for (const [key, value] of Object.entries(obj)) {
const currentPath = path ? `${path}.${key}` : key;
if (typeof value === 'string') {
const validation = sqlPrevention.validateInput(value);
if (!validation.valid) {
violations.push({
path: currentPath,
threats: validation.threats,
original: value,
sanitized: validation.sanitized
});
// Auto-sanitize
obj[key] = validation.sanitized;
}
} else if (typeof value === 'object' && value !== null) {
validateObject(value, currentPath);
}
}
return violations;
};
const violations = [];
// Check query parameters
if (req.query && Object.keys(req.query).length > 0) {
violations.push(...validateObject(req.query, 'query'));
}
// Check body
if (req.body && Object.keys(req.body).length > 0) {
violations.push(...validateObject(req.body, 'body'));
}
// Check params
if (req.params && Object.keys(req.params).length > 0) {
violations.push(...validateObject(req.params, 'params'));
}
// Log violations but continue (after sanitization)
if (violations.length > 0) {
console.warn('[SQL_INJECTION_PREVENTION] Potential threats detected and sanitized:', violations);
req.sqlInjectionViolations = violations;
}
next();
};
};
export default SQLInjectionPrevention;