database structure

[Alex]

i usually just store all user info in 1 table, including passwords, etc

a buddy and i were having a discussion about database safety and he says that it is better to store the password in a separate table and cross-reference it with the id in the user table.

i see how what he is saying could work but really if the database is compromised it doesn't really make a difference does it? i mean the guy can look up the password in the password table and find the id in the user table anyway, it just takes 1 extra step.

and this extra step is actually a pain in the ass when authenticating in the website, because i need 1 query to find the guy's id from his email and another query to match the id with the password from the other table...

can anyone please explain whether or not his method is worth it and if it's considered bad practice to store all user information in 1 table... thanks! 🙂

 

[troytime]

its easy to join the tables in a query to get the all the required data

however, i never keep the password in a different table
if the server isn't secure, whoever has access will be able to get the passwords anyway

store your passwords in a unique md5 (i like using the users insert datetime + 'myUniQUE strING' + theuserspassword)

that way if someone DOES gain entry, they won't be able to make a giant list of usernames and passwords

 

[Alex]

Originally posted by: troytime
its easy to join the tables in a query to get the all the required data

however, i never keep the password in a different table
if the server isn't secure, whoever has access will be able to get the passwords anyway

store your passwords in a unique md5 (i like using the users insert datetime + 'myUniQUE strING' + theuserspassword)

that way if someone DOES gain entry, they won't be able to make a giant list of usernames and passwords

interesting suggestion dude i like it!

i'm a mysql beginner but i can't figure out a query that will do that for me... the common info between both tables is the id but the information i have to start with is the email and the password, one from each table...

anyway that said, i'm gonna move it back into a 1 table setup because it makes more sense...

i like the MD5 idea too! 🙂

 

[troytime]

yeah keep em in the same table

for the record though, the join would be like
SELECT u.email, u.userid, p.password
FROM users u
JOIN passwords p ON u.userid = p.userid
WHERE u.email = LOGINEMAILHERE

a lot of login scripts query for both the username (or email) AND the password in a single query. so if the query returns 0 rows, the login fails.
personally i like to get the user info and compare the hashed password in the script.
that way i can have an accurate error message (username doesn't exist, or incorrect password)

are you using php?

 

[Alex]

Originally posted by: troytime
yeah keep em in the same table

for the record though, the join would be like
SELECT u.email, u.userid, p.password
FROM users u
JOIN passwords p ON u.userid = p.userid
WHERE u.email = LOGINEMAILHERE

a lot of login scripts query for both the username (or email) AND the password in a single query. so if the query returns 0 rows, the login fails.
personally i like to get the user info and compare the hashed password in the script.
that way i can have an accurate error message (username doesn't exist, or incorrect password)

are you using php?

thanks wow that's a pretty complex query :Q
yeah i'm using php!

i read up on it and i rewrote the login script to use 1 table only and the mysql MD5() function and it seems to work pretty well and adds that extra bit of security so i'm not storing plain-text passwords

just for the record whats that syntax you used u.email, u.userid and then you said "FROM users u" does that mean the table is called "users" and you just specified some kind of shorthand for it or am i completely lost?

 

[pstylesss]

Here is a topic of mine that was all about joins. Hopefully it'll give you a good idea on how to do them. Helped me out tons.

http://forums.anandtech.com/me...071632&highlight_key=y

 

[troytime]

Originally posted by: alex

Originally posted by: troytime
yeah keep em in the same table

for the record though, the join would be like
SELECT u.email, u.userid, p.password
FROM users u
JOIN passwords p ON u.userid = p.userid
WHERE u.email = LOGINEMAILHERE

a lot of login scripts query for both the username (or email) AND the password in a single query. so if the query returns 0 rows, the login fails.
personally i like to get the user info and compare the hashed password in the script.
that way i can have an accurate error message (username doesn't exist, or incorrect password)

are you using php?

thanks wow that's a pretty complex query :Q
yeah i'm using php!

i read up on it and i rewrote the login script to use 1 table only and the mysql MD5() function and it seems to work pretty well and adds that extra bit of security so i'm not storing plain-text passwords

just for the record whats that syntax you used u.email, u.userid and then you said "FROM users u" does that mean the table is called "users" and you just specified some kind of shorthand for it or am i completely lost?

yeah thats basically an 'alias' for that table name
it makes things more readable when the query joins 4 or 5 tables 🙂
make sure your md5 is more than just an md5(password)
while md5 isn't reversable, it IS a standard hash that can be created in many many languages

use a timestamp and a string-key of your liking combined with the password for the md5
that way in order for someone to brute force the md5, they'll have to work for it

 

[Alex]

Originally posted by: troytime

Originally posted by: alex

Originally posted by: troytime
yeah keep em in the same table

for the record though, the join would be like
SELECT u.email, u.userid, p.password
FROM users u
JOIN passwords p ON u.userid = p.userid
WHERE u.email = LOGINEMAILHERE

a lot of login scripts query for both the username (or email) AND the password in a single query. so if the query returns 0 rows, the login fails.
personally i like to get the user info and compare the hashed password in the script.
that way i can have an accurate error message (username doesn't exist, or incorrect password)

are you using php?

thanks wow that's a pretty complex query :Q
yeah i'm using php!

i read up on it and i rewrote the login script to use 1 table only and the mysql MD5() function and it seems to work pretty well and adds that extra bit of security so i'm not storing plain-text passwords

just for the record whats that syntax you used u.email, u.userid and then you said "FROM users u" does that mean the table is called "users" and you just specified some kind of shorthand for it or am i completely lost?

yeah thats basically an 'alias' for that table name
it makes things more readable when the query joins 4 or 5 tables 🙂
make sure your md5 is more than just an md5(password)
while md5 isn't reversable, it IS a standard hash that can be created in many many languages

use a timestamp and a string-key of your liking combined with the password for the md5
that way in order for someone to brute force the md5, they'll have to work for it

thanks, ok will do, gonna read up on this and see if i can get it working! :beer: