Social Network Authentication: Merging Accounts

This entry is part 3 of 4 in the series Using Social Networks as a Login System

Using Social Networks as a Login System

In this part, we will have a look at how we can make sure people don’t have multiple accounts after signing into our application through different means.

Merging accounts

If you allow users to sign up through different social networks and perhaps your own registration system, there is a good chance some users will have multiple accounts. How annoying can it be for a user who signed up through Facebook earlier, to come back later and log in through Twitter because he thought he used that one?We can prevent this by letting the user merge manually or try to use an automatic system to try and identify duplicated users.

Setup

I suggest a setup of two database tables. The first table is the general user table, which contains all information about the user.

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) DEFAULT NULL,
  `firstname` varchar(50) NOT NULL,
  `lastname` varchar(50) NOT NULL,
  `emailaddress` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `birtdate` date NOT NULL,
  `gender` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Note: As you can see, I used the fields that we also used in our SocialLoginInterface. You might need more or less fields depending on your application. You even could decide to split this table in a user and user_profile table if you wish.

The second table contains all data regarding any third party logins the user used.

CREATE TABLE IF NOT EXISTS `user_provider` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) COLLATE utf8_unicode_ci NOT NULL,
  `provider` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `provider_uid` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

In provider, we save the name of the provider we used – for example Google+. In the provider_uid column, we save the actual user ID from the provider.

Merging user accounts manually

Let’s imagine a user registered through Google+. Later on, he comes back and registers himself through your default registration system. After logging in, he suddenly remembers that he was already logged in once before with Google+.

According to the above database scheme, there will be two records in the user table and one record in the user_provider table. The best way to merge these two accounts is by letting the user connect other social networks to their account.

You can do this by allowing users to “log in” with a social network, after already being authorized in your system. However, instead of “log in”, we are now going to call it “connect”.

Just add a “connect” button to your application, which calls the log in URL from the social network as you probably did on the log in page. As soon as the callback URL is called, you check if the provided user id from the social network is already within your user_provider table.

If so, it means you found a different account of the user. In that case, you can basically remove the duplicate account and connect the user_provider record with the current user.

If not, then it just seems to be that this user was not logged in before with that social network. In that case, you can just add a record to user_provider. Next time he logs in through this social network, he will be immediately recognized.

Note: Before merging the 2 accounts together, you could first ask the user if he actually wants to do this. Maybe there is a reason the user had two separate accounts. Next to that, don’t forget to also merge any content added by the duplicate user with the current user, so all data is connected with one account.

There is, however, also a possibility that the user is logged in through Google+ and now wants to merge his account with an already existing account, which was registered through the default registration system. In that case, you could just ask the user to fill in a username and password. When filled in, you could check if there is actually a combination available in the user table, containing the provided username and password.

Merging user accounts automatically

Instead of letting the user merge accounts manually, we could also try to see if we can merge automatically. This can be achieved by checking the profile of the user we retrieved back from a social network with the already existing users, right at the moment after the connection with the social network.

A good start is by checking the email address. The email address is a field which cannot be easily faked and is quite unique. So after you received back all data from the social network, you can check if the email address you retrieved already exists in the database. If that’s the case, you seem to have found a match. Instead of creating a whole new user, you could update that existing user.

That’s it? I wish it was. However, not all social networks return an email address. Twitter, for example, does not return an email address and you cannot retrieve it any other way. Next to that, who says my Google+ email address is the same as I used in your application? In the end, there is no full guarantee that you merged all possible accounts.

Since we cannot guarantee there was an actual merge, we can add a second level which checks the user profile. As you have seen in the previous article, we are collecting more data then just the email address. The next check we could perform is any combination between the other fields. Checking the birth date alone for example, will give you too many possibilities. However, how big is the chance that you find two people with the exact same last name and birth date? Or how big is the chance that you find two people with the same location, first name and gender?

Basically, the combinations are endless. Just try to think reasonably and take into consideration that not all social networks are returning all data. In the previous article we saw for example that Google is not giving you back a birth date.

So, can we actually merge, based on these details? No! You just opened up a potential security flaw if you did. Just imagine I am impersonating someone on Google+. By logging into your application, I would be able to take control of the account I am impersonating. To prevent this from happening, we need to add one more step in between the log in and the actual merge: Validation.

Whenever the user logs in through Google+ and your system has found a possible match in your database, ask the user to validate. The simplest way is telling the user you found a potentially already existing account. Next, allow this user to verify it’s him by allowing him the original log in. So if the existing account was created through your default registration system, allow the user to fill in the password for this account. If the account was created by a different social log in, allow the user to log in again through that same method. If the user does so and you get a positive result on the verification, you know for sure you got the correct user.

In the end, we still can have some duplicate accounts. However, we at least tried to keep it to a minimum and by trying, we also tried to improve the user experience.

Conclusion

With this article, we reached the end of this series. Hopefully, these articles taught you something about how to create framework agnostic packages, how you can set up a social log in with Google+ and how you can merge accounts together.

Some follow up articles will be online soon, showing you how to expand these articles with other social networks. I am looking forward to your feedback in the comments below.

Using Social Networks as a Login System

<< Social Network Authentication: Google+Social Network Authentication: Twitter and Facebook >>

Win an Annual Membership to Learnable,

SitePoint's Learning Platform

  • KathrynDBradsher

    Start working at home>>CLICK NEXT TAB FOR MORE INFO AND HELP

  • https://www.peternijssen.nl/ Peter Nijssen

    @kwawer:disqus Why you believe that is better?

    I would suggest to keep it in a different table, for these reasons:
    A) Whenever you add a new provider, you don’t need to add an additional column to the user table
    B) If you have like 4 social networks, for most users, these columns will be just empty
    C) I prefer to separate the user data from these social networks

    Especially A and B are major improvements to your solution.

    • kwawer

      A) Do you really add new provider in dynamic way ? You probably add new provider by modification code (add client key and client secret) So add additional column is not so big deal.
      B) I agree, but …
      C) Ok, but …

      … in my proposition you will prevent collision (for example double uid for google for specific user) in database. In your proposition, you should add additional indexes, validation, and probably more code in application to prevent this situation.

      • https://www.peternijssen.nl/ Peter Nijssen

        A) “not a big deal” is not really a valid reason to use something “static” instead of “dynamic”.

        The other problems you are saying can happen in both our solutions. You can also have duplicates, you also will need validation, etc. I don’t see why you suddenly shouldn’t validate it. Same goes for duplicates. In fact, If the user clicks a login button, the first check that should be performed is if the uid exists already. In that case, we got a valid match and we log the user in to our system. If it doesn’t exist, it seems like we have a new user and we have to create an account (or merge with an existing one). Your method doesn’t change anything to this.

        Next to that, you will also need to add indexes if you want to deal with thousands or millions of users. In fact, you need to add 4 separate indexes. While I just need 1 index which is a combination of the uid and provider column. Indexes slow down inserts and updates within your user table. (minor speed difference, but still).

        • Moazam

          Perfect answer Peter

  • Günce Bektaş

    Generally seperating and module programming is good but in this case keeping these ids in users table is much more efficient because in my view it will really reduce code.

    • https://www.peternijssen.nl/ Peter Nijssen

      Which extra code do you have in mind? And you don’t mind the effects on your database in order to ‘reduce code’?