db design help, for a site with member profiles.

I am developing a website that will host lots of user information. Things like avatar images, personal information, information about the users skills, etc. I will also be implementing a forum at some point. I am using a MySql db just to be clear.

Ok now, I need some ideas on how to set up the database to hold all of this information. Right now I was thinking that I could create a MEMBERS table, to hold username, password, etc. Using the MEMBERS table to authenticate the user and login. Then I could created different 'INFORMATION' tables to hold all the information. The INFORMATION table has a row called owner that holds a username. Then when the user logs in I just match the user name to the owner name of the information, and display the correct information.

Does this sound like a good design or are there better / more secure ways to do this?

It sounds more reasonable to use the ID field like you said. I was (on my test db for design) using md5 to hash the passwords. I am going to look up the algorithm that you pointed out, and learn more about salts and dynamic salts.

Thank you for you're reply and pushing me in the right direction. Now I have specific things to look up, like database normalization, and key-value joins. Very helpful.

ShadowGate wrote: It sounds more reasonable to use the ID field like you said. I was (on my test db for design) using md5 to hash the passwords. I am going to look up the algorithm that you pointed out, and learn more about salts and dynamic salts.

Thank you for you're reply and pushing me in the right direction. Now I have specific things to look up, like database normalization, and key-value joins. Very helpful.

Look up the term "hash table". It's what cydo is failing at describing.

ShadowGate wrote: It sounds more reasonable to use the ID field like you said. I was (on my test db for design) using md5 to hash the passwords. I am going to look up the algorithm that you pointed out, and learn more about salts and dynamic salts.

Thank you for you're reply and pushing me in the right direction. Now I have specific things to look up, like database normalization, and key-value joins. Very helpful.

Look up the term "hash table". It's what cydo is failing at describing.

Ok thanks

Of course, only going to use the tools that most suit my needs. Now is the time for research. My web site is a labor of love, and I am putting lots of work into designing it correctly.

you should map out the pseudocode of what you'd like to do so that we may better understand what it is you actually want and suggest tools accordingly.

This was my first mock up, before this thread.

CREATE DATABASE mysitedb;

CREATE TABLE members
(
  	id                       int		 NOT NULL   AUTO_INCREMENT,     
  	username                 varchar(255)    NOT NULL   UNIQUE,
	password                 varchar(255)    NOT NULL,                                
  	email_address            varchar(255)    NOT NULL   UNIQUE,                                                               
  	user_group               varchar(255)    NOT NULL,
	registration_ip          varchar(255)    NOT NULL,
	registration_datetime    datetime        NOT NULL,                                                                                

  	PRIMARY KEY (id)
);

CREATE TABLE profiles
(
	id	        int	        NOT NULL	AUTO_INCREMENT,
	owner	        varchar(255)	NOT NULL	UNIQUE,
	real_name	varchar(255),
	date_of_birth	date,
	avatar_uri	varchar(255),
	about_me	blob,
	.
	.
	.
);

ShadowGate wrote: This was my first mock up, before this thread.

CREATE DATABASE mysitedb;

CREATE TABLE members
(
  	id                       int		 NOT NULL   AUTO_INCREMENT,     
  	username                 varchar(255)    NOT NULL   UNIQUE,
	password                 varchar(255)    NOT NULL,                                
  	email_address            varchar(255)    NOT NULL   UNIQUE,                                                               
  	user_group               varchar(255)    NOT NULL,
	registration_ip          varchar(255)    NOT NULL,
	registration_datetime    datetime        NOT NULL,                                                                                

  	PRIMARY KEY (id)
);

CREATE TABLE profiles
(
	id	        int	        NOT NULL	AUTO_INCREMENT,
	owner	        varchar(255)	NOT NULL	UNIQUE,
	real_name	varchar(255),
	date_of_birth	date,
	avatar_uri	varchar(255),
	about_me	blob,
	.
	.
	.
);

So what's the problem here? Set a session and cookies dependent upon correct user entry and echo the variables from that user's infodb. You could generate a random integer/hash/string to tag unique members' information if you don't want to create a new db for all their info and use that to associate their stuff and then just echo it in the UI in the proper locations.

Ok that was my plan. My question was is this the correct way to do it? By you're answer I would conclude that yes it is. I may look to the random hash values to id the info to each user (because it may be a fraction of a second quicker). I did gather good information on normalization of databases, because of this thread, among other things.

Thank you all for your time and help. I really just wanted a second opinion I guess.

Filter your inputs properly and you've got nothing to worry about in the regard you were thinking of. Real escape strings and addslashes for your php, and salt your hashes.

Thats a good point cydo. Will be fixed.

In the mean time I came across a problem that I hope you guys can again help me with.

On Register I generate a random salt, and hash the password with it in the database. On login I do the same but instead I get the salt from the database. However for some reason the hash that gets stored in the database is different from the one generated on login. So I cant login anymore.

Register

$randomSalt = rand(10, 99).mcrypt_create_iv(6, MCRYPT_DEV_RANDOM);
$saltyPassword = $system_salt.$randomSalt.$_POST['password'];
$passwordHash = hash('sha512', $saltyPassword);	
			
mysql_query("	INSERT INTO members(username,password,salt,email_address,user_group,registration_ip,registration_datetime)
VALUES(
	'".$_POST['username']."',
	'".$passwordHash."',
	'".$randomSalt."',
	'".$_POST['email']."',
	'"."user"."',
	'".$_SERVER['REMOTE_ADDR']."',
	NOW()
)");

Login

// Create salt and password
$randomSalt = mysql_fetch_assoc(mysql_query("SELECT salt FROM members WHERE username='{$_POST['username']}'"));
$saltyPassword = $system_salt.$randomSalt['salt'].$_POST['password'];
$passwordHash = hash('sha512', $saltyPassword);
		
$row = mysql_fetch_assoc(mysql_query("SELECT id,username FROM members WHERE username='{$_POST['username']}' AND password='".$passwordHash."'"));
if($row['username'])
{
    // If everything is OK login
    $_SESSION['username']=$row['username'];
    $_SESSION['id'] = $row['id'];
    $_SESSION['rememberMe'] = $_POST['rememberMe'];
			
    // Store some data in the session
    setcookie('Remember', $_POST['rememberMe']);
}
else 
{
    $err[]='Invalid username and/or password';
}

Ok I found out that the problem is in the mcrypt_create_iv(6, MCRYPT_DEV_RANDOM). I dont know why but it just doesn't work. I got an alt mcrypt and it works well.

function alt_mcrypt_create_iv ($size) {
    $iv = '';
    for($i = 0; $i < $size; $i++) {
        $iv .= chr(rand(0,255));
    }
    return $iv;
}

hey cydoes , please mention your ORIGINAL usernames inside your posts , otherwise it confuses the hell outta us .

Ah I see the problem now. That's a good fix hashing the raw stream. I don't know what problems you see in my code but I have been learning more about security since I started coding a login system to begin with. Just to note, that this is not full code, and the data has been filtered before this point.

Anyways I hope I can keep securing my system over time as I learn more.

And with that said I'll post a link that was shown too me by a friend.

http://codahale.com/how-to-safely-store-a-password/