read_data

Instructions

Implementation Reference

• src/tools/ReadDataTool.ts:203-255 (handler)

  The `run` method of ReadDataTool class that implements the core logic for executing SELECT queries on MSSQL database after validation and sanitization.

  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 definition for the read_data tool, specifying the required 'query' parameter.

  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:129-130 (registration)

  Registration in the tool dispatch switch statement in CallToolRequestSchema handler, where readDataTool is invoked based on name.

  case readDataTool.name:
    result = await readDataTool.run(args);

• src/index.ts:117-118 (registration)

  Inclusion of readDataTool in the list of available tools returned by ListToolsRequestSchema handler.

  ? [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/tools/ReadDataTool.ts:82-162 (helper)

  Helper method `validateQuery` used by the handler to check for dangerous SQL keywords and patterns before execution.

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