User Database - Login Structure

[FrOzeN]

I'm building a login system for a site, as well as the ability for users to register new accounts, etc. I'm trying to make it cover all the basic features as I build them in, that way I don't have to come back and continuously expand the table.

Here's what I've currently come up with for the structure, and values/datatypes to store the information. As I haven't done anything like this before I'm looking for feedback on my setup.

MySQL tables:

[list]
[*][b]Members[/b][list]
[*]username - max. length 15 - VARCHAR(15)
[*]password - max. length 20 - VARCHAR(71) - will be hashed, 71 characters to account for anything up to SHA-256
[*]cookievalue - VARCHAR(128)
[*]email - max. length 255 - VARCHAR(255)
[*]rememberme - BOOL - to determine whether or not to save a remember me cookie
[*]joinedtime - TIME
[*]joineddate - DATE
[*]timezone - TINYINT - (+/-) amount of 30 minutes from server GMT
eg: If server was GMT -5 and I lived in Sydney (GMT +10), when I picked my timezone it would set the TINYINT to 30
[*]datelightsavings - BOOL
[*]membergroup - TINYINT - 0 = Head Admin, 1 = Admin, etc.
[*]status - TINYINT - Banned, Suspended, Avaiting Email Validation, etc.
[*]suspendeduntil - DATE
[*]statusreason - VARCHAR(300) - moderators/admins whom ban/suspend users can leave a message that the member will see if they try viewing the site whilst suspended/banned, that way they 'know' what they did wrong
[*]lastlogintime - TIME
[*]lastlogindate - DATE
[*]birthday - DATE
[*]location - max. length 255 - VARCHAR(255)
[*]title - max. length 255 - VARCHAR(255)
[*]avatar - VARCHAR(255) - text link to image location
[*]website - max. length 255 - VARCHAR(255)
[*]msn - max. length 255 - VARCHAR(255)
[*]aim - max. length 255 - VARCHAR(255)
[*]yim - max. length 255 - VARCHAR(255)
[*]icq - max. length 255 - VARCHAR(255)
[*]postcount - INT
[*]interests - VARCHAR(300)
[*]signature - VARCHAR(500)
[/list]


[*][b]Temp Data[/b][list]
[*]sessionid - VARCHAR(255)
[*]sessionvalue - VARCHAR(64)
[*]ipaddress - VARCHAR(15)
[*]browserinfo - VARCHAR(255) - $_SERVER['HTTP_USER_AGENT']
[*]lastusetime - TIME
[*]lastusedate - DATE
[*]loggedin - BOOL
[*]membername - VARCHAR(15)
[/list]


[*][b]IP Addresses[/b][list]
[*]ip - VARCHAR(15)
[*]status - TINYINT
[*]reason - VARCHAR(300)[/list]

[/list]
[hr]
Structure:

[list]
[*][b]Part 1 - Login[/b]

[list][*][b]a)[/b] User visits webpage and their IP Address is matched against the 'IP Addresses' MySQL table to check it's status. If status isn't fine then the 'reason' value from that table is displayed to the user, and this will occur to them on all pages from that IP. There will also be contact information on that page which they can use to [i]try[/i] have there IP Address cleared if they have feasible reason as to why it should be.


[*][b]a - exit)[/b] If IP Address isn't fine then exit at this stage.


[*][b]b)[/b] If they already have a session, look it up and compare it's lastusetime/lastusedate to the current time/date. If it's within 20 minutes and the loggedin value is true, then login is complete and proceed to part 2. If it's within 20 minutes and doesn't have a loggedin value then skip to checking $_COOKIE info (1d). Otherwise, ignore the session info and treat it as new as it's setup in the next step. Note: After doing these checks, update the lastusetime/lastusedate.


[*][b]c)[/b] If session isn't already created then do so giving it the value 'sessionvalue' (a random 64 character string). Then in the 'Temp Data' MySQL table create an row containing sessionid, the assigned sessionvalue, ip address, the unparsed browser info (HTTP_USER_AGENT) and the current server's time/date in lastusetime/lastusedate respectively.


[*][b]d)[/b] Look up the 'Members' MySQL table for the cookievalue to see if it matches the the $_COOKIE['val'] saved on your computer. If it does then copy that 'username' across to the 'membername' value in the 'Temp Data' data and also set the 'loggedin' value to true. Login should now be complete, proceed to part 2. [color=red]Security note: Currently only using a random 128 character string to login users via cookie, open for other solutions.[/color]


[*][b]e)[/b] Run $_POST values through a filter to avoiding security leaks. If $_POST['loggingin'] isn't set to '1' then ignore this step as the post info isn't to do with the login. If it is then look up $_POST['username'] in the 'Members' table. If it's there then encrypt the $_POST['password'] to whatever the current encryption method is (probably SHA1), and then match it against the one on the same row in the table. If they match then set the 'Temp Data' table to 'loggedin' equals 'true' and copy the 'username' across into the 'membername' value. Login is now complete.


[/list]
[*][b]Part 2 - Show page[/b][list][*]Will do after I have finished coding the login, this is just a stub section.[/list]
[/list]

My thoughts:[list][*]Possibly switch 1d and 1e so $_POST check is done before $_COOKIE. Need opinions on that.
[*]Do $_COOKIE values need to run through a filter? Do they pose any vulnerabilities? (Like being able to change global variables, etc.)
[*]Need ideas on method of encryption, the MD5 is broken so I don't want to rely solely on that. Would MD5(SHA1(password)) be substantial?[/list]

I just typed this up then from the concept I had in my head, and it's 3AM in the morning. So may be a bit patchy. I only discussed the necessary parts regarding the login aspect. How does it sound so far? Any major problems, minor concerns, etc? All feedback is welcome, thanks. :)

[448191]

My sugestions:

Don't rely on IP's, unless you know for absolutely sure the IP is static. Also an IP can be easily spoofed, so even then it doesn't provide much protection.

I don't see the additional value of the "sessionvalue" as you call it. No need to set additional cookies either. You can use sha1 for session id's, that should be fine.

Use session_regenerate_id on login or any other acess level change.

Require password on change of profile information.

Use an uncomprised algorithim like SHA-256, currently bundled with php:
[code]<?php $pass = hash('sha256',$pass); ?>[/code]

Read this article: http://www.sitepoint.com/print/php-security-blunders

[itrebal]

1) What is the point of saving the cookie data? to verify it isn't hacked/changed from what you sent, in an effort to hack the account? If that is the point, I'd just store md5serialize($cookiedata)), after all - that is what MD5 is for.

2) Instead of storing the date/time they joined save a timestamp - much easier to work with.

3) postcount should be gotten by using count() in the posts table, not an integer stored near the user, IMO.