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