Infraviewer/mssql-mcp-readonly.js

#!/usr/bin/env node
const { Server } = require("@modelcontextprotocol/sdk/server/index.js");
const { StdioServerTransport } = require("@modelcontextprotocol/sdk/server/stdio.js");
const {
  CallToolRequestSchema,
  ListToolsRequestSchema,
} = require("@modelcontextprotocol/sdk/types.js");
const sql = require("mssql");

// Read DB config from environment variables
const config = {
  server: process.env.MSSQL_SERVER,
  port: parseInt(process.env.MSSQL_PORT || "1433"),
  user: process.env.MSSQL_USER,
  password: process.env.MSSQL_PASSWORD,
  database: process.env.MSSQL_DATABASE,
  options: {
    encrypt: process.env.MSSQL_ENCRYPT === "true",
    trustServerCertificate: process.env.MSSQL_TRUST_SERVER_CERTIFICATE === "true"
  }
};

// Helper to allow only SELECT queries (strict read-only enforcement)
function validateQuery(query) {
  const trimmed = query.trim().toUpperCase();
  
  // Must start with SELECT
  if (!trimmed.startsWith("SELECT")) {
    throw new Error("Only SELECT queries are allowed!");
  }
  
  // Block dangerous keywords that could modify data
  const dangerousKeywords = [
    'INSERT', 'UPDATE', 'DELETE', 'DROP', 'CREATE', 'ALTER', 
    'TRUNCATE', 'EXEC', 'EXECUTE', 'SP_', 'XP_', 'MERGE',
    'GRANT', 'REVOKE', 'DENY'
  ];
  
  for (const keyword of dangerousKeywords) {
    if (trimmed.includes(keyword)) {
      throw new Error(`Forbidden keyword detected: ${keyword}. Only SELECT queries are allowed!`);
    }
  }
  
  // Block semicolons (prevents query chaining like "SELECT 1; DROP TABLE")
  if (query.includes(';')) {
    throw new Error("Multiple statements not allowed. Only single SELECT queries permitted!");
  }
  
  return query;
}

async function runQuery(query) {
  validateQuery(query);
  const pool = await sql.connect(config);
  const result = await pool.request().query(query);
  await pool.close();
  return result.recordset;
}

// Create MCP server instance
const server = new Server(
  {
    name: "mssql-readonly-server",
    version: "1.0.0",
  },
  {
    capabilities: {
      tools: {},
    },
  }
);

// List available tools
server.setRequestHandler(ListToolsRequestSchema, async () => {
  return {
    tools: [
      {
        name: "execute_sql",
        description: "Execute read-only SQL queries on MS SQL Server. Only SELECT queries are allowed. Use this to fetch data from the Infra database tables such as TEILE.",
        inputSchema: {
          type: "object",
          properties: {
            query: {
              type: "string",
              description: "SQL SELECT query to execute. Must start with SELECT. No INSERT, UPDATE, DELETE, or other write operations allowed.",
            },
          },
          required: ["query"],
        },
      },
      {
        name: "list_tables",
        description: "List all available tables in the Infra database",
        inputSchema: {
          type: "object",
          properties: {},
        },
      },
      {
        name: "describe_table",
        description: "Get the schema/structure of a specific table including column names, data types, and constraints",
        inputSchema: {
          type: "object",
          properties: {
            table_name: {
              type: "string",
              description: "Name of the table to describe (e.g., 'TEILE')",
            },
          },
          required: ["table_name"],
        },
      },
      {
        name: "search_parts",
        description: "Search for parts in the TEILE table by term. Searches in Teil, Bez, Bez2, Hersteller, and other fields.",
        inputSchema: {
          type: "object",
          properties: {
            search_term: {
              type: "string",
              description: "Term to search for (e.g., manufacturer name, part number, description)",
            },
            limit: {
              type: "number",
              description: "Maximum number of results to return (default: 50)",
              default: 50,
            },
          },
          required: ["search_term"],
        },
      },
    ],
  };
});

// Handle tool execution
server.setRequestHandler(CallToolRequestSchema, async (request) => {
  try {
    const { name, arguments: args } = request.params;

    switch (name) {
      case "execute_sql": {
        const { query } = args;
        const rows = await runQuery(query);
        return {
          content: [
            {
              type: "text",
              text: JSON.stringify(rows, null, 2),
            },
          ],
        };
      }

      case "list_tables": {
        const listQuery = `
          SELECT TABLE_NAME 
          FROM INFORMATION_SCHEMA.TABLES 
          WHERE TABLE_TYPE = 'BASE TABLE' 
          ORDER BY TABLE_NAME
        `;
        const rows = await runQuery(listQuery);
        return {
          content: [
            {
              type: "text",
              text: JSON.stringify(rows, null, 2),
            },
          ],
        };
      }

      case "describe_table": {
        const { table_name } = args;
        const describeQuery = `
          SELECT 
            COLUMN_NAME,
            DATA_TYPE,
            CHARACTER_MAXIMUM_LENGTH,
            IS_NULLABLE,
            COLUMN_DEFAULT
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_NAME = '${table_name.replace(/'/g, "''")}'
          ORDER BY ORDINAL_POSITION
        `;
        const rows = await runQuery(describeQuery);
        return {
          content: [
            {
              type: "text",
              text: JSON.stringify(rows, null, 2),
            },
          ],
        };
      }

      case "search_parts": {
        const { search_term, limit = 50 } = args;
        const escaped = String(search_term).replace(/'/g, "''");
        const pattern = `N'%${escaped}%'`;
        const searchQuery = `
          SELECT TOP ${Math.max(1, parseInt(limit, 10) || 50)}
            t.Teil,
            t.Bez,
            t.Bez2,
            t.Ben7,
            t.Ben8,
            t.Hersteller,
            t.Stat,
            txt.Text AS [Text],
            ts.PrsVk AS PrsVK,
            ts.Ersatz AS Ersatz
          FROM TEILE t WITH (NOLOCK)
          OUTER APPLY (
            SELECT TOP 1 [Text]
            FROM TEXT WITH (NOLOCK)
            WHERE TextId = t.MemoID
            ORDER BY LfdNr
          ) txt
          OUTER APPLY (
            SELECT TOP 1 PrsVk, Ersatz
            FROM TSSAEF WITH (NOLOCK)
            WHERE Teil = t.Teil
            ORDER BY ISN
          ) ts
          WHERE
            t.Teil LIKE ${pattern} COLLATE SQL_Latin1_General_CP1_CI_AS
            OR t.Bez LIKE ${pattern} COLLATE SQL_Latin1_General_CP1_CI_AS
            OR t.Bez2 LIKE ${pattern} COLLATE SQL_Latin1_General_CP1_CI_AS
            OR t.Ben7 LIKE ${pattern} COLLATE SQL_Latin1_General_CP1_CI_AS
            OR t.Ben8 LIKE ${pattern} COLLATE SQL_Latin1_General_CP1_CI_AS
            OR t.Hersteller LIKE ${pattern} COLLATE SQL_Latin1_General_CP1_CI_AS
        `;
        const rows = await runQuery(searchQuery);
        return {
          content: [
            {
              type: "text",
              text: JSON.stringify(rows, null, 2),
            },
          ],
        };
      }

      default:
        throw new Error(`Unknown tool: ${name}`);
    }
  } catch (error) {
    return {
      content: [
        {
          type: "text",
          text: `Error: ${error.message}`,
        },
      ],
      isError: true,
    };
  }
});

// Start the server
async function main() {
  const transport = new StdioServerTransport();
  await server.connect(transport);
  console.error("MS SQL Read-only MCP Server running on stdio");
}

main().catch((error) => {
  console.error("Server error:", error);
  process.exit(1);
});