execute-sql-query
Execute SELECT queries on PostgreSQL databases to retrieve data securely through the MCP server, supporting read-only operations with built-in security validation.
Instructions
Execute a SQL query on the database. Only SELECT statements are allowed for security.
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| sql | Yes | The SQL query to execute |
Implementation Reference
- src/server.ts:174-203 (handler)The core handler function for the 'execute-sql-query' tool. It validates the SQL query using sqlValidation, executes it safely with initDb().unsafe(sql), and returns the JSON-formatted results or an error message.async ({ sql }) => { const validation = sqlValidation(sql); if (!validation.success) { return { content: [{ type: "text", text: `❌ Query validation failed: ${validation.message}` }], isError: true }; } try { const results = await initDb().unsafe(sql); return { content: [{ type: "text", text: JSON.stringify(results, null, 2) }] }; } catch (err: unknown) { const error = err as Error; return { content: [{ type: "text", text: `❌ Query execution failed: ${error.message}` }], isError: true }; } }
- src/server.ts:165-204 (registration)The registration call for the 'execute-sql-query' tool, specifying the name, metadata (title, description), input schema using Zod, and the handler function.server.registerTool( "execute-sql-query", { title: "Execute SQL Query", description: "Execute a SQL query on the database. Only SELECT statements are allowed for security.", inputSchema: { sql: z.string().describe("The SQL query to execute") }, }, async ({ sql }) => { const validation = sqlValidation(sql); if (!validation.success) { return { content: [{ type: "text", text: `❌ Query validation failed: ${validation.message}` }], isError: true }; } try { const results = await initDb().unsafe(sql); return { content: [{ type: "text", text: JSON.stringify(results, null, 2) }] }; } catch (err: unknown) { const error = err as Error; return { content: [{ type: "text", text: `❌ Query execution failed: ${error.message}` }], isError: true }; } } );
- src/server.ts:170-172 (schema)The input schema definition for the tool, using Zod to validate a single 'sql' string parameter.inputSchema: { sql: z.string().describe("The SQL query to execute") },
- src/server.ts:56-82 (helper)SQL validation helper function used by the handler to ensure only safe (SELECT) queries are executed by blocking dangerous SQL keywords.function sqlValidation(sql: string) { const dangerousKeywords = ["DROP", "DELETE", "TRUNCATE", "ALTER", "CREATE", "INSERT", "UPDATE"]; // check if these keywords appear at the start of statements allows them in column names, comments, etc. const statements = sql.split(';').map(s => s.trim()).filter(s => s.length > 0); for (const statement of statements) { // Skip empty statements and comments if (!statement || statement.startsWith('--') || statement.startsWith('/*')) { continue; } // Check if statement starts with a dangerous keyword const firstWord = statement.split(/\s+/)[0].toUpperCase(); if (dangerousKeywords.includes(firstWord)) { return { success: false, message: `Dangerous operation detected: ${firstWord}. Only SELECT statements are allowed.` }; } } return { success: true, message: "SQL is safe" }; }
- src/server.ts:49-54 (helper)Database initialization helper that lazily creates and caches the database connection using getDb() for reuse across tool calls.const initDb = () => { if(!dbConnection) { dbConnection = getDb(); } return dbConnection; };