Single Or Multiple User Login On One Page?

[justlukeyou]

Hi,

 

I have 3 databases (users, users1 and users2). I am trying to create a login page in which someone can log in and they are then logged into their profile which is designed differently based on the three different types of users.

 

For example it could be a games website and log in as an xbox, playstation or Wii user.

 

I was originally planning to have 3 different form but I am not planning to have one single form which logs into one of 3 databases. Can anyone advise which they think is best? Any thoughts please?

[DavidAM]

You have 3 "databases" or you have 3 "tables"? which is it?

 

Why are you using separate databases/tables for different user types? Unless there is a compelling business reason, "Type" should be an attribute of the User. It would be stored in the one and only Users table. The application should then make decisions based on the Type of the user.

[Andy123]

Why are you using separate databases/tables for different user types? Unless there is a compelling business reason, "Type" should be an attribute of the User. It would be stored in the one and only Users table. The application should then make decisions based on the Type of the user.

 

What DavidAM said. You will probably have a lot of redundancy when keeping users stored in different tables, assuming that you are keeping at least some similar data about them (username, password, e-mail, etc.). I'd go with a single table which then has a foreign key to a "user_group" table. By doing this, you can use the same form for logging in and take the appropriate action depending on the user's user group or level. If you want to keep things very simple, you could even forget about the foreign key and just store the user level/group as an integer in your users table. I'd go with the foreign key personally, though. :-)

[justlukeyou]

Hi,

 

Yes apologies it is tables. Some of the fields (name, address etc) are very similiar however some of them are very specific to the 1 of 3 different user groups.

 

Is it possible to have a login script which idenitifies which table the user is in and directs them to the relevant user type.

[PFMaBiSmAd]

You have two replies that both state you need to have one user table. Now would be the time to fix your design.

 

Here's a rule of thumb - fixing design problems in the planning stage saves about 10 times the time and money it takes to fix them after they are implemented in code. Fixing design problems while code is being developed saves about 10 times the time and money it takes to fix them after the code is released.

[justlukeyou]

Im not saying your wrong. The thing is I would it would create a huge table with many of columns. Keeping it as 3 tables would be easier to manage.

[Pikachu2000]

No, it really wouldn't be easier to manage as 3 tables. It will end up being a nightmare, as it's already started to become. Fix the design now, before it's too late.

Edited November 25, 2012 by Pikachu2000

[PFMaBiSmAd]

Keeping it as 3 tables would be easier to manage.

 

How is this easier to manage? You have created a structure that is overly complicated to the point that you cannot even figure out how to query the tables to log someone in.

[justlukeyou]

How is this easier to manage? You have created a structure that is overly complicated to the point that you cannot even figure out how to query the tables to log someone in.

 

I can log in, I have that done. However I want to create a single page which allows someone to login. If I can get around that it should work fine.

[Pikachu2000]

Fix your database design, then there won't be any need to "get around that".

[DavidAM]

You are getting advice here from professionals; advice that would cost you hundreds of dollars in consulting fees if you hired a consultant. Even if you hired a consultant, you'd get advice from a single person; here you have advice from multiple seasoned professionals who have been there and done that. We are all telling you that the design is going to create problems. Yes, you have the per-group login working. But as you are now experiencing, you want to do something slightly different from what was considered in the design phase, and that task is much more difficult to accomplish because the design faulty.

 

There are two ways to change the design, create a single table with all the columns from all the tables and revise your current code/plans. Yes, this makes a large table, with lots of columns, but so what? that's what databases are for.

 

The other option is to create a user table with all the columns that are common between the three existing tables. Then remove those common columns from the three tables and add a foreign key to the "super users" table. Put a type flag in the "super users" table to indicate which of the three groups the user belongs to. Keep in mind the user could belong to more than one group.

 

Which solution is "correct" for your application is a decision to be made by the design team (you). It will require consideration of a lot of factors that only your design team will have.

 

Consider this:

In your current design what happens when you have a user in all three groups; and that user moves. He has to update his address in all three group tables. Or maybe he forgets and only updates one address. How do you know which address is correct? And why should the user have to go to the extra effort to enter the same information (his new address) in three different places?

 

I only have to write the code once; the user has to use it every day. Who should have the easier task?

 

If I can get around that it should work fine.

As soon as you get around this obstacle, you will find another similar obstacle which also becomes more difficult because of the database design.

[justlukeyou]

If Im wrong then I will be wrong and egg on my face. The changes of someone moving from one group to the other is remote.

 

Is it possible to have a page which enables login into one of three tables?

 

This is one example, obviously I dont know if there are two tables but their a two user profiles and a single login page:

 

http://wahooly.com/login

[DavidAM]

Add a select list or radio buttons on the login form, and have the user choose which system they want to login to. Then have your script check the selection to determine which login process to run and where to direct the user. You could store their selection in a cookie so it is pre-selected the next time they come to your site.

 

By the way, I know several gamers who have both xbox and playstation.

[Christian F.]

Justlukeyou: You know, this thread is a prime example why I don't bother with trying to help you any more: You don't listen to the advice given, but are dead-set on doing it Your Way™. All you want is for us to work, for free, doing it Your Way™.

Even if you say that you might be wrong, you don't mean it, and you certainly don't seem to be changing your approach either.

 

So I've come to the conclusion that trying to help you and give you advice is like pissing in the sea to raise the sea level: Utterly pointless.

Keep this up, and I wouldn't be surprised if more people come to the same conclusion as I have.

 

I know I've linked you to the article "How to ask questions the smart way" several times, and I really think it's time that you actually start to adhere to what it says.

Edited November 26, 2012 by Christian F.

[Muddy_Funster]

obvious, tried and tested approach is to have a single user table

 

TBL_USER

uid

fistName

lastName

email

password

groupID

address1

address2

address3

address4

post/zipcode

telephoneMain

telephoneSecond

telephoneMobile

...other user info as required

 

one groups table

 

TBL_GROUPS

groupID

groupName

groupType

accessLevel

...other relivent group level information

 

If your group relates to a corp client then the address info can be moved into that, so can the telephone info, adding an optional extension feild to the user table if it's applicable. It's not quantum displacement theory.

Edited November 26, 2012 by Muddy_Funster

[justlukeyou]

Hi,

 

Radio buttons would look weird, its got be free flowing. The site isn't based on console gamers, that was just an example. Lets say its something tar more difinitive. For example, professional boxers, racing car driver or soccer player. Its highly unlikely the members will flick between the different user groups.

 

Is it possible to have a login script which reads through each table and logs in based on the relevant table?

[Andy123]

Is it possible to have a login script which reads through each table and logs in based on the relevant table?

 

Again you are back to the three table approach that we have all discouraged. I'm not sure why you even ask when you disregard any advice given to you. Anyways, sure it's possible; just run three SQL queries and job done. But I should add that it's a very bad idea...

[justlukeyou]

Hi,

 

I tried this. I have 3 tables users, users1 and users2

 

FROM users, users1, users2

 

 

But this doesn't work. Can you please advise how I can query the three tables?

 

    if(count($errors) === 0)
    {
$query    = 'SELECT * FROM users, users1, users2 WHERE email = "' . mysql_real_escape_string($loginEmail) . '" AND password = MD5("' . mysql_real_escape_string($loginPassword) . '") LIMIT 1'; 
	    $result = mysql_query($query);
	    if (!$result)

[mrMarcus]

MD5("' . mysql_real_escape_string($loginPassword) . '")

 

This is no good. If a user enters a quote (single or double), it will be escaped by mysql_real_escape_string(), then hashed. Unless you have this exact code everywhere the user logs in, and also when they register, it will continue to work.

 

What might happen is the following:

 

(note the differences in the hashes)

 

$pass = "fb7s6df9s'fdsa";

echo mysql_real_escape_string($pass) .'<br/>'; // fb7s6df9s\'fdsa
echo md5(mysql_real_escape_string($pass)) .'<br/>'; // 38a827e687af1b3bbdaec53c751cf153
echo md5($pass); // 0ec74de5dd867e1fb027d0e73001af53

 

I would recommend hashing the password before the query:

 

$loginPassword = md5($loginPassword);
$query = 'SELECT * FROM users, users1, users2 WHERE email = "' . mysql_real_escape_string($loginEmail) . '" AND password = "' . $loginPassword . '" LIMIT 1';

 

EDIT: Please note, if you change your code to the above, you might very well need to change your registration code, too, to reflect the same practice. Otherwise, you will lock everybody out who uses quotes in their passwords.

Edited November 26, 2012 by mrMarcus

[justlukeyou]

Brilliant thanks,

 

I always delighted to have stuff like this. So does this add hashes to the password that is submitted to the database and then reenters them one someone and there password when logging in.

 

Im a bit lost on hour applying it though. Does it wrap all the hashes into a string called $pass

[mrMarcus]

Brilliant thanks,

 

I always delighted to have stuff like this. So does this add hashes to the password that is submitted to the database and then reenters them one someone and there password when logging in.

 

Im a bit lost on hour applying it though. Does it wrap all the hashes into a string called $pass

 

That was just pseudo code to show how the hash can/will change when using mysql_real_escape_string() before using md5() on the password in question. Of course, if there are no characters to be escaped, then, the hash will go unchanged.

 

I applied the change directly to your code as you can see in my last post.

 

In your table you already have the passwords hashed. You are simply comparing the hash in the table (`password` column) with the incoming form data ($loginPassword) which is why you must first hash $loginPassword to do the compare. MySQL has a built-in MD5() function as you were using, which is fine to use; however, by placing an 'unscrubbed/unsanitized' variable directly into your query leaves you open for malicious behaviour/injection. Which is why, personally, I prefer to hash the password before applying it to the query and removing the MySQL MD5() function so you do not re-hash the already hashed password.

 

Applying mysql_real_escape_string() on any data that is to be hashed is redundant. It adds no extra security, but can, as I displayed, cause some login issues.

 

You must be sure to apply the same measures on all your login scripts, as well as your registration scripts, to ensure integrity. For example, if you keep similar code in your registration code (md5(mysql_real_escape_string($password))), and somebody registers with a quote in their password, then when they try and login at a later date, if you are using the code I provided, their hashes will not match and they will not be able to login.

[justlukeyou]

I see, so this registration script which adds MD5 to the password. I have changed the login page to the MD5 which no longer logs in. Is there a reason for this?

 

If I have MD5 on the registration how come it doesn't allow me log in. Is it because I am transferring the "registerPassword" into a "loginPassword"?

 

if(!$errors){
  $query = "INSERT INTO users (firstname, surname, email, password, date_registered) VALUES ('" . $firstname . "', '" . $surname . "', '" . mysql_real_escape_string($registerEmail) . "', MD5('" . mysql_real_escape_string($registerPassword) . "'), NOW())";

[mrMarcus]

I see, so this registration script which adds MD5 to the password. I have changed the login page to the MD5 which no longer logs in. Is there a reason for this?

 

If I have MD5 on the registration how come it doesn't allow me log in. Is it because I am transferring the "registerPassword" into a "loginPassword"?

 

if(!$errors){
 $query = "INSERT INTO users (firstname, surname, email, password, date_registered) VALUES ('" . $firstname . "', '" . $surname . "', '" . mysql_real_escape_string($registerEmail) . "', MD5('" . mysql_real_escape_string($registerPassword) . "'), NOW())";

 

As I stated, you're registration and login procedures need to match in terms of how the password is hashed.

 

// register
$registerPassword = md5($registerPassword);
$query = "INSERT INTO users (firstname, surname, email, password, date_registered) VALUES ('" . $firstname . "', '" . $surname . "', '" . mysql_real_escape_string($registerEmail) . "', '". $registerPassword ."', NOW())";

// login
$loginPassword = md5($loginPassword);
$query = 'SELECT * FROM users, users1, users2 WHERE email = "' . mysql_real_escape_string($loginEmail) . '" AND password = "' . $loginPassword . '" LIMIT 1';

 

As for why you cannot login, I'm not sure at this point. Going through your thread regarding the storage of users within multiple tables made me sick to me light-headed. I agree with the consensus in that keeping a single `users` table is the only way to go, so I went off-topic to point out an issue with potential disaster with your usage of md5 and mysql_real_escape_string as it pains me to assist further in a poor database design.

[justlukeyou]

Thanks, I made those changes and it works fine.

 

In terms of the different user groups I cant see what the issue is. It makes the makes the site much easier to manage.

 

Lets say for example you make a site for hotels and travellers. The fields would be completely different. For example:

 

Traveller: Favorite Destination, Budget,

 

Hotel: Location, Images, Costs, Number of Rooms, Facilities

 

Putting these together would make a more complicated table. I cant see why anyone would want to mix these two tables together.

[mrMarcus]

Thanks, I made those changes and it works fine.

 

In terms of the different user groups I cant see what the issue is. It makes the makes the site much easier to manage.

 

Lets say for example you make a site for hotels and travellers. The fields would be completely different. For example:

 

Traveller: Favorite Destination, Budget,

 

Hotel: Location, Images, Costs, Number of Rooms, Facilities

 

Putting these together would make a more complicated table. I cant see why anyone would want to mix these two tables together.

 

But going back on your initial post:

 

Hi,

 

I have 3 databases (users, users1 and users2). I am trying to create a login page in which someone can log in and they are then logged into their profile which is designed differently based on the three different types of users.

 

For example it could be a games website and log in as an xbox, playstation or Wii user.

 

I was originally planning to have 3 different form but I am not planning to have one single form which logs into one of 3 databases. Can anyone advise which they think is best? Any thoughts please?

 

And using your example of a "games website", it's as simple as having a single `users` table with a single column `user_type` where `user_type` can be a number of things, ie. xbox, playstation, wii.

 

Upon login, check the `user_type` and have the system act accordingly.

 

The argument of 3 tables being easier to manage than 1 will not fly with me. Let's up that to 100 user tables vs 1. Would that be easier to manage? What if you have 500 different user types? 5,000? The logic in which you are implementing will not allow for scaling, whatsoever.