mcp-mysql-lens

Instructions

Implementation Reference

• src/index.ts:196-222 (handler)

  The handler logic for the 'execute_query' tool within the CallToolRequestSchema request handler. Extracts query and database parameters, validates the query is read-only, executes it using the executeQuery helper, and returns JSON-formatted results.

  case "execute_query": {
    console.error('[Tool] Executing execute_query');
    
    const query = request.params.arguments?.query as string;
    const database = request.params.arguments?.database as string | undefined;
    
    if (!query) {
      throw new McpError(ErrorCode.InvalidParams, "Query is required");
    }
    
    // Validate that the query is read-only
    validateQuery(query);
    
    const { rows } = await executeQuery(
      pool,
      query,
      [],
      database
    );
    
    return {
      content: [{
        type: "text",
        text: JSON.stringify(rows, null, 2)
      }]
    };
  }

• src/index.ts:107-123 (registration)

  Registration of the 'execute_query' tool in the ListToolsRequestSchema handler, including name, description, and input schema definition.

  {
    name: "execute_query",
    description: "Execute a read-only SQL query",
    inputSchema: {
      type: "object",
      properties: {
        query: {
          type: "string",
          description: "SQL query (only SELECT, SHOW, DESCRIBE, and EXPLAIN statements are allowed)"
        },
        database: {
          type: "string",
          description: "Database name (optional, uses default if not specified)"
        }
      },
      required: ["query"]
    }

• src/connection.ts:63-109 (helper)

  Core helper function executeQuery that performs the actual database query execution using a MySQL connection pool, with timeout, row limiting, and database switching.

  export async function executeQuery(
    pool: mysql.Pool,
    sql: string,
    params: any[] = [],
    database?: string
  ): Promise<{ rows: any; fields: mysql.FieldPacket[] }> {
    console.error(`[Query] Executing: ${sql}`);
    
    let connection: mysql.PoolConnection | null = null;
    
    try {
      // Get connection from pool
      connection = await pool.getConnection();
      
      // Use specific database if provided
      if (database) {
        console.error(`[Query] Using database: ${database}`);
        await connection.query(`USE \`${database}\``);
      }
      
      // Execute query with timeout
      const [rows, fields] = await Promise.race([
        connection.query(sql, params),
        new Promise<never>((_, reject) => {
          setTimeout(() => reject(new Error('Query timeout')), DEFAULT_TIMEOUT);
        }),
      ]);
      
      // Apply row limit if result is an array
      const limitedRows = Array.isArray(rows) && rows.length > DEFAULT_ROW_LIMIT
        ? rows.slice(0, DEFAULT_ROW_LIMIT)
        : rows;
      
      // Log result summary
      console.error(`[Query] Success: ${Array.isArray(rows) ? rows.length : 1} rows returned`);
      
      return { rows: limitedRows, fields };
    } catch (error) {
      console.error('[Error] Query execution failed:', error);
      throw error;
    } finally {
      // Release connection back to pool
      if (connection) {
        connection.release();
      }
    }
  }

• src/validators.ts:45-92 (schema)

  Input validation schema enforcement for execute_query via validateQuery and supporting isReadOnlyQuery functions, ensuring only read-only SQL queries (SELECT, SHOW, etc.) are permitted.

  export function isReadOnlyQuery(query: string): boolean {
    // Normalize query by removing comments and extra whitespace
    const normalizedQuery = query
      .replace(/--.*$/gm, '') // Remove single-line comments
      .replace(/\/\*[\s\S]*?\*\//g, '') // Remove multi-line comments
      .replace(/\s+/g, ' ') // Normalize whitespace
      .trim()
      .toUpperCase();
    
    // Check if query starts with an allowed command
    const startsWithAllowed = ALLOWED_COMMANDS.some(cmd => 
      normalizedQuery.startsWith(cmd + ' ') || normalizedQuery === cmd
    );
    
    // Check if query contains any disallowed commands
    const containsDisallowed = DISALLOWED_COMMANDS.some(cmd => {
      const regex = new RegExp(`(^|\\s)${cmd}(\\s|$)`);
      return regex.test(normalizedQuery);
    });
    
    // Check for multiple statements (;)
    const hasMultipleStatements = normalizedQuery.includes(';') && 
      !normalizedQuery.endsWith(';');
    
    // Query is read-only if it starts with an allowed command,
    // doesn't contain any disallowed commands, and doesn't have multiple statements
    return startsWithAllowed && !containsDisallowed && !hasMultipleStatements;
  }
  
  /**
   * Validates if a SQL query is safe to execute
   * @param query SQL query to validate
   * @throws Error if the query is not safe
   */
  export function validateQuery(query: string): void {
    console.error('[Validator] Validating query:', query);
    
    if (!query || typeof query !== 'string') {
      throw new Error('Query must be a non-empty string');
    }
    
    if (!isReadOnlyQuery(query)) {
      console.error('[Validator] Query rejected: not read-only');
      throw new Error('Only read-only queries are allowed (SELECT, SHOW, DESCRIBE, EXPLAIN)');
    }
    
    console.error('[Validator] Query validated as read-only');
  }