execute-query
Execute parameterized SQL queries on PostgreSQL databases with built-in safety checks to prevent SQL injection. Supports SELECT, INSERT, UPDATE, and DELETE operations.
Instructions
Execute a parameterized SQL query with safety checks. Supports SELECT, INSERT, UPDATE, DELETE operations with parameter binding to prevent SQL injection.
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| query | Yes | ||
| params | No | ||
| explain | No |
Implementation Reference
- tools/executeQuery.ts:22-136 (handler)Core handler function for 'execute-query' tool: validates input, checks connection and SQL safety (blocks DROP/TRUNCATE/CREATE/INSERT, requires WHERE for UPDATE/DELETE), optionally runs EXPLAIN, executes query via executePostgresQuery, processes numeric results, formats response with query type, timing, row count.export async function executeQuery( rawParams: any ): McpToolResponse { try { // Validate and parse parameters const params = executeQuerySchema.parse(rawParams); // Check database connection status const connectionStatus = getConnectionStatus(); if (connectionStatus.status !== 'connected') { return createDatabaseUnavailableResponse("execute SQL query"); } const { query, params: queryParams, explain } = params; // Basic security checks const trimmedQuery = query.trim().toLowerCase(); // Prevent dangerous operations const dangerousPatterns = [ /drop\s+table/i, /drop\s+database/i, /drop\s+schema/i, /truncate\s+table/i, /alter\s+table.*drop/i, /alter\s+table.*add/i, // Prevent adding columns /create\s+table/i, // Prevent creating tables /insert\s+into/i, // Prevent data insertion for security ]; // Check for DELETE/UPDATE without WHERE clause if (trimmedQuery.startsWith('delete from') && !trimmedQuery.includes(' where ')) { throw new Error(`DELETE without WHERE clause is not allowed for safety.`); } if (trimmedQuery.startsWith('update ') && trimmedQuery.includes(' set ') && !trimmedQuery.includes(' where ')) { throw new Error(`UPDATE without WHERE clause is not allowed for safety.`); } for (const pattern of dangerousPatterns) { if (pattern.test(query)) { throw new Error(`Potentially dangerous SQL operation detected. Query rejected for safety.`); } } const startTime = Date.now(); let results: any[]; let executionPlan: any[] | undefined; // Execute EXPLAIN if requested if (explain) { const explainQuery = `EXPLAIN (FORMAT JSON, ANALYZE, BUFFERS) ${query}`; try { executionPlan = await executePostgresQuery(explainQuery, queryParams); } catch (explainError) { debug("Failed to get execution plan: %o", explainError); // Continue with normal execution even if EXPLAIN fails } } // Execute the main query results = await executePostgresQuery(query, queryParams); const executionTime = Date.now() - startTime; // Convert numeric strings to numbers for better usability results = results.map(row => { const convertedRow: any = {}; for (const [key, value] of Object.entries(row)) { if (typeof value === 'string' && value !== '' && !isNaN(Number(value))) { // Only convert if it's a proper numeric string const numValue = Number(value); if (Number.isInteger(numValue) || !Number.isNaN(numValue)) { convertedRow[key] = numValue; } else { convertedRow[key] = value; } } else { convertedRow[key] = value; } } return convertedRow; }); // Determine query type let queryType = "SELECT"; if (trimmedQuery.startsWith("insert")) { queryType = "INSERT"; } else if (trimmedQuery.startsWith("update")) { queryType = "UPDATE"; } else if (trimmedQuery.startsWith("delete")) { queryType = "DELETE"; } else if (trimmedQuery.startsWith("create")) { queryType = "CREATE"; } else if (trimmedQuery.startsWith("alter")) { queryType = "ALTER"; } const response = { success: true, query_type: queryType, execution_time_ms: executionTime, row_count: results.length, data: results, results: results, // Add for backward compatibility with tests ...(executionPlan && { execution_plan: executionPlan }), executed_at: new Date().toISOString(), }; debug("Query executed successfully: %s rows in %dms", results.length, executionTime); return createMcpSuccessResponse(response); } catch (error) { return createMcpErrorResponse("execute query", error); } }
- tools/executeQuery.ts:12-20 (schema)Zod schema defining inputs for execute-query: 'query' (required SQL string), 'params' (optional array of parameters), 'explain' (optional boolean for execution plan).// Zod schema for input validation export const executeQueryShape: ZodRawShape = { query: z.string().min(1, "SQL query is required"), params: z.array(z.any()).optional().default([]), explain: z.boolean().optional().default(false), }; export const executeQuerySchema = z.object(executeQueryShape);
- index.ts:41-46 (registration)Registration of 'execute-query' tool in McpServer with description, input schema (executeQueryShape), and handler (executeQuery).server.tool( "execute-query", "Execute a parameterized SQL query with safety checks. Supports SELECT, INSERT, UPDATE, DELETE operations with parameter binding to prevent SQL injection.", executeQueryShape, executeQuery );