[SOLVED] Making a user database

[zyrolasting]

Hello.

 

I'm taking someone's advice and making a comments section. I want to only allow registered users to leave comments, but I don't know how much I need or how secure I need to be. There's no checklist I can see. I want to be future proof as I plan to make a forum one day. I am using the following MySQL table.

 

CREATE TABLE  `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `pass` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `rank` varchar(255) NOT NULL DEFAULT 'Newcomer',
  `posts` mediumint(9) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

I felt kind of dirty having a table where everyone's passwords were visible. I have Terms of Service and a privacy policy and I am now working on a form to allow login/registration.

 

Is there anything I'm missing I should know about?

[jonsjava]

For starts, lets trim the overhead a bit, ok?

 

Lets start with this:

CREATE TABLE  `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `pass` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `rank` varchar(255) NOT NULL DEFAULT 'Newcomer',
  `rights` varchar(255) NOT NULL DEFAULT 'Member',
  `posts` mediumint(9) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

Lets not make everything 255. I don't know many people who can remember a username longer than 16, but for the sake of argument, lets set it to 20.

Next: if you md5 and salt your pass, it will always be 32 characters in length. the ID: you are allowing up to 99,999,999,999,999,999,999(100 septillion) people have accounts. I'm pretty sure that's more people than have EVER LIVED. Lets go with this:

tinyint(6) That allows for a good million people (right at, starting at 0)

Next one up:

e-mail: ok, RFC says that the max length of an e-mail address is 320 characters, so we are actually UPPING that to be RFC-friendly

rank: unless you are going to have a TON of possible ranks, lets set this to tinyint(2). This gives you up to 100 different ranks (if you just set them to an integer)

same goes for rights.

posts, I think it's kinda large. Lets set that to int(6).

So, the schema could look something like this:

CREATE TABLE  `users` (
  `id` tinyint(6) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `pass` varchar(32) NOT NULL,
  `email` varchar(320) NOT NULL,
  `rank` tinyint(2) NOT NULL DEFAULT 'Newcomer',
  `rights` tinyint(2) NOT NULL DEFAULT 'Member',
  `posts` tinyint(6) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[abazoskib]

Hello.

 

I'm taking someone's advice and making a comments section. I want to only allow registered users to leave comments, but I don't know everything I need or how secure I need to be. There's no checklist as far as I can tell. I want to be more future proof with this, as I plan to make a forum one day.  Because of this I am using the following MySQL table.

 

CREATE TABLE  `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `pass` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `rank` varchar(255) NOT NULL DEFAULT 'Newcomer',
  `posts` mediumint(9) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

I felt kind of dirty having a table where everyone's passwords were visible on table access. I used a TOS generator to get some Terms of Service and a privacy policy and I am now working on a form to allow login/registration.

 

Is there anything I'm missing I should know about? Should I include myself in this table?

 

For one, do you really need an unsigned bigint for your user id? Thats a hell of a lot of users and not realistic. Unisgned int should be more than enough. 255 characters is way too long for your string columns.

 

You will need a separate table for comments(I hope that is what you are planning).

 

In terms of what you already have, instead of storing the user's actual password, you should implement an encrypted password. A lot of people find use of the MD5() function. Using it, you would compare the user inputted password's MD5 hash value to the one in the database, if it matches, the user can proceed as logged in. Else they are not logged in. Since MD5 has been shown to be prone to collision in rare cases, an even more secure way of encrypting the passwords would be using SHA1(). I find that SHA1() alone is enough security for me. Others find that they need to include what's called a "salt" in addition to the encryption. A salt is a randomly generated string of characters (5-12 characters long usually), generated when the user first registers, that is also stored for each user. This salt is then concatenated with the user inputted password before calculating the SHA1() or MD5(). It does add difficulty to a brute force attack, but its not required in my opinion.

 

Also, I hope you are escaping all user input properly, to avoid an injection attack.

 

Other things to consider are limiting the amount of times a user can try to login to help fight brute force attacks. You might also want to start logging IP addresses at least at the time of registration. If you are looking for the most security possible, you can alert the user when they are not logged in from the same IP and ask them a few security questions about their account.

 

That should be more than enough to keep you busy for a while, but hopefully I covered most of the important stuff. Good luck!

 

edit: jonsjava said some of the stuff i said, except tinyint will not allow for a million people. stick with unsigned int.

[jonsjava]

I just crunched the numbers, and the number you were allowing to join (99,999,999,999,999,999,999) is an estimated 939,351,982 times the total of people who have ever lived (106,456,367,669).

[zyrolasting]

Good Lord did I misinterpret on the allocation bit! That was NOT intentional!

So, I have everything I need?

 

Also, I hope you are escaping all user input properly, to avoid an injection attack.

 

I don't know the first thing about what you are saying. I'm on my first site and have only been using PHP for a couple of weeks, and was in web development for a couple more than that. My affinity is more in MS Native C++98.

 

Thank you for the input so far!

 

I just crunched the numbers, and the number you were allowing to join (99,999,999,999,999,999,999) is an estimated 939,351,982 times the total of people who have ever lived (106,456,367,669).

 

It was a mistake. I got you the first time, and I would appreciate it if you didn't rub it in my face.

[jonsjava]

It was a mistake. I would appreciate if you didn't rub it in my face.

 

I wasn't trying. I thought it be humorous. Who hasn't misunderstood how these things work in the past?  I've done my share of varchar(255) because I saw it everywhere.  Just trying to lighten the mood.

[zyrolasting]

I'm not offended, I'm just trying to assure a good foundation for me. No hard feelings.

[abazoskib]

Good Lord did I misinterpret on the allocation bit! That was NOT intentional!

So, I have everything I need?

 

Also, I hope you are escaping all user input properly, to avoid an injection attack.

 

I don't know the first thing about what you are saying. I'm on my first site and have only been using PHP for a couple of weeks, and was in web development for a couple more than that. My affinity is more in MS Native C++98.

 

Thank you for the input so far!

 

I just crunched the numbers, and the number you were allowing to join (99,999,999,999,999,999,999) is an estimated 939,351,982 times the total of people who have ever lived (106,456,367,669).

 

It was a mistake. I got you the first time, and I would appreciate it if you didn't rub it in my face.

 

zyrolasting: he was kidding! lol this community is actually pretty cool so don't worry.

 

read up on mysql_real_escape_string() in the php manual online. sql injection attack is when someone enters SQL into a user input(like a username field, or password field) to exploit your database, and may view a list of all the records in your table. mysql_real_escape_string() will escape all harmful characters in any user input and you'll be much happier with it. if you need help with any of the stuff, id advise you to read up on it, try it out, then come back..its the best way to learn!

[zyrolasting]

zyrolasting: he was kidding! lol this community is actually pretty cool so don't worry.

Again, I'm cool I'm cool. =) Thanks for the tidbit, too!

 

By the way, I came up with my own proof against this story: http://www.huffingtonpost.com/2009/10/05/nyr-whos-in-big-brothers_n_309196.html Try this, it's actually a lot of fun to disprove!

 

I think I have all I need. Thanks guys!

[jonsjava]

I always get lost above quadrillion.