MCP MySQL Server

Instructions

Implementation Reference

• src/index.ts:365-413 (handler)

  The handler function for the 'query' tool. Ensures database connection, validates the SQL query (must be SELECT and pass security checks), executes it using the MySQL connection pool, and returns the results as a formatted JSON string.

  private async handleQuery(requestId: string, args: any) {
    await this.ensureConnection();
  
    if (!args.sql) {
      throw new McpError(ErrorCode.InvalidParams, 'SQL query is required');
    }
  
    if (!args.sql.trim().toUpperCase().startsWith('SELECT')) {
      throw new McpError(
        ErrorCode.InvalidParams,
        'Only SELECT queries are allowed with query tool'
      );
    }
  
    const sql = args.sql.trim();
  
    // 验证SQL安全性
    if (!validateSqlQuery(sql)) {
      throw new McpError(
        ErrorCode.InvalidParams,
        'SQL query contains potentially dangerous patterns'
      );
    }
  
    try {
      console.error(`[${requestId}] Executing query: ${sql.substring(0, 100)}${sql.length > 100 ? '...' : ''}`);
      const [rows] = await this.pool!.query(args.sql, args.params || []);
  
      // 计算结果集大小
      const resultSize = JSON.stringify(rows).length;
      console.error(`[${requestId}] Query executed successfully, result size: ${resultSize} bytes`);
  
      return {
        content: [
          {
            type: 'text',
            text: JSON.stringify(rows, null, 2),
          },
        ],
      };
    } catch (error) {
      const errorMsg = getErrorMessage(error);
      console.error(`[${requestId}] Query execution failed: ${errorMsg}`);
      throw new McpError(
        ErrorCode.InternalError,
        `Query execution failed: ${errorMsg}`
      );
    }
  }

• src/index.ts:218-234 (schema)

  The input schema definition for the 'query' tool, specifying 'sql' as required string and optional 'params' array.

  inputSchema: {
    type: 'object',
    properties: {
      sql: {
        type: 'string',
        description: 'SQL SELECT query',
      },
      params: {
        type: 'array',
        items: {
          type: ['string', 'number', 'boolean', 'null'],
        },
        description: 'Query parameters (optional)',
      },
    },
    required: ['sql'],
  },

• src/index.ts:215-235 (registration)

  The tool registration in the ListTools response, defining name, description, and input schema for 'query'.

  {
    name: 'query',
    description: 'Execute a SELECT query',
    inputSchema: {
      type: 'object',
      properties: {
        sql: {
          type: 'string',
          description: 'SQL SELECT query',
        },
        params: {
          type: 'array',
          items: {
            type: ['string', 'number', 'boolean', 'null'],
          },
          description: 'Query parameters (optional)',
        },
      },
      required: ['sql'],
    },
  },

• src/index.ts:293-295 (registration)

  The dispatch/registration in the CallToolRequestHandler switch statement that routes 'query' calls to the handleQuery function.

  case 'query':
    result = await this.handleQuery(requestId, request.params.arguments);
    break;

• src/index.ts:43-58 (helper)

  Helper function to validate SQL query for basic security by checking against dangerous patterns like DROP, DELETE, UNION SELECT, etc.

  // Helper to validate SQL query for basic security
  function validateSqlQuery(sql: string): boolean {
    // 检查是否存在常见SQL注入模式
    const dangerousPatterns = [
      /;\s*DROP\s+/i,
      /;\s*DELETE\s+/i,
      /;\s*UPDATE\s+/i,
      /;\s*INSERT\s+/i,
      /UNION\s+SELECT/i,
      /--/,
      /\/\*/,
      /xp_cmdshell/i
    ];
  
    return !dangerousPatterns.some(pattern => pattern.test(sql));
  }