execute-sql-query
Execute SELECT queries on PostgreSQL databases to retrieve data while maintaining security through read-only operations.
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-204 (handler)The main handler function for the "execute-sql-query" tool. Validates the SQL query using sqlValidation, executes it with initDb().unsafe(sql) if valid, and returns JSON-stringified 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:170-172 (schema)Zod-based input schema defining the required 'sql' parameter as a string.inputSchema: { sql: z.string().describe("The SQL query to execute") },
- src/server.ts:165-205 (registration)Registration of the "execute-sql-query" tool using McpServer.registerTool, including name, metadata, input schema, and inline 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:56-82 (helper)Security helper function that validates the SQL string to ensure it only contains safe SELECT statements by checking for dangerous keywords at the beginning of each statement.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)Helper function to initialize and cache the postgres.Sql database connection.const initDb = () => { if(!dbConnection) { dbConnection = getDb(); } return dbConnection; };