read_data
Execute SELECT queries on Microsoft SQL Server databases to retrieve data without destructive operations, enabling secure database interaction through natural language.
Instructions
Executes a SELECT query on an MSSQL Database table. The query must start with SELECT and cannot contain any destructive SQL operations for security reasons.
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| query | Yes | SQL SELECT query to execute (must start with SELECT and cannot contain destructive operations). Example: SELECT * FROM movies WHERE genre = 'comedy' |
Implementation Reference
- src/tools/ReadDataTool.ts:203-255 (handler)The `run` method implements the core logic of the read_data tool: validates the input SQL query for security, executes SELECT query on MSSQL database, sanitizes results, and returns formatted response with success status.async run(params: any) { try { const { query } = params; // Validate the query for security issues const validation = this.validateQuery(query); if (!validation.isValid) { console.warn(`Security validation failed for query: ${query.substring(0, 100)}...`); return { success: false, message: `Security validation failed: ${validation.error}`, error: 'SECURITY_VALIDATION_FAILED' }; } // Log the query for audit purposes (in production, consider more secure logging) console.log(`Executing validated SELECT query: ${query.substring(0, 200)}${query.length > 200 ? '...' : ''}`); // Execute the query const request = new sql.Request(); const result = await request.query(query); // Sanitize the result const sanitizedData = this.sanitizeResult(result.recordset); return { success: true, message: `Query executed successfully. Retrieved ${sanitizedData.length} record(s)${ result.recordset.length !== sanitizedData.length ? ` (limited from ${result.recordset.length} total records)` : '' }`, data: sanitizedData, recordCount: sanitizedData.length, totalRecords: result.recordset.length }; } catch (error) { console.error("Error executing query:", error); // Don't expose internal error details to prevent information leakage const errorMessage = error instanceof Error ? error.message : 'Unknown error occurred'; const safeErrorMessage = errorMessage.includes('Invalid object name') ? errorMessage : 'Database query execution failed'; return { success: false, message: `Failed to execute query: ${safeErrorMessage}`, error: 'QUERY_EXECUTION_FAILED' }; } }
- src/tools/ReadDataTool.ts:9-18 (schema)Input schema defining the parameters for the read_data tool, specifying a required 'query' string parameter with description.inputSchema = { type: "object", properties: { query: { type: "string", description: "SQL SELECT query to execute (must start with SELECT and cannot contain destructive operations). Example: SELECT * FROM movies WHERE genre = 'comedy'" }, }, required: ["query"], } as any;
- src/index.ts:109-113 (registration)Registration of read_data tool in the MCP server's listTools handler, conditionally included based on READONLY mode.server.setRequestHandler(ListToolsRequestSchema, async () => ({ tools: isReadOnly ? [listTableTool, readDataTool, describeTableTool] // todo: add searchDataTool to the list of tools available in readonly mode once implemented : [insertDataTool, readDataTool, describeTableTool, updateDataTool, createTableTool, createIndexTool, dropTableTool, listTableTool], // add all new tools here }));
- src/index.ts:123-124 (registration)Dispatch/handling of read_data tool calls in the MCP server's callTool request handler switch statement.case readDataTool.name: result = await readDataTool.run(args);
- src/tools/ReadDataTool.ts:82-162 (helper)Helper method `validateQuery` that performs comprehensive security validation on the input SQL query to prevent injections and ensure only safe SELECT operations.private validateQuery(query: string): { isValid: boolean; error?: string } { if (!query || typeof query !== 'string') { return { isValid: false, error: 'Query must be a non-empty string' }; } // Remove comments and normalize whitespace for analysis const cleanQuery = query .replace(/--.*$/gm, '') // Remove line comments .replace(/\/\*[\s\S]*?\*\//g, '') // Remove block comments .replace(/\s+/g, ' ') // Normalize whitespace .trim(); if (!cleanQuery) { return { isValid: false, error: 'Query cannot be empty after removing comments' }; } const upperQuery = cleanQuery.toUpperCase(); // Must start with SELECT if (!upperQuery.startsWith('SELECT')) { return { isValid: false, error: 'Query must start with SELECT for security reasons' }; } // Check for dangerous keywords in the cleaned query using word boundaries for (const keyword of ReadDataTool.DANGEROUS_KEYWORDS) { // Use word boundary regex to match only complete keywords, not parts of words const keywordRegex = new RegExp(`(^|\\s|[^A-Za-z0-9_])${keyword}($|\\s|[^A-Za-z0-9_])`, 'i'); if (keywordRegex.test(upperQuery)) { return { isValid: false, error: `Dangerous keyword '${keyword}' detected in query. Only SELECT operations are allowed.` }; } } // Check for dangerous patterns using regex for (const pattern of ReadDataTool.DANGEROUS_PATTERNS) { if (pattern.test(query)) { return { isValid: false, error: 'Potentially malicious SQL pattern detected. Only simple SELECT queries are allowed.' }; } } // Additional validation: Check for multiple statements const statements = cleanQuery.split(';').filter(stmt => stmt.trim().length > 0); if (statements.length > 1) { return { isValid: false, error: 'Multiple SQL statements are not allowed. Use only a single SELECT statement.' }; } // Check for suspicious string patterns that might indicate obfuscation if (query.includes('CHAR(') || query.includes('NCHAR(') || query.includes('ASCII(')) { return { isValid: false, error: 'Character conversion functions are not allowed as they may be used for obfuscation.' }; } // Limit query length to prevent potential DoS if (query.length > 10000) { return { isValid: false, error: 'Query is too long. Maximum allowed length is 10,000 characters.' }; } return { isValid: true }; }