This article is written for and published on SitePoint.
In this part, we will have a look at how we make sure people don’t have multiple accounts after signing into our application through different means.
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?
The best thing we can do is try to avoid that our users have multiple accounts. We can prevent this by letting the user merge manually or try to use an automatic system to try and identify duplicated users.
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.
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 for this user. 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 separated 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.
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 yet that you merged all possible accounts.
Since we cannot guarantee there was a 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.
With this article, we reached the end of this serie. 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.