We need a way to store login data for each account:
- User name
(Actually, we won't store passwords directly. More later.)
Let's put user data in the DB. There are several ways to do the task. Let's keep it simple, and make a single
Here's the table's structure.
user_id is the primary key of the table. As usual, an integer (whole number), required (Null is no), and auto increment. As we add records to the table, MySQL will make a new id for us.
name is a character field, with a maximum length of 50 characters. It's required as well.
The last two fields need more explanation.
password_hash, you need to know that we should never store passwords in a database. If someone hacks the DB, we don't want them to be able to see passwords.
Store hashes instead.
Wait, what? How can we check logins, if we don't know people's passwords?
OK, to see how this works, you need to know about hashing functions. A hashing function turns text into gobbledygook.
So you send something in, and you get something out. For example:
DoggsAreGreat! in, and
The function is predictable. Put the same text in, and you always get the same hash out.
The hashing function is a one-way algorithm. Given some plain text, you can compute a hash. But you can't reverse it. If you have a hash, you can't turn that back into plain text. That's why storing hashes in the DB is safe.
There are a bunch of hashing functions in PHP already. We'll use the MD5 algorithm. It's not great from a security point of view, but it's easy to use in a login system. The function is
md5(). Give it something, and it will give you back a hash.
- $hash = md5('Firefly is a great show!');
MD5 always gives you a 32 character result. If the input to
md5() is 10 characters long,
md5() will return 32 characters. If the input is 1,100 characters, the output will be 32 characters long.
That's why the
password_hash in the DB can hold 32 characters:
We don't store passwords in the field
password_hash. We store hashes. Like this:
Hey, when you created those two records, you wanted MySQL to store hashes, not passwords. How did you do that?
Good question! phpMyAdmin let's you apply MD5 to a field, when you insert a new record.
No clear text password
users table does not store anyone's password. It stores hashes of passwords.
Now, our app has a login form.
Someone types a username and password. That data goes to a PHP program. It works out the MD5 hash of what the user entered, and compares that to the hash in the database.
If they're they same:
What if someone types the wrong password?
See what's going on here: the login system works, even though the DB doesn't have passwords in it (it has hashes instead).
Now that's cool.
Aye, it is.
Ooo! I think I figured something out.
Way back in the beginning of the course, you said that you don't know what our passwords are for this site. Is that because this website uses the method you just showed us?
Indeed! It doesn't use MD5, it uses something stronger, but it stores hashes, not plain passwords.
So, ask me what your password is, and I couldn't tell you, even though I have access to the database. I have no way to figure out your password. I can change it to something new, but I can't tell you what the current one is.
Hey, good thinking, Georgina!
Yes, good work!
Here's the table's structure again:
Roles are stored in a text field. Let's see how it works.
Each role has a name, like:
When someone has more than one role, let's stick the names together, separated by commas. For example...
...would mean someone has the
sales-rep goals. So, they should see everything managers can see, and everything sales reps can see.
Let's see how a login program can use the data in this table.