MySQL database questions about hashing/storing dates/storing emails

Question

I'm trying to design a simple user table with a primary key userid, and then another table that uses userid as a foreign key to the users table. Do I have the right idea here?

CREATE TABLE `users` (
    `userid` INT(6) NOT NULL,
    `username` VARCHAR(50) NOT NULL,
    `password` VARCHAR(50) NOT NULL,
    `date_join` DATE NOT NULL,       <- should I just make this CURDATE instead?
    `user_handle` VARCHAR(50) NOT NULL,
    `date_modified` DATE NOT NULL,
    PRIMARY KEY (`userid`)
)

1) How can I make userid auto increment?

2) How do you store passwords as an MD5 hash? Also, I've read various coders strongly recommending brcrypt, thoughts?

3) How can I set the default user_handle to be the first part of an email before the @ symbol? Such that john@smith.com would yield a user handle of john.

4) Any extra security measures I should take when designing a user database?

5) The foreign key in other tables assocated with users would need a foreign key that points to userid in users?

Thanks a lot guys, I hope that's not too many questions!

score 3 · Accepted Answer · edited May 23 '17 at 12:12

How can I make userid auto increment?

Specify the AUTO_INCREMENT attribute on the userid column:
```
CREATE TABLE `users` (
    `userid` INT(6) NOT NULL AUTO_INCREMENT,
    -- etc.
```
How do you store passwords as an MD5 hash? Also, I've read various coders strongly recommending brcrypt, thoughts?

The main reason for recommending bcrypt over MD5 for hashing passwords is that bcrypt was designed for that purpose, whereas MD5 was designed to verify the integrity of messages: thus bcrypt is intentionally slow, whereas MD5 is intentionally fast. This means that it requires substantially more work for an attacker to brute-force bcrypt hashes versus MD5 ones.

Since both functions produce fixed-size binary output (of 16 bytes in the case of MD5 and 56 bytes in the case of bcrypt), a sensible column type is BINARY: BINARY(16) for MD5 and BINARY(56) for bcrypt.

In either case, you should be sure to salt your hashes. Salt is a random string that is concatenated with the user's password before the (final) hash is calculated: the salt that is used is stored with the user record in the database, but is different for each user. This defeats rainbow table attacks to recover users' passwords should your database ever become compromised.

The actual code involved in performing these actions will depend on the language, libraries and/or frameworks with which you are developing your application.
How can I set the default user_handle to be the first part of an email before the @ symbol? Such that john@smith.com would yield a user handle of john.

Such logic is probably most suited to your application code, but it can also be done in the SQL INSERT statement using MySQL's SUBSTRING_INDEX() function:
```
INSERT INTO users VALUES (
  -- [ deletia ]
  SUBSTRING_INDEX('john@smith.com', '@', 1),
  -- [ deletia ]
);
```
Any extra security measures I should take when designing a user database?

I highly recommend that you read this excellent post for a thorough explanation of related security concepts.
The foreign key in other tables assocated with users would need a foreign key that points to userid in users?

Yes. A foreign key exists by virtue of it being used for that purpose; if you wish to enforce foreign key constraints in MySQL (that is, ensuring that the referenced record exists in the foreign table), you will need to be using the InnoDB storage engine for both the local and foreign tables; furthermore, indexes must exist on both the local and foreign copies of the key. The constraint is then defined in the referencing table using a clause like:
```
FOREIGN KEY (userid) REFERENCES users (userid)
```

aroth · Answer 2 · 2012-08-27T01:46:44.877

2

Assuming you have the ability to modify your CREATE TABLE statement, you can just do like:
```
CREATE TABLE `users` (
    `userid` INT(6) NOT NULL AUTO_INCREMENT,
    ...
)
```
Typically, the server that you run on top of the database will generate the password hashes itself (at registration and login time) and then pass the hash to the database as a string. So from a database standpoint, your schema is fine for this. Or nearly fine, depending upon the ultimate length of your password hashes.
Again, this is generally something you would handle in your server code (or even in JavaScript) and not in the database itself. A simple string split() or substring() would do the job. If you really want to do this in the database you can use a trigger to detect the insert event and set the desired default value.
Yes. Salt your passwords.
Yes, your other tables will have a key which references the userid field in 'users'. Something like:
```
CONSTRAINT `FK3FD7C193F39FADA` FOREIGN KEY (`owner_id`) REFERENCES `users` (`userid`)
```

edited Aug 27 '12 at 01:46

answered Aug 27 '12 at 01:01

aroth

51,522
20
132
168

Schema not *quite* fine if bcrypt hashes are used, as 50 characters is probably insufficient: of course this will depend upon the column's encoding, but anything other than binary could lead to problems (unless one is storing the hex representation of the bytes, in which case 50 characters is *definitely* insufficient). – eggyal Aug 27 '12 at 01:40
@eggyal - Good point. Base64 is also an option that should work and be quite a bit more efficient than storing the hex representation as a string. But in any case the field needs to be long enough to accommodate the chosen hash size plus any encoding overhead if it is stored as a string, as you point out. I generally advise against storing binary data directly in a database, mostly because in a lot of database frameworks/API's (particularly Java-based ones like Hibernate) working with binary fields is an order of magnitude more complex than working with text. – aroth Aug 27 '12 at 02:34

MySQL database questions about hashing/storing dates/storing emails

2 Answers2