// SQL validation utilities extracted for testing
// Function to validate read-only queries
export function isReadOnlyQuery(query: string): boolean {
const trimmedQuery = query.trim();
// Check if query starts with SELECT (case-insensitive)
if (!trimmedQuery.toUpperCase().startsWith('SELECT')) {
return false;
}
// Additional security checks
// 1. Check for multiple statements using semicolons
const semicolonCount = (trimmedQuery.match(/;/g) || []).length;
if (semicolonCount > 1) {
return false;
}
// 2. If there's a semicolon, ensure nothing follows it
if (semicolonCount === 1) {
const parts = trimmedQuery.split(';');
// If there's content after the semicolon, reject
if (parts.length > 2 || (parts.length === 2 && parts[1].trim().length > 0)) {
return false;
}
}
// 3. Check for dangerous SQL keywords (case-insensitive)
const dangerousKeywords = [
'DROP', 'DELETE', 'UPDATE', 'INSERT', 'CREATE', 'ALTER',
'TRUNCATE', 'EXEC', 'EXECUTE', 'UNION', 'MERGE', 'REPLACE',
'CALL', 'DO', 'HANDLER', 'LOAD', 'RENAME', 'GRANT', 'REVOKE'
];
const upperQuery = trimmedQuery.toUpperCase();
for (const keyword of dangerousKeywords) {
// Check if dangerous keyword appears after SELECT (not in string literals)
const regex = new RegExp(`\\b${keyword}\\b`, 'i');
if (regex.test(upperQuery)) {
return false;
}
}
// 4. Check for SQL injection patterns
const injectionPatterns = [
/--/, // SQL comments
/\/\*/, // Multi-line comment start
/\*\//, // Multi-line comment end
/xp_/, // SQL Server extended procedures
/sp_/, // SQL Server stored procedures
];
for (const pattern of injectionPatterns) {
if (pattern.test(trimmedQuery)) {
return false;
}
}
// 5. Check for MySQL-specific dangerous functions and clauses
const dangerousPatterns = [
{ pattern: /\bLOAD_FILE\s*\(/i, name: 'LOAD_FILE' },
{ pattern: /\bINTO\s+OUTFILE\b/i, name: 'INTO OUTFILE' },
{ pattern: /\bINTO\s+DUMPFILE\b/i, name: 'INTO DUMPFILE' },
{ pattern: /\bSYSTEM\s*\(/i, name: 'SYSTEM' },
{ pattern: /\bSLEEP\s*\(/i, name: 'SLEEP' }
];
for (const { pattern, name } of dangerousPatterns) {
if (pattern.test(trimmedQuery)) {
return false;
}
}
return true;
}
// Function to add automatic LIMIT clause to prevent large result sets
export function addAutoLimit(query: string, defaultLimit: number = 1000): string {
const trimmedQuery = query.trim();
const upperQuery = trimmedQuery.toUpperCase();
// Don't add LIMIT if query already has one
if (upperQuery.includes(' LIMIT ')) {
return trimmedQuery;
}
// Don't add LIMIT to COUNT(*) queries or other aggregate functions without LIMIT
if (/COUNT\s*\([^)]+\)/i.test(trimmedQuery)) {
return trimmedQuery;
}
// Don't add LIMIT to EXISTS or NOT EXISTS subqueries
if (/EXISTS\s*\(/i.test(trimmedQuery) || /NOT\s+EXISTS\s*\(/i.test(trimmedQuery)) {
return trimmedQuery;
}
// Don't add LIMIT if query ends with a semicolon (handle it gracefully)
if (trimmedQuery.endsWith(';')) {
return trimmedQuery.slice(0, -1) + ` LIMIT ${defaultLimit};`;
}
// Add LIMIT clause
return `${trimmedQuery} LIMIT ${defaultLimit}`;
}