mssql_validate_query

Instructions

Implementation Reference

• src/index.ts:782-844 (handler)

  Main handler function for mssql_validate_query tool. Performs security validation via SecurityValidator, adds performance warnings and optimization suggestions without executing the query or requiring a database connection.

  private async handleValidateQuery(args: any) {
    const { query, includeOptimizations = false } = args;
  
    try {
      const validation = this.securityValidator.validateQuery(query);
      const sanitizedQuery = this.securityValidator.sanitizeQuery(query);
      
      const result: any = {
        isValid: validation.isValid,
        originalQuery: query,
        sanitizedQuery: sanitizedQuery,
        errors: validation.errors,
        warnings: [],
        suggestions: []
      };
  
      // Basic query analysis
      const trimmedQuery = query.trim().toLowerCase();
      
      // Check for potential performance issues
      if (trimmedQuery.includes('select *')) {
        result.warnings.push('Using SELECT * may impact performance - consider specifying specific columns');
      }
      
      if (trimmedQuery.includes('like \'%') || trimmedQuery.includes('like "%')) {
        result.warnings.push('Leading wildcard in LIKE clause may cause full table scan');
      }
  
      if (!trimmedQuery.includes('where') && (trimmedQuery.includes('update') || trimmedQuery.includes('delete'))) {
        result.errors.push('UPDATE/DELETE statements without WHERE clause are dangerous');
        result.isValid = false;
      }
  
      if (includeOptimizations) {
        // Basic optimization suggestions
        if (trimmedQuery.includes('order by') && !trimmedQuery.includes('top')) {
          result.suggestions.push('Consider adding TOP clause when using ORDER BY for better performance');
        }
      }
  
      return {
        content: [
          {
            type: 'text',
            text: JSON.stringify(result, null, 2),
          },
        ],
      };
    } catch (error) {
      return {
        content: [
          {
            type: 'text',
            text: JSON.stringify({
              isValid: false,
              error: error instanceof Error ? error.message : String(error)
            }, null, 2),
          },
        ],
        isError: true,
      };
    }
  }

• src/index.ts:399-406 (schema)

  Input schema definition for the mssql_validate_query tool, specifying query string (required) and optional optimizations flag.

  inputSchema: {
    type: 'object',
    properties: {
      query: { type: 'string', description: 'SQL query to validate' },
      includeOptimizations: { type: 'boolean', description: 'Include optimization suggestions (default: false)', default: false },
    },
    required: ['query'],
  },

• src/index.ts:396-407 (registration)

  Tool registration in the ListToolsRequestSchema handler's tools array.

  {
    name: 'mssql_validate_query',
    description: 'Validate a SQL query for security and syntax without executing it',
    inputSchema: {
      type: 'object',
      properties: {
        query: { type: 'string', description: 'SQL query to validate' },
        includeOptimizations: { type: 'boolean', description: 'Include optimization suggestions (default: false)', default: false },
      },
      required: ['query'],
    },
  },

• src/index.ts:451-452 (registration)

  Dispatcher switch case in CallToolRequestSchema handler that routes to the tool handler.

  case 'mssql_validate_query':
    return await this.handleValidateQuery(args);

• src/index.ts:89-150 (helper)

  SecurityValidator class providing validateQuery and sanitizeQuery methods used by the handler for security checks.

  class SecurityValidator {
    private config: SecurityConfig;
  
    constructor(config: SecurityConfig) {
      this.config = config;
    }
  
    validateQuery(query: string): { isValid: boolean; errors: string[] } {
      const errors: string[] = [];
  
      if (!this.config.enableQueryValidation) {
        return { isValid: true, errors: [] };
      }
  
      // Check query length
      if (query.length > this.config.maxQueryLength) {
        errors.push(`Query exceeds maximum length of ${this.config.maxQueryLength} characters`);
      }
  
      // Check for blocked keywords
      const lowercaseQuery = query.toLowerCase();
      for (const keyword of this.config.blockedKeywords) {
        if (lowercaseQuery.includes(keyword.toLowerCase())) {
          errors.push(`Query contains blocked keyword: ${keyword}`);
        }
      }
  
      // Check for dangerous patterns
      const dangerousPatterns = [
        /;\s*(drop|truncate|alter)\s+/i,
        /union\s+.*select/i,
        /\/\*.*\*\//g, // Block comments (potential SQL injection)
        /--.*$/gm,     // Line comments in suspicious contexts
        /xp_[\w]+/i,
        /sp_[\w]+/i,
      ];
  
      for (const pattern of dangerousPatterns) {
        if (pattern.test(query)) {
          errors.push(`Query contains potentially dangerous pattern: ${pattern.source}`);
        }
      }
  
      // Validate allowed operations
      if (this.config.allowedOperations.length > 0) {
        const firstWord = query.trim().split(/\s+/)[0]?.toUpperCase();
        if (firstWord && !this.config.allowedOperations.includes(firstWord)) {
          errors.push(`Operation '${firstWord}' is not allowed. Allowed operations: ${this.config.allowedOperations.join(', ')}`);
        }
      }
  
      return { isValid: errors.length === 0, errors };
    }
  
    sanitizeQuery(query: string): string {
      // Basic sanitization
      return query
        .replace(/--.*$/gm, '') // Remove line comments
        .replace(/\/\*.*?\*\//g, '') // Remove block comments
        .trim();
    }
  }