Saving OAuth tokens from Google API response to database?

Question

I'm building an MVC app that will authenticate users on our website that want us to post events into their calendars. I've installed the .NET C# client library as well as the Auth and MVC packages.

Using Google.Apis.Auth.OAuth2.Mvc I'm having trouble accessing the TokenResponse in my implementation of IDataStore, instead it saves the URL as the token i.e. localhost:6055/calendar/calendarasync/deantest04556734

All of the examples that I've found seem to be out of date, not using the Mvc package and not implementing a DataStore to save to a database so I've usedUsing bits of code from daimto's example and the official examples to get started.

Can anyone point me in the right direction or see any issues with my code?

DatabaseDataStore

public class DatabaseDataStore : IDataStore
{
    private SqlConnection connection;

    public DatabaseDataStore(SqlConnection sqlConn)
    {
        if (sqlConn != null)
        {
            connection = sqlConn;

            if(connection.State != ConnectionState.Open)
                connection.Open();
        }
    }

    /// <summary>
    /// Stores the given value for the given key. It creates a new file (named <see cref="GenerateStoredKey"/>) in 
    /// <see cref="FolderPath"/>.
    /// </summary>
    /// <typeparam name="T">The type to store in the data store</typeparam>
    /// <param name="key">The key</param>
    /// <param name="value">The value to store in the data store</param>
    public Task StoreAsync<T>(string key, T value)
    {
        if (string.IsNullOrEmpty(key))
        {
            throw new ArgumentException("Key MUST have a value");
        }

        var serialized = NewtonsoftJsonSerializer.Instance.Serialize(value);
        string userId = getUserId(key);

        if (userId == null)
        {
            insertUserData(key, serialized);
        }
        else
        {
            updateUserData(userId, key, serialized);
        }

        return Task.Delay(0);
    }

    /// <summary>
    /// Returns the stored value for the given key or <c>null</c> if the matching file (<see cref="GenerateStoredKey"/>
    /// in <see cref="FolderPath"/> doesn't exist.
    /// </summary>
    /// <typeparam name="T">The type to retrieve</typeparam>
    /// <param name="key">The key to retrieve from the data store</param>
    /// <returns>The stored object</returns>
    public Task<T> GetAsync<T>(string key)
    {
        //Key is the user string sent with AuthorizeAsync
        if (string.IsNullOrEmpty(key))
        {
            throw new ArgumentException("Key MUST have a value");
        }

        TaskCompletionSource<T> tcs = new TaskCompletionSource<T>();
        string refreshToken = null;

        // Try and find the Row in the DB.
        using (SqlCommand cmd = new SqlCommand("Calendar_GetRefreshToken", connection))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout = 2700;
            try
            {
                cmd.Parameters.AddWithValue("@username", key);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    refreshToken = reader["RefreshToken"].ToString();
                }
                reader.Dispose();

                if (refreshToken == null)
                {
                    // we don't have a record so we request it of the user.
                    tcs.SetResult(default(T));
                }
                else
                {
                    try
                    {
                        // we have it we use that.
                        tcs.SetResult(NewtonsoftJsonSerializer.Instance.Deserialize<T>(refreshToken));
                    }
                    catch (Exception ex)
                    {
                        tcs.SetException(ex);
                    }
                }
            }
            catch (Exception ex)
            {
                //logger.Error("Method:CheckLocalProperty - id: " + propId + " - Error:" + ex.Message);
                return null;
            }
        }

        return tcs.Task;
    }

    /// <summary>
    /// Clears all values in the data store. This method deletes all files in <see cref="FolderPath"/>.
    /// </summary>
    public Task ClearAsync()
    {
        // Removes all data from the Table.
        string truncateString = "truncate table [dbo].[tblCactusGoogleUsers] ";
        SqlCommand commandins = new SqlCommand(truncateString, connection);
        commandins.ExecuteNonQuery();

        return Task.Delay(0);
    }

    /// <summary>
    /// Deletes the given key. It deletes the <see cref="GenerateStoredKey"/> named file in <see cref="FolderPath"/>.
    /// </summary>
    /// <param name="key">The key to delete from the data store</param>
    public Task DeleteAsync<T>(string key)
    {
        if (string.IsNullOrEmpty(key))
        {
            throw new ArgumentException("Key MUST have a value");
        }

        deleteUserData(key);

        return Task.Delay(0);
    }

    /// <summary>Creates a unique stored key based on the key and the class type.</summary>
    /// <param name="key">The object key</param>
    /// <param name="t">The type to store or retrieve</param>
    public static string GenerateStoredKey(string key, Type t)
    {
        return string.Format("{0}-{1}", t.FullName, key);
    }

    private string getUserId(string value)
    {
        using (SqlCommand cmd = new SqlCommand("Calendar_GetUserId", connection))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout = 2700;
            try
            {
                cmd.Parameters.AddWithValue("@username", value);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    return reader["UserId"].ToString();
                }
                reader.Dispose();
            }
            catch (Exception ex)
            {
                //logger.Error("Method:CheckLocalProperty - id: " + propId + " - Error:" + ex.Message);
                return null;
            }
        }
        return null;
    }

    private void insertUserData(string key, string value)
    {
        using (SqlCommand cmd = new SqlCommand("Calendar_InsertUser", connection))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout = 2700;
            try
            {
                cmd.Parameters.AddWithValue("@token", value);
                cmd.Parameters.AddWithValue("@username", key);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //logger.Error("Method:insertUserData - id: " + key + " - Error:" + ex.Message);
            }
        }
    }

    private void updateUserData(string userId, string key, string value)
    {
        using (SqlCommand cmd = new SqlCommand("Calendar_UpdateUser", connection))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout = 2700;
            try
            {
                cmd.Parameters.AddWithValue("@userid", userId);
                cmd.Parameters.AddWithValue("@username", key);
                cmd.Parameters.AddWithValue("@token", value);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //logger.Error("Method:updateUserData - id: " + key + " - Error:" + ex.Message);
            }
        }
    }

    private void deleteUserData(string key)
    {
        using (SqlCommand cmd = new SqlCommand("Calendar_DeleteUser", connection))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout = 2700;
            try
            {
                cmd.Parameters.AddWithValue("@username", key);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //logger.Error("Method:deleteUserData - id: " + key + " - Error:" + ex.Message);
            }
        }
    }
}

FlowMetadata

public class AppAuthFlowMetadata : FlowMetadata
{
    private SqlConnection connection;
    private readonly IAuthorizationCodeFlow flow;

    public AppAuthFlowMetadata(SqlConnection sqlConn, string clientId, string clientSecret)
    {
        if (sqlConn != null)
        {
            connection = sqlConn;

            if (connection.State != ConnectionState.Open)
                connection.Open();

            flow = new GoogleAuthorizationCodeFlow(new GoogleAuthorizationCodeFlow.Initializer
            {
                ClientSecrets = new ClientSecrets
                {
                    ClientId = clientId,
                    ClientSecret = clientSecret
                },
                Scopes = new[] { 
                    CalendarService.Scope.Calendar
                },
                DataStore = new DatabaseDataStore(connection)
            });
        }
        else
        {
            throw new ArgumentException("sqlConn is null");
        }
    }

    public override string GetUserId(Controller controller)
    {
        /* TODO - Get UserId from form post */
        return controller.User.Identity.Name;
    }

    public override IAuthorizationCodeFlow Flow
    {
        get { return flow; }
    }
}

Controller

public class CalendarController : Controller
{
    CalendarService service;
    string CLIENT_ID = ConfigurationManager.AppSettings["GoogleClientID"].ToString();
    string CLIENT_SECRET = ConfigurationManager.AppSettings["GoogleClientSecret"].ToString();

    [Authorize]
    public async Task<ActionResult> CalendarAsync(CancellationToken cancellationToken)
    {
        ViewBag.Message = "Your calendar page.";

        var result = await new AuthorizationCodeMvcApp(this, new AppAuthFlowMetadata(
                        new SqlConnection(ConfigurationManager.ConnectionStrings["HFConnString"].ConnectionString),
                        CLIENT_ID,
                        CLIENT_SECRET)
                     ).AuthorizeAsync(cancellationToken);

        if (result.Credential == null)
            return new RedirectResult(result.RedirectUri);

        service = new CalendarService(new BaseClientService.Initializer
            {
                HttpClientInitializer = result.Credential,
                ApplicationName = "ASP.NET Google APIs MVC Sample"
            });
        ...
    }
}

Answer 1

Spent the last two days figuring this out myself. I'm gonna paste the code I'm using and if there is anything you don't understand, just ask. I have read so many posts and I litteraly got this working right now so there is some commented code and it has not been refactored yet. I hope this will help someone. The NuGet packages I'm using are these:

Google.Apis.Auth.MVC

Google.Apis.Calendar.v3

Code:

AuthCallbackController:

[AuthorizationCodeActionFilter]
public class AuthCallbackController : Google.Apis.Auth.OAuth2.Mvc.Controllers.AuthCallbackController
{
protected static readonly ILogger Logger = ApplicationContext.Logger.ForType<AuthCallbackController>();

/// <summary>Gets the authorization code flow.</summary>
protected IAuthorizationCodeFlow Flow { get { return FlowData.Flow; } }

/// <summary>
/// Gets the user identifier. Potential logic is to use session variables to retrieve that information.
/// </summary>
protected string UserId { get { return FlowData.GetUserId(this); } }

/// <summary>
/// The authorization callback which receives an authorization code which contains an error or a code.
/// If a code is available the method exchange the coed with an access token and redirect back to the original
/// page which initialized the auth process (using the state parameter).
/// <para>
/// The current timeout is set to 10 seconds. You can change the default behavior by setting 
/// <see cref="System.Web.Mvc.AsyncTimeoutAttribute"/> with a different value on your controller.
/// </para>
/// </summary>
/// <param name="authorizationCode">Authorization code response which contains the code or an error.</param>
/// <param name="taskCancellationToken">Cancellation token to cancel operation.</param>
/// <returns>
/// Redirect action to the state parameter or <see cref="OnTokenError"/> in case of an error.
/// </returns>
[AsyncTimeout(60000)]
public async override Task<ActionResult> IndexAsync(AuthorizationCodeResponseUrl authorizationCode,
   CancellationToken taskCancellationToken)
{
    if (string.IsNullOrEmpty(authorizationCode.Code))
    {
        var errorResponse = new TokenErrorResponse(authorizationCode);
        Logger.Info("Received an error. The response is: {0}", errorResponse);
        Debug.WriteLine("Received an error. The response is: {0}", errorResponse);
        return OnTokenError(errorResponse);
    }

    Logger.Debug("Received \"{0}\" code", authorizationCode.Code);
    Debug.WriteLine("Received \"{0}\" code", authorizationCode.Code);


    var returnUrl = Request.Url.ToString();
    returnUrl = returnUrl.Substring(0, returnUrl.IndexOf("?"));

    var token = await Flow.ExchangeCodeForTokenAsync(UserId, authorizationCode.Code, returnUrl,
        taskCancellationToken).ConfigureAwait(false);

    // Extract the right state.
    var oauthState = await AuthWebUtility.ExtracRedirectFromState(Flow.DataStore, UserId,
        authorizationCode.State).ConfigureAwait(false);

    return new RedirectResult(oauthState);
}

protected override Google.Apis.Auth.OAuth2.Mvc.FlowMetadata FlowData
{
    get { return new AppFlowMetadata(); }
}

protected override ActionResult OnTokenError(TokenErrorResponse errorResponse)
{
    throw new TokenResponseException(errorResponse);
}


//public class AuthCallbackController : Google.Apis.Auth.OAuth2.Mvc.Controllers.AuthCallbackController
//{
//    protected override Google.Apis.Auth.OAuth2.Mvc.FlowMetadata FlowData
//    {
//        get { return new AppFlowMetadata(); }
//    }
//}

}

Method for Controller calling Google API

    public async Task<ActionResult> GoogleCalendarAsync(CancellationToken cancellationToken)
    {
        var result = await new AuthorizationCodeMvcApp(this, new AppFlowMetadata()).
            AuthorizeAsync(cancellationToken);

        if (result.Credential != null)
        {
            //var ttt = await result.Credential.RevokeTokenAsync(cancellationToken);

            //bool x = await result.Credential.RefreshTokenAsync(cancellationToken);

            var service = new CalendarService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = result.Credential,
                ApplicationName = "GoogleApplication",
            });
            var t = service.Calendars;

            var tt = service.CalendarList.List();

            // Define parameters of request.
            EventsResource.ListRequest request = service.Events.List("primary");
            request.TimeMin = DateTime.Now;
            request.ShowDeleted = false;
            request.SingleEvents = true;
            request.MaxResults = 10;
            request.OrderBy = EventsResource.ListRequest.OrderByEnum.StartTime;

            // List events.
            Events events = request.Execute();
            Debug.WriteLine("Upcoming events:");
            if (events.Items != null && events.Items.Count > 0)
            {
                foreach (var eventItem in events.Items)
                {
                    string when = eventItem.Start.DateTime.ToString();
                    if (String.IsNullOrEmpty(when))
                    {
                        when = eventItem.Start.Date;
                    }
                    Debug.WriteLine("{0} ({1})", eventItem.Summary, when);
                }
            }
            else
            {
                Debug.WriteLine("No upcoming events found.");
            }


            //Event myEvent = new Event
            //{
            //    Summary = "Appointment",
            //    Location = "Somewhere",
            //    Start = new EventDateTime()
            //        {
            //            DateTime = new DateTime(2014, 6, 2, 10, 0, 0),
            //            TimeZone = "America/Los_Angeles"
            //        },
            //    End = new EventDateTime()
            //        {
            //            DateTime = new DateTime(2014, 6, 2, 10, 30, 0),
            //            TimeZone = "America/Los_Angeles"
            //        },
            //    Recurrence = new String[] {
            //        "RRULE:FREQ=WEEKLY;BYDAY=MO"
            //        },
            //    Attendees = new List<EventAttendee>()
            //        {
            //        new EventAttendee() { Email = "[email protected]" }
            //        }
            //};

            //Event recurringEvent = service.Events.Insert(myEvent, "primary").Execute();

            return View();
        }
        else
        {
            return new RedirectResult(result.RedirectUri);
        }
    }

Derived class of FlowMetadata

public class AppFlowMetadata : FlowMetadata
    {
        //static readonly string server = ConfigurationManager.AppSettings["DatabaseServer"];
        //static readonly string serverUser = ConfigurationManager.AppSettings["DatabaseUser"];
        //static readonly string serverPassword = ConfigurationManager.AppSettings["DatabaseUserPassword"];
        //static readonly string serverDatabase = ConfigurationManager.AppSettings["DatabaseName"];
    ////new FileDataStore("Daimto.GoogleCalendar.Auth.Store")
    ////new FileDataStore("Drive.Api.Auth.Store")
    //static DatabaseDataStore databaseDataStore = new DatabaseDataStore(server, serverUser, serverPassword, serverDatabase);


    private static readonly IAuthorizationCodeFlow flow =
new ForceOfflineGoogleAuthorizationCodeFlow(new GoogleAuthorizationCodeFlow.Initializer
{
    ClientSecrets = new ClientSecrets
    {
        ClientId = "yourClientId",
        ClientSecret = "yourClientSecret"

    },
    Scopes = new[]
    {
CalendarService.Scope.Calendar, // Manage your calendars
//CalendarService.Scope.CalendarReadonly // View your Calendars
    },
    DataStore = new EFDataStore(),
});

    public override string GetUserId(Controller controller)
    {
        // In this sample we use the session to store the user identifiers.
        // That's not the best practice, because you should have a logic to identify
        // a user. You might want to use "OpenID Connect".
        // You can read more about the protocol in the following link:
        // https://developers.google.com/accounts/docs/OAuth2Login.

        //var user = controller.Session["user"];
        //if (user == null)
        //{
        //    user = Guid.NewGuid();
        //    controller.Session["user"] = user;
        //}
        //return user.ToString();

        //var store = new UserStore<ApplicationUser>(new ApplicationDbContext());
        //var manager = new UserManager<ApplicationUser>(store);
        //var currentUser = manager.FindById(controller.User.Identity.GetUserId());

        return controller.User.Identity.GetUserId();

    }

    public override IAuthorizationCodeFlow Flow
    {
        get { return flow; }
    }

    public override string AuthCallback
    {
        get { return @"/GoogleApplication/AuthCallback/IndexAsync"; }
    }
}

Entity framework 6 DataStore class

 public class EFDataStore : IDataStore
    {
        public async Task ClearAsync()
        {
            using (var context = new ApplicationDbContext())
            {
                var objectContext = ((IObjectContextAdapter)context).ObjectContext;
                await objectContext.ExecuteStoreCommandAsync("TRUNCATE TABLE [Items]");
            }
        }

        public async Task DeleteAsync<T>(string key)
        {
            if (string.IsNullOrEmpty(key))
            {
                throw new ArgumentException("Key MUST have a value");
            }

        using (var context = new ApplicationDbContext())
        {
            var generatedKey = GenerateStoredKey(key, typeof(T));
            var item = context.GoogleAuthItems.FirstOrDefault(x => x.Key == generatedKey);
            if (item != null)
            {
                context.GoogleAuthItems.Remove(item);
                await context.SaveChangesAsync();
            }
        }
    }

    public Task<T> GetAsync<T>(string key)
    {
        if (string.IsNullOrEmpty(key))
        {
            throw new ArgumentException("Key MUST have a value");
        }

        using (var context = new ApplicationDbContext())
        {
            var generatedKey = GenerateStoredKey(key, typeof(T));
            var item = context.GoogleAuthItems.FirstOrDefault(x => x.Key == generatedKey);
            T value = item == null ? default(T) : JsonConvert.DeserializeObject<T>(item.Value);
            return Task.FromResult<T>(value);
        }
    }

    public async Task StoreAsync<T>(string key, T value)
    {
        if (string.IsNullOrEmpty(key))
        {
            throw new ArgumentException("Key MUST have a value");
        }

        using (var context = new ApplicationDbContext())
        {
            var generatedKey = GenerateStoredKey(key, typeof(T));
            string json = JsonConvert.SerializeObject(value);

            var item = await context.GoogleAuthItems.SingleOrDefaultAsync(x => x.Key == generatedKey);

            if (item == null)
            {
                context.GoogleAuthItems.Add(new GoogleAuthItem { Key = generatedKey, Value = json });
            }
            else
            {
                item.Value = json;
            }

            await context.SaveChangesAsync();
        }
    }

    private static string GenerateStoredKey(string key, Type t)
    {
        return string.Format("{0}-{1}", t.FullName, key);
    }
}

Derived class for GoogleAuthorizationCodeFlow. Enabling long-lived refresh token that take care of automatically "refreshing" the token, which simply means getting a new access token.

https://developers.google.com/api-client-library/dotnet/guide/aaa_oauth

internal class ForceOfflineGoogleAuthorizationCodeFlow : GoogleAuthorizationCodeFlow
{
    public ForceOfflineGoogleAuthorizationCodeFlow(GoogleAuthorizationCodeFlow.Initializer initializer) : base (initializer) { }

    public override AuthorizationCodeRequestUrl CreateAuthorizationCodeRequest(string redirectUri)
    {
        return new GoogleAuthorizationCodeRequestUrl(new Uri(AuthorizationServerUrl))
        {
            ClientId = ClientSecrets.ClientId,
            Scope = string.Join(" ", Scopes),
            RedirectUri = redirectUri,
            AccessType = "offline",
            ApprovalPrompt = "force"
        };
    }
}

GoogleAuthItem is used with EFDataStore

public class GoogleAuthItem
{
    [Key]
    [MaxLength(100)]
    public string Key { get; set; }

    [MaxLength(500)]
    public string Value { get; set; }
}

public DbSet<GoogleAuthItem> GoogleAuthItems { get; set; }