Detect unsafe functions in SQL queries on DataCentral ETL pages
Od
// ==UserScript== // @name Datanet: SQL Unsafe Function Detector // @namespace http://tampermonkey.net/ // @version 1.3 // @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 */ (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(); // CSS styles for UI and highlighting const styles = ` .unsafe-function-highlight { background-color: #ffeb3b !important; border-radius: 3px !important; padding: 1px 2px !important; font-weight: bold !important; user-select: text !important; -webkit-user-select: text !important; -moz-user-select: text !important; -ms-user-select: text !important; } .unsafe-function-panel { position: fixed; top: 20px; right: 20px; width: 350px; max-height: 500px; background: white; border: 2px solid #ff9800; border-radius: 8px; box-shadow: 0 4px 12px rgba(0,0,0,0.3); z-index: 9999; font-family: Arial, sans-serif; overflow: hidden; } .unsafe-function-panel-header { background: #ff9800; color: white; padding: 12px; font-weight: bold; display: flex; justify-content: space-between; align-items: center; } .unsafe-function-panel-content { padding: 12px; max-height: 400px; overflow-y: auto; } .unsafe-function-category { margin-bottom: 12px; } .unsafe-function-category-title { font-weight: bold; color: #d84315; margin-bottom: 4px; border-bottom: 1px solid #eee; padding-bottom: 2px; } .unsafe-function-item { margin: 4px 0; padding: 4px 8px; background: #fff3e0; border-left: 3px solid #ff9800; font-family: monospace; font-size: 12px; cursor: pointer; transition: background-color 0.2s ease; position: relative; } .unsafe-function-item:hover { background: #ffe0b2; box-shadow: 0 2px 4px rgba(0,0,0,0.1); } .unsafe-function-item::after { content: "📍"; position: absolute; right: 8px; top: 50%; transform: translateY(-50%); opacity: 0; transition: opacity 0.2s ease; font-size: 14px; } .unsafe-function-item:hover::after { opacity: 1; } .temporary-line-flash { animation: lineFlash 1.5s ease-out; } @keyframes lineFlash { 0% { background-color: rgba(255, 193, 7, 0.8) !important; } 50% { background-color: rgba(255, 193, 7, 0.6) !important; } 100% { background-color: rgba(255, 235, 59, 0.3) !important; } } .close-btn { background: none; border: none; color: white; font-size: 18px; cursor: pointer; padding: 0; width: 24px; height: 24px; display: flex; align-items: center; justify-content: center; } .toggle-btn { position: fixed; bottom: 20px; right: 20px; background: #ff9800; color: white; border: none; padding: 10px 15px; border-radius: 6px; cursor: pointer; font-size: 13px; font-weight: bold; z-index: 9998; box-shadow: 0 2px 8px rgba(0,0,0,0.2); transition: all 0.3s ease; } .toggle-btn:hover { background: #f57c00; transform: translateY(-2px); box-shadow: 0 4px 12px rgba(0,0,0,0.3); } .no-unsafe-functions { color: #4caf50; font-style: italic; text-align: center; padding: 20px; } `; // 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 observer = null; // Store observer reference 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; } // 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 :: type conversion patterns (e.g., ::DATE, ::TEXT, ::INTEGER) if (functionName.startsWith('::')) { const typeName = functionName.substring(2); // Remove the :: return new RegExp('\\w+::' + 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; } // 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; } ////console.log('Starting detection on full SQL text:', sqlText.substring(0, 200) + '...'); // Debug log // 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 // Step 2: Process each SQL statement separately sqlStatements.forEach((statement, statementIndex) => { // Note: We now use semantic detection instead of position-based clause ranges // Check if this statement contains any WHERE or JOIN ON patterns const hasRelevantClauses = /\b(WHERE|JOIN\s+\w+\s+ON)\b/i.test(statement.text); if (!hasRelevantClauses) { return; // No relevant clauses found in this statement } // Get all CodeMirror lines const lines = codeMirrorElement.querySelectorAll('.CodeMirror-line'); // Scan each line to see if it belongs to this statement and contains unsafe functions lines.forEach((line, lineIndex) => { const lineText = line.textContent || ''; const lineNumber = lineIndex + 1; // Check if this line is part of the current statement by position if (isLineInStatement(lineText, sqlText, statement)) { // Check if this line is within a relevant clause (WHERE or JOIN ON) using semantic detection if (isLineInRelevantClause(lineText, statement.text, null)) { // Remove comments from the line before processing const cleanedLineText = removeComments(lineText); // Determine which type of clause this line is in using semantic detection const clauseType = getLineClauseType(lineText, statement.text, null); let expressionsToCheck = []; if (clauseType === 'WHERE') { // For WHERE clauses: extract only left-side expressions expressionsToCheck = extractLeftSideExpressionsFromLine(cleanedLineText); } else if (clauseType === 'JOIN_ON') { // For JOIN ON clauses: check the entire line for unsafe functions 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) { 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: lineNumber, column: actualColumn, context: lineText.trim(), // 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 Detected</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 = 'Scan SQL for Unsafe Functions'; 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) { // Clone the element to avoid modifying the original const clone = codeMirrorElement.cloneNode(true); // Remove all gutter-related elements const gutterElements = clone.querySelectorAll('.CodeMirror-gutter-wrapper, .CodeMirror-gutter, .CodeMirror-linenumber, .CodeMirror-gutters'); gutterElements.forEach(el => el.remove()); // Get text content without gutter elements return clone.textContent || clone.innerText || ''; } // 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); if (functionRegex.test(cleanedLineText) && isLineInStatement(lineText, fullSqlText, statement) && isLineInRelevantClause(lineText, statement.text, null)) { ////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, unusedParameter) { const cleanedLineText = removeComments(lineText.trim()); if (!cleanedLineText) return false; // Method 1: Check if this is a WHERE condition line if (isWhereConditionLine(cleanedLineText)) { return true; } // Method 2: Check if this is a JOIN ON condition line 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 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 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; } // Get the type of clause a line is in (WHERE or JOIN_ON) using content-based semantic detection function getLineClauseType(lineText, fullSqlText, unusedParameter) { const cleanedLineText = removeComments(lineText.trim()); if (!cleanedLineText) return null; // Use the same semantic detection logic as isLineInRelevantClause if (isWhereConditionLine(cleanedLineText)) { return 'WHERE'; } if (isJoinOnConditionLine(cleanedLineText)) { return 'JOIN_ON'; } return null; } // 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-color: rgba(255, 235, 59, 0.3) !important; border-left: 3px solid #ff9800 !important; padding-left: 5px !important; user-select: text !important; -webkit-user-select: text !important; -moz-user-select: text !important; -ms-user-select: text !important; } .CodeMirror-line.has-unsafe-function:hover { background-color: rgba(255, 235, 59, 0.5) !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: 10px; top: 0; background: #ff9800; color: white; padding: 2px 6px; border-radius: 3px; font-size: 10px; font-weight: bold; pointer-events: none; z-index: 10; } `; }); 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(); // Set up click protection for highlights setupClickProtection(); // Note: Removed MutationObserver to fix performance issues // Detection now only runs when user clicks the button } // Wait for page to be ready if (document.readyState === 'loading') { document.addEventListener('DOMContentLoaded', initialize); } else { initialize(); } })();
