Follow us on Twitter!
You cannot teach a man anything; you can only help him find it within himself. - Galileo
Wednesday, April 16, 2014
Navigation
Home
HellBoundHackers Main:
HellBoundHackers Find:
HellBoundHackers Information:
Learn
Communicate
Submit
Shop
Challenges
HellBoundHackers Exploit:
HellBoundHackers Programming:
HellBoundHackers Think:
HellBoundHackers Track:
HellBoundHackers Patch:
HellBoundHackers Other:
HellBoundHackers Need Help?
Other
Members Online
Total Online: 18
Guests Online: 16
Members Online: 2

Registered Members: 82807
Newest Member: Black Hawk
Latest Articles
View Thread

HellBound Hackers | Computer General | Webmasters Lounge

Author

db design help, for a site with member profiles.

ShadowGate
Member

Your avatar

Posts: 24
Location:
Joined: 18.07.11
Rank:
Newbie
Posted on 24-02-12 10:35
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?
Author

RE: db design help, for a site with member profiles.

ShadowGate
Member

Your avatar

Posts: 24
Location:
Joined: 18.07.11
Rank:
Newbie
Posted on 24-02-12 20:15
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.



Author

RE: db design help, for a site with member profiles.

Arabian
Banned



Posts: 332
Location: inside you.
Joined: 22.09.10
Rank:
Apprentice
Posted on 24-02-12 20:21
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.


G'bye y'all! I was an asshole, So korg banned me.
Author

RE: db design help, for a site with member profiles.

Arabian
Banned



Posts: 332
Location: inside you.
Joined: 22.09.10
Rank:
Apprentice
Posted on 24-02-12 20:21
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.


G'bye y'all! I was an asshole, So korg banned me.
Author

RE: db design help, for a site with member profiles.

ShadowGate
Member

Your avatar

Posts: 24
Location:
Joined: 18.07.11
Rank:
Newbie
Posted on 24-02-12 20:31
Ok thanks
Author

RE: db design help, for a site with member profiles.

ShadowGate
Member

Your avatar

Posts: 24
Location:
Joined: 18.07.11
Rank:
Newbie
Posted on 24-02-12 21:25
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.
Author

RE: db design help, for a site with member profiles.

Arabian
Banned



Posts: 332
Location: inside you.
Joined: 22.09.10
Rank:
Apprentice
Posted on 24-02-12 22:19
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.


G'bye y'all! I was an asshole, So korg banned me.
Author

RE: db design help, for a site with member profiles.

ShadowGate
Member

Your avatar

Posts: 24
Location:
Joined: 18.07.11
Rank:
Newbie
Posted on 25-02-12 02:27
This was my first mock up, before this thread.

Code

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,
   .
   .
   .
);



Author

RE: db design help, for a site with member profiles.

Arabian
Banned



Posts: 332
Location: inside you.
Joined: 22.09.10
Rank:
Apprentice
Posted on 25-02-12 03:05
ShadowGate wrote:
This was my first mock up, before this thread.

Code

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.


G'bye y'all! I was an asshole, So korg banned me.
Author

RE: db design help, for a site with member profiles.

ShadowGate
Member

Your avatar

Posts: 24
Location:
Joined: 18.07.11
Rank:
Newbie
Posted on 25-02-12 03:56
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.
Author

RE: db design help, for a site with member profiles.

Arabian
Banned



Posts: 332
Location: inside you.
Joined: 22.09.10
Rank:
Apprentice
Posted on 25-02-12 05:12
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.


G'bye y'all! I was an asshole, So korg banned me.
Author

RE: db design help, for a site with member profiles.

ShadowGate
Member

Your avatar

Posts: 24
Location:
Joined: 18.07.11
Rank:
Newbie
Posted on 28-02-12 23:47
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
Code

$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
Code

// 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';
}





Edited by ShadowGate on 28-02-12 23:51
Author

RE: db design help, for a site with member profiles.

ShadowGate
Member

Your avatar

Posts: 24
Location:
Joined: 18.07.11
Rank:
Newbie
Posted on 29-02-12 10:26
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.

Code

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



Author

RE: db design help, for a site with member profiles.

newbee
Member



Posts: 127
Location: India
Joined: 27.12.11
Rank:
Active User
Warn Level: 20
Posted on 29-02-12 17:13
hey cydoes , please mention your ORIGINAL usernames inside your posts , otherwise it confuses the hell outta us .


www.hellboundhackers.org/sig/r/64440.png
i1078.photobucket.com/albums/w488/ads99nrg/signature.png

Author

RE: db design help, for a site with member profiles.

ShadowGate
Member

Your avatar

Posts: 24
Location:
Joined: 18.07.11
Rank:
Newbie
Posted on 01-03-12 07:13
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.
Author

RE: db design help, for a site with member profiles.

ShadowGate
Member

Your avatar

Posts: 24
Location:
Joined: 18.07.11
Rank:
Newbie
Posted on 01-03-12 18:54
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/