Execute SQL Query

Instructions

Implementation Reference

• src/server.ts:174-204 (handler)

  The async handler function for execute-sql-query. Validates SQL using sqlValidation, then executes it via initDb().unsafe(sql) and returns the JSON-stringified results, or an error message on failure.

    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:167-172 (schema)

  The input schema for execute-sql-query, defining a single 'sql' parameter of type string via Zod.

  {
    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")
    },

• src/server.ts:165-204 (registration)

  Registration of the 'execute-sql-query' tool using server.registerTool with title, description, inputSchema, and handler.

  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)

  The sqlValidation helper function that checks SQL for dangerous keywords (DROP, DELETE, TRUNCATE, ALTER, CREATE, INSERT, UPDATE) to ensure only SELECT statements are executed.

  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)

  The initDb helper function that lazily initializes and returns the database connection.

  const initDb = () => {
    if(!dbConnection) {
      dbConnection = getDb();
    }
    return dbConnection;
  };