execute_query
Execute read-only SQL queries (SELECT, SHOW, DESCRIBE, EXPLAIN) to retrieve data from MySQL databases for analysis and reporting.
Instructions
Execute a read-only SQL query
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| query | Yes | SQL query (only SELECT, SHOW, DESCRIBE, and EXPLAIN statements are allowed) | |
| database | No | Database name (optional, uses default if not specified) |
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'); }