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