Enhanced MCP MSSQL Server

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