MySQL ReadOnly MCP Server

Instructions

Implementation Reference

• src/index.ts:102-127 (handler)

  Handler for the 'mysql_query' tool. Validates the query is read-only using isReadOnlyQuery, adds an automatic LIMIT clause with addAutoLimit, executes the query via MySQLConnection.executeQuery, and returns the JSON-formatted results.

  case 'mysql_query': {
    const { query } = args as { query: string };
  
    // Validate that it's a read-only query
    if (!isReadOnlyQuery(query)) {
      throw new Error('Only SELECT statements are allowed for security reasons');
    }
  
    // Apply automatic LIMIT to prevent large result sets
    const safeQuery = addAutoLimit(query);
  
    // Log if LIMIT was added for transparency
    if (safeQuery !== query.trim()) {
      console.error(`Added automatic LIMIT to query for safety: ${query}`);
    }
  
    const result = await mysqlConnection.executeQuery(safeQuery);
    return {
      content: [
        {
          type: 'text',
          text: JSON.stringify(result, null, 2),
        },
      ],
    };
  }

• src/index.ts:32-45 (registration)

  Registration of the mysql_query tool in the ListTools response, including name, description, and input schema definition.

  {
    name: 'mysql_query',
    description: 'Execute a read-only SQL query on MySQL database',
    inputSchema: {
      type: 'object',
      properties: {
        query: {
          type: 'string',
          description: 'SQL query to execute (SELECT statements only)',
        },
      },
      required: ['query'],
    },
  },

• src/utils/sql-validator.ts:4-76 (helper)

  Helper function isReadOnlyQuery used in the handler to validate that the provided SQL query is safe (SELECT only, no dangerous keywords, no injection patterns).

  export function isReadOnlyQuery(query: string): boolean {
    const trimmedQuery = query.trim();
  
    // Check if query starts with SELECT (case-insensitive)
    if (!trimmedQuery.toUpperCase().startsWith('SELECT')) {
      return false;
    }
  
    // Additional security checks
  
    // 1. Check for multiple statements using semicolons
    const semicolonCount = (trimmedQuery.match(/;/g) || []).length;
    if (semicolonCount > 1) {
      return false;
    }
  
    // 2. If there's a semicolon, ensure nothing follows it
    if (semicolonCount === 1) {
      const parts = trimmedQuery.split(';');
      // If there's content after the semicolon, reject
      if (parts.length > 2 || (parts.length === 2 && parts[1].trim().length > 0)) {
        return false;
      }
    }
  
    // 3. Check for dangerous SQL keywords (case-insensitive)
    const dangerousKeywords = [
      'DROP', 'DELETE', 'UPDATE', 'INSERT', 'CREATE', 'ALTER',
      'TRUNCATE', 'EXEC', 'EXECUTE', 'UNION', 'MERGE', 'REPLACE',
      'CALL', 'DO', 'HANDLER', 'LOAD', 'RENAME', 'GRANT', 'REVOKE'
    ];
  
    const upperQuery = trimmedQuery.toUpperCase();
    for (const keyword of dangerousKeywords) {
      // Check if dangerous keyword appears after SELECT (not in string literals)
      const regex = new RegExp(`\\b${keyword}\\b`, 'i');
      if (regex.test(upperQuery)) {
        return false;
      }
    }
  
    // 4. Check for SQL injection patterns
    const injectionPatterns = [
      /--/, // SQL comments
      /\/\*/, // Multi-line comment start
      /\*\//, // Multi-line comment end
      /xp_/, // SQL Server extended procedures
      /sp_/, // SQL Server stored procedures
    ];
  
    for (const pattern of injectionPatterns) {
      if (pattern.test(trimmedQuery)) {
        return false;
      }
    }
  
    // 5. Check for MySQL-specific dangerous functions and clauses
    const dangerousPatterns = [
      { pattern: /\bLOAD_FILE\s*\(/i, name: 'LOAD_FILE' },
      { pattern: /\bINTO\s+OUTFILE\b/i, name: 'INTO OUTFILE' },
      { pattern: /\bINTO\s+DUMPFILE\b/i, name: 'INTO DUMPFILE' },
      { pattern: /\bSYSTEM\s*\(/i, name: 'SYSTEM' },
      { pattern: /\bSLEEP\s*\(/i, name: 'SLEEP' }
    ];
  
    for (const { pattern, name } of dangerousPatterns) {
      if (pattern.test(trimmedQuery)) {
        return false;
      }
    }
  
    return true;
  }

• src/utils/sql-validator.ts:79-105 (helper)

  Helper function addAutoLimit used in the handler to automatically append a LIMIT clause to queries without one, preventing large result sets.

  export function addAutoLimit(query: string, defaultLimit: number = 1000): string {
    const trimmedQuery = query.trim();
    const upperQuery = trimmedQuery.toUpperCase();
  
    // Don't add LIMIT if query already has one
    if (upperQuery.includes(' LIMIT ')) {
      return trimmedQuery;
    }
  
    // Don't add LIMIT to COUNT(*) queries or other aggregate functions without LIMIT
    if (/COUNT\s*\([^)]+\)/i.test(trimmedQuery)) {
      return trimmedQuery;
    }
  
    // Don't add LIMIT to EXISTS or NOT EXISTS subqueries
    if (/EXISTS\s*\(/i.test(trimmedQuery) || /NOT\s+EXISTS\s*\(/i.test(trimmedQuery)) {
      return trimmedQuery;
    }
  
    // Don't add LIMIT if query ends with a semicolon (handle it gracefully)
    if (trimmedQuery.endsWith(';')) {
      return trimmedQuery.slice(0, -1) + ` LIMIT ${defaultLimit};`;
    }
  
    // Add LIMIT clause
    return `${trimmedQuery} LIMIT ${defaultLimit}`;
  }

• src/mysql-connection.ts:57-81 (helper)

  Core query execution method in MySQLConnection class, called by the mysql_query handler to run the validated SQL query using a connection pool.

  async executeQuery(query: string): Promise<QueryResult> {
    if (!this.pool) {
      throw new Error('MySQL connection pool not initialized');
    }
  
    let connection: mysql.PoolConnection | undefined;
    try {
      // Get connection from pool
      connection = await this.pool.getConnection();
      const [rows, fields] = await connection.execute(query);
  
      return {
        rows: Array.isArray(rows) ? rows : [],
        fields: fields || undefined,
      };
    } catch (error) {
      const errorMessage = error instanceof Error ? error.message : 'Unknown query error';
      throw new Error(`Query execution failed: ${errorMessage}`);
    } finally {
      // Always release the connection back to the pool
      if (connection) {
        connection.release();
      }
    }
  }