Detect unsafe functions in SQL queries on DataCentral ETL pages
// ==UserScript== // @name Datanet: SQL Unsafe Function Detector // @namespace http://tampermonkey.net/ // @version 1.5 // @description Detect unsafe functions in SQL queries on DataCentral ETL pages // @author [email protected] // @match https://datacentral.a2z.com/dw-platform/servlet/dwp/template/EtlViewExtractJobs.vm/job_profile_id/* // @grant none // ==/UserScript== /* REVISION HISTORY: 1 - 2025-09-16 - zhjy@ - Initial version 1.1 - 2025-09-16 - zhjy@ - Updated "match" to make it available to new Datanet UI 1.2 - 2025-09-16 - zhjy@ - Revert to 1.0 1.3 - 2025-09-16 - zhjy@ - Fixed performance issues by removing automatic monitoring, detection now only runs on button click 1.4 - 2025-09-16 - zhjy@ - Enhanced WHERE clause detection to identify complete WHERE clauses and JOIN ON clause extraction for improved accuracy. 1.5 - 2025-09-18 - zhjy@ - Fixed SQL text extraction; Modernized UI design; Refactored unsafe function detection logic */ (function() { 'use strict'; // Define unsafe functions by category const UNSAFE_FUNCTIONS = { 'Date/Time Functions': [ 'DATE_TRUNC', 'DATE_PART', 'EXTRACT', 'TO_CHAR', 'ADD_MONTHS', 'CONVERT_TIMEZONE', 'CURRENT_DATE', 'INTERVAL', 'TRUNC', '::DATE' ], 'String Functions': [ 'LENGTH', 'LEFT', 'RIGHT', 'SUBSTR', 'POSITION', 'CONCAT', 'REPLACE' ], 'String Matching Functions': [ 'SIMILAR TO', 'REGEXP_COUNT', 'LIKE' ], 'Type Conversion Functions': [ 'CAST', 'TO_NUMBER', 'TO_DATE', '::DATE', '::TIMESTAMP', '::TIME', '::INTEGER', '::NUMERIC', '::TEXT', '::VARCHAR', '::CHAR', '::BOOLEAN' ], 'Time Comparison Functions': [ 'TIME BETWEEN', 'EXTRACT.*EPOCH' ] }; // Flatten all unsafe functions for easy lookup const ALL_UNSAFE_FUNCTIONS = Object.values(UNSAFE_FUNCTIONS).flat(); // Define safe TO_DATE patterns that should not be flagged as unsafe const SAFE_TO_DATE_PATTERNS = [ /to_date\s*\(\s*['"]\{RUN_DATE_YYYYMMDD\}['"],\s*['"]YYYYMMDD['"]\s*\)/gi, /to_date\s*\(\s*['"]\{RUN_DATE_YYYY-MM-DD\}['"],\s*['"]YYYY-MM-DD['"]\s*\)/gi, /to_date\s*\(\s*['"]\{RUN_DATE_YYYY\/MM\/DD\}['"],\s*['"]YYYY\/MM\/DD['"]\s*\)/gi ]; // CSS styles for UI and highlighting const styles = ` .unsafe-function-highlight { background-color: #fef3c7 !important; border-radius: 4px !important; padding: 1px 3px !important; font-weight: 600 !important; user-select: text !important; -webkit-user-select: text !important; -moz-user-select: text !important; -ms-user-select: text !important; box-shadow: 0 1px 2px rgba(0, 0, 0, 0.05) !important; } .unsafe-function-panel { position: fixed; top: 20px; right: 20px; width: 380px; max-height: 520px; background: linear-gradient(135deg, #ffffff 0%, #f8fafc 100%); border: 1px solid #e2e8f0; border-radius: 12px; box-shadow: 0 20px 25px -5px rgba(0, 0, 0, 0.1), 0 10px 10px -5px rgba(0, 0, 0, 0.04); z-index: 9999; font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, 'Helvetica Neue', Arial, sans-serif; overflow: hidden; backdrop-filter: blur(10px); border-top: 3px solid #334155; } .unsafe-function-panel-header { background: linear-gradient(135deg, #334155 0%, #475569 100%); color: white; padding: 16px 20px; font-weight: 600; font-size: 15px; display: flex; justify-content: space-between; align-items: center; letter-spacing: -0.025em; } .unsafe-function-panel-content { padding: 20px; max-height: 420px; overflow-y: auto; scrollbar-width: thin; scrollbar-color: #cbd5e1 #f1f5f9; } .unsafe-function-panel-content::-webkit-scrollbar { width: 6px; } .unsafe-function-panel-content::-webkit-scrollbar-track { background: #f1f5f9; border-radius: 3px; } .unsafe-function-panel-content::-webkit-scrollbar-thumb { background: #cbd5e1; border-radius: 3px; } .unsafe-function-panel-content::-webkit-scrollbar-thumb:hover { background: #94a3b8; } .unsafe-function-category { margin-bottom: 18px; } .unsafe-function-category:last-child { margin-bottom: 0; } .unsafe-function-category-title { font-weight: 600; color: #1e293b; margin-bottom: 8px; border-bottom: 2px solid #e2e8f0; padding-bottom: 6px; font-size: 14px; letter-spacing: -0.025em; display: flex; align-items: center; gap: 8px; } .unsafe-function-category-title::before { content: "⚠️"; font-size: 16px; } .unsafe-function-item { margin: 6px 0; padding: 12px 16px; background: linear-gradient(135deg, #ffffff 0%, #f8fafc 100%); border: 1px solid #e2e8f0; border-left: 4px solid #f59e0b; border-radius: 8px; font-family: 'SF Mono', Monaco, 'Cascadia Code', 'Roboto Mono', Consolas, 'Courier New', monospace; font-size: 13px; cursor: pointer; transition: all 0.2s cubic-bezier(0.4, 0, 0.2, 1); position: relative; line-height: 1.5; } .unsafe-function-item:hover { background: linear-gradient(135deg, #fef3c7 0%, #fef9e7 100%); border-color: #f59e0b; box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.1), 0 2px 4px -1px rgba(0, 0, 0, 0.06); transform: translateY(-1px); } .unsafe-function-item strong { color: #dc2626; font-weight: 700; font-size: 14px; } .unsafe-function-item small { color: #64748b; font-size: 12px; line-height: 1.4; margin-top: 4px; display: block; } .unsafe-function-item::after { content: "🔍"; position: absolute; right: 12px; top: 50%; transform: translateY(-50%); opacity: 0; transition: all 0.2s cubic-bezier(0.4, 0, 0.2, 1); font-size: 16px; } .unsafe-function-item:hover::after { opacity: 1; transform: translateY(-50%) scale(1.1); } .temporary-line-flash { animation: lineFlash 2s ease-out; } @keyframes lineFlash { 0% { background-color: rgba(51, 65, 85, 0.3) !important; box-shadow: 0 0 0 4px rgba(51, 65, 85, 0.2) !important; } 50% { background-color: rgba(51, 65, 85, 0.2) !important; box-shadow: 0 0 0 2px rgba(51, 65, 85, 0.1) !important; } 100% { background-color: rgba(245, 158, 11, 0.15) !important; box-shadow: none !important; } } .close-btn { background: rgba(255, 255, 255, 0.2); border: none; color: white; font-size: 20px; cursor: pointer; padding: 0; width: 28px; height: 28px; display: flex; align-items: center; justify-content: center; border-radius: 6px; transition: all 0.2s cubic-bezier(0.4, 0, 0.2, 1); } .close-btn:hover { background: rgba(255, 255, 255, 0.3); transform: scale(1.05); } .toggle-btn { position: fixed; bottom: 24px; right: 24px; background: linear-gradient(135deg, #334155 0%, #475569 100%); color: white; border: none; padding: 14px 20px; border-radius: 12px; cursor: pointer; font-size: 14px; font-weight: 600; z-index: 9998; box-shadow: 0 10px 15px -3px rgba(0, 0, 0, 0.1), 0 4px 6px -2px rgba(0, 0, 0, 0.05); transition: all 0.3s cubic-bezier(0.4, 0, 0.2, 1); font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, 'Helvetica Neue', Arial, sans-serif; letter-spacing: -0.025em; min-width: 200px; text-align: center; } .toggle-btn:hover { background: linear-gradient(135deg, #475569 0%, #64748b 100%); transform: translateY(-2px); box-shadow: 0 20px 25px -5px rgba(0, 0, 0, 0.1), 0 10px 10px -5px rgba(0, 0, 0, 0.04); } .toggle-btn:active { transform: translateY(0); box-shadow: 0 10px 15px -3px rgba(0, 0, 0, 0.1), 0 4px 6px -2px rgba(0, 0, 0, 0.05); } .no-unsafe-functions { color: #059669; font-style: normal; text-align: center; padding: 32px 20px; font-size: 15px; font-weight: 500; line-height: 1.6; background: linear-gradient(135deg, #ecfdf5 0%, #f0fdf4 100%); border-radius: 8px; border: 1px solid #bbf7d0; } .no-unsafe-functions::before { content: "✅"; display: block; font-size: 32px; margin-bottom: 12px; } `; // Add styles to page const styleSheet = document.createElement('style'); styleSheet.textContent = styles; document.head.appendChild(styleSheet); let detectedFunctions = {}; let panelVisible = false; let panel = null; let toggleBtn = null; let isScanning = false; let scanTimeout = null; let highlightStyleSheet = null; // Store dynamic highlight styles let highlightedLines = new Map(); // Store highlighted line information for persistence let highlightProtectionInterval = null; // Interval for highlight protection // Create unique key for deduplication function createUniqueKey(func) { return `${func.function}-${func.line}-${func.column}-${func.context.trim()}`; } // Deduplicate detected functions using unique keys function deduplicateDetections(detectedFunctions) { const deduplicatedFunctions = {}; const seenKeys = new Set(); Object.entries(detectedFunctions).forEach(([category, functions]) => { const uniqueFunctions = []; functions.forEach(func => { const uniqueKey = createUniqueKey(func); if (!seenKeys.has(uniqueKey)) { seenKeys.add(uniqueKey); uniqueFunctions.push(func); } else { // Duplicate detection skipped } }); if (uniqueFunctions.length > 0) { deduplicatedFunctions[category] = uniqueFunctions; } }); return deduplicatedFunctions; } // Jump to line functionality - completely independent functions function jumpToLine(lineNumber, elementIndex) { try { // Find the correct CodeMirror instance const codeMirrorInstance = findCodeMirrorForLine(lineNumber, elementIndex); if (!codeMirrorInstance) { return false; } // Get the target line element const lines = codeMirrorInstance.querySelectorAll('.CodeMirror-line'); const targetLineIndex = lineNumber - 1; // Convert to 0-based index if (targetLineIndex >= 0 && targetLineIndex < lines.length) { const targetLine = lines[targetLineIndex]; // Scroll to the line with smooth behavior targetLine.scrollIntoView({ behavior: 'smooth', block: 'center', inline: 'nearest' }); // Add temporary flash effect addTemporaryHighlight(targetLine); return true; } else { return false; } } catch (error) { console.error('Error in jumpToLine:', error); return false; } } function findCodeMirrorForLine(lineNumber, elementIndex) { try { // Strategy 1: Use elementIndex if provided if (typeof elementIndex === 'number') { const codeMirrorElements = document.querySelectorAll('.CodeMirror-scroll'); if (elementIndex >= 0 && elementIndex < codeMirrorElements.length) { return codeMirrorElements[elementIndex]; } } // Strategy 2: Find CodeMirror instance that contains the line const allCodeMirrorElements = document.querySelectorAll('.CodeMirror-scroll'); for (let element of allCodeMirrorElements) { const lines = element.querySelectorAll('.CodeMirror-line'); if (lineNumber <= lines.length) { return element; } } // Strategy 3: Return the first available CodeMirror instance return allCodeMirrorElements[0] || null; } catch (error) { console.error('Error in findCodeMirrorForLine:', error); return null; } } function addTemporaryHighlight(lineElement) { try { if (!lineElement) return; // Add flash animation class lineElement.classList.add('temporary-line-flash'); // Remove the class after animation completes setTimeout(() => { if (lineElement && lineElement.classList) { lineElement.classList.remove('temporary-line-flash'); } }, 1500); // Match the animation duration } catch (error) { console.error('Error in addTemporaryHighlight:', error); } } function setupSummaryClickHandlers() { try { if (!panel) return; // Use event delegation to handle clicks on summary items panel.addEventListener('click', function(event) { // Check if clicked element is an unsafe function item const clickedItem = event.target.closest('.unsafe-function-item'); if (!clickedItem) return; // Prevent event bubbling event.stopPropagation(); // Extract line number from the item content const lineInfo = extractLineInfoFromItem(clickedItem); if (lineInfo) { jumpToLine(lineInfo.line, lineInfo.elementIndex); } }); } catch (error) { console.error('Error in setupSummaryClickHandlers:', error); } } function extractLineInfoFromItem(itemElement) { try { const text = itemElement.textContent || ''; // Extract line number from text like "REPLACE at line 15:23" const lineMatch = text.match(/at line (\d+):(\d+)/); if (lineMatch) { const lineNumber = parseInt(lineMatch[1], 10); const columnNumber = parseInt(lineMatch[2], 10); // Try to find the corresponding elementIndex from detectedFunctions let elementIndex = 0; // Default to first element // Search through detectedFunctions to find matching entry Object.values(detectedFunctions).forEach(functions => { functions.forEach(func => { if (func.line === lineNumber && func.column === columnNumber) { // Try to find elementIndex from highlightedLines highlightedLines.forEach((highlightInfo, lineKey) => { if (highlightInfo.lineNumber === lineNumber) { elementIndex = highlightInfo.elementIndex; } }); } }); }); return { line: lineNumber, column: columnNumber, elementIndex: elementIndex }; } return null; } catch (error) { console.error('Error in extractLineInfoFromItem:', error); return null; } } // Split SQL text into individual statements by semicolon, handling quotes and comments function splitSQLStatements(sqlText) { const statements = []; let currentStatement = ''; let i = 0; let inSingleQuote = false; let inDoubleQuote = false; let inMultiLineComment = false; let inSingleLineComment = false; let statementStartPosition = 0; // Clean the SQL text first - remove zero-width characters and normalize sqlText = sqlText.replace(/[\u200B-\u200D\uFEFF]/g, ''); // Remove zero-width characters sqlText = sqlText.replace(/\u00A0/g, ' '); // Replace non-breaking spaces with regular spaces while (i < sqlText.length) { const char = sqlText[i]; const nextChar = i + 1 < sqlText.length ? sqlText[i + 1] : ''; // Handle multi-line comments /* */ if (!inSingleQuote && !inDoubleQuote && !inSingleLineComment) { if (char === '/' && nextChar === '*') { inMultiLineComment = true; currentStatement += char + nextChar; i += 2; continue; } } if (inMultiLineComment) { currentStatement += char; if (char === '*' && nextChar === '/') { inMultiLineComment = false; currentStatement += nextChar; i += 2; continue; } i++; continue; } // Handle single-line comments -- (improved to handle multiple dashes) if (!inSingleQuote && !inDoubleQuote && !inSingleLineComment) { if (char === '-' && nextChar === '-') { inSingleLineComment = true; currentStatement += char + nextChar; i += 2; continue; } } if (inSingleLineComment) { currentStatement += char; if (char === '\n' || char === '\r') { inSingleLineComment = false; } else if (char === ';') { // Special case: semicolon can end a single-line comment if there's no newline // This handles cases where SQL statements are on the same line inSingleLineComment = false; // Don't increment i here, let the semicolon be processed normally continue; } i++; continue; } // Handle string literals with escape sequence support if (!inMultiLineComment && !inSingleLineComment) { if (char === "'" && !inDoubleQuote) { // Check for escaped quote if (i > 0 && sqlText[i-1] === '\\') { currentStatement += char; i++; continue; } inSingleQuote = !inSingleQuote; currentStatement += char; i++; continue; } else if (char === '"' && !inSingleQuote) { // Check for escaped quote if (i > 0 && sqlText[i-1] === '\\') { currentStatement += char; i++; continue; } inDoubleQuote = !inDoubleQuote; currentStatement += char; i++; continue; } } // Handle semicolon (statement separator) if (!inSingleQuote && !inDoubleQuote && !inMultiLineComment && !inSingleLineComment && char === ';') { currentStatement += char; // Trim and add statement if it's not empty const trimmedStatement = currentStatement.trim(); if (trimmedStatement && trimmedStatement !== ';') { statements.push({ text: trimmedStatement, startPosition: statementStartPosition, endPosition: i + 1 }); } // Reset for next statement - skip whitespace after semicolon currentStatement = ''; i++; // Skip whitespace and newlines to find the start of next statement while (i < sqlText.length && /\s/.test(sqlText[i])) { i++; } statementStartPosition = i; continue; } // Normal character currentStatement += char; i++; } // Add the last statement if it exists (improved handling) const trimmedStatement = currentStatement.trim(); if (trimmedStatement) { statements.push({ text: trimmedStatement, startPosition: statementStartPosition, endPosition: sqlText.length }); } return statements; } // Remove SQL comments from text while preserving line structure function removeComments(sqlText) { let result = ''; let i = 0; let inSingleQuote = false; let inDoubleQuote = false; let inMultiLineComment = false; while (i < sqlText.length) { const char = sqlText[i]; const nextChar = i + 1 < sqlText.length ? sqlText[i + 1] : ''; // Handle string literals (don't process comments inside strings) if (!inMultiLineComment) { if (char === "'" && !inDoubleQuote) { inSingleQuote = !inSingleQuote; result += char; i++; continue; } else if (char === '"' && !inSingleQuote) { inDoubleQuote = !inDoubleQuote; result += char; i++; continue; } } // Skip comment processing if we're inside a string literal if (inSingleQuote || inDoubleQuote) { result += char; i++; continue; } // Handle multi-line comments /* */ if (!inMultiLineComment && char === '/' && nextChar === '*') { inMultiLineComment = true; result += ' '; // Replace with spaces to maintain positioning i += 2; continue; } if (inMultiLineComment) { if (char === '*' && nextChar === '/') { inMultiLineComment = false; result += ' '; // Replace with spaces i += 2; continue; } else if (char === '\n') { result += char; // Preserve line breaks i++; continue; } else { result += ' '; // Replace comment content with space i++; continue; } } // Handle single-line comments -- if (char === '-' && nextChar === '-') { // Replace everything from -- to end of line with spaces while (i < sqlText.length && sqlText[i] !== '\n') { result += ' '; i++; } // Don't increment i here as we want to process the \n normally continue; } // Normal character result += char; i++; } return result; } // Check if a TO_DATE function call matches safe patterns function isSafeToDatePattern(expression) { // Reset all regex patterns before testing SAFE_TO_DATE_PATTERNS.forEach(pattern => { pattern.lastIndex = 0; }); // Test against all safe patterns return SAFE_TO_DATE_PATTERNS.some(pattern => { pattern.lastIndex = 0; // Reset regex state return pattern.test(expression); }); } // Extract complete TO_DATE function call from text around a match position function extractToDateFunctionCall(text, matchIndex) { // Find the start of the function call let start = matchIndex; while (start > 0 && text[start - 1] !== ' ' && text[start - 1] !== '\t' && text[start - 1] !== '\n' && text[start - 1] !== '(' && text[start - 1] !== ',') { start--; } // Find the end of the function call by matching parentheses let parenCount = 0; let end = matchIndex; let foundOpenParen = false; // Move to the opening parenthesis while (end < text.length) { if (text[end] === '(') { foundOpenParen = true; parenCount++; end++; break; } else if (text[end] === ' ' || text[end] === '\t') { end++; } else { end++; } } if (!foundOpenParen) { return text.substring(start, Math.min(start + 50, text.length)); // Fallback } // Find the matching closing parenthesis while (end < text.length && parenCount > 0) { if (text[end] === '(') { parenCount++; } else if (text[end] === ')') { parenCount--; } end++; } return text.substring(start, end).trim(); } // Create regex patterns for detecting unsafe functions function createFunctionRegex(functionName) { // Handle special cases if (functionName === 'SIMILAR TO') { return new RegExp('\\bSIMILAR\\s+TO\\b', 'gi'); } if (functionName === 'TIME BETWEEN') { return new RegExp('\\bTIME\\s+BETWEEN\\b', 'gi'); } if (functionName === 'EXTRACT.*EPOCH') { return new RegExp('\\bEXTRACT\\s*\\([^)]*EPOCH[^)]*\\)', 'gi'); } // Handle POSITION function which can have different syntax: POSITION(x IN y) or POSITION(x,y) if (functionName === 'POSITION') { return new RegExp('\\bPOSITION\\s*\\(', 'gi'); } // Handle INTERVAL function - supports both INTERVAL(...) and INTERVAL 'value' formats if (functionName === 'INTERVAL') { return new RegExp('\\bINTERVAL\\s*(?:\\(|\'|\\d)', 'gi'); } // Handle :: type conversion patterns (e.g., ::DATE, ::TEXT, ::INTEGER) // Allow any non-whitespace character before :: to handle cases like (expression)::date if (functionName.startsWith('::')) { const typeName = functionName.substring(2); // Remove the :: return new RegExp('[^\\s]::\\s*' + typeName.replace(/[.*+?^${}()|[\]\\]/g, '\\$&') + '\\b', 'gi'); } // Standard function pattern: FUNCTION_NAME followed by opening parenthesis return new RegExp('\\b' + functionName.replace(/[.*+?^${}()|[\]\\]/g, '\\$&') + '\\s*\\(', 'gi'); } // Parse comparison expressions and extract only the left side (field being compared) function parseComparisonExpression(condition) { const leftSideExpressions = []; // First, remove any nested SELECT...FROM statements from the condition const cleanedCondition = removeNestedSelectStatements(condition); // Handle BETWEEN operator specially const betweenMatch = cleanedCondition.match(/^(.+?)\s+BETWEEN\s+/i); if (betweenMatch) { leftSideExpressions.push(betweenMatch[1].trim()); return leftSideExpressions; } // Handle IN operator with potential subqueries const inMatch = cleanedCondition.match(/^(.+?)\s+(?:NOT\s+)?IN\s*\(/i); if (inMatch) { leftSideExpressions.push(inMatch[1].trim()); return leftSideExpressions; } // Handle EXISTS operator (left side is empty for EXISTS) if (cleanedCondition.match(/^\s*(?:NOT\s+)?EXISTS\s*\(/i)) { return leftSideExpressions; // No left side for EXISTS } // Handle standard comparison operators (=, >, <, >=, <=, !=, <>) // Use a more robust approach to handle nested parentheses in complex expressions let parenDepth = 0; let inQuotes = false; let quoteChar = ''; let operatorIndex = -1; // Find the comparison operator at the top level (not inside parentheses or quotes) for (let i = 0; i < cleanedCondition.length; i++) { const char = cleanedCondition[i]; // Handle quotes (including escaped quotes) if ((char === '"' || char === "'") && !inQuotes) { inQuotes = true; quoteChar = char; } else if (char === quoteChar && inQuotes) { // Check if it's escaped if (i > 0 && cleanedCondition[i-1] === '\\') { continue; // Skip escaped quote } inQuotes = false; quoteChar = ''; } else if (!inQuotes) { // Handle parentheses if (char === '(') { parenDepth++; } else if (char === ')') { parenDepth--; } else if (parenDepth === 0) { // Check for comparison operators at top level const remaining = cleanedCondition.substring(i); // Include PostgreSQL regex operators: ~, ~*, !~, !~* // Also include LIKE, ILIKE, SIMILAR TO if (remaining.match(/^(>=|<=|<>|!=|!~\*|!~|~\*|~|>|<|=|\s+(?:NOT\s+)?(?:LIKE|ILIKE)\s+|\s+SIMILAR\s+TO\s+)/i)) { operatorIndex = i; break; } } } } if (operatorIndex !== -1) { const leftSide = cleanedCondition.substring(0, operatorIndex).trim(); if (leftSide) { leftSideExpressions.push(leftSide); } } return leftSideExpressions; } // Remove nested SELECT...FROM statements from a condition to avoid detecting unsafe functions within them function removeNestedSelectStatements(condition) { let result = condition; let changed = true; // Keep removing nested SELECT statements until no more are found while (changed) { changed = false; let parenDepth = 0; let inQuotes = false; let quoteChar = ''; let selectStart = -1; for (let i = 0; i < result.length; i++) { const char = result[i]; // Handle quotes if ((char === '"' || char === "'") && !inQuotes) { inQuotes = true; quoteChar = char; } else if (char === quoteChar && inQuotes) { if (i > 0 && result[i-1] === '\\') { continue; // Skip escaped quote } inQuotes = false; quoteChar = ''; } else if (!inQuotes) { // Handle parentheses if (char === '(') { parenDepth++; // Check if this starts a SELECT statement const remaining = result.substring(i + 1).trim(); if (remaining.match(/^SELECT\s+/i) && selectStart === -1) { selectStart = i; } } else if (char === ')') { parenDepth--; // If we're closing a SELECT statement if (selectStart !== -1 && parenDepth === 0) { // Replace the SELECT statement with placeholder const beforeSelect = result.substring(0, selectStart); const afterSelect = result.substring(i + 1); result = beforeSelect + '(SUBQUERY_PLACEHOLDER)' + afterSelect; changed = true; break; } } } } } return result; } // Split WHERE clause into individual conditions, handling nested parentheses function splitWhereConditions(whereClause) { const conditions = []; let current = ''; let parenDepth = 0; let inQuotes = false; let quoteChar = ''; for (let i = 0; i < whereClause.length; i++) { const char = whereClause[i]; const nextChars = whereClause.substr(i, 4).toUpperCase(); // Handle quotes if ((char === '"' || char === "'") && !inQuotes) { inQuotes = true; quoteChar = char; current += char; } else if (char === quoteChar && inQuotes) { inQuotes = false; quoteChar = ''; current += char; } else if (inQuotes) { current += char; } else { // Handle parentheses if (char === '(') { parenDepth++; current += char; } else if (char === ')') { parenDepth--; current += char; } else if (parenDepth === 0 && (nextChars === 'AND ' || nextChars === 'OR ')) { // Found AND/OR at top level if (current.trim()) { conditions.push(current.trim()); } current = ''; i += 3; // Skip past 'AND' or 'OR ' } else { current += char; } } } // Add the last condition if (current.trim()) { conditions.push(current.trim()); } return conditions; } // Remove SELECT...FROM portion of statement to reduce false positives function removeSelectFromPortion(statement) { // Remove SELECT...FROM between content, keep FROM and after return statement.replace(/SELECT[\s\S]*?FROM\s+/i, 'FROM '); } function extractCompleteWhereClauses(statement) { // Remove SELECT...FROM part to reduce false positives let cleanedStatement = removeSelectFromPortion(statement); // Use non-greedy matching to extract WHERE clauses const whereRegex = /\bWHERE\s+([\s\S]*?)(?=\s+(?:GROUP\s+BY|ORDER\s+BY|HAVING|LIMIT|OFFSET|UNION|INTERSECT|EXCEPT|;|$))/gi; const whereClauses = []; let match; while ((match = whereRegex.exec(cleanedStatement)) !== null) { const whereClause = match[1].trim(); if (whereClause) { const parts = splitAtUnmatchedParen(whereClause); whereClauses.push(...parts); } } return whereClauses; } // Helper function: Calculate statement line range boundaries function getStatementLineRange(statement, fullSqlText, lines) { try { // Strategy 1: Use character positions to find line boundaries const startPos = statement.startPosition; const endPos = statement.endPosition; let startLine = 1; let endLine = lines.length; let currentPos = 0; // Find start line by counting characters for (let i = 0; i < lines.length; i++) { const lineText = lines[i].textContent || ''; const lineLength = lineText.length + 1; // +1 for newline character if (currentPos <= startPos && currentPos + lineLength > startPos) { startLine = i + 1; } if (currentPos < endPos && currentPos + lineLength >= endPos) { endLine = i + 1; break; } currentPos += lineLength; } // Strategy 2: Fallback using content matching if position-based fails if (startLine === 1 && endLine === lines.length) { const statementLines = statement.text.split('\n'); const firstStatementLine = statementLines[0].trim(); const lastStatementLine = statementLines[statementLines.length - 1].trim(); if (firstStatementLine) { for (let i = 0; i < lines.length; i++) { const lineText = (lines[i].textContent || '').trim(); if (lineText.includes(firstStatementLine) || firstStatementLine.includes(lineText)) { startLine = i + 1; break; } } } if (lastStatementLine && lastStatementLine !== firstStatementLine) { for (let i = startLine - 1; i < lines.length; i++) { const lineText = (lines[i].textContent || '').trim(); if (lineText.includes(lastStatementLine) || lastStatementLine.includes(lineText)) { endLine = i + 1; break; } } } } // Ensure valid range startLine = Math.max(1, startLine); endLine = Math.min(lines.length, Math.max(startLine, endLine)); return { startLine, endLine }; } catch (error) { console.error('Error calculating statement line range:', error); // Fallback to full range return { startLine: 1, endLine: lines.length }; } } // Optimized function: Extract WHERE clauses with line number information (limited to statement range) function extractWhereClausesWithLineNumbers(statement, fullSqlText, lines) { const whereClauses = extractCompleteWhereClauses(statement.text); const clausesWithLineNumbers = []; // Get the line range for this statement to limit search scope const { startLine, endLine } = getStatementLineRange(statement, fullSqlText, lines); whereClauses.forEach(clause => { const clauseInfo = { text: clause, lineNumbers: [] }; // Only search within the statement's line range for better performance and accuracy for (let lineIndex = startLine - 1; lineIndex < endLine && lineIndex < lines.length; lineIndex++) { const line = lines[lineIndex]; const lineText = line.textContent || ''; const lineNumber = lineIndex + 1; // Check if this line belongs to the current statement and clause // Since we're already within the statement range, we can skip the expensive isLineInStatement check if (isLineMatchingClause(lineText, clause)) { clauseInfo.lineNumbers.push({ lineNumber: lineNumber, lineText: lineText.trim(), lineElement: line }); } } if (clauseInfo.lineNumbers.length > 0) { clausesWithLineNumbers.push(clauseInfo); } }); return clausesWithLineNumbers; } // Optimized function: Extract JOIN ON clauses with line number information (limited to statement range) function extractJoinOnClausesWithLineNumbers(statement, fullSqlText, lines) { const joinOnClauses = extractCompleteJoinOnClauses(statement.text); const clausesWithLineNumbers = []; // Get the line range for this statement to limit search scope const { startLine, endLine } = getStatementLineRange(statement, fullSqlText, lines); joinOnClauses.forEach(clause => { const clauseInfo = { text: clause, lineNumbers: [] }; // Only search within the statement's line range for better performance and accuracy for (let lineIndex = startLine - 1; lineIndex < endLine && lineIndex < lines.length; lineIndex++) { const line = lines[lineIndex]; const lineText = line.textContent || ''; const lineNumber = lineIndex + 1; // Check if this line belongs to the current statement and clause // Since we're already within the statement range, we can skip the expensive isLineInStatement check if (isLineMatchingClause(lineText, clause)) { clauseInfo.lineNumbers.push({ lineNumber: lineNumber, lineText: lineText.trim(), lineElement: line }); } } if (clauseInfo.lineNumbers.length > 0) { clausesWithLineNumbers.push(clauseInfo); } }); return clausesWithLineNumbers; } function splitAtUnmatchedParen(clause) { let results = []; let start = 0; let depth = 0; for (let i = 0; i < clause.length; i++) { const ch = clause[i]; if (ch === "(") { depth++; } else if (ch === ")") { if (depth === 0) { // find single )，end WHERE clause results.push(clause.slice(start, i).trim()); start = i + 1; } else { depth--; } } } const rest = clause.slice(start).trim(); if (rest) results.push(rest); return results; } // Extract complete JOIN ON clauses from a SQL statement using regex function extractCompleteJoinOnClauses(statement) { // Remove SELECT...FROM part to reduce false positives let cleanedStatement = removeSelectFromPortion(statement); //console.log(cleanedStatement); cleanedStatement = cleanedStatement.replace( /\b(LEFT\s+JOIN|RIGHT\s+JOIN|INNER\s+JOIN|JOIN)\s+([\s\S]*?)\s+ON\s+/gi, '$1 TABLE_PLACEHOLDER ON ' ); //console.log(cleanedStatement); // Match various JOIN...ON patterns const joinOnRegex = /\b(INNER\s+JOIN|LEFT\s+(?:OUTER\s+)?JOIN|RIGHT\s+(?:OUTER\s+)?JOIN|FULL\s+(?:OUTER\s+)?JOIN|CROSS\s+JOIN|JOIN)\s+[\w.]+(?:\s+AS\s+\w+)?\s+ON\s+([\s\S]*?)(?=\s+(?:INNER\s+JOIN|LEFT\s+JOIN|RIGHT\s+JOIN|FULL\s+JOIN|CROSS\s+JOIN|JOIN|WHERE|GROUP\s+BY|ORDER\s+BY|HAVING|LIMIT|UNION|;|$))/gi; const joinOnClauses = []; let match; while ((match = joinOnRegex.exec(cleanedStatement)) !== null) { const onCondition = match[2].trim(); if (onCondition) { joinOnClauses.push(onCondition); } } return joinOnClauses; } // Split clause into individual lines while preserving logical completeness function splitClauseIntoLines(clause) { if (!clause) return []; // Split by newlines first const rawLines = clause.split('\n'); const processedLines = []; rawLines.forEach(line => { const trimmedLine = line.trim(); // Skip empty lines and pure comment lines if (!trimmedLine || trimmedLine.startsWith('--')) { return; } // Remove inline comments but keep the main content const cleanedLine = trimmedLine.replace(/--.*$/, '').trim(); if (cleanedLine) { processedLines.push(cleanedLine); } }); return processedLines; } // Enhanced line matching with multiple strategies function isLineMatchingClause(lineText, clause) { const trimmedLine = lineText.trim(); if (!trimmedLine) return false; // Remove comments from the line for comparison const cleanedLine = trimmedLine.replace(/--.*$/, '').trim(); if (!cleanedLine) return false; const clauseLines = splitClauseIntoLines(clause); const filteredClauseLines = clauseLines.filter(line => line.length >= 5); /* // Strategy 1: Direct content matching in the complete clause if (clause.includes(trimmedLine) || clause.includes(cleanedLine)) { return true; } // Strategy 2: Normalized whitespace matching const normalizedLine = cleanedLine.replace(/\s+/g, ' '); const normalizedClause = clause.replace(/\s+/g, ' '); if (normalizedClause.includes(normalizedLine)) { return true; } // Strategy 3: Split clause into lines and check each line for (const clauseLine of clauseLines) { // Direct match if (clauseLine === cleanedLine || clauseLine === trimmedLine) { return true; } // Normalized match const normalizedClauseLine = clauseLine.replace(/\s+/g, ' '); if (normalizedClauseLine === normalizedLine) { return true; } // Partial match - check if the line is contained within a clause line if (clauseLine.includes(cleanedLine) || cleanedLine.includes(clauseLine)) { return true; } // Handle cases where the line might be a subset of a larger expression // For example, line: "user_id = 123" and clauseLine: "AND user_id = 123" if (clauseLine.includes(cleanedLine) && (clauseLine.startsWith('AND ') || clauseLine.startsWith('OR ') || clauseLine.endsWith(' AND') || clauseLine.endsWith(' OR'))) { return true; } }*/ // Strategy 4: Reverse check - see if any clause line is contained in the current line // This handles cases where the line has extra content like comments //console.log(`cleanedLine: ${cleanedLine}`); for (const clauseLine of filteredClauseLines) { if (cleanedLine.includes(clauseLine)) { return true; } } return false; } // Check if a line is within a complete WHERE clause function isLineInCompleteWhereClause(lineText, whereClauses) { const trimmedLine = lineText.trim(); if (!trimmedLine) return false; return whereClauses.some(whereClause => { return isLineMatchingClause(lineText, whereClause); }); } // Check if a line is within a complete JOIN ON clause function isLineInCompleteJoinOnClause(lineText, joinOnClauses) { const trimmedLine = lineText.trim(); if (!trimmedLine) return false; return joinOnClauses.some(joinOnClause => { return isLineMatchingClause(lineText, joinOnClause); }); } // Detect unsafe functions in SQL text (in WHERE and JOIN ON clauses) with accurate line numbers function detectUnsafeFunctions(sqlText, codeMirrorElement = null) { const detected = {}; if (!codeMirrorElement) { return detected; } // Step 1: Split SQL text into individual statements const sqlStatements = splitSQLStatements(sqlText); console.log(`Split SQL into ${sqlStatements.length} statements:`, sqlStatements.map(s => s.text.substring(0, 50) + '...')); // Debug log // Get all CodeMirror lines const lines = codeMirrorElement.querySelectorAll('.CodeMirror-line'); // Step 2: Process each SQL statement separately sqlStatements.forEach((statement, statementIndex) => { // Check if this statement contains any WHERE or JOIN ON patterns const hasRelevantClauses = /\b(WHERE|JOIN[\s\S]*?ON)\b/gi.test(statement.text); if (!hasRelevantClauses) { return; // No relevant clauses found in this statement } // Extract WHERE and JOIN ON clauses with line number information const whereClausesWithLines = extractWhereClausesWithLineNumbers(statement, sqlText, lines); const joinOnClausesWithLines = extractJoinOnClausesWithLineNumbers(statement, sqlText, lines); console.log(`Statement ${statementIndex + 1}: Found ${whereClausesWithLines.length} WHERE clauses and ${joinOnClausesWithLines.length} JOIN ON clauses`); // Process WHERE clauses whereClausesWithLines.forEach(clauseInfo => { clauseInfo.lineNumbers.forEach(lineInfo => { // Remove comments from the line before processing const cleanedLineText = removeComments(lineInfo.lineText); // For WHERE clauses: extract only left-side expressions const expressionsToCheck = extractLeftSideExpressionsFromLine(cleanedLineText); // Check each expression for unsafe functions expressionsToCheck.forEach(expression => { Object.entries(UNSAFE_FUNCTIONS).forEach(([category, functions]) => { functions.forEach(functionName => { const regex = createFunctionRegex(functionName); let match; regex.lastIndex = 0; // Reset regex while ((match = regex.exec(expression)) !== null) { // Special handling for TO_DATE function - check if it matches safe patterns if (functionName === 'TO_DATE') { // Extract the complete TO_DATE function call const toDateCall = extractToDateFunctionCall(expression, match.index); // Check if this TO_DATE call matches any safe patterns if (isSafeToDatePattern(toDateCall)) { //console.log(`Skipping safe TO_DATE pattern: ${toDateCall}`); continue; // Skip this detection as it's a safe pattern } } if (!detected[category]) { detected[category] = []; } // Calculate the actual column position in the original line const expressionStartInLine = cleanedLineText.indexOf(expression); const actualColumn = expressionStartInLine >= 0 ? expressionStartInLine + match.index + 1 : match.index + 1; detected[category].push({ function: functionName, line: lineInfo.lineNumber, column: actualColumn, context: lineInfo.lineText, // Keep original line text for context display statementIndex: statementIndex // Track which statement this detection belongs to }); } }); }); }); }); }); // Process JOIN ON clauses joinOnClausesWithLines.forEach(clauseInfo => { clauseInfo.lineNumbers.forEach(lineInfo => { // Remove comments from the line before processing const cleanedLineText = removeComments(lineInfo.lineText); // For JOIN ON clauses: check the entire line for unsafe functions const expressionsToCheck = [cleanedLineText]; // Check each expression for unsafe functions expressionsToCheck.forEach(expression => { Object.entries(UNSAFE_FUNCTIONS).forEach(([category, functions]) => { functions.forEach(functionName => { const regex = createFunctionRegex(functionName); let match; regex.lastIndex = 0; // Reset regex while ((match = regex.exec(expression)) !== null) { // Special handling for TO_DATE function - check if it matches safe patterns if (functionName === 'TO_DATE') { // Extract the complete TO_DATE function call const toDateCall = extractToDateFunctionCall(expression, match.index); // Check if this TO_DATE call matches any safe patterns if (isSafeToDatePattern(toDateCall)) { //console.log(`Skipping safe TO_DATE pattern: ${toDateCall}`); continue; // Skip this detection as it's a safe pattern } } if (!detected[category]) { detected[category] = []; } // Calculate the actual column position in the original line const expressionStartInLine = cleanedLineText.indexOf(expression); const actualColumn = expressionStartInLine >= 0 ? expressionStartInLine + match.index + 1 : match.index + 1; detected[category].push({ function: functionName, line: lineInfo.lineNumber, column: actualColumn, context: lineInfo.lineText, // Keep original line text for context display statementIndex: statementIndex // Track which statement this detection belongs to }); } }); }); }); }); }); }); return detected; } // Check if a line belongs to a specific SQL statement using improved content matching function isLineInStatement(lineText, fullSqlText, statement) { const trimmedLineText = lineText.trim(); if (!trimmedLineText) return false; // Strategy 1: Check if the line content exists within the statement text if (statement.text.includes(trimmedLineText)) { return true; } // Strategy 2: Try normalized whitespace matching within the statement const normalizedLine = trimmedLineText.replace(/\s+/g, ' '); const normalizedStatement = statement.text.replace(/\s+/g, ' '); if (normalizedStatement.includes(normalizedLine)) { return true; } // Strategy 3: Check for partial matches with key SQL keywords from the line const sqlKeywords = ['SELECT', 'FROM', 'WHERE', 'JOIN', 'ON', 'AND', 'OR', 'CREATE', 'INSERT', 'UPDATE', 'DELETE']; const lineKeywords = sqlKeywords.filter(keyword => trimmedLineText.toUpperCase().includes(keyword)); if (lineKeywords.length > 0) { // If the line contains SQL keywords, check if those keywords exist in the statement const hasMatchingKeywords = lineKeywords.every(keyword => statement.text.toUpperCase().includes(keyword) ); if (hasMatchingKeywords) { return true; } } // Strategy 4: Fallback to position-based matching (improved) let lineIndex = fullSqlText.indexOf(trimmedLineText); if (lineIndex === -1) { lineIndex = fullSqlText.replace(/\s+/g, ' ').indexOf(normalizedLine); } if (lineIndex !== -1) { const inRange = lineIndex >= statement.startPosition && lineIndex < statement.endPosition; return inRange; } return false; } // Extract left-side expressions from a single line of SQL function extractLeftSideExpressionsFromLine(lineText) { const leftSideExpressions = []; // Split the line by common logical operators while preserving the structure const conditions = splitLineConditions(lineText); conditions.forEach(condition => { // Check if condition contains comparison operators (including regex operators) if (/[=<>~]|BETWEEN/i.test(condition)) { // Parse the comparison and extract only the left side const leftSides = parseComparisonExpression(condition); leftSideExpressions.push(...leftSides); } }); return leftSideExpressions; } // Split a line into individual conditions, similar to splitWhereConditions but for single lines function splitLineConditions(lineText) { const conditions = []; let current = ''; let parenDepth = 0; let inQuotes = false; let quoteChar = ''; for (let i = 0; i < lineText.length; i++) { const char = lineText[i]; const nextChars = lineText.substr(i, 4).toUpperCase(); // Handle quotes if ((char === '"' || char === "'") && !inQuotes) { inQuotes = true; quoteChar = char; current += char; } else if (char === quoteChar && inQuotes) { inQuotes = false; quoteChar = ''; current += char; } else if (inQuotes) { current += char; } else { // Handle parentheses if (char === '(') { parenDepth++; current += char; } else if (char === ')') { parenDepth--; current += char; } else if (parenDepth === 0 && (nextChars === 'AND ' || nextChars === 'OR ')) { // Found AND/OR at top level if (current.trim()) { conditions.push(current.trim()); } current = ''; i += 3; // Skip past 'AND' or 'OR ' } else { current += char; } } } // Add the last condition if (current.trim()) { conditions.push(current.trim()); } // If no conditions were found, return the entire line as a single condition if (conditions.length === 0 && lineText.trim()) { conditions.push(lineText.trim()); } return conditions; } // Create summary panel function createSummaryPanel() { if (panel) { panel.remove(); } panel = document.createElement('div'); panel.className = 'unsafe-function-panel'; panel.style.display = panelVisible ? 'block' : 'none'; const header = document.createElement('div'); header.className = 'unsafe-function-panel-header'; header.innerHTML = ` <span>Unsafe Functions</span> <button class="close-btn" onclick="this.parentElement.parentElement.style.display='none'">×</button> `; const content = document.createElement('div'); content.className = 'unsafe-function-panel-content'; const hasDetections = Object.keys(detectedFunctions).length > 0; if (!hasDetections) { content.innerHTML = '<div class="no-unsafe-functions">No unsafe functions detected in SQL queries.</div>'; } else { Object.entries(detectedFunctions).forEach(([category, functions]) => { const categoryDiv = document.createElement('div'); categoryDiv.className = 'unsafe-function-category'; const titleDiv = document.createElement('div'); titleDiv.className = 'unsafe-function-category-title'; titleDiv.textContent = `${category} (${functions.length})`; categoryDiv.appendChild(titleDiv); functions.forEach(func => { const itemDiv = document.createElement('div'); itemDiv.className = 'unsafe-function-item'; itemDiv.innerHTML = ` <strong>${func.function}</strong> at line ${func.line}:${func.column}<br> <small>${func.context}</small> `; categoryDiv.appendChild(itemDiv); }); content.appendChild(categoryDiv); }); } panel.appendChild(header); panel.appendChild(content); document.body.appendChild(panel); // Set up click handlers for jump-to-line functionality (non-intrusive addition) setupSummaryClickHandlers(); } // Create toggle button function createToggleButton() { if (toggleBtn) { toggleBtn.remove(); } toggleBtn = document.createElement('button'); toggleBtn.className = 'toggle-btn'; toggleBtn.textContent = 'Unsafe Functions Detector'; toggleBtn.title = 'Built by ARTS DE' toggleBtn.onclick = () => { panelVisible = !panelVisible; if (panel) { panel.style.display = panelVisible ? 'block' : 'none'; } if (panelVisible) { scanForUnsafeFunctions(); } }; document.body.appendChild(toggleBtn); } // Extract SQL text from CodeMirror element, excluding gutter content function extractSQLText(codeMirrorElement) { // Get all CodeMirror-line elements directly from the original element const lines = codeMirrorElement.querySelectorAll('.CodeMirror-line'); // Extract text from each line and join with newlines to preserve line structure const lineTexts = []; lines.forEach(line => { const lineText = line.textContent || line.innerText || ''; lineTexts.push(lineText); }); // Join lines with newline characters to maintain proper word boundaries return lineTexts.join('\n'); } // Clear existing CSS-only highlights function clearCSSHighlights() { // Remove existing highlight stylesheet if (highlightStyleSheet) { highlightStyleSheet.remove(); highlightStyleSheet = null; } // Remove ALL highlight classes and data attributes from ALL CodeMirror lines const allLines = document.querySelectorAll('.CodeMirror-line'); allLines.forEach(line => { line.classList.remove('has-unsafe-function'); line.removeAttribute('data-unsafe-function'); }); // Remove data attributes from CodeMirror elements const codeMirrorElements = document.querySelectorAll('.CodeMirror-scroll'); codeMirrorElements.forEach(element => { element.removeAttribute('data-unsafe-functions'); }); // Force remove any remaining highlight styles by creating an empty stylesheet const cleanupStyleSheet = document.createElement('style'); cleanupStyleSheet.id = 'unsafe-function-cleanup'; cleanupStyleSheet.textContent = ` .CodeMirror-line { background-color: transparent !important; border-left: none !important; padding-left: 0 !important; } .CodeMirror-line::after { display: none !important; } `; document.head.appendChild(cleanupStyleSheet); // Remove cleanup stylesheet after a brief moment setTimeout(() => { if (cleanupStyleSheet && cleanupStyleSheet.parentNode) { cleanupStyleSheet.remove(); } }, 100); } // Apply robust highlighting with persistence and text selectability function applyCSSOnlyHighlighting(codeMirrorElement, elementIndex) { const fullSqlText = extractSQLText(codeMirrorElement); if (!fullSqlText) return; ////console.log('Full SQL Text:', fullSqlText.substring(0, 200) + '...'); // Debug log // Use the SAME logic as detectUnsafeFunctions to ensure consistency const detectedFunctions = detectUnsafeFunctions(fullSqlText, codeMirrorElement); ////console.log('Detection results:', detectedFunctions); // Debug log // If no unsafe functions detected, don't highlight anything if (Object.keys(detectedFunctions).length === 0) { ////console.log('No unsafe functions detected - skipping highlighting'); // Debug log return; } // Find all text nodes within CodeMirror lines const lines = codeMirrorElement.querySelectorAll('.CodeMirror-line'); // Clear previous highlight tracking for this element highlightedLines.clear(); // Split SQL into statements to get statement-specific clause ranges const sqlStatements = splitSQLStatements(fullSqlText); ////console.log(`Highlighting: Split SQL into ${sqlStatements.length} statements`); // Debug log // Directly highlight lines based on detection results Object.entries(detectedFunctions).forEach(([category, functions]) => { functions.forEach(func => { ////console.log(`Highlighting function: ${func.function} at line ${func.line} (statement ${func.statementIndex + 1})`); // Debug log // Find the specific line by line number (func.line is 1-based, array is 0-based) const targetLineIndex = func.line - 1; if (targetLineIndex >= 0 && targetLineIndex < lines.length) { const line = lines[targetLineIndex]; const lineText = line.textContent || ''; // Get the statement this function belongs to const statement = sqlStatements[func.statementIndex]; if (!statement) { ////console.log(`Statement ${func.statementIndex} not found, skipping highlight`); // Debug log return; } // Verify this line contains the function and is in relevant clause of the correct statement const cleanedLineText = removeComments(lineText); const functionRegex = createFunctionRegex(func.function); // Extract complete WHERE and JOIN ON clauses for this statement const whereClauses = extractCompleteWhereClauses(statement.text); const joinOnClauses = extractCompleteJoinOnClauses(statement.text); if (functionRegex.test(cleanedLineText) && isLineInStatement(lineText, fullSqlText, statement) && isLineInRelevantClause(lineText, statement.text, statement, whereClauses, joinOnClauses)) { ////console.log(`Highlighting line ${func.line}: ${lineText.trim()} for function: ${func.function}`); // Debug log // Apply both CSS class and inline styles for maximum persistence line.classList.add('has-unsafe-function'); // Apply inline styles that are harder to override line.style.setProperty('background-color', 'rgba(255, 235, 59, 0.3)', 'important'); line.style.setProperty('border-left', '3px solid #ff9800', 'important'); line.style.setProperty('padding-left', '5px', 'important'); line.style.setProperty('user-select', 'text', 'important'); line.style.setProperty('-webkit-user-select', 'text', 'important'); line.style.setProperty('-moz-user-select', 'text', 'important'); line.style.setProperty('-ms-user-select', 'text', 'important'); // Store multiple functions if they exist on the same line const existingFunctions = line.getAttribute('data-unsafe-function') || ''; const functionList = existingFunctions ? existingFunctions.split(',') : []; if (!functionList.includes(func.function)) { functionList.push(func.function); line.setAttribute('data-unsafe-function', functionList.join(',')); } // Store highlight information for persistence tracking const lineKey = `${elementIndex}-${func.line}`; highlightedLines.set(lineKey, { element: line, functions: functionList, lineNumber: func.line, elementIndex: elementIndex }); } else { ////console.log(`Skipping highlight for line ${func.line}: function test=${functionRegex.test(cleanedLineText)}, inStatement=${isLineInStatement(lineText, fullSqlText, statement)}, inRelevantClause=${isLineInRelevantClause(lineText, statement.text, null)}`); // Debug log } } }); }); // Generate CSS rules for highlighting generateHighlightCSS(); // Start highlight protection if not already running startHighlightProtection(); } // Note: findRelevantClauseRanges method removed - we now use semantic detection // instead of position-based range matching for better accuracy and maintainability // Check if a line is within a relevant clause (WHERE or JOIN ON) using content-based semantic detection function isLineInRelevantClause(lineText, fullSqlText, statement, whereClauses, joinOnClauses) { const cleanedLineText = removeComments(lineText.trim()); if (!cleanedLineText) return false; // Method 1: Complete clause matching (more accurate) if (whereClauses && isLineInCompleteWhereClause(lineText, whereClauses)) { return true; } if (joinOnClauses && isLineInCompleteJoinOnClause(lineText, joinOnClauses)) { return true; } // Method 2: Fallback to semantic detection if (isWhereConditionLine(cleanedLineText)) { return true; } if (isJoinOnConditionLine(cleanedLineText)) { return true; } return false; } // Check if a line contains WHERE condition expressions function isWhereConditionLine(lineText) { const upperLineText = lineText.toUpperCase(); // Skip if this looks like a SELECT statement or FROM clause if (upperLineText.includes('SELECT ') || upperLineText.includes('FROM ')) { // But allow if it's clearly a WHERE condition with SELECT in a subquery if (!upperLineText.includes('WHERE ')) { return false; } } // Skip if this is obviously a JOIN ON context if (isObviousJoinOnContext(lineText)) { return false; } // Skip if this is part of a CASE expression if (isCaseExpressionLine(lineText)) { return false; } // Skip if this is part of CREATE TABLE or other DDL statements if (isDDLContextLine(lineText)) { return false; } // Check for comparison operators const hasComparisonOperators = /[=<>!~]|BETWEEN|IN\s*\(|EXISTS\s*\(|LIKE|ILIKE|SIMILAR\s+TO/i.test(lineText); // Check for logical operators (but not in SELECT context) const hasLogicalOperators = /\b(AND|OR)\b/i.test(lineText) && !upperLineText.includes('SELECT '); // Check for WHERE keyword const hasWhereKeyword = /\bWHERE\b/i.test(lineText); // Check for common WHERE condition patterns const hasConditionPatterns = /\b(TRUNC|TO_DATE|EXTRACT|DATE_PART|CAST)\s*\(/i.test(lineText); const isWhereCondition = hasComparisonOperators || hasLogicalOperators || hasWhereKeyword || hasConditionPatterns; return isWhereCondition; } // Check if a line is obviously in JOIN ON context function isObviousJoinOnContext(lineText) { const upperLineText = lineText.toUpperCase(); // Direct JOIN keywords with ON in the same line if (/\b(INNER\s+JOIN|LEFT\s+(?:OUTER\s+)?JOIN|RIGHT\s+(?:OUTER\s+)?JOIN|FULL\s+(?:OUTER\s+)?JOIN|CROSS\s+JOIN|JOIN)\b.*\bON\b/i.test(lineText)) { return true; } // Lines that start with JOIN keywords if (/^\s*(INNER\s+JOIN|LEFT\s+(?:OUTER\s+)?JOIN|RIGHT\s+(?:OUTER\s+)?JOIN|FULL\s+(?:OUTER\s+)?JOIN|CROSS\s+JOIN|JOIN)\b/i.test(lineText)) { return true; } // Lines that contain table.column = table.column patterns (typical JOIN conditions) // This pattern is more specific than general comparisons and strongly suggests JOIN context if (/\b\w+\.\w+\s*[=<>!]\s*\w+\.\w+\b/i.test(lineText)) { // Additional check: if the line also contains JOIN or ON keywords, it's definitely JOIN context if (/\b(JOIN|ON)\b/i.test(lineText)) { return true; } // Even without explicit JOIN/ON keywords, table.column = table.column is typically JOIN // But be more conservative - only if it doesn't contain WHERE keyword if (!upperLineText.includes('WHERE')) { return true; } } return false; } // Check if a line is part of a CASE expression function isCaseExpressionLine(lineText) { const upperLineText = lineText.toUpperCase().trim(); // Direct CASE keywords if (/\b(CASE|WHEN|THEN|ELSE|END)\b/i.test(lineText)) { return true; } // Lines that are likely part of CASE expression context // Look for patterns like "WHEN condition" or "AND condition" following CASE if (/^\s*(WHEN\s+|AND\s+|OR\s+).*[=<>!~]/i.test(lineText)) { return true; } // Lines with comparison operators that start with logical operators (likely CASE conditions) if (/^\s*(AND|OR)\s+.*[=<>!~]/i.test(lineText)) { return true; } return false; } // Check if a line is in DDL (Data Definition Language) context function isDDLContextLine(lineText) { const upperLineText = lineText.toUpperCase(); // DDL statement keywords if (/\b(CREATE\s+TABLE|ALTER\s+TABLE|DROP\s+TABLE|CREATE\s+INDEX|CREATE\s+VIEW)\b/i.test(lineText)) { return true; } // Column definition patterns in CREATE TABLE if (/\b(DISTKEY|SORTKEY|ENCODE|NOT\s+NULL|PRIMARY\s+KEY|FOREIGN\s+KEY)\b/i.test(lineText)) { return true; } // Table constraint patterns if (/^\s*--.*\b(table|column|constraint)\b/i.test(lineText)) { return true; } return false; } // Check if a line contains JOIN ON condition expressions function isJoinOnConditionLine(lineText) { const upperLineText = lineText.toUpperCase(); // Check for JOIN ON keywords const hasJoinOnKeywords = /\b(JOIN|ON)\b/i.test(lineText); // Check for table join patterns (table.column = table.column) const hasJoinPatterns = /\w+\.\w+\s*[=<>!]\s*\w+\.\w+/i.test(lineText); // Check for comparison operators in JOIN context const hasComparisonInJoin = hasJoinOnKeywords && /[=<>!~]/i.test(lineText); const isJoinOnCondition = hasJoinOnKeywords || hasJoinPatterns || hasComparisonInJoin; return isJoinOnCondition; } // Generate dynamic CSS rules for highlighting unsafe functions function generateHighlightCSS() { // Remove existing highlight stylesheet if (highlightStyleSheet) { highlightStyleSheet.remove(); } // Create new stylesheet for highlights highlightStyleSheet = document.createElement('style'); highlightStyleSheet.id = 'unsafe-function-highlights'; // Create CSS rules that highlight lines containing unsafe functions let cssRules = ` .CodeMirror-line.has-unsafe-function { background: linear-gradient(135deg, rgba(245, 158, 11, 0.15) 0%, rgba(251, 191, 36, 0.1) 100%) !important; border-left: 4px solid #f59e0b !important; padding-left: 8px !important; user-select: text !important; -webkit-user-select: text !important; -moz-user-select: text !important; -ms-user-select: text !important; position: relative !important; transition: all 0.2s cubic-bezier(0.4, 0, 0.2, 1) !important; } .CodeMirror-line.has-unsafe-function:hover { background: linear-gradient(135deg, rgba(245, 158, 11, 0.25) 0%, rgba(251, 191, 36, 0.2) 100%) !important; border-left-color: #d97706 !important; box-shadow: 0 2px 4px -1px rgba(0, 0, 0, 0.1), 0 1px 2px -1px rgba(0, 0, 0, 0.06) !important; } `; // Add specific highlighting for each unsafe function type ALL_UNSAFE_FUNCTIONS.forEach(functionName => { const escapedName = functionName.replace(/[.*+?^${}()|[\]\\]/g, '\\$&'); cssRules += ` .CodeMirror-line[data-unsafe-function*="${functionName}"] { position: relative; } .CodeMirror-line[data-unsafe-function*="${functionName}"]::after { content: "⚠️ ${functionName}"; position: absolute; right: 12px; top: 50%; transform: translateY(-50%); background: linear-gradient(135deg, #dc2626 0%, #b91c1c 100%); color: white; padding: 3px 8px; border-radius: 6px; font-size: 11px; font-weight: 600; font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif; pointer-events: none; z-index: 10; box-shadow: 0 2px 4px -1px rgba(0, 0, 0, 0.1), 0 1px 2px -1px rgba(0, 0, 0, 0.06); opacity: 0.9; transition: all 0.2s cubic-bezier(0.4, 0, 0.2, 1); } .CodeMirror-line[data-unsafe-function*="${functionName}"]:hover::after { opacity: 1; transform: translateY(-50%) scale(1.05); } `; }); highlightStyleSheet.textContent = cssRules; document.head.appendChild(highlightStyleSheet); } // Main scanning function function scanForUnsafeFunctions() { if (isScanning) { return; // Prevent concurrent scans } isScanning = true; detectedFunctions = {}; try { // Clear existing CSS highlights clearCSSHighlights(); // Find all CodeMirror-scroll elements (top-level containers only to avoid duplicate scanning) const codeMirrorElements = document.querySelectorAll('.CodeMirror-scroll'); codeMirrorElements.forEach((element, index) => { const sqlText = extractSQLText(element); if (sqlText && sqlText.trim()) { // Pass the CodeMirror element to detectUnsafeFunctions for accurate line numbers const detected = detectUnsafeFunctions(sqlText, element); // Merge detected functions with comprehensive deduplication Object.entries(detected).forEach(([category, functions]) => { if (!detectedFunctions[category]) { detectedFunctions[category] = []; } // Add all functions from this element functions.forEach(func => { detectedFunctions[category].push(func); }); }); // Apply CSS-only highlighting to WHERE/AND clauses only applyCSSOnlyHighlighting(element, index); } }); // Apply comprehensive deduplication to the final results detectedFunctions = deduplicateDetections(detectedFunctions); createSummaryPanel(); } finally { isScanning = false; } } // Highlight protection system - restores lost highlights function startHighlightProtection() { if (highlightProtectionInterval) { return; // Already running } highlightProtectionInterval = setInterval(() => { if (!panelVisible || highlightedLines.size === 0) { return; } // Check each highlighted line and restore if needed highlightedLines.forEach((highlightInfo, lineKey) => { const { element, functions, lineNumber, elementIndex } = highlightInfo; // Check if element still exists in DOM if (!document.contains(element)) { highlightedLines.delete(lineKey); return; } // Check if highlight styles are still applied const hasClass = element.classList.contains('has-unsafe-function'); const hasInlineStyle = element.style.backgroundColor && element.style.backgroundColor.includes('255, 235, 59'); if (!hasClass || !hasInlineStyle) { // Restore CSS class element.classList.add('has-unsafe-function'); // Restore inline styles element.style.setProperty('background-color', 'rgba(255, 235, 59, 0.3)', 'important'); element.style.setProperty('border-left', '3px solid #ff9800', 'important'); element.style.setProperty('padding-left', '5px', 'important'); element.style.setProperty('user-select', 'text', 'important'); element.style.setProperty('-webkit-user-select', 'text', 'important'); element.style.setProperty('-moz-user-select', 'text', 'important'); element.style.setProperty('-ms-user-select', 'text', 'important'); // Restore data attribute element.setAttribute('data-unsafe-function', functions.join(',')); } }); }, 500); // Check every 500ms } // Handle click events on CodeMirror lines to prevent highlight loss function setupClickProtection() { document.addEventListener('click', function(event) { const clickedLine = event.target.closest('.CodeMirror-line'); if (clickedLine && clickedLine.classList.contains('has-unsafe-function')) { // Schedule highlight restoration after a short delay setTimeout(() => { if (clickedLine.classList.contains('has-unsafe-function')) { // Ensure inline styles are still applied clickedLine.style.setProperty('background-color', 'rgba(255, 235, 59, 0.3)', 'important'); clickedLine.style.setProperty('border-left', '3px solid #ff9800', 'important'); clickedLine.style.setProperty('padding-left', '5px', 'important'); clickedLine.style.setProperty('user-select', 'text', 'important'); clickedLine.style.setProperty('-webkit-user-select', 'text', 'important'); clickedLine.style.setProperty('-moz-user-select', 'text', 'important'); clickedLine.style.setProperty('-ms-user-select', 'text', 'important'); } }, 50); } }, true); // Use capture phase to catch events early } // Debounced scan function function debouncedScan() { if (scanTimeout) { clearTimeout(scanTimeout); } scanTimeout = setTimeout(() => { if (panelVisible && !isScanning) { scanForUnsafeFunctions(); } }, 300); } // Initialize the script function initialize() { createToggleButton(); setupClickProtection(); } // Wait for page to be ready if (document.readyState === 'loading') { document.addEventListener('DOMContentLoaded', initialize); } else { initialize(); } })();
