MySQL ReadOnly MCP Server

Instructions

Input Schema

Input Schema (JSON Schema)

Implementation Reference

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

  Main handler logic for 'mysql_query' tool: extracts query arg, validates read-only with isReadOnlyQuery, adds auto LIMIT, executes via MySQLConnection.executeQuery, returns 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:35-44 (schema)

  Input schema definition for 'mysql_query' tool: object with required 'query' string property.
  inputSchema: { type: 'object', properties: { query: { type: 'string', description: 'SQL query to execute (SELECT statements only)', }, }, required: ['query'], },
• src/index.ts:32-45 (registration)

  Tool registration in listTools handler: defines name, description, and schema for 'mysql_query'.
  { 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/mysql-connection.ts:57-81 (helper)

  Helper function in MySQLConnection class that executes the SQL query using a connection pool and returns rows and fields.
  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(); } } }
• src/utils/sql-validator.ts:4-76 (helper)

  Security helper: validates SQL query is read-only SELECT, blocks dangerous keywords, multiple statements, 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; }