mssql_validate_query

Validate SQL queries for security vulnerabilities and syntax errors before execution. Includes optional optimization suggestions to improve query performance.

Instructions

Validate a SQL query for security and syntax without executing it

Input Schema

TableJSON Schema

Name	Required	Description	Default
`query`	Yes	SQL query to validate
`includeOptimizations`	No	Include optimization suggestions (default: false)

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();
  }
}

Enhanced MCP MSSQL Server