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;
};
```

PostgreSQL MCP Server