execute_query
Execute SQL queries to retrieve data from MariaDB/MySQL databases. Supports SELECT, SHOW, DESCRIBE, and EXPLAIN statements for secure read-only database access.
Instructions
Execute a 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/connection.ts:49-114 (handler)Core handler function that executes the SQL query logic for the 'execute_query' tool, managing MariaDB connections, performing query validation, applying timeouts and row limits, and handling errors.export async function executeQuery( sql: string, params: any[] = [], database?: string ): Promise<{ rows: any; fields: mariadb.FieldInfo[] }> { console.error(`[Query] Executing: ${sql}`); // Create connection pool if not already created if (!pool) { console.error("[Setup] Connection pool not found, creating a new one"); pool = createConnectionPool(); } try { // Get connection from pool if (connection) { console.error("[Query] Reusing existing connection"); } else { console.error("[Query] Creating new connection"); connection = await pool.getConnection(); } // Use specific database if provided if (database) { console.error(`[Query] Using database: ${database}`); await connection.query(`USE \`${database}\``); } if (!isAlloowedQuery(sql)) { throw new Error("Query not allowed"); } // Execute query with timeout const [rows, fields] = await connection.query({ metaAsArray: true, namedPlaceholders: true, sql, ...params, 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 with ${JSON.stringify(params)}` ); return { rows: limitedRows, fields }; } catch (error) { if (connection) { connection.release(); console.error("[Query] Connection released with error"); } console.error("[Error] Query execution failed:", error); throw error; } finally { // Release connection back to pool if (connection) { connection.release(); console.error("[Query] Connection released"); } } }
- src/index.ts:104-112 (registration)Registration of the 'execute_query' tool in the MCP server's ListTools response, specifying name, description, and input schema.{ name: "execute_query", description: "Run an arbitrary SQL query", inputSchema: { type: "object", properties: { query: { type: "string" }, database: { type: "string" } }, required: ["query"], }, },
- src/index.ts:107-111 (schema)Input schema for the 'execute_query' tool, requiring a 'query' string parameter and optionally accepting 'database'.inputSchema: { type: "object", properties: { query: { type: "string" }, database: { type: "string" } }, required: ["query"], },
- src/index.ts:147-156 (handler)MCP CallTool request handler case that implements the 'execute_query' tool by extracting parameters, calling the query executor, and returning results as formatted JSON text.case "execute_query": { const qry = args.query as string; if (!qry) throw new McpError(ErrorCode.InvalidParams, "`query` is required"); const db = args.database as string | undefined; const { rows } = await executeQuery(qry, [], db); return { content: [{ type: "text", text: JSON.stringify(rows, null, 2) }], }; }
- src/validators.ts:45-85 (helper)Helper function for validating SQL queries to ensure only allowed commands (based on env config) are executed, preventing dangerous operations in the execute_query tool.export function isAlloowedQuery(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(); const ALLOW_INSERT = process.env.MARIADB_ALLOW_INSERT === "true"; const ALLOW_UPDATE = process.env.MARIADB_ALLOW_UPDATE === "true"; const ALLOW_DELETE = process.env.MARIADB_ALLOW_DELETE === "true"; // Check if query starts with an allowed command const startsWithAllowed = ALLOWED_COMMANDS.some( (cmd) => normalizedQuery.startsWith(cmd + " ") || normalizedQuery === cmd ); const startsWithAllowedNoSpace = normalizedQuery.startsWith("INSERT") && !ALLOW_INSERT; // Check if query contains any disallowed commands const containsDisallowed = DISALLOWED_COMMANDS.some((cmd) => { if (cmd === "INSERT" && !ALLOW_INSERT) { return false; // Skip INSERT if not allowed } if (cmd === "UPDATE" && !ALLOW_UPDATE) { return false; // Skip UPDATE if not allowed } if (cmd === "DELETE" && !ALLOW_DELETE) { return false; // Skip DELETE if not allowed } 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; }