A user table

We need a way to store login data for each account:

• User name
• Password
• Roles

(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 users table.

The users table

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

To understand 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.

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 252cea... out.

Another one:

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.

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:

Good question! phpMyAdmin let's you apply MD5 to a field, when you insert a new record.

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

Aye, it is.

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.

Yes, good work!

Roles

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:

• admin
• manager
• sales-rep
• service-technician

When someone has more than one role, let's stick the names together, separated by commas. For example...

manager,sales-rep

...would mean someone has the manager and sales-rep goals. So, they should see everything managers can see, and everything sales reps can see.

Easy peasy!

Up next

Let's see how a login program can use the data in this table.