Enhanced MCP MSSQL Server

Instructions

Implementation Reference

• src/index.ts:574-629 (handler)

  The primary handler function for the 'mssql_query' tool. It validates the input query for security, establishes a connection to the MSSQL database, optionally adds a TOP limit clause, executes the query, and returns the results with metadata including row count and execution time.

  private async handleQuery(args: any) {
    const startTime = Date.now();
    this.metrics.totalQueries++;
  
    try {
      const config = ConnectionSchema.parse(args);
      const { query, limit = 1000 } = args;
      
      // Enhanced security validation
      const validation = this.securityValidator.validateQuery(query);
      if (!validation.isValid) {
        throw new MCPError(
          `Query validation failed: ${validation.errors.join(', ')}`,
          'QUERY_VALIDATION_ERROR',
          { errors: validation.errors }
        );
      }
  
      const sanitizedQuery = this.securityValidator.sanitizeQuery(query);
  
      const pool = await this.getConnection(config);
      const request = pool.request();
      
      // Add TOP clause if not already present and limit is specified
      let finalQuery = sanitizedQuery;
      const trimmedQuery = sanitizedQuery.trim().toLowerCase();
      
      if (limit && !trimmedQuery.includes(' top ') && trimmedQuery.startsWith('select')) {
        finalQuery = sanitizedQuery.replace(/^select\s+/i, `SELECT TOP ${Math.min(limit, this.config.security.maxRowsPerQuery)} `);
      }
  
      const result = await request.query(`USE [${config.database}]; ${finalQuery}`);
  
      // Update metrics
      this.metrics.avgExecutionTime = (this.metrics.avgExecutionTime + (Date.now() - startTime)) / this.metrics.totalQueries;
  
      return {
        content: [
          {
            type: 'text',
            text: JSON.stringify({
              server: config.server,
              database: config.database,
              query: finalQuery,
              rowCount: result.recordset.length,
              executionTime: Date.now() - startTime,
              data: result.recordset,
            }, null, 2),
          },
        ],
      };
    } catch (error) {
      this.metrics.failedQueries++;
      throw error;
    }
  }

• src/index.ts:323-341 (registration)

  Tool registration in the ListToolsRequestSchema handler, defining the name, description, and input schema for 'mssql_query'.

  {
    name: 'mssql_query',
    description: 'Execute a read-only SELECT query',
    inputSchema: {
      type: 'object',
      properties: {
        server: { type: 'string', description: 'MSSQL Server hostname or IP address' },
        port: { type: 'number', description: 'Port number (default: 1433)', default: 1433 },
        user: { type: 'string', description: 'Username for authentication' },
        password: { type: 'string', description: 'Password for authentication' },
        database: { type: 'string', description: 'Database name' },
        query: { type: 'string', description: 'SQL SELECT query to execute' },
        limit: { type: 'number', description: 'Maximum number of rows to return (default: 100)', default: 100 },
        encrypt: { type: 'boolean', description: 'Use encrypted connection (default: true)', default: true },
        trustServerCertificate: { type: 'boolean', description: 'Trust server certificate (default: true)', default: true },
      },
      required: ['server', 'user', 'password', 'database', 'query'],
    },
  },

• src/index.ts:76-84 (schema)

  Zod schema used to validate and parse connection parameters from the tool input arguments.

  const ConnectionSchema = z.object({
    server: z.string().describe('MSSQL Server hostname or IP address'),
    port: z.number().default(1433).describe('Port number (default: 1433)'),
    user: z.string().describe('Username for authentication'),
    password: z.string().describe('Password for authentication'),
    database: z.string().optional().describe('Database name (optional)'),
    encrypt: z.boolean().default(true).describe('Use encrypted connection'),
    trustServerCertificate: z.boolean().default(true).describe('Trust server certificate'),
  });

• src/index.ts:443-444 (registration)

  Dispatch case in the CallToolRequestSchema switch statement that routes 'mssql_query' calls to the handleQuery method.

  case 'mssql_query':
    return await this.handleQuery(args);

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

  SecurityValidator class providing query validation and sanitization used in the mssql_query handler to prevent SQL injection and enforce security policies.

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