run_select

Instructions

Implementation Reference

• src/db.ts:268-279 (handler)

  The actual handler for 'run_select' – executes the SQL query after calling assertReadOnly() for validation, caps results at maxRows, and returns rows with metadata.

  async runSelect(query: string, maxRows: number) {
    assertReadOnly(query);
    const cap = Math.min(Math.max(Math.floor(maxRows), 1), 1000);
    const r = await (await this.getPool()).request().query(query);
    const rows = r.recordset.slice(0, cap).map((row) => truncateRow(row));
    return {
      rows,
      row_count_returned: rows.length,
      row_count_total: r.recordset.length,
      truncated: r.recordset.length > rows.length,
    };
  }

• src/index.ts:167-175 (registration)

  Registers the 'run_select' tool on the MCP server with its schema (query string, max_rows number) and the handler that delegates to db.runSelect().

  server.tool(
    'run_select',
    'Run a single SELECT or WITH (CTE) statement. DML/DDL/EXEC are rejected before reaching the server. Results are capped at max_rows (default 100, hard max 1000).',
    {
      query: z.string().describe('A single SELECT or WITH statement.'),
      max_rows: z.number().int().positive().max(1000).default(100),
    },
    async ({ query, max_rows }) => runTool(() => db.runSelect(query, max_rows))
  );

• src/index.ts:167-173 (schema)

  Input schema for 'run_select' – defines 'query' (string) and 'max_rows' (integer, default 100, max 1000) via Zod.

  server.tool(
    'run_select',
    'Run a single SELECT or WITH (CTE) statement. DML/DDL/EXEC are rejected before reaching the server. Results are capped at max_rows (default 100, hard max 1000).',
    {
      query: z.string().describe('A single SELECT or WITH statement.'),
      max_rows: z.number().int().positive().max(1000).default(100),
    },

• src/validation.ts:1-77 (helper)

  Validation guardrail for run_select – assertReadOnly() strips comments/strings, checks for single SELECT/WITH statement, and rejects forbidden DDL/DML keywords before the query reaches the database.

  /**
   * Lexical guardrail for run_select. The actual security boundary is the
   * read-only database login; this just produces friendly errors before the
   * statement reaches the server.
   */
  const FORBIDDEN_KEYWORDS = [
    'INSERT',
    'UPDATE',
    'DELETE',
    'MERGE',
    'TRUNCATE',
    'DROP',
    'CREATE',
    'ALTER',
    'GRANT',
    'REVOKE',
    'DENY',
    'EXEC',
    'EXECUTE',
    'BACKUP',
    'RESTORE',
    'BULK',
    'OPENROWSET',
    'OPENDATASOURCE',
    'OPENQUERY',
    'DBCC',
    'KILL',
    'SHUTDOWN',
    'INTO', // SELECT INTO creates a new table
  ];
  
  export class InvalidQueryError extends Error {
    constructor(message: string) {
      super(`[INVALID_QUERY] ${message}`);
      this.name = 'InvalidQueryError';
    }
  }
  
  function stripCommentsAndStrings(sql: string): string {
    return sql
      .replace(/--[^\n]*/g, '')
      .replace(/\/\*[\s\S]*?\*\//g, '')
      .replace(/'(?:[^']|'')*'/g, "''")
      .replace(/"(?:[^"]|"")*"/g, '""');
  }
  
  export function assertReadOnly(sql: string): void {
    if (!sql || !sql.trim()) {
      throw new InvalidQueryError('Empty query.');
    }
  
    const cleaned = stripCommentsAndStrings(sql).trim();
  
    // Allow a single trailing semicolon, no others.
    const noTrailingSemi = cleaned.replace(/;\s*$/, '');
    if (noTrailingSemi.includes(';')) {
      throw new InvalidQueryError(
        'Multi-statement batches are not allowed. Submit a single SELECT or WITH (CTE).'
      );
    }
  
    const firstWord = noTrailingSemi.match(/^\s*([A-Za-z_]+)/)?.[1]?.toUpperCase();
    if (firstWord !== 'SELECT' && firstWord !== 'WITH') {
      throw new InvalidQueryError(
        `Only SELECT or WITH (CTE) statements are allowed; got "${firstWord ?? 'unknown'}".`
      );
    }
  
    for (const kw of FORBIDDEN_KEYWORDS) {
      const re = new RegExp(`\\b${kw}\\b`, 'i');
      if (re.test(noTrailingSemi)) {
        throw new InvalidQueryError(
          `Statement contains forbidden keyword "${kw}". This server is read-only.`
        );
      }
    }
  }